1. Create Two Tables
a. Deptmaster ( deptid int, deptname varchar(50)
b. Empmaster ( Empid int, empname varchar(50) , deptid int, salary )
c. Errormaster ( Errorid int, ErrorCode varchar(20)) ;
( 1 - Sucessfully Inserted , 2 - Sucessfully Updated, 3- Can't Delete the data , 4-DeptName Already Exists, 5- Empname Already Exists )
// Create a SP for insert Deptmaster.
a. Deptmaster.deptname is unique.
b. If I pass same input once again , Show ErrorId - 4 , If its inserted into the table then show Error_id = 1 )
My stored procedure to get a person name written in PL/SQL environment is following:
( PERSON_ID IN NUMBER)
RETURN VARCHAR2 IS
SELECT FIRST_NAME, LAST_NAME INTO F_NAME,L_NAME FROM PERSON WHERE ID = PERSON_ID ;
NAME := F_NAME || ' ' || L_NAME;
WHEN NO_DATA_FOUND THEN
RETURN 'NONAME_BYFUNCTION' ;
I wrote a small VC++ program to get the result for checking. I created PL/SQL block and then carefully prepared it :
:result := GET_PERSON_NAME(:person_id);
I have two variables in VC++ program:
unsigned int per_id;
and want to get the person name in to the variable 'result'. So, I bound the variables in my VC++ program to the PL/SQL function by calling the OCI function OCIBindByPos():
However, after executing the PL/SQL block, the result that i received is not as expected. There are more than one person name in the result string. The length of string becomes larger than the actual length that I previously obtained by calling the same function in SQL Navigator 4.0, as following:
normal: GET_PERSON_NAME(2) ---> the result is: "NGUYEN VIET HUNG"
my case: GET_PERSON_NAME(2) ---> the result is: "NGUYEN VIET HUNG...NONAME_BYFUNCTION...NONAME....."
I think I made a fault in the function OCIBindByPos() but I can't find where my fault is.
How can I get the actual length of the result string so I can display it correctly? Pls help me. Thanks in advance.
I have an image (as a column) in a database. It contains just binary data. I would like to get the size of this blob, and also be able to get at each byte of the data using MS SQL, and ODBC. If anyone out there has a model, I would be ever thankful.
I have a VC++ program which gathers user input to get results from a database using MS SQL. A stored procedure is called and is passed many parameters and everything works fine. Now I want to give the user the ability to sort on the basis of columns as specified in the input window of the user's interface. The program will then create an ORDER BY clause.
I would like to send the ORDER BY CLAUSE parameters as an
input parameter to the stored procedure. The stored
procedure will have something like
ORDER BY "input string"
I want to specify the "input string" as a parameter as well
in addition to the parameters that the stored procedure is
already being passed.
This approach does not work, and the message I get when I
do syntax checking is a pop-up screen with the
title "Microsoft SQL-DMO(ODBC SQL State:42000) and the text
of the error message is "Error 1008: The SELECT item
identified by the ORDER BY number 1 contains a variable as
part of the expression identifying a column position.
Variables are only allowed when ordering by an expression
referencing a column name."
Is there a way I can specify the ORDER BY clause string as
a parameter to the stored procedure? Help would be
I must write a program about "Extended Stored Proc" to transfer data from a SQL Server to a file.But I don't know how to write it.Visual c++ has "Extended Stored Proc Wizard",but I can't use it.Who can help me?
Its surprising to note that ODBC provides such an entangled method of calling stored procedures. I think its a microsoft tactics to develop ODBC API in such a way that calling stored procedure becomes extremely difficult. Result, programers community will avoid calling stored procedures. Side effect, Not calling stored procedure will leave only one option open - Type in SQL statements in C++ code. The moment you type in SQL statements in C++ code, your code becomes DBMS (Oracle, MSSQL, etc) dependant. Because SQL is not same on all leading DBMS in market. For example in SELECT statement for Oracle database we often use TO_CHAR and TO_DATE functions, which are not executable on MSSQL database. Its really high time, we should stop using shit like technology called ODBC in application development. Again the new ADO classes are so difficult to use in VC++ that your ADO based VC++ program will either stuck during compile time or run time.