Table of contents:

  Info
  Introduction and Files
  How to use the CLI
  Definitions and Common Data Types
    Data Qualifier FAR, NEAR
    Data Type BOOL
    Data Type CHAR, UCHAR
    Data Type DWORD, SDWORD, UDWORD
    Data Type HANDLE
    Data Type HWND
    Data Type LONG, ULONG
    Data Type PTR
    Data Type RETCODE
    Data Type SHORT, USHORT
    Data Type WORD, SWORD, UWORD
    Data Type INT
    Function Qualifier PASCAL
    Function Qualifier SQL_API
    Function Qualifier SQL_LOCAL_API
    Function Macro MAX, MIN
  SQL Data Types
    SQLAllocConnect
    SQLAllocEnv
    SQLAllocHandle
    SQLAllocStmt
    SQLBindCol
    SQLBindParam
    SQLBrowseConnect
    SQLCancel
    SQLCloseCursor
    SQLColAttributes
    SQLColumnPrivileges
    SQLColumns
    SQLConnect
    SQLCopyDesc
    SQLDescribeCol
    SQLDescribeParam
    SQLDisconnect
    SQLDriverConnect
    SQLDriverSetup
    SQLEndTran
    SQLError
    SQLExecDirect
    SQLExecute
    SQLExtendedFetch
    SQLFetch
    SQLForeignKeys
    SQLFreeConnect
    SQLFreeEnv
    SQLFreeHandle
    SQLFreeStmt
    SQLGetCol
    SQLGetConnectAttr
    SQLGetConnectOption
    SQLGetCursorName
    SQLGetData
    SQLGetDescField
    SQLGetDescRec
    SQLGetDiagField
    SQLGetDiagRec
    SQLGetEnvAttr
    SQLGetInfo
    SQLGetStmtAttr
    SQLGetStmtOption
    SQLGetTypeInfo
    SQLLanguages
    SQLMoreResults
    SQLNativeSql
    SQLNumParams
    SQLNumResultCols
    SQLParamData
    SQLParamOptions
    SQLPrepare
    SQLPrimaryKeys
    SQLProcedureColumns
    SQLProcedures
    SQLPutData
    SQLReleaseEnv
    SQLRowCount
    SQLServerInfo
    SQLSetConnectOption
    SQLSetCursorName
    SQLSetDescField
    SQLSetDescRec
    SQLSetEnvAttr
    SQLSetParam
    SQLSetPos
    SQLSetScrollOptions
    SQLSetStmtAttr
    SQLSetStmtOption
    SQLSpecialColumns
    SQLStatistics
    SQLTablePrivileges
    SQLTables
    SQLTransact
  CLI Function Return Codes
  Sample Sources
  iODBC
    Description of iODBC.INI
    Sample iODBC.INI for OS/2
    Sample iODBC.INI for Unix
    Compatibility List
  Limitations and Differences to Standard CLI/ODBC
    Limits for mSQL
    Limits for Oracle
  History
  Author's Details
  Archive Location

iODBC V "1.00.5"


Info

intrinsic Open Data Base Connection (iODBC) for OS/2
* standardization of database access *
All programs included in this package are provided "as is", without
any warranty! Try them on your own risk.

This documentation is based on various documentations and own expiriences.

(c) 1997 by Dirk Ohme - all rights reserved

Note:
All files (except iODBC) are copyrighted by Dirk Ohme, all other are under GNU-law. You are allowed to use the CLI and all related stuff in non-commerical environments without any fee. If you want to use the CLI within a commerical environment, please contact the author. If you want to use iODBC within a commercial environment, see the GNU law. The archive may be distributed freely, but only as one package that mustn't be modified to the original distribution!

Introduction and Files

