Page Comparison

...

Now, let's look at submitting form data to the database. We will create a form using which a customer can place a new order. The order is then saved to the database. Note that we have simplified the form for demonstration purposes.

Define queries

We will define There are two queries defined in the built-in configuration file. The first one creates an order. In addition to the usual <retrieve> operation, this query also has a <create> operation.

 <query name="createOrder">
            <retrieve>
                <statement>
                    SELECT "orderNumber" as "onum", "customerNumber" as "cnum" from "Orders" WHERE "orderNumber"=10100
                  WHERE orderNumber=10100<</statement>
            </retrieve>
            <create>
                <statement>INSERT into Orders (orderNumber,orderDate, requiredDate, status,customerNumber)<statement>
                    INSERT INTO VALUES ({onum}, Now(), Now(), 'In Process', {cnum})"Orders" 
                     </statement>   ("orderNumber","orderDate","requiredDate","status","customerNumber")
                    VALUES 
                   </create>     ({onum}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,  </query>

...

'In Process',{cnum})
                </statement>
            </create>
</query>

Let's understand this query. We specify a <retrieve> operation (SELECT statement) in order to generate an XML schema/form controls. When the form is submitted,  generates an XML document for those controls. The XML document is used to execute the <create> operation, which INSERTs INSERTS the data. We have simply hard-coded a known order number for the example but you can use any query that generates a result set with the desired columns.

...

<query name="getOrderNumber">
            <retrieve>
                <statement>SELECT<statement>
max(orderNumber) + 1 as onum FROM Orders</statement>             </retrieve> SELECT max("orderNumber") + 1 as "onum" FROM "Orders"
                </statement>
            </retrieve>
</query>

Once again, this is an oversimplification for tutorial purposes. In practice, order numbers might be generated by the outer application rather than the database.

...

Now, we'll add line items to the order.

Define queries

Once again, we will require two queriesThere are two queries defined in the built-in configuration.xml file. The first one fetches product codes and descriptions from the database so that the user can select a product for purchase.

<query name="allProducts" autocreate="true">
            <retrieve>
                <!--maps<statement>
to HTTP GET -->                 <statement> SELECT "productCode", "productName" from "Products" order by "productName </statement>"
                </retrieve>statement>
            </retrieve>
</query>

The second query creates the order detail line item records in the database.

 <query name="createOrderDetail">
            <retrieve>
                <statement>
      <statement>SELECT orderNumber as onum,productCode,quantityOrdered,priceEach,orderLineNumber           SELECT 
        from OrderDetails where orderNumber=10100             "orderNumber" as "onum","productCode","quantityOrdered","priceEach","orderLineNumber"
 </statement>             </retrieve>      FROM 
     <create>                 <statement>INSERT into "OrderDetails (orderNumber,productCode,quantityOrdered,priceEach,orderLineNumber)"
                    WHERE 
   VALUES ({onum}, '{productCode}',{quantityOrdered},{priceEach},{orderLineNumber})                    "orderNumber"=10100
                </statement>
            </create>retrieve>
            <create>
                    </query>

 The steps are very similar to the earlier examples.

Generate XML schema

• In your browser go to the URL: http://localhost:8082/database/BIRT/createOrderDetail/schema Save the resulting XML schema file to disk.
• Upload the schema to . See Data Sources for instructions.

Modify the form

See Data Sources for help on the steps below.

1. Make a copy of the form from Example 4 above and open it for editing.
2. We can hide the Order Info section since it does not contain information entered by the user. Select the section by clicking on its header and uncheck the Visible checkbox in the properties pane. The entire section should gray out.
3. In the properties panel on the left, open the Data Sources pane by clicking on it.
4. Click 'New from XSD'.
5. You should see your schema in the resulting dialog box. Expand it and add the 'createOrderDetail' element to the form by clicking the + icon next to it.
6. Close the dialog box by clicking X in the top right corner.
7. In the Data Sources pane, expand the createOrderDetail data source by clicking on the + icon to the left.
8. Click the + icon next to the row element.  will generate controls in the form. Note that  automatically generates a repeating element since a single Order can contain any number of line items.
9. You can drag and drop to re-arrange as you wish.
10. Rename the Section named Row to Order Line.
11. Name the repeat control OrderLines. Take note of the "s" in the name.
12. Select the Product Code input control. In the properties panel:
    1. Set the name to pc. 
    2. Select Dropdown from the Display As property list.
13. Select the Onum control inside the Order Line section and in the properties pane set the name to olonum. Uncheck the Visible property.
14. Select the Order Line Number control inside the Order Line section and in the properties pane set the name to oln. Uncheck the Visible property.
15. Set default values in the Onum and Cnum controls inside the Order Info section and in the Onum and Order Line Number controls inside the Order Line section. You can set any values since we will be overriding them in a rule. We need to set a default value otherwise  will gray out the Submit button and the form cannot be submitted.

This step generates controls that are bound to the createOrderDetail data source by . You can render the controls as you wish but  will always generate an XML document conforming to the XML schema above. Here's what our form looks like:

Image Removed

Create/Update business rules

...

