Open Database Connectivity - 'ODBC'

[Note: to check GetFunctions stuff vs Gulutzan p935.. ;
Also InfoType in SQLGetInfo p 939.
then catalogs p 963. ]

ODBC - let's C!
ODBC functions in Detail

We know that there are many flavours of SQL, and that most databases (even big fancy commercial ones) are not completely SQL-92 compliant (despite their protestations to the contrary). In addition nearly all define their own proprietary extensions that do their best to lock you into a particular database. Wouldn't it be nice if we had some standardised way of interrogating these databases? Enter Open DataBase Connectivity (ODBC) and the closely related SQL Call-Level Interface (CLI)! In the following text we will be quite liberal in our use of the term ODBC, although strictly it should refer only to the Microsoft product.

ODBC nominally allows one to interrogate 'any' SQL database using 'any' package. This works by having an ODBC driver that sits between the database and the interrogator. The interrogator submits ODBC-standard questions, and obtains standardised replies. Needless to say, the database (DBMS or 'database management system') must understand the questions (once they've been "tweaked" by the ODBC driver), and the application must submit queries in standard ODBC format. In techspeak, the database is an "ODBC-compatible data source". Each data source has a name (or 'DSN').

ODBC version 1.0 was released by MicroSoft in 1992. They did a vaguely reasonable job in designing their standard, (we will defer criticism until later) and ODBC has been widely accepted and implemented for a host of different databases. The more recent version of ODBC, version 3.0 released by Micro$oft in 1998, has a number of extensions that are not necessarily compatible with the SQL standard, although this standard is largely contained in version 3.0. There are a few balls-ups, and, needless to say, if you want a copy of the ODBC software development kit to make your own drivers, you will have to pay MicroSoft! This doesn't prevent you from writing code that talks to established ODBC drivers. The bottom line - if you stick to functions common to both Microsoft ODBC and the SQL CLI specification, you should generally be fine.

A. Using ODBC with C

The following section assumes a working knowledge of C++ (If you only know C, then you can probably still hum the tune, as we've tried to avoid the more obnoxious characteristics of C++). We've chosen C because it is widely used to write programs that talk to ODBC. You might think that all one needs to do to get data out of a database is set up a connection, send off standard SQL queries, have these queries massaged by the ODBC driver into a format that the peculiar database understands, and then get back answers. Unfortunately, things are a bit more complex, because the answers also sometimes need to be 'massaged' into a format that is easily accessible to the interrogating program. In addition, there are frills. Let's look at some actual code - we will first view a simple example, then look at a more comprehensive example that deals with SQL statements like "SELECT *", after this examine something called prepared execution, and finally look briefly at error diagnostics, data sources, and some frills.

1. Simple C Code

We will take a stepwise approach to connecting to ODBC, interrogating a database, and closing off. But before we get started, take note of the following general comments, which should help you immensely.

  1. ODBC functions return an integer. You can look at this integer to see whether the function succeeded (value is SQL_SUCCESS), or didn't work too well. We will discuss fetching errors much later - but here's a list of some common errors, for the inquisitive!

  2. ODBC uses a slightly peculiar (but moderately sensible) method of talking about a string argument. It uses a pointer to the string, and then the very next argument is usually the length of the string. (We will generally highlight such usage in purple). If the string ends in ASCII NUL (hexadecimal zero), then instead of specifying the length, one can usually just say SQL_NTS which stands for "null-terminated string" (Yet another name for this is an ASCIIZ string. Note that some languages other than C don't support null-terminated strings, e.g. COBOL, and that there is a performance hit if you use SQL_NTS). You will also sometimes find that a third argument must be submitted in such circumstances - a pointer to a number where the actual length of the string will be stored! Look for the pattern:


    which refers to string buffer xxx, the buffer length yyy, and the true string length zzz.

  3. There are often multiple ways of doing the same thing in ODBC - a bit of a curse, actually! In addition, there is a wealth of often very similar data types. The C data types are peculiar to ODBC, for example instead of an integer data type, we refer to an SQL_INTEGER data type. You will encounter SQLINTEGER, SQLUINTEGER, SQLSMALLINT, SQLUSMALLINT, SQLLEN, (U in these refers to unsigned integers), as well as both SQLCHAR * and SQLPOINTER data types, written both with and without _underscores_ inside them. Don't let them scare you.
    {For the record, INTEGERs are 32 bit values, SMALLINTs are 16-bit, SQLPOINTERs are untyped, SQLREALs are single precision floating point, and SQLDOUBLEs are double precision floating point. SQLCHARs are strings of 8-bit bytes. }.

  4. To make things more complex, one may need to specify the C data type (for example, SQL_C_CHAR), as well as the SQL data type (for example, SQL_CHAR). Here's a table of data types, which you probably won't want to visit until later.

  5. As we'll discover in a moment, SQL makes extensive use of things called handles. The three common types are graced with the ugly labels SQLHENV, SQLHDBC, and SQLHSTMT. { All handles are actually stored in 32 bit integers. }.

To kick off, because we're writing in C, at the start we have to say things like:

#include <stdlib.h>
#include <stdio.h>
#include <odbc/sql.h>             // the STANDARD name is actually <sqlcli.h>
#include <odbc/sqlext.h>          // The sql.h and sqlext.h are required if
#include <odbc/sqltypes.h>        // you want all the MS functionality.

We then find out about handles..

1A. Getting a handle on things

  1. First, get a handle that allows you to talk to ODBC. (For the uninitiated, a handle is just a number that acts as a 'label' to tell ODBC that you are referring to a particular 'resource'). The handle is stored in a variable of type "SQLHENV", (which is just the alter ego of a 32 bit integer). You use SQLAllocHandle to get this handle. The format is
     SQLHENV  handleENVIR;     // Handle into ODBC environment
      call SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &handleENVIR); 
    The variable handleENVIR will then contain your much-desired environment handle!
    {The function SQLAllocHandle is a combination of several, older ODBC functions - SQLAllocConnect, SQLAllocEnv, and SQLAllocStmt, and was introduced into ODBC version 3}.

  2. Say which version of ODBC you want to use. You really do want version 3.0, so use SetEnvAttr thus:
    call SQLSetEnvAttr(handleENVIR, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);  //MS ODBC specific
    Note that you should not skip the above step when programming for MicroSoft ODBC!
    {The 'mirror image' of this boring little function is SQLGetEnvAttr - but you'll probably never use it}.

  3. Next, you need another handle, to deal with the actual database connection! Unsurprisingly, this handle is called a connection handle, stored in a variable of type "SQLHDBC". Try:
      SQLHDBC handleCONNECT;
      call SQLAllocHandle(SQL_HANDLE_DBC, handleENVIR, &handleCONNECT);

  4. ( At this stage, an option is to modify the connection, notably how long the connection takes to time-out. The function is SQLSetConnectAttr (..) but most of the MS ODBC options are non-standard, and the ISO standard is singularly depleted, rendering the function useless! )

  5. Next, connect to the database using:
     call SQLConnect(handleCONNECT, ServerName, SQL_NTS, User, SQL_NTS, Password, SQL_NTS);

    { You will need to know and specify the server and user names and password. Alternative, somewhat more complex connection options are SQLDriverConnect, and SQLBrowseConnect. We don't explore these at all as they are MS-specific}.

  6. You are now almost ready to perform an SQL query. Does it surprise you that you now need to get yet another handle - one for an SQL statement? Yep, again we say something along the lines of:
     SQLHSTMT handleSTMT;
      call SQLAllocHandle(SQL_HANDLE_STMT, handleCONNECT, &handleSTMT );

    A statement handle here is not just some sort of reference to an SQL statement, but has associated information about everything to do with the statement - sets of results created in response to processing of the statement, and other stuff besides. A better term than 'statement' would perhaps be a 'resource'. Statement handles are used by most SQL commands!

    (Although it's mildly confusing, we should here also say something about Descriptors (sometimes abbreviated to 'descs'). When you set up a statement handle, four descs are automagically created. They rejoice under the abbreviations:

    1. IRD or Implementation Row Descriptor
    2. ARD or Application Row Descriptor
    3. IPD or Implementation Parameter Descriptor
    4. APD or Application (you guessed it!) Parameter Descriptor.

    You can talk to these descs to find out about available result sets, and also how ODBC views these data. You may also use them to tell ODBC how to transfer data to the host database. The magic function that gives you the four descs is called SQLSetStmtAttr, but you should not look at it now, as it will only confuse you! We discuss descs in detail later ).

    1B. Talking to the Database

  7. How do we actually Perform a Query? We'll, let's assume our database contains a table called 'MyTable' with two columns called 'MyKey' and 'MyName'. MyKey is numeric, and MyName is a column of text strings. If we wish to submit the following query:
    "SELECT MyKey,MyName FROM MyTable ORDER BY MyKey;"

    How do we do the actual query? The 'best' way (knowing our columns) is to prepare things by binding data items for each column to variables of the same type before we actually do the query. Then each value simply pops into a variable of the correct type when we fetch a row from the database (In a moment we'll see how to fetch a row). Let's therefore define two new variables, thus:

       SQLINTEGER sqMyKey;
       SQLCHAR * sqMyName;                  // (Do we know length eg 256 chars?)
       sqMyName = new char [256];           // C++ style memory allocation!
       SQLINTEGER signal;                   // (see SQLBindCol documentation below!)

    Next, we use SQLBindCol to bind the variables to columns (column 1 is bound to sqMyKey, and so on):

       SQLBindCol(handleSTMT, 1, SQL_C_SLONG, &sqMyKey, sizeof(sqMyKey), &signal)
       SQLBindCol(handleSTMT, 2, SQL_C_CHAR, &sqMyName, 256, &signal)

    Finally, we execute our SQL statement:

      long fred;
      fred = SQLExecDirect(handleSTMT,
                           "SELECT MyKey,MyName FROM MyTable ORDER BY MyKey" ,

    See how we get the result of the SQLExecDirect function into a long integer fancifully called 'fred'. It's a good idea to examine the value in fred to see that our statement succeeded (Return values are listed in our documentation. Technically, we should perhaps have made fred a 16 bit SQLSMALLINT integer, as this is what was returned. Darn. I'm just so used to simply having everything a 32 bit integer)! Assuming the statement succeeded, then a result set will have been created. We are one small step away from viewing our results..

  8. Actually getting data! Enough mucking around. We simply use SQLFetch to obtain the first row of data, thus:
       long jane;
       jane = SQLFetch(handleSTMT);

    Now, if you examine the bound variables, they should contain the much-sought-after data from the database! You can carry on performing SQLFetch statements until you get back SQL_NO_DATA, at which point you know that you've run out of data rows. This is cool, but there's another way - you can use the SQLRowCount function to find out how many rows there are:

       SQLINTEGER howmanyrows;
       SQLRowCount(handleSTMT, &howmanyrows);

    Be very careful if you use this function. It would seem that some data sources will not return the number of rows available, before they are fetched. Ugly, isn't it?

    Note that you should do the following when you're finished with fetching your data:


    or the bogeyman will come out of the closet and eat you. Promise!

    1C. Closing Down

  9. How do we close things down? Easy! We end off the transaction using SQLEndTran, and deallocate the handles in reverse order compared with the way we allocated the handles. First we free the statement handle, then we disconnect from the database using SQLDisconnect, and then use SQLFreeHandle to free up all the other handles we allocated! We say:
    SQLFreeHandle(SQL_HANDLE_STMT, handleSTMT); 
    SQLFreeHandle(SQL_HANDLE_DBC, handleCONNECT); 
    SQLFreeHandle(SQL_HANDLE_ENV, handleENVIR); 

    Note that if there were results pending for handleSTMT in the above example (there aren't), then FreeHandle would result in the pending results being deleted without warning. In our trivial example, we used SQL_ROLLBACK for SQLEndTran, but could just as well have committed (after all, we did nothing to modify the database). We could, I suppose, even have left without an EndTran, but I wouldn't like to try this. Also note that submitting COMMIT or ROLLBACK as SQL statements rather than using SQLEndTran is frowned upon (with the dire warning that 'it will not be interoperable between DBMS products') !

    And that's really that. Well, almost.

2. A more comprehensive approach

What if we had said "SELECT * .." in our SQL statement? In other words, if we didn't know the number of columns that resulted from our query? There's a special function that addresses this problem - NumResultCols. We'd say something like:
long howmanycols;

SQLNumResultCols(handleSTMT, &howmanycols);
.. and we'd have our number. But be careful - for SQLNumResultCols only works after we have already invoked one of a limited number of statements. The relevant statements are SQLExecDirect, and two others that we'll encounter in a moment - SQLPrepare and SQLExecute. We now have a problem:

The answer is that we can get the data directly into a variable without any prior binding! The function SQLGetData does just this:

SQLGetData(handleSTMT, 1, SQL_C_SLONG, &sqMyKey, sizeof(sqMyKey), &signal);

But we still have a problem! For we don't yet know the type of datum contained in a column - in the above example, we can't really be sure that column 1 is of type SQL_C_SLONG. If we know nothing more than the column number, how on earth do we find out this 'target type'? Fortunately, there's a function that gets us information about a column before we call upon SQLGetData. It's called SQLColAttribute. Let's use it to find the data type of a column:

   SQLCHAR * sqMyName;        

   sqMyName = new char [256];

   SQLINTEGER signal;         
   long coltype;
   long fred;
   long howmanycols;

   /* here open handles etc */
   fred = SQLExecDirect(handleSTMT,
                       "SELECT * FROM MyTable" ,

   SQLNumResultCols(handleSTMT, &howmanycols);

   while (howmanycols > 0)
     {   SQLColAttribute (handleSTMT, howmanycols, SQL_DESC_CONCISE_TYPE,
                          SQL_NULL, 0, SQL_NULL, &coltype);
          /* .. more code here .. */
          howmanycols --;

This might allow us to specify the column type in our GetData statement. Something along the lines of..

      SQLGetData(handleSTMT, howmanycols, coltype, &sqMyKey, sizeof(sqMyKey), &signal);

Can you see why this code is unsatisfactory? Yes, the variable sqMyKey is of type integer, which would really screw us around if coltype referred to a string. So you need some sort of switch .. case statement that selects between different types and then handles them appropriately. There's one more problem, and that is that different character strings (for example) differ in size, so one really should also find out the maximum size of the character string that will (in the above example) be slotted into sqMyName! One way of finding this is to call SQLColAttribute again with the third argument set at SQL_DESC_LENGTH, but note that this is specific to ODBC version 3(+).

There is another function that is similar to SQLColAttribute - it's called SQLDescribeCol. It too will provide you with the column name, type, size, 'nullability' (are nulls allowed?) and number of decimal digits. In fact the syntax is a little less arcane than that of SQLColAttribute. We leave you to explore this function on your own! Perhaps you might wish to use it in preference to SQLCollAttribute, especially if you're using ODBC drivers below version 3.

3. A third way - Prepared Execution

We doubt whether Micro$oft can ever be accused of simplicity or a minimal approach, and ODBC is no exception. There is yet another way of submitting SQL statements via ODBC. For example, let's say that instead of a query, we wanted to insert a new row into our table called 'MyTable', thus:
  "INSERT INTO MyTable (MyID, MyName) VALUES (1234, 'Mr Walrus');"

First we use SQLPrepare:

  long fred;

  fred = SQLPrepare (handleSTMT,
                     "INSERT INTO MyTable (MyID, MyName) VALUES (1234, 'Mr Walrus');",

The interesting wrinkle is that here one can include things called parameter markers within the SQL statement.


  "INSERT INTO MyTable (MyID, MyName) VALUES (?, ?);"
If you're using a grown up language like Perl, there's also no reason why you cannot take such a statement and replace the first question mark with 1234, and the second with 'Mr Walrus'. ODBC allows us to go a step further - we bind the first question mark to an appropriate variable, and likewise with the second. Then we can allocate different values to the variables, repeatedly submit the SQL statement, and each time a 'different' statement (with different parameter values) can be executed!

The "?"s are known as parameter markers. Once you have submitted such a statement using SQLPrepare, you bind the parameter markers to the appropriate variables using yet another new function, SQLBindParameter:

    SQLBindParameter (..)

The main advantage of such prepared statements is that you save on conversion of non-text data types - if we were to take the integer 1234, turn it into a string ("1234"), substitute this value for a marker, submit the statement, and then the database had to go through the process of converting back to an integer, time would be lost.

With the above prepared statement, once you've bound your parameter markers to variables, you can easily insert a row by altering the relevant variables, and calling the function SQLExecute:

    SQLExecute (..

Note that once you have SQLPrepared a statement, then the statement is bound to the statement handle. You can repeatedly execute the same statement by calls to SQLExecute, but the binding to the statement will be lost when you use the same handle with a call to another SQLPrepare statement, a call to SQLExecDirect, or you free the statement with yet another function we haven't discussed, SQLFreeStmt.

Note well that once you COMMIT using SQLEndTran, this may screw around with such prepared statements. The Microsoft documentation is rather vague on this point.

There are OTHER PROBLEMS with SQLBindParameter, especially if you Prepare and then bind (as above) - it may even be better to call SQLBindParameter and then say SQLExecDirect! There is at least one database {no names mentioned?} which evaluates input parameters during SQLPrepare, consequently screwing things up if you then use SQLBindParameter! Even worse, if you didn't call SQLFreeStmt (..SQL_RESET_PARAMS), then previous bindings may be in force! (Aargh)!

Okay, time to come clean! In fact, SQLExecDirect is just a Prepare and an Execute rolled into one for convenience! The idea is that during the preparation phase, the SQL statement is validated and then bound (etc); after execution, thing come to an end unless a query was performed. If a query provided some results, then a cursor is associated with the result set, and becomes available by talking to the statement handle.

Some SQL statements cannot actually be prepared. Here they are:

SQL statement classes
Sheep (preparable) Goats (not)!

4. Cursors

Implicit in the execution of an SQL query via ODBC is the opening of a cursor. The cursor tells ODBC where the first row of the result set is. There are six cursor functions:

A cursor is also closed when SQLEndTran is invoked.

Getting and setting a cursor name is only really required if you do fancy things like using positioned UPDATE or DELETE statements. (These are statements like "UPDATE .. WHERE CURRENT OF cursorname", and "DELETE FROM .. WHERE CURRENT OF cursorname").

{Perhaps have note on holdable and sensitive cursors - see Guluzan and Pelzer p 817-8}.

5. Descriptors - ARD, IRD, APD, IPD

Recall the descriptors ('descs') associated with a statement handle?
  1. IRD or Implementation Row Descriptor (How are rows seen in the actual implementation of the database?)
  2. ARD or Application Row Descriptor (How are rows seen by the [ODBC] application?)
  3. IPD or Implementation Parameter Descriptor (Parameters as seen by the database)
  4. APD or Application (you guessed it!) Parameter Descriptor. (Parameters as seen by ODBC)

Also remember how, with prepared execution, we used parameter markers ( "?"s ) within SQL statements. We then associated such parameter markers with local C variables.

Well descs are used to represent such parameter marker trickery, and a lot more besides. A considerable insight into the inner workings of ODBC can be obtained by having a peek at values contained in the descs. A desc has both a header and Item Descriptor Areas (IDAs). The latter are far more complex. Here are the details of both, but first take note that the single function SQLGetDescField can be used to read most of the fields discussed in the following sections (over and above the other functions listed below)!

  1. Desc Headers

    There are five fields in the desc header - of these, only the first-mentioned is important:

    1. SQL_DESC_COUNT: This should contain the number of item descriptor areas. (For example, in an Implementation Row Descriptor header, this count might be the number of columns, as set up by an SQLPrepare command. You can check whether this automatic 'population' of the desc occurs - use GetConnectAttr with SQL_ATTR_AUTO_IPD to see if SQLPrepare can in fact set this value to the numer of IDAs).

      Note that for each type of desc, SQL_DESC_COUNT has a different meaning, and is affected by different functions.

      1. IRD: How many columns are there (as seen by the database)? Set by SQLPrepare, interrogated by SQLNumResultCols.
      2. ARD: How many columns are there (ODBC will tell the database)! Set by SQLBindCol or SQLSetDescRec, and used by SQLGetData.
      3. IPD: How does the database see various parameters? Set by the database itself (automatic population of fields during SQLPrepare), or by SQLBindParameter or SQLSetDescRec. Used by SQLExecute.
      4. APD: Tell database how to handle data provided to it by ODBC. Set by SQLBindParameter, SQLSetDescRec; and used by SQLExecute, SQLGetDescRec, SQLGetParamData, or SQLParamData.

    2. SQL_DESC_ALLOC_TYPE: This little field simply tells us whether the desc was created automatically (when the statement handle was made) or explicitly { using SQLAllocHandle(SQL_HANDLE_DESC, ..) }.

    3. SQL_DESC_DYNAMIC_FUNCTION: This obscure field (not seen in ODBC) contains a text string with a copy of the prepared statement - usually the name of an SQL function. Who knows why?

    4. SQL_DESC_DYNAMIC_FUNCTION_CODE: This contains a numeric code that corresponds to the previous function name. Obscure.

    5. SQL_DESC_KEY_TYPE: Another non-ODBC component which tells you about the relationship between the columns in a select statement and the keys in the table. Curiouser and curiouser.

  2. Item Descriptor Areas (IDAs)

    There are numerous fields in the IDA (28 in all). To totally confuse you, they are also referred to as "detail records" or "fields of the descriptor record" (ODBCspeak). We will just call them IDA fields. The important fields are listed below. Concentrate on the first two, which are most noteworthy! Multiple fields can be queried using SQLGetDescRec, or single fields with the more general SQLGetDescField

    1. : SQL_DESC_DATA_POINTER. This IDA field is only of significance in the APD and ARD - it talks about a local variable in the application program, pointing to an input parameter (in the APD), or a target for a column result (in the ARD). SQLSetDescRec can set a value in either APD or ARD, while SQLBindParameter can set only an APD field of this type.
      SQLExecute uses this field in the APD, and SQLGetData uses the ARD field.

    2. : SQL_DESC_TYPE gives the item's data type (click on the reference for a list). Subtypes of the interval type are stored in a separate field, the SQL_DESC_DATETIME_INTERVAL_CODE. The ARD and IRD type values may be read by SQLGetData, but only the IRD value is obtainable using SQLDescribeCol. SQLSetDescRec can be used to set the field in ARD, APD and IPD (but not IRD which can only be altered by SQLPrepare). In addition, SQLBindCol sets the value in the ARD, and SQLBindParameter sets the value in the APD or IPD.

    3. : SQL_DESC_DATETIME_INTERVAL_CODE. Useful if SQL_DESC_TYPE is SLQ_DATETIME or SQL_INTERVAL. Contains one of eighteen different sub-types.

    4. : SQL_DESC_DATETIME_INTERVAL_PRECISION. Precision of the most significant component of a DATETIME or INTERVAL.


    6. : SQL_DESC_LENGTH. The character length of string data, the bit length of bit strings, the octet length of a BLOB, and the 'position length' (number of digits + fillers) for a time. In ISO SQL, you may not alter this value, but can read it for an IRD using SQLDescribeCol. {ANSI allows alterations!} SQLExecute may use the value in the APD and IPD.

    7. : SQL_DESC_NULLABLE. Says whether the field can contain null - SQLDescribeCol allows us to ask whether this is the case for the IRD.

    8. : SQL_DESC_OCTET_LENGTH. The maximum octet (8 bit byte) length for any data type, with the [possible] exception of datetime and interval. You can check the value for all four types of descriptor using SQLGetDescRec. To set it for all 4 use SQLSetDescRec; or use SQLBindCol (ARD), SQLPrepare (IRD), SQLBindParameter (APD and IPD).

    9. : SQL_DESC_OCTET_LENGTH_POINTER. The address of a length (in octets i.e. 8-bit bytes). May be the actual length of a datum (VARCHAR, BIT VARYING, BLOB), OR the maximum length (all other data types). Does not include a terminal zero for ASCIIZ strings. It's not a good idea to fiddle with the value, even if your application allows you to (using SQLSetDescRec). SQLExecute may use the value in the APD.

    10. : SQL_DESC_PRECISION. This is important for numeric data types (total number of digits, or number of bits in the mantissa), and times (fractional seconds precision). SQLGetData reads the ARD and IRD; SQLExecute the IPD, and you can view the IRD value using SQLDescribeCol.

    11. : SQL_DESC_SCALE. Number of digits after the decimal point in decimal and numeric data types. Otherwise zero.

    12. : There are many others. For the record, here they are - ** indicates that the field is peculiar (and we mean peculiar) to ANSI, and * designates fields found in SQL3 but not in ODBC:

[The whole thing needs a rewrite. Need more explicit description of descriptors, their implementation, and usage.]

6. A note on fetching errors!

Note that practically every ODBC function returns a sixteen bit integer value, commonly referred to as SQLRETURN. (See our example above, where we fetched the result of SQLExecDirect into an integer called 'fred'). It is good practice (but ugly coding) to fetch and check on the result for every command. This on its own is however not good enough. If the value of 'fred' is not SQL_SUCCESS, then we need to look further. (The other five possible values for SQLRETURN are SQL_INVALID_HANDLE, SQL_ERROR, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, and SQL_NO_DATA: the corresponding numeric values are -2, -1, 1, 99, and 100). Every handle has associated with it a Diagnostics Area which contains 'error' (and other) information.

We use the function SQLGetDiagRec to get diagnostic information. SQLGetDiagRec only provides you with the most commonly interrogated fields of the diagnostics area. There are others that can only be read by the more comprehensive function SQLGetDiagField. Note that each diagnostics area has nine header fields, and 28 status records. To make things even more complex, there may be multiple copies of the status records! (The header contains the number of such copies in a special field called SQL_DIAG_NUMBER).

The nine header fields are:

  1. SQL_DIAG_RETURNCODE (the last code returned by a function - e.g. the value stored in 'fred' in our example)
  2. SQL_DIAG_NUMBER (as above)
  3. SQL_DIAG_ROW_COUNT (remarkably, the value returned by SQLRowCount)
  4. SQL_DIAG_DYNAMIC_FUNCTION (a string containing the name of the last SQL function Executed)
  5. SQL_DIAG_DYNAMIC_FUNCTION_CODE (the corresponding code - see Gulutzan pp891-4 for a full list)
  6. SQL_DIAG_MORE (were there more diagnostic records that couldn't fit into the diagnostic area?)
  7. SQL_DIAG_TRANSACTIONS_COMMITTED (number of transactions committed)
  8. SQL_DIAG_TRANSACTIONS_ROLLED_BACK (number of transactions rolled back)
  9. SQL_DIAG_TRANSACTION_ACTIVE (still busy with a transaction - a cursor is open or the database is waiting for a deferred parameter)

The status records (up to SQL_DIAG_NUMBER of them, each with 28 fields) are also known as Descriptor Records or 'conditional information items'. We've lumped some together:

  1. SQL_DIAG_SQLSTATE - the five-character 'SQLSTATE'. There are many options. Closely associated are SQL_DIAG_CLASS_ORIGIN (authority defining the first two letters of SQLSTATE), and SQL_DIAG_SUBCLASS_ORIGIN (who defined the last 3 letters)!
  2. SQL_DIAG_NATIVE (database-defined error code)
  3. SQL_DIAG_MESSAGE_TEXT (useful rude message from database, and SQL_DIAG_MESSAGE_LENGTH contains the character length of this string. SQL_DIAG_MESSAGE_OCTET_LENGTH is identical unless the message is in unicode. )
  4. SQL_DIAG_SERVER_NAME (which server returned an error. Most useful with failed CONNECT, DISCONNECT or SET CONNECTION statements).
  5. SQL_DIAG_CONDITION_NUMBER is simply the number of the current status record.
  7. SQL_DIAG_CATALOG_NAME, SQL_DIAG_SCHEMA_NAME, SQL_DIAG_TABLE_NAME, SQL_DIAG_COLUMN_NAME are all to do with 'where the problem occurred'. You can also identify an offending cursor with SQL_DIAG_CURSOR_NAME. SQL_DIAG_CONDITION_NAME refers to user-defined exceptions. (A similar _PARAMETER_NAME is poorly characterised). Errors related to offending routines include SQL_DIAG_ROUTINE_CATALOG, SQL_DIAG_ROUTINE_SCHEMA, SQL_DIAG_ROUTINE_NAME and SQL_DIAG_SPECIFIC NAME.
  8. Trigger-related problems are diagnosed using SQL_DIAG_TRIGGER_CATALOG, .._SCHEMA and _NAME.

Note that the error diagnostic functions don't themselves post error diagnostic information! The only thing you get back is a return code.

6.1 Easy errors

How do we really screw things up? You'll deduce that from the complexity of ODBC and the SQL CLI, there is a near-infinite number of ways. But to really bollox things, try:


[Move the table from SQLExecDirect to here, augment it]

7. Exploring SQLDataSources

In ODBC you can actually find out what data sources are available to be interrogated. Use SQLDataSources to do so. In a very similar fashion, you can obtain a list of drivers, and their capabilities by talking to SQLDrivers. One can also acquire a vast array of information about specific characteristics of data sources and drivers. The magic function is SQLGetInfo - not for the faint-hearted.

You can even find out which ODBC functions are supported (use SQLGetFunctions), and data types supported (SQLGetTypeInfo).

8. Frills, bells, ..

In this text we won't discuss:

Other topics that also might have been covered (but aren't) are asynchrony, shared environments (ODBC-specific), catalogs & schemas.

[Perhaps also SQL_C_DEFAULT option with binding numbers.. Gulutzan p 863]

What about mysterious SQLOpenTable {? v2.0}, SQLBindKey {? v2.0}, ?

B. A Lot More Detail

ODBC can be very complex. Microsoft (who played a major role in defining ODBC) define about sixty ODBC functions, and documentation on these runs into hundreds of pages. Fortunately, you will probably never need most of the options provided. Above, we have outlined perhaps the most important options, and even there you can see the redundancy! The full ISO specification is contained in "ISO/IEC 9075-3:1995" (Part three of the SQL standard, the call-level interface section, which is very similar to something called the "X/Open SQL CLI"). It's rather large. Here (just for the record) are the functions defined by both MicroSoft (many, but not all, are from the ISO/IEC specification), and ISO 9075-3. 'Old-fashioned' functions are greyed out, a zero0 superscript indicates an initial (v1.0) API conformance level of zero, likewise for 1 and 2. Version 3.0 functions are in red. An asterisk* indicates a function that doesn't belong to ISO, but is defined in X/Open, and functions in institutional green are peculiar to Microsoft ODBC (avoid them)! Note that functions in grey are not actually deprecated by ISO, so you can still use them, although Microsoft is pretty hard on them.

ODBC functions
Function What it does
SQLAllocConnect 0 replaced by SQLAllocHandle Takes 2 arguments: SQLHENV and SQLHDBC.
SQLAllocEnv 0 replaced by SQLAllocHandle Accepts a single argument - SQLHENV *
SQLAllocHandle Allocate one of a variety of handles - make sure that you're working with ODBC v3 drivers, or calls to this function will have to be replaced by the relevant version 1 calls (SQLAllocConnect, ..Env and ..Stmt). The format is:
SQLAllocHandle(SQLSMALLINT HandleType, SQLINTEGER InputHandle, SQLINTEGER FAR *OutputHandle);

The handle types are:

  1. SQL_HANDLE_ENV {for an environment handle, giving access to global ODBC information. The second argument of the function must be SQL_NULL_HANDLE.}
  2. SQL_HANDLE_DBC {for a database connection handle - you must provide a valid ENV handle as the second argument!}
  3. SQL_HANDLE_STMT {for a statement handle, which gives access to 'statement information'. The second argument here must be a connection handle.}
  4. SQL_HANDLE_DESC {to do with complex things called explicitly allocated descriptors ('user descs') which are not very important. You can ignore them. One thing one can do is to copy an automatically allocated desc into one of these user descs using SQLCopyDesc}
{MS ODBC returns various values when SQLAllocHandle is called - SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, or SQL_ERROR. If the value SQL_ERROR is returned from a call specifying anything other than SQL_HANDLE_ENV, then there is a sneaky way of finding out what went wrong. You simply take the second argument and submit it to SQLGetDiagRec, and - voila - lots of error messages! If however you specified SQL_HANDLE_ENV, then the same strategy won't of course work. Here, if allocation of the output handle failed totally, then the value SQL_NULL_HENV will be put into the SQLHENV variable. }
SQLAllocStmt 0 replaced by SQLAllocHandle
Arguments: SQLAllocStmt(SQLHDBC ConnectionHandle, SQLHSTMT FAR *StatementHandle);
SQLBindCol 0 Bind a particular column in a table to a variable, so that when you peform a query on a particular row, the value of the record in the specified column is transferred to the variable. The syntax is:
SQLBindCol( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLSMALLINT TargetType, SQLPOINTER TargetValuePtr, SQLINTEGER BufferLength, SQLLEN * StrLen_or_Ind);

{ Of the six arguments, (1) the statement handle is self-explanatory. (2)The column number would appear to be a little unfortunate (one would think that in true SQL, numbering a column would be taboo) but in fact, the number refers to the result of the query. You know the columns you are obtaining in the query, so you know which column is what! Note that column numbering starts at one, not zero (In fact, in ODBC one can use zero, for bookmarks, but this is arcane rubbish that you should ignore).

(3)There is a long list of possible target types - here are several (with the most important, we think, in bold face). Note that the native SQL specification varies from database to database. Also take note that things are even more complex, for there are also complex ('verbose') combinations of a data type and subtype, for certain data types (those beginning in SQL_INTERVAL_.., as well as dates and times. Here's a list).

Data types
SQL type identifier C type native SQL specification
SQL_NUMERIC(?) {usually cast to char!}NUMERIC(p,s)
SQL_FLOAT(?) {if p<23 float, else double} FLOAT(p)
SQL_INTERVAL (.._ there's a whole bunch of these in ODBC)

There are many, many target types. Which you use obviously depends on the database you are interrogating - it's clearly best if you have control over the latter, and limited the number of data types when you designed it! (For example, it's silly to have complex date and time structures, when these can easily be represented by single (Julian) numbers, as astronomers have done for years). Note that the SQLBIGINT is a 64-bit signed number, and that the date structure is: struct tagDATE_STRUCT { SQLSMALLINT year; SQLUSMALLINT month; SQLUSMALLINT day; } DATE_STRUCT;

(4) Next (did we lose you there?) you specify TargetValuePtr which is simply the actual variable you are binding to,and after this (5) you specify the size of the variable bound to (in fact, for fixed-length variables, this is often ignored, but best play it safe), and finally
(6) the confusing StrLen_or_Ind. This last variable is a bugger - the easy thing to do is simply provide a variable where a number can be stored, and never look at it again! (The really fine print is that this variable can be used for a variety of purposes. The value may be written by SQLFetch, SQLFetchScroll, SQLBulkOperations, and SQLSetPos. SQLBulkOperations may under certain circumstances actually read the value in this variable).

You may wish to look at our example, which should make things clearer!

Note that what SQLBindCol is actually doing is setting fields in an ARD. All the functions of SQLBindCol can be replaced by calls to SQLSetDescRec, or multiple calls to SQLSetDescField.

SQLBindParameter Binds a buffer to a parameter marker in a prepared SQL statement. In ODBC v 1.0, the function was SQLSetParam, which has been replaced by this function. The format is:
SQLBindParameter( SQLHSTMT StatementHandle, SQLUSMALLINT ParameterNumber, SQLSMALLINT InputOutputType, SQLSMALLINT ValueType, SQLSMALLINT ParameterType, SQLUINTEGER ColumnSize, SQLSMALLINT DecimalDigits, SQLPOINTER ParameterValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StrLen_or_IndPtr);

This complex function is often used to transfer data to the database. The parameter value pointer is the (deferred) variable which will be bound, and the buffer length says how big this variable is. (The StrLen_or_IndPtr is more fiddling, similar to that for SQLBindCol). Of more interest are InputOutputType, and the following two types. InputOutputType merely specifies whether the variable to be bound will be used for input, output, or both - SQL_PARAM_MODE_IN, SQL_PARAM_MODE_OUT, SQL_PARAM_MODE_INOUT (or, in ODBC: SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT). The value type specifies the C data type of the parameter (e.g. SQL_C_CHAR),and the parameter type specifies the SQL data type [click on the reference to view the same table]. ColumnSize and DecimalDigits depend on the type of data column, and are likely to sew much confusion.

{The function usually affects the IPD and APD associated with the statement handle. ParameterNumber thus refers to a particular IDA. Gulutzan & Pelzer go into great detail about the specific effects of SQLBindParameter (p869..) }.

SQLBrowseConnect2 Sequentially invoke this command to progressively find out how to connect through various levels until a full connection is established. If you're really enthusiastic, get the MS specification and compare this with SQLDriverConnect and SQLConnect.
SQLBulkOperations (ODBC v3. Bookmark-related bulk insertions, etc)
SQLCancel 0Woops. Cancel an SQL statement!
Format: SQLCancel(SQLHSTMT StatementHandle);
SQLCloseCursor Close the cursor associated with its single argument - a statement handle. You must call this function when you're done with a data set, or SQLPrepare will fail when you try and re-use the statement handle!

{The following functions automatically close a cursor: SQLEndTran, SQLCancel, SQLFreeHandle, SQLMoreResults, and SQLFreeStmt (with the SQL_CLOSE option)}.

(NB ODBC & ISO differ!)
Describe attributes of a column (of result set). The format is:
SQLColAttribute ( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLUSMALLINT FieldIdentifier, SQLPOINTER CharacterAttributePtr, SQLSMALLINT BufferLength, SQLSMALLINT * StringLengthPtr, SQLPOINTER NumericAttributePtr);
The statement handle and column number are self-explanatory. The field identifier says what column attribute you want (see below), and the next entry says where the function should write an answer string. 'BufferLength' says how big this answer buffer is, and StringLengthPtr tells you the total number of characters that are available (useful if you provided too small a buffer). Note that character information is returned in the CharacterAttributePtr, and that numeric information is put into the last argument, the NumericAttributePtr!

What this function actually does is get one field of an IRD. An SQL "SELECT" statement must have been either prepared or executed to populate the IRD. But note that with some databases, SQLColAttribute will NOT reliably populate the IRD after an SQLPrepare statement, so don't rely on this! The same stricture applies to SQLDescribeCol and even SQLGetDescField and SQLGetDescRec. Of interest is that the SQLGetDescField function gives you exactly the same information as SQLColAttribute!

{ Consult our section on descriptors for the SQL3 standard field identifiers, but here are the identifiers for ODBC. [NuA] indicates that the result is put into NumericAttributePtr - otherwise it's a string written to CharacterAttributePtr. ODBC does its own merry thing here. Of particular interest is that, once an ODBC cursor is closed, the IRD is invalidated, which does not happen in SQL3. We would avoid most of the following.

First, for ODBC v1:
SQL_DESC_AUTO_UNIQUE_VALUE (is the field auto-incrementing? A bad idea! [NuA] value is SQL_TRUE or SQL_FALSE)
SQL_DESC_CASE_SENSITIVE (is column 'case sensitive' - SQL_TRUE or SQL_FALSE) [NuA]
SQL_DESC_CONCISE_TYPE ( e.g. SQL_TYPE_TIME, etc. We have a full list below)
SQL_DESC_COUNT (how many columns are there in the result set? Compare with SQLNumResultCols) [NuA]
SQL_DESC_DISPLAY_SIZE (maximum character width needed to display column data) [NuA]
SQL_DESC_FIXED_PREC_SCALE (SQL_TRUE only if column is fixed precision, nonzero scale specific to the data source) [NuA]
SQL_DESC_SEARCHABLE (can column be used in a "WHERE" clause? May be SQL_PRED_NONE, .._CHAR (only use with LIKE), .._BASIC (never use with LIKE), or SQL_PRED_SEARCHABLE (use with any comparison)) [NuA]

SQL_DESC_TYPE_NAME (character string giving type name. Do NOT use, as this is dependent on the data-source eg "VARCHAR2")
SQL_DESC_UNSIGNED (SQL_TRUE iff an unsigned integer) [NuA]

.. version 2:
SQL_DESC_CATALOG_NAME (name of catalog of relevant table. Hmm. ver 2.)
SQL_DESC_LABEL (column label, may differ from column name e.g. be an alias)
SQL_DESC_SCHEMA_NAME (schema of table)
SQL_DESC_TABLE_NAME (the table name)

.. and finally, version 3:
SQL_DESC_BASE_COLUMN_NAME (the name of the column in the result set)
SQL_DESC_BASE_TABLE_NAME (name of table containing column)
SQL_DESC_LENGTH (maximum character length for fixed-length data type, or actual character length for variable length data type) [NuA]
SQL_DESC_LITERAL_PREFIX (up to 128 characters that are recognized as a 'literal prefix')
SQL_DESC_LITERAL_SUFFIX (similar to the prefix)
SQL_DESC_LOCAL_TYPE_NAME ((a 'localized name'))
SQL_DESC_NAME (column alias, or if not present, column name)
SQL_DESC_NULLABLE (SQL_NULLABLE or SQL_NO_NULLS, depending on whether column can take on a null value or not. Avoid NULLs!)
SQL_DESC_NUM_PREX_RADIX(Returns 2 or 10 [NuA] - is SQL_DESC_PRECISION base 2 or base 10?)
SQL_DESC_OCTET_LENGTH (Length in bytes of character or binary string) [NuA]
SQL_DESC_PRECISION (precision of number; if time, precision of seconds component) [NuA]
SQL_DESC_SCALE (scale of numeric data type) [NuA]
SQL_DESC_TYPE (SQL data type - e.g. SQL_INTEGER. NB with datetime and interval data you get only the verbose data type) [NuA]
SQL_DESC_UNNAMED (Does the field have a name? SQL_NAMED or SQL_UNNAMED is returned [NuA])
(The above are ODBC options. For SQL3 see our section on descriptors).

Concise/Verbose Data types. Note that for some types of variable, ODBC specifies both concise data type names and 'verbose' names. A list of these concise data types, followed in brackets by the 'verbose' type and subtype follows:

(Here's a long list of other data types).

SQLColAttributes Obsolete. Use the very similar SQLColattribute
SQLColumnPrivileges2 (List columns and associated privileges)
Format: SQLColumnPrivileges(SQLHSTMT StatementHandle, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *TableName, SQLSMALLINT NameLength3, SQLCHAR FAR *ColumnName, SQLSMALLINT NameLength4 );
SQLColumns1* List the column names for the specified tables. The syntax appears fairly complex:
SQLColumns( SQLHSTMT StatementHandle,
SQLCHAR * CatalogName, SQLSMALLINT NameLength1,
SQLCHAR * SchemaName, SQLSMALLINT NameLength2,
SQLCHAR * TableName, SQLSMALLINT NameLength3,
SQLCHAR * ColumnName, SQLSMALLINT NameLength4);
but the arguments merely specify search patterns for name and size of catalog, schema, table and columns. (Smart users can tweak the comparisons performed by setting SQL_ATTR_METADATA_ID to true or false, using SQLSetConnectAttr).

{ Note how SQLColumns returns data - as a data set of results associated with the handle (similar to SQLGetTypeInfo)! The four columns returned are: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION. Be careful, for ORDINAL_POSITION will not reliably be performed by ODBC drivers under version 3, and anyway, it's always a silly idea to rely on the ordinality of a column in SQL. Don't assume that e.g. a "SELECT *" statement will return the columns in the same order as SQLColumns does! }.

SQLConnect 0 Connect to a driver (and thence of course, to a data source). The format is moderately complex:
SQLConnect( SQLHDBC ConnectionHandle,
SQLCHAR * ServerName, SQLSMALLINT NameLength1,
SQLCHAR * UserName, SQLSMALLINT NameLength2,
SQLCHAR * Authentication, SQLSMALLINT NameLength3);

where the ConnectionHandle is self-explanatory. You must know the ServerName (and can work out the length of the name and put it into NameLength1), your user name and its length, a password ('authentication') and also its length. Then you're away. That is, unless you get back SQL_ERROR or SQL_INVALID_HANDLE.

{If the latter happened, as usual one can interrogate ODBC using SQLGetDiagRec. A wide variety of errors is possible, including driver specific errors (01000), bad ValuePtr (01S02), 08001 (can't make connection), 08002 (connection name in use), 08004 (server rejected connection - password could be a dud!), 08S01 (comms link failure), 28000 (bad username or password), HY000 (vague error, try looking at SQLGetDiagRec *MessageText buffer), HY001 (memory allocation failed), HY013 (memory management screwed up), HY090 (bad string or buffer length), HYT00 (timeout before connection), HYT01 (timeout before response), IM001 (function not supported), IM002 (data source not found), IM003 (can't connect to driver), IM004 (bad SQL_HANDLE_ENV - you forgot to set it!), IM005 (bad SQL_HANDLE_DBC), IM006 (SQLSetConnectAttr failed), IM009 (connection to a specified dynamic link library that does translation failed horribly), IM010 (data source name was too long). }.

Note that you should not submit the SQL command "CONNECT" via the CLI. (And likewise for DISCONNECT).

SQLCopyDesc Copy information from one descriptor handle to another! See SQLAllocHandle You could even copy an IRD to an ARD, 'making sure' that all the ARD fields have the 'correct' values that the database wants (After all, the database should 'automagically' set up the IRD, shouldn't it?)
Format: SQLRETURN SQL_API SQLCopyDesc(SQLHDESC SourceDescHandle, SQLHDESC TargetDescHandle);
SQLDataSources2 Get list of available data resources. The format is
SQLDataSources( SQLHENV EnvironmentHandle, SQLUSMALLINT Direction, SQLCHAR * ServerName, SQLSMALLINT BufferLength1,
SQLSMALLINT * NameLength1Ptr, SQLCHAR * Description, SQLSMALLINT BufferLength2, SQLSMALLINT * NameLength2Ptr);

The 'Direction' refers to whether you want to fetch information on the first driver manager (SQL_FETCH_FIRST), or SQL_FETCH_NEXT, (or possibly SQL_FETCH_FIRST_USER, or SQL_FETCH_FIRST_SYSTEM, which limits subsequent 'next' fetches to user or system DSNs respectively). Results are fetched into ServerName (the data source name), and Description (a description of the driver associated with the data source). Note that after repeated calls specifying SQL_FETCH_NEXT, you will eventually get back SQL_NO_DATA when you run out of data source names (a subsequent ..FETCH_NEXT will wrap around to the start again)!

SQLDescribeCol0 Describe a column (of a result set) - get the column name, type, size, decimal digits, and nullability. Why, you might ask yourself, do we have SQLDescribeCol, SQLColAttribute and SQLGetDescField? Do yourself a favour, and compare the three, noting in particular the strictures regarding calling these functions after SQLPrepare but before SQLExecute!

The format is:
SQLDescribeCol( SQLHSTMT StatementHandle, SQLSMALLINT ColumnNumber, SQLCHAR * ColumnName, SQLSMALLINT BufferLength, SQLSMALLINT * NameLengthPtr, SQLSMALLINT * DataTypePtr, SQLUINTEGER * ColumnSizePtr, SQLSMALLINT * DecimalDigitsPtr, SQLSMALLINT * NullablePtr);

You must provide output buffers for the column name (and the length of this buffer), as well as an integer where the function can store the actual length of the name. In addition, provide an integer buffer for the data type, and three more fields for column size, number of decimal digits, and whether the field is nullable. {Also take a peek at SQLPrepare!} Do NOT provide NULL pointers for unwanted fields.
{There are minor differences between ODBC and the SQL CLI standard }.

SQLDescribeParam2(Describe a specific parameter in a prepared SQL statement)
SQLDisconnect0 Close connection - that's it! The sole argument is the connection handle. Note that SQLDisconnect fails (leaving the connection open) if transactions have not been completed using SQLEndTran, in other words, if 'there is an incomplete transaction associated with the connection handle'! The associated error (SQLSTATE code) is 25000. Also note that if a statement handle associated with the connection hasn't been freed, then SQLDisconnect will generally just free the statement unless the statement is still asynchronously executing. In the latter case, SQLDisconnect fails, with SQLSTATE code HY010. Lots of room for terror!
{In addition, if your DBMS allows double connections on the same DBC, a previously dormant connection might become active}!
SQLDriverConnect1 Connect to specific driver (by string, or dialogue box). This is the bells-and-whistles version of SQLConnect, allowing the use of 'dialog boxes', special data sources, and more arguments than just the server name, user name, and password. See also SQLBrowseConnect.
SQLDrivers2List of installed drivers. Very similar to SQLDataSources, but there are only two possible values for Direction - SQL_FETCH FIRST and SQL_FETCH_NEXT. The arguments have the same format:
SQLDrivers( SQLHENV EnvironmentHandle, SQLUSMALLINT Direction, SQLCHAR * DriverDescription, SQLSMALLINT BufferLength1, SQLSMALLINT * NameLength1Ptr, SQLCHAR * DriverAttributes, SQLSMALLINT BufferLength2, SQLSMALLINT * AttributesLengthPtr);

This function lists drivers, providing a description, and then a string that describes that driver's attributes. The format of the latter is a little contrived - each driver attribute is in the format attribute=value followed by a NUL (so the string continues past the NULs), and then a NUL right at the end of the string (so effectively, the string only ends when two side-by-side NULs are encountered)!

SQLEndTran Commit or roll back transaction! Replaces SQLTransact! (Note that SQLCancel can [unwisely] be used to cancel a single transaction, but SQLEndTran does the whole commit or rollback thing)! You should be asking yourself "Why, if SQL has its own COMMIT and ROLLBACK, do we need one here??". Good question. The answer is that ODBC drivers use caching of data to speed things up. Unfortunate, but true. EndTran seems to be the way that we ensure all of these buffers are flushed out. You MUST NOT submit COMMIT or ROLLBACK statements using SQLExecute or SQLExecDirect - Das ist verboten!!

The arguments are:
SQLEndTran( SQLSMALLINT HandleType, SQLHANDLE Handle, SQLSMALLINT CompletionType);
You can only specify two types of handle - SQL_HANDLE_ENV and SQL_HANDLE_DBC. CompletionType is either SQL_COMMIT or SQL_ROLLBACK. As you guessed, if you specify the ENV handle, then every single DBC associated with the handle will commit or rollback.

{Note that you cannot use SQLEndTran on a shared environment. Also note that if you are silly enough to use 'autocommit mode', then SQLEndTran always just smiles at you and says SQL_SUCCESS. {Look up the SQL3 methods of specifying release-savepoint, and rollback-to-savepoint}! }.

SQLError 0 Obsolete. Don't use
Arguments were: SQLError(SQLHENV EnvironmentHandle, SQLHDBC ConnectionHandle, SQLHSTMT StatementHandle, SQLCHAR FAR *Sqlstate, SQLINTEGER FAR *NativeError, SQLCHAR FAR *MessageText, SQLSMALLINT BufferLength, SQLSMALLINT FAR *TextLength);
SQLExecDirect 0 Execute a statement. The format is:
SQLRETURN SQLExecDirect( SQLHSTMT StatementHandle,
SQLCHAR * StatementText, SQLINTEGER TextLength);

The statement handle is just that, the text is an SQL command or query - you must also supply the length of the text (or SQL_NTS). See our example to find out how to bind columns before SQLExecDirect. Note that SQLExecDirect is actually redundant - it is exactly the same as saying SQLPrepare {read me now}, followed by SQLExecute. The function is a convenience only!

{If SQLExecDirect returns anything other than SQL_SUCCESS, it's a darn good idea to use SQLGetDiagRec to find out what went wrong. A variety of nasty 5-character SQLSTATE values may be returned. In the following list, * indicates that the value is associated with SQL_SUCCESS_WITH_INFO, rather than the other more ominous options of SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA or SQL_INVALID_HANDLE.

01000* - 'general warning' (Look for driver-specific message)
01001* - problem with updating/deleting rows (none, or many)!
01003* - NULLs were eliminated from data before a function (eg AVG) was applied
01004* - data was truncated (string or binary)
01006* - you tried to REVOKE but you're a wimp and cannot do so
01007* - likewise for GRANT
01S02* - We merrily changed an invalid statement attribute (such as SQL_ATTR_MAX_ROWS, SQL_ATTR_QUERY_TIMEOUT, ..) to one that suited us. Now you're stuck with this. Use SQLGetStmtAttr to find out what we did!
01S07* - Fractional part of result was truncated (time or numeric datum)
07002 - Bad number of parameters specified in a preceding SQLBindParameter statement!
07006 - Data type conversion (cf SQLBindParameter) failed
07S01 - No default parameter available (despite SQLBindParameter specifying SQL_DEFAULT_PARAM)
08S01 - communication link failed
21S01 - SQL "INSERT" statement failed (bad number of values)
21S02 - SQL "CREATE VIEW" statement failed (too many columns)
22001 - string was truncated
22002 - NULL data bound to a null pointer (!?, SQLBindParameter)
22003 - Number overflow resulted in truncation of number
22007 - Invalid date or time
22008 - similar to 22007 (date or time overflow)
22012 - Division by zero occurred during SQL calculations
22015 - Conversion of numeric data (to 'interval datum') resulted in loss of significant digits
22018 - 'Cast' in statement failed
22019 - "LIKE .. WHERE .. ESCAPE" SQL statement cause failure due to bad ESCAPE clause
22025 - another bad escape sequence (bad pattern)
23000 - SQL integrity constrain violation
24000 - Bad cursor state (See also SQLFetch, SQLFetchScroll)
34000 - Invalid cursor (bad name)
3D000 - Bad catalog name
3F000 - Bad schema name
40001 - Resource deadlock forced rollback (aargh)!
40003 - Connection failed during execution of statement (double aargh)!
42000 - Bad SQL syntax (or you're not allowed to execute such statements)
42S01 - "CREATE TABLE" or "CREATE VIEW" failed as the table already exists
42S02 - One of a variety of possible commands failed because specified table doesn't exist!
42S11 - "CREATE INDEX" or "CREATE SCHEMA" failed as index already exists
42S12 - "DROP INDEX" failed as index doesn't exist
42S21 - "ALTER TABLE .. ADD" failed as column already exists
42S22 - Specified column wasn't found!
44000 - "..WITH CHECK OPTION.." violated
HY000 - Nonspecific error - best use SQLGetDiagRec to get text description!
HY001 - bad memory allocation
HY008 - SQLCancel was used to cancel this operation (ugly)!
HY009 - *StatementText was a null pointer (no!)
HY010 - (Hmm. complex. 'Function sequence error'). Asynchrony and all that.
HY013 - Memory management screwed up.
HY090 - Dud string length specified (TextLength), or dud string length specified in SQLBindParameter!
HY105 - SQLBindParameter specified SQL_PARAM_OUTPUT, but the parameter was an input parameter!
HY109 - SQLSetPos or SQLSetScroll positioned a cursor in a silly place
HYT01 - connection timeout (see SQLSetConnectAttr)
IM001 - Function not supported.

SQLExecute 0 Execute a prepared statement. See also SQLPrepare ! Format simply: SQLExecute(SQLHSTMT StatementHandle);
SQLExtendedFetch 2 Obsolete ODBC. Fetch set of rows
SQLFetch 0 Fetch a row of results. Should be preceded by e.g. SQLExecute. Its single argument is a statement handle. After an execute statement, and before SQLFetch, the cursor is located at just before the first row of the data set. Note that if no columns are bound, then no data items are retrieved, but the 'cursor' is still moved to the next row in the data set. (See also SQLGetData, and for binding, see our example))!

{SQLFetch returns SQL_SUCCESS if things worked out OK, SQL_NO_DATA when there are no more rows to fetch, or a variety of possible errors which can be identified using the usual strategy of calling SQLGetDiagRec and looking at the SQLSTATE. (Common sqlstate values include 01000, 01004, 01S01, 01S07, 07006, 07009, 08S01, 22001, 22002, 22003, 22007, 22012, 22015, 22018, 24000, 40001, 40003, HY000, HY001, HY008, HY010, HY013, HY090, HY107, HYT01, IM001, all of which are listed elsewhere apart from 01S01 (error fetching row), 07009 (complex bookmark error on column 0 using ODBC v2 driver), and HY107(row out of range)

Hidden within the MS documentation is the ability to fetch multiple rows using SQLFetch. Poison. ) }.

SQLFetchScroll Fetch rows, but in a scrollable fashion. The format:
SQLFetchScroll(SQLHSTMT StatementHandle, SQLSMALLINT FetchOrientation, SQLINTEGER FetchOffset);

One can skip around within the result set (cf. SQLFetch) by specifying different values of FetchOffset and FetchOrientation. FetchOrientation can take on one of six values:

  1. SQL_FETCH_NEXT (identical to SQLFetch)

With the last five options, you first have to call SetStmtAttr with the SQL_ATTR_CURSOR_SCROLLABLE option, or they won't work!

The _RELATIVE option with a FetchOffset of +1 is the same as saying _NEXT, and with a value of -1 is the same as saying _PRIOR. If you specify a FetchOffset that is negative together with SQL_FETCH_ABSOLUTE, this value is used as an offset from the end of the data set! If you try and move FetchOffset outside the data set, it sticks at before the first column, or after the last one.

SQLForeignKeys2(List of column names for foreign keys)
Format: SQLForeignKeys(SQLHSTMT StatementHandle, SQLCHAR FAR *PKCatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *PKSchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *PKTableName, SQLSMALLINT NameLength3, SQLCHAR FAR *FKCatalogName, SQLSMALLINT NameLength4, SQLCHAR FAR *FKSchemaName, SQLSMALLINT NameLength5, SQLCHAR FAR *FKTableName, SQLSMALLINT NameLength6);
SQLFreeConnect 0 Obsolete. See SQLFreeHandle Format: SQLFreeConnect(SQLHDBC ConnectionHandle);
SQLFreeEnv 0 Obsolete. See SQLFreeHandle Took a single argument - SQLHENV
SQLFreeHandle Destroy the resource associated with any one of a variety of handles. This replaces SQLFreeConnect, SQLFreeEnv, and (to a degree) SQLFreeStmt. The two arguments are simply the handle type, and the handle itself, thus:
(Also take a look at SQLAllocHandle). Note that if SQL_ERROR is returned, then the handle has not been deallocated, and is still valid. Contrariwise, all sorts of nonsense can occur if you try and use a handle after it has been 'freed'.
SQLFreeStmt0 This function is partially redundant. The format is: SQLFreeStmt(SQLHSTMT StatementHandle, SQLUSMALLINT Option);

There are five possible (standard) options:

  5. SQL_REALLOCATE (not supported by ODBC)

SQL_DROP really is obsolete - rather use SQLFreeHandle, but the others are of interest, as they are used for freeing things associated with statements rather than bumping off the whole statement! SQL_CLOSE is pretty boring, as it does the same as SQLCloseCursor; but UNBIND is useful in turning off the binding (SQLBindCol) of an SQL column to a local variable that has been freed and that might otherwise crash your application (Bless you, Windows). _RESET_PARAMS is similar, cancelling all SQLBindParameter calls made on a statement handle! _REALLOCATE is even more final, destroying the statement and cursors associated with a statement handle.

SQLGetConnectAttr Get connection attributes. Also take a look at SQLSetConnectAttr. The format is:
SQLGetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute,
SQLPOINTER ValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StringLengthPtr);

Attribute is the attribute to retrieve, as for SetConnectAttr. The only standard attribute is SQL_ATTR_AUTO_IPD, which is read only, and says whether an IPD is automatically populated whenever an SQL statement is prepared.

As Gulutzan and Pelzer (p773) point out, this function does not retrieve time zone offset; default catalog, schema, character set or collation; nor name of connection, SQL-server or session user. (Quite boring, actually).

SQLGetConnectOption1 Obsolete. See SQLGetConnectAttr
SQLGetCursorName0 Get name of cursor associated with a particular statement handle - see our notes
SQLGetData1 Get datum from a given column (for the current row). The format is:
SQLRETURN SQLGetData( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLSMALLINT TargetType, SQLPOINTER TargetValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StrLen_or_IndPtr);

The statement handle and column number are self-explanatory, if you've read our major example. The TargetType identifies the type of datum, as described in SQLBindCol, which also explains TargetValuePtr. Note that there are two other possible target types - SQL_C_DEFAULT, in which case the C data type is selected based on the data type of the source (!), and another even more obscure type called SQL_ARD_TYPE {QV}. Buffer length is the length of the buffer at TargetValuePtr.

This function doesn't actually alter the ARD, but you could have fooled me - for what it does is very similar! It temporarily binds the target variable to the specified column, and then transfers the datum into the target. Generally, binding once using SQLBindCol is probably a bit more efficient than using this function, which binds anew, for each result.

Avoid the ODBC feature which allows you to get successive parts of a field using successive calls to the function

SQLGetDescField Get value of one descriptor field. This is an important function if you wish to look at such fields. Its format is:
SQLGetDescField(SQLHDESC DescriptorHandle, SQLSMALLINT RecordNumber, SQLSMALLINT FieldIdentifier, SQLPOINTER Value, SQLINTEGER BufferLength, SQLINTEGER FAR *StringLength);

The RecordNumber parameter is irrelevant when you're looking at header fields, but is important for IDAs (as there may be many of them). See how you need a descriptor handle to get this function to work - how do you get such a beast? Easy, use SQLGetStmtAttr with the relevant attribute. The various field identifiers (for example, SQL_DESC_COUNT, SQL_DESC_DATA_POINTER) are listed in the section on descs.

Note that several other routines can merely be seen as 'calls to SQLGetDescField' - for example, SQLColAttribute, and SQLGetDescRec.

SQLGetDescRec Read the IDA. This returns seven desc fields - name, type, subtype, length, precision, scale, and nullable. The format is:

{See SQLGetDescField for further details - it's as if you called this function seven times with SQL_DESC_NAME, .._TYPE, _DATETIME_INTERVAL_CODE, _OCTET_LENGTH, _PRECISION, _SCALE AND _NULLABLE. Values that are irrelevant are set to NULL. Despite its size, the BufferLength will never be longer than a SMALLINT. SQLGetDescRec only appeared in ODBC 3.5! }.

SQLGetDiagField Used to get diagnostic information.

The arguments are SQLGetDiagField( SQLSMALLINT HandleType, SQLHANDLE Handle, SQLSMALLINT RecNumber, SQLSMALLINT DiagIdentifier, SQLPOINTER DiagInfoPtr, SQLSMALLINT BufferLength, SQLSMALLINT * StringLengthPtr);

First read the section on SQLGetDiagRec, then come back here! This function returns the value of just one field of a diagnostic record. You can even peek at the value in a header field. For more details, see our diagnostic record documentation.

You have to supply the type of handle, the handle itself, the status record number, the 'DiagIdentifier' which says which particular field you're interested in (more of this later), and a pointer to where the function will put the diagnostic information it provides. DiagIdentifier is non-trivial.

{ ODBC is similar to the standard, but not identical: If RecNumber was specified as zero, then we want to look at a header field. There are several such fields, all specified by using one of the following as DiagIdentifier. The type of information returned is given in brackets after the code - this is the data structure that must be pointed to by 'DiagInfoPtr' if we want the function to succeed:

Conversely, if you specified a RecNumber of over zero, then the following DiagIdentifier values are acceptable:

Of the above, perhaps the most interesting are SQL_DIAG_DYNAMIC_FUNCTION (which allows us to find out about the SQL statement actually executed during SQLExecute / SQLExecuteDirect, or indeed SQLMoreResults), SQL_DIAG_NUMBER (from which we can find out how many status records are associated with a handle, rather than just using repeated SQLGetDiagRec calls until they run out), SQL_DIAG_ROW_COUNT (which tells us how many rows were actually affected by an insert, delete or update operation), SQL_DIAG_MESSAGE_TEXT (the text of an error or warning message), and SQL_DIAG_NATIVE (which gives a data-source-specific native error code).

There is a slew of SQL_DIAG_DYNAMIC_FUNCTION_CODEs - corresponding to various SQL commands. These include SQL_DIAG_ALTER_DOMAIN, SQL_DIAG_ALTER_TABLE, SQL_DIAG_CREATE_ASSERTION, and so on. }.

SQLGetDiagRec This function gets diagnostic information related to errors and other happenings.
{It's hellishly complex. The basic idea is that any call to an ODBC function can result in the formation of one or more diagnostic records contained within a diagnostic data structure. These diagnostic records have a header (with associated data fields), and a set of status records. To interrogate the header (or the status records), use the related function SQLGetDiagField. To interrogate just a few status record fields, use this function. Also see our documentation - technically, what we are doing with this function is simply querying three fields for each status record - SQL_DIAG_SQLSTATE, SQL_DIAG_NATIVE_ERROR, and SQL_DIAG_MESSAGE_TEXT.

The function takes the arguments: SQLGetDiagRec( SQLSMALLINT HandleType, SQLHANDLE Handle, SQLSMALLINT RecNumber, SQLCHAR * Sqlstate, SQLINTEGER * NativeErrorPtr, SQLCHAR * MessageText, SQLSMALLINT BufferLength, SQLSMALLINT * TextLengthPtr);

You submit the type of handle you wish to interrogate, and the actual handle. You also submit a 'status record' (RecNumber) - you must start counting at 1, and provide a 'BufferLength' which is the length of your text buffer. You get back a complicated five-character SQLSTATE code, an integer value that reflects the 'native error code', a text message, and a length (which is how long the text message could have been). Note that you have to provide pointers to spaces where the data will be put - a six character string to put the state code, an integer pointer for the native error code, and a text string (with length) for the message. Hmm.

The function itself returns a variety of possible codes - SQL_SUCCESS, SQL_INVALID_HANDLE, SQL_ERROR, and SQL_NO_DATA (all self-explanatory). The interesting one is SQL_SUCCESS_WITH_INFO, which actually tells you 'You fool. The MessageText field was too small to contain the message, so I did nothing'.
Most importantly, all diagnostic messages (etc) associated with a particular handle are irretrievably obliterated once you use that handle in another call. You must first call SQLGetDiagRec before you use the handle again, or all is lost! (OK, there are two other functions that don't screw things up, SQLGetDiagField, and SQLError).

SQLGetEnvAttr Get environment attribute - very similar to SQLSetEnvAttr, the Attribute being the same. The format is:
SQLGetEnvAttr( SQLHENV EnvironmentHandle, SQLINTEGER Attribute, SQLPOINTER valuePtr, SQLINTEGER BufferLength, SQLINTEGER * StringLengthPtr);
The only ISO-defined attribute is SQL_ATTR_OUTPUT_NTS.
SQLGetFunctions1 Get functions supported by the ODBC driver. The format appears simple: SQLGetFunctions (SQLHDBC ConnectionHandle, SQLUSMALLINT FunctionID, SQLUSMALLINT * SupportedPtr);

.. but in fact the SupportedPtr may be either a pointer to a single integer, or an SQLUSMALLINT array of 4000 bits!! {The former is the case unless the value of FunctionID is SQL_API_ODBC3_ALL_FUNCTIONS - you might wish not to try this value}! The single integer takes the value of either SQL_TRUE, or SQL_FALSE. Function IDs include:
.. and so on down to


(NB ODBC & ISO differ!)
Get information about specified driver, or DBMS; The format is:
SQLGetInfo (SQLHDBC ConnectionHandle, SQLUSMALLINT InfoType, SQLPOINTER InfoValuePtr, SQLUSMALLINT BufferLength, SQLSMALLINT * StringLengthPtr);

Information is returned in the InfoValuePtr. There is a vast number of information types that can be put into InfoType. Depending on this type, the function may return a null-terminated string, an SQLUSMALLINT, or an SQLUINTEGER. To make things more complex, an SQLUINTEGER may actually represent a number, binary value, or bit mask.

{The MS documentation lists several categories of information:

  3. Data source information (e.g. SQL_USER_NAME, SQL_DATA_SOURCE_READ_ONLY, ..)

Note that many values of InfoType first appeared in v3.0, and that others were renamed or Obsolete. You may wish to consult the relevant Micro$oft documentation (about 48 pages of it)! }.

Compare this with SQLGetTypeInfo, SQLTablePrivileges, SQLTables, which provide information about tables etc (metadata), and SQLGetFunctions !

SQLGetLength[NON-ODBC, for BLOB and CLOB locators]
Format: SQLGetLength(SQLHSTMT StatementHandle, SQLSMALLINT LocatorType, SQLINTEGER Locator, SQLINTEGER FAR *StringLength, SQLINTEGER FAR *IndicatorValue);
SQLGetParamData[NON-ODBC, Obscure]
Format: SQLGetParamData(SQLHSTMT StatementHandle, SQLSMALLINT ParameterNumber, SQLSMALLINT TargetType, SQLPOINTER TargetValue, SQLINTEGER BufferLength, SQLINTEGER FAR *StrLen_or_Ind);
SQLGetPosition[NON-ODBC, for BLOB and CLOB locators]
Format: SQLGetPosition(SQLHSTMT StatementHandle, SQLSMALLINT LocatorType, SQLINTEGER SourceLocator, SQLINTEGER SearchLocator, SQLCHAR FAR *SearchLiteral, SQLINTEGER SearchLiteralLength, SQLINTEGER FromPosition, SQLINTEGER FAR *LocatedAt, SQLINTEGER FAR *IndicatorValue);
SQLGetStmtAttr Get attribute of statement. Allows you to peek at internal ODBC functioning. The format is:
SQLGetStmtAttr(SQLHSTMT StatementHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER BufferLength, SQLINTEGER FAR *StringLength);

There are eight standard attributes (those marked * cannot be altered, the rest can, using the corresponding function that sets attributes - SQLSetStmtAttr):
SQL_ATTR_CURSOR_SENSITIVITY (does database support sensitive cursors?)
SQL_ATTR_CURSOR_HOLDABLE (does database support holdable cursors?)
SQL_ATTR_CURSOR_SCROLLABLE (does database support scrollable cursors?)

The last three are peculiar to SQL3.

SQLGetStmtOption 1 Obsolete, from ODBC v2. See SQLGetStmtAttr
SQLGetSubstring[NON-ODBC, for BLOB and CLOB locators]
Format: SQLGetSubString(SQLHSTMT StatementHandle, SQLSMALLINT LocatorType, SQLINTEGER SourceLocator, SQLINTEGER FromPosition, SQLINTEGER ForLength, SQLSMALLINT TargetType, SQLPOINTER TargetValue, SQLINTEGER BufferLength, SQLINTEGER FAR *StringLength, SQLINTEGER FAR *IndicatorValue);
SQLGetTypeInfo1 Get supported data types. Format is GetTypeInfo (SQLHSTMT StatementHandle, SQLSMALLINT DataType);

DataType may be SQL_ALL_TYPES, or any one of the standard data types. Note that data are returned as an SQL result set, just as for SQLColumns! If you asked for a particular data type, then that type is returned; if you ask for all types, the results are ordered by data type (DATA_TYPE). (Sub-ordering is pretty arbitrary, so don't rely on it). If a requested type is not supported then a null data set is returned.

The result set has a fair number of columns. They are:

Type Info Results Set
Column name Type Constraint(s) {nn=not NULL} Comment
TYPE_NAME VARCHAR nn NB depends on data source!
COLUMN_SIZE INTEGER - max character length or numeric precision
LITERAL_PREFIX VARCHAR - character that prefixes a literal eg quote mark, 0x, ..
LITERAL_SUFFIX VARCHAR - terminate a literal with eg quote mark'
CREATE_PARAMS VARCHAR - (keyword list)
NULLABLE SMALLINT nn can field be null?
SQL_DATA_TYPE SMALLINT nn (verbose data type)
NUM_PREC_RADIX INTEGER - 2 or 10 (2 means that COLUMN_SIZE is bits, 10 means 'decimal digits')
SQLMoreResults2Find/fetch more results (if available)

SQLMoreResults only exists because sometimes, just sometimes, several result sets may become associated with the single statement handle supplied to the function. This can only happen if an SQL CALL statement resulted in the production of multiple result sets, which can certainly happen! (All the called procedure need do is invoke several SELECT statements)! Each time it's called, MoreResults positions the cursor at the start of the next result set. The sole argument is a statement handle.

SQLNativeSql2 Get a driver-specific translation of a submitted SQL statement
SQLNumParams2 Get number of parameters in a statement
SQLNumResultCols0 Format:
SQLNumResultCols(SQLHSTMT StatementHandle, SQLSMALLINT FAR *ColumnCount);

Find out how many columns there are in a set of results. Note that this function is intimately associated with SQLPrepare and SQLExecute, and only works when a statement has been prepared or executed (SQLExecDirect also works).
{The 'bookmark' column zero (even if it exists) is not included in the count. Note that if and only if the last executed statement was a query, then *ColumnCount will be nonzero. (Interrogating the desc header field 'SQL_DESC_DYNAMIC_FUNCTION_CODE' using SQLGetDescField will provide similar information.) }.

SQLParamData1 (Use with SQLPutData to supply parameter data at execution time?!)
Format: SQLParamData(SQLHSTMT StatementHandle, SQLPOINTER FAR *Value);
Format: SQLParameters(SQLHSTMT StatementHandle, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *RoutineName, SQLSMALLINT NameLength3, SQLCHAR FAR *ParameterName, SQLSMALLINT NameLength4);
SQLParamOptions 2 Obsolete ODBC. Use SQLSetStmtAttr
SQLPrepare0 Prepare SQL string for execution! The format is straightforward:
SQLPrepare (SQLHSTMT StatementHandle, SQLCHAR * StatementText, SQLINTEGER TextLength);
See our example and also SQLExecute. There are some statements that cannot be prepared - here's a list of preparable and non-preparable SQL statements.

TAKE NOTE that if an SQLPrepare statement fails, then the preceding SQL statement previously prepared remains valid. This has magnificent potential for cockups, if you now try and SQLExecute the 'new' statement and get the old one in its place! Conversely, DO NOT assume that a prepared statement is still valid after you've called SQLEndTran - prepare it again! You can ensure that a prepared statement is killed off by calling SQLFreeStmt with the SQL_REALLOCATE parameter, but this won't work with ODBC.

SQLPrimaryKeys2Get names of columns that make up table's primary key.
Format: SQLPrimaryKeys(SQLHSTMT StatementHandle, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *TableName, SQLSMALLINT NameLength3);
SQLProcedureColumns2 (List i/o parameters etc. for stated procedures)
SQLProcedures2 (List names of procedures stored in a data source)
SQLPutData 1 Send parameter or column data to driver at time of execution of statement!
Format is: SQLPutData(SQLHSTMT StatementHandle, SQLPOINTER Data, SQLINTEGER StrLen_or_Ind);
Format: SQLRoutinePrivileges(SQLHSTMT StatementHandle, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *RoutineName, SQLSMALLINT NameLength3);
SQLRowCount0 Format simply:
SQLRowCount(SQLHSTMT StatementHandle, SQLINTEGER FAR *RowCount);

Wouldn't it be nice if we had a function that could tell you how many rows in your result set? Dream on.. for this apparently friendly function has limitations, many of them dependent on the database you are working with! A variety of statements (SQLExecute, SQLExecDirect, SQLSetPos, and SQLMoreResults) may set the 'row count' value that is returned by SQLRowCount, this is only with certain SQL commands! For example, some data sources apparently cannot tell you the number of rows returned by e.g. a SELECT statement. Apparently, the value is reliable when UPDATE, INSERT and DELETE statements are used, provided you realise that only directly affected rows are counted in (e.g. rows deleted by a "CASCADE" clause are ignored in the count).

{ "UPDATE .. WHERE CURRENT OF" (and similar statements) have no effect on row count. SQLRowCount is identical to "SQLGetDiagField(SQL_HANDLE_STMT, handleSTMT, 0, SQL_DIAG_ROW_COUNT, &rowcount, 0, NULL);" provided you call this function immediately after e.g. SQLExecute. Alternatives to using this unreliable function to get the number of rows in a SELECT statement are:

  • "SELECT COUNT(*)", which may also fail under some circumstances!
  • Using SQLFetchScroll repeatedly until the SQLRETURN value is SQL_NO_DATA;
Unfortunate, isn't it? }.
SQLSetConnectAttr Set attributes of a connnection - there's a long list of these in MS ODBC, but little of use in the ISO standard. The format is: SQLSetConnectAttr( SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);

{Most attributes can be set before or after connecting, but some (labelled ) can (or should) only be set before, and others (+), after connection is established. Here they are:
SQL_ATTR_ASYNC_ENABLE (set this before an active statement is created),
SQL_ATTR_TXN_ISOLATION (only set if no open transactions)

Access mode options include things like SQL_MODE_READ_ONLY, atrocities like 'autocommit' are managed using (wait for it) SQL_ATTR_AUTOCOMMIT, and SQL_ATTR_CONNECTION_TIMEOUT specifies how long a wait occurs before a timeout occurs. The default wait is forever (ValuePtr is zero). Other fancy things include character-set translation, logging of SQL statements, cursor use, and even (sometimes) network packet size!

You can also pass an integer value in ValuePtr, casting the integer using eg "(void *)". }.

SQLSetConnectOption 1 Obsolete. Use SQLSetConnectAttr
SQLSetCursorName0Set name of cursor. Format: SQLSetCursorName(SQLHSTMT StatementHandle, SQLCHAR FAR *CursorName, SQLSMALLINT NameLength); Also see our notes.
SQLSetDescField Set value of one descriptor field - the corresponding 'get' function is SQLGetDescField. The format is:
SQLSetDescField(SQLHDESC DescriptorHandle, SQLSMALLINT RecordNumber, SQLSMALLINT FieldIdentifier, SQLPOINTER Value, SQLINTEGER BufferLength);

This is not a function to be invoked lightly! There is a particular sequence in which you must alter fields. The order is:

  1. SQL_DESC_TYPE (altering this alters many other fields to default values - for a full list see Gulutzan & Pelzer p848!!)
  3. other fields (in any order)
Expect problems if you don't adhere to the above. Also note that although you would expect the RecordNumber field to be irrelevant to changes in header fields, you should still set this to zero if you're talking to a header field. The Value field should be of the same data type as that of the field being altered.
SQLSetDescRec Set multiple descriptor field values - the format is:

Compare this with SQLSetDescField, and the analogous SQLGetDescRec, but note that there is not good correspondence with the latter function in terms of what can be set. {? have comparison}. The effect is similar to using SQLBindParameter!

SQLSetEnvAttr This function sets up the environment. The format is:
SQLSetEnvAttr( SQLHENV EnvironmentHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);

There is a limited number of attributes in MS ODBC:
SQL_ATTR_OUTPUT_NTS (the only ISO-defined one)

The one you're a little interested in is .._ODBC_VERSION, and this only because you must set it for subsequent calls that use the handle of the environment (or an "SQLSTATE HY010" error will result). You'll generally specify SQL_OV_ODBC3, unless you're using software that was written for earlier versions of ODBC, and the ODBC driver you have is version 3. Then specify SQL_OV_ODBC2.
{Note the usage in our example - we specify a string length of zero as the last argument, and cast SQL_OV_ODBC3 as a null pointer. Ugly, innit? For the record, the .._NTS command defines whether strings are returned with a terminal zero (a la ASCIIZ) or not. The default is SQL_TRUE and you're probably nuts if you tinker with this.
Pooling is vaguely interesting. The idea is that a pool of connections is established, and an application can then use a pooled connection without having to go through the 'whole connection process' every time it wishes to connect. This is said to save time for applications that repeatedly connect and disconnect (e.g. over a slow internet connection).

SQLSetParam0 Obsolete. Use SQLBindParameter
Arguments were: SQLSetParam( HSTMT hstmt, UWORD ipar, SWORD fCType, SWORD fSqlType, UDWORD cbColDef, SWORD ibScale, PTR rgbValue, SDWORD FAR *pcbValue);
Note that there is no InputOutputMode parameter - as there is for the replacement function SQLBindParameter.
SQLSetPos2 (Position a cursor)
SQLSetScrollOptions2 Determine scrolling behaviour
SQLSetStmtAttr Set attribute of statement - in other words, manipulate the internal ODBC handling of a statement. This was formerly performed by SQLParamOptions and SQLSetStmtOption. {? QV} The format is:
SQLSetStmtAttr (SQLHSTMT StatementHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);
- note that in C one often has to submit a value in ValuePtr by casting e.g. a handle as a void pointer:     (void*) handlename thus:

{ There are six standard attribute options that you can set - see the list under SQLGetStmtAttr. The important settings are to do with cursors - if you change SQL_ATTR_CURSOR_SCROLLABLE from the default of SQL_NONSCROLLABLE to SQL_SCROLLABLE, then you can move the cursor (using the SQLFetchScroll command) backwards and forwards in a variety of ways. If you leave it as is, then you can only fetch the very next record, even with SQLFetchScroll, and that's it! SQL_ATTR_CURSOR_HOLDABLE (if changed from SQL_NONHOLDABLE to SQL_HOLDABLE) allows you to stop the cursor from disappearing at the end of a transaction. SQL_ATTR_CURSOR_SENSITIVITY can be changed from the default of SQL_UNSPECIFIED to SQL_INSENSITIVE (changes made by others won't be seen by you) or SQL_SENSITIVE (other changes will be seen here).

Some ODBC attribute options are fairly weird - you can for example force ODBC to substitute similar values if the data source doesn't support a specified value (?!). }.

SQLSetStmtOption 1 Obsolete ODBC v2. Use SQLSetStmtAttr
SQLSpecialColumns1*Complex. Find columns updated together with a particular value, or find columns that uniquely identify a row in a table. Format:
SQLSpecialColumns(SQLHSTMT StatementHandle, SQLUSMALLINT IdentifierType, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *TableName, SQLSMALLINT NameLength3, SQLUSMALLINT Scope, SQLUSMALLINT Nullable);
SQLStatistics1* Stats of a given table, list of associated indexes
SQLTablePrivileges2List of tables and associated privileges. Format:
SQLTablePrivileges(SQLHSTMT StatementHandle, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *TableName, SQLSMALLINT NameLength3);
SQLTables1*List tables available in a data source. Format:
SQLTables(SQLHSTMT StatementHandle, SQLCHAR FAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR FAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR FAR *TableName, SQLSMALLINT NameLength3, SQLCHAR FAR *TableType, SQLSMALLINT NameLength4 );
SQLTransact 0 ODBC 2.0 - now obsolete. Use SQLEndTran
{ Took 3 arguments, an environment handle, a DBC handle, and a completion type}
Obsolete/obsolescent functions are greyed out, a zero0 superscript indicates an initial (v1.0) API conformance level of zero, likewise for 1 and 2. Version 3.0 functions are in red. An asterisk* indicates a function that doesn't belong to ISO, but is defined in X/Open, and functions in institutional green are peculiar to Microsoft ODBC (avoid them)!

C. Criticism

You might wish to skip this section and proceed to the references as all I do here is indulge in a bit of Microsoft-bashing.

When I started looking at ODBC in a little more detail, I expected it to be quite fun. From what I read, Microsoft had gone a long way to creating a reasonable, functional standard. I then read through most of the above sixty-odd functions, and came away reeling. "All I really wanted," I said, clutching my head "was to open up a connection to a database, pass it some information and get back data, and then close the connection"! Let's explore these simple requirements more carefully.

First, let's note that the idea of binding SQL variables to program variables is probably a good idea. Especially with things like floating point numbers, it seems crazy to translate to and from character strings when one can simply transfer the numbers. Score one for Microsoft.

Second, let us distinguish between:

  1. SQL data;
  2. metadata;
  3. 'tuning' ODBC to meet our needs.
By metadata, we mean information about the actual tables, columns, etc in the database, as opposed to the data contained in them.

Third, let us revisit Dr Codd's rules, notably rule number four:

        "The data base description is represented at the logical level in the same way
        as ordinary data, so that authorised users can apply the same relational
        language to its interrogation as they apply to the regular data"

Finally, we are ready to describe the functions I think are required to interrogate a database using something like ODBC. We need:

  1. A function to open connection(s) to the database(s)
  2. A function to close the connection(s)
  3. A function to bind local variables to SQL variables
  4. A function to submit SQL statements
  5. A function to fetch the first/next row of a result set
  6. A function to submit SQL statements relating to metadata
  7. Perhaps, a function to 'tune' the 'ODBC' environment.

And that's it! Now it's clear that, apart from function 6, Microsoft covers all of the above extremely thoroughly. So why am I whingeing? My first gripe is that there should surely also be a requirement for orthogonality - the functions should not only meet requirements, but avoid needless duplication. In addition, rather than having a whole host of shoddy little functions, it would seem a good idea to distribute what is done in a balanced way between the various functions.

My other complaint is related to 'function 6'. We see some glimmers of my requirement in functions like SQLGetTypeInfo, but such usage seems to be unrelated to any desire to stick to Dr Codd's rule. I suspect that the only reason why SQLGetTypeInfo returns an SQL-style data set is that even the MS programmers balked at creating a function which fetches data contained in nineteen variables! The welter of ODBC functions seems largely related to nobody having sat down and listened to Dr Codd! Many of the complex functions could simply have been replaced with a meta-data structure, which could be queried and altered with SQL-style instructions. I suppose that whether you agree with this, my major gripe, depends largely on your philosophy. And that's enough whining for the time being!

{To be fair, I should here create a C++ structure that adds a front end providing the functionality I desire. FDBC (free DBC) here we come? My guess is that if one perched this on ODBC, it would be very slow}.


Handy Cross-references

Here's a list of functions, ordered logically (after Gulutzan & Pelzer):
  1. Environment-related functions: SQLAllocHandle, SQLFreeHandle, also SQLGetEnvAttr, SQLSetEnvAttr, SQLAllocEnv, SQLFreeEnv.
  2. Connection (DBC) functions: SQLAllocHandle, SQLFreeHandle, SQLConnect, SQLDisconnect, also SQLGetConnectAttr, SQLSetConnectAttr, and SQLAllocConnect, SQLFreeConnect.
  3. Statement (STMT) functions: SQLAllocHandle, SQLFreeHandle; and SQLGetStmtAttr, SQLSetStmtAttr, also SQLAllocStmt, SQLFreeStmt;
  4. Prepare and Execute: SQLPrepare, SQLExecute, SQLExecDirect, SQLEndTran;
  5. Cursor-related: SQLFetch, SQLFetchScroll, SQLCloseCursor, and even SQLGetCursorName and SQLSetCursorName. (Several other functions may affect or effect cursors).
  6. Descriptor functions: SQLAllocHandle, SQLFreeHandle (of course) and SQLGetDescField, SQLSetDescField, SQLGetDescRec, SQLSetDescRec, SQLCopyDesc, SQLBindCol, SQLGetData, SQLBindParameter, SQLColAttribute, SQLDescribeCol, SQLNumResultCols, SQLGetParamData
  7. Diagnostics: SQLGetDiagField, SQLGetDiagRec (SQLError, SQLRowCount)
  8. Metadata - catalog functions (SQLColumnPrivileges, SQLColumns, SQLGetTypeInfo, SQLParameters, SQLPrimaryKeys, SQLForeignKeys, SQLRoutines, SQLRoutinePrivileges, SQLSpecialColumns, SQLTables, SQLTablePrivileges.
  9. Other: SQLDataSources, SQLGetFunctions, SQLGetInfo; also deferred parameter functions (SQLParamData, SQLPutData, SQLCancel).

Finally, a short table of functions in alphabetical order. Only functions with a decent description above are clickable references!

SQLAllocConnect SQLAllocEnv SQLAllocHandle SQLAllocStmt SQLBindCol SQLBindParameter
SQLBrowseConnect SQLBulkOperations SQLCancel SQLCloseCursor SQLColAttribute SQLColAttributes
SQLColumnPrivileges SQLColumns SQLConnect SQLCopyDesc SQLDataSources SQLDescribeCol
SQLDescribeParam SQLDisconnect SQLDriverConnect SQLDrivers SQLEndTran SQLError
SQLExecDirect SQLExecute SQLExtendedFetch SQLFetch SQLFetchScroll SQLForeignKeys
SQLFreeConnect SQLFreeEnv SQLFreeHandle SQLFreeStmt SQLGetConnectAttr SQLGetConnectOption
SQLGetCursorName SQLGetData SQLGetDescField SQLGetDescRec SQLGetDiagField SQLGetDiagRec
SQLGetEnvAttr SQLGetFunctions SQLGetInfo SQLGetLength SQLGetParamData SQLGetPosition
SQLGetStmtAttr SQLGetStmtOption SQLGetSubstring SQLGetTypeInfo SQLMoreResults SQLNativeSql
SQLNumParams SQLNumResultCols SQLParamData SQLParameters SQLParamOptions SQLPrepare
SQLPrimaryKeys SQLProcedureColumns SQLProcedures SQLPutData SQLRoutinePrivileges SQLRoutines
SQLRowCount SQLSetConnectAttr SQLSetConnectOption SQLSetCursorName SQLSetDescField SQLSetDescRec
SQLSetEnvAttr SQLSetParam SQLSetPos SQLSetScrollOptions SQLSetStmtAttr SQLSetStmtOption
SQLSpecialColumns SQLStatistics SQLTablePrivileges SQLTables SQLTransact

And that's it!

Date of First Draft: 2001/11/11 Date of Last Update: 2001/-/- Web page author: