...

Since we are using the in-memory databases, no further configuration is required.

In-house Customers should verify the following before beginning the tutorial

The latest tomcat bundle and the Database Connector are installed and are up an running on your in-house server.

...

...

Start the Insight server. and then start Live Forms.

...

...

After performing these steps you will have:

• Successfully installed the  database connector and retrieved data from the built-in test database

Cloud Customers should verify the following before beginning the tutorial

1. Install and start the Database Connector on an in-house server in Standalone mode.

   Expand
   title Click here for the details
   Download the Standalone connector zipfile here. Unzip the database.zip file to a location of your choice. We will refer to this directory as <db-home> Install the JDBC driver, if required. You can start the connector using plain java command or execute one of the files in this directory to install the DB Connector as a Windows service or *nix console instance: Choose one of these methods to start the connector: Method 1: Using java in a command window Navigate to the <directory where you unzipped the database.zipfile> Type java -jar database.war Method 2: for Windows OS Install the DB Connector as a Windows service by double clicking the Install-Service.bat file. Click the Start DBConnector Service.bat to start the service. Method 2 for *nix OS Execute the  DB Connector.sh shell script for *nix operating systems. Test the connector installation by copying this URL directly into your browser: http://localhost:8081/database/status Note the standalone connector runs on port 8081 by default. The port number can be changed when you configure the DB Connector in the next step. Use port 8081 to verify that the Db Connector is up and running for now.  Verify that query validation page is loaded with status Passed for the built-in database BIRT and myStore querysets

2. Verify that the Database Connector is accessible from the frevvo servers.
3. Login as a designer user. Change the <localhost:8082> in all Rules and Doc URi's in the Database Connector Tutorial application that you downloaded earlier to point to the <server:port> where you installed the DB Connector in Standalone mode. Save the changes.

Define a Queryset

The first step is to define a queryset and give it a name - BIRT in the configuration.xml file. Since we are using the built-in configuration.xml file, you do not have to do this. Here is an example of the BIRT queryset and the "allCustomers query that is described below.

...

This tutorial assumes that frevvo-tomcat bundle or the Database Connector Standalone bundle is installed and that the Database Connector is up and running.

Cloud customers should verify that the Database Connector is accessible from the frevvo servers.

Define a Queryset

The first step is to define a queryset and give it a name - BIRT in the configuration.xml file. Since we are using the built-in configuration.xml file, you do not have to do this. Here is an example of the BIRT queryset and the "allCustomers query that is described below.

 <dbconnector>
    <queryset name="BIRT">
         <query name="allCustomers" autocreate="true">
            <retrieve>
                <statement>
                    SELECT "customerNumber","customerName" from "Customers" order by "customerName"
                </statement>
            </retrieve>
        </query>
    </queryset>
</query>dbconnector>

The database connector can simultaneously connect to multiple databases; each must be defined in its own queryset. The queryset consists of a resource definition and any number of named queries. The resource definition is shown above for MySQL It is configured in the dbconnector-custom.properties file. You will need to modify it as required: change localhost to the name of the machine running MySQL, specify the MySQL driver, change the database user name and password.

...

• The 'localhost:8082' is obvious and points to the database connector inside a servlet container (e.g. Tomcat) on localhost and listening on port 8082.
• 'database' refers to the context path for the database connector web application running inside Tomcat.
• 'BIRT' refers to the name of the queryset defined above.
• 'allCustomers' is the name of the query.
• Since we are issuing an HTTP GET from the browser, this maps to the <retrieve> SQL.
•  _mediaType=xml tells the connector to return an XML document. This is the default; if you leave out a _mediaType, the connector will return XML.
  When the HTTP GET is issued, the connector executes the <retrieve> SQL for the 'allCustomers' query in the 'BIRT' queryset using the resource definition to connect to the appropriate database. The data in the resultset is converted to XML and returned.

...

Generating xml schema

If you prefer, you can generate the schema for the entire BIRT queryset before you begin. This eliminates the need to create a schema one by one for each query as specified in the instructions. The queryset schema can be uploaded to your application one time and then you can add the relevant schema elements to your forms/flows as you need them. Browse this URL to get the schema, save the schema as an .xsd file in your file system then upload the schema to Live Forms.

http://<server>:<port>/database/BIRT/schema

Example 1: View customer list

Our first example is to create a form with a dropdown control that is dynamically populated with the above list of customers from the database. When the user selects a customer by name, the value of the dropdown control should be set to the customer number.

Image Removed

The steps are as follows:

Define a query

...

Generating xml schema

If you prefer, you can generate the schema for the entire BIRT queryset before you begin. This eliminates the need to create a schema one by one for each query as specified in the instructions. The queryset schema can be uploaded to your application one time and then you can add the relevant schema elements to your forms/flows as you need them. Browse this URL to get the schema, save the schema as an .xsd file in your file system then upload the schema to Live Forms.

http://<server>:<port>/database/BIRT/schema

Example 1: View customer list

Our first example is to create a form with a dropdown control that is dynamically populated with the above list of customers from the database. When the user selects a customer by name, the value of the dropdown control should be set to the customer number.

Image Added

The steps are as follows:

Define a query

We will use the query 'allCustomers' in the configuration.xml file.

 <query name="allCustomers" autocreate="true">
            <retrieve>
                <statement>
                    SELECT "customerNumber","customerName" from "Customers" order by "customerName"
                </statement>
            </retrieve>
        </query>

Create a form

1. Create a new form. Accept the defaults by clicking the Finish button. The Form Designer will appear.
2. Drag a Dropdown control from the Palette and drop it into the form.
3. In the properties pane on the left, change the Label as desired.
4. In the properties pane, change the Name to sc.

...