/*member customerName, customerNumber, productCode, productName, resultSet */
var x;
if (form.load) 
{
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/allCustomers'));
  var opts= [];
  for (var i=0; i < x.<statement>
                    INSERT INTO "OrderDetails"
                        ("orderNumber","productCode","quantityOrdered","priceEach","orderLineNumber")
                    VALUES 
                        ({onum},'{productCode}',{quantityOrdered},{priceEach},{orderLineNumber})
                </statement>
            </create>
</query>

 The steps are very similar to the earlier examples.

Generate XML schema

• In your browser go to the URL: http://localhost:8082/database/BIRT/createOrderDetail/schema Save the resulting XML schema file to disk.
• Upload the schema to . See Data Sources for instructions.

Modify the form

See Data Sources for help on the steps below.

1. Make a copy of the form from Example 4 above and open it for editing.
2. We can hide the Order Info section since it does not contain information entered by the user. Select the section by clicking on its header and uncheck the Visible checkbox in the properties pane. The entire section should gray out.
3. In the properties panel on the left, open the Data Sources pane by clicking on it.
4. Click 'New from XSD'.
5. You should see your schema in the resulting dialog box. Expand it and add the 'createOrderDetail' element to the form by clicking the + icon next to it.
6. Close the dialog box by clicking X in the top right corner.
7. In the Data Sources pane, expand the createOrderDetail data source by clicking on the + icon to the left.
8. Click the + icon next to the row element.  will generate controls in the form. Note that  automatically generates a repeating element since a single Order can contain any number of line items.
9. You can drag and drop to re-arrange as you wish.
10. Rename the Section named Row to Order Line.
11. Name the repeat control OrderLines. Take note of the "s" in the name.
12. Select the Product Code input control. In the properties panel:
    1. Set the name to pc. 
    2. Select Dropdown from the Display As property list.
13. Select the Onum control inside the Order Line section and in the properties pane set the name to olonum. Uncheck the Visible property.
14. Select the Order Line Number control inside the Order Line section and in the properties pane set the name to oln. Uncheck the Visible property.
15. Set default values in the Onum and Cnum controls inside the Order Info section and in the Onum and Order Line Number controls inside the Order Line section. You can set any values since we will be overriding them in a rule. We need to set a default value otherwise  will gray out the Submit button and the form cannot be submitted.

This step generates controls that are bound to the createOrderDetail data source by . You can render the controls as you wish but  will always generate an XML document conforming to the XML schema above. Here's what our form looks like:

Image Added

Create/Update business rules

• Click the Image Added icon in the toolbar at the top of the Form Designer.
• Click the Image Added Edit icon for the Show Customers rule we created in Example 4. Change the name to Show Customers/Products.
• In the Rule text area: copy and paste the following:
  
  

/*member customerName, customerNumber, productCode, productName, resultSet */
var x;
if (form.load) 
{
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/allCustomers'));
  var opts= [];
  for (var i=0; i < x.resultSet.length; i++) {
    if (x.resultSet[i]) {
      opts[i] = x.resultSet[i].customerNumber + '=' + x.resultSet[i].customerName;
    }
  }
  sc.options = opts;
  // Products.
  eval ('x=' + http.get('http://localhost:8082/database/BIRT/allProducts'));
  opts= [];
  for (var i=0; i < x.resultSet.length; i++) {
    if (x.resultSet[i]) {
      opts[i] = x.resultSet[i].productCode + '=' + x.resultSet[i].productName;
    }
  }
  pc[0].options = opts;
}

...

In this example, we will update the credit limit for an existing customer.

Define query

As always we have to first define the query. This query has an <update> operation in addition to the usual <retrieve> operation. We retrieve the customerName in the retrieve operation purely for display purposes.

...

The query defined in the built-in configuration file is shown below. This query has an <update> operation in addition to the usual <retrieve> operation. We retrieve the customerName in the retrieve operation purely for display purposes.

<query name="customerCreditLimit">
            <retrieve>
                <statement>
                    SELECT 
                        "customerNumber" as "cnum","customerName","creditLimit"
                    FROM 
                        "Customers"
                    WHERE 
                        "customerNumber"={cnum}
                </statement>
     <retrieve>       </retrieve>
         <statement>   <update>
                <statement>
SELECT customerNumber as cnum,customerName,creditLimit FROM Customers where customerNumber={cnum}             UPDATE "Customers" SET
 </statement>             </retrieve>          "creditLimit" = {creditLimit}
<update>                 <statement>   WHERE
                 UPDATE Customers SET creditLimit = {creditLimit} WHERE "customerNumber"={cnum}
                </statement>
            </update>
       
</query>

Generate XML schema

...

When the form is submitted,  will issue an HTTP PUT request to the above URL sending the customerCreditLimit XML document in the payload. The database connector will map this to the <update> operation of the customerCreditLimit query that we defined above, will use the data in the XML document to resolve the query (replace {creditLimit} and {cnum} in the query with the actual values from the XML document) and perform the UPDATE.

<query name="customerByNumber“></query>
        <query name="customerByNumber" autocreate="true">
   <retrieve>         <retrieve>
         <!-- maps to HTTP GET -->  <statement>
                <statement>       SELECT * from "Customers" where "customerNumber"={cnum}  
 
                </statement>
            </retrieve>
</query>

...