IB2007 isc_dsql_batch_execute issues

[Dmitry Arefiev [da-soft.com]]

Hello All

I have performed experiments with IB2007 (8.0.0.123) and
new API isc_dsql_batch_execute. So, here are some aspects:

1) Comparing with:

StartTransaction;
SQL := 'insert into ...;
ExecSQL;
.....

ExecSQL;
Commit;

New batch API is around 15 times faster. I was running test
on the same PC with server.

2) I think batch size is limited by 32Kb message length. If
size is greater than this value, then isc_dsql_batch_execute
will give AV.

2.1) Not well - such things must be controlled by GDS32.DLL
code and in case of message buffer overflow appropriate error
must be returned.

2.2) Because, programmer does not know how much memory
will take one batch item in message buffer, it is not possible to
determine maximum batch size. Will be good to have appropriate
API entry for that. For example, isc_info_sql_max_batch_size.

3) After isc_dsql_batch_execute call, the content of insqlda is
modified.

3.1) It is SQLDA before call:

(version:2; sqldaid:(#0, #0, #0, #0, #0, #0, #0, #0); sqldabc:0;
sqln:1; sqld:1; sqlvar:((sqltype:481; sqlscale:0; sqlprecision:0;
sqlsubtype:0; sqllen:8; sqldata:nil; sqlind:nil; sqlname_length:0;
sqlname:(#0, #0, ..............

it is SQLDA after call:

(version:2; sqldaid:(#0, #0, #0, #0, #0, #0, #0, #0); sqldabc:0;
sqln:10; sqld:1; sqlvar:((sqltype:600; sqlscale:0; sqlprecision:0;
sqlsubtype:0; sqllen:8; sqldata:'a '; sqlind:nil; sqlname_length:0;
sqlname:(#0, #0, ................

Pay attention to:
- sqln is equal to number_of_rows
- sqlvar.sqltype always gets 600

3.2) If to perform sequence of isc_dsql_batch_execute:
isc_dsql_batch_execute(number_of_rows = 100)
isc_dsql_batch_execute(number_of_rows = 100)
isc_dsql_batch_execute(number_of_rows = 5)
Then on last call, error is returned:
SQL error code = -804
Incorrect values within SQLDA structure

To vercome that, after isc_dsql_batch_execute I am doing:
FXSQLDA^.sqln := FXSQLDA^.sqld;
isc_dsql_describe_bind(FXSQLDA);

Then all calls are OK.

4) Not all data types are supported.
I have performed query: INSERT INTO MyTab VALUES(?)
for differernt data types. And that is result:

SQL_SHORT -> ok
SQL_LONG -> failed
SQL_FLOAT -> failed
SQL_DOUBLE -> failed
SQL_TYPE_DATE -> failed
SQL_TYPE_TIME -> failed
SQL_TIMESTAMP -> failed
SQL_INT64 -> failed
SQL_VARYING -> ok
SQL_TEXT -> ok
SQL_BLOB -> failed
SQL_BOOLEAN -> ok

The error always was the same:
SQL error code = -804
SQLDA missing or incorrect version, or incorrect number/type of variables

Probably, I am doing something wrong ... But there are no
serious usage examples. Just one, which comes with IB2007.

Regards,
Dmitry

--
Dmitry Arefiev - www.da-soft.com
AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase
ASA, DbExpress, ODBC freeware data access engine
ThinDAC - multitier data access engine