> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sync.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

```xml theme={null}
<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

* [List all Tables](#dblisttables)
* [List Columns in Table](#dblistcolumns)
* [Execute a Query](#dbquery)
* [Execute a NonQuery](#dbnonquery)
* [Execute a Stored Procedure](#dbcall)
* [Begin a Transaction](#dbbegintransaction)
* [End a Transaction](#dbendtransaction)

### 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`. |
| paramname#               | Parameter names in the SQL statement.                                                                                         |
| paramValue#              | Parameter values in the SQL statement.                                                                                        |
| 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. |