PRB: SQL Server ODBC Driver Returns Output Parameter as a Result Set in a Plain SQL Statement (241147)
The information in this article applies to:
- Microsoft SQL Server 7.0
- Microsoft ODBC Driver for SQL Server 3.7
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
This article was previously published under Q241147 SYMPTOMS
The SQL Server ODBC Driver version 3.70.06.23 and later does not support the return of an output parameter from an SQL SELECT statement. Instead, the driver returns the output parameter as a result set. For example, when executing "Select ?=Max(qty) from sales" in the Pubs sample database, the value of Max(qty) is not returned as an output parameter, but rather as a result set.
The native OLE DB provider for SQL Server does support the return of an output parameter from a SELECT statement.
RESOLUTION
To retrieve an output parameter, you have the following options:
- From ActiveX Data Objects (ADO) or OLE DB, use the native OLE DB provider for SQL Server (SQLOLEDB).
- If you are using ODBC, retrieve the output parameter from the result set. See the example below for details.
- Instead of using an SQL SELECT statement, use a stored procedure to return the output parameter.
STATUS
This behavior is by design.
MORE INFORMATIONSteps to Reproduce Behavior
Use the ODBC Administrator to create a new data source name (DSN) called "TestBug" that uses the SQL Server ODBC Driver to connect to the SQL Server Pubs database. The following code is an ODBC application that demonstrates how the output parameter is returned:
#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
int main(int argc, char* argv[])
{
SQLHENV m_SQLEnvironment;
SQLHDBC m_SQLConnection;
SQLHSTMT m_SQLStatement;
BOOL m_Connected;
SQLRETURN iReturn;
DWORD returnValue = 0;
DWORD resultValue = 0;
long lBufLength = sizeof(returnValue);
long lBufLength1 = sizeof(resultValue);
//Connect
iReturn = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);
//CHANGE THE DSN NAME HERE along with the length of the DSN.
iReturn = SQLConnect(m_SQLConnection,(SQLCHAR*) "TestBug",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"",SQL_NTS);
m_Connected = TRUE;
//Run Query
iReturn = SQLAllocHandle(SQL_HANDLE_STMT,m_SQLConnection,&m_SQLStatement);
iReturn = SQLPrepare(m_SQLStatement,(SQLCHAR*) "Select ?=Max(qty) from sales",SQL_NTS);
iReturn = SQLBindParameter(m_SQLStatement,1,SQL_PARAM_OUTPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&returnValue,0,&lBufLength);
iReturn = SQLExecute(m_SQLStatement);
iReturn = SQLFetch(m_SQLStatement);
if (iReturn == SQL_SUCCESS || iReturn == SQL_SUCCESS_WITH_INFO)
{
SQLGetData(m_SQLStatement,1,SQL_C_SLONG,&resultValue,0,&lBufLength);
}
iReturn = SQLMoreResults(m_SQLStatement);
while (iReturn != SQL_NO_DATA)
iReturn = SQLMoreResults(m_SQLStatement);
//DISCONNECT
iReturn = SQLFreeHandle(SQL_HANDLE_STMT,m_SQLStatement);
iReturn = SQLDisconnect(m_SQLConnection);
iReturn = SQLFreeHandle(SQL_HANDLE_DBC,m_SQLConnection);
iReturn = SQLFreeHandle(SQL_HANDLE_ENV,m_SQLEnvironment);
m_SQLStatement = NULL;
m_SQLConnection = NULL;
m_SQLEnvironment = NULL;
return 0;
}
In the above example, the output parameter is not returned in the returnValue variable after the call to the SQLMoreResults function returns SQL_NO_DATA. Rather, the SQLGetData function returns the parameter in the resultValue variable.
| Modification Type: | Major | Last Reviewed: | 12/5/2003 |
|---|
| Keywords: | kbBug kbDatabase kbprb KB241147 |
|---|
|