A Rexx interface to SQL databases

Version 2.5

10 October 2006

Appendix G - Rexx/SQL for MySQL

Connection

• The database name argument in SQLCONNECT is mandatory.
• Examples of different connections with SQLCONNECT:

  The following connects to the TEST database running on the local machine with a connection name of MYCON.

  rc = sqlconnect("MYCON",,,"TEST")

  The following connects to the TEST database running on the machine xyz.my.org.

  rc = sqlconnect(,,,"TEST","xyz.my.org")

• If using a non-standard TCP port (not 3306) for your MySQL server, you can change the port number that Rexx/SQL uses by settingthe environment variable; MYSQL_TCP_PORT to the port number required. One way is to:

  Call Value 'MYSQL_TCP_PORT', 3307, 'ENVIRONMENT'

  before calling SQLCONNECT.

General:

• All statements are actually executed by SQLPREPARE. This obviates the SQLEXECUTE function, but it still should be used for portability.

Transactions:

MySQL has support for transactions, but only on BDB or INNOBASE tables. The return value from SQLVARIABLE('SUPPORTSTRANSACTIONS') is always 1, but this doesn't necessarily mean that a transaction will be committed or rolled back when you use the SQLCOMMIT or SQLROLLBACK functions. SQLROLLBACK will result in a warning (a positive number) if you attempt to rollback a transaction that updates a table which resides in neither BDB or INNOBASE.

Bind Variables:

MySQL supports bind variables in SQL statements, however, Rexx/SQL currently doesn't. The return value from SQLVARIABLE('SUPPORTSPLACEMARKERS') is always 0, so any references to bind variables in this document should be ignored for MySQL.

Column names:

If a column specification in a SQL statement passed to SQLCOMMAND or SQLPREPARE contains a table alias, eg. a.emp_id, the Rexx variables created corresponding to this column DO NOT contain the "a." prefix.

SQLDESCRIBE variables:

The MySQL implementation includes the extra variable component; PRIMARYKEY.

SQLTABLES behaviour:

The MySQL implementation has the following behaviour:

• The table qualifier argument is ignored. Only tables from the connected MySQL database are returned.
• The table owner argument is ignored.
• The table type argument is ignored.
• The table name argument is optional, but can have wildcards.

SQLCOLUMNS behaviour:

The MySQL implementation has the following behaviour:

• The table qualifier argument is ignored. Only tables from the connected MySQL database are returned.
• The table owner argument is ignored.
• The table type argument is ignored.
• The table name argument is mandatory and cannot have wildcards.