FreeTDS Development Group

[Nilesh Dhawale <ndhawale AT gmail.com>]

Sorry,  I forgot to attach the program to my previous email:

>>>
It seems like SQLNumResultCols is not working right in the freeTDS
ODBC driver. I am on Solaris. The function returns 0 for the number of
columns in a table which has some columns. Return code is SQL_SUCCESS.
This happens if the statement is only "prepared", not "executed". I
tried on 6 different databases on windows and DB2 driver on AIX and
they all seem to work fine without having to "execute"! I am using
freetds-0.62.4 odbc driver. Is this a known issue?
A complete test program is attached. Here is a code snippet that shows
the problem.

/* Prepare the SQL statement */
retcode = SQLPrepare(hstmt1, "select * from MYTBL", SQL_NTS);
checkStatus(retcode, "SQLPrepare", SQL_HANDLE_STMT, hstmt1, 0);
/*
   // WITHOUT THIS EXECUTE, SQLNumResultCols does not return
   // THE CORRECT NUMBER OF COLS; MOREOVER THE RETURN
   // CODE FROM SQLNumResultCols indicates success
   // execute
  retcode = SQLExecute(hstmt1);
  printf("SQLExecute RET CODE : %d\n", retcode);
  checkStatus(retcode, "SQLExecute", SQL_HANDLE_STMT, hstmt1, 0);
*/
retcode = SQLNumResultCols(hstmt1, &numberOfColumns);
checkStatus(retcode, "SQLNumResultCols", SQL_HANDLE_STMT, hstmt1, 0);
printf("numberOfColumns: %d\n", numberOfColumns);
printf("RET VALUE: %d\n", retcode);

Rgds,
Nilesh
--
Nilesh Dhawale
http://www.cs.albany.edu/~nilesh
--


-- 
Nilesh Dhawale
http://www.cs.albany.edu/~nilesh
--

// compile using:
// cl sqlGetDiagField.c /Zi /nologo /link odbc32.lib

#include <stdio.h>
#include <string.h>
//#include <windows.h>
#include <sql.h>
#include <sqlext.h>
//#include <odbcss.h>

#define MAXBUFLEN   255
#define MAXNAME 20
#define SALES_PERSON_LEN 10
#define STATUS_LEN 6
#define STR_LEN 128+1
#define REM_LEN 254+1

void checkStatus(SQLRETURN retcode, char* function,
                 SQLSMALLINT  handleType,
                 SQLHANDLE    handle,
                 SQLSMALLINT  recNumber)
{
    if (retcode != SQL_SUCCESS)
    {
        char messageText[256];
        SQLSMALLINT errorMessageLength;
        SQLCHAR   sqlState[6];

        SQLRETURN sqlReturn = 
SQLGetDiagRec(handleType,handle,1/*recNumber*/,sqlState,NULL,
                                            
messageText,sizeof(messageText),&errorMessageLength);
        printf("%s failed with return code %d\n", function, retcode);
        printf("MESSAGE: %s\n", messageText);

        sqlReturn = 
SQLGetDiagRec(handleType,handle,2/*recNumber*/,sqlState,NULL,
                                            
messageText,sizeof(messageText),&errorMessageLength);
        printf("MESSAGE: %s\n", messageText);
    }

    if(!SQL_SUCCEEDED(retcode))
    {
        exit(1);
    }
}

