Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Section
Column
Info

The database connector maps between HTTP requests issued from

Frevvoproduct
 forms to SQL queries executed against your database. The connector enables you to use the database of your choice to save and load your form data. You can create a new database or you can automatically generate forms that connect to your existing database tables.

To successfully complete this tutorial,

  • Install your database,
    Frevvoproduct
    and the database connector on the same server, if possible.
  • Configure your database to work with
    Frevvoproduct
  • Install

    you will need:

    You can download the completed application and then upload it to your account. This will provide a reference for the example forms described below.

    Info

    There are additional resources designed to help you learn how to use the power of

    Frevvoproduct
    forms with your database. Refer to Database Connector Examples for more information.

    Column
    width225px

    On this page:

    Table of Contents
    maxLevel1

    ...

    Code Block
        <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

    Frevvoproduct
     server's tomcat/webapps directory and
    Frevvoproduct
     is running port 8082, then you must change the <url> from localhost to localhost:8082.

    ...

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

    ...

    Since order details is a complex structure (product name, quantity, price etc.) we will use

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

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

    Code Block
    <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:

    ...

    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. Set the URL to: http://localhost:8082/database/BIRT/createOrder.
    6. 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.

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

    Code Block
     <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>

    ...

    ...

    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.

    Code Block
    <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>

    ...

    ...