...

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, the URI will be executed but now with the POST method selected from the dropdown. The database connector will than execute the Insert operation identified by the URI.

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

2. A Live Forms A  business rule to pass the data to the database connector post using URL query parameters. One consideration when using this method is that URLs have a maximum length. This limits the number of URL parameters you can add therefore limiting the amount of fields you can send.

   Code Block

    

    

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;

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

...

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:

...

1. Business rules execute in a form/flow when the specified condition is met. When the user clicks on the Trigger control, the database connector will execute the Insert operation identified by the URI 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); }

2. 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
   • 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 URI 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 will override 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

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>

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.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?cid=' + 
	customerId));
onum.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.

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>

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;

...

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

<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

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

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>