I am developing an application, using ODBC, that needs to call a stored procedure in an SQL Server DBMS that has a mix of INPUT and INPUT_OUTPUT parameters. I have the code so that there aren't any errors returned from the function calls but I do not see the bound data change after the SQLExecute() function.
Code snippets follow.
CHAR szStatement[256];
CHAR szBuf[256];
SQLINTEGER irval = 0, filenum = 808042, DoNotCall = 0;
SQLVARCHAR vcPhoneNumber[PHONE_LEN];
SQLVARCHAR vcInstanceCode[INSTANCE_LEN] = {0x20};
SQLVARCHAR vcReason[REASON_LEN] = {0x20};
SQLINTEGER rvalLen = 0, fileLen = 0, noCallLen = 10, phoneLen = SQL_NTS, instanceLen = SQL_NTS, reasonLen = SQL_NTS;
lstrcpy( szStatement, "exec ?= some_proc ?, ?, ?, ?, ?" );
sqlr = SQLPrepare( hStmt, szStatement, lstrlen( szStatement ) );
sqlr = SQLBindParameter( hStmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &irval, 0, &rvalLen );
sqlr = SQLBindParameter( hStmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &filenum, 0, &fileLen );
sqlr = SQLBindParameter( hStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, vcPhoneNumber, 10, &phoneLen );
sqlr = SQLBindParameter( hStmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 50, 0, vcInstanceCode, 0, &instanceLen );
sqlr = SQLBindParameter( hStmt, 5, SQL_PARAM_INPUT_OUTPUT, SQL_C_LONG, SQL_INTEGER, 1, 0, &DoNotCall, 0, &noCallLen );
sqlr = SQLBindParameter( hStmt, 6, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 250, 0, vcReason, 0, &reasonLen );
sqlr = SQLExecute( hStmt );
if( sqlr == SQL_SUCCESS || sqlr == SQL_SUCCESS_WITH_INFO )
{
sprintf( szBuf, "The return value is %d", irval );
sprintf( szBuf, "The file number is %d", filenum );
sprintf( szBuf, "The phone number is %s", vcPhoneNumber );
sprintf( szBuf, "The instance code is %s", vcInstanceCode );
sprintf( szBuf, "The do not call value is %d", DoNotCall );
sprintf( szBuf, "The reason is %s", vcReason );
}
The problem is that in each of the function calls, the sqlr == SQL_SUCCESS but after the call to SQLExecute(), the data does not change for the output parameters. I can do something similar in other query tools that show the proper values but I am not getting the chanes in my bound variables. I tried adding a call to SQLParamData() but I was having a "Function sequence error" problem.
Any help would be very much appreciated. Thanks in advance.
Hi,
Here're a couple of ideas:
== The behavior of the output parameter may vary based on the actual stored proc, which you are trying to execute. To make sure your parameter description is precise, try using SQLProcedureColumns and examine the types.
== As per MSDN:
If the InputOutputType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, ParameterValuePtr points to a buffer in which the driver returns the output value. If the procedure returns one or more result sets, the *ParameterValuePtr buffer is not guaranteed to be set until all result sets/row counts have been processed. If the buffer is not set until processing is complete, the output parameters and return values are unavailable until SQLMoreResults returns SQL_NO_DATA. Calling SQLCloseCursor or SQLFreeStmt with an Option of SQL_CLOSE will cause these values to be discarded.
So what you could do is to fetch the results, call SQLMoreResults appropriately and see if the output parameter will be produced as expected. Also - don't forget the "SET NOCOUNT ON", you might get count tokens as separate resultsets.
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
At first I was having a problem with syntax errors and some other things that didn't make sense to me. I did some poking around and found that the signature I was given whas different from the signature that was defined in the data. I corrected this and was able to remove the errors. I tried calling SQLFetch() and was receiving an error of "Function sequense error." I can try again. The stored procedure does not return a result set. The return to me is throug a return code, the first parameter, and two in/out parameters, the last two.
I am connecting to someone else's system and they have defined the stored procedure and I don't have any control over how they've defined it.
The SQLExecute is returning SQL_SUCCESS_WITH_INFO and this message. The state is "01000" the native error is "0 The message is: [Microsoft][ODBC SQL Server Driver][SQL Server] The 01000 state is a generic message and that message sure looks generic. :)
I added a call to SQLFetch() after the SQLExecute() and the this is the result. The state is "HY010" the native error is "0 The message is: [Microsoft][ODBC SQL Server Driver]Function sequence error
I'm at a loss as to where to go now.
|||Hi,
Would you be able to script the stored proc and the related tables/objects and post the script here? If it's not confidential or overly complicated, of course.
I have a suspicion about what you mentioned regarding the procedure not returning any rows. BTW - did you try the SQLMoreResults? Maybe you have multiple resultsets?
Thanks,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
The stored procedure does not return a result set. I don't think SQLMoreResults() would help.
The body, from a snippet I've been given looks something like this:
/*IsOKToCall Procedure*/
CREATE PROCEDURE IsOKToCall
@.FileNumber int,
@.PhoneNumber char(10),
@.Instance varchar(50),
@.DoNotCall int OUTPUT,
@.DoNotCallReason varchar(255) OUTPUT
AS
SELECT @.Instance = ISNULL(@.Instance, 'master')
-- TODO implement this method to return a non-zero in @.DoNotCall if the account should not be contacted.
SELECT @.DoNotCall = 0
SELECT @.DoNotCallReason = ''
Return @.@.Error
GO
Note that this is not the actual procedure. The person that created it has some PRINT statements in the body to show some debug values and there is logic to return different results based on the value passed in the FileNumber parameter. If I change the first parameter to SQL_PARAMETER_RETURN, I get an error when trying to bind the column. The live procedure also defines the OUTPUT parameters as INPUT_OUTPUT.
|||An update:
I installed SQL Express so I can see what is going on with both sides of the issue. Some of the error message I thought I had were messages from the "print ......" statements the developers put in thier stored procedures. If I call SQLFetch() after the statement execution, I always have an error of "Function Sequense Error" so I don't think that is why I am not getting values back.
Isn't SQLBindParameter() supposed to bind the parameter in the driver so that when the values are returned, they are also changed in the bound parameters? Is there something else I must do to the the correct values?
|||Someone explain this one to me. The statement being called is "exec ?= IsOkToCall ?, ?, ?, ?, ?" as far and myself and the driver are concerned, there are six parameters. The first one is bound to an INTEGER to receive the @.@.Error value. the second is the ID to search for and is an input type. The third one is a char type and an input parameter. The fourth is a varchar input type. The fifth is an integer and is input_output type to get a result value for logic. And the sixth is a varchar input_output used to recieve the message string. After getting all of the messages from the driver, the sixth parameter is being updated with the value that was set for the fifth result code variable. Changing the parameter numbers to see if there is an off by one error does not fix it.
What should I be looking for for this issue?
|||Robert,
Do not change the parameter numbers but you should try to call SQLMoreResults until you hit SQL_NO_DATA_FOUND and then see if the output parameters are updated or not. I believe print statements are treated as results and thus have to be flushed before getting to output parameters.
Thanks
Waseem
|||I found that out the hard way. It is after gathering all of the data that I am seeing the mixed up returns. The first parameter that is the return value from the procedure is never set and I still have the wrong value being updated with the integer value and it does not change even after I remove all of the print statements from the stored procedure.|||Waseem Basheer - MSFT wrote:
Robert,
Do not change the parameter numbers but you should try to call SQLMoreResults until you hit SQL_NO_DATA_FOUND and then see if the output parameters are updated or not. I believe print statements are treated as results and thus have to be flushed before getting to output parameters.
Thanks
Waseem
I found this to be the case and was doing so. I also found where I was setting the destination to the address of a pointer to a character array rather than sending the pointer to the character array. I also found this whole thing to be increadably finicky and full of poorly/non documented quirks. I have the IN/OUT parmeters working but the return code fom a call of "exec ?= my_proc ?, ?, ?" still illudes me. I am still not getting the value for the ?= parameter but I don't think It is too important for me to get this value.
((