Hi, all (and Vlad)

I was here to ask help about BLOB, and it helped a lot.

Now I have developed a function which can load multiple (different) files into multiple blob columns by INSERT or UPDATE with a single function call, after that help, thanks.

Encouraged by this I am going to explore the array, an abundant and lonely field seldom attended by anyone( but still useful).

The following codes are NOT working, would anyone like to point out where is wrong?

INSERT/UPDATE ONE array as ?

function (char *sqlcmd,
char *table_name,
char *column_name,
char *array_buffer,
int buffer_len))
{
ISC_STATUS status_vector[32];

ISC_ARRAY_DESC desc;
memset(&desc,0,sizeof( ISC_ARRAY_DESC));

ISC_QUAD array_id;
memset(&array_id,0,sizeof(ISC_QUAD));

printf("sql :%s \n", sqlcmd);

int *nullid = 0;
int fberr=0;

XSQLDA *in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(1));
memset ((void*)(qi->in_sqlda->sqlvar), 0,
( 1 * sizeof(XSQLVAR)));

in_sqlda->version = SQLDA_VERSION1;
in_sqlda->sqln = 1;
in_sqlda->sqld = 1;

in_sqlda->sqlvar[0].sqldata = (char*) &array_id;
in_sqlda->sqlvar[0].sqltype = SQL_ARRAY+1;
in_sqlda->sqlvar[0].sqllen = sizeof( ISC_QUAD) ;
in_sqlda->sqlvar[0].sqlind = &nullid;

// getting connected to database...
/ then.....

isc_start_transaction( &status_vector[0], &tr, 1, &db, 0, NULL);
if (status_vector[0] == 1 && status_vector[1]) {
printf("start main transaction ERROR!\n");
isc_print_status( status_vector);
return;
}else { printf("start transaction OK\n"); }


isc_array_lookup_bounds( status_vector[0], //status_vector
&db, //db_handle,
&tr, //&trans,
table_name,/* table name */
column_name,/* array column name */
&desc);

if (status_vector[0] == 1 && status_vector[1]) {
printf("get array descriptor ERROR!\n");
isc_print_status( status_vector);
return;
}else { printf("get array descriptor OK\n"); }

int lob = desc.array_desc_bounds[0].array_bound_lower;
int upb = desc.array_desc_bounds[0].array_bound_upper;
short slice_len = desc.array_desc_length;

printf("lower bound: %d\n", lob);
printf("upper bound: %d\n", upb);
printf("slice length %d\n", slice_len);
printf("buffer length: %d\n", buffer_len);

isc_array_put_slice( &status_vector[0], //status_vector,
&db, //&db_handle,
&tr, //&trans,
&array_id,/* array ID (NULL, or existing array's array ID) */
&desc, /* array descriptor describing where to write data */
array_buffer,/*array buffer containing data to write to array*/
&buffer_len /* length of array buffer */
);

if (status_vector[0] == 1 && status_vector[1]) {
printf("put array slices ERROR!\n");
isc_print_status( status_vector);
return;
}else { printf("put array slices OK\n"); }


isc_dsql_execute_immediate(status_vector[0],
&db, // DB handle
&tr, // transaction
0, // indicates string to execute is null-terminated
sqlcmd, // UPDATE statement string to be executed
1, // XSQLDA version number
in_sqlda // XSQLDA supplying parameters to UPDATE statement
);

if (status_vector[0] == 1 && status_vector[1]) {
printf("execute immediate ERROR!\n");
isc_print_status(status_vector);
printf("\n");
return;
}else {
printf("execute main immediate OK\n");
printf("SQL: %s\n",sqlcmd);

}

}
//---------------------------------------------------

the table with array column is as:

table name: TBL_D
column name: FLD_ARRA CHAR(20)[5]

SQL:UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100

char *arr_buf[] = { "AAA", "AAA", "AAA", "AAA", "AAA" };

// firebird array (lower bound) starts from 1
// but that should not be the case here.
//arr_buf[0] = "AAA";
//arr_buf[1] = "AAA";
//arr_buf[2] = "AAA";
//arr_buf[3] = "AAA";
//arr_buf[4] = "AAA";

int buf_len = sizeof (arr_buf); //= 20, why, how ?

function ("UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100 ",
"TBL_D",
"FLD_ARRA",
arr_buf, // is it like this OK?
buf_len))

It runs on Linux Fedora 14 with FB superserver 2.1.3
whole OS on UTF-8

running results:

sql:UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100

start transaction OK
get array descriptor OK
lower bound: 1
upper bound: 5
slice length 80
buffer length: 20
put array slices ERROR!
subscript out of bounds //here comes the rub!!

if increase the buf_len, it will be:
Mal forward string length...

Ho, array, the last samurai, or really mission impossible?

This is the question, Who can answer ?
thanks in advance

yours
o'rock