The No-Code Doc URI Approach

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.

CRUD Operations on a Table

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

• View the products for any product line.
• Add a product to the product Line by adding a table row.
• Update any product by editing the data in the table.
• Delete any product by deleting a table row.

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.

Write the SQL Statements

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.

Generate a DBC Query

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.

Test the Query URL

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.

Generate a Schema

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.

Create a URL to Fetch Dropdown Options

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.

Upload the Schema

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.

1. Log into frevvo as the designer user.
2. Navigate to the Project where you will create your form (or create a new Project.)
3. Click the Schemas tab.
4. Click the blue plus icon to add a new schema.
5. Provide a name and description and upload the allproducts.xsd file you saved earlier.
6. Click Upload

Create the Form

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.)

Dynamic Options

1. Drag and drop a Dropdown control to you form. 
   1. Label "Select Product Line"
   2. Name "pl"
2. Change the Options Src property to Web Service.
3. Set the Options Url property to the URL you generated earlier. In our example this URL is http://localhost:8082/database/products/productlines?_mediaType=json
4. The Value Binding and Label Binding properties set the options syntax value=label. In this case they are both the same, the column name productLine. When using json data, we'll prepend that column name with /resultSet/.
   1. Value Binding: /resultSet/productLine
   2. Label Binding: /resultSet/productLine

You could test your form now, and see that the dropdown contains all the product lines from your database table productlines.

Table from Schema

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!)

1. On the Data Sources tab, click New from XSD.
   
2. Expand the allProducts line, which represents your schema, and then click the green plus icon to add the allProducts query elements to your Data Sources tab.
   
3. In the Data Sources tab, click the green plus beside the newly-added allProducts element. Notice that a Section control named All Products, with a Repeat control inside it, gets automatically added to the top of your form. 
   
4. We'll customize these controls so they look nice for our users.
   1. Drag and drop the All Products section below your dropdown control.
   2. Change the label of the All Products section to give the user some context about which product line is being queried. We'll use the control template {pl}, which at run-time will display the value selected from the dropdown. The new label is 'All Products in {pl=Product Line}'.
      
   3. Click the Repeat control (the carbon copy icon ). Change the Control Type from Repeat to Table. If you plan to at some point use business rules to customize the behavior of this table, you can also give it a more meaningful name, however that is not required for this example.
      
   4. Notice that your table has columns corresponding to your database columns. If desired, you can make further customizations to the table, such as modifying the column widths, labels, etc. See this documentation for changes you can make to controls from schema.
      

Manually Set Doc URIs

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.

1. Go to Settings editing mode. 
2. Select the Document Actions tab, and then the Send Data Sub-tab.
   
3. Select the option Manually Set Doc URls.
4. On the Document: allProducts (the one you pulled in as a Data Source) we will set a Read URL with the method GET, which will map to the SELECT statement in our query, and a Write URL with the method PUT, which will map to the UPDATE, CREATE, and DELETE statements. These URLs are constructed similarly to the browser test URL.
   1. Read URL: http://localhost:8082/database/products/allProducts?pline={pl!encode=true}
      1. This URL point to the location of the database connector, the queryset name, and the query name with the parameter ?pline= that meets the WHERE condition of the SELECT statement.
      2. The template {pl} will resolve to the value of the dropdown in our form, which will be a valid productLine result since the options are populated dynamically from our database.
      3. Notice that we've added the syntax !encode=true inside the template. Since some of the productLines contain spaces, and a valid URL cannot contain a space, this will ensure that the spaces are encoded correctly. It's a good idea to encode templates used in URLs when the value might contain spaces or special characters.
   2. Read URL Method: GET.

   3. 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.

   4. Write URL Method: PUT. The PUT method, which typically maps to UPDATE statements, will also perform CREATE and DELETE statements as long as the query contains the attributes autocreate="true", autodelete="true" and deleteKey="productCode" (replace with your table's unique key).

That's it! You're ready to test the form.

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.

1. Add a row and enter some data.
2. Edit another row.
3. Delete a different row.

Submit the form. The database connector will

1. Execute the INSERT statement for any form table row that was added.
2. Execute the UPDATE statement for any form table row that was changed.
3. Execute the DELETE statement for any form table row that was deleted.

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>

Troubleshooting

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.

Issue

• The form does not read from or update the database as expected.
• I received a Doc Post Failure notification and/or the Submission has errors.

Resolution

Take the following troubleshooting steps to find and resolve the issue.

• Go to the Database Connector status page (http://localhost:8082/database/status). This page will confirm if you database connector is running. See this documentation on testing your Database Connector.
• Check your database connector logs. If an error occurred, the description will display here. You can also enable the DEBUG logging level to get even more information if needed. The error description should help point you to an issue with your configuration.xml or your database's response.
• Run the query URL in a browser tab, as described above. This will rule out an issue in configuration.xml. If it works, then the problem may be in your form properties. If it fails, check your configuration.xml for typos or other syntax errors. 
  • Some values must be passed inside quotes, such as '{productCode}'. Test the SQL Statement in your database to see which values require this syntax.
  • Variables that reference controls in the form are case sensitive.
• Check your Form Setting: Document Actions: Send Data settings and ensure the correct Read/Write URLs and methods are set, including any necessary parameters. It's often helpful to copy/paste the URL from a successful test in the browser.
  • Remember that SELECT statements with a WHERE condition must use a URL parameter to input the WHERE condition's value. Similarly, if your SELECT (with WHERE) and DELETE statements are in the same query, the Write URL must also include that parameter.

Issue

I see "premature end of file" in the debug console.

Resolution

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.