Extended CLI function (Append)

Machbase provides extended CLI functions to materialize the append protocol which is provided for inserting data at ultra high-speed into the server. There are four types of extended functions: open channel, data entry channel, flush channel, and close channel.

Understanding Append Protocol

The "append" protocol is operated asynchronously. Asynchronous operations mean that the client's requests to the server are not completely synchronized rather it would be operated when an event occurs. Even though the client performs the append protocol, you cannot retrieve the result and check it right away. You can check it at a specified time when the server is ready. For this reason, you have to understand the interior operation as the following while you are developing applications using the append protocol. The following contents will explain when/how the server detects an asynchronous error and give it back to the user.

Sending Data

In general, Machbase sends results from the server back to the client immediately when calling SQLExecute() or SQLExecDirect(). However, SQLAppendDataV2() doesn't response immediately after the user inserts data. Instead, it sends data to the client all at once after the client communication buffer is filled. The reason for this is that Machbase is using buffering method to transfer data at ultra high-speed assuming that the volume of data input from clients will be hundreds of thousands data per second. If a user wants to send data from a buffer before it is filled, call SQLAppendFlush() function and insert data explicitly.

Error Check

As mentioned above, append is asynchronous protocol so that data is buffered. Especially, when there is no error, no response from the server. When only an error occurs, an error check is conducted. Therefore, it's very important to understand when and how an error is detected. In addition, the cost to detect errors is relatively large, so it's very inefficient to check every time a record is inserted. Now, Machbase detects errors in the following three cases.

Error callback functions which the user set, are called each time the error is detected.

  • Error check after the send buffer is filled and the data is explicitly sent to server.
  • Error check after sending data explicitly to server within SQLAppendFlush().
  • Error check right before SQLAppendClose() is closing.

Basically, it detects errors in the three cases above and designed to minimize the occurrence of I/O.

Additional Options to Check Server Error

The error detection method was set as default in order to achieve maximum performance. However, a user can check errors more often if required. In other words, you have to adjust the values of the last argument of SQLAppendOpen() function, aErrorCheckCount. If the value is 0, the default will be executed without additional error checks. However, if this value is larger than 0, it is set to check errors each time SQLAppendData() is called. That is, if the value is 10, it pays the costs of checking errors for append operations 10 times. Therefore, if the value is smaller, you have to adjust the number adequately because it uses a lot of system resources for detecting errors.

Record Trace Logs When Server Error Occurs

If you want to record a trace log of the data, which occurred errors, the property DUMP_APPEND_ERROR is set as 1. If it's set to 1, it keeps the record that caused error of machbase.trc file in the form of file. If the error occurs too many times, the use of system resources increases dramatically and then it can drop overall performance of Machbase.

Append Functions

SQLAppendOpen

This function opens a channel to the destination table. The channel remains open if the channel is not closed. It can set up to 1024 statements for one connection. It can use the SQLAppendOpen for each statement.

SQLRETURN SQLAppendOpen(SQLHSTMT aStatementHandle,
SQLCHAR *aTableName,
SQLINTEGER aErrorCheckCount );
  • aStatementHandle : It is the statement handle to append.
  • aTableName : It indicates the name of the target table to append.
  • aErrorCheckCount : It sets the number of the error check to detect server errors based on the number of input. If the number is 0, the error check is not required.

SQLAppendData (Deprecated in Version 2.0)

It is a function to insert data for the channel.

SQLRETURN SQLAppendData(SQLHSTMT StatementHandle, void *aData[]);

aData is an array that contains a pointer to the data to be entered.
The number of the array must match with the number of columns in the specified table when it is opened.

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, and SQL_ERROR are available for return values.
When SQL_SUCCESS_WITH_INFO is returned, there might be an issue such as the entered specific column is cut due to its length is too long. Thus, it would be better to check the result again.

Data set based on types

Types of numeric and text

  • Types such as float, double, short, int, long long, and char *work fine by setting the pointer to the values.

