A Rexx interface to SQL databases

Version 2.5

10 October 2006

Appendix I - Rexx/SQL for SQLite3

General:

• All arguments to SQLCONNECT are optional. If no database name argument is supplied, then the SQLite3 feature of an in-memory database will be used. With this feature the database is created on connection and all tables need to be created and can then be manipulated as one would normally. The data in the database is lost when disconnecting.
  The database name argument in SQLCONNECT is the only argument that can be supplied. All other arguments should not be specified. The database name argument is a file name. If the database in the specified file name does not exists, it will be created.
• SQLite only allows tables to be described; not a SELECT statement. Therefore is you want to use the SQLDESCRIBE function, pass a table name as the parameter, not a select statement.

Bind Variables:

SQLite supports standard placemarkers and SQLite specific placemarkers. Need to document how these work; similar to Oracle.

Transactons:

SQLite supports transactions, but you need to explicitly execute a "BEGIN TRANSACTION" command to start a transaction. See tester.cmd sample program for an example.

Data types:

SQLite is a typeless database. All columns are stored as text, so SQLite3 allows any datatype you want. Rexx/SQL for SQLite3 however, supports the following datatypes explicitly:

• INTEGER PRIMARY KEY
• INTEGER
• INT
• FLOAT
• NUMERIC
• CHARACTER
• CHAR
• TEXT
• VARCHAR
• BOOLEAN
• BLOB
• CLOB
• TIMESTAMP
• DATETIME