For clarity, the routines are presented here with two differences from the standard. The names of the parameters of the routines are abbreviated in this appendix to make the routine headers easier to read and, in some cases, to clarify their function. In actual calls to the routines from an application program, you use the names of the application program variables to be used as input and output parameters instead of the parameter names. Also for clarity, the data types of the parameters are stated here in terms of the actual C-language data types (e.g., long, short, *char). The standard defines the parameters using defined symbolic constants (#define’s in the C language) to represent these data types.
Appendix A.1 of the standard (ISO/IEC 9075-3:1995) is a C-language header file that defines symbolic constants for all of the constants and codes specified in the standard, and uses the full parameter variable names specified in the standard. The following is a summary of the routines, organized by function:
AllocHandle() Allocates resources for environment, connection,
descriptor, or statement
FreeHandle() Frees previously allocated resources
AllocConnect() Allocates resources for a database connection
FreeConnect() Frees resources for a database connection
Connect() Establishes a database connection
Disconnect() Ends an established database connection
DataSources() Gets a list of available SQL servers to which connection
may be made
AllocEnv() Allocates resources for a SQL environment
FreeEnv() Frees resources for a SQL environment
SetEnvAttr() Sets attribute value for a SQL environment
GetEnvAttr() Retrieves attribute value for a SQL environment
AllocStmt() Allocates resources for a SQL statement
FreeStmt() Frees resources for a SQL statement
SetStmtAttr() Sets descriptor area to be used for a SQL statement
GetStmtAttr() Gets descriptor area for a SQL statement
ExecDirect() Directly executes a SQL statement
Prepare() Prepares a SQL statement for subsequent execution
Execute() Executes a previously prepared SQL statement
EndTran() Ends a SQL transaction
Cancel() Cancels execution of a SQL statement
GetDescField() Gets value of a descriptor field
SetDescField() Sets value of a descriptor field
GetDescRec() Gets values from a descriptor record
SetDescRec() Sets values in a descriptor record
CopyDesc() Copies descriptor area values
NumResultCols() Determines the number of query results columns
DescribeCol() Describes one column of query results
ColAttribute() Gets attribute of a query results column
BindParam() Binds program location to a parameter value
ParamData() Processes deferred parameter values
PutData() Provides deferred parameter value or portion of a
character string value
SetCursorName() Sets the name of a cursor
GetCursorName() Obtains the name of a cursor
Fetch() Fetches a row of query results
FetchScroll() Fetches a row of query results with scrolling
GetData() Obtains the value of a query results column
CloseCursor() Closes an open cursor
Error() Obtains error information
GetDiagField() Gets value of a diagnostic record field
GetDiagRec() Gets value of the diagnostic record
RowCount() Gets number of rows affected by last SQL statement
GetFunctions() Gets information about supported features of a SQL
implementation
GetInfo() Gets information about supported features of a SQL
implementation
GetTypeInfo() Gets information about supported data types
1. CLI Return Values
Every Call-Level Interface (CLI) routine returns a short value with one of the following values and meanings:
CLI Return Value Meaning
0 Statement completed successfully
1 Successful completion with warning
No data found (when retrieving query results)
99 Data needed (required dynamic parameter missing)
–1 Error during SQL statement execution
–2 Error—invalid handle supplied in call
2. General Handle Management Routines
These routines are used to allocate a handle for use by the CLI, and to free a previously allocated handle that is no longer needed. The allocation routine accepts an argument indicating which type of handle is to be allocated. In general, it may be preferable to use the routines that create and free the specific types of handles, described in their respective sections. These routines must be used to allocate and free application program descriptor handles.
/* Allocate a handle for use in subsequent CLI calls */
short SQLAllocHandle (
short hdlType, /* IN: integer handle type code */
long inHdl, /* IN: env or conn handle */
long *rtnHdl) /* OUT: returned handle */
/* Free a handle previously allocated by SQLAllocHandle() */
short SQLFreeHandle (
short hdlType, /* IN: integer handle type code */
long inHdl) /* IN: handle to be freed */
3. SQL Environment Management Routines
These routines are used to allocate a handle for a new SQL environment, to free an environment handle when it is no longer needed, and to retrieve and set the value of attributes associated with the SQL environment.
/* Allocate a handle for a new SQL-environment */
short SQLAllocEnv (
long *envHdl) /* OUT: returned env handle */
/* Free an environment handle previously allocated */
short SQLFreeEnv (
long envHdl) /* IN: environment handle */
/* Obtain the value of a SQL-environment attribute */
short SQLGetEnvAttr(
long envHdl, /* IN: environment handle */
long AttrCode, /* IN: integer attribute code*/
void *rtnVal, /* OUT: return value */
long bufLen, /* IN: length of rtnVal buffer */
long *strLen) /* OUT: length of actual data */
/* Set the value of a SQL-environment attribute */
short SQLSetEnvAttr(
long envHdl, /* IN: environment handle */
long AttrCode, /* IN: integer attribute code*/
void *attrVal, /* IN: new attribute value */
long *strLen) /* IN: length of data */
4. SQL Connection Management Routines
These routines are used to create, terminate, and manage a connection to a SQL server. They allocate and free the handles used to maintain connection status, set up and terminate connections, manage the attributes associated with a connection, and obtain a list of the SQL servers available for connection.
/* Allocate a handle for a new SQL-connection */
short SQLAllocConnect (
long envHdl, /* IN: environment handle */
long *connHdl) /* OUT: returned connection handle */
/* Free a connection handle previously allocated */
short SQLFreeConnect (
long connHdl) /* IN: connection handle */
/* Initiate a connection to a SQL-server */
short SQLConnect(
long connHdl, /* IN: connection handle */
char *svrName, /* IN: name of target SQL-server */
short svrnamlen, /* IN: length of SQL-server name */
char *userName, /* IN: user name for connection */
short usrnamlen, /* IN: length of user name */
char *passwd, /* IN: connection password */
short pswlen) /* IN: password length */
/* Disconnect from a SQL-server */
short SQLDisconnect(
long connHdl) /* IN: connection handle */
/* Get the name(s) of accessible SQL-servers for connection */
short SQLDataSources (
long envHdl, /* IN: environment handle */
short direction, /* IN: indicates first/next rqst */
char *svrname, /* OUT: buffer for server name */
short buflen, /* IN: length of server name buffer */
short *namlen, /* OUT: actual length of server name */
char *descrip, /* OUT: buffer for description */
short buf2len, /* IN: length of description buffer */
short *dsclen) /* OUT: actual length of description */
/* Obtain the value of a SQL-connection attribute */
short SQLGetConnectAttr(
long connHdl, /* IN: connection handle */
long AttrCode, /* IN: integer attribute code*/
void *rtnVal, /* OUT: return value */
long bufLen, /* IN: length of rtnVal buffer */
long *strLen) /* OUT: length of actual data */
/* Set the value of a SQL-connection attribute */
short SQLSetConnectAttr(
long connHdl, /* IN: connection handle */
long AttrCode, /* IN: integer attribute code*/
void *attrVal, /* IN: new attribute value *
5. SQL Statement Management Routines
These routines are used to allocate and free the handle associated with a SQL statement, to pass SQL statement text for execution, and to request preparation and actual execution of the statement via the CLI.
6. SQL Statement Execution Routines
These routines are used to pass SQL statement text to the CLI and to request SQL statement execution, either immediately or after being prepared. They also control the execution of SQL transactions and the cancellation of currently operating statements.
7. Query Results Processing Routines
These routines are used to retrieve rows of query results and to specify the application program data areas that are to receive the returned query results.
/* Advance the cursor to the next row of query results */
short SQLFetch (
long stmtHdl) /* IN: statement handle */
/* Scroll the cursor up or down through the query results */
short SQLFetchScroll (
8. Query Results Description Routines
These routines are used to obtain a description of the results of a query, including the number of columns of query results, the data type, and other attributes of each column.
9. Query Results Descriptor Management Routines
These routines are used to obtain a description of the results of a query using the CLI descriptor mechanism, and to manipulate the descriptors to manage the return of query results into application program data areas.
10. Deferred Dynamic Parameter Processing Routines
These routines are used to process deferred parameters when their values are requested by the CLI during execution of a SQL statement containing them.
11. Error, Status, and Diagnostic Routines
These routines are used to determine the reason for an error condition returned by the CLI, to determine the number of rows affected by successful statement execution, and to obtain detailed diagnostic information about error conditions.
12. CLI Implementation Information Routines
These routines return information about the specific CLI implementation, including the CLI calls, statements, and data types it supports.
13. CLI Parameter Value Codes
These codes are passed to or returned by the CLI as parameter values, to indicate handle types, data types, statement types, and so on.
Code Value
Handle type codes
SQL-environment handle 1
SQL-connection handle 2
SQL-statement handle 3
SQL-descriptor handle 4
SQL implementation data type codes
CHARACTER 1
NUMERIC 2
DECIMAL 3
INTEGER 4
SMALLINT 5
FLOAT 6
REAL 7
DOUBLE 8
DATETIME 9
INTERVAL 10
VARCHAR 12
BIT 14
Implementation-defined < 0
Application program language
data type codes
CHARACTER 1
NUMERIC 2
DECIMAL 3
INTEGER 4
SMALLINT 5
FLOAT 6
REAL 7
DOUBLE 8
Implementation-defined < 0
DateTime subcodes for SQL
data types
DATE 1
TIME 2
TIMESTAMP 3
TIME w/ ZONE 4
TIMESTAMP w/ ZONE 5
DateTime interval codes for SQL
DateTime types
YEAR 1
MONTH 2
DAY 3
HOUR 4
MINUTE 5
SECOND 6
YEAR TO MONTH 7
DAY TO HOUR 8
DAY TO MINUTE 9
DAY TO SECOND 10
HOUR TO MINUTE 11
HOUR TO SECOND 12
MINUTE TO SECOND 13
Transaction termination codes
COMMIT 0
ROLLBACK 1
FLOAT 6
REAL 7
DOUBLE 8
Implementation-defined < 0
DateTime subcodes for SQL
data types
DATE 1
TIME 2
TIMESTAMP 3
TIME w/ ZONE 4
TIMESTAMP w/ ZONE 5
DateTime interval codes for SQL
DateTime types
YEAR 1
MONTH 2
DAY 3
HOUR 4
MINUTE 5
SECOND 6
YEAR TO MONTH 7
DAY TO HOUR 8
DAY TO MINUTE 9
DAY TO SECOND 10
HOUR TO MINUTE 11
HOUR TO SECOND 12
MINUTE TO SECOND 13
Transaction termination codes
COMMIT 0
ROLLBACK 1
ColAttribute 6
Connect 7
CopyDesc 1004
DataSources 57
DescribeCol 8
Disconnect 9
EndTran 1005
Error 10
ExecDirect 11
Execute 12
Fetch 13
FetchScroll 1021
FreeConnect 14
FreeEnv 15
FreeHandle 1005
FreeStmt 16
GetConnectAttr 1007
GetCursorName 17
GetData 43
GetDescField 1008
GetDescRec 1009
GetDiagField 1010
GetDiagRec 1011
GetEnvAttr 1012
GetFunctions 44
GetInfo 45
GetStmtAttr 1014
GetTypeInfo 4
NumResultCols 18
ParamData 48
Prepare 19
PutData 49
RowCount 20
SetConnectAttr 1016
SetCursorName 21
SetDescField 1017
SetDescRec 1018
SetEnvAttr 1019
SetStmtAttr 1020
Concise data type codes
CHARACTER 1
NUMERIC 2
DECIMAL 3
INTEGER 4
SMALLINT 5
FLOAT 6
REAL 7
DOUBLE 8
VARCHAR 12
BIT 14
VARBIT 15
DATE 91
TIME 92
TIMESTAMP 93
TIME W/ ZONE 94
TIMESTAMP W/ ZONE 95
INTERVAL YEAR 101
INTERVAL MONTH 102
INTERVAL DAY 103
INTERVAL HOUR 104
INTERVAL MINUTE 105
INTERVAL SECOND 106
INTERVAL YEAR TO MONTH 107
INTERFAL DAY TO HOUR 108
INTERVAL DAY TO MINUTE 109
INTERVAL DAY TO SECOND 110
INTERVAL HOUR TO MINUTE 111
INTERVAL HOUR TO SECOND 112
INTERVAL MINUTE TO SECOND 113
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.