Cannot retrieve return value from stored procedure via ODBC

IDS 7.30TC9 on NT Informix Client SDK 2.40 I have a stored procedure that retrieves the serial value for the last insert statement. I can use this procedure successfully within dbaccess and it returns the expected value, but if I try to call it from ODBC, the parameter length (cbValue) indicates no return value (SQL_NULL_DATA) and *value is always 0. onstat -g ses <#> shows that the last executed command was EXECUTE PROCEDURE. ODBC Tracing shows that the *value is being sent to the RDBMS. I added the SQLMoreResults call but it didn't change the return value. I have also tried adding parameters to the procedure but that did not change the results either. My stored procedure CREATE Serial_Value () RETURNING INT; DEFINE Local_Value INT;; SELECT dbinfo("sqlca.sqlerrd1") INTO Local_Value FROM Systables WHERE tabid=1; RETURN Local_Value; END PROCEDURE; My ODBC logic: int serial_value(int *value) { SQLRETURN rc; SQLRETURN odbc_status = SQL_SUCCESS; SQLHSTMT hstmt; SQLINTEGER cbvalue = 4; SQLINTEGER dummy = 0; SQLINTEGER cbdummy = 0; /*If statement handle allocated successfully then*/ if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)) == SQL_SUCCESS) { //Bind the serial value as an output parameter rc = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, value, 0, &cbvalue); // rc = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &dummy, 0, &cbdummy); //If an error occurred if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { /*Clear serial value*/ *value = 0; /*Check status using statement handle*/ odbc_status = process_status(SQL_HANDLE_STMT, hstmt, rc, 0); } //Execute the Serial_Value stored procedure to retrieve the serial value of last insert rc = SQLExecDirect(hstmt, "{?=call Serial_Value()}", SQL_NTS); /*If an error occurred then*/ if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { /*Clear serial value*/ *value = 0; /*Check status using statement handle*/ odbc_status = process_status(SQL_HANDLE_STMT, hstmt, rc, 0); } else { // Show parameters are not filled. printf("Before result sets cleared: RetCode = %d, OutParm = %d.\\n", rc, *value); // Clear any result sets generated. while ( ( rc = SQLMoreResults(hstmt) ) != SQL_NO_DATA ); // Show parameters are now filled. printf("After result sets drained: RetCode = %d, OutParm = %d.\\n", rc, *value); } } /*Else error allocating statement handle*/ else { /*Check status using database handle*/ odbc_status = process_status(SQL_HANDLE_DBC, hdbc, rc, 0); } //Return the status return odbc_status; } Are there any obvious logic/code problem? Does anybody have a better way of retrieving the serial value via ODBC? Thanks for any help with this problem. Tim Gilbert

Keep in mind that dbinfo("sqlca.sqlerrd1") get initialized with every SQL statement. For example, in your dbaccess example that worked, if you had called serial_value() twice in succession after the insert, only the first call would return the serial value because the second call would report on serial inserts of the previous SQL stmt (i.e the first call to serial_value). That could point to your problem. Its possible that something in your serial_value C function before your procedure call is treated by Informix as an SQL statement - could be SQLAllocHandle or SQLBindParameter. One solution is to skip the serial_value C wrapper, directly calling the procedure within the function that does the INSERT. Rudy Tim Gilbert wrote: > IDS 7.30TC9 on NT > Informix Client SDK 2.40 > > I have a stored procedure that retrieves the serial value for the last > insert statement. > I can use this procedure successfully within dbaccess and it returns the > expected value, but if I try to call it from ODBC, the parameter length > (cbValue) indicates no return value (SQL_NULL_DATA) and *value is always > 0. onstat -g ses <#> shows that the last executed command was EXECUTE > PROCEDURE. ODBC Tracing shows that the *value is being sent to the > RDBMS. I added the SQLMoreResults call but it didn't change the return > value. I have also tried adding parameters to the procedure but that > did not change the results either. > ... > Tim Gilbert