...

You define the scripts that will work with your database in the configuration file. For example, the sample configuration file defines a '''query''' called ''customers'' query called customers. That query assumes that a table such as the one below exists in your Data Source.

...

One query cannot have two SQL statements of the same type. If you need two different <retrieve> statements (for example, Select * from customers and Select * from Product), you’ll need two different <query> elements.  A query may have fewer than four SQL statements—if users can’t delete data from your database via your forms, your query does not need a <delete> operation. 

Here is the retrieve operation for query '''customers'''. The SQL statement returns all records from the customers table that match a given customer id:

...

You can use any valid SQL statement in the configuration.    

Note the string '''{customerId}'''. The database connector SQL statements are actually templates that are resolved at run time using values passed in the http request. In the example above, if there is a parameter in the http GET request that hits the connector with customerId=1234 than the statement would return the record for customer 1234.

...

You can set the attribute '''autocreate''' in a '''query''' element autocreate in a query element.

<query name="customers" autocreate="true">

This property applies only when users submit an HTTP PUT request to the database connector. The property tells the database connector to create a new row in the database if one doesn't exist already meaning that the connector will run the '''create''' statement create statement automatically if the '''update''' statement update statement fails. In summary: 

• If the user is updating an existing record, the Update statement will work as it normally does and the autocreate function won’t kick in.
• If the user is adding a new record, the '''update''' statement update statement will fail (by design, because the record cannot exist if the user hasn’t added it yet) and the Connector will then run the '''create''' statement create statement.   

The autocreate feature is particularly useful when working with frevvo's '''repeat''' control repeat control. frevvo's repeat control gives you the ability to work with dynamic collections, for instance: customers, cars, addresses, dependents and others. When the user loads the form, the form may be initialized with some items (we will see how to do that with frevvo later). If the user adds new items to the collection and submits the form, those items will be automatically added to the database if '''autocreate=true'''true 

This behavior is actually enabled by default so if you want to turn it off you can set autocreate to false.

...

The autodelete feature is useful when working with frevvo '''repeat''' controls repeat controls. Imagine you have a collection of elements in the form that were initialized from a database. If you eliminate an item in the collection and submit the form, the connector will automatically remove the item from the database.  For that to happen, set the attribute '''autodelete''' to '''true''' in autodelete to true in the query element.

<query name="customers" autocreate="true" autodelete="true" deleteKey="customerId">

Behind the scenes, the connector actually compares the items in the database with what is submitted in the form. That comparison criteria is based on a key that you define with the attribute '''deleteKey''' deleteKey (required). The deleteKey value is normally the name of the primary key in the table that contains the repeat items.

...

If you define a date, time or timestamp column in your database the database connector will need to know the format of those dates in order to properly parse them. Also, when the connector reads the dates from the database, it will transform them to XML dates and those also can have a specific format. You can define both the databse and the xml date formats. Those definitions are done by defining attributes in the '''queryset''' element queryset element. For instance:

<queryset name="myStore" timeStampFormat="yyyy-MM-dd HH:mm:ss" dateFormat="yyyy-MM-dd" xmlDateFormat="yyyy-MM-dd">

...

On the other hand, when the XML documents is created, the date format will follow the definition of the attribute '''xmlDateFormat'''.

SQL Query Examples

A solid understand of SQL syntax is helpful when creating forms that interact with your database. Below are common and useful example queries.

...