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

...

<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

...

Add a Text control named "ID" in your form where the unique sequential number will be saved.

SQL IN Statements

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. 

Image Added

Add a business rule that will set a value in your hidden BillingTypeForSQL text control. If the use selects 'all', set the text control all possible values in a comma separated string. The characters '%2C' encode the commas so they can be passed in the URL. Otherwise, set the text control to the value specified in the radio control.

if (form.load || SelectBillingType.value) {
  if (SelectBillingType.value === 'all') {
    BillingTypeForSQL.value = "annual%2Cmonthly%2Cquarterly";
  } else {
    BillingTypeForSQL.value = SelectBillingType.value;
  }
}

Finally, set your Doc URI Read URL to the query URL with the parameter ?billingType={BillingTypeForSQL}. This sends the control template's value to the query template, and returns the results. This simple example works with or without the control template parameter !encode=true. You can encode the URL if the template value might have spaces or special characters.

Image Added

Save and test your form. The result is that selecting 'annual' or 'monthly' returns just customers with that billing type, but selecting 'all' returns customers with any billing type.

Image Added

Image Added

Image Added