|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 241147 - Last Review: December 5, 2003 - Revision: 4.1 PRB: SQL Server ODBC Driver Returns Output Parameter as a Result Set in a Plain SQL StatementThis article was previously published under Q241147
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.
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.
This behavior is by design.
Steps 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.
APPLIES TO- Microsoft SQL Server 7.0 Standard Edition
- Microsoft ODBC Driver for Microsoft 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
| kbbug kbdatabase kbprb KB241147 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |