Example of Extended Append Function

Machbase provides the append protocol as a method of inserting a large amount of data at a high speed. By using append protocol, let's make an example. First, let's look at the appending methods based on the types that were provided by Machbase. Each append method has its own settings, so it's easy to use any type of data. Therefore, if you know how to use these methods, it will be easy for you to write programs more efficiently. Please refer to the example below.

The name of file is sample4_append1.c.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <machbase_sqlcli.h>
#include <arpa/inet.h>

#if __linux__
#include <sys/time.h>
#endif

#if defined(SUPPORT_STRUCT_TM)
# include <time.h>
#endif

#define MACHBASE_PORT_NO 5656
#define MAX_APPEND_COUNT 0xFFFFFFFF
#define ERROR_CHECK_COUNT 100

#define ERROR -1
#define SUCCESS 0

SQLHENV gEnv;
SQLHDBC gCon;
SQLHSTMT gStmt;
SQLCHAR gErrorState[6];

void connectDB();
void disconnectDB();
void outError(const char *aMsg);
void executeDirectSQL(const char *aSQL, int aErrIgnore);
void createTable();
void appendOpen();
void appendData();
int appendClose();
time_t getTimeStamp();

int main()
{
unsigned int sCount=0;
time_t sStartTime, sEndTime;

connectDB();
createTable();

appendOpen();
sStartTime = getTimeStamp();
appendData();
sEndTime = getTimeStamp();
appendClose();

printf("timegap = %ld microseconds for %d records\n", sEndTime - sStartTime, sCount);
printf("%.2f records/second\n", ((double)sCount/(double)(sEndTime - sStartTime))*1000000);

disconnectDB();
return SUCCESS;
}

void connectDB()
{
char sConnStr[1024];

if (SQL_ERROR == SQLAllocEnv(&gEnv)) {
outError("SQLAllocEnv error!!");
}

if (SQL_ERROR == SQLAllocConnect(gEnv, &gCon)) {
outError("SQLAllocConnect error!!");
}

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

if (SQL_ERROR == SQLDriverConnect( gCon, NULL,
(SQLCHAR *)sConnStr, SQL_NTS,
NULL, 0, NULL,
SQL_DRIVER_NOPROMPT ))
{
outError("connection error\n");
}

if (SQL_ERROR == SQLAllocStmt(gCon, &gStmt) )
{
outError("AllocStmt error");
}

printf("connected ... \n");
}

void disconnectDB()
{
if( SQL_ERROR == SQLFreeStmt(gStmt, SQL_DROP) )
{
outError("SQLFreeStmt error");
}

if (SQL_ERROR == SQLDisconnect(gCon)) {
outError("disconnect error");
}
SQLFreeConnect(gCon);
SQLFreeEnv(gEnv);
}

void outError(const char *aMsg)
{
SQLINTEGER sErrorNo;
SQLSMALLINT sMsgLength;
SQLCHAR sErrorMsg[1024];

printf("ERROR : (%s)\n", aMsg);
if (SQL_SUCCESS == SQLError( gEnv, gCon, gStmt, NULL, &sErrorNo,
sErrorMsg, 1024, &sMsgLength ))
{
printf(" mach-%d : %s\n", sErrorNo, sErrorMsg);
}

if( gStmt )
{
SQLFreeStmt(gStmt, SQL_DROP);
}

if( gCon )
{
SQLFreeConnect( gCon );
}

if( gEnv )
{
SQLFreeEnv( gEnv );
}
exit(ERROR);
}

void executeDirectSQL(const char *aSQL, int aErrIgnore)
{
SQLHSTMT sStmt;

if (SQLAllocStmt(gCon, &sStmt) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
outError("AllocStmt error");
}

if (SQLExecDirect(sStmt, (SQLCHAR *)aSQL, SQL_NTS) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
printf("sql_exec_direct error[%s] \n", aSQL);
outError("sql_exec_direct error");
}

if (SQL_ERROR == SQLFreeStmt(sStmt, SQL_DROP))
{
if (aErrIgnore != 0) return;
outError("FreeStmt Error");
}
}

