Skip to content
≡

Administrator

Developer

Tools

External Tools

Appendix D. Upgrade#

This appendix describes the requirements to make ODBC or Altibase CLI applications for Altibase 4 available for Altibase 5 as follows.

The level of CLI interface has been improved since the upgrade to Altibase 5. Especially it has high compatibility with user applications and general purpose applications to follow the standard of X/Open CLI or ODBC specification to the highest degree.

This appendix explains data types newly added or defined, and other changes.

  • Data Types
  • Other Changes

Data Type#

This section describes data types newly added to Altibase 5.

The user can resolve the problems derived from compiling the existing applications to firmly keep the standard compared to previous version.

SQLCHAR, SQLSCHAR#

The previous CLI applications have used SQLCHAR and char together. However, standardoriented SQLCHAR is defined newly as follows.

typedef unsigned char SQLCHAR;
typedef signed char SQLSCHAR;

Therefore, errors occur when you compile the existing applications with the following statements.

char *query = "....";
SQLPrepare(stmt, query, SQL_NTS);

The user only has to modify type casting as follows to solve this problem.

char *query = "....";
SQLPrepare(stmt, (SQLCHAR *)query, SQL_NTS);

SQL_BIT, SQL_VARBIT#

Subsequent releases, starting with version 5, support BIT type as standard SQL92 and VARBIT type for your convenience. Refer to SQL Reference for details about them.

BIT to C type#

The following indicates conversion table related to BIT.

C type id Test *TargetValuePtr *StrLen_or_IndPtr SQLSTATE
SQL_C_CHAR BufferLength > 1 Data ('0' or '1') 1 n/a
BufferLength <= 1 Undefined Undefined 22003
SQL_C_STINYINT SQL_C_UTINYINT SQL_C_SBIGINT SQL_C_UBIGINT SQL_C_SSHORT SQL_C_USHORT SQL_C_SLONG SQL_C_ULONG SQL_C_FLOAT SQL_C_DOUBLE SQL_C_NUMERIC None(The values of BufferLength have the fixed type like this, and they are ignored in case of coversion.) Data (0 or 1) Size of C type n/a
SQL_C_BIT None Data (0 or 1) 1 n/a
SQL_C_BINARY None Data Data (See below for formats) Data length to be written in the memory the user binds n/a

VARBIT to C type#

The following indicates conversion table related to VARBIT.

