5. Using Array Binding and Array Fetching#
This chapter describes array binding and array fetching to insert multiple data with one executing statement.
Overview#
Array Binding is a method of passing array values to the server for each parameter of an SQL statement. These methods can provide a decrease in network round-trip time and significant performance benefits when moving large amounts of data.
The following figure illustrates the array binding operation briefly. With using this, it takes shorter time for more data to be transfered because signals sent or received in networks decrease.
The following figure briefly illustrates the Array Binding. It can be seen that the number of network transmissions is reduced, allowing more data to be transmitted in a shorter time.
For more detailed information about using the array binding and array fetching, refer to CLI User's Manual.
How to Set ALTIBASE_BIND#
To use the array binding and array fetching, the user should use ALTIBASE_BIND as you whould when using traditional method. After this, you can set the bind argument which is the address of an array of ALTIBASE_BIND by using altibase_stmt_bind_param() or altibase_stmt_bind_result().
If specifying a size of an array by using altibase_stmt_set_array bind() or altibase_stmt_set_array_fetch(), ACI automatically recognizes that you want to use the array binding or array fetching, and considers that the bind argument is sent in suitable usage for the array binding and array fetching. Using the array binding or array fetching requires special care in specifying buffer and buffer_length.
buffer#
The buffer must be sufficiently large to hold the number of rows specified in array size. The folloiwng example shows how the application should allocate a large enough buffer if array size is set to 5 and column type is ALTIBASE_BIND_INTEGER in the buffer.
#define ARRAY_SIZE 5
/* ... omit ... */
int int_dat[ARRAY_SIZE];
ALTIBASE_BIND bind;
/* ... omit ... */
bind.buffer_type = ALTIBASE_BIND_INTEGER;
bind.buffer = int_dat;
If the total length of the buffer is greater than array size, the rest is ignored except the number of rows specified in array size.
buffer_length#
buffer_length must be specified as the fixed value if you want to use variable-length data such as ALTIBASE_BIND_STRING contained in the buffer. The following example shows how to set buffer_length if array size is set to 5 and maximum size of CHAR is specified as 50.
#define ARRAY_SIZE 5
#define STR_SIZE 50
/* ... omit ... */
char str_dat[ARRAY_SIZE][STR_SIZE];
ALTIBASE_BIND bind;
/* ... omit ... */
bind.buffer_type = ALTIBASE_BIND_STRING;
bind.buffer = str_dat;
bind.buffer_length = STR_SIZE;
In the case of a type that is sized like ALTIBASE_BIND_INTEGER, the buffer_length value does not need to be set to the size of the buffer. If buffer_length is initialized to 0 when the buffer is of fixed size type, the ACI library assumes that the buffer has been allocated the size necessary to hold the data.
Array Binding#
Array Binding is a method of passing array values to the server by binding array variables for each parameter of an SQL statement. This method can provide significant performance benefits. The example of using this method is provided as follows.
Example
#define ARRAY_SIZE 2
#define PARAM_COUNT 2
#define STR_SIZE 50
#define QSTR "INSERT INTO t1 VALUES (?, ?)"
int int_dat[ARRAY_SIZE];
char str_dat[ARRAY_SIZE][STR_SIZE];
ALTIBASE_LONG length[PARAM_COUNT][ARRAY_SIZE];
ALTIBASE altibase;
ALTIBASE_STMT stmt;
ALTIBASE_BIND bind[PARAM_COUNT];
int rc;
int i;
/* ... omit ... */
int_dat[0] = 1;
int_dat[1] = 2;
strcpy(str_dat[0], "test1");
strcpy(str_dat[1], "test2");
length[0][0] = sizeof(int);
length[0][1] = sizeof(int);
length[1][0] = strlen(str_dat[0]);
length[1][1] = ALTIBASE_NTS;
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = ALTIBASE_BIND_INTEGER;
bind[0].buffer = int_dat;
bind[0].length = length[0];
bind[1].buffer_type = ALTIBASE_BIND_STRING;
bind[1].buffer = str_dat;
bind[1].buffer_length = STR_SIZE;
bind[1].length = length[1];
stmt = altibase_stmt_init(altibase);
/* ... check return value ... */
rc = altibase_stmt_prepare(stmt, QSTR);
/* ... check return value ... */
rc = altibase_stmt_set_array_bind(stmt, ARRAY_SIZE);
/* ... check return value ... */
rc = altibase_stmt_bind_param(stmt, bind);
/* ... check return value ... */
rc = altibase_stmt_execute(stmt);
/* ... check return value ... */
printf("processed : %d\n", altibase_stmt_processed(stmt));
for (i = 0; i < ARRAY_SIZE; i++)
{
printf("%d status : %d\n", i, altibase_stmt_status(stmt)[i]);
}
Array Fetch#
At fetch time, multiple rows worth of a column are copied to an array of variable by using the array fetching, This method can provide significant performance benefits.
When using Array Fetch, the number of rows retrieved when executing altibase_stmt_fetch() may be smaller than the size of the configured array. Therefore, after executing altibase_stmt_fetch(), the user should check the number of rows actually retrieved using altibase_stmt_fetched(). If the value returned by altibase_stmt_fetched() is smaller than the size of the configured array, it means that there are no more rows to fetch.
Example
#define ARRAY_SIZE 2
#define FIELD_COUNT 2
#define STR_SIZE 50
#define QSTR "SELECT * FROM t1"
int int_dat[ARRAY_SIZE];
char str_dat[ARRAY_SIZE][STR_SIZE];
ALTIBASE_LONG length[FIELD_COUNT][ARRAY_SIZE];
ALTIBASE_BOOL is_null[FIELD_COUNT][ARRAY_SIZE];
ALTIBASE altibase;
ALTIBASE_STMT stmt;
ALTIBASE_BIND bind[FIELD_COUNT];
int rc;
int i;
int row;
int fetched;
int status;
/* ... omit ... */
stmt = altibase_stmt_init(altibase);
/* ... check return value ... */
rc = altibase_stmt_prepare(stmt, QSTR);
/* ... check return value ... */
rc = altibase_stmt_execute(stmt);
/* ... check return value ... */
rc = altibase_stmt_set_array_fetch(stmt, ARRAY_SIZE);
/* ... check return value ... */
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = ALTIBASE_BIND_INTEGER;
bind[0].buffer = int_dat;
bind[0].length = length[0];
bind[0].is_null = is_null[0];
bind[1].buffer_type = ALTIBASE_BIND_STRING;
bind[1].buffer = str_dat;
bind[1].buffer_length = STR_SIZE;
bind[1].length = length[1];
bind[1].is_null = is_null[1];
rc = altibase_stmt_bind_result(stmt, bind);
/* ... check return value ... */
do
{
rc = altibase_stmt_fetch(stmt);
if (rc == ALTIBASE_NO_DATA)
{
break;
}
if (ALTIBASE_NOT_SUCCEEDED(rc))
{
/* ... error handling ... */
break;
}
fetched = altibase_stmt_fetched(stmt);
for (i = 0; i < fetched; i++)
{
printf("row %d : ", row);
status = altibase_stmt_status(stmt)[i];
if (ALTIBASE_ROW_SUCCEEDED(status))
{
if (is_null[0][i] == ALTIBASE_TRUE)
{
printf("{null}");
}
else
{
printf("%d", int_dat[i]);
}
printf(", ");
if (is_null[1][i] == ALTIBASE_TRUE)
{
printf("{null}");
}
else
{
printf("(%d) %s", length[1][i], str_dat[i]);
}
}
else
{
printf("{status:%d}", status);
}
printf("\n");
row++;
}
} while (fetched == ARRAY_SIZE);