void createTable()
{
executeDirectSQL("DROP TABLE CLI_SAMPLE", 1);
executeDirectSQL("CREATE TABLE CLI_SAMPLE(short1 short, integer1 integer, long1 long, float1 float, double1 double, datetime1 datetime, varchar1 varchar(10), ip ipv4, ip2 ipv6, text1 text, bin1 binary)", 0);
}

void appendOpen()
{
const char *sTableName = "CLI_SAMPLE";

if( SQLAppendOpen(gStmt, (SQLCHAR *)sTableName, ERROR_CHECK_COUNT) != SQL_SUCCESS )
{
outError("SQLAppendOpen error");
}

printf("append open ok\n");
}

void appendData()
{
SQL_APPEND_PARAM sParam[11];
char sVarchar[10] = {0, };
char sText[100] = {0, };
char sBinary[100] = {0, };

memset(sParam, 0, sizeof(sParam));

/* NULL FOR ALL*/
/* fixed column */
sParam[0].mShort = SQL_APPEND_SHORT_NULL;
sParam[1].mInteger = SQL_APPEND_INTEGER_NULL;
sParam[2].mLong = SQL_APPEND_LONG_NULL;
sParam[3].mFloat = SQL_APPEND_FLOAT_NULL;
sParam[4].mDouble = SQL_APPEND_DOUBLE_NULL;
/* datetime */
sParam[5].mDateTime.mTime = SQL_APPEND_DATETIME_NULL;
/* varchar */
sParam[6].mVarchar.mLength = SQL_APPEND_VARCHAR_NULL;
/* ipv4 */
sParam[7].mIP.mLength = SQL_APPEND_IP_NULL;
/* ipv6 */
sParam[8].mIP.mLength = SQL_APPEND_IP_NULL;
/* text */
sParam[9].mText.mLength = SQL_APPEND_TEXT_NULL;
/* binary */
sParam[10].mBinary.mLength = SQL_APPEND_BINARY_NULL;
SQLAppendDataV2(gStmt, sParam);

/* FIXED COLUMN Value */
sParam[0].mShort = 2;
sParam[1].mInteger = 4;
sParam[2].mLong = 6;
sParam[3].mFloat = 8.4;
sParam[4].mDouble = 10.9;
SQLAppendDataV2(gStmt, sParam);

/* DATETIME : absolute value */
sParam[5].mDateTime.mTime = MACH_UINT64_LITERAL(1000000000);
SQLAppendDataV2(gStmt, sParam);

/* DATETIME : current */
sParam[5].mDateTime.mTime = SQL_APPEND_DATETIME_NOW;
SQLAppendDataV2(gStmt, sParam);

/* DATETIME : string format*/
sParam[5].mDateTime.mTime = SQL_APPEND_DATETIME_STRING;
sParam[5].mDateTime.mDateStr = "23/May/2014:17:41:28";
sParam[5].mDateTime.mFormatStr = "DD/MON/YYYY:HH24:MI:SS";
SQLAppendDataV2(gStmt, sParam);

/* DATETIME : struct tm format*/
sParam[5].mDateTime.mTime = SQL_APPEND_DATETIME_STRUCT_TM;
sParam[5].mDateTime.mTM.tm_year = 2000 - 1900;
sParam[5].mDateTime.mTM.tm_mon = 11;
sParam[5].mDateTime.mTM.tm_mday = 31;
SQLAppendDataV2(gStmt, sParam);

/* VARCHAR : string */
strcpy(sVarchar, "MY VARCHAR");
sParam[6].mVar.mLength = strlen(sVarchar);
sParam[6].mVar.mData = sVarchar;
SQLAppendDataV2(gStmt, sParam);

/* IPv4 : ipv4 from binary bytes */
sParam[7].mIP.mLength = SQL_APPEND_IP_IPV4;
sParam[7].mIP.mAddr[0] = 127;
sParam[7].mIP.mAddr[1] = 0;
sParam[7].mIP.mAddr[2] = 0;
sParam[7].mIP.mAddr[3] = 1;
SQLAppendDataV2(gStmt, sParam);

/* IPv4 : ipv4 from binary */
sParam[7].mIP.mLength = SQL_APPEND_IP_IPV4;
*(in_addr_t *)(sParam[7].mIP.mAddr) = inet_addr("192.168.0.1");
SQLAppendDataV2(gStmt, sParam);

/* IPv4 : ipv4 from string */
sParam[7].mIP.mLength = SQL_APPEND_IP_STRING;
sParam[7].mIP.mAddrString = "203.212.222.111";
SQLAppendDataV2(gStmt, sParam);

/* IPv6 : ipv6 from binary bytes */
sParam[8].mIP.mLength = SQL_APPEND_IP_IPV6;
sParam[8].mIP.mAddr[0] = 127;
sParam[8].mIP.mAddr[1] = 127;
sParam[8].mIP.mAddr[2] = 127;
sParam[8].mIP.mAddr[3] = 127;
sParam[8].mIP.mAddr[4] = 127;
sParam[8].mIP.mAddr[5] = 127;
sParam[8].mIP.mAddr[6] = 127;
sParam[8].mIP.mAddr[7] = 127;
sParam[8].mIP.mAddr[8] = 127;
sParam[8].mIP.mAddr[9] = 127;
sParam[8].mIP.mAddr[10] = 127;
sParam[8].mIP.mAddr[11] = 127;
sParam[8].mIP.mAddr[12] = 127;
sParam[8].mIP.mAddr[13] = 127;
sParam[8].mIP.mAddr[14] = 127;
sParam[8].mIP.mAddr[15] = 127;
SQLAppendDataV2(gStmt, sParam);
sParam[8].mIP.mLength = SQL_APPEND_IP_NULL; /* recover */

/* TEXT : string */
memset(sText, 'X', sizeof(sText));
sParam[9].mVar.mLength = 100;
sParam[9].mVar.mData = sText;
SQLAppendDataV2(gStmt, sParam);

/* BINARY : datas */
memset(sBinary, 0xFA, sizeof(sBinary));
sParam[10].mVar.mLength = 100;
sParam[10].mVar.mData = sBinary;
SQLAppendDataV2(gStmt, sParam);
}

