Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagehtml/xml
<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>

 


The configuration.xml contains all the SQL statements (create, retrieve, update, delete) you need to integrate with your forms. This file uses XML syntax. The key elements are:

...

Note the string {customerId}. This is an example of how to pass data from your form fields into your SQL statements. For example, if your form field named customerId contains the value 1234 than the select statement would return the record for customer 1234.

Note

Using > or < characters in the configuration.xml SQL statements is not supported. These characters need to be escaped, so < should be replaced by &lt; and > should be replaced by &gt; in the query.

Retrieving NULL or Blank Database columns

...

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. 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 emptyStringForEmptyResultSetattribute emptyStringForEmptyResultSet="true" is  is configured by default.

Frevvoproduct
 logs a warning to that effect. You may notice the an error message similar to the image below in the debug console when testing your forms if no rows are returned by a query.

 Image Modified

Post/Put to the Database Connector from a Business Rule

The Database Connector supports URL parameters and JSON payload in POSTThis 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.

...

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.

...

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.

...

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

...

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



Excerpt
hiddentrue

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.

Step 1 - Create a table in your database (SQL Server):

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

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

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

Code Block
<query name="insertformid" autocreate="true">
        <retrieve>
            <statement>EXEC dbo.getid @formid = '{formuid}'</statement>
        </retrieve>

Step 4 - Add this rule to your form

Code Block
/*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:

...

The autodelete feature is useful when working with

Frevvoproduct
 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. The autodelete function only works when the Write method is set to PUT. 

Code Block
languagehtml/xml
<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.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.

The Doc URI Approach: Performing CRUD Operations using a Table Control

The Doc URI or "data first" approach is an easy method to perform CRUD (CREATE, RETRIEVE, UPDATE, DELETE) operations on your database from a table or repeat control in your form. The best part is – using our powerful database connector, you don't need programming. 

This Doc URI approach with a Repeat or Table control is generally much easier and more efficient than using a business rule to connect your form to your database. This chart explains why you might choose this approach.

Doc URIBusiness Rules
No code neededJavascript code required
Automatically pass all controls from schemaPass each individual control
Call the DB Connector once for all operations (using the autocreate featureCall the DB Connector query for every add/update operation

You can use a form created from schema to perform all of the CRUD operations on your database table as long as all your SQL statements (SELECT, CREATE, UPDATE, and DELETE) are declared in the same DB Connector query. For example consider this query:

Code Block
<query name="products" autocreate="true" autodelete="true" deleteKey="id">
	<retrieve>
	    <statement>
		<!-- Maps to the HTTP GET method -->
		SELECT * FROM products WHERE orderId='{orderId}'
	    </statement>                                                    
	</retrieve>
	<create> 
	    <statement>
		INSERT INTO products
		(orderId, productName, quantity,price) 
		VALUES ({orderId}, '{productName}', '{quantity}', {price})
	    </statement>
	</create>
	<update>
	    <statement>
		UPDATE products SET  productName = '{productName}', quantity = {quantity}, price = {price}                                                
		WHERE id = {id}                                                                         
	    </statement>
	</update>
	<delete>
	    <statement>
	     DELETE from products where id={id} 
	     </statement>
	</delete>
</query>
  1. Note that the id column of this products table is a Primary Key/Unique column and an automatically incrementing number is assigned as value to this column (on the database side) when inserting a new record in Products table.
  2. Notice the autocreate and autodelete attributes of this query. Please see Auto Create Rows above.
  3. This query has all the CREATE, RETRIEVE, UPDATE, and DELETE statements declared in it.
  4. To create the schema, browse the DB connector query URL with /schema appended to it. For example: http://localhost:8082/database/BIRT/products/schema?orderId=10000
  5. After creating the form fields using this schema, configure the Doc Action -> Doc URI to:

    1. Read URL: http://localhost:8082/database/BIRT/products?orderId=\{orderId} with method: GET
    2. Write URL: http://localhost:8082/database/BIRT/products with method: PUT

      Info

      See this section for an explanation of how HTML statements (GET, PUT, etc.) translate to SQL statements (RETRIEVE, UPDATE, etc.)

  6. Test the form. Enter a valid orderId, and notice that the form table will automatically expand and display all the order items retrieved by the SELECT statement.
    1. Add a row and enter some data.
    2. Edit another row.
    3. Delete a different row.
      Image Added
  7. Submit the form. The database connector will
    1. Execute the INSERT statement for any form table row that was added.
    2. Execute the UPDATE statement for any form table row that was changed.
    3. Execute the DELETE statement for any form table row that was deleted.
Info

What is happening "behind the scenes"? When you use the Doc URI approach to read/add/update/delete rows, the controls in your form are bound to the schema (data source) from which they were created. When the form is submitted, frevvo creates an XML document which conforms to that schema. This XML document has all the values entered in those schema controls. When the form is submitted, this XML document is sent to the DB connector. The DB connector can get the control values directly from this document and use them while executing the SQL query. 

Dates and Timestamps

Date, Time and DateTime formats are managed automatically by the connector.  You do not need the attributes that define the formats in your querysets. The defaults now match what 
Frevvoproduct
sends to the DBConnector and should cover most cases. If you define a date, time or timestamp column in your database, the database connector will know the format of those dates in order to properly parse them. Below is an example of the attribute in the queryset element that is no longer needed.. For instance:

...

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

...