frevvo + SQL: Part 4. Multiple Results

db-multiple-results

Multiple Rows from SQL

Everyday business activities (Customer Service, HR etc.) are simply more useful if they can access information from internal SQL databases when it’s needed. In Part 2 of this series we saw how to create dynamic pick lists initialized from SQL and Part 3 explored how to create master-detail views from SQL.

Today, we’ll take a look at queries that return multiple results. It’s another very common scenario in real applications e.g. look up order line items for a particular order. With frevvo’s Database Connector, complex forms like this can be created without programming.

As before, you’ll need to install and configure the DB Connector one-time so it can connect to your SQL database. After that, the steps are as follows:

1. Define the SQL Query

<queryset name=”BIRT” …>
  <query name=”orderDetailsByOrder”>
    <retrieve>
      <statement>SELECT p.productName as product, o.quantityOrdered as quantity, o.priceEach as price, p.productDescription as description, p.MSRP FROM OrderDetails o, Products p WHEREo.productCode=p.productCode and o.orderNumber={onum} ORDER by o.orderLineNumber
      …

 

This is a straightforward query that simply retrieves order details for a particular order number. It can be accessed via a URL: http://<server>/database/BIRT/orderDetailsByOrder?onum=10100. As before, the {onum} in curly braces is a variable whose value should be passed in the URL. Try it now in your browser. You’ll get back an XML document with order details.

Now, we need to connect this to the form. As before, we will use Live Forms’s built-in XML features to generate controls and populate them from the database.

2. Generate an XML Schema

The DB connector can generate an XML schema from any query’s <retrieve> operation. This schema can then be used to generate a Live Forms form. Try it now in your browser, save the resulting XML schema file to disk and upload it to Live Forms. See the documentation for detailed instructions. Note that we need to pass in a known order number so that the query can successfully execute.

dbconnector-multiple-items

Automatically populate multiple rows in a form Table from a SQL query

3. Create the form

We’ll use a copy of the form from the previous article. It already has a dynamic pick list which lists the customers in the format value=label (e.g. 242=Alpha Cognac). Edit the form and:

  1. Create a new data source from the Schema uploaded above in Step 2.
  2. Add controls to the form and rearrange them as desired.

4. Link to the database

Now, we connect to the DB query defined above. We use Doc URI wizards to do this. In a nutshell, this means configuring the form so that the data source is bound to the URL for the query that we saw in Step 1.

Try it now using our example form.

Interested in learning more? The Database Connector Tutorial explains this in great detail. Register for an upcoming webinar and visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

2 Responses to frevvo + SQL: Part 4. Multiple Results

  1. Pingback: frevvo + SQL: Part 5. Create/Update records | frevvoblog

  2. Pingback: frevvo + SQL: Part 1. Dynamic database forms | frevvoblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: