Microsoft KB Archive/293790

= FIX: SQLDescribeParam Causes Syntax Error or Access Violation =

Article ID: 293790

Article Last Modified on 9/17/2003

-

APPLIES TO


 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q293790



SYMPTOMS
When calling SQLDescribeParam for a parameterized query where a &quot;?&quot; parameter marker appears after a nested subquery, the following errors may occur:

DIAG [07009] [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index(0)

DIAG [42000] [Microsoft][ODBC SQL Server Driver]Syntax error or access violation (0)



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

This problem was corrected in Microsoft Data Access Components (MDAC) 2.7.



MORE INFORMATION
Calling SQLDescribeParam to obtain information for parameter markers inside a subquery is unsupported. However, if you have a parameter marker after a subquery and you call SQLDescribeParam for that parameter, you will receive the above errors.

Steps to Reproduce Behavior
  Create a new Win32 console application and add the following code to it: #include      #include       #include 
 * 1) include 

void main {     HENV              henv; HDBC             hdbc; HSTMT            hstmt; SQLRETURN        sr;

SQLCHAR*         theDiagState = new SQLCHAR[50]; SQLINTEGER       theNativeState; SQLCHAR*         theMessageText  = new SQLCHAR[255]; SQLSMALLINT      iOutputNo,DataType,DecimalDigits, Nullable; SQLUINTEGER      ParamSize;

SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, & henv );

sr = SQLSetEnvAttr( henv, SQL_ATTR_ODBC_VERSION, ( void * ) SQL_OV_ODBC3, 0 );

sr = SQLAllocHandle( SQL_HANDLE_DBC, henv, & hdbc );

//Note that the DSN name is Pubs here. Change the DSN name, //user ID, and password here. sr = SQLConnect( hdbc, ( unsigned char * ) &quot;Pubs&quot;, SQL_NTS,               ( unsigned char * ) &quot;sa&quot;, SQL_NTS,                ( unsigned char * ) &quot;&quot;, SQL_NTS );

sr = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, & hstmt ); //COMMENT THIS LINE AFTER YOU GET THE ERROR. sr = SQLPrepare(hstmt,( unsigned char * ) &quot;select A.fname from employee A where (A.job_id IN (select B.Job_id from jobs B)) AND job_lvl=?&quot;, SQL_NTS ); //UNCOMMENT THE FOLLOWING LINE TO SEE THE CODE WITHOUT ERROR. //This query is equivalent to the one above, but it has the parameter     //marker before the subquery. //sr = SQLPrepare(hstmt,( unsigned char * ) &quot;select A.fname from employee A where job_lvl=? AND (A.job_id IN (select B.Job_id from jobs B)) &quot;, SQL_NTS );

sr = SQLDescribeParam(hstmt,1, &DataType, &ParamSize, &DecimalDigits, &Nullable);

if (sr != SQL_SUCCESS) {          //With this bug, you will get an error message. Check this message in theMessageText.

SQLGetDiagRec(SQL_HANDLE_STMT, hstmt,1,theDiagState, &theNativeState, theMessageText, 100, &iOutputNo); printf( &quot;Error :%s\n&quot;, theMessageText); }     if (sr == SQL_SUCCESS) {      printf( &quot;SQLDescribeparam was successful\n&quot;); }

//Free allocated memory and disconnect. SQLFreeStmt( hstmt, SQL_CLOSE ); SQLFreeStmt( hstmt, SQL_DROP ); SQLDisconnect( hdbc ); SQLFreeHandle( SQL_HANDLE_DBC, hdbc ); SQLFreeHandle( SQL_HANDLE_ENV, henv ); delete theMessageText; delete theDiagState;

}                    Change the data source name (DSN), user ID, and password. Run the code and you will see the error message. Comment the SQLPrepare call, uncomment the call to SQLPrepare that has the parameter marker before the subquery, and then run the code.</li></ol>

Additional query words: API kbODBC

Keywords: kbbug kbfix KB293790

-

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

© Microsoft Corporation. All rights reserved.