HLL scanners will emit 3 XML element types: SqlTable, SqlColElem, and SqlColumn.

SqlTable is equivalent to the ARZOS TABLE entity. It will be identified by SERVER_NAME, CREATOR, and TB_NAME.

SqlColumn will be emitted for any reference to a column within a table in an SQL DML statement (INSERT, UPDATE, SELECT, or DELETE.) References will be found in any clause, including the WHERE clause, and the DML statement may appear inside of a DECLARE CURSOR statement. SqlColumn is identified by its containing SqlTable and a column name within the table. The ARZOS metamodel does not have an analogue for SqlColumn, but it will be processed by the loader as described below.

SqlColElem will be emitted for each SqlColumn where that column is assigned to or from a host variable; that is, in the following situations:

• Assignment from SELECT INTO statement into host variable
• Assignment from host variable into INSERT or UPDATE statement

Note that SqlColElem is not currently emitted for in the following situations.

• Assignment from FETCH statement into host variable (future enhancement, note that the table and column will be noted via the DECLARE CURSOR STATE)
• Relational comparisons between columns and host variables in the WHERE, JOIN, or GROUP BY clauses (future enhancement)

The ARZOS metamodel equivalent of SqlColElem is the COLUMN relationship. The COLUMN relationship will be created by the loader as follows:

For a SqlColumn that has an SqlColElem associated with it, create a COLUMN relationship from the source TABLE to the target ELEMENT specified by the SqlColElem.

The loader will also synthesize an ELEMENT based on the column name, and create the COLUMN relationship from the source TABLE to the synthesized ELEMENT. This is done for compatibility with the ARZOS metamodel and not due to any requirement of the scanner.

Details of SQL Scanner XML

The SQL scanner outputs the following XML elements

SqlTable

This XML element represents an explicit reference to a table in the SQL. It has the following attributes:

id - a unique numeric identifier for the element
name - the name of the table
selects - The number of times the program used the table as the object of a SELECT statement
inserts - The number of times the program used the table as the object of a INSERT statement
updates - The number of times the program used the table as the object of a UPDATE statement
deletes - The number of times the program used the table as the object of a DELETE statement

SqlColumn

This XML element represents an explicit reference to a column in the SQL. It has the following attributes:

id - a unique numeric identifier for the element
name - the name of the column
table - the id of the table that owns this column
inserts - the number of times the column was mentioned in an INSERT statement.
selects - the number of times the column was mentioned in a SELECT statement.
updates - the number of times the column was mentioned in an UPDATE statement.
deletes - the number of times the column was mentioned in a DELETE statement.

SqlColElem

This XML element represents an explicit assignment from a SqlColumn to or from a program variable. These assignments can occur in a SELECT INTO statement, the VALUES clause of an INSERT statement, or the SET clause of an UPDATE statement. This element has the following attributes:

id - a unique numeric identifier for the SqlColElem element
source - The id of the source of the assigment (the value being read). This is the id of either a SqlColumn element or a ELEMENT, GROUP, or RECORD element.
target - The id of the target of the assigment (the value being written).
This is the id of either a SqlColumn element or a ELEMENT, GROUP, or RECORD element.

Examples

SELECT NAME,ADDRESS FROM CUSTOMERS WHERE STATE='OK'

<SqlTable id="88" name="CUSTOMERS" >
  <SqlColumn id="97" name="NAME" selects="1" />
  <SqlColumn id="98" name="ADDRESS" selects="1" /> <SqlColumn id="102" name="STATE" selects="1" />
</SqlTable >

SELECT NAME, ADDRESS INTO :NAME, :ADDRESS FROM CUSTOMERS WHERE ID=:ID

<!-- *output by the prgram scanner -->
<ELEMENT id="22" name="ID" />
<ELEMENT id="31" name="NAME" />
<ELEMENT id="64" name="ADDRESS" />
<!-- *output by the prgram scanner -->
<SqlTable id="88" name="CUSTOMERS" selects="1" >
  <SqlColumn table="88" id="97" name="NAME" selects="1" />
  <SqlColumn table="88" id="98" name="ADDRESS" selects="1" /> <SqlColumn table="88" id="102" name="ID" selects="1" />
</SqlTable >
<SqlColElem id="1024" source="97" target="31" />
<SqlColElem id="1025" source="98" target="64" />

Note: The comparison ID=:ID is not noted because it is not an assignment to/from a variable from/to a database column.

INSERT INTO CUSTOMERS (ID, NAME, ADDRESS) VALUES (:ID,:NAME,:ADDRESS);

<!-- *output by the prgram scanner -->
<ELEMENT id="22" name="ID" />
<ELEMENT id="31" name="NAME" />
<ELEMENT id="64" name="ADDRESS" />
<!-- *output by the prgram scanner -->
<SqlTable id="88" name="CUSTOMERS" inserts="1" >
  <SqlColumn table="88" id="97" name="NAME" inserts="1" />
  <SqlColumn table="88" id="98" name="ADDRESS" inserts="1" /> <SqlColumn table="88" id="102" name="ID" insert="1" />
</SqlTable >
<SqlColElem id="1024" source="22" target="102" />
<SqlColElem id="1025" source="31" target="97" />
<SqlColElem id="1026" source="64" target="98" />