HP MPE/iX 6.x Operating System manual ALLBASE/SQL and IMAGE/SQL Restrictions on the Odbc Gram- mar

Appendix A Implementation Notes ODBCLink/SE Reference Manual

⊗If you are in AUTOCOMMIT mode ON (this is the default option, set through SQLSetConnectOptions and in ODBC Administrator), there is an implicit Commit done whenever you fetch the last row. This is so that locks held on the database by the open cursor are released. This may destroy other open cursors. Note also that opening a table for the first time automatically triggers various initialization calls that cause a commit to be done when AUTOCOMMIT is ON.

⊗If you are using prepared statements with substitution parameters to insert or update you need not be concerned about this, as the prepared statement will still be valid after a Commit, and you may bind new parameters and SQLExecute again with no problem. Prepared statements are SQL statements with question marks ‘?’ that you SQLPrepare and then call SQLBindParam or SQLBindCol.

If you wish to use multiple statements, but are not writing your own SQL (i.e. you are using a 4GL application) you can tell ODBCLink/SE to have SQLGetInfo report more than 1 statement per connection. To do this, modify the ODBC.INI or the registry manually, and add a new parameter “MAXSTMT=50".

ALLBASE/SQL and IMAGE/SQL Restrictions on the ODBC Gram- mar

The following table summarizes the ALLBASE/SQL restrictions on the ODBC grammar:

Statement

Programming Considerations

CREATE TABLE	UNIQUE PRIMARY KEY must follow NOT NULL
DROP TABLE	ALLBASE/SQL does not provide CASCADE or RESTRICT
REVOKE	ALLBASE/SQL does not provide RESTRICT.

ALLBASE/SQL provides a DATETIME data type that is similar to TIMESTAMP.

ALLBASE/SQL does not implement optimistic locking. Optimistic locking means that the DBMS does not lock data until just before an update is made, thus improving concurrency because locks are not held for long.

Using the ANSI Character Set

The HP3000 and HP9000 both use the default ROMAN8 character set. Most applications running under Microsoft Windows use the 8-bit ANSI (ISO 8859/1) character set to provide support for Western European languages (including American). The first 127 characters are the same for both ROMAN8 and ANSI character sets, so only the extended and special characters are different.

If your PC client application uses these extended or special characters, then the data from the database server must be converted from ROMAN8 to ANSI, and the data returning to the database server must be converted from ANSI to ROMAN8. To activate the conversion, select the “Roman8 to PC-ANSI translation DLL during the data-source setup. The conversion takes place on the PC client.

Bind variables of datatype CHAR and VARCHAR are also converted. A bind variable in an SQL statement is associated (bound) to variables defined in a program. A bind variable is another name for a dynamic parameter.

	ODBCLink/SE
68	©M.B. Foster Associates Limited 1995-2000