This is an example configuration file:

<dbconnector version="2.0">

   <queryset name="myStore" timeStampFormat="yyyy-MM-dd HH:mm:ss" 
                            dateFormat="yyyy-MM-dd" 
                            xmlDateFormat="yyyy-MM-dd">

      <!-- HSQLDB -->
      <resource-def>
        <url>jdbc:hsqldb:file:mystore</url>       
        <driver>org.hsqldb.jdbcDriver</driver>
        <user>sa</user>
        <password></password>
      </resource-def>
      <query name="allOrders">
         <retrieve> 
           <statement>               
              SELECT * FROM "orders"                     
           </statement>                                     
        </retrieve>       
      </query>
      <query name="orders" autocreate="true">
         <retrieve> <!-- Maps to the HTTP GET method -->
            <statement>
                SELECT * FROM "orders" WHERE "orderId"='{orderId}'
            </statement>                                     
         </retrieve>
      </query>
      <query name="allProducts">
        <retrieve> 
          <statement>               
               SELECT * FROM "products"                
           </statement>                                     
        </retrieve>       
      </query>
   </queryset> 
   
</dbconnector>

The xml elements in the file are as follows:

• The queryset element groups together a Data Source definition and a list of query elements.
• The SQL queries defined under each query element will be executed against the respective Data Source.
• Each query element defines a SQL statement for each CRUD operation (create, retrieve, update, delete).
• You can define as many queryset elements as required with each pointing to a different Data Source. In essence, you can have one instance of the database connector working with multiple databases.

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.

SQL Statements

You define the scripts that will work with your database in the configuration file. For example, the sample configuration file defines a query called customers. That query assumes that a table such as the one below exists in your Data Source.

CREATE TABLE customers (
customerId INT,
firstName  VARCHAR(50),
lastName   VARCHAR(50)
)

The SQL statements are nested inside a <query> element and one query can include up to four SQL statements. This is part of the SQL-to-Browser translation—four is the “magical” number because under the covers the connector is translating the four basic SQL functions: create, retrieve, update and delete (CRUD) to the four basic browser functions of POST, GET, PUT and DELETE. That is reflected in the children elements of the <query> element: <create>, <update>, <retrieve> and <delete>.

One query cannot have two SQL statements of the same type. If you need two different <retrieve> statements (for example, Select * from customers and Select * from Product), you’ll need two different <query> elements.  A query may have fewer than four SQL statements—if users can’t delete data from your database via your forms, your query does not need a <delete> operation. 

Here is the retrieve operation for query customers. The SQL statement returns all records from the customers table that match a given customer id:

<query name="customers">          
   <retrieve>
   <!-- maps to the http GET method --> 
      <statement> 
        SELECT * FROM customers WHERE customerId='{customerId}'
      </statement>                                     
   </retrieve>
   <!-- Omitted other statements -->       
</query>

You can use any valid SQL statement in the configuration.    

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 = {cid}, @dept = {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.

<query name="customers" autocreate="true">

This property applies only when users submit an HTTP PUT request to the database connector. The property tells the database connector to create a new row in the database if one doesn't exist already meaning that the connector will run the create statement automatically if the update statement fails. In summary: 

• If the user is updating an existing record, the Update statement will work as it normally does and the autocreate function won’t kick in.
• If the user is adding a new record, the update statement will fail (by design, because the record cannot exist if the user hasn’t added it yet) and the Connector will then run the create statement.   

The autocreate feature is particularly useful when working with 's repeat control.  repeat control gives you the ability to work with dynamic collections, for instance: customers, cars, addresses, dependents and others. When the user loads the form, the form may be initialized with some items (we will see how to do that with  later). If the user adds new items to the collection and submits the form, those items will be automatically added to the database if autocreate=true 

This behavior is actually enabled by default so if you want to turn it off you can set autocreate to false.

Auto Delete Rows

The autodelete feature is useful when working with  repeat controls. Imagine you have a collection of elements in the form that were initialized from a database. If you eliminate an item in the collection and submit the form, the connector will automatically remove the item from the database.  For that to happen, set the attribute autodelete to true in the query element.

<query name="customers" autocreate="true" autodelete="true" deleteKey="customerId">

Behind the scenes, the connector actually compares the items in the database with what is submitted in the form. That comparison criteria is based on a key that you define with the attribute deleteKey (required). The deleteKey value is normally the name of the primary key in the table that contains the repeat items.

Dates and Timestamps

If you define a date, time or timestamp column in your database the database connector will need to know the format of those dates in order to properly parse them. Also, when the connector reads the dates from the database, it will transform them to XML dates and those also can have a specific format. You can define both the database and the xml date formats. Those definitions are done by defining attributes in the queryset element. For instance:

<queryset name="myStore" timeStampFormat="yyyy-MM-dd HH:mm:ss" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd">

In this case, the time stamp and date formats in the database are "yyyy-MM-dd HH:mm:ss" and "yyy-MM-dd" respectively. That is the format the database connector will use to parse the date types from the database.

On the other hand, when the XML documents is created, the date format will follow the definition of the attribute xmlDateFormat.

SQL Query Examples

A solid understand of SQL syntax is helpful when creating forms that interact with your database. Below are common and useful example queries.

Like Query

Sometimes it is useful to match a row where the matching string is not exact. To do this use the SQL Like and % wild card. In this example we want to retrieve all customers that have an email address with a specific email domain 'frevvo.com'. The % wild card must be coded into the configuration.xml query. It cannot be passed down to the query as part of the URI template.

<query name="customers"> 
    <retrieve>
        <statement> 
          SELECT * FROM customers WHERE emailAddr='%{domain}%'
        </statement> 
    </retrieve>
</query>