Your SQL queries are defined in configuration.xml. The database connector looks for this file in

• Standalone bundle - <db-home>\database\database-connector-(DBC version number)\config.
• frevvo-tomcat bundle - The location you specified in <frevvo-home>\tomcat\conf\frevvo-config.properties via the frevvo.connectors.database.configuration property.

...

...

Configuration.xml Content

Here is a sample configuration.xml file for a queryset named BIRT.

...

...

Your SQL queries are defined in configuration.xml. The database connector looks for this file in

• Standalone bundle - <db-home>\database\database-connector-(DBC version number)\config.
• frevvo-tomcat bundle - The location you specified in <frevvo-home>\tomcat\conf\frevvo-config.properties via the frevvo.connectors.database.configuration property.

Configuration.xml Content

Here is a sample configuration.xml file for a queryset named BIRT.

<dbconnector>
    <queryset name="BIRT">
        <query name="allProducts" autocreate="true">
        o.productCode=p.productCode and o.orderNumber={onum} ORDER by o.orderLineNumber   <retrieve>
                <!--maps to HTTP GET -->
                <statement> SELECT productCode, productName from Products order by productName </statement>
            </retrieve>
        </query>
        <query name="productDetailsorderDetailsByOrder" autocreate="true">
            <retrieve>
                <!--maps to HTTP GET -->
                <statement> SELECT *p.productName fromas Products order by productName </statement>product, o.quantityOrdered as quantity, o.priceEach as price,
            </retrieve>        p.productDescription as description, p.MSRP  <create>FROM OrderDetails o, Products p WHERE
                 <statement>INSERT into Products (productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice,MSRP) o.productCode=p.productCode and o.orderNumber={onum} ORDER by o.orderLineNumber </statement>
            </retrieve>
       VALUES ('{productCode}','{productName}','{productLine}','{productScale}','{productVendor}','{productDescription}',{quantityInStock},{buyPrice},{MSRP})</statement></query>
        <query name="productDetails" autocreate="true">
            <retrieve>
</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:

...

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

• <query> -  contains a SQL statement (create, retrieve, update, delete). See the Database Connector Tutorial for working examples.
• <queryset> - contains a set of <query> elements. Define one <queryset> element per database datasource. See <<Datasource Definitions>>. The database connector can simultaneously connect to multiple databases; each must have its own queryset.

...

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.

frevvo logs a warning to that effect. You may notice an error message that reads "Premature end of file", similar to the image below, in the debug console when testing your forms.

...

The Database Connector supports URL parameters and JSON payload in POST/PUT requests. You can use http.post() and http.put() statements in a frevvo 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.

...

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

...

A Doc URI - this is the simplest way but the Doc URI is only executed when a form is submitted or a workflow has completed it’s final step. When the user submits the form or completes the workflow, 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.

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

...

() statements in a frevvo 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 workflow has completed it’s final step. When the user submits the form or completes the workflow, 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 frevvo 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 frevvo 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 frevvo 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,
   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

   • ..

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

...

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.

/*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 frevvo 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 ;

...

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

SQL Server

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

<query name="getOrderNumber">
    <retrieve>
        <statement>
            callexec 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:

...

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

Stored Procedure to Insert Rows

Here is an example of using a Stored Procedure with an insert statement that can be called in a business rule using http.get. For this example we created a table 'interns' in the Classic Models sample database.

use classicmodels;
CREATE TABLE interns (
  `AutoID` int(11) NOT NULL AUTO_INCREMENT,
  `StartDate` varchar(255) DEFAULT NULL,
  `JobType` varchar(255) DEFAULT NULL,
  `JobTitle` varchar(255) DEFAULT NULL,
  `NameOfIntern` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`AutoID`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;

Create the Stored Procedure.

DELIMITER $$
CREATE PROCEDURE `SP_Interns`(IN StartDate varchar(255),IN JobType varchar(255),IN JobTitle varchar(255),IN NameOfIntern varchar(255))
BEGIN
INSERT INTO interns (StartDate,JobType,JobTitle,NameOfIntern) VALUES (StartDate,JobType,JobTitle,NameOfIntern);
SELECT
    MAX(AutoID) AS autoid
FROM
    interns;
END$$
DELIMITER ;

Add this query to the configuration.xml file.

<queryset name='interns'>
   	<query name="interns_forschema">
	            <retrieve>
	        <statement>        <statement>SELECT StartDate,JobType,JobTitle,NameOfIntern FROM interns</statement>
	 exec GetNewOrderNum          </statement>retrieve>
	      </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">
	        <query name="addInterns" autocreate="true">
	     <retrieve>       <retrieve>
	 <statement>             exec GetNewOrderNum @cust<statement>call =SP_Interns ({cidStartDate}, @dept ={JobType}, {JobTitle}, {did}NameOfIntern})</statement>
	            </statement>retrieve>
        </retrieve>query>
   </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.

queryset>

Create a form from the schema query above.

Image Added

Write a business rule to call the Stored Procedure. In this case we used a trigger control, but you may want to call it when a particular workflow step is completed on form.unload or another condition.

var x;
if (SendToDatabse.clicked){
eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber?cid/interns/addInterns?StartDate=' + 
	StartDate.value + '&JobType=' + JobType.value + '&JobTitle=' + 
	customerIdJobTitle.value + '&didNameOfIntern=' + departmentNameOfIntern.value));
}	

When the user fills in the details and clicks Send to Database, the values are inserted in the 'interns' table:

Image Added

Auto Create Rows

You can set the attribute autocreate in a query element.

...

The autocreate feature is particularly useful when working with frevvo's repeat control. frevvo's 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 frevvo 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 

...

The autodelete feature is useful when working with frevvo 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. 

...

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

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

   
   

...

Consider a case where you want to display customers from your database table based on the billing type you select in your form. The billing type options are all, annual, and monthly. Your query passes the billing type selected and returns results. If the user selects annual or monthly, your SQL SELECT statement works well since those are actual values in the database. However, 'all' is not a valid option, and will not returns results. One option is to create a second query that does not pass a value for billing type. The simpler option to add an IN condition to your SELECT statement, and then use a simple business rule in your form to create a string with all possible options. First, create your query. For this example, we've added a column "billingType" to the Classic Models sample database 'customers' table. Note the FIND_IN_SET() function in this statement, which takes two parameters (columnname, {value template}). We will pass a value to the template using a control template from our form that represents the user's billing type selection. The function searches for the column's value in the string returned by the /{billingType} template.

  <queryset name="customers">
   	<query name="billingInfo">
   		<retrieve>
   			<statement>
   				SELECT customerName, customerNumber, billingType FROM customers WHERE FIND_IN_SET(billingType, {billingType})
   			</statement>
   		</retrieve>
   	</query>
   </queryset>

Next, create a form form that has a radio control to select the billing type and a table from schema to show the results. Add a hidden text field named "BillingTypeForSQL". You will use this capture the billingType value and send it in the query URL. 

...