dbcall

Calls a stored procedure in an external database via JDBC. You can define input and output parameters in the SQL statement using names that start with a colon (:).

Command properties

Syntax

Target

PROFILE

JDBC profile name. Can be specified as a query parameter. The JDBC profile must be registered in advance.

SQL

SQL statement for calling the stored procedure. Can include input and output parameters.

• Input parameters: Specify in the format :name. The query parameter value set by the set command is substituted.
• Output parameters: Specify in the format :name(type). The type must be one of varchar, int, or datetime.

Error codes

Parse errors

Runtime errors

N/A

Description

The dbcall command connects to an external database registered as a JDBC profile and calls a stored procedure. You can define input and output parameters in the SQL statement using names that start with a colon (:).

Input parameters are specified in the format :name. The query parameter value set by the set command is substituted. String values are automatically enclosed in quotes, and numeric values are inserted as-is.

Output parameters are specified in the format :name(type). Supported types are:

• varchar: String
• int: Integer
• datetime: Date/time

The output varies depending on how the stored procedure returns results:

• When only output parameters are returned without a result set, one record composed of the output parameters is output.
• When both a result set and output parameters are returned, the output parameter fields are added to all records in the result set.
• When multiple result sets are returned, all result sets are output sequentially.

Examples

1. Calling a stored procedure

   dbcall mydb {call sp_get_users()}
   

   Calls the sp_get_users stored procedure using the mydb JDBC profile and outputs the result set.

2. Calling a stored procedure with input parameters

   set dept="sales"
   | dbcall mydb {call sp_get_employees(:dept)}
   

   Sets the dept query parameter to sales and calls the sp_get_employees stored procedure with the parameter.

3. Calling a stored procedure with output parameters

   dbcall mydb {call sp_count_orders(:total_count(int))}
   

   Calls the sp_count_orders stored procedure and outputs a record containing the integer-type output parameter total_count.

4. Using both input and output parameters

   set category="electronics"
   | dbcall mydb {call sp_category_summary(:category, :item_count(int), :total_amount(varchar))}
   

   Passes the category input parameter and outputs a record containing the item_count and total_amount output parameters.