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. |