int appendClose()
{
int sSuccessCount = 0;
int sFailureCount = 0;

if( SQLAppendClose(gStmt, &sSuccessCount, &sFailureCount) != SQL_SUCCESS )
{
outError("SQLAppendClose error");
}

printf("append close ok\n");
printf("success : %d, failure : %d\n", sSuccessCount, sFailureCount);
return sSuccessCount;
}

time_t getTimeStamp()
{
#if _WIN32 || _WIN64

#if defined(_MSC_VER) || defined(_MSC_EXTENSIONS)
#define DELTA_EPOCH_IN_MICROSECS 11644473600000000Ui64
#else
#define DELTA_EPOCH_IN_MICROSECS 11644473600000000ULL
#endif
FILETIME sFT;
unsigned __int64 sTempResult = 0;

GetSystemTimeAsFileTime(&sFT);

sTempResult |= sFT.dwHighDateTime;
sTempResult <<= 32;
sTempResult |= sFT.dwLowDateTime;

sTempResult -= DELTA_EPOCH_IN_MICROSECS;
sTempResult /= 10;

return sTempResult;
#else
struct timeval sTimeVal;
int sRet;

sRet = gettimeofday(&sTimeVal, NULL);

if (sRet == 0)
{
return (time_t)(sTimeVal.tv_sec * 1000000 + sTimeVal.tv_usec);
}
else
{
return 0;
}
#endif
}
Register and compile sample4_append1.c in makefile, then the results will be shown as follows.

