CLI (Call Level Interface)

CLI is defined software development standards in ISO/IEC 9075-3:2003. It defines how CLI passes SQL to database and also defines the function and specifications how the results are received and analyzed. CLI was developed in the beginning of the 1990s and created for only C and COBOL languages. The specifications of CLI are still maintained.

The well-known standard interface is ODBC (Open Database Connectivity) and it allows client programs how to be connected with the database, regardless of database types. Currently, the latest version of ODBC API is defined in the ISO and the X/Open standard as 3.52.

Table of Contents

Sample Test

Describe files in sample directory.

File Description

File Name Description
Makefile compile file
make_data.c create data.txt file for sample4_append2 case
make_suffle_data.c create data files for sample8_multi_session_multi_table case
sample1_connect.c connect with Machbase server
sample2_insert.c direct insert and retrieve data from table
sample3_prepare.c prepare insert and retrieve data from table
sample4_append1.c insert data with high speed input protocol.
sample4_append2.c read data from text file and insert data with high speed input protocol.
sample5_describe.c get column information with SQLDescribeCol().
sample6_columns.c get column information with SQLColumns().
sample8_multi_session_multi_table.c read data files and insert data with multi thread.

Go to CLI sample directory, compile sample files and run each sample.
For more information about this sample, please see the application development section.

# go to CLI sample directory.
[Mach@localhost]$ cd $MACHBASE_HOME/sample/cli

# compile sample files.
[Mach@localhost]$ make

# run sample1_connect
[Mach@localhost]$ ./sample1_connect

# run sample2_insert
[Mach@localhost]$ ./sample2_insert

# to confirm how it works, run each command.

Standard CLI Functions

For the use of the standard functions, refer to the following links.

Wikipedia
Open Group Documents

Connection String

In order to connect via CLI, connection strings are required. See below for detailed information.

** Table. Setting Up Connection Strings

Name of Connection String Description
DSN Specify the name of data source. In ODBC, it describes the section name of the file that has resource, and it defines the server name or IP address in CLI.
DBNAME Describe the database name of Machbase.
SERVER It points to a host name or IP address of the Machbase server.
NLS_USE Set the language type (reserved for future expansion)
UID User ID
PWD User password
PORT_NO The port number to connect
PORT_DIR If you are connecting via Unix domain, specify a file path that is used. (It specifies it when a path was modified in the server. Default path is already existed so no need to specify it.)
CONNTYPE It specifies the method of connecting the client and the server. 1. Connect with TCP/IP INET 2. Connect with TCP/IP Unix domain
COMPRESS Indicate whether to compress the append protocol: If the value is 0, it transmits without compression. If the value is greater than zero, it compress only when append record is larger than the value. e.g.) COMPRESS = 512 It compresses when the size of record is bigger than 512. If it's the remote connection, compression can improve the transmission performance.
SHOW_HIDDEN_COLS Decide whether to show the hidden column (_arrival_time) when conducting SELECT *. In the case of 0, the hidden column is not shown. In the case of 1, the information of the column will be output.
CONNECTION_TIMEOUT Set how long you will wait at the initial connection. It's set to 30 seconds by default. If the server response time is taking longer than 30 seconds on the initial connection, set the value larger.

CLI Connection example:

sprintf(connStr,"DSN=127.0.0.1;COMPRESS=512;UID=SYS;PWD=MANAGER;CONNTYPE=1;PORT_NO=%d", MACHBASE_PORT_NO);

if (SQL_ERROR == SQLDriverConnect( gCon, NULL,
(SQLCHAR *)connStr,
SQL_NTS,
NULL, 0, NULL,
SQL_DRIVER_NOPROMPT ))

CLI Function List

SQLAllocConnect SQLDisconnect SQLGetDescField SQLPrepare
SQLAllocEnv SQLDriverConnect SQLGetDescRec SQLPrimaryKeys
SQLAllocHandle SQLExecDirect SQLGetDiagRec SQLStatistics
SQLAllocStmt SQLExecute SQLGetEnvAttr SQLRowCount
SQLBindCol SQLFetch SQLGetFunctions SQLSetConnectAttr
SQLBindParameter+ SQLFreeConnect SQLGetInfo SQLSetDescField
SQLColAttribute SQLFreeEnv SQLGetStmtAttr SQLSetDescRec
SQLColumns SQLFreeHandle SQLGetTypeInfo SQLSetEnvAttr
SQLConnect SQLFreeStmt SQLNativeSQL SQLSetStmtAttr
SQLCopyDesc SQLGetConnectAttr SQLNumParams SQLStatistics
SQLDescribeCol SQLGetData SQLNumResultCols SQLTables

