...

Your SQL queries are defined in configuration.xml. The database connector looks for this file in

...

...

Configuration.xml Content

Here is a sample configuration.xml file for a queryset named BIRT.

Configuration.xml Content

Here is a sample configuration.xml file for a queryset named BIRT.

<dbconnector>
    <queryset name="BIRT">
        <query name="allProducts" autocreate="true">
            <retrieve>
                <!--maps to HTTP GET -->
                <statement> SELECT productCode, productName from Products order by productName </statement>
            </retrieve>
        </query>
        <query name="orderDetailsByOrder" autocreate="true">
            <retrieve>
                <!--maps to HTTP GET -->
                <statement> SELECT p.productName as product, o.quantityOrdered as quantity, o.priceEach as price,
                    p.productDescription as description, p.MSRP FROM OrderDetails o, Products p WHERE
                    o.productCode=p.productCode and o.orderNumber={onum} ORDER by o.orderLineNumber </statement>
            </retrieve>
        </query>
        <query name="productDetails" autocreate="true">
            <retrieve>
                <!--maps to HTTP GET -->
                <statement> SELECT * from Products order by productName </statement>
            </retrieve>
            <create>
                <statement>INSERT into Products (productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice,MSRP)
                   VALUES ('{productCode}','{productName}','{productLine}','{productScale}','{productVendor}','{productDescription}',{quantityInStock},{buyPrice},{MSRP})</statement>
            </create>
        </query>      
</dbconnector>

...

• <query> -  contains a SQL statement (create, retrieve, update, delete). . <<See See the Database Connector Tutorial for working examples>>examples.
• <queryset> - contains a set of <query> elements. Define one <queryset> element per database datasource. See <<Datasource Definitions>>. The database connector can simultaneously connect to multiple databases; each must have its own queryset.

...

if(x.resultSet[0].contactemailaddr) 
     vendorEmail.value = x.resultSet[0].contactemailaddr;
else
     vendorEmail.value = null;

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.

...

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.

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.

...

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

...

Unique Sequential Id

This example uses a stored procedure, a table in your database, a database connector query and a business rule to generate a unique sequential number when a form loads. This number can populate a ticket or invoice number field in your form.

...

SQL Server

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

Step 2 - Create a Stored Procedure (SQL Server):

CREATE PROCEDURE dbo.getid
@formid varchar (255)

AS
SET NOCOUNT ON;
INSERT INTO [dbo].[TBLUniqueID] ([formuid]) VALUES (@formid);

SELECT * from [dbo].[TBLUniqueID] WHERE formuid = '@formid';

Step 3 - Add the query to your configuration.xml file

...

...

CREATE TABLE dbo.TBLUniqueID
(
UniqueID int IDENTITY(10000,1) PRIMARY KEY,
formuid varchar (255) NOT NULL
)

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

...

...

...

...

Step 4 - Add this rule to your form

...

 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=' + 

...

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

...

<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

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

When the XML documents is created, the date format will follow the definition of the attribute xmlDateFormat.

Enable the Database Connector Cache

Normally the database connector retrieves a resultset from the database every time a query executes. Enabling caching will improve performance. A good time to use caching is when you are retrieving a resultset where the data does not change often. For example to retrieve a list of managers or product codes.

Add a cache element to each <retrieve> element you want to cache. The first time the <retrieve> runs the resultset gets cached. The resultset is retrieved from the database connector cache (not from your database) until the <timeToIdle> expires. 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 before retrieving a fresh resultset database to be cached again.. This example will refresh the cache every 300 seconds. If you set this value to 0, caching will never expire.

Send User ID in "Use Mode"

If you are looking for a simple way to communicate information about the logged in user when DOC URI's are invoked, subject parameters are now included in http headers whenever  is configured to make a call to the database connector on behalf of a given user.  has been changed so that a request header called _frevvo.subject.id will be populated in the following cases:

• http calls from rules
• DOC URI calls
• doc/form action posts

The database connector has been enhanced to inspect http headers for resolving query parameters. Any URL parameter that begins with "_frevvo" will be ignored. Only user id is supported at this time. 

In the database connector, these parameters can be referenced in the sql configuration file just like any other parameters:

select * from users where userid = {_frevvo.subject.id}

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>

...

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

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

When the XML documents is created, the date format will follow the definition of the attribute xmlDateFormat.

Enable the Database Connector Cache

Normally the database connector retrieves a resultset from the database every time a query executes. Enabling caching will improve performance. A good time to use caching is when you are retrieving a resultset where the data does not change often. For example to retrieve a list of managers or product codes.

Add a cache element to each <retrieve> element you want to cache. The first time the <retrieve> runs the resultset gets cached. The resultset is retrieved from the database connector cache (not from your database) until the <timeToIdle> expires. 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 before retrieving a fresh resultset database to be cached again.. This example will refresh the cache every 300 seconds. If you set this value to 0, caching will never expire.

Send User ID in "Use Mode"

If you are looking for a simple way to communicate information about the logged in user when DOC URI's are invoked, subject parameters are now included in http headers whenever  is configured to make a call to the database connector on behalf of a given user.  has been changed so that a request header called _frevvo.subject.id will be populated in the following cases:

• http calls from rules
• DOC URI calls
• doc/form action posts

The database connector has been enhanced to inspect http headers for resolving query parameters. Any URL parameter that begins with "_frevvo" will be ignored. Only user id is supported at this time. 

In the database connector, these parameters can be referenced in the sql configuration file just like any other parameters:

select * from users where userid = {_frevvo.subject.id}

Disabling QuerySets/Queries

When you are developing your integration, you might want to disable a particular queryset/query so you can focus on the one you are troubleshooting. QuerySets/queries can be disabled in one of two ways:

1. Add the enabled= attribute with a value of false to the <querySet/> or individual <query> elements in the configuration.xml file to completely disable it.

   Code Block
   <dbconnector> <querySet name="BIRT" enabled="false" ...>

2. The same can be done by adding the enabled property with a value of false as shown below to the dbconnector.properties(standalone bundle) or frevvo-config.properties (tomcat bundle).  

   Code Block
   dbconnector.querySet@<queryset name>.enabled=false

   This property disables all querysets. Add it to the dbconnector.properties(standalone bundle) or frevvo-config.properties (tomcat bundle).

   Code Block
   dbconnector.querySet.enabled=false

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>

CREATE TABLE dbo.TBLUniqueID
(
UniqueID int IDENTITY(10000,1) PRIMARY KEY,
formuid varchar (255) NOT NULL
)

CREATE PROCEDURE dbo.getid
@formid varchar (255)

AS
SET NOCOUNT ON;
INSERT INTO [dbo].[TBLUniqueID] ([formuid]) VALUES (@formid);

SELECT * from [dbo].[TBLUniqueID] WHERE formuid = '@formid';

<query name="insertformid" autocreate="true">
	<retrieve>
		<statement>EXEC dbo.getid @formid = '{formuid}'</statement>
	</retrieve>
</query>
<query name="getformid">
	<retrieve>
		<statement>SELECT [UniqueID] FROM [dbo].[TBLUniqueID] WHERE [formuid]={formuid}</statement>
	</retrieve>
</query>

/*member, UniqueID, resultSet*/
var x;
var formid = _data.getParameter('form.id');

if (form.load && !ID.value) {
  if (formid.length > 0) {
    http.get('http://<your server>:<port>/database/<queryset name>/insertformid?formuid=' + formid);
    eval ("x=" + http.get('http://<your server>:<port>/database/<queryset name>/getformid?formuid=' + formid + '&_mediaType=json'));
    ID.value = x.resultSet[0].UniqueID;
  } 
}