...

Generating the XML schema

You can use the database connector to generate the schemas you will need to use in the Live Forms the  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 Live Forms  to  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.

...

The first screen in the wizard is for the form's default document. This is indicated by the Document Name of "default" displayed in the wizard. The default document maps to all of the controls you added to this form by dragging a dropping and  dropping from the palette. This is typically not the document you want to configure.

• Click the Next button to advance from the default document to the next schema document

...

• . You should see the Document name change to your schema's document name

...

Image Removed

You also must specify which of the four SQL statements to run—you do this by choosing the appropriate values in the Document URI Read Method and Document URI Write Method dropdowns according to the table below. (You may notice in the Forms Designer that the Document URI Read Method drop down box includes a POST option, but it is not shown below because it is not used by the database connector.)

...

• . 

The next screen has two sections: Read URI and Write URI. Typically, when the form loads,  customers read form data from their database to initialize the form and send the updated XML document to the same database upon form submission. Enter the same URI in the Read URI and the Write URI fields, as shown in the image, with the appropriate SQL statement to run if you are reading from and writing to the same database using the database connector. It is possible to read the form initialization data from one database and then update a different database. Enter different URIs in the Read URI and the Write URI fields if this is your situation.

• Enter the database connector Url to your query in the wizard's URL input. If the database connector is running in the same host and port as the  form server, then you can omit http://<host>:<port>/ from the URL. See this example below.

Image Added

You also must specify which of the four SQL statements to run—you do this by choosing the appropriate values in the Document URI Read Method and Document URI Write Method dropdowns according to the table below. (You may notice in the Forms Designer that the Document URI Read Method drop down box includes a POST option, but it is not shown below because it is not used by the database connector.)

...

• Read Method - Choose GET if your form will be initialize initialized with data from your database; leave the dropdown blank if it won’t. (Don’t set the Read Method to POST; this is used for integrating forms with different back ends.) 
• Write Method - If your form is read-only, leave this blank. Otherwise, use the chart above chose either PUT or POST. PUT maps to the update method while POST maps to create.

...

http://localhost:8082/database/myStore/customers?customerId={customerId}

If you choose the Write method to be POST, the SQL statement that will be executed is:

<create>               
 <statement>
 INSERT into customers (customerId,firstname,lastname)      
 VALUES ({customerId},'{firstname}','{lastname}')
 </statement>          
</create>

...

Let's assume that you defined a Document URI the same Document URI in the Read and Write URI fields in the wizard for a schema in the form, a GET Read Method and a PUT Write Method.

When a user loads your form, a GET request for that URI will be sent to the database connector. That will cause the database connector to execute the retrieve operation for the query specified in the URI, take the result of the SQL execution and transform it to XML. The database connector will than then return the resulting XML back to the form which will be initialized with the contents of the XML file.

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 update operation identified by that URI.

...

If you have already uploaded the schema to Live Forms , you can still make the changes and update the schema. The process of updating the schema is described [[V4_Data_Sources_and_Schemas#Updating_a_Schema | here]]; here.

The common customizations are discussed below.

...

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 Display As property to Checkbox and changing the actual schema typeto type to xsd:list as shown below:

<xsd:element minOccurs="0" name="colorChoice">
      <xsd:simpleType>
           <xsd:list itemType="xsd:string"/>
      </xsd:simpleType>
</xsd:element>

Once the Color Choice control is formatted as a checkbox, a [[ V4_Designing_Forms#Labels |labels property]] will display in the properties settings tab for this control. Enter your checkbox options here.

Image Removed

Limitations

the Color Choice control is formatted as a checkbox, a labels property will display in the properties settings tab for this control. Enter your checkbox options here.

Image Added

Limitations

Make a control Required

If your database column allows NULL then the XSD schema generated by the  database connector will set minOccurs='0' for this control. When this control is used in your form the field will not be required. To make this field required you have to edit the XSD to set minOccurs='1' which makes the XSD schema more restrictive then your database schema.

See also Data Sources - Required Schema Controls. 

Changing text controls to date Controls

...

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns=http://www.frevvo.com/database/Customer 
targetNamespace="http://www.frevvo.com/database/Customer"> 
   <xsd:element name="Customers"> 
      <xsd:complexType> 
         <xsd:sequence>sequence> 
            <xsd:element maxOccurs="unbounded" name="row"> 
               <xsd:complexType> 
                  <xsd:sequence

Since the schema says the element is unbounded, the control you generate initially will be a repeat control.  You’ll see the Repeat Row heading in the Forms Designer and if you don’t make any changes, users will see the + sign that comes with repeat controls.

...

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns=http://www.frevvo.com/database/Customer 
targetNamespace="http://www.frevvo.com/database/Customer"> 
   <xsd:element name="Customer"> <xsd:complexType> 
      <xsd:complexType> 
         <xsd:sequence> 
            <xsd:element maxOccurs="1" name="row"> 
               <xsd:complexType> 
                  <xsd:sequence>

Note that now maxOccurs="1" and that will force the + sign to disappear.

...

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

...

<xsd:element minOccurs="0" name="Home Phone" type="phoneType"/>

Now the Live Forms the  form will only accept valid phone numbers. If you enter an invalid phone the form will flag the field as invalid.

...

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

...

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 .

See the documentation for Data Sources and Schemas a solution for implementing show/hide with [[V4_Data_Sources_and_Schemas#Required_Controls | required controls]].