MS ODBC driver 18 for SQL server : output parameters are not binding to the variables when stored procedure is executed

Question

C program is running in Linux redhat to connect to sql server 2019 using MSODBC  ODBC APIs (msodbcsql18-18.1.1.1-1.x86_64.rpm ).

ISSUE:  sql output parameters are not binding to the variables when stored procedure is executed using

SQLBindParameter(stmt,1,SQL_PARAM_OUTPUT,SQL_C_LONG,SQL_INTEGER,10,0,&retValue,sizeof(retValue),NULL);

SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,50,0,&batchid,sizeof(batchid),NULL);

SQLBindParameter(stmt,3,SQL_PARAM_OUTPUT,SQL_C_LONG,SQL_INTEGER,10,0,&count,sizeof(count),NULL);

SQLBindParameter(stmt,4,SQL_PARAM_OUTPUT,SQL_C_LONG,SQL_INTEGER,1,0,&action,sizeof(action),NULL);

ret = SQLExecDirect(stmt, "{? = call est_proc (?,?,?)}", SQL_NTS);

am trying to print the value of variables before procedure is called and after procedure is executed.

Before procedure is called

return value is : 1514548520

action is : 5384

count is : 1514548516

after procedure is called, variables are still with same value and reaches SQL_NO_DATA

return value is : 1514548520

action is : 5384

count is : 1514548516

no data

But the stored procedure is getting executed and running multiple inserts and updates which is working fine. but ouptut parameter retValue , count and action is not returned with right numbers.

Answer 1

Based on the provided information, it seems that there might be an issue with how the output parameters are bound or how the stored procedure is being called. Here are a few things to check and consider:

1. SQL Server Stored Procedure Definition: Verify that the stored procedure est_proc is correctly defined in SQL Server and that it indeed has three output parameters: retValue, count, and action. Ensure that the data types and sizes of the output parameters match the ones used in the C program.
2. SQLBindParameter Parameters: Check the parameters passed to the SQLBindParameter function. Ensure that the parameter numbers and data types match the ones expected by the stored procedure. For example, ensure that the parameter number for retValue is 1, for count is 3, and for action is 4.
3. Error Handling: Check for any error codes returned by the ODBC functions, including SQLBindParameter and SQLExecDirect. Make sure to handle any potential errors appropriately. You can use SQLGetDiagRec to retrieve error information for diagnosis.
4. Output Buffer Sizes: Ensure that the output buffer sizes for the variables retValue, count, and action are large enough to hold the data returned by the stored procedure. The buffer size should be at least the size of the data type.
5. ODBC Driver Compatibility: Verify that the version of the MS ODBC driver 18 for SQL Server (msodbcsql18-18.1.1.1-1.x86_64.rpm) is compatible with SQL Server 2019. It's essential to use a compatible ODBC driver version with the corresponding SQL Server version.
6. Stored Procedure Execution: Double-check that the stored procedure is executed correctly using the SQLExecDirect function. Ensure that the parameters are passed in the correct order and that the procedure is called as expected.
7. Stored Procedure Behavior: Examine the behavior of the stored procedure itself. Make sure there are no issues with the logic inside the procedure that might affect the output parameter values.
8. Debugging: Consider adding additional debug information in your C program to trace the values of the output parameters before and after the stored procedure call. This can help identify any unexpected behavior.

If you have verified all the above aspects and the issue persists, it might be helpful to consult the official documentation of the MS ODBC driver, review any relevant Microsoft support articles, or reach out to their support team for further assistance with the specific version of the ODBC driver and SQL Server being used.

Answer 2

I have little experience with ODBC programming, but I have worked with other APIs. Generally, you need to retrieve all result sets, before output parameters can be retrieved. You say that your procedure performs inserts and updates, so there may not be any SELECT statements returning data. However, INSERT and UPDATE produce these "1 row(s) affected" messages, and these rowcount comes empty result set. You can decline to get this rowcounts by adding SET NOCOUNT ON to the stored procedure, but even then, you may still have to attempt to get the first result set (only be told that there isn't any) before the output parameters becomes available.