A Rexx interface to SQL databases

Version 2.5

10 October 2006

Appendix A - Rexx/SQL for Oracle

General:

• All arguments to SQLCONNECT are optional.

• Examples of different connections with SQLCONNECT:

  The following connects to the database running on the local machine as SCOTT with password TIGER.

  rc = sqlconnect(,"scott","tiger")

  
  The following connects to the database identifed by the SQL*Net V2 entry; XYZ.WORLD as SCOTT with password TIGER with a connection name of MYCON.

  rc = sqlconnect("MYCON","scott","tiger",,"XYZ.WORLD")

  
  The following connects to the database running on the local machine as an externally identified user.

  rc = sqlconnect()

  
  

• If, when the first call to SQLCOMMAND or SQLPREPARE is made, the user is not connected to a database, an implicit SQLCONNECT is made. ie. via the OPS$ feature of Oracle.

Bind Variables:

Rexx/SQL for Oracle can use Oracle's two proprietary forms of placemarkers for bind variables; numbers and names, as well as the standard placemarker; ?, if SQLVARIABLE('STANDARDPLACEMARKERS',1) is called before calling SQLCOMMAND or SQLOPEN. See 7. Standard placemarkers and bind variables for further details.

The following describes the Oracle bind mechanisms:

Bind by number:

You cannot bind a LONG or RAW column value with this mechanism. The reason is that there is no mechanism to specify that the input value is a LONG or RAW. You need to either use Standard placemarkers and bind variables or Bind by name and specify the bind value type.

The placemarkers in the sql statement are numeric; :1, :2 etc. The arguments passed to the SQLCOMMAND and SQLOPEN functions for bind values consist of a '#' followed by the bind values. eg.

query1 = "select name from emp where id = :1 and deptno = :2" rc = sqlcommand(q1,query1,"#",345,10)

Bind by name:

The arguments passed to the SQLCOMMAND and SQLOPEN functions are pairs of placemarker name and bind variable value.

The format of the bind variable names is similar to those provided in 7. Standard placemarkers and bind variables, but the bind variable name is appended. Examples of valid bind variable names are:

• FILE:LONG RAW:IMAGE - bind value is LONG RAW column type from a file bound to name :image
• LONG:IMAGE - bind value is LONG column type from Rexx variable bound to name :image
• :IMAGE - bind value not specified from Rexx variable bound to name :image

query1 = "select name from my_table where image_name = :name and image = :image"
rc = sqlcommand( q1, query1, "VARCHAR2:name", "mypicture.jpg", "FILE:LONG RAW:image", "/tmp/mypicture.jpg" )

If a bind variable type is not specified it is assumed to be "VARCHAR2". So in the following example the placemarkers in the sql statement named; :PHONE, :ADD are VARCHAR2 values.

insert1 = "insert into person values( :PHONE , :ADDRESS )" rc = sqlcommand(i1,insert1,":PHONE",'+613456123',":ADD",'10 somewhere')

Overcoming limitations:

The placemarkers in the sql statement are named; :ID, :DEP. The 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 Oracle bind values and/or placemarkers to be specified as arrays. This capability is similar to the method of passing bind values when using standard placemarkers but is not as flexible.
To specify that arrays are being used to pass bind values and/or placemarkers, the first bind parameter must be a '.' followed by either one or two stem variables. These stem variables 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.

The following example demonstrates the use of arrays when using numbered placemarkers (only 1 array required):

query1 = "select name from emp where id = :1 and deptno = :2" bv.0 = 2 bv.1 = 345 bv.2 = 10 rc = sqlcommand(q1,query1,".","bv.")

The following example demonstrates the use of arrays when using named placemarkers (two arrays required):

query1 = "select name from emp where id = :ID and deptno = :DEP" bn.0 = 2 bn.1 = ":ID" bn.2 = ":DEP" bv.0 = 2 bv.1 = 345 bv.2 = 10 rc = sqlcommand(q1,query1,".","bn.","bv.")

Column names:

If a column specification in a SQL statement passed to SQLCOMMAND or SQLPREPARE contains a function or is a constant, the column specifier must be aliased so that a valid Rexx variable can be generated for that column.

SQLDESCRIBE variables:

The Oracle implementation does not include any extra variable components.