Fox Using Storage Location

This worksheet will help you understand how storage location’s work in FOX and why they are essential.

SYNTAX

Within the storage location element, you can add an fm:database node, to tell FOX that the data from the Data DOM has a direct relationship with a database table. The Data DOM would be stored as a Clob or XML column on this table.

The fm:database syntax has 3 child elements:

fm:query in which you can write SELECT statements
fm:update in which you can write UPDATE statements
fm:insert in which you can write INSERT statements

Aside from having the SQL code inside an fm:sql tag, each of the 3 above elements are syntactically the same as an fm:query in a db-interface.

Here is an example of the syntax:
<fm:storage-location name="main">
  <fm:cache-key string="CACHE_NAME:1">
    <fm:using using-type="UNIQUE"/>
  </fm:cache-key>
  <fm:new-document>
    <fm:root-element>ROOT</fm:root-element>
  </fm:new-document>
  <fm:database>
  <fm:query>
  <fm:sql>
SELECT xml_data FROM portal_folders WHERE id = :1
FOR UPDATE OF xml_data NOWAIT
  </fm:sql>
  <fm:using>:{params}/P_PF_ID</fm:using>
  </fm:query>
  <fm:insert>
    <fm:sql>
INSERT INTO portal_folders VALUES (:1, :2)
    </fm:sql>
    <fm:using>:{params}/P_PF_ID</fm:using>
    <fm:using using-type="DATA-XMLTYPE"/>
  </fm:insert>
  <fm:update>
    <fm:sql>
UPDATE portal_folders SET xml_data = :1 WHERE id = :2
    </fm:sql>
    <fm:using using-type="DATA-XMLTYPE"/>
    <fm:using>:{params}/P_PF_ID</fm:using>
  </fm:update>
  </fm:database>
</fm:storage-location>

CONCEPTS

FOX will automatically read and write data back to the database from the Data DOM on each transaction cycle, through the storage location, dependant on what clauses you specify. For instance:

When the module is first entered, data is queried into memory from the database using the fm:query ‘SELECT’ statement. A select statement is mandatory and should always exist.
If no rows are returned from the SELECT statement, then FOX looks for an fm:insert statement and tries to execute this, to insert stub data into the table possibly using parameters in the Params DOM.
If the SELECT statement in the fm:query block successfully finds data, and the data changes during a Transaction Process, FOX looks for an fm:update statement to UPDATE the database table.
When the request has been processed, the Data DOM and the row in the database table will be the same.

If there are no fm:database statements, then FOX will create a blank XML document with a root element of whatever is specified in the fm:new-document/fm:root-element text.

Storage locations can only operate on a single record; usually derived from a parameter passed to the module. To operate on a record that is part of a list, you can use phantoms.

EXERCISES

MINI WALK-THROUGH

Get the file EMPLOYEEMODULE_SL.xml from the Training/FOX folder in PVCS.
Create a personal copy in your FOX Training folder in My Documents and rename it XX_EMPLOYEEMODULE_SL.xml (where XX are your initials).
Right-click on the file and go to Properties. Un-tick the Read-only box.
Open your copy of XX_EMPLOYEEMODULE_SL.xml in XML Spy.
Replace all instances of EMPLOYEEMODULE_SL with XX_EMPLOYEEMODULE_SL.
Replace the xx in xx_employee_search with your initials for all instances.

This module queries a list of Employees into the Data DOM. The “Select” phantom will load the same module from a different entry theme (more on these in a later Chapter). The “Save and Return” button exits the most recent module call, returning to the list of Employees.

Exercise 1

Run the following SQL on the SCOTT schema, replacing xx with your initials in the table name:

CREATE TABLE xx_employee_search AS
(
SELECT
id
, XMLELEMENT("EMPLOYEE"
, XMLFOREST(id, forename, surname, hire_date)
) xml_data
FROM scott.employee_search
WHERE id < 2000
)
/
GRANT SELECT, INSERT, UPDATE ON scott.xx_employee_search TO APPENV
/

Exercise 2

Change the storage location “sl-detail” so that it queries the selected employee’s data from scott.xx_employee_search into the Data DOM on entry and updates the data upon POST.

NB: Look at “action-goToDetail” to see how the employee’s ID is passed.

View the Data DOM to see how the data is handled.