DB Connector Reference¶ This documentation provides a reference guide for the DB Connector. The DB Connector allows you to connect to relational databases via JDBC and perform various operations including executing queries, modifying data, calling stored procedures, and managing transactions. Connection configurations¶ The DB Connector utilizes connection pooling for efficient database interaction. You need to configure a connection before using the connector operations. Connection parameters¶ Common parameters for configuring a database connection: Connection Parameters Parameter Name Description Required Connection Name A unique name to identify this connection configuration. Yes JDBC Driver Class The fully qualified name of the JDBC driver class (e.g., com.mysql.cj.jdbc.Driver, org.postgresql.Driver). Standard driver classes are supported. Yes Database URL The JDBC URL for connecting to the database (e.g., jdbc:mysql://localhost:3306/mydatabase, jdbc:postgresql://localhost:5432/mydatabase). Yes Database Username The username for database authentication. Yes Database Password The password for database authentication. Yes JDBC Driver Select Options Choose "Use Default Driver" to use the default driver for the connection else you can add your own driver with the options "Select Local Driver" or "Add Maven Dependency". All the standard drivers are supported. Yes Allow access to database metadata If this option is enabled, the VS Code extension will connect to the database during development and display the available tables and columns. This enhances the overall developer experience by providing easier access to database metadata. Additional parameters for connection¶ Parameter Name Description Required maxActiveConnections Maximum number of active connections that can be allocated from this pool at the same time. Optional maxIdleConnections Maximum number of connections that can remain idle in the pool, without extra ones being released. Optional minIdleConnections Minimum number of connections that can remain idle in the pool, without extra ones being created. Optional poolConnectionAgedTimeout The time in milliseconds after which an aged connection should be removed from the pool. Optional maxWaitTime The maximum number of milliseconds that the pool will wait (when no connections are available) for a connection to be returned before throwing an exception. Optional evictionCheckInterval The interval in milliseconds between runs of the idle object evictor thread. Optional minEvictionTime The minimum amount of time in milliseconds an object may sit idle in the pool before it is eligible for eviction by the idle object evictor. Optional exhaustedAction Specifies the behavior when the pool is exhausted (e.g., 'FAIL', 'BLOCK', 'GROW'). Optional Example: MySQL Connection<?xml version="1.0" encoding="UTF-8"?> <localEntry key="DBCon11" xmlns="http://ws.apache.org/ns/synapse"> <db.init> <connectionType>MYSQL</connectionType> <dbUrl>jdbc:mysql://localhost:3306/supermarket</dbUrl> <dbUser>user1</dbUser> <dbPassword>password</dbPassword> <driverClass>com.mysql.cj.jdbc.Driver</driverClass> <userConsent>true</userConsent> <dbDriver>MySQL</dbDriver> <exhaustedAction>WHEN_EXHAUSTED_BLOCK</exhaustedAction> <name>DBCon11</name> </db.init> </localEntry> Example: PostgreSQL Connection<?xml version="1.0" encoding="UTF-8"?> <localEntry key="PostgreSQLCon" xmlns="http://ws.apache.org/ns/synapse"> <db.init> <connectionType>POSTGRESQL</connectionType> <dbUrl>jdbc:postgresql://localhost:5432/supermarket</dbUrl> <dbUser>user1</dbUser> <dbPassword>password</dbPassword> <driverClass>org.postgresql.Driver</driverClass> <userConsent>true</userConsent> <dbDriver>PostgreSQL</dbDriver> <exhaustedAction>WHEN_EXHAUSTED_BLOCK</exhaustedAction> <name>PostgreSQLCon</name> </db.init> </localEntry> Operations¶ Operations use the configKey attribute to reference a pre-defined connection configuration. selectExecutes a SQL SELECT query using prepared statements. Parameter NameDescriptionRequired QueryThe Raw SQL SELECT statementYes Table NameThe name of the table to be queried.Optional Table ColumnsA comma-separated list of column names to be returned in the result set.Optional Response ColumnsA comma-separated list of data types for the columns (e.g., VARCHAR, INTEGER, DOUBLE).Optional Order ByThe column name to order the result set by.Optional LimitThe maximum number of rows to return from the result set.Optional OffsetThe number of rows to skip before starting to return results.Optional Fetch SizeThe number of rows to fetch at a time from the database.Optional Max RowsThe upper limit on the number of rows returned/affected by a query, regardless of the total number of rows in the table.Optional Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional Output Variable NameIf specified, the result set is stored in this context variable.Optional Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional Sample configuration <db.select configKey="DBCon1"> <query>SELECT * FROM `Products` WHERE `product_id` = '${params.queryParams.productId}'</query> <columnNames>product_id</columnNames> <columnTypes>VARCHAR</columnTypes> <format>json</format> <table>Products</table> <columns>[]</columns> <queryType>online</queryType> <orderBy></orderBy> <limit></limit> <offset></offset> <queryTimeout></queryTimeout> <fetchSize></fetchSize> <maxRows></maxRows> <responseVariable>db_select_1</responseVariable> <overwriteBody>false</overwriteBody> </db.select> Sample response (JSON in db_select_1 variable) [ { "product_id": "PROD001", "name": "Super Widget", "price": 19.99, "stock_quantity": 148 }, { "product_id": "PROD002", "name": "Mega Widget", "price": 29.99, "stock_quantity": 75 }, { "product_id": "PROD003", "name": "Ultra Widget", "price": 39.99, "stock_quantity": 50 } ] (Note: The exact JSON structure might vary based on the database schema and query.) insertExecutes a SQL INSERT statement using prepared statements. Can retrieve auto-generated keys. Parameter NameDescriptionRequired QueryThe Raw SQL INSERT statementYes Table NameThe name of the table to be inserted into.Optional Table ColumnsA comma-separated list of column names to be inserted.Optional Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional Output Variable NameIf specified, the result set is stored in this context variable.Optional Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional Sample configuration <db.insert configKey="DBCon11"> <query>INSERT INTO `orders` (`customer_id`, `total_amount`) VALUES (${params.queryParams.customerId}, ${vars.db_select_1.payload.rows[0].price * params.queryParams.quantity})</query> <columnNames>customerid, totalamount</columnNames> <columnTypes>INT, DECIMAL</columnTypes> <format>json</format> <table>orders</table> <columns>[]</columns> <queryType>online</queryType> <queryTimeout></queryTimeout> <responseVariable>db_insert_1</responseVariable> <overwriteBody>false</overwriteBody> </db.insert> Sample result (in insertResult variable) { "generatedKeys": [ { "GENERATED_KEY": 12345 } ], "affectedRows": 1 } deleteExecutes a SQL DELETE statement using prepared statements. Parameter NameDescriptionRequired QueryThe Raw SQL DELETE statementYes Table NameThe name of the table to be deleted from.Optional Table ColumnsA comma-separated list of column names involved in the WHERE clause.Optional Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional Output Variable NameIf specified, the result set is stored in this context variable.Optional Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional Sample configuration <db.delete configKey="DBCon22"> <query>DELETE FROM `OrderItems` WHERE `order_item_id` = 1 AND `product_id` = 'PROD001'</query> <columnNames>order_item_id, productid</columnNames> <columnTypes>INT, VARCHAR</columnTypes> <format>json</format> <table>OrderItems</table> <columns>[]</columns> <queryType>online</queryType> <queryTimeout></queryTimeout> <responseVariable>db_delete_1</responseVariable> <overwriteBody>false</overwriteBody> </db.delete> Sample result (in db_delete_1 variable) { "affectedRows": 1 // Number of rows affected by the delete operation } callProcedureCalls a database stored procedure or function. Parameter NameDescriptionRequired QueryThe Raw SQL CALL statementYes Stored Procedure NameThe name of the table to be queried.Optional ParametersA comma-separated list of column names to be returned in the result set.Optional Fetch SizeThe maximum time in milliseconds to wait for the query to execute.Optional Max RowsThe maximum time in milliseconds to wait for the query to execute.Optional Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional Output Variable NameIf specified, the result set is stored in this context variable.Optional Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional Sample configuration (Conceptual) <db.call configKey="DBCon33"> <query>CALL `GetEmployeesByDepartment`(2, '3')</query> <columnNames>deptID, deptName</columnNames> <columnTypes>INT, VARCHAR</columnTypes> <format>json</format> <table>GetEmployeesByDepartment</table> <columns>[]</columns> <queryType>online</queryType> <queryTimeout></queryTimeout> <fetchSize></fetchSize> <maxRows></maxRows> <responseVariable>db_call_51</responseVariable> <overwriteBody>false</overwriteBody> </db.call> Sample result (in db_call_51 variable) { "outputParameters": { "deptID": 2, "deptName": "Sales" }, "resultSets": [ /* Optional: if procedure returns result sets */ ] } executeQueryExecutes an arbitrary SQL statement. Use when you need to execute complex queries. Parameters can be passed in the SQL statement using the ? placeholder. The parameters are passed in the order they appear in the SQL statement. Prepared statement support is currently available only for executeQuery operation. Parameter NameDescriptionRequired QueryThe SQL statement to execute.Yes Prepared StatementIf 'true', the SQL statement is treated as a prepared statement. If 'false', it is treated as a regular SQL statement.Yes Result SetIf 'true', the result is treated as a result set. If 'false', it is treated as an update count.Yes Fetch SizeThe maximum time in milliseconds to wait for the query to execute.Optional Transaction IsolationThe maximum time in milliseconds to wait for the query to execute.Optional Max RowsThe maximum time in milliseconds to wait for the query to execute.Optional Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional Output Variable NameIf specified, the result set is stored in this context variable.Optional Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional Sample configuration <db.executeQuery configKey="DBCon1"> <sql>SELECT o.order_id, o.order_date, o.status, o.total_amount, c.customer_name, c.email, oi.product_id, p.name AS product_name, oi.quantity, oi.unit_price FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id JOIN Order_Items oi ON o.order_id = oi.order_id JOIN Products p ON oi.product_id = p.product_id WHERE o.order_id = ?</sql> <columns>[[1,"INTEGER"]]</columns> <isPreparedStatement>true</isPreparedStatement> <isResultSet>true</isResultSet> <resultType>JSON</resultType> <responseVariable>db_execute_query_1</responseVariable> <overwriteBody>false</overwriteBody> </db.executeQuery> Sample result (in db_execute_query_1 variable) [{ "order_id": 101, "order_date": "2025-04-30 14:23:45", "status": "Shipped", "total_amount": 199.99, "customer_name": "John Doe", "email": "[email protected]", "product_id": "PROD001", "product_name": "Super Widget", "quantity": 2, "unit_price": 19.99 }] beginTransactionStarts a new database transaction associated with the current message context. Subsequent DB operations within the same flow (until commit or rollback) will participate in this transaction. Parameter NameDescriptionRequired isolationLevelSpecifies the transaction isolation level (e.g., TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE). Default depends on the database.Optional The isolationLevel parameter can take one of the following values: - TRANSACTION_READ_UNCOMMITTED: Allows reading uncommitted changes (dirty reads). - TRANSACTION_READ_COMMITTED: Prevents dirty reads; allows non-repeatable reads. - TRANSACTION_REPEATABLE_READ: Prevents dirty reads and non-repeatable reads; allows phantom reads. - TRANSACTION_SERIALIZABLE: Prevents dirty reads, non-repeatable reads, and phantom reads. This is the highest isolation level. Sample configuration <db.beginTransaction configKey="DBCon1"> <timeout>300</timeout> <isolationLevel>TRANSACTION_READ_COMMITTED</isolationLevel> </db.beginTransaction> commitTransactionCommits the database transaction associated with the current message context. Sample configuration <db.commitTransaction configKey="DBCon1"> <enable>true</enable> </db.commitTransaction> rollbackTransactionRolls back the database transaction associated with the current message context. Sample configuration <db.rollbackTransaction configKey="DBCon1"> <enable>true</enable> </db.rollbackTransaction> Note: For a practical example demonstrating transaction management and various operations, see DB Connector Example.