SQLAllocConnect

It allocates an environment, connection, statement, or descriptor handle. Use it after allocating handles for environments, connections, statements, and descriptors. It can be replaced with SQLAllocHandle.

Syntax

SQLRETURN SQLAllocConnect (SQLHENV aEnv, SQLHDBC *aCon)

Arguments

Table. Arguments

Type Name Input/Output Usage
SQLHENV aEnv Input Environment handle
SQLHDBC* aCon Output Pointer to connection handle

Return Values

Table. Return Values

Return Values Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination _Fail to allocate diagnostic data _Fail to allocate connection handle
SQL_INVALID_HANDLE Abnormal environment handle *Environment handle is NULL

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
HY001 Memory allocation error Fail to allocate memory
HY009 Invalid use of null pointer Unallocated connetion handle

Sample Code

#include <stdio.h>
#include <stdlib.h>
#include <mach_sqlcli.h>

void printError(SQLHENV aEnv, SQLHDBC aCon, SQLHSTMT aStmt, char* msg);

int main()
{
SQLHENV sEnv = SQL_NULL_HANDLE;
SQLHDBC sCon = SQL_NULL_HANDLE;
SQLHSTMT sStmt = SQL_NULL_HANDLE;
int sPortNumber = 5656;

if( SQLAllocEnv(&sEnv) == SQL_ERROR )
{
printError(sEnv, NULL, NULL, "SQL Alloc Env Handle error");
}

if( SQLAllocConnect(sEnv, &sCon) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Alloc Con Handle error");
}

if( SQLSetConnectAttr( sCon, SQL_ATTR_PORT_NO, &sPortNumber, sizeof(sPortNumber)) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQLSetConnectAttr error");
}

if( SQLConnect(sCon,
(SQLCHAR*)"127.0.0.1", SQL_NTS,
NULL, 0,
NULL, 0) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQLConnect error");
}

if( SQLAllocStmt(sCon, &sStmt) == SQL_ERROR )
{
printError(sEnv, sCon, sStmt, "SQL Alloc Stmt Handle error");
}

if( SQLFreeStmt( sStmt, SQL_DROP ) == SQL_ERROR )
{
printError(sEnv, sCon, sStmt, "SQL Free Stmt Handle error");
}

if( SQLDisconnect(sCon) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Disconnect error");
}

if( SQLFreeConnect( sCon ) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Free Con Handle error");
}

if( SQLFreeEnv( sEnv ) == SQL_ERROR )
{
printError(sEnv, NULL, NULL, "SQL Free Env Handle error");
}
return 0;
}

void printError(SQLHENV aEnv, SQLHDBC aCon, SQLHSTMT aStmt, char* msg)
{
SQLSMALLINT sMsgLength;
SQLCHAR sErrMsg[1024];
SQLCHAR sSqlCode[6];
SQLINTEGER sErrNo;

printf("%s\n", msg);
if( SQLError(aEnv, aCon, aStmt, sSqlCode, &sErrNo, sErrMsg, 1024, &sMsgLength) == SQL_SUCCESS )
{
printf("SQL[%s] Machbase[%d] - %s\n", sSqlCode, sErrNo, sErrMsg);
}
exit(-1);
}

SQLAllocEnv

It allocates an environment handle. It can be replaced with SQLAllocHandle.

Syntax

SQLRETURN SQLAllocEnv (SQLHENV *aEnv)

Arguments

Table. Arguments

Type Name Input/Output Usage
SQLHENV* aEnv Output Pointer to an environment handle

Return Values

Table. Return Values

Return Values Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination

Sample Code

Refer to the SQLAllocConnect in this chapter for more information.

SQLAllocHandle

It allocates handles for environments, connections, statements, or descriptors in accordance with the arguments of handle type. It can be replaced with SQLAllocEnv, SQLAllocConnect, and SQLAllocStmt.

Syntax

SQLRETURN SQL_API SQLAllocHandle (SQLSMALLINT aHandleType,
SQLHANDLE aInputHandle,
SQLHANDLE *aOutputHandlePtr)

