...

...

...

...

...

...

...

...

...

...

...

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

...

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

...

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

...

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. The first example disables the queryset named BIRT and the second example disables the allCustomer query.

   Code Block
   title This example disables the querySet named BIRT
   <dbconnector> <queryset name="BIRT" enabled="false" ...>

   
   

   Code Block
   language sql title This example disables the querynamed allCustomers
   <queryset name="BIRT"> <query name="allCustomers" autocreate="true" enabled="false" > <retrieve> <!-- maps to HTTP GET --> <statement> SELECT customerNumber,customerName from Customers order by customerName </statement> </retrieve> </query>

   
   

2. The same can be done by adding the enabled property with a value of false as shown below to the dbconnector.properties in the standalone or tomcat bundles.  

   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

   
   

...

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

...