[mach@localhost cli]$ make sample4_append1
gcc -c -g -W -Wall -rdynamic -fno-inline -m64 -mtune=k8 -g -W -Wall -rdynamic -fno-inline -m64 -mtune=k8 -I/home/mach/machbase_home/include -I. -L//home/mach/machbase_home/include -osample4_append1.o sample4_append1.c
gcc -m64 -mtune=k8 -L/home/mach/machbase_home/lib -osample4_append1 sample4_append1.o -lmachcli -L/home/mach/machbase_home/lib -lm -lpthread -ldl -lrt -rdynamic
[mach@localhost cli]$ ./sample4_append1
connected ...
append open ok
append close ok
success : 13, failure : 0
timegap = 48 microseconds for 13 records
270833.33 records/second
[mach@localhost cli]$

You can check what is inserted after MACH_SQL.

mach> select * from CLI_SAMPLE;
SHORT1 INTEGER1 LONG1 FLOAT1 DOUBLE1
-----------------------------------------------------------------------------------------------------------
DATETIME1 VARCHAR1 IP IP2
------------------------------------------------------------------------------------------------------------------------------
TEXT1
------------------------------------------------------------------------------------
BIN1
------------------------------------------------------------------------------------
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR 203.212.222.111 NULL
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXX
FAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFA
FAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFA
FAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFAFA
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR 203.212.222.111 NULL
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXX
NULL
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR 203.212.222.111 7F7F:7F7F:7F7F:7F7F:7F7F:7F7F:7F7F:7F7F
NULL
NULL
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR 203.212.222.111 NULL
NULL
NULL
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR 192.168.0.1 NULL
NULL
NULL
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR 127.0.0.1 NULL
NULL
NULL
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 MY VARCHAR NULL NULL
NULL
NULL
2 4 6 8.4 10.9
2000-12-31 00:00:00 000:000:000 NULL NULL NULL
NULL
NULL
2 4 6 8.4 10.9
2014-05-23 17:41:28 000:000:000 NULL NULL NULL
NULL
NULL
2 4 6 8.4 10.9
2015-04-09 16:44:11 134:256:000 NULL NULL NULL
NULL
NULL
2 4 6 8.4 10.9
1970-01-01 09:00:01 000:000:000 NULL NULL NULL
NULL
NULL
2 4 6 8.4 10.9
1970-01-01 09:00:00 000:000:000 NULL NULL NULL
NULL
NULL
[12] row(s) selected.
mach>

Now it is time to check how to append files at a high speed. It's a useful example for inserting a large amount of values of logs and packets in real work. The name of file is sample4_append2.c.

You have to store the data to "data.txt" in advance.

./make_data

If you modify the given "make_data.c", you can create suitable data.txt file for your environment.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/time.h>
#include <machbase_sqlcli.h>

#define MACHBASE_PORT_NO 5656
#define MAX_APPEND_COUNT 0xFFFFFFFF
#define ERROR_CHECK_COUNT 100

SQLHENV gEnv;
SQLHDBC gCon;
SQLHSTMT gStmt;
SQLCHAR gErrorState[6];

void connectDB();
void disconnectDB();
void outError(const char *aMsg);
void executeDirectSQL(const char *aSQL, int aErrIgnore);
void createTable();
void appendOpen();
int appendData();
void appendClose();
time_t getTimeStamp();

int main()
{
unsigned int sCount=0;
time_t sStartTime, sEndTime;

connectDB();
createTable();

appendOpen();
sStartTime = getTimeStamp();
sCount = appendData();
sEndTime = getTimeStamp();

appendClose();

printf("timegap = %ld microseconds for %d records\n", sEndTime - sStartTime, sCount);
printf("%.2f records/second\n", ((double)sCount/(double)(sEndTime - sStartTime))*1000000);

disconnectDB();

return 0;
}

