Database Operations

Version 24.2.9064


Database Operations


Database operations enable you to connect to databases and execute queries against them. You can use these operations to apply transformations after a job run, create a logging system, and much more.

The example below uses the dbNonQuery operation in the post-job event to insert a new row that logs the job results:

<api:info title="After Run" desc="This event is fired after running a job.">
  <input name="JobName"         required="true" desc="The name of the job being executed." />
  <input name="Source"          required="true" desc="The name of source connection." />
  <input name="Destination"     required="true" desc="The name of destination connection." />
  <input name="JobStatus"       required="true" desc="The status of the completed run."/>
  <input name="Query#"          required="true" desc="An array containing each query that was executed." />
  <input name="QueryStatus#"    required="true" desc="An array containing the status of each query that was executed." />
</api:info>

<api:set attr="db.driver"  value="System.Data.SqlClient"/> 
<api:set attr="db.conn"   value="Data Source=sqlserver;Initial Catalog=Test;User ID=user;Password=pass;"/> 
<api:set attr="db.query">
INSERT INTO MyLog (JobName, RunDate, Results) VALUES ('[_input.JobName]', '[null | now()]', '[_input.JobStatus]')
</api:set>
<api:call op="dbNonQuery" in="db"/>

Operations

dbListTables

Lists the tables in the database.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
Optional Input Attribute Description
includesystemtables Whether to include system tables. The allowed values are True and False. The default value is False.
schema The schema to which the table belongs.
Output Attribute Description
db:name The name of the table.
db:type The type of the table.
db:schema The schema to which the table belongs.
db:catalog The catalog to which the table belongs.

dbListColumns

Lists the columns of a table or a view.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
table Table (or view) to list columns from.
Optional Input Attribute Description
schema The schema to which the table belongs.
Output Attribute Description
db:columnname The name of the column.
db:datatype The data type of the column.

dbQuery

Executes a query against the database.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
query The SQL query string to execute.
Optional Input Attribute Description
commandtimeout CommandTimeout, in seconds, for the operation to complete. Zero (0) means no timeout. This attribute defaults to 60.
paramname# Parameter names in the SQL statement.
paramValue# Parameter values in the SQL statement.
Output Attribute Description
db:* Output varies based upon the query.

dbNonQuery

Executes a nonquery against the database.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
query The SQL query string to execute.
Optional Input Attribute Description
commandtimeout CommandTimeout, in seconds, for the operation to complete. Zero (0) means no timeout. This attribute defaults to 60.
paramname# Parameter names in the SQL statement.
paramValue# Parameter values in the SQL statement.
Output Attribute Description
db:* Output varies based upon the query.

dbCall

Executes a stored procedure in the database.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
Sp The name of the stored procedure.
Optional Input Attribute Description
commandtimeout CommandTimeout, in seconds, for the operation to complete. Zero (0) means no timeout. This attribute defaults to 60.
dynamic The type of stored procedure. The value should be true if this is a dynamic stored procedure. The default value is false.
inparam:* Stored procedure input parameters with a value other than null.
outparams The stored procedure output parameters, separated by commas.
paramtype:* The data type of a specified data column.
paramsize:* The columns size of a specified data column.
Output Attribute Description
db:* Output varies based upon the stored procedure.

dbBeginTransaction

Start a transaction in the database.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
Optional Input Attribute Description
transactiontimeout Timeout, in seconds, of this transaction. Zero (0) means no timeout. The default value is 60.
transactionid The Id of the transaction.

dbEndTransaction

End a Transaction by commiting or rolling back.

Required Input Attribute Description
driver The JDBC driver class name.
conn The connection string or database URL.
transactionaction The action of the transaction. The allowed values are commit and rollback.
Optional Input Attribute Description
transactionid The identifier for the transaction.