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:
The following Call-Level Interfaces (CLI) may be used as iODBC-compliant ODBC drivers:
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
To use the CLI properly there is a predefined path showing when to call
which routine. The next picture describes a CLI session.
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:
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!):
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 defines a boolean data type with it's two possible values
This defines a data type for storing signed / unsigned integer values,
i.e. characters.
This defines a data type for storing signed / unsigned double words.
The purpose of this data type is to store a unqualified handle to
"anything".
The purpose of this data type is to store a handle for window manipulation.
This defines a data type for storing signed / unsigned long integer
values.
This data type is intended to be used for unqualified pointer
specification. It is comparable with the ANSI-C data type void *.
This data type is intended to be used to specify an unqualified
program return code, i.e. a return code of "something".
This defines a data type for storing signed / unsigned short integer
values.
This defines a data type for storing signed / unsigned words.
This defines a data type for storing unsigned integer values.
The intention of this function qualifier is to distinguish between
function with "C" and "PASCAL" parameter/calling convention. For
example:
This qualifier is used for specification of a public call-level
interface routine (procedure).
This qualifier is used for specification of a non-public (private)
call-level interface routine (procedure).
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.
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):
And now the SQL data types:
Note: Special structures used by the iODBC:
calling parameters
return codes
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.
calling parameters
return codes
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: calling parameters
return codes
calling parameters
return codes
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().
calling parameters
return codes
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.
calling parameters
return codes
calling parameters
return codes
calling parameters
return codes
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: calling parameters
return codes
calling parameters
return codes
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().
calling parameters
return codes
calling parameters
return codes
calling parameters
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 }
Data Type CHAR, UCHAR
CHAR - signed char
UCHAR - unsigned char
Data Type DWORD, SDWORD, UDWORD
DWORD - double word
SDWORD - signed double word
UDWORD - unsigned double word
Data Type HANDLE
HANDLE - (unqualified) handle
Data Type HWND
HWND - handle (for) window
Data Type LONG, ULONG
LONG - signed long integer
ULONG - unsigned long integer
Data Type PTR
PTR - (unqualified) pointer
Data Type RETCODE
RETCODE - (unqualified) program return code
Data Type SHORT, USHORT
SHORT - signed short integer
USHORT - unsigned short integer
Data Type WORD, SWORD, UWORD
WORD - word
SWORD - signed word
UWORD - unsigned word
Data Type INT
UINT - unsigned integer
Function Qualifier PASCAL
PASCAL
void funcProto(int a, int b ); /* C calling convention */
void PASCAL funcProto(int a, int b); /* PASCAL calling convention */
Function Qualifier SQL_API
SQL_API
Function Qualifier SQL_LOCAL_API
SQL_LOCAL_API
Function Macro MAX, MIN
MAX(x,y) ((x > y) ? x : y)
MIN(x,y) ((x < y) ? x : y)
SQL Data Types
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
***SQL native*** identifier 'C' ***comment***
SQLBINARY SQL_C_BINARY unsigned char bit field of 8 bits
SQLBIT SQL_C_BIT BOOL
SQLBLOBLOCATOR SQL_C_BLOB_LOCATOR long reference
SQLCHAR SQL_C_CHAR unsigned char
SQLCLOBLOCATOR SQL_C_CLOB_LOCATOR long reference
SQLDATE - unsigned char pointer to string
SQLDBCHAR SQL_C_DBCHAR unsigned char character
SQLDBCLOBLOCATOR SQL_C_DBCLOB_LOCATOR long reference
SQLDECIMAL - unsigned char pointer to string
SQLDOUBLE SQL_C_DOUBLE double
SQLINTEGER SQL_C_LONG long
SQLFLOAT SQL_C_FLOAT float
SQLNUMERIC - unsigned char pointer to string
SQLREAL SQL_C_REAL float
SQLSCHAR SQL_C_TINYINT signed char
SQLSHORT SQL_C_SHORT short
SQLSMALLINT SQL_C_SHORT short
SQLTIME - unsigned char pointer to string
SQLTIMESTAMP - unsigned char pointer to string
SQLTINYINT SQL_C_TINYINT signed char
***SQL native*** identifier sub-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
minute minute
second second
SQLTIMESTAMP SQL_C_TIMESTAMP TIMESTAMP_STRUCT time stamp
year year
month month
day day
hour hour
minute minute
second second
fraction ???
***SQL*** Handle/Pointer 'C' ***comment***
SQLHDBC HDBC, LPDBC DBC CLI database context
SQLHDESC HDESC, LPDESC DESC CLI descriptor
SQLHENV HENV, LPENV ENV CLI environment
SQLHSTMT HSTMT, LPSTMT STMT CLI statement
BLOB stands for binary large object block.
CLOB stands for character large object block.
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
return code SQLError comment
SQL_ERROR S1001 memory allocation error
S1009 invalid pointer phdbc
SQL_INVALID_HANDLE - invalid pointer lpenv
SQL_SUCCESS - context successfully allocated
SQLAllocEnv
RETCODE SQL_API SQLAllocEnv( SQLHENV FAR *phenv
);
ODBC core level
X/Open yes
return code SQLError comment
SQL_ERROR - internal error (memory problems?)
SQL_INVALID_HANDLE - invalid pointer phenv
SQL_SUCCESS - environment successfully allocated
There is no error message (see SQLError) available.
SQLAllocHandle
ODBC
X/Open
return code SQLError comment
SQL_ERROR
SQL_INVALID_HANDLE -
SQL_SUCCESS -
SQLAllocStmt
RETCODE SQL_API SQLAllocStmt(
LPDBC lpdbc,
HSTMT FAR *phstmt);
ODBC core level
X/Open yes
return code SQLError comment
SQL_ERROR S1001 memory allocation failure
S1009 invalid pointer phstmt
SQL_INVALID_HANDLE - invalid handle lpdbc
SQL_SUCCESS - statment handle created successfully
SQLBindCol
RETCODE SQL_API SQLBindCol(
LPSTMT lpstmt,
UWORD icol,
SWORD fCType,
PTR rgbValue,
SDWORD cbValueMax,
SDWORD FAR *pcbValue);
ODBC core level
X/Open yes
return code SQLError comment
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
SQLBindParam
ODBC
X/Open
return code SQLError comment
SQL_ERROR
SQL_INVALID_HANDLE -
SQL_SUCCESS -
ODBC
X/Open
return code SQLError comment
SQL_ERROR
SQL_INVALID_HANDLE -
SQL_SUCCESS -
SQLCancel
RETCODE SQL_API SQLCancel(
LPSTMT lpstmt);
ODBC core level
X/Open yes
return code SQLError comment
SQL_INVALID_HANDLE - invalid handle lpstmt
SQL_SUCCESS - no errors
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
return code SQLError comment
SQL_ERROR
SQL_INVALID_HANDLE -
SQL_SUCCESS -
ODBC core level
X/Open yes
In this implementation for numerical data types SQL_ALL_EXCEPT_LIKE
and for character data types SQL_SEARCHABLE will be returned.
In this implementation all columns are updateable.
return code SQLError comment
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_INFO 01004 result string rgbDesc has been truncated
SQL_SUCCESS - no errors
SQLColumnPrivileges
ODBC
X/Open
return code SQLError comment
SQL_ERROR
SQL_INVALID_HANDLE -
SQL_SUCCESS -
ODBC
X/Open
return code SQLError comment
SQL_ERROR
SQL_INVALID_HANDLE -
SQL_SUCCESS -
ODBC core level
X/Open yes
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"
