...

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

...

...

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>,  <retrieve>, <update> 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.

Support for NULL or Blank Values

By default, optional/empty form fields will send a null from the form field to the Db Connector and return an xml element or json property for an optional/empty form field when the database column contains a null. This is controlled by the emit Null column feature.

if you prefer optional/empty form fields to send/return an empty string, you can disable this behavior for the whole db connector or at the queryset/query level.

There are two ways to disable the emit Null column attribute:

1. Add the dbconnector.emitNullColumns=false property to the dbconnector properties file for your installation.

2. OR add the emitNullColumns="false" attribute to the respective element in the configuration.xml file for your installation:
   
   For example:

• To disable it at the instance level add the attribute at the top of configuration.xml

  Code Block
  <dbconnector version="2.5" emitNullColumns="false">

• To disable it at the queryset level, add the attribute to the queryset in configuration.xml

  Code Block
  queryset name="BIRT" emitNullColumns="false">

• To disable it at the query level, add the attribute to the query in configuration.xml

  Code Block
  <query name="lkadgroupmembers2" emitNullColumns="false">

If you choose to turn off the emit NULL columns feature, there is a way to insert a conditional null in an insert statement if you are using a MySQL database. Refer to the Conditional NULL in MySQL Insert Statement topic for the details.

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.

Queries with dollar sign or space in a column name

Queries with a dollar sign ($) or space in a column name like the one shown below may cause an invalid xml error. 

<retrieve>
    <statement> 
    	SELECT id, tenant, role, de$cription FROM roles;
    </statement>
</retrieve>

This workaround is recommended - Use an 'as' clause in the query like this:

<retrieve>
    <statement> 
    	SELECT id, tenant, role, de$cription as description FROM roles
    </statement>
</retrieve>

Empty resultsets

The XML Schema generated by the dbconnector requires at least 1 row (minOccurs=1). When the resultset has no rows, the connector sends an empty string, i.e. invalid XML instance. This is because the attribute emptyStringForEmptyResultSet="true" is configured by default.

logs a warning to that effect. You may notice an error message similar to the image below in the debug console when testing your forms.

 Image Added

This behavior can be controlled by adding the queryset attribute emptyStringForEmptyResultSet with a value of false to the queryset in the configuration.xml file or by adding the property dbconnector.queryset.emptyStringForEmptyResultSet=false to the dbconnector.properties file.

Image Added

Post/Put to the Database Connector from a Business Rule

The Database Connector supports URL parameters and JSON payload in POST/PUT requests. You can use http.post() and http.put() statements in a Live Forms business rule to send data to the frevvo Database Connector to insert/update records into your external database.

Use the http.post method to insert records into your database and use the http.put method to update existing records.

Let’s take a look at an example to explain this.

Create a form from the Database Connector schema (productDetails) to insert a record into the products table in an external database named classicmodels. We have added a Trigger control to make testing the rules easier. The MySQL classicmodels database has a table named products. When we execute the rule in our frevvo form, we want to insert a record into this database table.

Here is an image of the form:

Image Added

This query is included in the configuration.xml.

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

There are 3 ways to insert/update a record in your external database by passing data to the Database Connector.

1. A Doc URI - this is the simplest way but the Doc URI is only executed when a form is submitted or a flow has completed it’s final step. When the user submits the form or completes the flow, the URI will be executed with the POST method selected from the dropdown. The database connector will execute the Insert operation identified by the URI.

   Code Block
   http://localhost:8082/database/BIRT/productDetails

2. A  business rule to pass the data to the database connector post using URL query parameters.  When the user clicks on the Trigger control, this rule will run and the database connector will execute the Insert operation identified by the URL in the productDetails query in the configuration.xml file..

   Code Block
   language js
   if (trigger.clicked) { var PostURL = 'http://localhost:8082/database/BIRT/productDetails?productCode=' + productCode.value + '&productName=' + productName.value + '&productLine=' + productLine.value + '&productScale=' + productScale.value + '&productVendor=' + productVendor.value + '&productDescription=' + productDescription.value + '&quantityInStock=' + quantityInStock.value + '&buyPrice=' + buyPrice.value + '&MSRP=' + MSRP.value; http.post(PostURL); }

