Appendix

The appendix first defines all of the driver-specific options defined in Odbcss.h and then has two sample applications that illustrate processing text and image data.

Odbcss.h

Odbcss.h is a header file containing the definitions used for all of the driver-specific options in the SQL Server ODBC driver. Odbcss.h is distributed with SQL Server Workstation and with SQL Server 6.5 SP2. The version distributed with SP2 has a few extra connection options related to controlling the ANSI options used by the driver. The list below relates to the 6.5 SP2 version of Odbcss.h.

SQLSetConnectOption

The following options can be set on using SQLSetConnectOption. The bulleted literals are specified as the fOption parameter; the literals grouped under each bulleted fOption are specified as vParam.

SQL_COPT_SS_REMOTE_PWD 

(replaces SQL_REMOTE_PWD)

SQL_COPT_SS_USE_PROC_FOR_PREP 

(replaces SQL_USE_PROCEDURE_FOR_PREPARE)

Controls generation of stored procedures on SQLPrepare.

SQL_COPT_SS_INTEGRATED_SECURITY 

(replaces SQL_INTEGRATED_SECURITY)

Controls use of integrated security when connecting to SQL Server.

SQL_COPT_SS_PRESERVE_CURSORS 

(replaces SQL_PRESERVE_CURSORS)

Controls whether cursors are dropped at the end of a transaction.

SQL_COPT_SS_USER_DAT
A

SQL_COPT_SS_ANSI_OEM

Controls ANSI to EOM conversion of data.

SQL_AO_DEFAULT = SQL_AO_OFF, unless DSN OEM/ANSI checkbox is selected.

SQL_COPT_SS_ENLIST_IN_DTC

Controls enlistment in a distributed transaction managed by the Microsoft Distributed Transaction Coordinator.

SQL_COPT_SS_ENLIST_IN_XA

Enlists in a distributed transaction managed by a transaction manager that complies with the X/Open XA standard.

The vParam value is a pointer to a variable defined using the structure:

typedef struct SQLXaTranTAG
{
   void FAR *transManager;
   void FAR *xaTransID;
   ULONGxaTransIDLength;
   DWORD   dwErrorInfo;
} SQLXaTran;


SQL_COPT_SS_CONNECTION_DEAD

Used to see if connection is still active.

SQL_COPT_SS_FALLBACK_CONNECT

Controls the use of SQL Server Fallback Connections.

SQL_COPT_SS_PERF_DATA

Controls the logging of driver performance data.

SQL_COPT_SS_PERF_DATA_LOG

Specifies the file in which to log performance data.

The vParam value is a pointer to a null-terminated string that contains the file name.

SQL_COPT_SS_PERF_QUERY_INTERVAL

Specifies the interval for the trigger point to log a long-running query.

The vParam value is an integer specifying the interval in seconds.

SQL_COPT_SS_PERF_QUERY_LOG

Specifies the file in which to log long running queries.

The vParam value is a pointer to a null-terminated string that contains the file name.

SQL_COPT_SS_PERF_QUERY

Controls the logging of long running queries.

SQL_COPT_SS_PERF_DATA_LOG_NOW

Instructs the driver to write a performance statistics record to the log.

The vParam value is NULL.

SQL_COPT_SS_QUOTED_IDENT

Controls setting of QUOTED_IDENTIFIER (can only be set before connecting).

SQL_COPT_SS_ANSI_NPW

Controls setting of ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS (can only be set before connecting).

SQLSetStmtOption

The following options can be set on using SQLSetStmtOption. The bulleted literals are specified as the fOption parameter, the literals grouped under each bulleted fOption are specified as vParam.

SQL_SOPT_SS_TEXTPTR_LOGGING 

(replaces SQL_TEXTPTR_LOGGING)

Controls logging of text/image operations.

SQL_SOPT_SS_HIDDEN_COLUMNS

Expose FOR BROWSE hidden columns.

SQL_SOPT_SS_NOBROWSETABLE

SET NOBROWSETABLE option.

SQLGetStmtOption

If SQLGetStmtOption is called with fOption = SQL_SOPT_SS_CURRENT_COMMAND, the driver returns an integer to pvParam indicating which command in the batch is the one whose results are being processed. The first command in the batch is 1.

SQLColAttributes

The following options can be set on using SQLColAttributes. The bulleted literals are specified as the fDescType parameter; the literals grouped under each bulleted fDescType are the values returned as pfDesc.

SQL_CA_SS_COLUMN_SSTYPE

The base data type of a SQL Server result column.
pfDesc values:
SQLBINARY, SQLBIT, SQLCHAR, SQLDATETIME, SQLDATETIM4, SQLDATETIMN, SQLDECIMAL, SQLDECIMALN, SQLFLT4, SQLFLT8, SQLFLTN, SQLIMAGE, SQLINT1, SQLINT2, SQLINT4, SQLINTN, SQLMONEY, SQLMONEY4, SQLMONEYN, SQLNUMERIC, SQLNUMERICN SQLTEXT, SQLVARBINARY, SQLVARCHAR 

