...

This is an example configuration file:

...

If you update the configuration file, the database connector will pick up changes automatically.

Enable the Database Connector Cache

The Database Connector can cache result sets (read-only data) to improve performance if you want to reduce the number of times your database is being accessed. Simply add the cache element to the retrieve elements in the configuration file.

The first time the query runs the results set gets cached if it is not already cached. Data of ensuing invocations will be retrieved from cache until it has expired.

Here is an example:

<query name="getRoleInMsg" autocreate="true">
	<retrieve>
		<statement> SELECT role FROM roles WHERE tenant="tn14" </statement>
		<cache>
			<timeToIdle>300</timeToIdle>
		</cache>
	</retrieve>
</query>

The timeToIdle parameter specifies the number of seconds that must expire before a fresh object is retrieved from the database to be cached again. In the example, this is set for 300 seconds. If you set this value to 0, caching will never expire.

• Negative values for cache are not allowed.
• If you do not include the cache element, then your retrieve statements are not configured for caching. Results will be retrieved from the database every time the query runs.

Empty resultsets

If the results of a query are empty, the Database Connector returns an empty HTTP response. This is because the attribute emptyStringForEmptyResultSet="true" is configured by default. You may notice the error message below in the debug console when testing your forms if no rows are returned by a query.

Image Added

SQL Statements

...

Note the string {customerId}. The database connector SQL statements are actually templates that are resolved at run time using values passed in the http request. In the example above, if there is a parameter in the http GET request that hits the connector with customerId=1234 than the statement would return the record for customer 1234.

Conditional NULL in MySQL Insert Statement

If you are using the MySQL database, there is a special syntax to insert a conditional null in an insert statement. For Example, let's say your form/flow has an optional date field in a form that is designed to insert a row into the database. The insert will fail because the database connector inserts an empty string where MySQL expects NULL.

Use this Insert statement to resolve the issue:

INSERT into log (VisitorName,optional_date)  VALUES ('{VisitorName}', (CASE {optional_date} WHEN '' THEN NULL ELSE {optional_date} END))

In this example, {optional_date} refers to a column of type date and is optional. When its empty in your form, NULL is inserted fulfilling the MySQL requirement otherwise the value in the field will be inserted.

Stored Procedures

You can also execute stored procedures via the database connector. Here is an example of a mySql stored procedure.

DELIMITER //
CREATE PROCEDURE GetNewOrderNum()
    BEGIN
    SELECT max(orderNumber) + 1 as onum FROM Orders;
    END //
DELIMITER ;

To call this from the mySql command line you would use the command: call GetNewOrderNum(); To call this from the database connector you would add the following to configuration.xml:

<query name="getOrderNumber">
    <retrieve>
        <statement>
            call GetNewOrderNum() 
        </statement> 
   </retrieve> 
</query>

To retrieve the next order number from your database and populate that value into a control in your form named 'onum', add the following business rule to your form:

eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber'));
onum.value = x.resultSet[0].onum;

SQL Server

Here is an example with the syntax required for a SQL server stored procedure:

<query name="getOrderNumber">
    <retrieve>
        <statement>
            exec GetNewOrderNum 
        </statement> 
    </retrieve> 
</query>

To pass form field values to your SQL server stored procedure, append the variables to the end of the exec line. For example if your form contains a field named customerId and department, and your sproc takes two arguments @cust and @dept:

<query name="getOrderNumber">
    <retrieve>
        <statement>
            exec GetNewOrderNum @cust = {customerIdcid}, @dept = {department}did} 
        </statement> 
    </retrieve> 
</query>

If you need to call this stored procedure from a business rule you can pass the form data to the database connector as shown below. Note that customerId and department are the name of two controls in your form and that cid and did are the two url parameters in the http URL below.

eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber?cid=' + 
	customerId.value + '&did=' + department.value));

Auto Create Rows

You can set the attribute autocreate in a query element.

...