frevvo + SQL: Part 5. Create/Update records

DBConnectorPrevious articles in this series (Create dynamic pick lists initialized from SQLMaster-detail views from SQL and Handling multiple results from a Query) have focused on reading information from the database and dynamically initializing fields on your form.

But, many business problems require creating new records or updating existing records in the database. Today, we’ll take a look at these scenarios and see how you can create complex forms without programming using frevvo’s Database (DB) Connector. Fair Warning: this is a fairly technical article so we’ve only touched upon the highlights here. You can explore in detail by reading the DB Connector Tutorial in the documentation.

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 very similar to the previous articles:

1. Define the SQL Queries

This time, there are multiple SQL queries.

<queryset name=”BIRT” …>
  <query name=”createOrder”>
    <retrieve>
      <statement>SELECT orderNumber as onum, customerNumber as cnum from Orders WHERE orderNumber=10100
      …
    </retrieve>

<create>

      <statement>INSERT into Orders (orderNumber,orderDate, requiredDate, status,customerNumber) VALUES ({onum}, Now(), Now(), ‘In Process’, {cnum})

      …

2. Generate an XML Schema

The first query is used to generate an XML schema and form controls bound to that schema. There will be two (onum and cnum). In this case, you’ll have to modify the XML a bit due to a current limitation in the DB Connector. See the documentation for detailed instructions.

3. Create the form

Generate the form using the above XML schema as a data source.

4. Link to the database

Connect it to the DB query defined above. We use Doc URI wizards with the only change being that we configure a Write URL using HTTP POST (create) instead of a Read URL.

That’s it. When the form is submitted, frevvo will generate an XML document with values for onum and cnum and send the XML to the DB Connector. The Connector will extract onum and cnum from the XML and execute the INSERT operation.

Insert Multiple Records

It’s easy to build upon this to satisfy far more complex business requirements. For example, in this case, you want to insert an Order but there will also be multiple Order Line Items. This means running an INSERT for the Order itself + multiple INSERTs for the line items with different data. Fortunately, with frevvo you can create forms with multiple Data Sources. Each one generates its own XML document and each one can have its own Doc URI.

dbconnector-post
A single frevvo form can process multiple documents and execute multiple SQL queries when the form is submitted.

Update Existing Records

You can equally easily update an existing record with the DB Connector. The process is almost identical. The only difference is that you will use an <update> query instead of a <create> query and the document is sent using HTTP PUT instead of POST.

<queryset name=”BIRT” …>
  <query name=”customerCreditLimit”>
  <update>

      <statement>UPDATE Customers SET creditLimit = {creditLimit} WHERE customerNumber={cnum}

      …

The rest is identical. Generate the form using an XML schema, connect the resulting document to this query and frevvo + the DB Connector take care of the rest.

Auto Create Rows

You can also setup the queries so that they will first try an update and if it fails, create a new record.

<query name=”customers” autocreate=”true”>

The autocreate feature is particularly useful when working with Repeat Controls or Tables. With these controls, your forms can work with dynamic collections, for instance: customers, cars, addresses, dependents and others. When the user loads the form, the form may be initialized with some items. If the user adds new items to the collection, updates a couple and submits the form, the items will be automatically added to the database and the existing ones will be updated if autocreate=true.

Auto Delete Rows

Auto-creation raises the obvious question. What if the user deletes a row from the repeating collection described above. If you want the DB Connector to delete the corresponding records from the database, you can simply set the autodelete property and provide a key.

<query name=”customers” autocreate=”true” autodelete=”true” deleteKey=”customerId”>

Behind the scenes, the connector compares the items in the database with what is submitted in the form. That comparison is based on a key that you define with the attribute deleteKey (required). The deleteKey value is normally the name of the primary key in the table that contains the repeat items.

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.

One thought on “frevvo + SQL: Part 5. Create/Update records

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