SQL syntax (FlashBASIC)

To call a FlashBASIC subroutine using SQL, use this syntax:

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

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 (').

Example

This example computes the sum of two numbers.

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

Assume it is compiled in the file bp in the account ba. To make this subroutine add the numbers 5 and 7 from ODBC, use this SQL statement:

SELECT result, r1
   FROM sql.sqlsub
   WHERE cmd = '‘DO-SQLSUB ba,bp, addit 3 x 5 7‘'

Upon successful completion, result would contain OK, and r1 would contain 12. Note that the string x was passed as the first input parameter for use as a place holder. This value was ignored by the subroutine.

Limitations

Parameter values cannot contain attribute marks or spaces. File variables and select lists cannot be passed.
The maximum number of parameters is 16.
The length of input parameters combined with the length of the file and subroutine names cannot exceed 255 characters.
BASIC input and output to the screen is not valid. Screen output can be used for errors only.

Error Handling

Error handling is accomplished via examination of the result column.

Under normal circumstances, this column returns OK to indicate proper completion. If the column value is unequal to OK, an error has occurred and the results in the parameter columns are invalid.

Example

For example, assume that the above subroutine mysub is called with the incorrect number of parameters:

SELECT result, r1
   FROM sql.sqlsub
   WHERE cmd = '`DO-SQLSUB ba,bp, addit 5 x 5 7 x x`'

The value in result would be [B25] in program do-sqlsub. This is the first line of BASIC error message B25 which indicates that the number of parameters is incorrect.

Users may add their own error handling by placing this code in a BASIC subroutine:

IF error.condition THEN print error.message; stop

For example, consider this subroutine which calculates the square root of a number:

SUBROUTINE mysqrt(value, num)
   IF num < 0 THEN
      print "Negative Input Illegal! "; stop
   END ELSE
      value = sqrt(num)
   END
RETURN

Assume that this subroutine is compiled in the file bp in the account ba. If this subroutine were called with an illegal value as in the SQL statement below, result would contain Negative Input Illegal!.

SELECT result, r1
   FROM sql.sqlsub
   WHERE cmd = '`DO-SQLSUB ba,bp, mysqrt 2 x -1`'