C type id Test *TargetValuePtr *StrLen_or_IndPtr SQLSTATE
SQL_C_CHAR BufferLength > 1 Data Precision of varbit n n/a
BufferLength <= 1 Undefined Undefined 22003
SQL_C_BIT None Data (0 or 1) 1 n/a
SQL_C_BINARY Data (Data (Its format is same as that of BIT) Data length to be written in the memory the user binds n/a

C type to BIT/VARBIT#

No type is converted to BIT currently.

Binary Format#

0 7 8 15 16 23 24 31 32 39 ... 8n 8n+7
Precision Data ....
  • n= (Precision+7)/8 + 3
  • Precision : Length of BIT data
  • Data : BIT Data

Data Type Conversion Example#

BIT/VARBIT SQL_C_BINARY#

Data themselves are sent from server to user when you bind and fetch BIT to SQL_C_BINARY. Data formats sotred in user buffer are as mentioned above.

The user can access to the server conveniently if specifying struct bit_t as the following examples and using it.

CREATE TABLE T1(I1 BIT(17), I2 VARBIT(37));
INSERT INTO T1 VALUES(BIT'11111011010011011', 
VARBIT'0010010010101110001010100010010011011');
INSERT INTO T1 VALUES(BIT'110011011',
VARBIT'001110001010100010010011011');

void dump(unsigned char *Buffer, SQLINTEGER Length)
{
for (SQLINTEGER i = 0; i < Length; i++) 
{
printf("%02X ", *(Buffer + i));
}
}

typedef struct bit_t
{
SQLUINTEGER mPrecision;
unsigned char mData[1];
} bit_t;

bit_t *Bit;
bit_t *Varbit;
SQLLEN Length;
SQLRETURN rc;

Bit = (bit_t *)malloc(BUFFER_SIZE);
Varbit = (bit_t *)malloc(BUFFER_SIZE);

SQLBindCol( stmt, 1, 
SQL_C_BINARY, 
(SQLPOINTER)Bit, 
BUFFER_SIZE, 
&LengthBit);

SQLBindCol( stmt, 2, 
SQL_C_BINARY, 
(SQLPOINTER)Varbit, 
BUFFER_SIZE, 
&LengthVarbit);
do
{
memset(Buffer, 0, BUFFER_SIZE);
rc = SQLFetch(stmt);

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

printf(">> Bit\n");
printf("Length : %d\n", LengthBit);
printf("Precision : %d\n", Bit->mPrecision);
dump(Bit->mData, LengthBit – sizeof(SQLUINTEGER));

printf(">> Varbit\n");
printf("Length : %d\n", LengthVarbit);
printf("Precision : %d\n", Varbit->mPrecision);
dump(Varbit->mData, LengthVarbit – sizeof(SQLUINTEGER));
} while (rc != SQL_NO_DATA);

When the above program is executed, the following output is displayed.

>> Bit
Length : 7
Precision : 17
FB 4D 80 (1111 1011 0100 1101 1)
>> Varbit
Length : 9
Precision : 37
24 AE 2A 24 D8 (0010 0100 1010 1110 0010 1010 0010 0100 1101 1)
------
>> Bit
Length : 7
Precision : 17                     # Precision indicates 17 because "0" bit is appended.
CD 80 00 (1100 1101 1000 0000)
>> Varbit
Length : 8
Precision : 27                     #  Precision indicates 27 because VARBIT doesn't perform padding.
38 A8 93 60 (0011 1000 1010 1000 1001 0011 011)

If BUFFER_SIZE is less than required, SQLFetch() returns SQL_SUCCESS_WITH_INFO, and wirtes its data in the memory bound as BUFFER_SIZE.

BIT/VARBIT to SQL_C_BIT#

SQL_C_BIT of ODBC requires special care because it is the unsigned 8bit integer whose value is 0 or 1. In other words, bound variables don't have 0x64 but 0x01 even though BIT '011001' is stored on the table of server when you bind them with SQL_C_BIT and fetch them.

BIT to SQL_C_CHAR#

If you bind BIT column with SQL_C_CHAR when fetching it, the result always has 0 or 1 following ODBC type conversion rules.

CREATE TABLE T1 (I1 BIT(12));
INSERT INTO T1 VALUES(BIT'110011000010');
INSERT INTO T1 VALUES(BIT'010011000010');

SQLCHAR sData[128];
SQLLEN sLength;

sQuery = (SQLCHAR *)"SELECT I1 FROM T1";

SQLBindCol(stmt, 1, SQL_C_CHAR, sData, sizeof(sData), sLength);

SQLExecDirect(stmt, sQuery, SQL_NTS);

while (SQLFetch(stmt) != SQL_NO_DATA)
{
printf("bit value = %s, ", sData);
printf("sLength = %d\n", sLength);
}

When the above program is executed, the following output is displayed.

1, sLength = 1
0, sLength = 1
VARBIT to SQL_C_CHAR#

Since there is no VARBIT type in the ODBC standard, so the conversion tool is built on its own, when fetching a VARBIT column, all the data in that column is fetched.

CREATE TABLE T1 (I1 VARBIT(12));
INSERT INTO T1 VALUES(VARBIT'110011000010');
INSERT INTO T1 VALUES(VARBIT'01011010');

SQLCHAR sData[128];
SQLLEN sLength;
sQuery = (SQLCHAR *)"SELECT I1 FROM T1";
SQLBindCol(stmt, 1, SQL_C_CHAR, sData, sizeof(sData), &sLength);
SQLExecDirect(stmt, sQuery, SQL_NTS);
while (SQLFetch(stmt) != SQL_NO_DATA)
{
printf("bit value = %s, ", sData);
printf("sLength = %d\n", sLength);
}

When the above program is executed, the following output is displayed.

110011000010, sLength = 12
01011010, sLength = 8

SQL_NIBBLE#

SQL_C_NIBBLE supported in Altibase 4 is not available to Altibase 5. However, the user can fetch data with SQL_C_BINARY.

NIBBLE to C type#

Conversion is available only to SQL_C_CHAR and SQL_C_BINARY.

C type id Test *TargetValuePtr *StrLen_or_IndPtr SQLSTATE
SQL_C_CHAR BufferLength > 1 Data ('0' or '1') Data length to be written in the memory the user binds (Except null and termination character) n/a
BufferLength <= 1 Undefined Undefined 22003
SQL_C_BINARY None Data (See below for formats) Data length to be written in the memory the user binds n/a

NIBBLE is fetched in binary format in the same way as BIT, but binary format of NIBBLE is different from that of BIT because its precision field has 1 byte integer.

Binary Format#

0 7 8 15 ... 8n 8n+7
Precision Data ....
  • n = (Precision+1)/2
  • Precision : Length of NIBBLE data
  • Data : Nibble Data

Data Type Conversion Example#

NIBBLE to SQL_C_BINARY#

Data themselves are sent from server to user when you bind and fetch NIBBLE to SQL_C_BINARY. Data types stored in user buffer are as metioned above.

The user can access to the server conveniently if specifying nibble_t as the results are as follows.

CREATE TABLE T1(I1 NIBBLE, I2 NIBBLE(10), I3 NIBBLE(21) NOT NULL);
INSERT INTO T1 VALUES(NIBBLE'A', NIBBLE'0123456789', NIBBLE'0123456789ABCDEF00121');
INSERT INTO T1 VALUES(NIBBLE'B', NIBBLE'789', NIBBLE'ABCD1234');

void dump(unsigned char *Buffer, int Length)
{
for (int i = 0; i < Length; i++) printf("%02X ", *(Buffer + i));
}

typedef struct nibble_t
{
unsigned char mPrecision;
unsigned char mData[1];
} nibble_t;

nibble_t *Buffer;
SQLLEN Length;
SQLRETURN rc;

Buffer = (nibble_t *)malloc(BUFFER_SIZE);

SQLBindCol(stmt, 2, SQL_C_BINARY, (SQLPOINTER)Buffer, BUFFER_SIZE, &Length);
do
{
memset(Buffer, 0, BUFFER_SIZE);
rc = SQLFetch(stmt);

printf("----\n");
printf("Length : %d\n", Length);
printf("Precision : %d\n", Buffer->mPrecision);
dump(Buffer->mData, Length – sizeof(SQLUINTEGER));
} while (rc != SQL_NO_DATA);

When the above program is executed, the following output is displayed.

Length : 6
Precision : 10
01 23 45 67 89
----
Length : 3
Precision : 3
78 90
NIBBLE to SQL_C_CHAR#

Examples and results are omitted cause of no unusual events in this case.

SQL_BYTE#

This is bound to SQL_C_BINARY instead of SQL_C_BYTE in Altibase 4 and then is executed in the same way as this is in Altibase 4.

BYTE to C types#

Conversion to other types is not available except SQL_C_CHAR and SQL_C_BINARY. However, original data requires special care that its 1 byte is expressed as ASCII 2 characters when you convert binary data to SQL_C_CHAR.

C type id Test *TargetValuePtr *StrLen_or_IndPtr SQLSTATE
SQL_C_CHAR (Byte length of data) * 2 < BufferLength Data Length of data in bytes n/a
(Byte length of data) * 2 >= BufferLength Truncated data Length of data in bytes 01004
SQL_C_BINARY Byte length of data <= BufferLength Data Length of data in bytes n/a
Byte length of data > BufferLength Truncated data Length of data in bytes 01004

Each byte of binary data is always converted to a pair of hex characters when the Altibase CLI converts them to SQL_C_CHAR. Therefore, if buffer size of bound SQL_C_CHAR indicates the even, NULL termination character is printed not in the last byte of it but in ahead of that.

Source binary data (hex characters) Size of bound buffer (bytes) Contents of the buffer bound as SQL_C_CHAR *StrLen_or_IndPtr SQLSTATE
AA BB 11 12 8 hex : 41 41 42 42 31 31 00 ??1
string : "AABB11"		 8 01004
AA BB 11 12 9 hex : 41 41 42 42 31 31 31 32 00
string : "AABB1112"		 8 N/A

Binary Format#

Byte type doesn't have special format like NIBBLE or BIT, but is the conjuction of binary data.

Data Type Conversion Example#

BYTE to SQL_C_CHAR#
CREATE TABLE T1(I1 BYTE(30));
INSERT INTO T1 VALUES(BYTE'56789ABC');

SQLLEN Length;
SQLRETURN rc;

// execution query : SELECT * FROM T1;

Buffer = (nibble_t *)malloc(BUFFER_SIZE);

SQLBindCol(stmt, 1, SQL_C_CHAR, (SQLPOINTER)Buffer, BUFFER_SIZE, &Length);
do
{
memset(Buffer, 0, BUFFER_SIZE);
rc = SQLFetch(stmt);
printf("Length : %d\n", Length);
printf("Data : %s\n", Length);
} while (rc != SQL_NO_DATA);

When the above program is executed, the following output is displayed.

Execution Query 1 : BUFFER_SIZE >= 9#
Length : 8
Data : 56789ABC
Execution Query 2 : BUFFER_SIZE == 8#
Length : 8
Data : 56789A     # 8 This is bound in byte buffer, and then only 6 characters are expressed.
Execution Query 3 : BUFFER_SIZE == 7#
Length : 8
Data : 56789A     # same as BUFFER_SIZE == 8
Execution Query 4 : BUFFER_SIZE == 6#
Length : 8
Data : 5678

SQLFetch() returns SQL_SUCCESS_WITH_INFO for execution results except 1. SQLSTATE indicates 01004.

BYTE to SQL_C_BINARY#

No unusual events for binding to binary in this case.

DATE : SQL_TYPE_TIMESTAMP#

SQL_TYPE_TIMESTAMP is retuned when Altibase 5 inserts data as SQL Type into DATE column with using SQLDescribeCol() or SQLColAttribute(). SQL_TYPE_TIMESTAMP is similar to Altibase DATE type of ODBC standard, and consists of year, month, day, hour and second.

However, if you call SQLColAttribute() or SQLDescribeCol(), SQL_DATE is returned as SQL type because DATE type in Altibase 4 consists of basic elements such as day and hour, and much data such as special characters separating basic elements.

Therefore, when using the Altibase CLI of Altibase 5, SQL_TYPE_DATE, SQL_TYPE_TIME and SQL_TYPE_TIMESTAMP as constant numbers for ODBC 3.0 are recommended.

LOB#

Data Type#

In Altibase 4, length of LOB type is limited to page size. However, it consists of BLOB and CLOB supporting maximum 2GB.

Altibase 4 DDL#
CREATE TABLE T1 (I1 BLOB(3));
Altibase 5 DDL#

The precision disappears.

CREATE TABLE T1 (I1 BLOB);

Using LOB Functions#

Altibase CLI application supports private functions to use LOB. Refer to Chapter3: LOB Interface for details about special functions for LOB.

The user can use functions available to general binary and character type except functions for LOB. You can store and search LOB data with standard ODBC in ODBC application cause of these features.

The user cannot update and retrieve data partially in ODBC application, whereas you can in Altibase CLI application with SQLGetLob and SQLPutLob.

-- Turn off the AUTOCOMMIT mode of the connection.
CREATE TABLE T1 (I1 BLOB, I2 CLOB);

SQLCHAR sBlobData[128];
SQLCHAR sClobData[128];
SQLLEN sBlobLength;
SQLLEN sClobLength;

SQLCHAR *sQuery = (SQLCHAR *)"INSERT INTO T1 VALUES(?, ?)";
SQLPrepare(stmt, sQuery, SQL_NTS);

SQLBindParameter(stmt, 1, SQL_C_BINARY, SQL_BLOB,0, 0, sBlobData, sizeof(sBlobData), &sBlobLength);
SQLBindParameter(stmt, 2, SQL_C_CHAR, SQL_CLOB,0, 0, sClobData, sizeof(sClobData), &sClobLength);
sBlobLength = create_blob_data(sBlobData);
sprintf((char *)sClobData, "this is clob data");
sClobLength = SQL_NTS;

SQLExecute(stmt);

Using LOB in ODBC application#

In order to fetch LOB column in ODBC application and store data in LOB column, call SQLDescribeCol, SQLColAttribute or SQLDescribeParam.

If these functions are executed in LOB column, they are returned as data types of SQL_BLOB and SQL_CLOB. However, ODBC application doesn't recognize data types such as SQL_BLOB or SQL_CLOB.

Therefore, the user may return them as data type which ODBC application recognizes. The user can solve this problem by setting LongDataCompat = on in odbc.ini. If you call SQLColAttribute() in LOB column for this option, ODBC returns SQL_LONGVINARYto SQL_BLOB and SQL_LONGVARCHAR to SQL_CLOB relatively

Use Examples in PHP Program#

The following is an example of using LOB in a PHP application.

php.ini#

Before running the program, you should check two properties in php.ini:

odbc.defaultlrl = 4096     # This value must be specified as greater than 1.
odbc.defaultbinmode = 0    # You must specify this as 0 for using LOB because this can be executed without allocating additional memory.

~/.odbc.ini is as follows.

[Altibase]
Driver         = AltibaseODBCDriver
Description    = Altibase DSN
ServerType     = Altibase
UserName       = SYS
Password       = MANAGER
Server         = 127.0.0.1
Port           = 20073
LongDataCompat = on
NLS_USE = US7ASCII
php program#

<?
/*
* =================================================
* Connection Trial
* =================================================
*/

$Connection = @odbc_connect("Altibase", "SYS", "MANAGER");
if (!$Connection)
{
echo "ConnectFail!!!\n";
exit;
}

/*
* =================================================
* Table Creation
* =================================================
*/

@odbc_exec($Connection, "DROP TABLE T2 ");
if (!@odbc_exec($Connection, 
"CREATE TABLE T2 (I1 INTEGER, B2 BLOB, C3 CLOB) TABLESPACE SYS_TBS_DATA"))
{
echo "create test table Fail!!!\n";
exit;
}

/*
* =================================================
*  autocommit off for using LOB
* =================================================
*/

odbc_autocommit($Connection, FALSE);

/*
* =================================================
* Data Insertion
* =================================================
*/

$query = "INSERT INTO T2 VALUES (?, ?, ?)";
$Result1 = @odbc_prepare($Connection, $query);
if (!$Result1)
{
$msg = odbc_errormsg($Connection);
echo "prepare insert: $msg\n";
exit;
}

for ($i = 0; $i < 10; $i++)
{

/*
* ----------------------
* Reading in File
* ----------------------
*/

$fileno2 = $i + 1;
$filename2 = "a$fileno2.txt";
print("filename = $filename2\n");
$fp = fopen($filename2, "r");
$blob = fread($fp, 1000000);
fclose($fp);

$fileno3 = 10 - $i;
$filename3 = "a$fileno3.txt";
print("filename = $filename3\n");
$fp = fopen($filename3, "r");
$clob = fread($fp, 1000000);
fclose($fp);

/*
* ----------------------
* INSERT
* ----------------------
*/

$Result2 = @odbc_execute($Result1, array($i, $blob, $clob));

print("inserting $i ,$filename2 and $filename3 into T2 ......... ");

if (!$Result2)
{
print("FAIL\n");
$msg = odbc_errormsg($Connection);
echo "execute insert: $msg \n";
exit;
}

print("OK\n");
}

/*
* =================================================
* COMMIT
* =================================================
*/

odbc_commit($Connection);

/*
* =================================================
* Check inserted data
* =================================================
*/
print "\n\n";
print "==========================================\n";
print "Selecting from table\n";
print "==========================================\n";

$query = "select * from t2";
$Result1 = @odbc_exec($Connection, $query);
if (!$Result1)
{
$msg = odbc_errormsg($Connection);
echo "ERROR select: $msg\n";
exit;
}

$rownumber = 0;
while (odbc_fetch_row($Result1))
{
$data1 = odbc_result($Result1, 1);
$data2 = odbc_result($Result1, 2);
$data3 = odbc_result($Result1, 3);
$len2 = strlen($data2); 
$len3 = strlen($data3); 

print "\n==========================================\n";
print "Row $rownumber....\n";
$rownumber++;
print "data1 = ".$data1."\n";
print "-------\n";
print "data2 = \n";
// print $data2; //  Output is omitted because this is binary data.
print "\n";
print "dataLen2 = [$len2]\n";
print "-------\n";
print "data3 = \n";
print $data3;
print "\n";
print "dataLen3 = [$len3]\n";
}

odbc_commit($Connection);

@odbc_close($Connection);
?>

Other Changes#

This section describes changes except data types.

SQLCloseCursor#

Since there is no ODBC state machine in Altibase 4, the Altibase CLI library calls the functions in the following order.

SQLHSTMT stmt;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
SQLPrepare(stmt, (SQLCHAR *)"SELECT I1 FROM T1", SQL_NTS);

SQLExecute(stmt);
SQLFetch(stmt);
SQLExecute(stmt);

However, if above codes are executed with the Altibase CLI driver in Altibase 5, function sequence error occurs in SQLExecute(stmt).

Because stmt performing SQLExecute() first indicates to generate result set. Therefore, ODBC cursor becomes open and state of stmt indicates S5. (Refer to MSDN ODBC specification.). However, error occurs in this state cause of no performing SQLExecute().

In order to perform SQLExecute() again, call SQLCloseCursor() clearly as follows and then make stmt have S1 or S3 state.

SQLExecute(stmt);
SQLFetch(stmt);
SQLCloseCursor(stmt); 

SQLExecute(stmt);

SQLBindParameter – ColumnSize Argument#

ColumnSize of SQLBindParameter() as the 6th parameter in Altibase 5 is different from that of previous one. If you insert 0 into this argument for previous version, no problem. However, if you insert maximum length of data transmitted to server in Altibase 5, its performance has problem because it checks their information whenever executed.

SQLBindParameter – StrLen_or_IndPtr Argument#

CLI library in Altibase 4 references data only if they, which StrLen_or_IndPtr argument indicates, have variable length. However, Altibase 5 references the values in the memory StrLen_or_IndPtr argument indicates whenever performing SQLExecute() or SQLExecDirect() because Altibase 5 can implement SQLPutData() and SQLParamData().

Therefore, you need special care in perfectly initializing memory the pointer indicates if sending StrLen_or_Ind as the last argument of SQLBindParameter() to not Null pointer but valid pointer variables.

If SQL_DATA_AT_EXEC is -2 as constant number or SQL_LEN_DATA_AT_EXEC() is less than - 100 without initializing memory completely, CLI library judges user intends to send the argument with SQLPutData(). And CLI library returns SQL_NEED_DATA when you call SQLExecute().

If SQLExecDirect() returns the unintended value(SQL_NEED_DATA) cause of no initialized value above, this influences on functions called next. So you need special care that function sequence errors in all functions called next cause to return SQL_ERROR.

SQLPutData(), SQLParamData()#

The Altibase CLI in Altibase 5 supports SQLPutData() and SQLParamData() provided not in previous version. Refer to MSDN for details about each function.

The following is the example program with using functions and StrLen_or_IndPtr mentioned above.

Table Schema : 

CREATE TABLE T2_CHAR (I1 INTEGER, I2 CHAR(50));

void putdata_test(void)
{
SQLRETURN sRetCode;
SQLHANDLE sStmt;

SQLINTEGER i1;
SQLLEN i1ind;

SQLCHAR *i2[10] =
{
(unsigned char *)"0000000000000.",
(unsigned char *)"1111111111111. test has been done.",
(unsigned char *)"2222222222222. Abra ca dabra",
(unsigned char *)"3333333333333. Short accounts make long friends.",
(unsigned char *)"4444444444444. Whar the hell are you doing man!",
(unsigned char *)"5555555555555. Oops! I missed this row. What an idiot!",
(unsigned char *)"6666666666666. SQLPutData test is well under way.",
(unsigned char *)"7777777777777. The length of this line is well over 50 characters.",
(unsigned char *)"8888888888888. Hehehe",
(unsigned char *)"9999999999999. Can you see this?",
};

SQLLEN i2ind;

SQLINTEGER i;

SQLINTEGER sMarker = 0;

i1ind = SQL_DATA_AT_EXEC;
i2ind = SQL_DATA_AT_EXEC;

sRetCode = SQLAllocHandle(SQL_HANDLE_STMT, gHdbc, &sStmt);
check_error(SQL_HANDLE_DBC, gHdbc, "STMT handle allocation", sRetCode);

sRetCode = SQLBindParameter(sStmt, 1, SQL_PARAM_INPUT, 
SQL_C_SLONG, SQL_INTEGER, 
0, 0, (SQLPOINTER *)1, 0, &i1ind);

sRetCode = SQLBindParameter(sStmt, 2, SQL_PARAM_INPUT, 
SQL_C_CHAR, SQL_CHAR, 
60, 0, (SQLPOINTER *)2, 0, &i2ind);

sRetCode = SQLPrepare(sStmt, 
(SQLCHAR *)"insert into t2_char values (?, ?)", SQL_NTS);

for(i = 0; i < 10; i++)
{
i1 = i + 1000;

printf("\n");
printf(">>>>>>>> row %d : inserting %d, \"%s\"\n", i, i1, i2[i]);
sRetCode = SQLExecute(sStmt);

if(sRetCode == SQL_NEED_DATA)
{
sRetCode = SQLParamData(sStmt, (void **)&sMarker);

while(sRetCode == SQL_NEED_DATA)
{
printf("SQLParamData gave : %d\n", sMarker);

if(sMarker == 1)
{
sRetCode = SQLPutData(sStmt, &i1, 0);
}
else if(sMarker == 2)
{
int unitsize = 20;
int size;
int pos;
int len;

len = strlen((char *)(i2[i]));
for(pos = 0; pos < len;)
{
size = len - pos;
if(unitsize < size)
{
size = unitsize;
}
sRetCode = SQLPutData(sStmt, i2[i] + pos, size);

pos += size;
}
}
else
{
printf("bbbbbbbbbbbbbbbbbbbbbbbbbbbbb!!! unknown marker value\n");
exit(1);
}

sRetCode = SQLParamData(sStmt, (void **)&sMarker);
}
}
}

sRetCode = SQLFreeHandle(SQL_HANDLE_STMT, sStmt);
check_error(SQL_HANDLE_DBC, gHdbc, "STMT free", sRetCode);
}

Limitation on ALTER SESSION statements#

Altibase 4 specifies AUTOCOMMIT MODE and DEFAULT_DATE_FORMAT as session properties as follows.

SQLExecDirect(stmt, 
"ALTER SESSION SET AUTOCOMMIT=FALSE", 
SQL_NTS);

SQLExecDirect(stmt,
"ALTER SESSION SET DEFAULT_DATE_FORMAT='YYYY/MM/DD'",
SQL_NTS);

The Altibase CLI driver must have information on two session-related properties above because they definitely affect conversion of Altibase CLI and operation of functions related to transactions.

However, the Altibase CLI driver cannot know property changes if transmitting SQL syntaxes to server directly with using SQLExecDirect(). The Altibase CLI driver can get information from server to know the values of property, but this causes to have worse performance.

In Altibase 5 the property is modified with SQLSetConnectAttr() to solve this problem. Altibase 5 always makes the property in the Altibase CLI driver same as that in server.

It can be written as follows when using the Altibase CLI.

SQLSetConnectAttr(conn,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF,
0);
SQLSetConnectAttr(conn,
ALTIBASE_DATE_FORMAT,
(SQLPOINTER)"YYYY/MM/DD",
SQL_NTS);

SQLRowCount(), SQLMoreResults() functions#

There are two results of the Altibase CLI.

  • Number of affected rows

  • Result set

The Altibase CLI considers multiple results in Altibase 5. In other words, you can get them by one execution. Therefore, returned results of SQLRoewCount() are different from those of Altibase 4 when you use array binding.

  • SQLRowCount() : gets affected row count in "current" result.

  • SQLMoreResults() : moves to "next" result and returns SQL_NO_DATA if current result is last.

Example#

CREATE TABLE T1 (I1 INTEGER);
INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);             -- repeat 1000 times

