Version 23.4.8839

• Required Parameters
• Optional Parameters
• Output Attributes
• Example

Executes a query against 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.
• query: The SQL query string.

Optional Parameters

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

Output Attributes

• db:\: Output varies based on the query.

Example

This example executes a stored procedure in a MySQL database using the cross-platform edition of Arc. It SELECTs all films from the film table where the rating is PG-13 and outputs a file for each film. The filename is the film title, and the contents of the file are the film description. The paramname/paramvalue attributes are used to demonstrate how that can work in a script.

<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.query" value="SELECT * FROM `film` WHERE rating = @max_rating"/>
<arc:set attr="db.paramname#1" value="max_rating" />
<arc:set attr="db.paramvalue#1" value="PG-13" />

<arc:call op="dbQuery" in="db" out="results" >
  <!-- optional logging step to see the output of the query in the application log -->
  <arc:set attr="_log.info" value="[results.*]" />
  <!-- setting the data of each output file to be the description of the film -->
  <arc:set attr="output.data" value="[results.db:description]" />
  <!-- setting the output filename to be the title of each film -->
  <arc:set attr="output.filename" value="[results.db:title].txt" />
  <arc:push item="output" />
</arc:call>

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;"/>