void connectDB()
{
char sConnStr[1024];

if (SQL_ERROR == SQLAllocEnv(&gEnv)) {
outError("SQLAllocEnv error!!");
}

if (SQL_ERROR == SQLAllocConnect(gEnv, &gCon)) {
outError("SQLAllocConnect error!!");
}

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

if (SQL_ERROR == SQLDriverConnect( gCon, NULL,
(SQLCHAR *)sConnStr, SQL_NTS,
NULL, 0, NULL,
SQL_DRIVER_NOPROMPT ))
{
outError("connection error!!");
}

if( SQL_ERROR == SQLAllocStmt(gCon, &gStmt) )
{
outError("SQLAllocStmt error!!");
}

printf("connected ... \n");
}

void disconnectDB()
{
if( SQL_ERROR == SQLFreeStmt(gStmt, SQL_DROP) )
{
outError("SQLFreeStmt error");
}

if (SQL_ERROR == SQLDisconnect(gCon)) {
outError("disconnect error");
}

SQLFreeConnect(gCon);
SQLFreeEnv(gEnv);
}

void outError(const char *aMsg)
{
SQLINTEGER sErrorNo;
SQLSMALLINT sMsgLength;
SQLCHAR sErrorMsg[1024];

printf("ERROR : (%s)\n", aMsg);

if (SQL_SUCCESS == SQLError( gEnv, gCon, gStmt, NULL, &sErrorNo,
sErrorMsg, 1024, &sMsgLength ))
{
printf(" mach-%d : %s\n", sErrorNo, sErrorMsg);
}

if( gStmt )
{
SQLFreeStmt( gStmt, SQL_DROP );
}
if( gCon )
{
SQLFreeConnect( gCon );
}
if( gEnv )
{
SQLFreeEnv( gEnv );
}
exit(-1);
}

void executeDirectSQL(const char *aSQL, int aErrIgnore)
{
SQLHSTMT sStmt;

if (SQLAllocStmt(gCon, &sStmt) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
outError("AllocStmt error");
}

if (SQLExecDirect(sStmt, (SQLCHAR *)aSQL, SQL_NTS) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
outError("sql_exec_direct error");
}

if (SQL_ERROR == SQLFreeStmt(sStmt, SQL_DROP))
{
if (aErrIgnore != 0) return;
outError("FreeStmt Error");
}
}

void createTable()
{
executeDirectSQL("DROP TABLE CLI_SAMPLE", 1);
executeDirectSQL("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)", 0);

printf("table created\n");
}

void appendOpen()
{
const char *sTableName = "CLI_SAMPLE";

if( SQLAppendOpen(gStmt, (SQLCHAR *)sTableName, ERROR_CHECK_COUNT) != SQL_SUCCESS )
{
outError("SQLAppendOpen error!!");
}

printf("append open ok\n");
}

int appendData()
{
FILE *sFp;
char sBuf[1024];
int j;
char *sToken;
unsigned int sCount=0;
SQL_APPEND_PARAM sParam[11];

sFp = fopen("data.txt", "r");
if( !sFp )
{
printf("file open error\n");
exit(-1);
}

printf("append data start\n");

memset(sBuf, 0, sizeof(sBuf));

while( fgets(sBuf, 1024, sFp ) != NULL )
{
if( strlen(sBuf) < 1)
{
break;
}

j=0;
sToken = strtok(sBuf,",");

while( sToken != NULL )
{
memset(sParam+j, 0, sizeof(sParam));
switch(j){
case 0 : sParam[j].mShort = atoi(sToken); break; //short
case 1 : sParam[j].mInteger = atoi(sToken); break; //int
case 2 : sParam[j].mLong = atol(sToken); break; //long
case 3 : sParam[j].mFloat = atof(sToken); break; //float
case 4 : sParam[j].mDouble = atof(sToken); break; //double
case 5 : //string
case 9 : //text
case 10 : //binary
sParam[j].mVar.mLength = strlen(sToken);
strcpy(sParam[j].mVar.mData, sToken);
break;
case 6 : //ipv4
case 7 : //ipv6
sParam[j].mIP.mLength = SQL_APPEND_IP_STRING;
strcpy(sParam[j].mIP.mAddrString, sToken);
break;
case 8 : //datetime
sParam[j].mDateTime.mTime = SQL_APPEND_DATETIME_STRING;
strcpy(sParam[j].mDateTime.mDateStr, sToken);
sParam[j].mDateTime.mFormatStr = "DD/MON/YYYY:HH24:MI:SS";
break;
}

sToken = strtok(NULL, ",");

j++;
}
if( SQLAppendDataV2(gStmt, sParam) != SQL_SUCCESS )
{
printf("SQLAppendData error\n");
return 0;
}
if ( ((sCount++) % 10000) == 0)
{
printf(".");
}

if( ((sCount) % 100) == 0 )
{
if( SQLAppendFlush( gStmt ) != SQL_SUCCESS )
{
outError("SQLAppendFlush error");
}
}
if (sCount == MAX_APPEND_COUNT)
{
break;
}
}

printf("\nappend data end\n");

fclose(sFp);

return sCount;
}

