Execute

The primary use of the “Execute” structure is to phrase more complex SQL queries; the basic structure is given below (We’ve utilized a generic example here; for an example as used in query.xml, see the section on Subvariables):

<sql:Iterate over="variableName" as="itemName">
        <sql:Execute into="secondVariableName" as="recordName">
                <sql:SQL>
                        SELECT
                                someValues
                        FROM
                                TableNameQualifier.TableName
                        WHERE
                                someCondition = ?
                                someOtherCondition = ?
                </sql:SQL>
        <sql:Params>ognl:#itemName.getFieldValue('COLUMNNAME')</sql:Params>
        <sql:Params>ognl:#itemName.getFieldValue('OTHERCOLUMNNAME')</sql:Params>
        </sql:Execute>
</sql:Iterate>

<sql:Execute>
        <sql:SQL>
                INSERT INTO TableNameQUalifier.TableName
                        Values (Default, Default);
        </sql:SQL>
</sql:Execute>

Notice in the first example we’ve encapsulated the Execute in an Iterate function; this is so we have a local variable we can utilize OGNL on, to see how dynamic parameters are passed into the SQL query. The SQL query itself can take absolutely any form – be aware, however, that SQLXML cannot ensure that the query is valid for the database that is being executed against.

When an Execute function is expected to have a data set returned, specify an “into” and “as” attribute, in similar function to the Select structure. When it is expected not to, do not; the SQLXML may not execute properly, depending on the database being executed against.