Page Comparison

...

    <queryset name="BIRT" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd" xmlTimeStampFormat="MM/dd/yyyy">
        <resource-def>
            <url>jdbc:odbc:ClassicModels</url>
            <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>
            <user/>
            <password/>
        </resource-def>
        ...
    </queryset>

Change the port number to the port where your database connector is running. For example if you installed database.war into the  server's tomcat/webapps directory and  is running port 8082, then you must change the <url> from localhost to localhost:8082.

...

We will use a business rule to retrieve the list of customers from the database as JSON and dynamically populate this drop down control.

1. Click the Image Modified icon in the toolbar at the top of the Form Designer.
2. Click  to create a new rule and the Image Modified Edit icon to open the rule.
3. Set the Name to 'Show customers'.
4. In the Rule text area: copy and paste the following:

...

Next, we will add a drop down that displays the list of orders for a particular customer. When the user selects a customer name from the first drop down, we will display the list of orders for that customer from the database. This is essentially repeating the steps above except that the database query is triggered by a user action (selecting a customer from the drop down) and depends on the selected customer.

Define a query

Add Check the configuration file for the following query to the configuration file. If it is not there, addit. Note that,this query requires a parameter indicated by the {cnum}.

...

Create a business rule

1. Click the Image Modified icon in the toolbar at the top of the Form Designer.
2. Click  to create a new rule and the Image Modified Edit icon to open the rule.
3. Set the Name to 'Show orders'.
4. In the Rule text area: copy and paste the following:
   
   
   

...

Since order details is a complex structure (product name, quantity, price etc.) we will use 's built-in XML features to generate controls and populate them from the database. When an order is selected, the order line items will be displayed as shown below:

Define a query

As always, we must first define a query in Check the configuration file for the following query. If it is not there, add it. The query requires a parameter indicated by {onum}.

...

1. In a browser go to the URL: http://localhost:8082/database/BIRT/orderDetailsByOrder/schema?onum=10100. Save the resulting XML schema file to disk. Note that we need to pass in a known order number so that the query can successfully execute.
2. Upload the schema to . See Data Sources for instructions.

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. A new dialog box will appear. Click the Next button.
4. You should see the Document name change to 'orderDetailsByOrder', which is the data source we are interested in.
5. Set the URL to: http://localhost:8082/database/BIRT/orderDetailsByOrder?onum={so}.
6. Set the Read method to GET.
7. Click the Finish button.

...

We will define two queries. The first one creates an order. In addition to the usual <retrieve> operation, this query also has a <create> operation. Check the configuration file for this query. If it is not there, add it.

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

...

The second query is simply used to generate an order number. Check the configuration file for this query. If it is not there, add it.

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

...

• In your browser go to the URL: http://localhost:8082/database/BIRT/createOrder/schema. Save the resulting XML schema file to disk.
• Edit the file. Due to a current limitation in the database connector, the generated schema needs to be edited. Remove the maxOccurs="unbounded" attribute from the row element declaration. Replace:

...

Create a business rule

1. Click the the Image Modified icon in the toolbar at the top of the Form Designer.
2. Click  to create a new rule and the Image Modified Edit icon to open the rule.
3. Set the Name to 'Copy customer number and generate an order number'.
4. In the Rule text area: copy and paste the following:
   
   

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. A new dialog box will appear. Click the Next button.
4. You should see the Document name change to 'createOrder', which is the data source we are interested in.
5. Leave the Read method empty.
6. Set the Write URL to:http://localhost:8082/database/BIRT/createOrder.Leave the Read method empty.
7. Set the Write method to POST.
8. Click the Finish button.

...

Once again, we will require two queries. The first one fetches product codes and descriptions from the database so that the user can select a product for purchase. Check the configuration file for this query. If it is not there, add it.

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

The second query creates the order detail line item records in the database. Check the configuration file for this query. If it is not there, add it.

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

...

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

...

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 outThis section will be grayed out when you click on another control on the canvas.
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 Change the label and the name 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 and in . In the properties pane set 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.

...

Create/Update business rules

• Click the Image Modified icon in the toolbar at the top of the Form Designer.
• Edit 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:
  
  

...

1. Click on the Doc Action button in the toolbar at the top of the form.
2. In the dialog box that appears click on 'Manually set document URIs'.
3. A new dialog box will appear. Navigate to the desired document (createOrderDetail) by clicking the Next button twice.
4. Set the URL to: http://localhost:8082/database/BIRT/createOrderDetail.
5. Leave the Read method empty.
6. Set the Write method to POST.
7. Click the Finish button.

...

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. Check the configuration file for this query. If it is not there, add it.

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

...

• In your browser go to the URL: http://localhost:8082/database/BIRT/customerCreditLimit/schema?cnum=242. Save the resulting XML schema file to disk.
• Edit the file. Due to a current limitation in the database connector, the generated schema needs to be edited. Remove the maxOccurs="unbounded" attribute from the row element declaration. Replace:

...