void appendClose()
{
int sSuccessCount = 0;
int sFailureCount = 0;

if( SQLAppendClose(gStmt, &sSuccessCount, &sFailureCount) != SQL_SUCCESS )
{
outError("SQLAppendClose error");
}

printf("append close ok\n");
printf("success : %d, failure : %d\n", sSuccessCount, sFailureCount);
}

time_t getTimeStamp()
{
struct timeval tv;
gettimeofday(&tv, NULL);
return tv.tv_sec*1000000+tv.tv_usec;
}
Register and compile "sample4_append2.c" in makefile, then the results will be shown as follows.

[mach@localhost cli]$ make
gcc -c -g -W -Wall -rdynamic -fno-inline -m64 -mtune=k8 -g -W -Wall -rdynamic -fno-inline -m64 -mtune=k8 -I/home/mach/machbase_home/include -I. -L//home/mach/machbase_home/include -osingle_append2.o single_append2.c
gcc -m64 -mtune=k8 -L/home/mach/machbase_home/lib -osingle_append2 single_append2.o -lmachcli -L/home/mach/machbase_home/lib -lm -lpthread -ldl -lrt -rdynamic
[mach@localhost cli]$ ./single_append2
connected ...
table created
append open ok
append data start
....................................................................................................
append data end
append close ok
success : 1000000, failure : 0
timegap = 1641503 microseconds for 1000000 records
609197.79 records/second
[mach@localhost cli]$

Example of Obtaining Information of Table Column

There are various methods to obtain information regarding table columns. However, here, we will check only two of them: SQLDescribeCol and SQLColumns.

SQLDescribeCol

SQLDescribeCol returns the result descriptor - column name, type, column size, and buffer size - for one column in the result set.

The name of example file is sample5_describe.c.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <machbase_sqlcli.h>
#include <time.h>

#define MACHBASE_PORT_NO 5656

SQLHENV gEnv;
SQLHDBC gCon;
SQLHSTMT gStmt;
SQLCHAR gErrorState[6];