Type of address

  • In the case of IPv4, it sets 5-byte array of unsigned char.
  • The first byte is set to 4, and the subsequent four bytes are set to sequential addresses.
  • For example, in the case of 127.0.0.1, it will be set to 5-byte array of 0x04, 0x7f, 0x00, 0x00, and 0x01.

When the table has 4 columns such as short(16), int(32), long(64), and varchar,

func()
{
short val1 = 0;
int val2 = 1;
long long val3 = 2;
char *val4 = "my string";
void *valueArray[4];

valueArray[0] = (void *)&val1;
valueArray[1] = (void *)&val2;
valueArray[2] = (void *)&val3;
valueArray[3] = (void *)val4;

 SQLAppendData(aStmt, valueArray);

}

Data set based types

Type of datetime Machbase stores data records in nanoseconds so that it needs the transformation process when setting the time in the client, and it is displayed with 64-bit unsigned integer value. Therefore, you have to use "mktime" which is the Unix library for the proper conversion and add the nano after converting it to seconds.

Machbase time = (The total time starting from January 1, 1970, (seconds)) 1,000,000,000 + milli-second 1,000,000 + micro-second * 1000 + nano-second;

For date string in the form of "year-month-day hour:min:sec milli:micro:nano"

function(char *aDateString)
{
int yy, int mm, int dd, int hh, int mi, int ss;
unsigned long t1;
void *valueArray[5];
sscanf(aDateString, "%d-%d-%d %d:%d:%d %d:%d:%d",
&yy, &mm, &dd, &hh, &mi, &ss, &mmm, &uuu, &nnn);
sTm.tm_year = yy - 1900;
sTm.tm_mon = mm - 1;
sTm.tm_mday = dd;
sTm.tm_hour = hh;
sTm.tm_min = mi;
sTm.tm_sec = ss;
t1 = mktime(&sTm);
t1 = t1 * 1000000000L;
t1 = t1 + (mmm*1000000L) + (uuu*1000) + nnn;

valueArray[4] = &t1;
SQLAppendData(aStmt, valueArray);
}

SQLAppendDataByTime (Deprecated in Version 2.0)

It is a function to insert data for the channel and can set "_arrival_time" as the value of specific time rather than the current time when inserting data. For example, this function is used when you want to insert log files of one month ago with the then time rather than the current time.

SQLRETURN SQLAppendDataByTime(SQLHSTMT StatementHandle, SQLBIGINT aTime, void *aData[]);

aTime is the time value that is set to _arrival_time.
aData is an array that contains a pointer to the data to be entered.
The number of array must match with the number of column in the specified table when it's opened.

For detailed information, please refer to SQLAppendData() function.
In the case of the table that has 4 columns(short, int, long, varchar)
func()
{
long long sTime = 1;
short val1 = 0;
int val2 = 1;
long long val3 = 2;
char *val4 = "my string";
void *valueArray[4];

valueArray[0] = (void *)&val1;
valueArray[1] = (void *)&val2;
valueArray[2] = (void *)&val3;
valueArray[3] = (void *)val4;

SQLAppendDataByTime(aStmt, sTime, valueArray);

}

SQLAppendDataV2

It is introduced from Machbase 2.0 version and was greatly upgraded from the existing function to provide more convenience for data input. Especially, you can only insert the added TEXT and BINARY types SQLAppendDataV2() only.

  • Able to insert NULL for each data type.
  • The length of the string can be specified for VARCHAR data type.
  • For IPv4 and IPv6, both binary and string types of data can be entered.
  • It can specify the length of the text and binary types.
SQLRETURN SQLAppendDataV2(SQLHSTMT StatementHandle, SQL_APPEND_PARAM *aData);

aData is a pointer that indicates the arrary argument, SQL_APPEND_PARM. The number of arrary must match with the number of columns in the specified table when it's opened.

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, and SQL_ERROR are available for return values.

When SQL_SUCCESS_WITH_INFO is returned, there might be an issue such as the entered specific column is cut due to its length is too long. Thus, it would be better to check the result again.

The following is the definition of SQL_APPEND_PARAM used in SQLAppendDataV2. The content is included in mach_sqlcli.h.

