Home| Printable Version

The SQL Statement action enables access to relational databases. This provides the ability to define parameterised SQL queries and dynamically populate the parameters from incoming xml data. The resulting information from the queries is also available within the FactBase for further processing using other rules. Queries defined using the SQL Statement are packaged and executed against the RDBMS using JDBC. The SQL Statements are entered within the SQL Statement data entry editor. Multiple SQL Queries can be separated by using the ; character and the SQL parameters are denoted by using a $ prefix. RuleMaker will automatically create a parameter entry box under the SQL Parameters section for each parameter that is entered. You can specify an XPath for each SQL parameter to specify where to get the required value from the runtime data. As usual, this information could come from a variable if required, and the XPath can be easily populated by drag-and-drop from any open document. For each dynamic parameter in the SQL statement it is also important to set the type of data that this will contain, for example, is it a textual, numeric, or date value. This can be set using the dropdown at the right of each parameter. If a date type value (date, time or timestamp) is being used then the value in the XML data must be stored in the W3C Schema defined format. If the data is coming form a form, then this can be easily done using the inbuilt 'Value Conversions' functionality provided by FormMaker. This action also supports calling certain types of stored procedures using SQL Statement syntax of the form {call procedure_name($param1)}. This currently only supports procedures that use IN parameters, and return a single result set or update count. You can specify the location where the response from the query should be inserted using the to location parameter. As usual, you can choose whether to append to this location, or replace the existing contents. The response from the SQL Statements will be wrapped using the following document format:

<sql_result>
    <statement>
        <status outcome="success">
            <record_count>1</record_count>
            <generated_keys>
                <record>
                    <value>2</value>
                </record>
            </generated_keys>
        </status>
    </statement>
    <statement>
        <status outcome="success"/>
        <record>
            <client>1</client>
            <forename>Fred</forename>
            <surname>Bloggs</surname>
            <age>21</age>
        </record>
        <record>
            <client>2</client>
            <forename>Brenda</forename>
            <surname>Bloggs</surname>
            <age>20</age>
        </record>
    </statement>
</sql_result>

The sql_result element wraps the responses from one or more queries. For each statement you will notice a separate statement element. Within each statement element a status element will indicate the outcome of each query using the outcome attribute. For INSERT and UPDATE queries there will be additional elements to indicate the number of records that were affected via the record_count element. For INSERT queries you will also notice a generated_keys element. This element will contain details of the generated keys in the database resulting from the INSERT query. For SELECT queries, the ResultSet is returned as a list of record elements. You may also notice a final statement that can indicate the outcome of an implicit ROLLBACK operation on the database if an error has been detected. COMMIT and ROLLBACK All the SQL statements for the same Database, contained within a RuleBase will execute in the same transaction. Once all the rules have been processed, a ROLLBACK will be automatically issued, and any uncommitted changes will be lost. Therefore it is very important to include an explicit COMMIT statement in your rules whenever you are performing database insert or update type operations. If your rules were initially created for you by making use of a database when creating a page in FormMaker, then you should find that a specific COMMIT rule has already been added. You can of course add any other explicit COMMIT or ROLLBACK statements that you may need. Database Connection You can use the Select link against the Database field to display the following database connection information tab and populate the information to configure a database connection. Once you have defined a connection, you can reuse this connection for subsequent actions that use the same database. In this case you just need to select the entry from the Existing Connections drop down, rather than creating a new connection. Once a connection has been selected for the SQL Statement action, its name will be displayed against the DB Connection field, and the Change link will appear to allow a different connection to be selected. If you need to change the specific details of a database connection (for example, the connection URL or username/password details) then this can be done via the XDE deployment screens. Any SQL Statement action configured to use that database connection will automatically use the new details. Please see the XDE User Guide for more information.