Thanks.... doing some code in low level C++ and needed to call a stored procedure. Key point to get return code from stored procedure was the name of the parameter RETURN_VALUE and that you need to override the Move method for stored procedures that do NOT return result sets. Also... make sure the parameters are defined IN THE ORDER declared in the SQL command.. including starting with the RETURN_VALUE first.
ReplyHere's a link to an article about using the ATL OLE DB Consumer wizard in VS 2012 to accomplish the same thing. http://solutionstek.blogspot.com/
Reply1. 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 )
ReplyOriginally posted by: Nguyen Nhat Quang
FUNCTION GET_PERSON_NAME
BEGIN
I wrote a small VC++ program to get the result for checking. I created PL/SQL block and then carefully prepared it :
BEGIN
I have two variables in VC++ program:
char result[32];
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():
OCIBindByPos(...,1,result,SQLT_CHR,strlen(result),...);
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"
I think I made a fault in the function OCIBindByPos() but I can't find where my fault is.
My stored procedure to get a person name written in PL/SQL environment is following:
( PERSON_ID IN NUMBER)
RETURN VARCHAR2 IS
F_NAME VARCHAR(30);
L_NAME VARCHAR(30);
NAME VARCHAR(60);
SELECT FIRST_NAME, LAST_NAME INTO F_NAME,L_NAME FROM PERSON WHERE ID = PERSON_ID ;
NAME := F_NAME || ' ' || L_NAME;
RETURN NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'NONAME_BYFUNCTION' ;
END;
:result := GET_PERSON_NAME(:person_id);
END;
unsigned int per_id;
OCIBindByPos(...,2,&per_id,SQLT_NUM,sizeof(per_id),...);
my case: GET_PERSON_NAME(2) ---> the result is: "NGUYEN VIET HUNG...NONAME_BYFUNCTION...NONAME....."
How can I get the actual length of the result string so I can display it correctly? Pls help me. Thanks in advance.
Originally posted by: Chuck Iyer
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.
ReplyOriginally posted by: Uno tursadi
I need the source code plz
Thank u very much
God bless you :-)
Originally posted by: Chuck Iyer
Hi,
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
SELECT ......
FROM .....
WHERE ....
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
appreciated. Regards.
Chuck Iyer
Reply
Originally posted by: litowen
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?
ReplyOriginally posted by: Manoj Singh
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.
Reply
Originally posted by: sanjib
This is very nice thing
Reply