Introduction to tbCLI
Overview
tbCLI is a Call Level Interface(CLI) provided by Tibero and works as an SQL interface between user application programs and Tibero. Users can use the tbCLI library to create a C or C++ application program which accesses Tibero.
tbCLI is developed based on ODBC (Open Database Connectivity) and X/Open Call Level Interface Standard. tbCLI satisfies all conditions of Level 2 of ODBC and most of conditions of Level 2 of ODBC
3.0. Therefore, the existing application programs written with ODBC or CLI can be easily migrated to the tbCLI environment.
tbCLI is especially useful in a client/server environment, as the following shows.
[Figure 1] Client/Server Environment
If a client application program calls a tbCLI API, the database system processes it and returns the result to the client. Interfaces like tbESQL can be used to process data, but through tbCLI, application programs and data can be manipulated in a more sophisticated way.
The tbCLI has the following features.
Precompiler is not required to create an executable file.
Like tbESQL, tbCLI combines advantages of general program languages and SQL statements. The tbCLI is closer to general programs than tbESQL.
Through tbCLI, modules can be managed efficiently, and readability is enhanced.
No need to bind an application package.
Statistics of the database can be used.
The tbCLI ensures stability of threads to enable multi-threaded application programs to be developed
Components
This section describes the basic components a user is required to know before creating or executing the tbCLI program.
tbCLI Handle
Handle is a pointer for one of some major data structures managed by tbCLI. By using a handle, tbCLI can manage complicated data more easily.
The Internal data of a handle is automatically updated when a change occurs to the data structure. Therefore, an application program developer needs neither to know the details of the data structure, nor to access the internal data directly.
tbCLI uses four handles as follows:
Environment handle Includes data about tbCLI program environment. Includes data such as the current environment state, a list of connection handles allocated to the environment, and error information for the environment.
Connection handle Includes data about connections with data source. Includes data such as the current connection state, a list of statement handles allocated to the connection, and error information for the connection.
Statement handle Includes data about an SQL statement that will be executed from the tbCLI program. Includes data such as the current statement state, a list of input parameters and output columns within the statement, and error information for the statement.
Descriptor handle Includes data about each column or bound parameters of the result set related to a statement handle. Includes data such as: input parameters of an SQL statement and metadata for output columns.
tbCLI Function
In order to execute a database job from a tbCLI program, use the tbCLI functions. Most of tbCLI functions receive the target handle as an input parameter and have an SQLRETURN type return code.
The following is a prototype of the SQLExecDirect function among tbCLI functions. The function can execute an SQL statement directly.
SQLRETURN SQLExecDirect(SQLHSTMT StatementHandle, SQLCHAR *SQLString,
SQLINTEGER SQLStringSize);tbCLI functions are divided into several groups by functions: functions related to handle allocation and connection, functions related to SQL statement execution, functions related to SQL query results and research, functions related to descriptors, functions related to error information, and functions related to data source information and others.
tbCLI Error Message
The tbCLI program gets the execution result by the code returned after executing a tbCLI function, but the user may require more information. In order to offer such information, tbCLI creates a diagnostic record.
Diagnostic record has not only the return code of the executed function but also a variety of information about the execution result.
Header record Consists of return code, row count, status record count, and executed command type. Except in the case where the return code is SQL_INVALID_HANDLE, if a tbCLI function is executed, the header record is always created.
Status record Status record includes information about warnings and errors. Created when the return code is SQL_ERROR, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_NEED_DATA, or SQL_STILL_EXECUTING.
One of the most important fields of a status record is SQLSTATE. As standardization of error or warning code, the field values are defined by X/Open and ISP/IEC standards. Format is CCSSS, five-digit character string. CC refers to error class and SSS refers to sub class
A Diagnostic record starts with one head record, and one or more status records are added. In order to get a value from a diagnostic record, the SQLGetDiagRec and SQLGetDiagField functions are required. These two functions only return the diagnostic record information included in the handle given as a parameter. Diagnostic records are used and managed in environment, connection, statement and descriptor handles.
SQLGetDiagField
Used to get the information from a single field among the diagnostic records. Can be used for both header record and status record.
SQLGetDiagRec
Used to get multiple field values such as SQLSTATE code, error code, and diagnostic message, which are included in status record. Can be used only in status record.
Program Structure
The tbCLI program has basically the following structure.
[Figure 2] tbCLI program structure
Starting setting
SQL query execution and error handling
Ending setting
Starting setting
Before starting a tbCLI program, perform initialization settings. To perform initialization settings, allocate the environment and connection handles, and then implement an actual connection to a datasource. Here, a datasource refers to the whole composition of software and hardware of Tibero.
Starting a tbCLI program is as follows:
[Ex. 1] tbCLI Starting Settings for tbCLI Program
SQLHENV h_env;
SQLHDBC h_dbc;
SQLRETURN rc = SQL_SUCCESS;
...
rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &h_env); ... ① ...
if (rc != SQL_SUCCESS) ...
rc = SQLAllocHandle(SQL_HANDLE_DBC, h_env, &h_dbc); ... ② ...
if (rc != SQL_SUCCESS) ...
rc = SQLConnect(h_dbc, (SQLCHAR *)ds_name, SQL_NTS, (SQLCHAR *)user,
SQL_NTS, (SQLCHAR *)passwd, SQL_NTS); ... ③ ...
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) ...①, ② Allocate the environment handle and the connection handle for initialization settings.
③ Connect to a datasource through SQLConnect function. In order to call this function, data source name (ds_name), user name (user), and password (passwd) must also be sent as parameters. In addition to this, parameter length must also be set up. In [Ex. 1], the null-terminating string, namely SQL_NTS, is set up instead of length.
④ (4) If a datasource is connected, the tbCLI program must allocate at least one statement handle in order to execute an SQL statement.
SQLHSTMT h_stmt;
...
rc = SQLAllocHandle(SQL_HANDLE_STMT, h_dbc, &h_stmt); ... ④ ...
if (rc != SQL_SUCCESS) ...SQL Query Execution and Error Handling
There are two ways to execute an SQL statement.
Direct Execution
Executes an SQL statement at once by using the SQLExecDirect function.
Performing direct execution is as follows:
[Ex. 2] SQL query execution of tbCLI program - Direct execution
SQLCHAR *update = "UPDATE EMP SET SALARY = SALARY * 1.05 "
"WHERE DEPTNO = 5";
rc = SQLExecDirect(h_stmt, update, SQL_NTS);
if (rc != SQL_SUCCESS) ...Prepared Execution
Executes through two steps by using the SQLPrepare and SQLExecute functions.
If most of the SQL statements include parameters, use the prepared execution method. Call the SQLBindParameter function between the SQLPrepare and SQLExecute functions to set up the actual value of a parameter.
Executing an SQL statement including two input parameters is as follows:
[Ex. 3] SQL query execution of tbCLI program - Prepared execution
SQLCHAR *update = "UPDATE EMP SET SALARY = SALARY * ? "
"WHERE DEPTNO = ?";
double ratio = 0.0;
short deptno = 0;
...
rc = SQLPrepare(h_stmt, update, SQL_NTS); ... ① ...
if (rc != SQL_SUCCESS) ...
rc = SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE, 5, 2, &ratio, 0, NULL); ... ⓐ ...
if (rc != SQL_SUCCESS) ...
rc = SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_SHORT,
SQL_SMALLINT, 0, 0, &deptno, 0, NULL); ... ⓑ ...
if (rc != SQL_SUCCESS) ...
ratio = 1.05;
deptno = 5;
SQLExecute(h_stmt); ... ② ...
if (rc != SQL_SUCCESS) ...In the previous example, input parameters in the SQL statement are represented as a question mark (?). To mark the input parameter position, use 1 or a larger integer.
① (1) Set up a pointer variable which contains each input parameter included in the prepared SQL statement. Set the input/output direction, C or C++ data type, SQL data type, precision, and scale for input parameter (ⓐ, ⓑ)
② Execute the SQL statement. If this statement is executed, the EMP table is updated by the configured input parameters
Use the SQLRowCount function to check how many rows are updated after executing the SQL statement.
The following shows how to use the function
rc = SQLRowCount(h_stmt, &count);The Return code of each function has information about the execution result, and therefore a user must check the return code after calling the function.
Ending Settings
To terminate a tbCLI program, perform the opposite of the steps described in “Starting Settings”.
Terminating a tbCLI program is as follows:
[Ex. 4] Ending Settings for tbCLI Program
rc = SQLDisconnect(h_dbc); ... ① ...
if (rc != SQL_SUCCESS) ...
SQLFreeHandle(SQL_HANDLE_DBC, h_dbc); ... ② ...
SQLFreeHandle(SQL_HANDLE_ENV, h_env); ... ③ ...① (1) Disconnect the datasource.
②, ③ Return the allocated connection handle and the environment handle to the system.
Last updated