dbQuery

Version 22.0.8209


dbQuery

Version 22.0.8209


Executes a query against the database.

Required Parameters

  • driver: The JDBC driver class name in the Java version or the ADO.NET provider name in the .NET version.
  • 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. This attribute defaults to 60. The default value is ‘60’.
  • fromrow: Number of rows to skip from start. The default value is ‘0’.
  • maxrows: Maximum number of rows to return from the database. Set to -1 to get all results. The default value is ‘-1’.
  • paramname#: The parameter names.
  • paramvalue#: The parameter values.
  • paramtype#: The parameter types.
  • transactionid: The Id of the transaction.

Output Attributes

  • *: Output varies based upon the query.

Example

The ArcScript sample below uses a Script Connector to execute a query against a SQLite database:

<arc:set attr="db.driver" value="System.Data.SQLite" />
<arc:set attr="db.conn" value="Data Source=C:\\Users\\JoeUser\\Documents\\SecretSanta_2021.db" />
<arc:set attr="db.query" value="SELECT * FROM Participants WHERE Name=@Name AND Email=@Email" />

<arc:set attr="db.paramname#1" value="Name" />
<arc:set attr="db.paramname#2" value="Email"/>
<arc:set attr="db.paramvalue#1" value="Joe" />
<arc:set attr="db.paramvalue#2" value="joeuser@cdata.com" />

<arc:call op="dbQuery" in="db" out="record">
  <arc:enum item="record">
    <arc:set attr="output.filename" value="[_attr] is [_value]" />
    <arc:push item="output" />
  </arc:enum>
</arc:call>

Once executed, the script outputs files indicating the values of each column for the matching records.