SQL_CA_SS_COLUMN_UTYPE

The user-defined data type of a SQL Server result column.
pfDesc values:
SQLudtBINARY, SQLudtBIT, SQLudtCHAR, SQLudtDATETIME, SQLudtDATETIM4, SQLudtDATETIMN, SQLudtDECML, SQLudtDECMLN, SQLudtFLTN, SQLudtFLT4, SQLudtFLT8, SQLudtIMAGE, SQLudtINT1, SQLudtINT2, SQLudtINT4, SQLudtINTN, SQLudtMONEY, SQLudtMONEY4, SQLudtMONEYN, SQLudtNUM, SQLudtNUMN SQLudtSYSNAME, SQLudtTEXT, SQLudtTIMESTAMP, SQLudtVARBINARY, SQLudtVARCHAR,

SQL_CA_SS_NUM_ORDERS

pfDesc returns the number of columns in an ORDER BY clause.

SQL_CA_SS_COLUMN_ORDER

The SELECT list column ID of a column that appears in the SQL statement ORDER BY clause.

pfDesc returns the column ID.

SQL_CA_SS_COLUMN_VARYLEN

pfDesc is TRUE if the column's data can vary in length, otherwise FALSE.

SQL_CA_SS_NUM_COMPUTES

pfDesc returns the number of compute clauses in the current result set.

SQL_CA_SS_COMPUTE_ID

pfDesc returns the compute ID of a compute row.

SQL_CA_SS_COMPUTE_BYLIST

Returns a bylist: an array of the column IDs of the columns participating in a Transact-SQL COMPUTE BY clause.

pfDesc returns a pointer to the bylist for a compute row.

SQL_CA_SS_COLUMN_ID

pfDesc returns the SELECT list column ID to which a COMPUTE BY aggregate refers.

SQL_CA_SS_COLUMN_OP

Identifies the aggregate operation the COMPUTE BY applied to a column.

pfDesc returns:

SQLAOPANY
SQLAOPAVG (AVG())
SQLAOPCNT (COUNT())
SQLAOPMIN (MIN())
SQLAOPMAX (MAX())
SQLAOPNOOP
SQLAOPSUM (SUM())

SQL_CA_SS_COLUMN_SIZE

The maximum length of data for a column.

SQL_CA_SS_COLUMN_HIDDEN

The column is hidden. Applies only if the SELECT was FOR BROWSE and the driver-specific statement option SQL_SOPT_SS_HIDDEN_COLUMNS is set to SQL_HC_ON.

SQL_CA_SS_COLUMN_KEY

The column is a key column.

SQL_CA_SS_BASE_COLUMN_NAME

The base column name.

SQLGetInfo

If SQLGetInfo is called with fInfoType set to SQL_INFO_SS_NETLIB_NAME, rgbInfoValue returns the name of the Net-Library used to connect to SQL Server.

SQLPerf Structure

The meaning of the variables defined in the sqlperf structure are given in this section. These descriptions also apply to the statistics recorded in the performance log file. For a description of how to gather these statistics, see "Diagnosing and Profiling Applications."

Application Profile Statistics

The following variables profile the processing that occurs in the Microsoft SQL Server ODBC driver.

Connection Statistics

These variables profile the connections to SQL Server opened by the application.

Network Statistics

The network packet statistics reported by the driver relate to the TDS packets (for more information about TDS, see "Architecture"). The size of a TDS packet is either the server's default setting specified in sp_configure 'network packet size' or what the ODBC client might request through:

SQLSetConnectOption(hdbc, SQL_PACKET_SIZE, NNNN) 

These packets may be larger than the size of the network packets actually sent by the underlying protocol stack (such as TCP/IP or SPX/IPX). The SQL Server Net-Library DLLs and the underlying protocol stack are the components that map the TDS packets onto the network packets, but this is hidden from both the SQL Server ODBC driver and the DB-Library DLL.

Time Statistics

These are the time statistics.

Putimage.c and Getimage.c

The following sample programs are discussed in "Data-At-Execution and Text and Image Columns." Both depend on a table having been created as follows:

CREATE TABLE emp2 (name CHAR(30), age FLOAT, 
                  birthday DATETIME, BigBin IMAGE)

If Putimage is compiled and run first, then Getimage can be used to read the data. To confirm that all 300,000 bytes of image data has been entered by Putimage, run the following from ISQL/w:

SELECT name, age, birthday, BinLen = datalength(BigBin)
FROM emp2

