...
When working with the database connector, you will always used the latter and create the controls from the schema. In the future we intend to relax this requirement.
Generating the XML schema
You can use the database connector to generate the schemas you will need to use in the frevvo form.
To fetch the schema, type a URI in the browser that looks like the example below. This is almost identical to the query we used to test the connector, but it tells the connector to GET the schema instead.
...
Save the schema as an .xsd file in your file system. You can then [[V4_Data_Sources_and_Schemas#Uploading_a_Schema | upload the schema]] to frevvo and generate controls from the schema elements that map to your database tables. If you have multiple queries in your configuration file, you’ll need to generate a schema for each query.
Document URI Properties
After you’ve generated controls from the elements in your database schema, set your form’s Document URI to the database connector query Url. To do:
...
At run time, the values of {customerId}, {firstname} and {lastname} will be replaced by the values of the respective form controls.
What happens at run Time ?
Let's assume that you defined a Document URI for a schema in the form, a GET Read Method and a PUT Write Method.
...
When the user submits the form, the same URI will be executed but now with the PUT (or POST) method. The database connector will than execute the '''updade''' operation identified by that URI.
Customizing the XML Schema
The schema you retrieve from your database will be relatively generic. As a result the controls you generate from this database schema might need some tweaking to suit the specific needs of your form. You can modify a control’s behavior by making changes to the schema after you’ve retrieved it.
...
The common customizations are discussed below.
Adding Dropdown/Radio Options
Suppose you want users to select a specific manager’s name from a dropdown list. Your database has a 50-character limit but naturally does not include specific manager name restrictions, so the schema you retrieve initially might look something like this:
...
You also can populate dropdown boxes dynamically from your database by writing a [[V4_Designing_Forms#Triggers_.26_Dynamic_Options | rule.]]
Adding Checkbox Options
You may also need a control to allow multi-select options. Like dropdowns and radios controls described above, the control you generate from the initial schema will be formatted as a text control. You can make the control a checkbox by changing the control’s Format As property to Checkbox and changing the actual schema typeto xsd:list as shown below:
...
[[Image:text-toCheckbox-Example-2.png]]
Limitations
[[Image:18px-Symbol_OK.svg.png]] Currently it is not possible to list the options directly in the schema.
[[Image:18px-Symbol_OK.svg.png]] Currently it is not possible to have a checkbox option containing a space. So a label of "dark blue" will appear in the database as "dark_blue".
Changing text controls to date Controls
Controls you generate based on SQL Server date columns initially will be text controls, because all SQL Server dates are in datetime format and therefore have a type of xsd:datetime in the schema you retrieve from the database.
In the schema, change the XSD type from xsd:string to xsd:date If in your you want a date control instead of a text control.
Limiting potentially repeating Data
Often the schema from your database will allow any number of elements even though you know your form will never use more than one at a time. Every schema includes the “rows” element as shown in the partial schema below—note that based on the maxOccurs value of “unbounded” in the schema, there can be an unlimited number of customer elements.
...
Note that now maxOccurs="1" and that will force the + sign to disappear.
Type Restrictions
Sometimes the database field will let you enter any string such as a VarChar field. However if you field is a phone number you may which to have the form restrict the input to only valid phone numbers. See the section on [[V4_Using_Forms#Patterns | patterns]] for more common type restrictions. In this example you still want the form input to be a text box. So we do not change the Format As.
...
Now the frevvo form will only accept valid phone numbers. If you enter an invalid phone the form will flag the field as invalid.
Dynamic Dropdown Options
Individual controls also may trigger SQL statements, if you’ve customized your form with rules. See [[Rules_Examples#Database_Connector_REST_Services | Database Connector Rest Service]] for an example of using the ResultSet returned from the Database Connector to dynamically populate the options in a dropdown control.
Show/Hide Required Fields
There are times when you have several fields that you want hidden and then only want to make visible depending on the value entered into another field. Thus only when the fields are visible do you want them to be required. Currently hidden required fields are still required and will causes the submit button to remain disabled. Automatically making hidden fields not-required may be added in a future release of Live Forms.
...