SQL Syntax (Advanced)

A more complex mechanism is available to call BASIC subroutines when longer parameter lengths or parameters containing spaces are required. This mechanism utilizes an SQL INSERT statement to set up the input arguments followed by a SELECT statement to return the results.

To begin the call, the input parameter must be indicated with an INSERT statement using this format:

INSERT INTO sql.sqlsub (cmd[, p1[, p2 [,… p16])
VALUES ('sql_params'[, ‘param1'[, 'param2'[,… 'param16'})

The column cmd must always be specified as sql_params.

Each parameter is placed in a column named with the letter p followed by the parameter number. For each parameter column name specified, the corresponding value must be specified in the VALUES clause. Parameters which are not specified default to null strings.

To execute the subroutine and obtain the results, use this statement:

SELECT result[, r1[, r2 [,… r16]]] FROM sql.sqlsub
WHERE cmd = '`DO-SQLSUB sub.file sub.name num.param`’

Note: The portion of the statement beginning with DO-SQLSUB and ending with the last parameter must be surrounded by back quotes (‘) within single quotes (').

The syntax has these parameters:

Parameter	Description
`result`	Indicates the completion status. Successful completion returns the string OK.
`r1 - r16`	Return results of each parameter. For example, the return column r1 contains the result of the first parameter after the subroutine has completed.
`sub.file`	The full path (for example, account,file,) of the file in which the subroutine exists.
`sub.name`	The subroutine name.
`num.param`	The number of parameters. Note that no input parameters are specified. This tells the DO-SQLSUB driver to look in the row sql-params for the parameters instead.

Example

Consider again the example where a subroutine exists which computes the sum of two numbers and is defined as follows:

SUBROUTINE addit(sum, num1, num2)
   sum = num1 + num2
RETURN

Assume that this subroutine is compiled in the file bp in the account ba. To make this routine add the numbers 5 and 7 from ODBC using the advanced method, this set of SQL statements can be used:

INSERT INTO sql.sqlsub (cmd, p2, p3)
   VALUES ('sql_params', '5', '7')
SELECT result, r1
   FROM sql.sqlsub
   WHERE cmd = '`DO-SQLSUB ba,bp, addit 3`'

Upon successful completion, result would contain OK, and r1 would contain 12. Note that the first parameter was not specified in the INSERT statement since the default of a null string was acceptable in this case.

Errors are handled the same way as they are handled with the simple method.

Limitations

Parameter values cannot contain attribute marks. File variables and select lists cannot be passed.
The maximum number of parameters is 16.
The maximum length of each input or output parameter is 255 characters.
Because this method utilizes a two statement process, it may not be supported by certain ODBC client applications.
BASIC input and output to the screen is not valid. Screen output can be used for error information only.