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.

If the JDBC profile name is exactly sonar, it is a profile reserved for internal system use, so it is always blocked regardless of the execution path. If you need to temporarily allow it for maintenance, incident response, or another operational purpose, set the boot switch logpresso.sonar.allow_system_profile to true (default: not set, blocked).

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.

Change history

The dbcall command has been available since before Sonar 4.0.