This page is currently under construction. Please visit the Database Connector Tutorial for details on using both Doc URI's and Business Rules to integrate with your database.
The Doc URI or "data first" approach is an easy method to perform CRUD (CREATE, RETRIEVE, UPDATE, DELETE) operations on your database from a table or repeat control in your form. The best part is – using our powerful database connector, you don't need programming.
|Automatically pass all controls from schema||Pass each individual control|
|Call the DB Connector once for all operations (using the autocreate feature)||Call the DB Connector query for every add/update operation|
You can use a form created from schema to perform all of the CRUD operations on your database table as long as all your SQL statements (SELECT, CREATE, UPDATE, and DELETE) are declared in the same DB Connector query. Please review the documentation on Defining SQL Queries before starting this tutorial.
This tutorial will step you through how to add your SQL statements to a file so that the Database Connector can access and execute them, quickly create a form based on this schema, set up dynamic dropdown options and link your form to the database without writing any business rules or code. You can also watch this video which walks through the same steps.
Your first step is to write the SQL statements that will perform the select, insert, update and delete operations on your database. Your SQL expert will be able to write appropriate statements for your database table(s). For this example, we used the following statements:
This statement will ensure that any row deleted from the form table will be deleted from your database, using the unique key productCode to identify the row to be deleted.
As you know by now, the frevvo Database Connector requires SQL statements to be placed in the configuration.xml in the form of a queryset and query/queries. The configuration.xml file will allow the database connector to get values from your form and execute the statements on your database with those values. This query must also contain the attributes autocreate="true", autodelete="true" and deleteKey="productCode" (replace with your table's unique key). These attributes are discussed in this documentation, and basically allow you to use the Doc URI write method "PUT" to insert and delete rows in addition to updating them. We'll discuss this further in the Manually Set Doc URI's step.
Save the configuration.xml.
You can now test the queries by opening a new browser tab and using the Database URL http://localhost:8082/database/products/allProducts?pline=Ships. Note the URL parameter ?pline=, which is required to provide a value in the WHERE condition of your SQL statement. You should see the xml results of this query in your browser. If you do not, please check out the Troubleshooting section for possible reasons.
Next we will generate a schema from this query, which will allow our frevvo form to automatically create controls for us, and recognize those controls as connected to our database. Of course, you can create controls from the palette, but with large amounts of data/columns/tables, most customers find it much faster to create the form from schema.
Right click anywhere in the window and save the page as an xsd file. Name it anything you want, i.e. allProduct.xsd. We'll use this file in the Upload the Schema step below.
While you're in the browser testing the Database URL, go ahead and test http://localhost:8082/database/products/productlines, the query that fetches the Product Lines we will use as dropdown options. Append the URL parameter ?_mediaType=json to the end of this URL to show the results in json format. The frevvo dynamic options feature can use data in json or xml, but for this example json is a bit easier. Copy that entire URL and paste it to a text pad - we will use in the Create the Form step below.
Now we'll upload the schema you generated to frevvo, which will allow you to automatically create fields in your form that match the database columns.
Now for the fun part! We will create a form "from schema" with just a few clicks. Go to the Forms & Workflows tab, and click the blue plus icon and select Create a Form. (If prompted, add the form to the same Project where you uploaded the schema.)
You could test your form now, and see that the dropdown contains all the product lines from your database table productlines.
Next we'll add the table that will show you all the products in a given product line, just like the query we tested earlier (but in a much friendlier layout for your users!)
You've made it to the last step of editing! We'll set the Doc URIs which tell our form where to find the database connector and which query to use when "talking" to our database.
That's it! You're ready to test the form.
Simply click the Save and Test icon to run your form and see the database connector at work.
<video or gif... gif too big>
Setting up a form to connect to your database using the Doc URI method should be straightforward. However, if your form is not behaving as expected, here are a few common issues and their resolutions.
Take the following troubleshooting steps to find and resolve the issue.
I see "premature end of file" in the debug console.
You will see this if the Doc URI used to initialize the form is not valid, which happens in this test form when you run it and there is not yet a value selected in the Select a Product Line dropdown. Once you select a value, the Doc URI GET will work as expected.