SQLParamData is not fully compliant with streaming parameters in ODBC 3.8

[groprima]

According to the MS example regarding retrieving Output Parameters with SQLGetData, SQLParamData should be called twice to ensure there is no more streamed parameter data to retrieve. I tested it with MS ODBC drivers 17 and 18 by adding an intermediate state when SQL_PARAM_DATA_AVAILABLE is returned, and it works as expected. The check for the ODBC version also needs to be added (connection_mode >= SQL_OV_ODBC3_80).

else if ( ret == SQL_PARAM_DATA_AVAILABLE  )
{
    if (statement -> state = **STATE_S14_1**) {
        statement -> state = STATE_S14;
    } else {
        statement -> state = **STATE_S14_1**;
    }
}

Microsoft:

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/retrieving-output-parameters-using-sqlgetdata?view=sql-server-ver16

For each parameter, call SQLBindParameter with InputOutputType set to SQL_PARAM_OUTPUT_STREAM and ParameterValuePtr set to a token, such as a parameter number, a pointer to data, or a pointer to a structure that the application uses to bind input parameters. This example will use the parameter ordinal as the token.

Execute the query with SQLExecDirect or SQLExecute. SQL_PARAM_DATA_AVAILABLE will be returned, indicating that there are streamed output parameters available for retrieval.

Call SQLParamData to get the parameter that is available for retrieval. SQLParamData will return SQL_PARAM_DATA_AVAILABLE with the token of the first available parameter, which is set in SQLBindParameter (step 1). The token is returned in the buffer that the ValuePtrPtr points to.

Call SQLGetData with the argument Col_or_Param_Num set to the parameter ordinal to retrieve the data of the first available parameter. If SQLGetData returns SQL_SUCCESS_WITH_INFO and SQLState 01004 (data truncated), and the type is variable length on both the client and server, there is more data to retrieve from the first available parameter. You can continue to call SQLGetData until it returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO with a different SQLState.

Repeat step 3 and step 4 to retrieve the current parameter.

Call SQLParamData again. If it returns anything except SQL_PARAM_DATA_AVAILABLE, there is no more streamed parameter data to retrieve, and the return code will be the return code of the next statement that is executed.

Call SQLMoreResults to process the next set of parameters until it returns SQL_NO_DATA. SQLMoreResults will return SQL_NO_DATA in this example if the statement attribute SQL_ATTR_PARAMSET_SIZE was set to 1. Otherwise, SQLMoreResults will return SQL_PARAM_DATA_AVAILABLE to indicate that there are streamed output parameters available for the next set of parameters to retrieve.

[lurcher]

On 03/02/2023 18:37, groprima wrote: According to the MS example regarding retrieving Output Parameters with SQLGetData, SQLParamData should be called twice to ensure there is no more streamed parameter data to retrieve. I tested it with MS ODBC drivers 17 and 18 by adding an intermediate state when SQL_PARAM_DATA_AVAILABLE is returned, and it works as expected. The check for the ODBC version also needs to be added (connection_mode >= SQL_OV_ODBC3_80). |else if ( ret == SQL_PARAM_DATA_AVAILABLE ) { if (statement -> state = **STATE_S14_1**) { statement -> state = STATE_S14; } else { statement -> state = **STATE_S14_1**; } } | Microsoft: https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/retrieving-output-parameters-using-sqlgetdata?view=sql-server-ver16

Is there still a MS ODBC Reference that exists outside of a description of a driver? I remember MS giving ODBC to The OpenGroup. But I guess after doing that they needed to just make changes anyway. I am getting too old for this.

[groprima]

The only source I could find that defines this behaviour is the MS example above. The MS implementation of the SQL Server driver for macOS and Windows verifies the functionality.

[v-chojas]

It's not clear from your initial report and subsequent message that anything is actually wrong --- do you see an error message (and if so, what does it say) where you expected success?

[groprima]

Sorry, I wasn't clear. If SQLParamData is called a second time, as indicated in item 6 of the MS example, the DM returns the error message:

Msg 0, State HY010, [unixODBC][Driver Manager]Function sequence error

However, if this call is omitted, everything works fine, and correct results are returned. At this point, I suspect a mistake in the MS example.