CLI Reference Guide

ODBCLink/SE Reference Manual Appendix A Implementation Notes
ODBCLink/SE
©M.B. Foster Associates Limited 1995-2000 67
Appendix A Implementation Notes
Following is a list of features that are unique or specific to ODBCLink/SE and some details about them.
Primary key name returned by SQLStatistics
Most ODBC applications including Microsoft Access, Visual Basic and PowerBuilder require a unique primary key
for a table to be updatable.
Note: If a unique key has not be defined in ALLBASE/SQL the table
will not be updatable.
Reading or writing to ALLBASE/SQL LongVarBinary items
Any file residing on the HP can be inserted into an ALLBASE/SQL LongVarBinary field with the SQL command:
INSERT INTO Table VALUES (..., '<BLOB >%$', ...);
Where BLOB is the name of the file on the HP and the '>%$' tells ALLBASE/SQL to copy the contents to a memory
location when retrieving the row. You can also have ALLBASE/SQL copy it to a file during retrieval
('<BLOB >OUTFILE'). In either case, the data can be retrieved in the standard way (SQLFetch followed by
SQLGetData in chunks).
You may also create a binary column on the HP by using ODBC functions call sequence: SQLPrepare,
SQLBindparameter (DATA_AT_EXECUTION), SQLExecute, SQLParamData, SQLPutData.
Maximum number of statements
ODBCLink/SE supports up to 50 concurrent statements, or cursors, per connection. However, SQLGetInfo with
option SQL_MAX_STMT will only report 1 as the maximum number of concurrent statements. This is because
multiple statements (or cursors) in ALLBASE/SQL on the same connection are not truly independent. Having
SQLGetInfo return 1 for SQL_MAX_STMT forces MS-Access, and other applications, to use multiple connections
(multiple SQLDriverConnect’s) instead of multiple statements on the same connection (one SQLDriverConnect and
multiple SQLAllocStmt’s).
You can use multiple statements from within the same connection (in fact, some applications ignore the SQLGetInfo
and do this anyway). There is a performance advantage to doing things this way since a new statement (or cursor) in
ALLBASE/SQL takes much less overhead than a new connection. However you should be aware of the following:
⊗ In ALLBASE/SQL, a COMMIT or ROLLBACK operation closes all open
cursors within the connection. If you have multiple Select statements going
on different statements, the next time you SQLFetch on one statement, after
a Commit on another statement, you will get an error from ALLBASE/SQL
“ALLBASE has closed the cursor for this statement”. You may not get this
error immediately, as ODBCLink/SE caches a certain number of rows
during a fetch.