SQL_APPEND_PARAM Structure (macro definition of machAppendParam)
typedef struct machAppendVarStruct
{
unsigned int mLength;
void *mData;
} machAppendVarStruct;

/* for IPv4, IPv6 as bin or string representation */
typedef struct machAppendIPStruct
{
unsigned char mLength; /* 0:null, 4:ipv4, 8:ipv6, 255:string representation */
unsigned char mAddr[16];
char *mAddrString;
} machAppendIPStruct;

/* Date time*/
typedef struct machAppendDateTimeStruct
{
long long mTime;
struct tm mTM;
char *mDateStr;
char *mFormatStr;
} machAppendDateTimeStruct;

typedef union machAppendParam
{
short mShort;
int mInteger;
long long mLong;
float mFloat;
double mDouble;
machAppendIPStruct mIP;
machAppendVarStruct mVar; /* for all varying type */
machAppendVarStruct mVarchar; /* alias */
machAppendVarStruct mText; /* alias */
machAppendVarStruct mBinary; /* binary */
machAppendVarStruct mBlob; /* reserved alias */
machAppendVarStruct mClob; /* reserved alias */
machAppendDateTimeStruct mDateTime;
} machAppendParam;

#define SQL_APPEND_PARAM machAppendParamInsert Date and Time Data Types

As you can see, there is a union named machAppendParam. The data and string about each data type enter explicitly the length and value. The example of this fuction is as follows.

Insert Numeric Data Type with Fixed-Length The numeric data type with fixed-length means short, integer, long, float, and double. In this case, enter the direct value into the member of SQL_APPEND_PARAM structure.

Table. Insert Numeric Data Type with Fixed Length in SQLAppendDataV2

Type of Database NULL Macro SQL_APPEND_PARAM Member
SHORT SQL_APPEND_SHORT_NULL mShort
USHORT SQL_APPEND_USHORT_NULL mUShort
INTEGER SQL_APPEND_INTEGER_NULL mInteger
UINTEGER SQL_APPEND_UINTGER_NULL mUInteger
LONG SQL_APPEND_LONG_NULL mLong
ULONG SQL_APPEND_ULONG_NULL mULong
FLOAT SQL_APPEND_FLOAT_NULL mFloat
DOUBLE SQL_APPEND_DOUBLE_NULL mDouble

The following is an example of entering the actual value. Table schema has 8 columns and it is assumed that each of them is consisted with SHORT, USHORT, INTEGER, UNINTEGER, LONG, ULONG, FLOAT, and DOUBLE.

void funcAAA()
{
SQL_APPEND_PARAM sParam[8];

/* fixed column */
sParam[0].mShort = SQL_APPEND_SHORT_NULL;
sParam[1].mUShort = SQL_APPEND_USHORT_NULL;
sParam[2].mInteger = SQL_APPEND_INTEGER_NULL;
sParam[3].mUInteger = SQL_APPEND_UINTEGER_NULL;
sParam[4].mLong = SQL_APPEND_LONG_NULL;
sParam[5].mULong = SQL_APPEND_ULONG_NULL;
sParam[6].mFloat = SQL_APPEND_FLOAT_NULL;
sParam[7].mDouble = SQL_APPEND_DOUBLE_NULL;

SQLAppendDataV2(Stmt, sParam);

/* FIXED COLUMN Value */
sParam[0].mShort = 2;
sParam[1].mUShort = 3;
sParam[2].mInteger = 4;
sParam[3].mUInteger = 5;
sParam[4].mLong = 6;
sParam[5].mULong = 7;
sParam[6].mFloat = 8.4;
sParam[7].mDouble = 10.9;

SQLAppendDataV2(Stmt, sParam);
}

Insert Date and Time Data Types

Below is an example of entering data of DATETIME type. Several macros are ready for your convenience. It performs operation on the member of mDateTime in SQL_APPEND_PARAM. The macro blow can specify the date by setting the 64-bit integer to mTime in mDateTime structure. typedef struct machAppendDateTimeStruct