SELECT * FROM T1;
T1
----- 
1 
2 
3 
. 
. 
. 
1000 
-----

SQLINTEGER p1[3];
SQLINTEGER p2[3];
SQLLEN rowcount = 0L;
SQLLEN totalRowcount = 0L;

p1[0] = 10; p2[0] = 20;
p1[1] = 100; p2[1] = 200;
p1[2] = 11; p2[2] = 14;

SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, 3); // <--- array binding

SQLBindParameter(stmt, 1, p1 ..);
SQLBindParameter(stmt, 2, p2 ..);

SQLExecDirect(stmt, 
(SQLCHAR *)"DELETE FROM T1 WHERE I1>? AND I1<?", 
SQL_NTS);

do {
SQLRowCount(stmt, &rowcount);
printf("%d\n", rowcount);
totalRowcount += rowcount;
rc = SQLMoreResults(stmt);
} while (rc != SQL_NO_DATA);

printf("totalRowcount = %d\n", totalRowCount);

Execution Results:

9        # This is affected row count of DELETE FROM T1 WHERE I1>10 AND I1<20
199      # This is affected row count of DELETE FROM T1 WHERE I1>100 AND I1<200
0        # This is affected row count of DELETE FROM T1 WHERE I1>11 AND I1<14 (No record exists because it is deleted by the first execution.)
208      # This is the total of affected row counts

Each execution result of syntax the argument indicates is created, and then sent to the Altibase CLI driver. When multiple results are created like this, each data can move for next result with SQLMoreResults() and have its result with SQLRowCount().

In Altibase 4, when all three results are summed together, SQLRowCount() is 208 Return the result.

In order to get the same result as Altibase 4 in Altibase 5, SQLMoreResults() The function must be repeated until it returns SQL_NO_DATA and added to the result obtained by SQLRowCount().

Unlimited Array Execute, Array Fetch#

Altibase does not have restrictions on Array Execute and Array Fetch as buffer size.

Therefore, the user can bind array in the allocated memory and can use CM_BUFF_SIZE no more.

Unavailable Properties#

Batch Processing Mode#

The placement keyword of the connection string is no longer supported.

In addition, the connection attribute SQL_ATTR_BATCH is no longer supported.

SQL_ATTR_MAX_ROWS#

This indicates to specify the number of prefetched row for better performance in Altibase 4.

However, this property is similar to LIMIT of SELECT statement following ODBC. This option is not available for the Altibase CLI of Altibase. Therefore, if you specify property above as SQLSetStmtAttr(), this asks your attention because error occurs like 'Optional feature not implemented'.

  1. The part marked with ?? is not defined. ↩