Search

dbCall

Version 24.2.9039

dbCall

Executes a stored procedure in the database.

Required Parameters

  • driver: The JDBC driver class name in the cross-platform edition or the ADO.NET provider name in the .NET edition.
  • conn: The connection string or database URL.
  • sp: The name of the stored procedure.

Optional Parameters

  • commandtimeout: The command timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default is 60.
  • paramname#: The parameter names.
  • paramvalue#: The parameter values.
  • paramtype#: The parameter types.
  • fromrow: The number of rows to skip from the beginning. The default is 0.
  • maxrows: The maximum number of rows to return from the database. Set to -1 (the default) to get all results.
  • querypassthrough: Pass the query to the operation as-is instead of performing client-side validation and syntax correction.

Output Attributes

  • db:\: Output varies based upon the stored procedure.

Example

This example executes a stored procedure in a MySQL database using the cross-platform edition of Arc.

<arc:set attr="db.driver" value="cdata.jdbc.mysql.MySQLDriver" />
<arc:set attr="db.conn" value="jdbc:cdata:mysql:server=localhost;port=3306;database=sakila;user=root;password=Password123;"/>
<arc:set attr="db.sp" value="film_in_stock"/>
<arc:set attr="db.paramname#1" value="p_film_id"/>
<arc:set attr="db.paramvalue#1" value="3"/>
<arc:set attr="db.paramname#2" value="p_store_id"/>
<arc:set attr="db.paramvalue#2" value="7"/>

<arc:call op="dbCall" in="db" out="results" >
  <!-- optional logging step to see the output of the SP in the application log -->
  <arc:set attr="_log.info" value="RESULTS = [results.*]" />
  <!-- adding the result data from the stored procedure to the file that is being created and pushed out -->
  <arc:set attr="output.data" value="[results.*]" />
  <!-- setting the filename --> 
  <arc:set attr="output.filename" value="storedProcResults.txt" />
</arc:call>

<arc:push item="output" />

You can modify the example above to target other databases as long as the driver is installed on the machine where Arc is installed and running. Adjusting this example for .NET involves changing the driver and conn inputs as follows:

<arc:set attr="db.driver" value="System.Data.CData.MySql" /> 
<arc:set attr="db.conn" value="Server=localhost;Database=sakila;UID=root;Password=Password123;"/>