602SQL Documentation Index  

Calling External Routines and Parameter Passing (SQL)

Routines stored on a server may call procedures and functions created in an external program. The way to declare these routines are described in the procedure description. If the SQL server is running on Linux under the root user account, external procedures are not allowed, and the error WONT_RUN_AS_ROOT (228) will occur.

Example:

DECLARE FUNCTION TestFnc(INOUT number CHAR(25)) RETURNS INT;  
EXTERNAL NAME 'TestFnc@TestDLL';

It us assumed that the external routine use the _STDCALL calling convention. External routines must be in a 32-bit .DLL library or .SO on Linux. Calling 16-bit routines or routines with a different calling convention is not allowed.

You must set the compiler parameters when creating a function library so the function will use the standard stack frame. Parameters may not be passed to these functions in registers. Delphi-based libraries sometimes use register-passed parameters and may not be called from 602SQL.

Formal parameters marked as OUT or INOUT are passed by reference (by passing the parameter value address). Formal parameters marked as IN or unmarked are passed by value. Designation and type of the formal parameter in a routine declaration must correspond with the type and parameter passing method in the module, where the routine is implemented, using the rules defined by the following table:

Type in C/C++ Type in PASCAL Type in SQL routine description
int Integer IN INT
int * VAR Integer OUT INT
short SmallInt IN SMALLINT
short * VAR SmallInt INOUT SMALLINT
double Double IN REAL
double * VAR Double INOUT REAL
char * PChar INOUT CHAR(N)
char Boolean IN BOOLEAN
unsigned char * VAR Boolean INOUT BOOLEAN
struct { char[N]; } array of bytes passed by value IN BIT(N), N > 1
struct { char[N+1]; } array of chars passed by value IN CHAR(N)
char * PChar INOUT BIT(N) , N > 1
char * PChar INOUT CLOB

The same rules are valid for OUT as for INOUT (both are passed by reference).

The BLOB type is handled as the CLOB type. The REAL type can be handled in the same way as DOUBLE PRECISION type. The TIME, DATE and TIMESTAMP types may be handled in the same way as INT type, but the value must be decoded correctly on the external routine.

You can pass a parameter of variable-length (BLOB, CLOB) to the external routine. The value can be changed, but it cannot increase in size.

WARNING!

If you do not adhere to these rules and conventions (e.g. mismatch of IN and OUT/INOUT in external routine declaration) it may lead to memory problems and the 602SQL Server may crash.