...

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

...

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

...