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

...

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 frevvo business rule to send data to the frevvo Database Connector to insert/update records into your external database.

...

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

...

<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

...