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

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

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:
   
   SQLCHAR * xxx, SQLSMALLINT yyy, SQLSMALLINT * zzz,
   
   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" ,
                          SQL_NTS);
   

   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:

      SQLCloseCursor(handleSTMT);
   

   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:

   SQLEndTran (SQL_HANDLE_ENV, handleENVIR, SQL_ROLLBACK);
   SQLFreeHandle(SQL_HANDLE_STMT, handleSTMT); 
   SQLDisconnect(handleCONNECT);
   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.

long howmanycols;

SQLNumResultCols(handleSTMT, &howmanycols);

• If we can only get the number of columns after we've called SQLExecDirect, how on earth can we use SQLBindCol to bind local variables to all of the SQL columns?

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:

   SQLINTEGER sqMyKey;
   SQLCHAR * sqMyName;        

   sqMyName = new char [256];

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

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

   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

  "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');",
                     SQL_NTS);

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

Consider..

  "INSERT INTO MyTable (MyID, MyName) VALUES (?, ?);"

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:

• SQLFetch, which we've already encountered;
• SQLFetchScroll - unlike SQLFetch, this doesn't just fetch the 'next result', but allows us to move around.
• SQLCloseCursor - simply close a cursor (we've met you before too)!
• SQLGetCursorName )
• SQLSetCursorName )..boring
• SQLMoreResults

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

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.

   5. : SQL_DESC_INDICATOR_POINTER (aka SQL_SEC_INDICATOR_PTR). Used together with SQL_DESC_DATA_POINTER {??}.

   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:
       SQL_DESC_SET_CATALOG *, SQL_DESC_CHARACTER_SET_SCHEMA *, SQL_DESC_CHARACTER_SET_NAME *, SQL_DESC_COLLATION_CATALOG **, SQL_DESC_COLLATION_SCHEMA **, SQL_DESC_COLLATION_NAME **, SQL_DESC_KEY_MEMBER *, SQL_DESC_NAME, SQL_DESC_PARAMETER_MODE *, SQL_DESC_PARAMETER_ORDINAL_POSITION *, SQL_DESC_PARAMETER_SPECIFIC_CATALOG *, SQL_DESC_PARAMETER_SPECIFIC_SCHEMA *, SQL_DESC_PARAMETER_SPECIFIC_NAME *, SQL_DESC_UDT_CATALOG *, SQL_DESC_UDT_SCHEMA *, SQL_DESC_UDT_NAME *, SQL_DESC_UNNAMED.

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

6. A note on fetching errors!

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.
6. SQL_DIAG_CONSTRAINT_CATALOG, SQL_DIAG_CONSTRAINT_SCHEMA, SQL_DIAG_CONSTRAINT_NAME are all to do with constraint violations.
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

• Bind a variable local to a particular procedure (using SQLBindCol), then leave that procedure and do an SQLFetch (GPF, here we come)! See SQLFreeStmt (.. SQL_UNBIND) for an antidote.
• Use buffers that are too small to contain results. ODBC will merrily try to write past the end of the buffer, which will almost certainly give your program a terminal experience.

6.2 SQLSTATE

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

8. Frills, bells, ..

• bookmarks and associated functionality (SQLBulkOperations);
• privileges (SQLColumnPrivileges, SQLTablePrivileges);
• Other 'trivia' (SQLNativeSQL, SQLSpecialColumns, SQLStatistics, SQLPrimaryKeys, SQLProcedureColumns, SQLProcedures)

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

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.

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}.

References

• First, See Gulutzan & Pelzer's SQL '99 Complete, Really. (ISBN 0-87930-568-1) As usual, an absolutely superb reference, with several hundred pages of detailed discussion and examples. Go buy the book. Now! (No, they didn't pay us to say this). In their Ocelot database you can find a really good set of headers - view the file "SQLCLI.H".

• Microsoft's page - as expected, on the surface singularly devoid of content. If you look around enough, you might just find one or more of their MDAC downloads, eg MDAC 2.7 RTM There doesn't appear to be readily accessible documentation associated with these bulky monsters, but if you search hard enough you will finally find scores of pages of ODBC documentation! Don't however try and read these in Netscape. There's also a good chance they will have moved or removed the information by the time you click on this link!
  { MDAC, or 'Microsoft Data Access Components', is a hodge-podge of different layers of database software (drivers etc) for Windoze &c. Generally, go for the most recent 'RTM'. Don't get MDAC 2.7 if you're on Win95, use an earlier version; also some of the later versions require a recent version of Internet Explorer to function. Installing MDAC is something akin to watching the USA put the jackboot into the crunchy ribcage of some impoverished middle-eastern country. Presumably there are some food parcels in among the ± 10Megs of cluster bombs that are distributed willy-nilly in your Windows directory, but who knows? You are certainly not given the choice as to what you wish to install/overwrite }.

• There is documentation and a freeware ODBC driver for MySQL here

• And for PostgreSQL here (when up), or look at the documentation. Minoru development also have a ODBC kit for PostgreSQL.

• The Oracle ODBC page (Good, but only for Oracle).

• FreeODBC !

• UnixODBC (Look under Manuals | Programmer Tutorial) for a good (if somewhat Teutonic) introduction.

• The OpenLink standard is worth a read.

• A Visual basic page provides valuable links (many are now however broken)!

• A roadmap of data access is a must-see! Follow this reference.

• a Good article in DDJ on The SAG (SQL Access Group) call-level interface (CLI)

• Here's a "DBMS Online" article on ODBC v.3 (1996).

• Ken North has written an excellent (but 1994) overview of ODBC

• If you are into Perl, YOU MUST SEE Roth consulting's ODBC page. You can download the most recent version of Win32 ODBC for Perl from the site. There is also documentation on how to install this Perl extension. If you download the binaries then the README has a wealth of information about ODBC usage. There's also a little Perl demo. There's a good FAQ. Very worthwhile. For a full list of commands, see Roth's note.

Handy Cross-references

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!

And that's it!