Alternative Invocation Methods

Invocation using Named Parameters

SQL Server, Oracle, and DB2 support the passing of parameter arguments by name as an alternative to passing them by position. Typically, for most RDBMS when one parameter argument is supplied by name then all subsequent parameter arguments must be supplied in the same manner. The order of the parameter names doesn't matter but for clarity the following alternative examples pass the parameter names in the same order that they are declared in the routine. When parameter arguments are not passed by name the values must be supplied in the identical order (left to right) as the parameters are declared in the routine.
SQL Server example of invoking the Ws_Connect_Replace PROCEDURE using named parameters:

DECLARE @v_return_code varchar(1)
DECLARE @v_return_msg varchar(256)
DECLARE @v_result_num integer
EXEC Ws_Connect_Replace
@p_sequence = 0
, @p_job_name = 'Test Job Name'
, @p_task_name = 'Test Task Name'
, @p_job_id  = 0
, @p_task_id = 0
, @p_action = 'REPLACE'
, @p_source = 'Connection1'
, @p_target = 'Connection2'
, @p_return_code = @v_return_code OUTPUT
, @p_return_msg = @v_return_msg  OUTPUT
, @p_result = @v_result_num  OUTPUT

Oracle example of invoking the Ws_Connect_Replace FUNCTION using named parameters via an anonymous PL/SQL block:

DECLARE
v_return_code varchar2(1);
v_return_msg varchar2(256);
v_result_num integer;
BEGIN
v_result_num := Ws_Connect_Replace
( p_sequence    => 0
, p_job_name    => 'Test Job Name'
, p_task_name   => 'Test Task Name'
, p_job_id      => 0
, p_task_id     => 0
, p_action      => 'REPLACE'
, p_source      => 'Connection1'
, p_target      => 'Connection2'
, p_return_code => v_return_code
, p_return_msg  => v_return_msg
);
END;
/

DB2 example of invoking the Ws_Connect_Replace PROCEDURE using named parameters via another procedure:

– The owner/schema is DSSDEMO.
DROP PROCEDURE DSSDEMO.invoke_DB2_Ws_Connect_Replace;
CREATE PROCEDURE DSSDEMO.invoke_DB2_Ws_Connect_Replace
LANGUAGE SQL
BEGIN
DECLARE v_return_code           varchar(1);
DECLARE v_return_msg            varchar(256);
DECLARE v_result_num            integer;
CALL DSSDEMO.Ws_Connect_Replace
( p_sequence    => 0
, p_job_name    => 'Test Job Name'
, p_task_name   => 'Test Task Name'
, p_job_id      => 0
, p_task_id     => 0
, p_action      => 'REPLACE'
, p_source      => 'Connection1'
, p_target      => 'Connection2'
, p_return_code => v_return_code
, p_return_msg  => v_return_msg
, p_result      => v_result_num
);
END
/
CALL DSSDEMO.invoke_DB2_Ws_Connect_Replace();

Invocation via ODBC

The following examples illustrate how to invoke a WhereScape RED Callable Routine via an ODBC connection (using a tool, such as WhereScape SQL Admin), which uses both input and output parameters. The examples work for a SQL Server RED repository but for another RDBMS the routine name may need to be qualified with the appropriate owner/schema. Refer to the detailed description of the Ws_Connect_Replace routine for an explanation of the parameters/arguments.
The following invocations via ODBC are equivalent and the only difference is the formatting as the first command uses a single line while the second command is formatted across multiple lines:

– OBDC Example 1 (single line).

{ CALL Ws_Connect_Replace( 0,'Test Job Name', 'Test Task Name', 0, 0, 'REPLACE', 'Connection1', 'Connection2', ?, ?, ?) };

– OBDC Example 2 (same command formatted using multiple lines).

{ CALL Ws_Connect_Replace
( 0
, 'Test Job Name'
, 'Test Task Name'
, 0
, 0
, 'REPLACE'
, 'Connection1'
, 'Connection2'
, ?
, ?
, ?
)
};

The result of the Ws_Connect_Replace invocation can be confirmed by checking the target connection. In addition, a log entry is created using the specified job and task names that can be viewed via the Logs—Recent Audit Trail Logs menu item of the WhereScape RED Scheduler. 

Invocation via the Command-Line

Each WhereScape RED Callable Routine can also be invoked from the command-line using an RDBMS-specific tool such as:

These tools can be used to invoke a WhereScape RED Callable Routine by connecting to the database, executing SQL statements, and disconnecting from the database. Refer to the RDBMS-specific documentation for details of how to connect and execute SQL via the relevant CLI tool as well as details of tool's return codes.
Typically, the CLI command will include options to specify the database, connection credentials, and the SQL statement to execute. Multiple SQL statements can typically be executed by terminating/delimiting each statement, using a semi-colon (;). In most cases, the SQL statement needs to be quoted (typically double-quoted) when specified on the command-line and if the SQL statements include embedded quotes (such as single-quotes around literals) then they may need to be "escaped" depending on the CLI tool and the platform. Most of the tools also allow the SQL commands to be read from a file instead of from standard input.