SQLExecDirect not found by ODBC driver manager

Question

I am developing an ODBC driver on Windows 10 Pro 64-bit . I have defined SQLExecDirect in my ODBC driver. However when I test it Microsoft ODBC driver manager always returns "Driver does not support this function". Our SQLConnect, SQLDriverConnect and SQLPrepare all work fine, but this SQLExecDirect and SQLExecute could not work all the time. I verified the SQLPrepare and SQLExecDirect, they have same parameters. The strange thing is SQLPrepare works well, but not for SQLExecDirect. As the error is from ODBC manager, it is a blackbox for us. Currently I could not figure out what is wrong as I have checked every corner of our code. I did not see anything missing for SQLExecDirect. Anyone has idea what is wrong?

--ODBC trace

xxxx-odbc 4c4c-59e8	ENTER SQLExecDirectW   
		HSTMT               0x0000023EE00D9C00  
		WCHAR *             0x0000023EE1F91930 [      -3] "SELECT * from test\ 0"  
		SDWORD                    -3  
  
xxxx-odbc 4c4c-59e8	EXIT  SQLExecDirectW  with return code -1 (SQL_ERROR)  
		HSTMT               0x0000023EE00D9C00  
		WCHAR *             0x0000023EE1F91930 [      -3] "SELECT * from test\ 0"  
		SDWORD                    -3  
  
		**DIAG [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (0)**   

--C:\Program Files (x86)\Windows Kits\10\Include\10.0.19041.0\um\sqlucode.h

#define SQLExecDirect       SQLExecDirectW  

--SQLExecDirect definition

SQLRETURN SQL_API SQLExecDirect(SQLHSTMT stmt, SQLWCHAR* query,  
                                SQLINTEGER queryLen) {  
  return ournamespace::SQLExecDirect(stmt, query, queryLen);  
}  

Answer 1

Hi YujianYao-MSFT,

I think it is not like a compatibility issue. Today I tried to add my own SQLGetFunctions. The SQLExecDirect and SQLExecute could work successfully if I enabled them in SQL_API_ODBC3_ALL_FUNCTIONS block. However if I just enable them, SQLGetInfo will not be found. So I enabled SQLGetInfo, then SQLAllocHandle for statement is not found. So I enabled SQLAllocHandle. But the SQLExecDirect/SQLExecute problem came again. Just like I did not set all of them. I am not sure how Microsoft ODBC driver manager is implemented, seems there is some connection between these 3 functions. Maybe Microsoft DM is checking some info based on SQLGetInfo to decide if the ODBC driver could support SQLExecute/SQLExecDirect?

--my ODBC driver code of SQLGetFunctions  
        SQLRETURN SQLGetFunctions(SQLHDBC conn, SQLUSMALLINT funcId,  
                                          SQLUSMALLINT* supported) {  
          if (funcId == SQL_API_ODBC3_ALL_FUNCTIONS) {  
          
        supported[SQL_API_SQLEXECUTE>>4] |= (1 << ((SQL_API_SQLEXECUTE) & 0x000F));  
        supported[SQL_API_SQLEXECDIRECT >> 4] |= (1 << ((SQL_API_SQLEXECDIRECT) & 0x000F));  
      
        supported[SQL_API_SQLGETINFO>>4] |= (1 << ((SQL_API_SQLGETINFO) & 0x000F));  
        supported[SQL_API_SQLGETDATA >> 4] |= (1 << ((SQL_API_SQLGETDATA)&0x000F));  
        supported[SQL_API_SQLALLOCHANDLE>>4] |= (1 << ((SQL_API_SQLALLOCHANDLE) & 0x000F));   
        supported[SQL_API_SQLALLOCSTMT>>4] |= (1 << ((SQL_API_SQLALLOCSTMT) & 0x000F));  
        supported[SQL_API_SQLFREEHANDLE >> 4] |=  
            (1 << ((SQL_API_SQLFREEHANDLE)&0x000F));  
        supported[SQL_API_SQLFREESTMT >> 4] |=  
            (1 << ((SQL_API_SQLFREESTMT)&0x000F));  
        supported[SQL_API_SQLCONNECT >> 4] |= (1 << ((SQL_API_SQLCONNECT)&0x000F));  
        supported[SQL_API_SQLDRIVERS >> 4] |= (1 << ((SQL_API_SQLDRIVERS)&0x000F));  
        supported[SQL_API_SQLDRIVERCONNECT >> 4] |=  
            (1 << ((SQL_API_SQLDRIVERCONNECT)&0x000F));  
        supported[SQL_API_SQLDISCONNECT >> 4] |=  
            (1 << ((SQL_API_SQLDISCONNECT)&0x000F));  
      } else if (funcId == SQL_API_ALL_FUNCTIONS) {  
        supported[SQL_API_SQLEXECDIRECT] = 1;  
        supported[SQL_API_SQLEXECUTE] = 1;  
        supported[SQL_API_SQLGETINFO] = 1;  
        supported[SQL_API_SQLALLOCSTMT] = 1;  
      } else if (funcId == SQL_API_SQLEXECDIRECT ||   
          funcId == SQL_API_SQLEXECUTE || funcId == SQL_API_SQLGETINFO ||   
          funcId == SQL_API_SQLALLOCSTMT || funcId == SQL_API_SQLALLOCHANDLE) {  
        *supported = 1;  
      }  
      
      return SQL_SUCCESS;  
}  

Answer 2

Hi @YujianYao-MSFT

We are developing ODBC driver for a database so we could not avoid ODBC. Fortunately I have identified the root cause. Someone else made the following code change for iODBC on Mac. Seems there is a conflict with Windows DM.

 #ifdef SQL_CURSOR_COMMIT_BEHAVIOR  
      // Value that indicates how a COMMIT operation affects cursors and  
      // prepared statements in the data source (the behavior of the data source  
      // when you commit a transaction).  
      // The value of this attribute will reflect the current state of the next  
      // setting: SQL_COPT_SS_PRESERVE_CURSORS.  
      // SQL_CB_DELETE = Close cursors and delete prepared statements.  
      //    To use the cursor again, the application must reprepare and re-execute  
      //    the statement.  
      // SQL_CB_CLOSE = Close cursors. For prepared statements, the application  
      //    can call SQLExecute on the statement without calling SQLPrepare again.  
      //    The default for the SQL ODBC driver is SQL_CB_CLOSE. This means that the  
      //    SQL ODBC driver will close your cursor(s) when you commit a transaction.  
      // SQL_CB_PRESERVE = Preserve cursors in the same position as before the  
      //    COMMIT operation. The application can continue to fetch data, or it can  
      //    close the cursor and re-execute the statement without repreparing it.  
      intParams[SQL_CURSOR_COMMIT_BEHAVIOR] = SQL_CB_CLOSE;  
    #endif  // SQL_CURSOR_COMMIT_BEHAVIOR  
      
    #ifdef SQL_CURSOR_ROLLBACK_BEHAVIOR  
      // Value that indicates how a ROLLBACK operation affects cursors and prepared  
      // statements in the data source:  
      // SQL_CB_DELETE = Close cursors and delete prepared statements.  
      //    To use the cursor again, the application must reprepare and re-execute  
      //    the statement.  
      // SQL_CB_CLOSE = Close cursors. For prepared statements, the  
      //    application can call SQLExecute on the statement without calling  
      //    SQLPrepare again.  
      // SQL_CB_PRESERVE = Preserve cursors in the same position as before  
      //    the ROLLBACK operation. The application can continue to fetch data, or  
      //    it can close the cursor and re-execute the statement without repreparing  
      //    it.  
      intParams[SQL_CURSOR_ROLLBACK_BEHAVIOR] = SQL_CB_CLOSE;  
    #endif  // SQL_CURSOR_ROLLBACK_BEHAVIOR