Skip to main content
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="cdata.jdbc.sql.SQLDriver"/> 
<api:set attr="db.conn"    value="jdbc:sql:user=;password=;Server=;Database="/> 
<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 AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
Optional Input AttributeDescription
includesystemtablesWhether to include system tables. The allowed values are True and False. The default value is False.
schemaThe schema to which the table belongs.
Output AttributeDescription
db:nameThe name of the table.
db:typeThe type of the table.
db:schemaThe schema to which the table belongs.
db:catalogThe catalog to which the table belongs.

​
dbListColumns

Lists the columns of a table or a view.
Required Input AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
tableTable (or view) to list columns from.
Optional Input AttributeDescription
schemaThe schema to which the table belongs.
Output AttributeDescription
db:columnnameThe name of the column.
db:datatypeThe data type of the column.

​
dbQuery

Executes a query against the database.
Required Input AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
queryThe SQL query string to execute.
Optional Input AttributeDescription
commandtimeoutCommandTimeout, 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 AttributeDescription
db:*Output varies based upon the query.

​
dbNonQuery

Executes a nonquery against the database.
Required Input AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
queryThe SQL query string to execute.
Optional Input AttributeDescription
commandtimeoutCommandTimeout, 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 AttributeDescription
db:*Output varies based upon the query.

​
dbCall

Executes a stored procedure in the database.
Required Input AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
SpThe name of the stored procedure.
Optional Input AttributeDescription
commandtimeoutCommandTimeout, in seconds, for the operation to complete. Zero (0) means no timeout. This attribute defaults to 60.
dynamicThe 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.
outparamsThe 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 AttributeDescription
db:*Output varies based upon the stored procedure.

​
dbBeginTransaction

Start a transaction in the database.
Required Input AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
Optional Input AttributeDescription
transactiontimeoutTimeout, in seconds, of this transaction. Zero (0) means no timeout. The default value is 60.
transactionidThe Id of the transaction.

​
dbEndTransaction

End a Transaction by commiting or rolling back.
Required Input AttributeDescription
driverThe JDBC driver class name.
connThe connection string or database URL.
transactionactionThe action of the transaction. The allowed values are commit and rollback.
Optional Input AttributeDescription
transactionidThe identifier for the transaction.