Note: Some of the error checking has been removed for clarity. Also, both programs use the same function, ProcessLogMessages, whose source code has been deleted from Getimage.c to save space.

Putimage.c

// Sample application to write SQL_LONGVARBINARY data using SQLPutData.
// Assumes DSN has table:
//  CREATE TABLE EMP2 (NAME CHAR(30), AGE FLOAT, 
//           BigBin IMAGE)
#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   300000
HENV   henv = SQL_NULL_HENV;
HDBC   hdbc1 = SQL_NULL_HDBC;
HSTMT   hstmt1 = SQL_NULL_HSTMT;
char   logstring[MAXBUFLEN] = "";
void   ProcessLogMessages(HENV plm_henv, HDBC plm_hdbc,
             HSTMT plm_hstmt, char *logstring);
int main()
{
   RETCODE retcode;
   UCHAR   szDSN[MAXDSN+1] = "ab65def",
      szUID[MAXUID+1] = "sa",
      szAuthStr[MAXAUTHSTR+1] = "password";      
   // SQLBindParameter variables.
   SDWORD   cbTextSize, lbytes;
   //SQLParamData variable.
   PTR   pParmID;
   //SQLPutData variables.
   UCHAR   Data[] = 
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
   "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
         "abcdefghijklmnopqrstuvwxyz";
   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(szDSN),
               szUID, (SWORD)strlen(szUID),szAuthStr,
               (SWORD)strlen(szAuthStr));
   // Print info messages returned.
   if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) ) {
         ProcessLogMessages(henv,
                 hdbc1,
                hstmt1,
                "SQLConnect() Failed\n\n");
         return(9);
   }
   else {
      ProcessLogMessages(henv,
                 hdbc1,
                 hstmt1,
                 "\nConnect Successful\n\n");
   }   
   // Allocate a statement handle.
   retcode = SQLAllocStmt(hdbc1,&hstmt1);
   // Let ODBC know total length of data to send.
   lbytes = (SDWORD)SIZEOFTEXT;
   cbTextSize = SQL_LEN_DATA_AT_EXEC(lbytes);

   // Bind the parameter.
   retcode = SQLBindParameter(hstmt1,   // hstmt
         1,         // ipar
         SQL_PARAM_INPUT,   // fParamType
         SQL_C_BINARY,      // fCType
         SQL_LONGVARBINARY,   // FSqlType
         lbytes,         // cbColDef
         0,         // ibScale
         (VOID *)1,      // rgbValue
         0,         // cbValueMax
         &cbTextSize);      // pcbValue
   if ( (retcode != SQL_SUCCESS) &&
      (retcode != SQL_SUCCESS_WITH_INFO) ) {
         ProcessLogMessages(henv, hdbc1, hstmt1,
          "SQLBindParam hstmt1 Failed\n\n");
         return(9);
   }
   retcode = SQLExecDirect(hstmt1,
      "INSERT INTO EMP2 VALUES('JOHN SMITH', 27.3, ?)",
      SQL_NTS);
   if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) &&
        (retcode != SQL_NEED_DATA) ) {
         ProcessLogMessages(henv, hdbc1, hstmt1,
                 "SQLExecute Failed\n\n");
         return(9);
   }
   // 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) {
      // Send all but the final batch.
      while (lbytes > cbBatch) {
         SQLPutData(hstmt1, Data, cbBatch);
         lbytes -= cbBatch;
      }  // End while.
      // Put final batch.
      SQLPutData(hstmt1, Data, lbytes); 
   }
   else { // If not SQL_NEED_DATA, is some error.
         ProcessLogMessages(henv, hdbc1, hstmt1,
                 "SQLPutData Failed\n\n");
         return(9);
   }  // end if
   // Make final SQLParamData call to signal end of data.
   retcode = SQLParamData(hstmt1, &pParmID);
   if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) &&
        (retcode != SQL_NEED_DATA) ) {
         ProcessLogMessages(henv, hdbc1, hstmt1,
                "SQLParamData Failed\n\n");
         return(9);
   }
   /* Clean up. */
   SQLFreeStmt(hstmt1, SQL_DROP);
   SQLDisconnect(hdbc1);
   SQLFreeConnect(hdbc1);
   SQLFreeEnv(henv);
   return(0);
}
void ProcessLogMessages(HENV plm_henv, HDBC plm_hdbc,
                        HSTMT plm_hstmt, char *logstring)
{
   RETCODE   plm_retcode = SQL_SUCCESS;
   UCHAR   plm_szSqlState[MAXBUFLEN] = "",
               plm_szErrorMsg[MAXBUFLEN] = "";
   SDWORD   plm_pfNativeError = 0L;
   SWORD   plm_pcbErrorMsg = 0;

   printf(logstring);
   while (plm_retcode != SQL_NO_DATA_FOUND) {
      plm_retcode = SQLError(plm_henv, plm_hdbc,
                plm_hstmt, plm_szSqlState,
               &plm_pfNativeError,
                plm_szErrorMsg, MAXBUFLEN - 1,
               &plm_pcbErrorMsg);
      if (plm_retcode != SQL_NO_DATA_FOUND){
         printf("szSqlState = %s\n", plm_szSqlState);
         printf("pfNativeError = %d\n",
             plm_pfNativeError);
         printf("szErrorMsg = %s\n", plm_szErrorMsg);
         printf("pcbErrorMsg = %d\n\n",
            plm_pcbErrorMsg);
      } //end if
   } // end while
}

