fm:query, fm:using

fm:query is used to define the query data transformation rules

SYNTAX

<fm:db-interface name="dbint-dual">
  <fm:query name="qry-sysdate">
   <fm:select>
SELECT student_name
FROM   scott.students
WHERE  student_id = :1
   </fm:select>
   <fm:using name=”:1”>/ROOT/STUDENT/ID</fm:using>
 </fm:query>
</fm:db-interface>

CONCEPTS

<fm:select>

• The SQL select statement is defined in the fm:select clause
• The select statement can optionally have Bind Variable placeholders
• Bind Variables can be bound by :name (preferred method), and by position number with :1 or ?

e.g. if using names, it does not matter which order you put them in when listing each <fm:using name=”name”>Xpath</fm:using>, however, if you use :3 for example in the query as a bind variable, you will have to make sure the third <fm:using>Xpath</fm:using> you make actually points to the element you require for that particular bind variable.

<fm:using>

• The fm:using clause is required for each bind variable
• Attribute name is only required when binding by name e.g. name=":data"

NB: Do not use any of the SQL reserved words as your bind variable names. I.E. :data

• The value to bind is derived using XPath, either
• Attribute: datadom-location="/*/DATA"
• Text Content: <fm:using>:{params}/P_ACT_ID</fm:using>
• The XPath is evaluated relative to EACH Subject Element (in the run-query match clause)
• Optional attribute sql-type tells Oracle the SQL datatype provided. Default: VARCHAR2
• Optional attribute datadom-type tells FOX the XMLSchema data type
• The default datadom-type is either obtained from the xs:element type or defaults to xs:string
• As xs:date/xs:datetime often defined on xs:element, there is implicit date conversion
• Entire XML structures can be passed to oracle:
• datadom-type="dom" sql-type="xmltype"
• Large objects can be passed to Oracle using sql-type=”clob”
• datadom-type="xs:string" – text data passes as temporary clob
• datadom-type="clob" – XML data passed as temporary clob

NB: If data is to be returned back to an existing element, the columns that are queried in either have to have the same names, or use aliases, otherwise new elements will be created in the DOM.

EXAMPLES

Example 1

Query sysdate into the current attach point:

1. fm:db-interface declaration

<fm:db-interface name="dbint-dual">
  <fm:query name="qry-sysdate">
   <fm:select>
SELECT sysdate
FROM   dual
   </fm:select>
  </fm:query>
</fm:db-interface>

2. fm:run-query command

<fm:run-query interface="dbint-dual" query="qry-sysdate"/>

Example 2

Query to uppercase CD track titles

1. fm:db-interface declaration

<fm:db-interface name="dbint-dual">
  <fm:query name="qry-uppercase">
   <fm:select>
SELECT upper(:fname) “forename”
FROM   dual
   </fm:select>
   <fm:using name="fname">./FORENAME</fm:using>
  </fm:query>
</fm:db-interface>

NB: “forename” matches on DOM elements in order to replace the current element with the new value

2. fm:run-query command in entry theme

<fm:run-query interface="dbint-dual" query="qry-uppercase" match="/*/EMPLOYEE_LIST/EMPLOYEE"/>

EXERCISES

Exercise 1

In your copy of XX_EMPLOYEEMODULE (where XX are your initials), create an action called “action-change-date” that will run a query to convert the HIRE_DATE for each employee to the day of hire. This will require you to use what you have learned from Chapters 24 to 27.

Hint: Use TO_CHAR.