...

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. 

...

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.

...

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:

...

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.

...

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.

...

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.

Image RemovedImage Added

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

Image RemovedImage Added

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.

...

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.

...

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