Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q280757

↑ Back to the top


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:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'A?'. "
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.

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


More information

Steps to Reproduce Behavior

The following Visual C++ code illustrates an application that produces the behavior described in the "More Information" 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.
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#define MAXDSN        25
#define MAXUID        25
#define MAXAUTHSTR    25
#define MAXBUFLEN     255
#define SIZEOFTEXT    10
HENV    henv = SQL_NULL_HENV;
HDBC    hdbc1 = SQL_NULL_HDBC;
HSTMT    hstmt1 = SQL_NULL_HSTMT;
char    logstring[MAXBUFLEN] = "";
char	var1[]= "2testvalue";
char*   pvar1 = var1;
void    ProcessLogMessages(HENV plm_henv, HDBC plm_hdbc,
                HSTMT plm_hstmt, char *logstring);
int i;
int main()

{
    RETCODE retcode;
    UCHAR    szDSN[MAXDSN+1] = "Pubs",
             szUID[MAXUID+1] = "sa",
             szAuthStr[MAXAUTHSTR+1] = "";        
    //SQLParamData variable.
    PTR    pParmID;
    //SQLPutData variables.
    UCHAR    Data[] = 
    "2testvalue";
    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 *)"SELECT CHARCOL, INTCOL FROM TESTBASE WHERE CHARCOL=?", 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);
}
				

↑ Back to the top


Keywords: KB280757, kbmdac260fix, kbfix, kbbug

↑ Back to the top

Article Info
Article ID : 280757
Revision : 3
Created on : 10/17/2003
Published on : 10/17/2003
Exists online : False
Views : 295