{
long long mTime;
struct tm mTM;
char *mDateStr;
char *mFormatStr;
} machAppendDateTimeStruct;

Table. Insert Date and Time Data Type in SQLAppendDataV2 (mTime)

Macro Description
SQL_APPEND_DATETIME_NOW Enter the current client time.
SQL_APPEND_DATETIME_STRUCT_TM Set the value in structure mTM which is struct tm of mDateTime and enters the database
SQL_APPEND_DATETIME_STRING Set the value to mTM in "struct tm" structure of mDateTime, and insert the value to the database.( mDateStr: allocate the string value of the date./ mDateStr: allocate the string value of the date./ mFormatStr: allocate format string for the date string.)
SQL_APPEND_DATETIME_NULL Insert the value of a date column to NULL.
Any 64-bit value This value is entered as the actual datetime. It is displayed as the integer value which indicates the time in nano seconds from Jan 1, 1970. For example, if this value is a billion (1,000,000,000), it indicates Jan 1, 1970 0:00.01 (GMT).

The following is an example of entering the actual value of each case. It is assumed that DATETIME column exists.

void funcAAA()
{
SQL_APPEND_PARAM sParam[1];
/* NULL Entry */
sParam[0].mDateTime.mTime = SQL_APPEND_DATETIME_NULL;
SQLAppendDataV2(Stmt, sParam);

/* Enter the current time */
sParam[0].mDateTime.mTime = SQL_APPEND_DATETIME_NOW;
SQLAppendDataV2(Stmt, sParam);

/* Enter any value: value of Nanoseconds from Jan 1, 1970 to present */
sParam[0].mDateTime.mTime = 1234;
SQLAppendDataV2(Stmt, sParam);

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

/* Enter it by changing the value of struct tm */
sParam[0].mDateTime.mTime = SQL_APPEND_DATETIME_STRUCT_TM;
sParam[0].mDateTime.mTM.tm_year = 2000 - 1900;
sParam[0].mDateTime.mTM.tm_mon = 11;
sParam[0].mDateTime.mTM.tm_mday = 31;
SQLAppendDataV2(Stmt, sParam);
}

Insert type of Internet Address

The following is an example of entering data types of IPv4 and IPv6. Some macros are ready for your convenience as well. It performs the operation on the member of mLength in SQL_APPEND_PARAM.

typedef struct machAppendIPStruct
{
unsigned char mLength;
unsigned char mAddr[16];
char *mAddrString;
} machAppendIPStruct;

Table. Insert Date Type in SQLAppendDataV2

Macro (set on mLength) Description
SQL_APPEND_IP_NULL Enter a NULL value to the column.
SQL_APPEND_IP_IPV4 mAddr has IPv4.
SQL_APPEND_IP_IPV6 mAddr has IPv6.
SQL_APPEND_IP_STRING mAddrString has address string.

​The following is an example of an actual value input for each case.

void funcAAA()
{
SQL_APPEND_PARAM sParam[1];
/* NULL */
sParam[0].mIP.mLength = SQL_APPEND_IP_NULL;
SQLAppendDataV2(Stmt, sParam);

/* Change array directly */
sParam[0].mIP.mLength = SQL_APPEND_IP_IPV4;
sParam[0].mIP.mAddr[0] = 127;
sParam[0].mIP.mAddr[1] = 0;
sParam[0].mIP.mAddr[2] = 0;
sParam[0].mIP.mAddr[3] = 1;
SQLAppendDataV2(Stmt, sParam);

/* IPv4 from binary */
sParam[0].mIP.mLength = SQL_APPEND_IP_IPV4;
*(in_addr_t *)(sParam[0].mIP.mAddr) = inet_addr("192.168.0.1");
SQLAppendDataV2(Stmt, sParam);

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

/* IPv4: ipv4 from invalid string */
sParam[0].mIP.mLength = SQL_APPEND_IP_STRING;
sParam[0].mIP.mAddrString = "ip address is not valid";
SQLAppendDataV2(Stmt, sParam); <== Error happends

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

sParam[0].mIP.mLength = SQL_APPEND_IP_STRING;
sParam[0].mIP.mAddrString = "::127.0.0.1";
SQLAppendDataV2(Stmt, sParam);

sParam[0].mIP.mLength = SQL_APPEND_IP_STRING;
sParam[0].mIP.mAddrString = "FFFF:FFFF:1111:2222:3333:4444:7733:2123";
SQLAppendDataV2(Stmt, sParam);

}

