A Rexx interface to SQL databases

Version 2.5

10 October 2006

7. Standard placemarkers and bind variables

query1 = "select name from emp where id = ? and deptno = ?"

When providing values for bind variables it is necessary not only to provide the value of the bind variable, but also the datatype of that bind variable. Thus, bind variables are always supplied in pairs; the first being the datatype, the second the value.

Assuming the EMP table consists of the columns:

ID CHAR(4)
NAME VARCHAR(20)
DEPTNO SMALLINT

then using bind variables in a call to SQLCOMMAND would look like:

query1 = "select name from emp where id = ? and deptno = ?" rc = sqlcommand(q1,query1,"CHAR","F1","SMALLINT",10)

Some Rexx implementations limit the number of parameters that can be passed to a Rexx external function; (OS/2 Rexx has a limit of 20). To work around this limitation, Rexx/SQL allows the specification of a stem name for the datatype and the value. This stem variable must conform to Rexx's convention of arrays; ie. the compound variable with a tail of 0 contains the number of elements in the array.

Re-writing the above example using arrays:

query1 = "select name from emp where id = ? and deptno = ?" dt.0 = 2 dt.1 = "CHAR" dt.2 = "SMALLINT" bv.0 = 2 bv.1 = "F1" bv.2 = "10" rc = sqlcommand(q1,query1,"dt.","bv.")

Obviously in the above example, with only 2 bind variables, it is simpler to use the first method. When using the array method, there must only be two parameters passed; the two stem variable names, and these stem names must include the trailing '.'.

To add a bit more flexibility (and a bit more complication), the format of a bind variable can include a reference to an operating system file. This enables Rexx/SQL to insert data into a column directly from a file; useful for storing or retrieving BLOBs. The datatype specification consists of the string 'FILE:' followed by the datatype of the column, and the value portion consists of the operating system file name.

Our example program would now look like:

query1 = "select name from emp where id = ? and deptno = ?" rc = sqlcommand(q1,query1,"FILE:CHAR","./abc","FILE:SMALLINT","/tmp/xyz")

Assuming the file: ./abc contains the string "F1" and the file /tmp/xyz contains the string "10" (and no trailing line feed or carriage return characters),then this version of the program would function the same as the previous two.

A word of warning when using this method to insert large files into a database or retrieve BLOBs. Rexx/SQL will take advantage of some database implementations that allow the insertion or extraction of pieces of a column of data. It generally uses a chunk of data of 64kb in size. On database implementations that don't support this partial insertion or extraction of column data, Rexx/SQL has no choice but to insert the file or extract the BLOB in one piece. This requires the allocation of memory of the size of the file or BLOB. So if your file or BLOB is 2gb in size I hope you have a lot of memory!!!