Arguments

Table. Arguments

Type Name of Arguments Input/Output Usage
SQLSMALLINT aHandleType Input Type of handle to allocate.
SQLHANDLE aInputHandle Input Argument of input handle
SQLHANDLE* aOutputHandlePtr Output Argument of output handle pointer

Return Values

Table. Return Values

Return Values Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnomal termination
SQL_INVALID_HANDLE Abnormal connection handle

Diagnostic Codes

It has different diagnostic codes in accordance with input handle types.

Table. Diagnostic code

Type of Handle Link
SQL_HANDLE_ENV Refer to SQLAllocEnv.
SQL_HANDLE_DBC Refer to SQLAlloConnect
SQL_HANDLE_STMT Refer to SQLAllocStmt

Sample Code

#include <stdio.h>
#include <stdlib.h>
#include <mach_sqlcli.h>

void printError(SQLHENV aEnv, SQLHDBC aCon, SQLHSTMT aStmt, char* msg);

int main()
{
SQLHENV sEnv = SQL_NULL_HANDLE;
SQLHDBC sCon = SQL_NULL_HANDLE;
SQLHSTMT sStmt = SQL_NULL_HANDLE;

if( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sEnv) == SQL_ERROR )
{
printError(sEnv, NULL, NULL, "SQL Alloc Env Handle error\n");
}

if( SQLAllocHandle(SQL_HANDLE_DBC, sEnv, &sCon) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Alloc Con Handle error\n");
}

if( SQLDriverConnect(sCon, NULL,
(SQLCHAR*)"DSN=127.0.0.1;CONNTYPE=1;PORT_NO=5656", SQL_NTS,
NULL, 0,
NULL, SQL_DRIVER_NOPROMPT) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Driver Connect error");
}

if( SQLAllocHandle(SQL_HANDLE_STMT, sCon, &sStmt) == SQL_ERROR )
{
printError(sEnv, sCon, sStmt, "SQL Alloc Stmt Handle error");
}

if( SQLFreeHandle(SQL_HANDLE_STMT, sStmt ) == SQL_ERROR )
{
printError(sEnv, sCon, sStmt, "SQL Free Stmt Handle error");
}

if( SQLDisconnect(sCon) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Disconnect error");
}

if( SQLFreeHandle(SQL_HANDLE_DBC, sCon ) == SQL_ERROR )
{
printError(sEnv, sCon, NULL, "SQL Free Con Handle error");
}

if( SQLFreeHandle(SQL_HANDLE_ENV, sEnv ) == SQL_ERROR )
{
printError(sEnv, NULL, NULL, "SQL Free Env Handle error");
}
return 0;
}

void printError(SQLHENV aEnv, SQLHDBC aCon, SQLHSTMT aStmt, char* msg)
{
SQLSMALLINT sMsgLength;
SQLCHAR sErrMsg[1024];
void* sHandle;
int sHandleType = 0;

if( aStmt )
{
sHandle = aStmt;
sHandleType = SQL_HANDLE_STMT;
}
else if( aCon )
{
sHandle = aCon;
sHandleType = SQL_HANDLE_DBC;
}
else
{
sHandle = aEnv;
sHandleType = SQL_HANDLE_ENV;
}

printf("%s\n", msg);
if( SQLGetDiagField( sHandleType, sHandle, 1, SQL_DIAG_SQLSTATE, sErrMsg, sizeof(sErrMsg), &sMsgLength ) == SQL_SUCCESS )
{
printf("SQL[%s] ", sErrMsg);
}

if( SQLGetDiagField( sHandleType, sHandle, 1, SQL_DIAG_NATIVE, sErrMsg, sizeof(sErrMsg), &sMsgLength ) == SQL_SUCCESS )
{
printf("Machbase[%d] ", *(int*)&sErrMsg);
}

if( SQLGetDiagField( sHandleType, sHandle, 1, SQL_DIAG_MESSAGE_TEXT, sErrMsg, sizeof(sErrMsg), &sMsgLength ) == SQL_SUCCESS )
{
printf("%s\n", sErrMsg);
}

exit(-1);
}

SQLAllocStmt

It allocates handles for statements and connections. The maximum number of statement handles that can be connected to connection handle is 1024. It can be replaced with SQLAllocHandle.

