f2s_esql_set_proc_name¶

Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers

Syntax

f2s_esql_set_proc_name {ProcName} [using {variant}]

Parameter	Description
ProcName	A string containing the SQL procedure setup and execute
variant	Optional variant type variable that will receive a statement interface

Description

This command is used to set the name of a predefined SQL Stored Procedure that is to be executed. Once this command has been called, an interface is instantiated to handle the call.

The optional “using” syntax allows you to pass around the interface created by this command. The variable passed MUST be a variant. This allows for multiple statements to be active concurrently in some cases. If you use the “using” syntax, it must be used on all subsequent commands. Mixing the using syntax is not supported. So for instance, if you plan on having multiple statements active concurrently, all active statements must make use of the using syntax.

** Additional Notes**

For MS-SQL this can be either “ProcName” or “schema.ProcName”
For Oracle it can be “ProcName”, “Schema.ProcName”, “Package.ProcName” or “Schema.Package.ProcName”
For MS-SQL the scope of this call is the current database set via the f2s_db_name attribute. If no database has been set, the default database for the login will be used.

Examples

SQL Server Code to create a simple Stored Procedure ‘dbo.TEST’ that takes a ‘DATETIME’ input and adds 40 days to the ‘OUTPUT’ parameter

CREATE PROCEDURE TEST @dDateIN DATETIME, @dDateOUT DATETIME OUTPUT
 AS SELECT @dDateOUT = DATEADD(dd,40,@dDateIN)

DataFlex code to call the SQL Server ‘TEST’ Stored Procedure under the ‘.dbo’ schema

DateTime dtCurrentDateTime
DateTime dtOutputDateTime
Move (CurrentDateTime()) to dtCurrentDateTime

f2s_esql_set_proc_name "dbo.TEST"
f2s_esql_prepare_stmt
f2s_esql_set_proc_param "@dDateIN" to dtCurrentDateTime
f2s_esql_execute_proc
f2s_esql_get_proc_param "@dDateOUT" to dtOutputDateTime
Showln  dtOutputDateTime

Another Example of calling the SQL Server ‘sp_help’ Stored Procedure with the optional ‘using’ syntax with a variant variable

 Variant vStat
 Variant[] vRow
 Boolean bWorked
 String sVal
 Integer i
 Integer iSize

 f2s_esql_set_proc_name "sp_help" using vStat
 f2s_esql_set_cursor_type f2s_olecursor_client using vStat
 f2s_esql_prepare_stmt using vStat
 f2s_esql_set_proc_param "@objname" to "fn_helpdatatypemap" using vStat
 f2s_esql_execute_proc using vStat
 f2s_esql_move_next bWorked using vStat
 If (bWorked) Begin
     f2s_esql_get_row vRow using vStat
     Move (SizeOfArray(vRow)) to iSize
     For i from 0 to (iSize-1)
         Showln "item #" i " = " vRow[i]
         Inkey sVal
     Loop
 End

Related Commands

Replaces: Command SET_SQL_PROCEDURE_NAME