int main(int argc, char* argv[])
{
SQLHENV      henv = SQL_NULL_HENV;
SQLHDBC      hdbc1 = SQL_NULL_HDBC;     
SQLHSTMT   hstmt1 = SQL_NULL_HSTMT;

SQLSMALLINT      sOrderID;
SQLSMALLINT      sCustID;
DATE_STRUCT      dsOpenDate;
//SQLCHAR         szSalesPerson[SALES_PERSON_LEN];
SQLCHAR         szStatus[STATUS_LEN];
SQLINTEGER      cbOrderID = 0, cbCustID = 0, cbOpenDate = 0, cbSalesPerson = 
SQL_NTS,
cbStatus = SQL_NTS;
SQLRETURN retcode;
SQLHSTMT hstmt;

   // SQLBindCol variables
   SQLCHAR      szName[MAXNAME+1];
   SQLINTEGER   cbName;

/* Declare buffers for result set data */

SQLCHAR       szCatalog[STR_LEN], szSchema[STR_LEN];
SQLCHAR       szTableName[STR_LEN], szColumnName[STR_LEN];
SQLCHAR       szTypeName[STR_LEN], szRemarks[REM_LEN];
SQLCHAR       szColumnDefault[STR_LEN], szIsNullable[STR_LEN];
SQLINTEGER    ColumnSize, BufferLength, CharOctetLength, OrdinalPosition;
SQLSMALLINT   DataType, DecimalDigits, NumPrecRadix, Nullable;
SQLSMALLINT   SQLDataType, DatetimeSubtypeCode;
//SQLRETURN     retcode;
//SQLHSTMT      hstmt;

/* Declare buffers for bytes available to return */

SQLINTEGER cbCatalog, cbSchema, cbTableName, cbColumnName;
SQLINTEGER cbDataType, cbTypeName, cbColumnSize, cbBufferLength;
SQLINTEGER cbDecimalDigits, cbNumPrecRadix, cbNullable, cbRemarks;
SQLINTEGER cbColumnDefault, cbSQLDataType, cbDatetimeSubtypeCode, 
cbCharOctetLength;
SQLINTEGER cbOrdinalPosition, cbIsNullable;

   SQLCHAR version[MAXBUFLEN];
   SQLSMALLINT driverInformationSize = 0;
   SQLSMALLINT numberOfColumns;

   SQLCHAR* sqlText;

   // Allocate the ODBC Environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   checkStatus(retcode, "SQLAllocHandle", SQL_HANDLE_ENV, henv, 0);

   // Notify ODBC that this is an ODBC 3.0 application.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   checkStatus(retcode, "SQLSetEnvAttr", SQL_HANDLE_ENV, henv, 0);

   // Allocate an ODBC connection and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   checkStatus(retcode, "SQLAllocHandle", SQL_HANDLE_DBC, hdbc1, 0);

   retcode = SQLConnect(hdbc1, argv[1], SQL_NTS, argv[2], SQL_NTS, argv[3], 
SQL_NTS);
   //checkStatus(retcode, "SQLConnect", SQL_HANDLE_DBC, hdbc1, 0);
   
   // Find driver version.
   retcode = SQLGetInfo(hdbc1, SQL_DRIVER_VER, version, MAXBUFLEN, 
&driverInformationSize);
   checkStatus(retcode, "SQLGetInfo", SQL_HANDLE_DBC, hdbc1, 0);
   printf("DRIVER_VER: %s\n", version);
   retcode = SQLGetInfo(hdbc1, SQL_DRIVER_ODBC_VER, version, MAXBUFLEN, 
&driverInformationSize);
   checkStatus(retcode, "SQLGetInfo", SQL_HANDLE_DBC, hdbc1, 0);
   printf("ODBC_DRIVER_VER: %s\n", version);

   // Allocate a statement handle.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
   checkStatus(retcode, "SQLAllocHandle", SQL_HANDLE_STMT, hstmt1, 0);

   /* Prepare the SQL statement */
    retcode = SQLPrepare(hstmt1, "select * from MYTBL", SQL_NTS);
    checkStatus(retcode, "SQLPrepare", SQL_HANDLE_STMT, hstmt1, 0);

/* 
    // execute
   retcode = SQLExecute(hstmt1);
   printf("SQLExecute RET CODE : %d\n", retcode);
   checkStatus(retcode, "SQLExecute", SQL_HANDLE_STMT, hstmt1, 0);
*/

   retcode = SQLNumResultCols(hstmt1, &numberOfColumns);
    checkStatus(retcode, "SQLNumResultCols", SQL_HANDLE_STMT, hstmt1, 0);
   printf("numberOfColumns: %d\n", numberOfColumns);
   printf("RET VALUE: %d\n", retcode);

    /* Clean up. */
   SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
   SQLDisconnect(hstmt1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   return(0);


}