3. A  business rule to create JSON to post/put in the http request. This method is preferred over method 2 because there is a limit to the length of the URL string which will limit the number of form fields you can pass to the Database Connector.

   Code Block
   language js
   /*member MSRP, buyPrice, productCode, productDescription, productLine, productName, productScale, productVendor, quantityInStock*/ if (trigger.clicked) { var jp = { productCode: productCode.value, productName: productName.value, productLine: productLine.value, productScale: productScale.value, productVendor: productVendor.value, productDescription: productDescription.value, quantityInStock: quantityInStock.value, buyPrice: buyPrice.value, MSRP: MSRP.value, }; http.post('http://localhost:8082/database/BIRT/productDetails', jp); }

   Let’s analyze this rule:

   • The first line of the rule is the member directive required by the Live Forms rule validator
   • Line 2 is a conditional statement to specify that the rule runs when the Trigger control is clicked
   • var jp – defines the JSON payload variable
   • The next 9 lines define the JSON payload – specify the form field values that will be written to the columns in the products table.
   • The last line specifies the http.post operation to the database connector using the JSON payload. When the user clicks on the Trigger control, the database connector will execute the Insert operation identified by the URL in the productDetails query in the configuration.xml file..

You can also use a combination of data passed by URL parameters and JSON payload. Note that if a form field is specified both via a URL parameter and in the JSON payload, the URL parameter will take precedence. For example you can write a rule like this:

/*member MSRP, buyPrice, productCode, productDescription, productLine, productName, productScale, productVendor, quantityInStock*/

if (trigger.clicked) {
  var jp = {
    productCode: productCode.value,
    productName: productName.value,
    productLine: productLine.value,
    productScale: productScale.value,
    productVendor: productVendor.value,
    productDescription: productDescription.value,
    quantityInStock: quantityInStock.value,
    buyPrice: buyPrice.value,
    MSRP: MSRP.value,
  };
  http.post('http://localhost:8082/database/BIRT/productDetails?productCode='3', jp);
}

In this rule the value of “3” specified by the ?productCode=3 URL parameter overrides the value of the JSON object.

Writing rules with http.post and http.put requests eliminates the need to use a stored procedure to update/insert records into your database tables and then call that Stored Procedure from a business rule.

Stored Procedures

The Database Connector supports the use of Stored Procedures to update/insert data into a database table. Existing stored procedures can still be used. However, using the Post/Put to the Database Connector from a Business Rule accomplishes the same thing and is a more straight forward approach than using a stored procedure.

To use a stored procedure, you must:

• Create a stored procedure to update/insert the values into the database table
• Call this stored procedure in the <retrieve> tag of your configuration.xml query
• Use an http.get statement in your business rule to call this query. The stored procedure will execute and update/insert data into your table

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 the configuration.xml:

<query name="getOrderNumber">
    <retrieve>
        <statement> w
            call GetNewOrderNum() 
            </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='</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;

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>

/*member, UniqueID, resultSet*/
var x;
var formid;

formid = _data.getParameter('form.id');

if (form.load) {
  formuid.value = formid;
  
  if (formid.length > 0) {
    http.get('http://<your server>:<port>/database/CONTROL4/insertformid?formuid=' + formid);
    eval ("x=" + http.get('http://

...

<your server>:

...

<port>/database/

...

CONTROL4/getformid?formuid=' + formid + '&_mediaType=json'));
  

...

  ID.value = x.resultSet[0].

...

UniqueID;
  } 

SQL Server

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

...

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

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

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 When the XML documents is created, the date format will follow the definition of the attribute xmlDateFormat.

...

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}

...

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