Microsoft KB Archive/280757

= FIX: SQLParamData or SQLFetch Fails When SQL_ATTR_CURSOR_TYPE Cursor Type is Set to SQL_CURSOR_STATIC =

Article ID: 280757

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1

-



This article was previously published under Q280757



SYMPTOMS
When you use Microsoft Data Access Components (MDAC) 2.5, calling the SQLParamData function with a static server-side cursor returns SQL_ERROR (-1), and you may see this error message:

&quot;[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'A?'. &quot;

With MDAC 2.1, although the SQLParamData function call is successful, the first SQLFetch function call may return SQL_NO_DATA_FOUND (100) even though it should return SQL_SUCCESS because data is available.



RESOLUTION
This problem has been corrected in the SQL Server ODBC driver (Sqlsrv32.dll) included in MDAC 2.6.

This problem can also be avoided by using the odbccmpt utility to set the client compatibility to Microsoft SQL Server 6.5.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This bug was corrected in MDAC 2.6.



Steps to Reproduce Behavior
The following Visual C++ code illustrates an application that produces the behavior described in the &quot;More Information&quot; section. Refer to the comments in the code for the location of the failures with the different versions of MDAC. /* //Script to generate table TestBASE:

if exists (select * from sysobjects where id = object_id('dbo.TESTBASE') and sysstat & 0xf = 3) drop table dbo.TESTBASE GO

CREATE TABLE TESTBASE (CHARCOL CHAR (10), INTCOL INT) GO

INSERT INTO TESTBASE VALUES('2testvalue',1) GO

// A sample C program to reproduce this problem. HENV   henv = SQL_NULL_HENV; HDBC   hdbc1 = SQL_NULL_HDBC; HSTMT   hstmt1 = SQL_NULL_HSTMT; char   logstring[MAXBUFLEN] = &quot;&quot;; char   var1[]= &quot;2testvalue&quot;; char*  pvar1 = var1; void   ProcessLogMessages(HENV plm_henv, HDBC plm_hdbc,                HSTMT plm_hstmt, char *logstring); int i; int main
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) define MAXDSN       25
 * 7) define MAXUID       25
 * 8) define MAXAUTHSTR   25
 * 9) define MAXBUFLEN    255
 * 10) define SIZEOFTEXT   10

{   RETCODE retcode; UCHAR   szDSN[MAXDSN+1] = &quot;Pubs&quot;, szUID[MAXUID+1] = &quot;sa&quot;, szAuthStr[MAXAUTHSTR+1] = &quot;&quot;; //SQLParamData variable. PTR   pParmID; //SQLPutData variables. UCHAR   Data[] = &quot;2testvalue&quot;; SDWORD   cbBatch = (SDWORD)sizeof(Data)-1;

// Allocate the ODBC environment and save handle. retcode = SQLAllocEnv (&henv); // Allocate ODBC connection and connect. retcode = SQLAllocConnect(henv, &hdbc1); retcode = SQLConnect(hdbc1, szDSN, (SWORD)strlen((const char *)szDSN),                 szUID, (SWORD)strlen((const char *)szUID),szAuthStr,                  (SWORD)strlen((const char *)szAuthStr)); // Allocate a statement handle. retcode = SQLAllocStmt(hdbc1,&hstmt1); retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_TYPE, (void *)SQL_CURSOR_STATIC, SQL_IS_INTEGER); // Let ODBC know total length of data to send. SDWORD   lbytes, cbVarSize = 8; lbytes = (SDWORD)SIZEOFTEXT; cbVarSize = SQL_LEN_DATA_AT_EXEC(lbytes); //This will result in a straight insert statement // Bind the parameter. retcode = SQLBindParameter(hstmt1,   // hstmt            1,            // ipar            SQL_PARAM_INPUT,    // fParamType            SQL_C_CHAR,        // fCType            SQL_CHAR,    // FSqlType            10,            // cbColDef            0,            // ibScale            (VOID *)pvar1,        // rgbValue            0,            // cbValueMax            &cbVarSize);        // pcbValue

retcode = SQLExecDirect(hstmt1,(UCHAR *)&quot;SELECT CHARCOL, INTCOL FROM TESTBASE WHERE CHARCOL=?&quot;, SQL_NTS);

// Get ID of parameter that needs data. retcode = SQLParamData(hstmt1, &pParmID); // If data is needed for the Data-At-Execution parameter: if (retcode == SQL_NEED_DATA) {        retcode = SQLPutData(hstmt1, Data, cbBatch); }

// Make final SQLParamData call to signal end of data. // Bug: With MDAC 2.5, this call returns SQL_ERROR (-1). retcode = SQLParamData(hstmt1, &pParmID);

//Bug: With MDAC 2.1, this call returns SQL_NO_DATA_FOUND (100). retcode = SQLFetch(hstmt1); // This call returns SQL_NO_DATA_FOUND retcode = SQLFetch(hstmt1);

/* Clean up. */    SQLFreeStmt(hstmt1, SQL_DROP); SQLDisconnect(hdbc1); SQLFreeConnect(hdbc1); SQLFreeEnv(henv); return(0); }

Additional query words: sqlparamdata sqlfetch syntax

Keywords: kbbug kbfix kbmdac260fix KB280757

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.