Database Operations


Database Operations


Database Operations allow you to connect to databases and execute queries against them. These operations can be used for applying transformations after a job run, creating 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 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 or not. The allowed values are ‘True, 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 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. 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, rollback’.
Optional Input Attribute Description
transactionid The Id of the transaction.