10 October 2006
The following connects to the database running on the local machine as SCOTT with password TIGER.
rc = sqlconnect(,"scott","tiger")
rc = sqlconnect("MYCON","scott","tiger",,"XYZ.WORLD")
rc = sqlconnect()
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)
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:
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')
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.")
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.
The Oracle implementation does not include any extra variable components.