The Call Level Interface is an C-API for writing portable database applications. It is meant to be database independent. The sources are drawn a bit from ODBC (Open DataBase Connection - a firm specific standard by the Microsoft Corporation based on X/Open's CLI) and CLI specified by the ISO/ANSI comitee and X/Open.

Drivers (CLI) are located under the DRIVER directory. Currently there are the following drivers:

  • mSQL - driver for mSQL 1.x and 2.x

    The following Call-Level Interfaces (CLI) may be used as iODBC-compliant ODBC drivers:

  • IBM DB2/2 V 2.0 and above
  • SOLID V 2.1 SQL database

    How to use the CLI

    Within your programs, you have to include a file named sqlcli_.h. This is normally done by insertion of a statement like this:

             /*
             ** use the next include for use of CLI or iODBC
             ** (contains function prototypes)
             */
             #include <sqlcli_.h>
    
             /*
             ** on iODBC as an alternative to the above include file
             ** you may use one or both of these include files
             ** (lacks most function prototypes)
             */
             #include <isql.h>
             #include <isqlext.h>
    
    

    Make sure that this file is located to a directory normally being searched for include files by the compiler or specify the directory containing this file directly at compiler call (this is done by /Idirectory for the IBM C compilers or by -Idirectory for Unix style C compilers, e.g. gcc, there directory is the desired directory).

    Note:
    All environement dependend header files (i.e. msql.h for mSQL) have to be put to a directory, for which the same conditions exists (included during compilation per default or per parameter switch).

    In order to link the compiled sources, use following files located in the library directory (LIB) for

  • CLI (Driver)
  • under OS/2: libmsql.lib (dynamic linkage against MSQL.DLL, mSQL 1.x)
  • under OS/2: libmsql2.lib (dynamic linkage against MSQL.DLL, mSQL 2.x)
  • under Unix: libclimSQL.so (dynmaic linkage, mSQL 1.0.x)
  • under Unix: libclimSQL2.so (dynamic linkage, mSQL 2.0)
  • under Unix: libclioracle.so (dynmaic linkage, Oracle)
  • iODBC
  • under OS/2: iodbc.lib (dynamic linkage against IODBC.DLL)
  • under Unix: iodbc.so (dynamic linkage)

    To use the CLI properly there is a predefined path showing when to call which routine. The next picture describes a CLI session.

  • First, there has to be an allocation of environment space (see SQLAllocEnv()).
  • Then, a database context has to be set up (see SQLAllocConnect()).
  • In order to do transactions there must be a database connection established (done either by SQLConnect() or SQLDriverConnect()).
  • Transactions take place (see below).
  • After the last transaction, the connection to the database has to be closed (see SQLDisconnect()).
  • Then the database context has to be freed (see SQLFreeConnect()).
  • The last (CLI) operation is to free the environment space (see SQLFreeEnv()).

    Transactions could be done in various ways. A transaction can store or manipulate data (INSERT, UPDATE, DELETE, ...), retrieve data (SELECT, ...) or do anything else (manipulation on table structure, access control to data, creation of views, ...). The next picture contains an overview on transaction operation:

  • Before any transaction a statement buffer has to be allocated (see SQLAllocStmt()).
  • A transaction statement then can be prepared and then being executed (see SQLPrepare(), SQLExecute()) or run directly (see SQLExecDirect()). On both paths certain parameters of the statment could be bound to variables (see SQLBindParam()).
  • Depending on the transaction type, there are those possibilities:
  • On Data Retrival the number of resulting columns has to be gathered (see SQLResultCols()), the columns bound to variables (see SQLBindCol()) and all result rows processed (see SQLFetch()). Optionally additional columns informations can be retrieved (see SQLDescribeCol() or SQLColAttributes()) and column values asked (see SQLGetData()).
  • On Data Manipulation only the number of rows being affected can be retrieved (see SQLRowCount()).
  • On any other transaction type there are no special returns.
  • When the last transaction is done, the statement buffer has to be freed first (see SQLFreeStmt()).
  • All transactions then have to be commited or withdrawn by a SQLTransact() call.

    Definitions and Common Data Types

    This section contains the definitions used within the Call-Level Interface as there set in sqlcli_.h. For better understanding, notice, that (under OS/2!):

  • a character has a size of 8 bits,
  • a word has a size of 16 bits,
  • a short integer has a size of 16 bits,
  • a double word has a size of 32 bits,
  • a long integer has a size of 32 bits.

    Data Qualifier FAR, NEAR

      FAR
      NEAR
    

    The purpose of this qualifiers is to specify if a pointer is used within this data segment (NEAR) or not (FAR). Under OS/2 2.x or above it is not necessary to use this.

    Note:
    This is implemented for migration of software from platforms, where the determination of FAR and NEAR pointers is necessary.

    Data Type BOOL

      BOOL                   { FALSE, TRUE }
    

    This defines a boolean data type with it's two possible values

  • FALSE
  • TRUE

    Data Type CHAR, UCHAR

      CHAR                   - signed char
      UCHAR                  - unsigned char
    

    This defines a data type for storing signed / unsigned integer values, i.e. characters.

    Data Type DWORD, SDWORD, UDWORD

      DWORD                  - double word
      SDWORD                 - signed double word
      UDWORD                 - unsigned double word
    

    This defines a data type for storing signed / unsigned double words.

    Data Type HANDLE

      HANDLE                 - (unqualified) handle
    

    The purpose of this data type is to store a unqualified handle to "anything".

    Data Type HWND

      HWND                   - handle (for) window
    

    The purpose of this data type is to store a handle for window manipulation.

    Data Type LONG, ULONG

      LONG                   - signed long integer
      ULONG                  - unsigned long integer
    

    This defines a data type for storing signed / unsigned long integer values.

    Data Type PTR

      PTR                    - (unqualified) pointer
    

    This data type is intended to be used for unqualified pointer specification. It is comparable with the ANSI-C data type void *.

    Data Type RETCODE

      RETCODE                - (unqualified) program return code
    

    This data type is intended to be used to specify an unqualified program return code, i.e. a return code of "something".

    Data Type SHORT, USHORT

      SHORT                  - signed short integer
      USHORT                 - unsigned short integer
    

    This defines a data type for storing signed / unsigned short integer values.

    Data Type WORD, SWORD, UWORD

      WORD                   - word
      SWORD                  - signed word
      UWORD                  - unsigned word
    

    This defines a data type for storing signed / unsigned words.

    Data Type INT

      UINT                   - unsigned integer
    

    This defines a data type for storing unsigned integer values.

    Function Qualifier PASCAL

      PASCAL
    

    The intention of this function qualifier is to distinguish between function with "C" and "PASCAL" parameter/calling convention. For example:

      void funcProto(int a, int b );       /* C calling convention      */
      void PASCAL funcProto(int a, int b); /* PASCAL calling convention */
    
  • "C" calling stores first b, then a to the head
  • "PASCAL" calling stores first a, then b to the head

    Function Qualifier SQL_API

      SQL_API
    

    This qualifier is used for specification of a public call-level interface routine (procedure).

    Function Qualifier SQL_LOCAL_API

      SQL_LOCAL_API
    

    This qualifier is used for specification of a non-public (private) call-level interface routine (procedure).

    Function Macro MAX, MIN

      MAX(x,y)               ((x > y) ? x : y)
      MIN(x,y)               ((x < y) ? x : y)
    

    These macros can be used for checking two numbers (numerical values) against. MAX returns the greater number, MIN the minor number of the two given ones.

    SQL Data Types

    Accessing the database through the Call-Level Interface requires an abstraction of the data types. The 'C' programming language and the structured query language (SQL) have different names and types for certain data objects. So there are data types defined in the header file sqlcli_.h for interactions between 'C' code and SQL database.

    First there are the following definitions (constants):
    SQL_DATE_LEN maximum length of date strings
    SQL_MAX_DRV_ID_LENGTH maximum length of iODBC driver setup IDs
    SQL_MAX_ID_LENGTH maximum length of identifiers
    SQL_MAX_MESSAGE_LENGTH maximum length of messages
    SQL_SQLSTATE_SIZE maximum length of status codes
    SQL_TIME_LEN maximum length of time strings
    SQL_TIMESTAMP_LEN maxmimum length of a timestamp
    SQLPOINTER PTR
    SQLRETURN RETCODE SQL_API
    SQLUINTEGER UINT
    SQLUSMALLINT USHORT

    And now the SQL data types:
    ***SQL native***identifier'C' ***comment***
    SQLBINARY SQL_C_BINARYunsigned char bit field of 8 bits
    SQLBIT SQL_C_BIT BOOL
    SQLBLOBLOCATORSQL_C_BLOB_LOCATORlong reference
    SQLCHAR SQL_C_CHAR unsigned char
    SQLCLOBLOCATORSQL_C_CLOB_LOCATORlong reference
    SQLDATE - unsigned char pointer to string
    SQLDBCHAR SQL_C_DBCHARunsigned char character
    SQLDBCLOBLOCATORSQL_C_DBCLOB_LOCATORlongreference
    SQLDECIMAL - unsigned char pointer to string
    SQLDOUBLE SQL_C_DOUBLEdouble
    SQLINTEGER SQL_C_LONG long
    SQLFLOAT SQL_C_FLOAT float
    SQLNUMERIC - unsigned char pointer to string
    SQLREAL SQL_C_REAL float
    SQLSCHAR SQL_C_TINYINTsigned char
    SQLSHORT SQL_C_SHORT short
    SQLSMALLINTSQL_C_SHORT short
    SQLTIME - unsigned char pointer to string
    SQLTIMESTAMP- unsigned char pointer to string
    SQLTINYINT SQL_C_TINYINTsigned char
    ***SQL native***identifiersub-entries ***comment***
    SQLDATE SQL_C_DATE DATE_STRUCT date structure
    year year
    month month
    day day
    SQLTIME SQL_C_TIME TIME_STRUCT time structure
    hour hour
    minuteminute
    secondsecond
    SQLTIMESTAMPSQL_C_TIMESTAMPTIMESTAMP_STRUCTtime stamp
    year year
    month month
    day day
    hour hour
    minuteminute
    secondsecond
    fraction???
    ***SQL*** Handle/Pointer'C' ***comment***
    SQLHDBC HDBC, LPDBC DBC CLI database context
    SQLHDESC HDESC, LPDESCDESC CLI descriptor
    SQLHENV HENV, LPENV ENV CLI environment
    SQLHSTMT HSTMT, LPSTMTSTMT CLI statement

    Note:

    BLOB stands for binary large object block.
    CLOB stands for character large object block.

    Special structures used by the iODBC:

    typedef struct _SQLDrvOptList {        /* driver options list                */
            char        szOptName[SQL_MAX_DRV_ID_LENGTH]; /* - option name       */
            char        szOptValue[SQL_MAX_DRV_ID_LENGTH]; /* - option value     */
            void       *pNext;             /*   - pointer to next element        */
    } SQLDrvOptList, *SQLPDrvOptList;      /*                                    */
    
    typedef struct _SQLDriverList {        /* driver list                        */
            char        szName[SQL_MAX_DRV_ID_LENGTH]; /* - name of driver       */
            SQLPDrvOptList pOptions;       /*   - start of options list          */
            void       *pNext;             /*   - pointer to next element        */
    } SQLDriverList, *SQLPDriverList;      /*                                    */
    

    SQLAllocConnect

      RETCODE SQL_API SQLAllocConnect(
                                   LPENV               lpenv,
                                   HDBC FAR           *phdbc
                                     );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpenv - handle to CLI environment block
  • phdbc - pointer to handle for database context block

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1001 memory allocation error
    S1009 invalid pointer phdbc
    SQL_INVALID_HANDLE - invalid pointer lpenv
    SQL_SUCCESS - context successfully allocated

    The call provides the allocation of a database context structure, that is used for database connection and transactions. It takes the handle returned by SQLAllocEnv() as input parameter.

    SQLAllocEnv

      RETCODE SQL_API SQLAllocEnv( SQLHENV FAR   *phenv
                                 );
    
    ODBC core level
    X/Open yes

    calling parameters

  • phenv - pointer to handle for environment block

    return codes
    return code SQLErrorcomment
    SQL_ERROR - internal error (memory problems?)
    SQL_INVALID_HANDLE - invalid pointer phenv
    SQL_SUCCESS - environment successfully allocated

    This function has to be called at start of the program in order to create a environment where the CLI could work. phenv has to point to the variable used as handle to the environment block.

    Note:
    There is no error message (see SQLError) available.

    SQLAllocHandle

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLAllocStmt

      RETCODE SQL_API SQLAllocStmt(
              LPDBC   lpdbc,
              HSTMT FAR *phstmt);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpdbc - handle for database context
  • phstmt - pointer to variable storing statement handle

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1001 memory allocation failure
    S1009 invalid pointer phstmt
    SQL_INVALID_HANDLE - invalid handle lpdbc
    SQL_SUCCESS - statment handle created successfully

    Allocation of a statement handle, which is used for database interactions (queries, insertions, deletes, etc.). This function requires an open database context handle. After the last interaction and before freeing the database context, all allocated statement handles have to be closed via SQLFreeStmt().

    SQLBindCol

    RETCODE SQL_API SQLBindCol(
            LPSTMT  lpstmt,
            UWORD   icol,
            SWORD   fCType,
            PTR     rgbValue,
            SDWORD  cbValueMax,
            SDWORD FAR *pcbValue);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • icol - column to be bound (starting with 1)
  • fCType - type of data (SQL_C_...)
  • rgbValue - pointer to variable to be used for storage
  • cbValueMax - maximum length of data to be transfered
  • pcbValue - pointer to variable with length of data to be copied

    return codes
    return code SQLErrorcomment
    SQL_ERROR 07005 column icol not available
    S1003 unsupported C data type in fCType
    S1002 invalid column number icol
    S1010 no query has been done or no results
    S1090 result greater than allowed in cbValueMax
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    After execution of a query via SQLExecute() or SQLExecDirect() the results must be bound to variables before a SQLFetch() call for getting values can take place. This function bind a variable to a field or column of the resulting table.

    SQLBindParam

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLBrowseConnect

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLCancel

    RETCODE SQL_API SQLCancel(
            LPSTMT  lpstmt);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement

    return codes
    return code SQLErrorcomment
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    This function allows canceling of a database operation (INSERT, DELETE, UPDATE), needing more input data. Such a situation may occur when SQLExecute() or SQLExecDirect() returns a status SQL_NEED_DATA. Then this function can be called to cancel the execution of the command in order to specify all missing data and re-do the operation.

    Note:
    This function is a dummy function at the current state. mSQL does not support such things. It may support this in a later version.

    SQLCloseCursor

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLColAttributes

    RETCODE SQL_API SQLColAttributes(
            LPSTMT      lpstmt,
            UWORD       icol,
            UWORD       fDescType,
            UCHAR FAR  *rgbDesc,
            SWORD       cbDescMax,
            SWORD FAR  *pcbDesc,
            SDWORD FAR *pfDesc );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • icol - number of column to be queried (starting with 1)
  • fDescType - type of information to be queried, numerical or boolean results are stored in pfDesc, result string in rgbDesc.
  • SQL_COLUMN_AUTO_INCREMENT - check whether a column refers to an auto increment data type or not. Returns FALSE in pfDesc for all data types.
  • SQL_COLUMN_CASE_SENSITIVE - column can case sensitive on character fields, FALSE is returned on numerical fields.
  • SQL_COLUMN_CATALOG_NAME - return catalog name in rgbDesc for the column. Under mSQL returns always an empty string, since there are no catalogs.
  • SQL_COLUMN_QUALIFIER_NAME - same as SQL_COLUMN_CATALOG_NAME.
  • SQL_COLUMN_COUNT - pfDesc contains the number of resulting columns.
  • SQL_DISPLAY_SIZE - returns display size for column in pfDesc
  • SQL_COLUMN_DISTINCT_TYPE - implemented for compatibility with IBM's DB2/2. In this implementation returns always an empty string.
  • SQL_COLUMN_LABEL - returns the label of the column in rgbDesc.
  • SQL_COLUMN_LENGTH - returns the length of a column in pfDesc.
  • SQL_COLUMN_MONEY - TRUE on columns dealing with cash. In this implementation, this sub-routine returns always FALSE.
  • SQL_COLUMN_NAME - in this implementation, same as SQL_COLUMN_LABEL.
  • SQL_COLUMN_NULLABLE - returns boolean expression whether a column can store a NULL value or not (defined as NOT NULL).
  • SQL_COLUMN_PRECISION - on numerical columns (fields) the precision in digits, on character columns the maximum number of bytes.
  • SQL_COLUMN_SCALE - scaling of a column. In the implementation, returns always 0.
  • SQL_COLUMN_SCHEMA_NAME - returns the table name, in this implementation returns always an empty string.
  • SQL_COLUMN_OWNER_NAME - same as SQL_COLUMN_SCHEMA_NAME.
  • SQL_COLUMN_SEARCHABLE - returns on of these
  • SQL_UNSEARCHABLE - column can't be used with a WHERE clause
  • SQL_LIKE_ONLY - column can be used within WHERE on LIKE operations
  • SQL_ALL_EXCEPT_LIKE - column can be used within WHERE except on LIKE operations
  • SQL_SEARCHABLE - no restrictions in use
    In this implementation for numerical data types SQL_ALL_EXCEPT_LIKE and for character data types SQL_SEARCHABLE will be returned.
  • SQL_COLUMN_TABLE_NAME - returns table name, in this implementation returns always an empty string
  • SQL_COLUMN_TYPE - returns the data type of the column as numerical value (e.g. SQL_CHAR).
  • SQL_COLUMN_TYPE_NAME - returns the data type of the column as string. The string equals the data type name at column definition in a CREATE TABLE statement (e.g. "char" for character fields).
  • SQL_COLUMN_UNSIGNED - returns whether a column contains unsigned data or not. In this implementation TRUE on all character columns and FALSE on numerical fields.
  • SQL_COLUMN_UPDATABLE - returns one of these
  • SQL_ATTR_READWRITE_UNKNOWN - column is updateable
  • SQL_ATTR_READONLY - column can't be updated
    In this implementation all columns are updateable.
  • rgbDesc - pointer to string buffer for storing the result
  • cbDescMax - maximum length of string buffer rgbDesc
  • pcbDesc - pointer to variable storing the length of the result string
  • pfDesc - on non-string results (integer number results) the result is stored in the variable referenced by this pointer

    return codes
    return code SQLErrorcomment
    SQL_ERROR 07005 no result available for sub-function
    40003 communication to database server failed
    S1002 invalid column number icol
    S1010 there are no results of a query to be examined
    S1090 invalid pointer rgbDesc or pfDesc or length cbDescMax
    S1091 invalid sub-function fDescType
    S1C00 CLI can't resolve database server's answer
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS_WITH_INFO01004 result string rgbDesc has been truncated
    SQL_SUCCESS - no errors

    This function returns various informations about the result columns of a database query. Keep in mind, that each call of this function can return only one answer. An alternative may be SQLDescribeCol().

    SQLColumnPrivileges

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLColumns

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLConnect

      RETCODE SQL_API SQLConnect(  LPDBC               lpdbc,
                                   UCHAR FAR          *szDSN,
                                   SWORD               cbDSN,
                                   UCHAR FAR          *szUID,
                                   SWORD               cbUID,
                                   UCHAR FAR          *szAuthStr,
                                   SWORD               cbAuthStr
                                );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpdbc - handle to already allocated database context
  • szDSN - pointer to string containing database source (table)
  • cbDSN - size of string szDSN or SQL_NTS if string is \0 (NUL) terminated
  • szUID - pointer to string containing user identifier (user name) for database access
  • cbUID - size of string szUID or SQL_NTS if string is \0 (NUL) terminated
  • szAuthStr - pointer to string containing password for database access
  • cbAuthStr - size of string szAuthStr or SQL_NTS if string is \0 (NUL) terminated

    Note:

    This special implementation for mSQL may have a different use of szDSN as known by other CLIs or ODBC. You may specify user name, database name and host (system name or IP) within this entry. Examples:

      szDSN = <database name>                        "sample"
              <user name>.<database name>            "kim.sample"
              <database name>@<host>                 "sample@minerva.edu.au"
              <user name>.<database name>@<host>     "kim.sample@127.0.0.1"
    

    Note:

    If a host isn't specified, a connection to the server on the local machine (localhost) is tried to be established.

    return codes
    return code SQLErrorcomment
    SQL_ERROR 08001 can't connect to server
    08002 connection already established
    08004 can't connect to database
    SQL_INVALID_HANDLE - invalid database context handle
    SQL_SUCCESS - connection established

    This function has to be called after SQLAllocConnect(). It tries to connect to a database source (mSQL: host/database). After successful connection database activities may start.

    SQLCopyDesc

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLDescribeCol

    RETCODE SQL_API SQLDescribeCol(
            LPSTMT  lpstmt,
            UWORD   icol,
            UCHAR FAR *szColName,
            SWORD   cbColNameMax,
            SWORD FAR *pcbColName,
            SWORD FAR *pfSqlType,
            UDWORD FAR *pcbColDef,
            SWORD FAR *pibScale,
            SWORD FAR *pfNullable);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • icol - number of column in result list to be examined (starting with 1)
  • szColName - pointer to string used for storing column name
  • cbColNameMax - maximum length for szColName
  • pcbColName - pointer to variable storing real length of szColName
  • pfSqlType - pointer to variable storing type of column
  • pcbColDef - pointer to variable storing precision of column
  • pibScale - pointer to variable storing scale of (numeric) column
  • pfNullable - pointer to boolean variable storing info if column is nullable (SQL_NULLABLE) or not (SQL_NO_NULLS)

    return codes
    return code SQLErrorcomment
    SQL_ERROR 07005 column not available
    40003 can't get column info from server
    S1002 invalid column number icol
    S1010 no query has been executed
    S1097 invalid column type detected
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    This routine can be used first to find out how many columns a query returns. Second, it can be used to detect the type of columns.

    SQLDescribeParam

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLDisconnect

      RETCODE SQL_API SQLDisconnect(
                                   LPDBC               lpdbc
                                   );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpdbc - handle to database context

    return codes
    return code SQLErrorcomment
    SQL_ERROR 08003 no connection established
    25000 transaction must be ended first
    58004 can't find path to database
    SQL_INVALID_HANDLE - invalid handle to database context
    SQL_SUCCESS - connection successfully closed

    The purpose of this routine is to close an open connection established by SQLBrowseConnect(), SQLConnect() or SQLDriverConnect(). After successful close there can't be any database activities. Before closing, all open transactions have to be ended (see SQLTransact()).

    SQLDriverConnect

    RETCODE SQL_API SQLDriverConnect(
                                 LPDBC               lpdbc,
                                 HWND                hwnd,
                                 UCHAR FAR          *szConnStrIn,
                                 SWORD               cbConnStrIn,
                                 UCHAR FAR          *szConnStrOut,
                                 SWORD               cbConnStrOutMax,
                                 SWORD FAR          *pcbConnStrOutMax,
                                 UWORD               fDriverCompletion
                                    );
    
    ODBC level 1
    X/Open yes

    calling parameters

  • lpdbc - handle to database context
  • hwnd - handle for Presentation Manager desktop (OS/2), Windows desktop, Motif desktop (Unix), or NULL (currently not used)
  • szConnStrIn - connection input string, consists of keyword = value pairs, which are separated by semicolons
  • DSN - describes the data source name (name or alias-name of database)
  • UID - describes the user name
  • PWD - describes the user password
  • cbConnStrIn - length of szConnStrIn or SQL_NTS if the string is NUL terminated
  • szConnStrOut - pointer to area where the completed connection string is written
  • cbConnStrOutMax - maximum length of area szConnStrOut
  • pcbConnStrOut - pointer to variable, to be filled with the length of the completed connection string
  • fDriverCompletion - flag describing, how to proceed on insufficient data (not implemented yet - always assumes SQL_DRIVER_NOPROMPT)
  • SQL_DRIVER_PROMPT - always prompt a dialog (window) where the user may modify the data or abort the operation
  • SQL_DRIVER_COMPLETE - prompt a dialog (window) only if there is insufficient data for database login
  • SQL_DRIVER_COMPLETE_REQUIRED - prompt a dialog (window) only if there is insufficient data for database login, ask only for mandatory information
  • SQL_DRIVER_NOPROMPT - never present a dialog (window), if the given information is insufficient the database login fails

    return codes
    return code SQLErrorcomment
    SQL_ERROR 08001 can't connect to database server (host)
    08002 already connected
    08004 can't connect to database (on host)
    SQL_INVALID_HANDLE - invalid handle lpdbc
    SQL_SUCCESS - successful database login

    Comparable to SQLConnect(), but with more possibilities. If the given information is insufficient for database login, this routine may open a dialog window to give the user the possibility to complete the information. Further, the connection information is given by one string (no separate database, user name, password strings) - the structure is described above. The example above shows a connection information for login into a database testdb with user name master and password chief.

            DSN=testdb;UID=master;PWD=chief
    

    SQLDriverSetup

    iODBC and OS/2 specific!
    
    RETCODE SQL_API SQLDriverSetup(
                                 LPDBC               lpdbc,
                                 HWND                hwnd,
                                 HMODULE             hmod,
                                 SQLPDriverList      pDrvEntry
                                    );
    
    ODBC no - iODBC & OS/2 specific
    X/Open no

    calling parameters

  • lpdbc - handle to database context
  • hwnd - handle for Presentation Manager desktop (OS/2)
  • hmod - handle to module (*.DLL) which represents the iODBC driver - this can be used to specify the source of the ressources (dialog window ressources).
  • pDrvEntry - pointer to structure representing the iODBC driver manager control structure for this driver.

    return codes
    return code SQLErrorcomment
    SQL_INVALID_HANDLE - invalid lpdbc, hwnd, hmod or pDrvEntry
    SQL_SUCCESS - successful operation

    This is an iODBC specific function to able a driver special settings and setups. I may not be implemented in order to get a full functioning driver. But with this it is much easier to control certain behaviours and settings.

    SQLEndTran

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLError

      RETCODE SQL_API SQLError(
              LPENV   lpenv,
              LPDBC   lpdbc,
              LPSTMT  lpstmt,
              UCHAR FAR *szSqlState,
              SDWORD FAR *pfNativeError,
              UCHAR FAR *szErrorMsg,
              SWORD   cbErrorMsgMax,
              SWORD FAR *pcbErrorMsg);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpenv - handle for environment block
  • lpdbc - handle for database context block
  • lpstmt - handle for current statement structure
  • szSqlState - pointer to buffer for SQL error state code
  • pfNativeError - pointer to buffer for internal error code
  • szErrorMsg - pointer to string for storing error message text
  • cgErrorMsgMax - maximum length for szErrorMsg
  • pcbErrorMsg - result length for szErrorMsg

    return codes
    return code SQLErrorcomment
    SQL_INVALID_HANDLE - invalid lpenv, lpdbc and lpstmt
    SQL_NO_DATA_FOUND - no more data or no previous error
    SQL_SUCCESS - data available

    Returns a message text for a previously occurred error. If there was no previous error, SQL_NO_DATA_FOUND is returned. After successful return of this function, all internal states are resetted.

    SQLExecDirect

    RETCODE SQL_API SQLExecDirect(
            LPSTMT  lpstmt,
            UCHAR FAR *szSqlStr,
            SDWORD  cbSqlStr);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • sqSqlStr - SQL statement to be prepared
  • cbSqlStr - size of string szSqlStr or SQL_NTS if string is \0 (NUL) terminated

    return codes
    return code SQLErrorcomment
    SQL_ERROR xxxxx see below
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    This call combines SQLPrepare() with SQLExecute(). It may be used if there are no parameters to be set between preparation and execution of a statement. The error code are the same as described at the routines mentioned above.

    SQLExecute

      RETCODE SQL_API SQLExecute(
              LPSTMT  lpstmt);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statment

    return codes
    return code SQLErrorcomment
    SQL_ERROR 08001 cannot execute
    40003 no connection to database
    S1010 no prepared statement found
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - execution ended successfully

    After preparation of a SQL statement with SQLPrepare() this function executes the statement. Afterwards, on insertion or deletion SQLRowCount() returns the number of rows affected by the operation, on selection SQLNumResultCols() shows the number of selected rows, that can be queried by SQLDescribeCol(), SQLBindCol() and SQLFetch().

    SQLExtendedFetch

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLFetch

    RETCODE SQL_API SQLFetch(
            LPSTMT  lpstmt);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement

    return codes
    return code SQLErrorcomment
    SQL_ERROR 22005 error in data assignment
    58004 can't get field (internal error)
    S1010 query must be executed first
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_NO_DATA_FOUND - no more results can be fetched
    SQL_SUCCESS - no errors

    The results of a query, executed via SQLExecute() or SQLExecDirect()., with variables bound by SQLBindCol(), can be processed row by row with this function. It copies the resulting fields (columns) into the bounded variables.

    SQLForeignKeys

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLFreeConnect

      RETCODE SQL_API SQLFreeConnect(
                                   LPDBC               lpdbc
                                    );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpdbc - handle to database context block

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1010 connection has to be closed first
    SQL_INVALID_HANDLE - invalid pointer lpdbc
    SQL_SUCCESS - context successfully freed

    After database operation this call has to be used to remove the database context block. After successful removement SQLFreeEnv() can be called.

    SQLFreeEnv

      RETCODE SQL_API SQLFreeEnv(  LPENV               lpenv
                                );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpenv - handle for environment block

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1010 connections have to be closed first
    SQL_INVALID_HANDLE - invalid pointer lpenv
    SQL_SUCCESS - environment successfully freed

    This function has to be called at the end of the program to free the CLI environment block. After successful removement of the environment block, the variable used for handle storage (input parameter lpenv) has to be invalidated.

    Note:
    After successfully freeing the CLI environment block, no error message can be obtained through SQLError.

    SQLFreeHandle

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLFreeStmt

      RETCODE SQL_API SQLFreeStmt(
              LPSTMT  lpstmt,
              UWORD   fOption);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • fOption - option code:
  • SQL_CLOSE - close statement (cursor)
  • SQL_DROP - close statement (cursor) and free handle
  • SQL_UNBIND - unbind columns, keep statement
  • SQL_RESET_PARAMS - reset parameters on bind-columns

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1092 invalid option fOption
    SQL_INVALID_HANDLE - invalid lpstmt
    SQL_SUCCESS - operation succeeded

    Depending on the option code fOption a statement handle allocated with SQLAllocStmt() is reseted in parts or total, or is freed.

    SQLGetCol

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetConnectAttr

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetConnectOption

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetCursorName

    RETCODE SQL_API SQLGetCursorName(
            LPSTMT  lpstmt,
            UCHAR FAR *szCursor,
            SWORD   cbCursorMax,
            SWORD FAR *pcbCursor);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • szCursor - variable to be filled with cursor name
  • cbCursorMax - maximum length of szCursor
  • pcbCursor - pointer to variable storing result length of szCursor

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1090 parameter szCursor must not be NULL
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS_WITH_INFO01004 cursor name was truncated
    SQL_SUCCESS - no errors

    This function returns the name of the cursor associated with the statement. A new cursor name can be set by calling SQLSetCursorName().

    SQLGetData

    RETCODE SQL_API SQLGetData(
            LPSTMT  lpstmt,
            UWORD   icol,
            SWORD   fCType,
            PTR     rgbValue,
            SDWORD  cbValueMax,
            SDWORD FAR *pcbValue)
    
    ODBC level 1
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • icol - column to be bound (starting with 1)
  • fCType - type of data (SQL_C_...)
  • rgbValue - pointer to variable to be used for storage
  • cbValueMax - maximum length of data to be transfered
  • pcbValue - pointer to variable with length of data to be copied

    return codes
    return code SQLErrorcomment
    SQL_ERROR 07005 column icol not available
    S1002 invalid column number icol
    S1003 unsupported C data type in fCType
    S1010 no query has been done or no results
    S1090 result greater than allowed in cbValueMax
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    After execution of a query via SQLExecute() or SQLExecDirect() and after getting a resulting row by a SQLFetch() call, the column values can be gathered by this function.

    SQLGetDescField

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetDescRec

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetDiagField

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetDiagRec

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetEnvAttr

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetInfo

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetStmtAttr

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetStmtOption

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLGetTypeInfo

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLLanguages

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLMoreResults

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLNativeSql

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLNumParams

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLNumResultCols

    RETCODE SQL_API SQLNumResultCols(
            LPSTMT  lpstmt,
            SWORD FAR *pccol);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • pccol - pointer to variable used to store the number of columns

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1009 pccol must point to a variable
    S1010 the query has to be executed first
    SQL_INVALID_HANDLE - invalid pointer lpstmt
    SQL_SUCCESS - no errors

    After a successful execution of a query via SQLExecute(), the number of resulting columns can be obtained by this function.

    SQLParamData

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLParamOptions

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLPrepare

      RETCODE SQL_API SQLPrepare(
              LPSTMT  lpstmt,
              UCHAR FAR *szSqlStr,
              SDWORD  cbSqlStr);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • sqSqlStr - SQL statement to be prepared
  • cbSqlStr - size of string szSqlStr or SQL_NTS if string is \0 (NUL) terminated

    return codes
    return code SQLErrorcomment
    SQL_ERROR 24000 statement handle already in use
    S1001 memory allocation error
    S1009 invalid pointer szSqlStr
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - preparation successfully

    Prepares a SQL statement for execution with SQLExecute().

    SQLPrimaryKeys

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLProcedureColumns

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLProcedures

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLPutData

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLReleaseEnv

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLRowCount

    RETCODE SQL_API SQLRowCount(
            LPSTMT  lpstmt,
            SWORD FAR *pcrow);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • pcrow - pointer to variable used for storage of row count

    return codes
    return code SQLErrorcomment
    SQL_ERROR S1009 pcrow must point to a variable
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no error

    The number of rows affected by an UPDATE, INSERT or DELETE can be obtained with this function. For the number of rows returned by a SELECT have a look at SQLNumResultCols().

    Note:
    For the mSQL 1.x driver, this function is not behave absolutely identical to the ANSI, ODBC or X/Open version. On UPDATE, INSERT or DELETE a value of 0 is always returned regardless the true number of rows affected. On SELECT it returns the number of rows selected. The other drivers do behave conforming to ANSI, ODBC and X/Open.

    SQLServerInfo

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetConnectOption

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetCursorName

    RETCODE SQL_API SQLSetCursorName(
            LPSTMT  lpstmt,
            UCHAR FAR *szCursor,
            SWORD   cbCursor);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • szCursor - pointer to string containing new cursor name
  • cbCursor - length od szCursor or SQL_NTS

    return codes
    return code SQLErrorcomment
    SQL_ERROR 34000 invalid cursor name in szCursor
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    This is the reverse function to SQLGetCursorName(). It is used to set a new cursor name for the statement. A cursor name must start with a character (a..z or A..Z) and may then contain a mixture of characters, digits and underscores (a..z, A..Z, 0..9 or _).

    Note:
    Other implementations need unique cursor names and do not allow the re-use of a cursor name, that has been already in use. This implementation does not check cursor names for unique use, since cursor names are not being used internally, but are implemented for compatibility.

    Note:
    Since there will be a unique cursor name created during allocation of the statement handle (SQLAllocStmt()), you may not use this function within your programs.

    SQLSetDescField

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetDescRec

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetEnvAttr

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetParam

    RETCODE SQL_API SQLSetParam(
            LPSTMT      lpstmt,
            UWORD       ipar,
            SWORD       fCType,
            SWORD       fSqlType,
            UDWORD      cbColDef,
            SWORD       ibScale,
            PTR         rgbValue,
            SDWORD FAR *pcbValue );
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpstmt - handle for statement
  • ipar - number of parameter to be defined, starting left to right with 1.
  • fCType - C data type (SQL_C_...) or SQL_C_DEFAULT.
  • fSqlType - SQL data type (e.g. SQL_INTEGER).
  • cbColDef - length of data to be transfered (character fields) or precision on SQL_DECIMAL / SQL_NUMERIC, else ignored.
  • ibScale - scaling factor (used only at SQL_DECIMAL or SQL_NUMERIC).
  • rgbValue - pointer to variable to be bound to parameter.
  • pcbValue - pointer to variable used for signalising, how many bytes were transfered at execution of the statement.

    return codes
    return code SQLErrorcomment
    SQL_ERROR 07006 can't convert C data type into SQL data type
    S1003 invalid C data type in fCType
    S1004 invalid SQL data type in fSqlType
    S1009 rgbValue must not be NULL
    S1010 must be called after SQLPrepare()
    S1013 can't allocate internal memory
    S1093 invalid parameter number ipar
    S1094 invalid value in ibScale
    S1104 invalid value in cbColDef
    S1C00 unable to resolve the given C or SQL data type
    SQL_INVALID_HANDLE - invalid handle lpstmt
    SQL_SUCCESS - no errors

    A parameter is a place holder in a SQL statement which is resolved at execution time (via SQLExecute()). A parameter is marked by a simple question mark ("?"). Question marks inside of strings delimited by single quotes ("'"). are not treated as parameter place holders but as characters. An example would be this:
        select name, age from test where name='?^ or name=?
    
    At execution time this would be resolved to:
        select name, age from test where name='?^ or name='[contents of variable]'
    

    Note:
    Character fields will be enclosed in single quotes automatically.

    SQLSetPos

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetScrollOptions

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetStmtAttr

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSetStmtOption

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLSpecialColumns

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLStatistics

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLTablePrivileges

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLTables

    
    
    ODBC
    X/Open

    calling parameters

  • return codes
    return code SQLErrorcomment
    SQL_ERROR
    SQL_INVALID_HANDLE -
    SQL_SUCCESS -

    SQLTransact

    RETCODE SQL_API SQLTransact(
            LPENV   lpenv,
            LPDBC   lpdbc,
            UWORD   fType);
    
    ODBC core level
    X/Open yes

    calling parameters

  • lpenv - handle for environment (or SQL_NULL_HENV)
  • lpdbc - handle for database context (or SQL_NULL_HDBC)
  • fType - type of action to be done: SQL_COMMIT to commit all previous database activities, or SQL_ROLLBACK to withdraw all former activities

    return codes
    return code SQLErrorcomment
    SQL_ERROR 08003 no connection open for specified action
    58004 SQL_ROLLBACK failed
    S1012 invalid action code fType
    SQL_INVALID_HANDLE - handle lpenv or lpdbc must be set
    SQL_SUCCESS - no errors

    The purpose of the function is to commit or rollback (withdraw) database actions, like INSERT, UPDATE or DELETE. This function is called normally after (at the end of) a(n) (un)successful transaction.

    Note:
    mSQL 1.x does not allow rollbacks on previous database actions. This is because it does not have any recovery mechanism. For that reason all rollback operations result in an error SQL_ERROR with message code 58004, to signalize an unsuccessful rollback.

    CLI Function Return Codes

    Each Call-Level Interface (CLI) function returns a result code, that shows success or failure of an operation, or if there is additional information code to be processed.
    SQL_SUCCESS operation ended successfully
    SQL_SUCCESS_WITH_INFO same, but there is additional information (see table below)
    SQL_NEED_DATA operation aborted due to lack of data
    SQL_NO_DATA operation failed due to lack of data
    SQL_NO_DATA_FOUND there is no data found
    SQL_ERROR operation failed, see SQLError
    SQL_INVALID_HANDLE invalid handle ( HDBC, HDESC, HENV, HSTMT )

    Note:
    The definition SQL_SUCCEEDED(x) can be used to detect whether x specifies a return code of a successful operation or not.

    Internal error messages and codes (set at SQL_ERROR)
    Code Message
    01004 result string has been truncated
    07005 column or result value not available
    07006 can't convert C data type into SQL data type
    08001 can't connect to server, cannot execute
    08002 connection already established
    08003 no connection established
    08004 can't connect to database
    24000 statement handle already in use
    25000 transaction must be ended first
    34000 invalid cursor name
    40003 no connection to database or communication error
    58004 ROLLBACK failed or an other internal error
    S1001 memory allocation error
    S1002 invalid column number
    S1003 invalid or unsupported C data type
    S1004 invalid SQL data type
    S1009 pointer must address a variable and mustn't be NULL
    S1010 invalid sequence in function calls
    S1012 invalid action code
    S1013 can't allocate internal memory
    S1090 parameter error (parameter must not be NULL, range error)
    S1091 invalid sub-function specified
    S1092 invalid option
    S1093 invalid parameter number
    S1094 invalid value
    S1097 invalid column type detected
    S1104 invalid value
    S1C00 a parameter or result can't be resolved

    Sample Sources

    The sample sources directory contains of the following files:
    Makefile Makefile for building the programs, depending on the distribution (mSQL for OS/2, stand-alone) equals Makefile.OS2 or Makefile.UNX
    Makefile.OS2 original Makefile for use with OS/2 and IBM C/C++ Set/2 or IBM Visual Age C++
    Makefile.UNX original Makefile for use with various UNIX OSs.
    README.TXT file with the latest news about the sample sources
    monitor.c simple SQL monitor written in C. Queries for a SQL statement, sends it to the server and shows the results.
    showtab.c another C source. Like 'monitor', but returns additional information about the result columns.

    iODBC

    This and the following sections describe the intrinsic Open Data Base Connection (iODBC) package coming along with this.

    The iODBC library ables multiple database environments without having the need to re-compile the sources. This is done by a so-called driver manager. The driver manager is a layer between application and iODBC driver, which may be a CLI library. The driver manager's task is to check a database connection string in order to call the proper iODBC driver.

    If an application wants to connect to a database db_1, it does it through the driver manager, which resolves the request by a configuration file (iodbc.ini) telling that connections to the database db_1 requires the driver Driver_A. All further access goes via driver manager to the driver A.

    Description of iODBC.INI

    The file iODBC.INI is located

  • OS/2 in the same directory as the iODBC driver manager DLL iODBC.DLL,
  • Unix as file ~/.iodbc.ini in the user's home directory.

    If is an ASCII file containing these elements:

  • lines starting with # (hash sign) are ignored (remarks)
  • lines starting with ; (semicolon) are ignored (remarks)
  • a [DSN] starts a new data source name section - a data source name is the database name or database alias-name
  • Driver = path/file - path and file name of iODBC driver
  • DSN = new data source name - if given, replaces the DSN during a SQLConnect() or SQLDriverConnect() call
  • Trace = x - enable (x = 1, on, On, ON) or disable (x = 0, off, Off, OFF) tracing
  • Tracefile = stdout | stderr - on tracing specifies the path for output; only standard output or error channel are allowed!

    Sample iODBC.INI for OS/2

    #=============================================================================#
    # iodbc.ini (OS/2)                                                            #
    #=============================================================================#
    #                                                                             #
    # sample configuration file for iODBC                                         #
    #                                                                             #
    #-----------------------------------------------------------------------------#
    #                                                                             #
    # a driver section is set up like this:                                       #
    #       [<identifier>]                                                        #
    #               Driver = <path>/<name_of_dynamic_link_library>                #
    #               DSN    = <database_name>                                      #
    #                                                                             #
    # <identifier>  ::= [A-za-z][A-Za-z0-9_]*                                     #
    #  [default]    ::= entry used, if no other data matches                      #
    #                                                                             #
    # Trace = ON | On | on | 1 | OFF | Off | off | 0                              #
    #       enable tracing of ODBC routines                                       #
    # Tracefile = stdout | stderr                                                 #
    #       output for trace information, either stdout or stderr                 #
    #                                                                             #
    #=============================================================================#
    
    [DB2]
    Comment  = sample entry for IBM DB2/2
    Trace    = Off
    TraceFile= stderr
    Driver   = c:\sqllib\dll\db2cli.dll
    DSN      = test
    
    [mSQL]
    Comment  = sample entry for Mini SQL 1.0.x
    Trace    = Off
    TraceFile= stderr
    Driver   = ..\lib\libmsql.dll
    DSN      = test@localhost
    
    [mSQL2]
    Comment  = sample entry for Mini SQL 2.0
    Trace    = Off
    TraceFile= stderr
    Driver   = ..\lib\libmsql2.dll
    DSN      = test@localhost
    
    [default]
    Comment  = default entry - used when no entry else matches
    Trace    = On
    TraceFile= stderr
    Driver   = ..\lib\libmsql2.dll
    DSN      = Minerva@localhost
    
    #=============================================================================#
    # end of file                                                                 #
    #=============================================================================#
    

    Sample iODBC.INI for Unix

    #=============================================================================#
    # iodbc.ini (Unix)                                                            #
    #=============================================================================#
    #                                                                             #
    # sample configuration file for iODBC                                         #
    #                                                                             #
    #-----------------------------------------------------------------------------#
    #                                                                             #
    # a driver section is set up like this:                                       #
    #       [<identifier>]                                                        #
    #               Driver = <path>/<name_of_dynamic_link_library>                #
    #               DSN    = <database_name>                                      #
    #                                                                             #
    # <identifier>  ::= [A-za-z][A-Za-z0-9_]*                                     #
    #  [default]    ::= entry used, if no other data matches                      #
    #                                                                             #
    # Trace = ON | On | on | 1 | OFF | Off | off | 0                              #
    #       enable tracing of ODBC routines                                       #
    # Tracefile = stdout | stderr                                                 #
    #       output for trace information, either stdout or stderr                 #
    #                                                                             #
    #=============================================================================#
    
    [mSQL]
    Comment  = sample entry for Mini SQL 1.0.x
    Trace    = Off
    TraceFile= stderr
    Driver   = ../lib/libclimSQL.so
    DSN      = test@localhost
    
    [mSQL2]
    Comment  = sample entry for Mini SQL 2.0
    Trace    = Off
    TraceFile= stderr
    Driver   = ../lib/libclimSQL2.so
    DSN      = test@localhost
    
    [Oracle]
    Comment  = sample entry for Oracle 7.x database
    Trace    = Off
    TraceFile= stderr
    Driver   = ../lib/libclioracle.so
    DSN      = test/test@testdb
    
    [default]
    Comment  = default entry - used when no entry else matches
    Trace    = On
    TraceFile= stderr
    Driver   = ../lib/libclimSQL2.so
    DSN      = Minerva@localhost
    
    #=============================================================================#
    # end of file                                                                 #
    #=============================================================================#
    

    Compatibility List

    Under OS/2 the iODBC interface has been successfully tested against

  • IBM DB2/2 2.1 Call-Level Interface (CLI) DLL db2cli.dll
  • mSQL for OS/2 1.x Call-Level Interface (CLI) DLL libmsql.dll
  • mSQL for OS/2 2.0 Call-Level Interface (CLI) DLL libmsql2.dll

    Under Unix the iODBC interface has been successfully tested against

  • mSQL 1.0.x Call-Level Interface (CLI) dynamic library libclimSQL.so
  • mSQL 2.0 Call-Level Interface (CLI) dynamic library libclimSQL2.so
  • Oracle CLI-OCI-Bridge (CLI) dynamic library libclioracle.so

    Limitations and Differences to Standard CLI/ODBC

    Depending on the version of this CLI/ODBC implementation, there are limitations and differences to the CLI or ODBC standard. Some of them, which are common to all versions, are described below, others, unique to one version, in the following sub-sections.

    There are the following limits dependingless to the version:

  • The length of the string associated to a cursor is limited to the value SQL_MAX_CURSOR_NAME (32 byte = 31 characters plus terminating NUL).
  • Under OS/2 the order functions are stored in the dynamic link libraries was changed in 1.00.4. The new order can be watched in the file LIB\ODBC.def. The old order, which was valid up to version 1.00.3 can be checked in the file LIB\ODBC_103.def. It may be necessary to re-compile programs linked against an older version of this ODBC manager!

    Limits for mSQL

    Currently these SQL and C data types are supported for mSQL:

  • SQL_CHAR (SQL_C_CHAR) equals the CHAR data type of mSQL.
  • SQL_INTEGER (SQL_C_INTEGER) equals the INT data type of mSQL.
  • SQL_REAL (SQL_C_REAL) equals the REAL data type of mSQL.

    The implementation level of routines equals ODBC core level.

    There are the following limits for mSQL for Unix and mSQL for OS/2:

  • The number of resulting colums which can be bound to variables is limited to SQL_MAX_COLUMN (64).
  • The number of input parameters on a query, insert, update, delete, etc., which can be bound to variables, is limited to SQL_MAX_PARAM (16).
  • There is a maximum size a SQL statement can reach at execution time of SQL_MAX_BUFFER_SIZE (65535) bytes - this includes the SQL statement plus all string space provided by input parameters (bounded variables).

    Limits for Oracle

    Currently these SQL and C data types are supported for Oracle 7.x:

  • SQL_CHAR (SQL_C_CHAR) equals the VARCHAR2 data type of Oracle 7.x.
  • SQL_INTEGER (SQL_C_INTEGER) equals the NUMBER data type of Oracle 7.x.
  • SQL_REAL (SQL_C_REAL) equals the NUMBER data type of Oracle 7.x.

    The implementation level of routines equals ODBC core level, except SQLColAttributes() (see below).

    There are the following limits for Oracle 7.xcolon.

  • Requires Oracle's OCI (Oracle Call Interface) at a version 7020200.1 (Feb. 15, 1995) or newer.
  • SQL_CHAR does not refer to the CHAR data type of Oracle's database, but refers to VARCHAR2. This is due to the compatibility considerations - mSQL lacks such a data type, but its CHAR data type exquals VARCHAR2. So to keep both implementations compatible, the SQL_CHAR has been defined as stated. Another point is, that Oracle changed definitions on CHAR and VARCHAR2 data types between Oracle 6.x and 7.x.
  • SQLColAttributes() does not work properly (produces core dumps), SQLDescribeCol() has to be used instead. There you have to note, that every column can be stated only one time each! This is a limitation of Oracle's OCI ...

    History

    iODBC has been ported to OS/2 from the Unix Freeware version 2.12. The mSQL 1.0.x and 2.0 CLI / iODBC drivers have been developed by Dirk Ohme.

    Over all, this is the history:

  • V 1.0.5 - 1997/01/24
  • bug-fixes in SQLNumResultCols(), SQLRowCount()
  • SQLSetParam() in iODBC driver manager new implemented
  • bug-fixes in JDBC-ODBC bridge - variable types adapted for multi-platform support (Unix)
  • support for Unix extended, iODBC drivers and manager completed
  • iODBC driver for Oracle (tm) 7.x databases revised
  • V 1.0.4 - 1997/01/09
  • fixed some bugs in the makefiles
  • adaption to Intersolv ODBC driver, so it's compatible with it - NOTE: It may be necessary to re-compile your programs that has been linked against an older version of iODBC!
  • library definition files (*.def) centralized
  • porting back to Unix
  • Oracle 7 database (i)ODBC driver (core level) separated from mSQL driver (Unix only)
  • introducing SYSLEVEL information system (OS/2 only)
  • mSQL ODBC driver updated for latest version of mSQL 2.0 (Beta 1)
  • adaption of JDBC-ODBC bridge to JDBC 1.10 specifications
  • V 1.0.3 - 1996/11/04
  • 'extended setup' feature for iODBC drivers added
  • setup procedure inst_os2 extended - automatic check for update of CONFIG.SYS added
  • configuration tool autoconf.cmd extended with ability to detect a Java Development Kit (JDK)
  • implementation of a Java DataBase Connectivity (JDBC) to ODBC bridge for use with SunSoft's JDBC driver manager under OS/2 (JDBC-ODBC-bridge)
  • bug fix in mSQL 1.x and 2.x ODBC driver - on SQLFreeStmt() with option SQL_CLOSE the statement handle was closed, but this should be only with option SQL_DROP
  • bug fix in mSQL 2.x ODBC driver - the number of resulting rows is stored, the result is gathered by msqlQuery(), the error check at msqlQuery() has been adapted for mSQL 2.x
  • SQLGetData() added to mSQL ODBC drivers
  • adaption to Solid SQL Server 2.1 - works now against their CLI
  • V 1.0.2 - 1996/09/17
  • archived by its own - no more bundling with mSQL
  • archive re-arranged, IPF source file splitted
  • mSQL 2.0 CLI / iODBC driver (core level) implemented
  • iODBC driver manager for OS/2 Presentation Manager designed
  • driver manager DLL renamed from iODBC.DLL to ODBC.DLL
  • comments added to the INI file
  • WPS installation routine added
  • V 1.0.1 - 1996/07/10
  • iODBC driver manager fixed, first working release
  • bundled with mSQL 1.0.16
  • V 1.0.0 - 1996/06/27
  • initial version of the iODBC driver manager
  • mSQL 1.0.x CLI (core level) finished

    Author's Details

    Call-Level Interface (CLI) and (i)ODBC drivers for mSQL 1.0.x and 2.0, (i)ODBC driver manager and JDBC-ODBC bridge were written by:

      Dirk Ohme
      Programmer at transtec AG
      Tübingen, Germany
    
      E-mail:  Dirk.Ohme@transtec.de
      Fidonet: 2:246/2001.9@fidonet
    

    Archive Location

    The primary site for this product is:

      Host:   www.fh-albsig.de (141.87.110.2)
      URL:    http://www.fh-albsig.de/~ohme
      Files:  FILES/iODBC*.zip (Zip 2.0.1 archive)
      Note:   OS/2 port