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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.