Getimage.c

// Sample reading SQL_LONGVARBINARY using SQLGetData.
// Tested with SQL Server 6.5 and 2.65 drivers.
// Assumes DSN has table:
//  CREATE TABLE EMP2 (NAME CHAR(30), AGE FLOAT, 
//           BigBin IMAGE)
#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 BUFFERSIZE   450
HENV   henv = SQL_NULL_HENV;
HDBC   hdbc1 = SQL_NULL_HDBC;
HSTMT   hstmt1 = SQL_NULL_HSTMT;
char   logstring[MAXBUFLEN] = "";
void   ProcessLogMessages(HENV plm_henv, HDBC plm_hdbc,
             HSTMT plm_hstmt, char *logstring);
int main()
{
   RETCODE retcode;
   // Authorization strings.
        UCHAR   szDSN[MAXDSN+1] = "ab65def",
      szUID[MAXUID+1] = "sa",
      szAuthStr[MAXAUTHSTR+1] = "password";
   SWORD   cntr;
   //SQLGetData variables.
   UCHAR   Data[BUFFERSIZE];
   SDWORD   cbBatch = (SDWORD)sizeof(Data)-1;
   SDWORD   cbBinSize;
   // Clear data array.
   for(cntr = 0; cntr < BUFFERSIZE; cntr++)
      Data[cntr] = 0x00;
    // Allocate the ODBC environment and save handle.
   retcode = SQLAllocEnv (&henv);

   // Allocate ODBC connection and connect.
   retcode = SQLAllocConnect(henv, &hdbc1);
   // Make the connection, then print the information messages.
            retcode = SQLConnect(hdbc1, szDSN, (SWORD)strlen(szDSN),
               szUID, (SWORD)strlen(szUID),
               szAuthStr,
               (SWORD)strlen(szAuthStr));
   if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) ) {
         ProcessLogMessages(henv, hdbc1, hstmt1,
                "SQLConnect() Failed\n\n");
         return(9);
   }
   else {
        ProcessLogMessages(henv, hdbc1, hstmt1,
                "\nConnect Successful\n\n");
   }
   // Allocate the statement handle.
            retcode = SQLAllocStmt(hdbc1,&hstmt1);
   // Execute the SELECT statement.
            retcode = SQLExecDirect(hstmt1,
         "SELECT BigBin FROM emp2", SQL_NTS);
   if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) ) {
         ProcessLogMessages(henv, hdbc1, hstmt1,
                "SQLExecDirect hstmt1 Failed\n\n");
         return(9);
   }
   // Get first row.
   retcode = SQLFetch(hstmt1);
   if ( (retcode != SQL_SUCCESS) &&
        (retcode != SQL_SUCCESS_WITH_INFO) ) {
         ProcessLogMessages(henv, hdbc1, hstmt1,
                "SQLFetch hstmt1 Failed\n\n");
         return(9);
   }
   // Get the SQL_LONG column. CbBatch has size of data chunk
   // the buffer can handle. Call SQLGetData until
   // SQL_NO_DATA_FOUND.  cbBinSize on each call has the
   // amount of data left to transfer.
   cntr = 1;
   do {
      retcode = SQLGetData(hstmt1,   // hstmt
         1,         // ipar
         SQL_C_BINARY,      // fCType
         Data,         // rgbValue
         cbBatch,      // cbValueMax
         &cbBinSize);      // pcbValue
      printf("GetData iteration %d, pcbValue = %d\n",
          cntr++, cbBinSize);
      if ( (retcode != SQL_SUCCESS) &&
                    (retcode != SQL_SUCCESS_WITH_INFO) &&
           retcode != SQL_NO_DATA_FOUND ){
            ProcessLogMessages(henv, hdbc1, hstmt1,
                                  "SQLGetData hstmt1 Failed\n\n");
            return(9);
      }
   } while (retcode != SQL_NO_DATA_FOUND);
   /* Clean up. */
   SQLFreeStmt(hstmt1, SQL_DROP);
   SQLDisconnect(hdbc1);
   SQLFreeConnect (hdbc1);
   SQLFreeEnv(henv);
   return(0);
}