Database Operations

Version 23.4.8843

Operations

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

dbListTables

Lists the tables in the database.

Required Input Attribute	Description
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.

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 in the Java version or the ADO.NET provider name in the .NET version.
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 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 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 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 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 in the Java version or the ADO.NET provider name in the .NET version.
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 in the Java version or the ADO.NET provider name in the .NET version.
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 in the Java version or the ADO.NET provider name in the .NET version.
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.