When IP type is entered in string, mLength will be changed into 4 or 6 based on data types after SQLAppendDataV2. Thus, it is required to specify mLength as SQL_APPEND_IP_STRING each time before SQLAppendDataV2 when writing code in loop statement.

Insert Variable Data Type (character and binary data)

Variable data types are VARCHAR, TEXT, BLOB and CLOB. The traditional function only supports VARCHAR and there was no way for users to enter the length of a text string. For that reason, the length has to be obtained through strlen() function every time. Now users can specify the length of the variable data from function SQLAppendDataV2. Thus, if users already knew the length, they would then enter the data much faster.

The variable data type is a single structure, but for sake of convenience, a member was created based data types.

typedef struct machAppendVarStruct
{
unsigned int mLength;
void *mDataAn error occurred while writing data to the network.;
} machAppendVarStruct

typedef union machAppendParam
{
short mShort;
int mInteger;
long long mLong;
float mFloat;
double mDouble;
machAppendIPStruct mIP;
machAppendVarStruct mVar; /* for all varying type */
machAppendVarStruct mVarchar; /* alias */
machAppendVarStruct mText; /* alias */
machAppendVarStruct mBinary; /* binary */
machAppendVarStruct mBlob; /* reserved alias */
machAppendVarStruct mClob; /* reserved alias */
machAppendDateTimeStruct mDateTime;
} machAppendParam;

When using variable data type, it specifies the length of data to mLength and you can set the pointer of raw data as mData. If the length of mLength is greater than the defined schema, it is automatically cut and entered. In this case, SQLAppendDataV2() function returns SQL_SUCCESS_WITH_INFO, it displays the related warning message in the internal structure. In order to check the warning message, you have to use SQLError() function.

Table. Insert Variable Data Types in SQLAppendDataV2

Database Type NULL Macros SQL_APPEND_PARAM member(mVar is available)
VARCHAR SQL_APPEND_VARCHARNULL mVarchar
TEXT SQL_APPEND_TEXT_NULL mText
BINARY SQL_APPEND_BINARY_NULL mBinary
BLOB SQL_APPEND_BLOB_NULL mBlob
CLOB SQL_APPEND_CLOB_NULL mClob

The following example shows how to enter an actual value for each environment. It is assumed that there is a VARCHAR column.

create table ttt (name VARCHAR(10));

void funcAAA()
{
SQL_APPEND_PARAM sParam[1];


/* VARCHAR : NULL */
sParam[0].mVarchar.mLength = SQL_APPEND_VARCHAR_NULL
SQLAppendDataV2(Stmt, sParam); <== OK


/* VARCHAR : string */
strcpy(sVarchar, "MY VARCHAR");
sParam[0].mVarchar.mLength = strlen(sVarchar);
sParam[0].mVarchar.mData = sVarchar;
SQLAppendDataV2(Stmt, sParam); <== OK


/* VARCHAR : Truncation! */
strcpy(sVarchar, "MY VARCHAR9"); /* Truncation! */
sParam[0].mVarchar.mLength = strlen(sVarchar);
sParam[0].mVarchar.mData = sVarchar;
appendOneRecord(sParam); **<== SQL_SUCCESS_WITH_INFO**
}

The following is an example of inserting text type.

create table ttt (doc text);


void funcAAA()
{
SQL_APPEND_PARAM sParam[1];


/* VARCHAR : NULL */
sParam[0].mText.mLength = SQL_APPEND_TEXT_NULL
SQLAppendDataV2(Stmt, sParam); <== OK


/* VARCHAR : string */
strcpy(sText, "This is the sample document for tutorial.");
sParam[0].mVar.mLength = strlen(sText);
sParam[0].mVar.mData = sText;
SQLAppendDataV2(Stmt, sParam); <== OK

}