Syntax

SQLRETURN SQLAllocStmt (SQLHDBC aCon, SQLHSTMT *aStmt)

Arguments

Table. Arguments

Type Name of Argument Input/Output Usage
SQLHDBC aCon Input Connection handle
SQLHSTMT* aStmt Output Pointer to statement handle

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination 1) Fail to allocate statement handle 2) Exceeds the maximum number of statement handle 3) Connection handle that is not connected to database 4) Unallocated statement handle
SQL_INVALID_HANDLE Abnormal connection handle *Unallocated Statement handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
HY000 General error Fail to allocate command handle
HY001 Memory allocation error The number of handle that are allocated connection handle exceeds the maximum number (1024).
HY009 Invalid use of null pointer Connection handle is not connected.
HY010 Function sequence error Statement handle that tries to access is a null pointer.

Sample Code

Refer to SQLAllocConnect for more information.

SQLBindCol

Values located in the number of each row form results of returned record are connected to handle.

Syntax

SQLRETURN SQLBindCol(SQLHSTMT aStmt,
SQLUSMALLINT aColumnNumber,
SQLSMALLINT aTargetType,
SQLPOINTER aTargetValue,
SQLLEN aBufferLength,
SQLLEN *aStrLen_or_Ind)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle
SQLUSMALLINT aColumnNumber Input Column number
SQLSMALLINT aTargetType Input Type number
SQLPOINTER aTargetValue Input Actual data pointer
SQLLEN aBufferLength Input Length of buffer
SQLLEN* aStrLen_or_Ind Output Length that is actually used

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination 1) Invalid column number 2) Invalid type number 3) Invalid buffer length
SQL_INVALID_HANDLE Abnormal termination *Unassigned statement handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
07009 Invalid descriptor index Invalid column number
HY003 Invalid application buffer type Invalid type number
HY090 Invalid string or buffer length Invalid buffer length

Sample Code

Refer to SQLTables for more information.

SQLBindParameter

It binds a variable to each parameter in the SQL statement with a "?" character. Each variable is applied to "?" of aParameterNumber. aParameterNumber is referring to the first argument and the value is set to 1.

Syntax

SQLRETURN SQLBindParameter( SQLHSTMT aStmt,
SQLUSMALLINT aParameterNumber,
SQLSMALLINT aParamType,
SQLSMALLINT aCType,
SQLSMALLINT aSqlType,
SQLULEN aColumnSize,
SQLSMALLINT aScale,
SQLPOINTER aValue,
SQLLEN aValueMax,
SQLLEN *aValueLength)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle
SQLUSMALLINT aParameterNumber Input Number of parameter
SQLSMALLINT aParamType Input Number of parameter type
SQLSMALLINT aCType Input Number of C-type
SQLSMALLINT aSqlType Input Number of SQL-type
SQLULEN aColumnSize Input Size of column
SQLSMALLINT aScale Input Number of decimal number
SQLPOINTER aValue Input Pointer to actual data
SQLLEN aValueMax Input Lenght of buffer
SQLLEN* aValueLength Output A pointer to a buffer for the parameter's length.

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination: 1) Fail to convert a pair of types. 2) Invalid SQL type 3) Fail to allocate additional parameter 4) Invalid C type 5) Unallocated aValue and aValueLength 6)The size of buffer is less than 0 or greater than 64K. 7)Invalid parameter type
SQL_INVALID_HANDLE Abnormal termination *Statement handle that is not allocated.

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
07006 Restricted data type attribute violation aCType and aSqlType pair cannot be converted.
07009 Invalid descriptor index Invalid SQL type number
HY001 Memory allocation error Failed to allocate additional parameter
HY003 Invalid application buffer type It does not support C data type.
HY009 Invalid use of null pointer aValue and aValueLength not declared.
HY090 Invalid string or buffer length The size of the buffer was less than 0 or greater than 64K.
HY105 Invalid parameter type Parameter type was invalid.

Sample Code

Refer to SQLPrepare for more information.

SQLCancel

To cancel processing on a connection or statement, use SQLCancle function. Connection between SQLHSTMT and SQLHDBC is suspended.

Syntax

SQLRETURN SQLCancel (SQLHSTMT aStmt)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination
SQL_INVALID_HANDLE Abnormal statement handle

Sample Code

