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.
This Doc URI approach with a Repeat or Table control is generally much easier and more efficient than using a business rule to connect your form to your database. This chart explains why you might choose this approach.
Doc URI | Business Rules |
---|---|
No code needed | Javascript code required |
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.
In this example, we used the sample database 'classicmodels' which contains a table named 'products.' We plan to let our users select a Product Line, then show a table that contains all the products in that Product Line. The table must include the products table's unique key, 'productCode'. We've chosen to include just a few columns for simplicity.
We will allow the user to
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:
SELECT productCode, productLine, productName, MSRP from products WHERE productLine={pline} |
This statement shows the Product Code, Product Line, Product Name and MSRP for any product in the specified product line. Note the variable /{pline}/. This will be defined in our form. When testing this statement on your database, please replace the variable with an actual productLine value, such as "Ships".
INSERT IGNORE INTO products (productCode, productName, productLine, MSRP) VALUES ('{productCode}', '{productName}', '{productLine}', {MSRP}) |
This statement will create a new product with the columns productCode, productName, productLine, and MSRP filled. The "IGNORE" statement tells the statement to ignore any rows with an existing product code, and to ignore the fact that other columns in this table are left null. Note again the variables inside curly braces, which will pass values from the form to the database connector.
UPDATE products SET productName='{productName}', productLine='{productLine}', MSRP={MSRP} WHERE productCode={productCode} |
This statement will update each product with the any changes made in your form table, using the unique key productCode to identify the row. Note again the variables inside curly braces, which will pass values from the form to the database connector.
DELETE from products WHERE productCode={productCode} |
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.
Create a queryset and query in your configuration.xml like this (if you are using your own database instead of the sample database, replace the SQL statements with your own). The first query, 'allProducts", contains our four SQL statements as described above. We will use the second query, 'productlines', to populate a dropdown control with the available product lines so we can filter our table right in our form. The statement in that query simply selects all the productLine rows in the productlines table.
<dbconnector> <queryset name="products"> <query name="allProducts" autocreate="true" autodelete="true" deleteKey="productCode"> <retrieve> <statement> SELECT productCode, productLine, productName, MSRP from products WHERE productLine={pline} </statement> </retrieve> <create> <statement> INSERT IGNORE INTO products (productCode, productLine, productName, MSRP) VALUES ('{productCode}','{productLine}','{productName}',{MSRP}) </statement> </create> <update> <statement> UPDATE products SET productName='{productName}', productLine='{productLine}', MSRP={MSRP} WHERE productCode={productCode} </statement> </update> <delete> <statement> DELETE from products WHERE productCode={productCode} </statement> </delete> </query> <query name="productlines"> <retrieve> <statement> SELECT productLine from productlines </statement> </retrieve> </query> </queryset> </dbconnector> |
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.
It's easy to generate the schema. Go to the browser tab where you just tested your query URL http://localhost:8082/database/products/allProducts?pline=Ships
. Now edit the URL and add /schema after allProducts but before the ?pline= parameter. Your URL will look like this: http://localhost:8082/database/products/allProducts/schema?pline=Ships
. The results should now show in xsd format, which frevvo can translate into form controls.
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.)
http://localhost:8082/database/products/productlines?_mediaType=json
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.
http://localhost:8082/database/products/allProducts?pline={pl!encode=true}
Write URL: http://localhost:8082/database/products/allProducts?pline={pl!encode=true}
If your SELECT statement does not include a WHERE condition, and/or if you are only performing CREATE/UPDATE operations, you do not need the parameter ?pline= on this URL. However, when the SELECT statement is in the same query and uses a WHERE condition, and you need to perform DELETE operations, you must include the parameter on the Write URL as well. |
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.
Select a Product Line, and notice that the form table will automatically expand and display all the products retrieved by the SELECT statement.
Submit the form. The database connector will
This .gif show product S99_9999 being updated, S99_7777 being added, and S99_8888 being deleted. To check if this worked as expected, just test the form again, and notice that the updated row appears with its new data, the newly added row appears, and the deleted row does not appear.
<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.