SQLAppendDataByTimeV2

This function is for inputting data for the channel and can set the value of specific time to the value of "_arrival_time" which will be stored in the database rather than the current time. For example, it can be used when entering log files of one month ago with the then-time.

SQLRETURN SQLAppendDataByTimeV2(SQLHSTMT StatementHandle, SQLBIGINT aTime, SQL_APPEND_PARAM *aData);

aTime is a time value to be set to _arrival_time.
** It has to enter nano second values from Jan 1, 1970 to the present. In addition, the inserted values should be arranged from past to present. aData is an array that contains a pointer to the data which will be entered. ** 
The number of the array must match with the number of column in the specified table when it is opened.


For more information, please refer to the function SQLAppendDataV2().

SQLAppendFlush

This function transmits the currently buffered data in the channel to Machbase server.

SQLRETURN SQLAppendFlush(SQLHSTMT StatementHandle);

SQLAppendClose

This function closes the currently opened channel. If there is a channel that doesn't open, an error occurs.

SQLRETURN SQLAppendClose(SQLHSTMT StatementHandle,
int *aSuccessCount,
int *aFailureCount );

aSuccessCount: It has the value for the number of successful append.
aFailureCount: It has the value for the number of failed append.

SQLAppendSetErrorCallback

This function sets the callback function which is called when an error was occurred while appending. If this function was not set, the client will ignore even if an error was occurred in the server.

SQLRETURN SQLAppendSetErrorCallback(SQLHSTMT aStmtHandle,
SQLAppendErrorCallback aFunc);
  • aStmtHandle: It specifies the statement to determine errors.
  • aFunc: It specifies a pointer to callback function when an error occurs.

A prototype of SQLAppendErrorCallback is as follows.

typedef void (*SQLAppendErrorCallback)(SQLHSTMT aStmtHandle,
SQLINTEGER aErrorCode,
SQLPOINTER aErrorMessage,
SQLLEN aErrorBufLen,
SQLPOINTER aRowBuf,
SQLLEN aRowBufLen);
  • aStatementHandle: Statement handle that has the error
  • aErrorCode: 32-bit error code that caused the error
  • aErrorMessage: String for the error code
  • aErrorBufLen: Length of the aErrorMessage
  • aRowBuf: String containing detailed information of the record that caused the error
  • aRowBufLen: Length of aRowBuf

Examples of error callback (dumpError)

Sample source

void dumpError(SQLHSTMT aStmtHandle,
SQLINTEGER aErrorCode,
SQLPOINTER aErrorMessage,
SQLLEN aErrorBufLen,
SQLPOINTER aRowBuf,
SQLLEN aRowBufLen)
{
char sErrMsg[1024] = {0, };
char sRowMsg[32 * 1024] = {0, };

if (aErrorMessage != NULL)
{
strncpy(sErrMsg, (char *)aErrorMessage, aErrorBufLen);
}

if (aRowBuf != NULL)
{
strncpy(sRowMsg, (char *)aRowBuf, aRowBufLen);
}

fprintf(stdout, "Append Error : [%d][%s]\n[%s]\n\n", aErrorCode, sErrMsg, sRowMsg);
}

......

if( SQLAppendOpen(m_IStmt, TableName, aErrorCheckCount) != SQL_SUCCESS )
{
fprintf(stdout, "SQLAppendOpen error\n");
exit(-1);
}
// Set the callback.
assert(SQLAppendSetErrorCallback(m_IStmt, dumpError) == SQL_SUCCESS);

doAppend(sMaxAppend);


