Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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 (required). The deleteKey value is normally the name of the primary key in the table that contains the repeat items.

The Doc URI Approach: Performing CRUD Operations using a Table Control

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.

Doc URIBusiness Rules
No code neededJavascript code required
Automatically pass all controls from schemaPass each individual control
Call the DB Connector once for all operations (using the autocreate featureCall the DB Connector query for every add/update operation

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. For example consider this query:

Code Block
<query name="products" autocreate="true" autodelete="true" deleteKey="id">
	<retrieve>
	    <statement>
		<!-- Maps to the HTTP GET method -->
		SELECT * FROM products WHERE orderId='{orderId}'
	    </statement>                                                    
	</retrieve>
	<create> 
	    <statement>
		INSERT INTO products
		(orderId, productName, quantity,price) 
		VALUES ({orderId}, '{productName}', '{quantity}', {price})
	    </statement>
	</create>
	<update>
	    <statement>
		UPDATE products SET  productName = '{productName}', quantity = {quantity}, price = {price}                                                
		WHERE id = {id}                                                                         
	    </statement>
	</update>
	<delete>
	    <statement>
	     DELETE from products where id={id} 
	     </statement>
	</delete>
</query>
  1. Note that the id column of this products table is a Primary Key/Unique column and an automatically incrementing number is assigned as value to this column (on the database side) when inserting a new record in Products table.
  2. Notice the autocreate and autodelete attributes of this query. Please see Auto Create Rows above.
  3. This query has all the CREATE, RETRIEVE, UPDATE, and DELETE statements declared in it.
  4. To create the schema, browse the DB connector query URL with /schema appended to it. For example: http://localhost:8082/database/BIRT/products/schema?orderId=10000
  5. After creating the form fields using this schema, configure the Doc Action -> Doc URI to:

    1. Read URL: http://localhost:8082/database/BIRT/products?orderId=\{orderId} with method: GET
    2. Write URL: http://localhost:8082/database/BIRT/products with method: PUT

      Info

      See this section for an explanation of how HTML statements (GET, PUT, etc.) translate to SQL statements (RETRIEVE, UPDATE, etc.)

  6. Test the form. Enter a valid orderId, and notice that the form table will automatically expand and display all the order items retrieved by the SELECT statement.
    1. Add a row and enter some data.
    2. Edit another row.
    3. Delete a different row.
      Image Added
  7. 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.
Info

What is happening "behind the scenes"? When you use the Doc URI approach to read/add/update/delete rows, the controls in your form are bound to the schema (data source) from which they were created. When the form is submitted, frevvo creates an XML document which conforms to that schema. This XML document has all the values entered in those schema controls. When the form is submitted, this XML document is sent to the DB connector. The DB connector can get the control values directly from this document and use them while executing the SQL query. 

Dates and Timestamps

Date, Time and DateTime formats are managed automatically by the connector.  You do not need the attributes that define the formats in your querysets. The defaults now match what 
Frevvoproduct
sends to the DBConnector and should cover most cases. If you define a date, time or timestamp column in your database, the database connector will know the format of those dates in order to properly parse them. Below is an example of the attribute in the queryset element that is no longer needed.. For instance:

...