void connectDB()
{
char connStr[1024];

SQLINTEGER errNo;
SQLSMALLINT msgLength;
SQLCHAR errMsg[1024];

if (SQL_ERROR == SQLAllocEnv(&gEnv)) {
printf("SQLAllocEnv error!!\n");
exit(1);
}

if (SQL_ERROR == SQLAllocConnect(gEnv, &gCon)) {
printf("SQLAllocConnect error!!\n");
SQLFreeEnv(gEnv);
exit(1);
}

sprintf(connStr,"DSN=127.0.0.1;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 ))
{
printf("connection error\n");

if (SQL_SUCCESS == SQLError ( gEnv, gCon, NULL, NULL, &errNo,
errMsg, 1024, &msgLength ))
{
printf(" mach-%d : %s\n", errNo, errMsg);
}
SQLFreeEnv(gEnv);
exit(1);
}

if (SQLAllocStmt(gCon, &gStmt) == SQL_ERROR)
{
outError("AllocStmt error", gStmt);
}

printf("connected ... \n");

}

void disconnectDB()
{
SQLINTEGER errNo;
SQLSMALLINT msgLength;
SQLCHAR errMsg[1024];

if (SQL_ERROR == SQLDisconnect(gCon)) {
printf("disconnect error\n");

if( SQL_SUCCESS == SQLError( gEnv, gCon, NULL, NULL, &errNo,
errMsg, 1024, &msgLength ))
{
printf(" mach-%d : %s\n", errNo, errMsg);
}
}

SQLFreeConnect(gCon);
SQLFreeEnv(gEnv);
}

void outError(const char *aMsg, SQLHSTMT stmt)
{
SQLINTEGER errNo;
SQLSMALLINT msgLength;
SQLCHAR errMsg[1024];

printf("ERROR : (%s)\n", aMsg);

if (SQL_SUCCESS == SQLError( gEnv, gCon, stmt, NULL, &errNo,
errMsg, 1024, &msgLength ))
{
printf(" mach-%d : %s\n", errNo, errMsg);
}
exit(-1);
}

void executeDirectSQL(const char *aSQL, int aErrIgnore)
{
SQLHSTMT stmt;

if (SQLAllocStmt(gCon, &stmt) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
outError("AllocStmt error", stmt);
}

if (SQLExecDirect(stmt, (SQLCHAR *)aSQL, SQL_NTS) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
printf("sql_exec_direct error[%s] \n", aSQL);
outError("sql_exec_direct error", stmt);
}

if (SQL_ERROR == SQLFreeStmt(stmt, SQL_DROP))
{
if (aErrIgnore != 0) return;
outError("FreeStmt Error", stmt);
}
}

void createTable()
{
executeDirectSQL("DROP TABLE CLI_SAMPLE", 1);
executeDirectSQL("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)", 0);

}

int main()
{
char sSqlStr[] = "select * from cli_sample";
SQLCHAR sColName[32];
SQLSMALLINT sColType;
SQLSMALLINT sColNameLen;
SQLSMALLINT sNullable;
SQLULEN sColLen;
SQLSMALLINT sDecimalDigits;
SQLLEN sOutlen;
SQLCHAR* sData;
SQLLEN sDisplaySize;
int i;

SQLSMALLINT sColumns;

connectDB();

createTable();

if(SQLPrepare(gStmt, (SQLCHAR*)sSqlStr, SQL_NTS))
{
outError("sql prepare fail", gStmt);
return -1;
}

if(SQLNumResultCols(gStmt, &sColumns) != SQL_SUCCESS )
{
printf("get col length error \n");
return -1;
}

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

for(i = 0; i < sColumns; i++)
{
SQLDescribeCol(gStmt,
(SQLUSMALLINT)(i + 1),
sColName,
sizeof(sColName),
&sColNameLen,
&sColType,
(SQLULEN *)&sColLen,
&sDecimalDigits,
(SQLSMALLINT *)&sNullable);

printf("%32s%16d%10d\n",sColName, sColType, sColLen);
}

printf("----------------------------------------------------------------\n");

disconnectDB();

return 0;
}

If you add the above file and execute "make", you can see the results as shown below.

[mach@localhost cli]$ make

[mach@localhost cli]$ ./sample5_describe
connected ...
----------------------------------------------------------------
Name Type Length
----------------------------------------------------------------
SEQ 5 5
SCORE 4 10
TOTAL -5 19
PERCENTAGE 6 27
RATIO 8 27
ID 12 10
SRCIP 2104 15
DSTIP 2106 60
REG_DATE 9 31
TLOG 2100 67108864
IMAGE -2 67108864
----------------------------------------------------------------
[mach@localhost cli]$

SQLColumns

SQLColumns returns the list of column names in specified tables. It returns this information as a result set as well. Machbase supports SQLColumns so that a user can select and analyze information of each column.

The name of file is sample6_columns.c.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <machbase_sqlcli.h>

#include <time.h>

#define MACHBASE_PORT_NO 5656

SQLHENV gEnv;
SQLHDBC gCon;
SQLHSTMT gStmt;
SQLCHAR gErrorState[6];

void connectDB()
{
char connStr[1024];

SQLINTEGER errNo;
SQLSMALLINT msgLength;
SQLCHAR errMsg[1024];

if (SQL_ERROR == SQLAllocEnv(&gEnv)) {
printf("SQLAllocEnv error!!\n");
exit(1);
}

if (SQL_ERROR == SQLAllocConnect(gEnv, &gCon)) {
printf("SQLAllocConnect error!!\n");
SQLFreeEnv(gEnv);
exit(1);
}

sprintf(connStr,"DSN=127.0.0.1;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 ))
{
printf("connection error\n");

if (SQL_SUCCESS == SQLError ( gEnv, gCon, NULL, NULL, &errNo,
errMsg, 1024, &msgLength ))
{
printf(" mach-%d : %s\n", errNo, errMsg);
}
SQLFreeEnv(gEnv);
exit(1);
}

if (SQLAllocStmt(gCon, &gStmt) == SQL_ERROR)
{
outError("AllocStmt error", gStmt);
}

printf("connected ... \n");

}

void disconnectDB()
{
SQLINTEGER errNo;
SQLSMALLINT msgLength;
SQLCHAR errMsg[1024];

if (SQL_ERROR == SQLDisconnect(gCon)) {
printf("disconnect error\n");

if( SQL_SUCCESS == SQLError( gEnv, gCon, NULL, NULL, &errNo,
errMsg, 1024, &msgLength ))
{
printf(" mach-%d : %s\n", errNo, errMsg);
}
}

SQLFreeConnect(gCon);
SQLFreeEnv(gEnv);
}

void outError(const char *aMsg, SQLHSTMT stmt)
{
SQLINTEGER errNo;
SQLSMALLINT msgLength;
SQLCHAR errMsg[1024];

printf("ERROR : (%s)\n", aMsg);

if (SQL_SUCCESS == SQLError( gEnv, gCon, stmt, NULL, &errNo,
errMsg, 1024, &msgLength ))
{
printf(" mach-%d : %s\n", errNo, errMsg);
}
exit(-1);
}

void executeDirectSQL(const char *aSQL, int aErrIgnore)
{
SQLHSTMT stmt;

if (SQLAllocStmt(gCon, &stmt) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
outError("AllocStmt error", stmt);
}

if (SQLExecDirect(stmt, (SQLCHAR *)aSQL, SQL_NTS) == SQL_ERROR)
{
if (aErrIgnore != 0) return;
printf("sql_exec_direct error[%s] \n", aSQL);
outError("sql_exec_direct error", stmt);
}

if (SQL_ERROR == SQLFreeStmt(stmt, SQL_DROP))
{
if (aErrIgnore != 0) return;
outError("FreeStmt Error", stmt);
}
}

void createTable()
{
executeDirectSQL("DROP TABLE CLI_SAMPLE", 1);
executeDirectSQL("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)", 0);
}

int main()
{
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;

connectDB();

createTable();

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");

disconnectDB();

return 0;
}

Add the file above and execute "make". Results are as follows.

[mach@localhost cli]$ make

[mach@localhost cli]$ ./sample6_columns
connected ...
--------------------------------------------------------------------------------
Name Type TypeName Length
--------------------------------------------------------------------------------
_ARRIVAL_TIME 93 DATE 31
SEQ 5 SMALLINT 5
SCORE 4 INTEGER 10
TOTAL -5 BIGINT 19
PERCENTAGE 6 FLOAT 27
RATIO 8 DOUBLE 27
ID 12 VARCHAR 10
SRCIP 2104 IPV4 15
DSTIP 2106 IPV6 60
REG_DATE 93 DATE 31
TLOG 2100 TEXT 67108864
IMAGE -2 BINARY 67108864
--------------------------------------------------------------------------------
[mach@localhost cli]$

results matching ""

    No results matching ""