if( SQLAppendClose(m_IStmt, &sSuccessCount, &sFailureCount) != SQL_SUCCESS )
{
fprintf(stdout, "SQLAppendClose error\n");
exit(-1);
}
}
`

SQLSetConnectAppendFlush

The data entered by append are recorded in the communication buffer. Users can call SQLAppendFlush function to send the buffered data to the server or the data will be sent when the communication buffer is filled. SQLSetConnectAppendFlush is used when users want to send the data at regular intervals even though the buffer is not filled. This function calculates the time interval between the latest data sent time of 100ms time period and the current time. It sends the contents of communication buffer to the server if the specified time is passed (if time is not set, it is set to 1 second).

SQLRETURN SQL_API SQLSetConnectAppendFlush(SQLHDBC hdbc, SQLINTEGER option)

Parameters are as follows:

  • hdbc: Database is the connection handle.
  • option: If the number is 0, turn off auto flush. If it's not 0, turn on auto flush.

If you conduct an operation against HDBC which is not connected, an error occurs.

SQLSetStmtAppendInterval

If the time unit flush is turned on by using SQLSetConnectAppendFlush, SQLSetStmtAppendInterval can be used when a user wants to turn off automatic flush over a specific statement or adjust the period of automatic flush.

SQLRETURN SQL_API SQLSetStmtAppendInterval(SQLHSTMT hstmt, SQLINTEGER fValue)

Parameters are as follows:

  • hstmt: Statement handle to adjust the flush cycle.
  • fValue: It is the value of the flush cycle. If the value is 0, it doesn't flush and the unit is ms. The thread is executed to flush the data per 100 ms. Thus, the value should be set as multiple of 100. It doesn't flush automatically at the exact desired point. 1000 is the default value.

This function can be successfully executed even though the time-based flush is not running.

Description of Error Code

Description of error codes and the method to check errors. For CLI functions, if the return value is not SQL_SUCCESS, check the error message by using the code below.

SQLINTEGER errNo;
int msgLength;
char sqlState[6];
char errMsg[1024];

if (SQL_SUCCESS == SQLError ( env, con, stmt, (SQLCHAR *)sqlState, &errNo,
(SQLCHAR *)errMsg, 1024, &msgLength ))
{
//Specify the error code to a five digit numeric value.
printf("ERROR-%05d : %s\n", errNo, errMsg);
}

Error codes and messages regarding append related functions are as follows.

Table. Table of Append Error Code

Function Code Message Description
SQLAppendOpen 9000 Statement is already opened. It's occurred when SQLAppendOpen functions are duplicated.
SQLAppendOpen 9001 Failed to close stream protocol. Failed to shut down the stream protocols.
SQLAppendOpen 9002 Failed to read protocol. Error occurs when failed to read network.
SQLAppendOpen 9003 Cannot read column meta. Information structure of column meta is incorrect.
SQLAppendOpen 9004 Cannot allocate memory. Allocation error of internal buffer memory has occurred.
SQLAppendOpen 9005 Cannot allocate compress memory. Allocation error of compression buffer memory has occurred.
SQLAppendOpen 9006 Invalid return after reading column meta. There is an error in the return value.
SQLAppendData 9100 Statement is not opened. Call AppendData before conducting AppendOpen.
SQLAppendData 9101 column() truncated : It occurs when the data is larger than the specified size in the varchar type column.
SQLAppendData 9102 Failed to add binary. An error occurred while writing data to the communication buffer.
SQLAppendClose 9200 Statement is not opened. Not the state of AppendOpen.
SQLAppendClose 9201 Failed to close stream protocol. Failed to shut down stream protocols.
SQLAppendClose 9202 Failed to close buffer protocol. Failed to shut down buffer protocols.
SQLAppendClose 9204 Cannot read column meta. Information structure of column meta is incorrect.
SQLAppendClose 9205 Invalid return after reading column meta. There is an error in the return value.
SQLAppendFlush 9300 Statement is not opened. It is not AppendOpen state
SQLAppendFlush 9301 Failed to close stream protocol. An error occurred while writing data to the network.
SQLSetErrorCallback 9400 Statement is not opened. It is not AppendOpen state.
SQLSetErrorCallback 9401 Protocol Error (not APPEND_DATA_PROTOCOL) The value of APPEND_DATA_PROTOCOL is not matched with the result of a communication buffer.
SQLAppendDataV2 9500 Invalid date format or date string. An error occurs when a datetime format is wrong.
SQLAppendDataV2 9600 Statement is not opened. It is not AppendOpen state.
SQLAppendDataV2 9601 column() truncated : It occurs when the data is larger than the specified size in the binary type column.
SQLAppendDataV2 9602 column() truncated : It occurs when the data is larger than the specified size in varchar and text type columns.
SQLAppendDataV2 9603 Failed to add stream. An error occurred while writing data to the communication buffer.
SQLAppendDataV2 9604 IP address length is invalid. Invalid value is set to mLength structure of IPv4 and IPv6 types.
SQLAppendDataV2 9605 IP string is invalid. The data is not in the form of IPv4 and IPv6.
SQLAppendDataV2 9606 Unknown data type has been specified. Not supported data types of Machbase.

Application Development

CLI Installation Check

If the following files exist in the "include" and "lib" of the directory where the Machbase has installed, the environment for developing applications is ready.

Mach@localhost:~/machbase_home$ ls -l include lib install/
include:
total 176
-rwxrwxr-x 1 mach mach 31449 Jun 18 19:26 machbase_sqlcli.h

install/:
total 12
-rw-rw-r-- 1 mach mach 1667 Jun 18 19:26 machbase_env.mk


lib:
total 16196
-rw-rw-r-- 1 mach mach 78603 Jun 18 19:26 machbase.jar
-rw-rw-r-- 1 mach mach 964290 Jun 18 19:26 libmachbasecli.a

Guide to Makefiles

mach@localhost:~/machbase_home$ cd sample/
mach@localhost:~/machbase_home/sample$ cd cli/
mach@localhost:~/machbase_home/sample/cli$ ls
Makefile sample1_connect.c
mach@localhost:~/machbase_home/sample/cli$

When you install the Machbase package, a sample makefile will be created as follows.

include $(MACHBASE_HOME)/install/machbase_env.mk

INCLUDES += $(LIBDIR_OPT)/$(MACHBASE_HOME)/include


all : sample1_connect


sample1_connect : sample1_connect.o
$(LD_CC) $(LD_FLAGS) $(LD_OUT_OPT)$@ $< $(LIB_OPT)machbasecli$(LIB_AFT) $(LIBDIR_OPT)$(MACHBASE_HOME)/lib $(LD_LIBS)


sample1_connect.o : sample1_connect.c
$(COMPILE.cc) $(CC_FLAGS) $(INCLUDES) $(CC_OUT_OPT)$@ $<

clean :
rm -f sample1_connect

Compiling and Linking

Execute the given sample below, then executable file will be created.

mach@localhost:~/machbase_home/sample/cli$ make
gcc -c -g -W -Wall -rdynamic -O3 -finline-functions -fno-omit-frame-pointer -fno-strict-aliasing -m64 -mtune=k8 -g -W -Wall -rdynamic -O3 -finline-functions -fno-omit-frame-pointer -fno-strict-aliasing -m64 -mtune=k8 -I/home/machbase/machbase_home/include -I. -L//home/machbase/machbase_home/include -osample1_connect.o sample1_connect.c
gcc -m64 -mtune=k8 -L/home/machbase/machbase_home/lib -osample1_connect sample1_connect.o -lmachbasecli -L/home/machbase/machbase_home/lib -lm -lpthread -ldl -lrt -rdynamic
mach@localhost:~/machbase_home/sample/cli$ ls -al
total 1196
drwxrwxr-x 2 mach mach 4096 Jun 18 20:15 .
drwxrwxr-x 4 mach mach 4096 Jun 18 19:26 ..
-rw-rw-r-- 1 mach mach 483 Jun 18 19:26 Makefile
-rwxrwxr-x 1 mach mach 1196943 Jun 18 20:15 sample1_connect
-rw-rw-r-- 1 mach mach 549 Jun 18 19:26 sample1_connect.c
-rw-rw-r-- 1 mach mach 8168 Jun 18 20:15 sample1_connect.o
mach@localhost:~/machbase_home/sample/cli$

Write applications by modifying the sample makefile above as required.

results matching ""

    No results matching ""