//sStmt : Allocated command variables
QLCancel(sStmt);

SQLColAttribute

It returns descriptor information for a column in a result set.

Syntax

SQLRETURN SQL_API SQLColAttribute (SQLHSTMT aStatementHandle,
SQLUSMALLINT aColumnNumber,
SQLUSMALLINT aFieldIdentifier,
SQLPOINTER aCharacterAttribute,
SQLSMALLINT aBufferLength,
SQLSMALLINT *aStringLength,
SQLLEN *aNumericAttribute )

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle
SQLUSMALLINT aColumn Input Number of column
SQLUSMALLINT aDescType Input Type of attribute
SQLPOINTER aDesc Input Attribute value of character string
SQLSMALLINT aDescMax Input Buffer size of character string attribute
SQLSMALLINT* aDescLength Input Length of character string attribute
SQLLEN* aNumericDescPtr Input Value of numeric attribute

Return Values

Table. Return Values

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination *Invalid column number
SQL_INVALID_HANDLE Abnormal termination *Unallocated statement handle

Diagnostic Codes

Table. Diagnostic Codes

Code Error Description
07009 Invalid descriptor index Invalid column number

SQLColumns

It returns the list of column names in specified tables.

Syntax

SQLRETURN SQL_API SQLColumns (SQLHSTMT aStmt,
SQLCHAR *aTableQualifier,
SQLSMALLINT aTableQualifierLength,
SQLCHAR *aTableOwner,
SQLSMALLINT aTableOwnerLength,
SQLCHAR *aTableName,
SQLSMALLINT aTableNameLength,
SQLCHAR *aColumnName,
SQLSMALLINT aColumnNameLength)

Arguments

Table. Arguments

Type Name of Argument Input/Output Description
SQLHSTMT aStmt Input Statement handle
SQLCHAR* aTableQualifier Input Not used
SQLSMALLINT aTableQualifierLength Input Not used
SQLCHAR* aTableOwner Input Not used
SQLSMALLINT aTableOwnerLength Input Not used
SQLCAHR* aTableName Input Name of table
SQLSMALLINT aTableNameLength Input Length of table name
SQLCHAR* aColumnName Input Not used
SQLSMALLINT aColumnNameLength Input Not used

Return Value

Table. Return Value

Return Value Description
SQL_SUCCESS Normal termination
SQL_ERROR Abnormal termination
SQL_INVALID_HANDLE Abnormal termination *Unallocated statement handle

Sample Code

Refer to SQLColumns for more information.

/* create query
"CREATE TABLE CLI_SAMPLE(seq short, score integer, total long, percentage float, ratio double, id varchar(10), srcip ipv4, dstip ipv6, reg_date datetime, tlog text, image binary)";
*/
SQLCHAR sColName[32];
SQLSMALLINT sColType;
SQLCHAR sColTypeName[16];
SQLSMALLINT sColNameLen;
SQLSMALLINT sColTypeLen;
SQLSMALLINT sNullable;
SQLULEN sColLen;
SQLSMALLINT sDecimalDigits;
SQLLEN sOutlen;
SQLCHAR* sData;
SQLLEN sDisplaySize;
int i;

SQLSMALLINT sColumns;

if(SQLColumns(gStmt, NULL, 0, NULL, 0, "cli_sample", SQL_NTS, NULL, 0) != SQL_SUCCESS)
{
printf("sql columns error!\n");
return -1;
}

SQLBindCol(gStmt, 4, SQL_C_CHAR, sColName, sizeof(sColName), &sColNameLen);
SQLBindCol(gStmt, 5, SQL_C_SSHORT, &sColType, 0, &sColTypeLen);
SQLBindCol(gStmt, 6, SQL_C_CHAR, sColTypeName, sizeof(sColTypeName), NULL);
SQLBindCol(gStmt, 7, SQL_C_SLONG, &sColLen, 0, NULL);

printf("--------------------------------------------------------------------------------\n");
printf("%32s%16s%16s%10s\n","Name","Type","TypeName","Length");
printf("--------------------------------------------------------------------------------\n");

while( SQLFetch(gStmt) != SQL_NO_DATA )
{
printf("%32s%16d%16s%10d\n",sColName, sColType, sColTypeName, sColLen);
}
printf("--------------------------------------------------------------------------------\n");

results matching ""

    No results matching ""