Save time with generalized JDBC datatypes

[Former Member]

XML SQL message protocol is an easy method to render a SQL Statement with a predefined datatype structure, like the one represented in the figure below: With this structure you:

• Define the statement type (SELECT, UPDATE, INSERT, DELETE) in the (action) attribute
• Set the table name in the (table) field
• Define the fields you want to use and their characteristics in the (access) structure
• Define the key of the statement in the (key) structure

Although this structure is common for all statement types, there are some differences that you should consider, like:

• Don't use (key) with INSERT statement
• Don't use (access) with DELETE statement
• In the (access) and (key)structure declare only the fields that you need for that statement, otherwise they will be used to build the statement or the WHERE clause
• You can find more info on XML SQL in the XI documentation: "Document formats for the receiver JDBC adapter"

For these reasons you may think that for each different statement you need a different, specialized datatype... This is not true! Learn how to build a single, "generalized" structure that you can map to all the statements you need.

1. First, define a datatype that represent the structure of your table: The field field_4_date is defined to handle a DATE for Oracle database. See this weblog for more detail: DATE/TIME datatypes and Oracle Database
2. Define a datatype that represent the single statement for the table: Both the (access) and (key)structure reference the XMLSQL_TABLE_STRUCTURE Note also the occurence of the fields: using 0...unbounded we can manage:
   • DELETE and INSERT statements
   • Complex WHERE clause: each (key) structure define a condition combined with OR operator
   • Multiple inserts: For INSERT statement each (access) structure represent a row insertion
3. Finally define a datatype that represent a complex database operation: In this datatype we define a single field of type XMLSQL_STATEMENT_STRUCTURE: thanks to the "open" occurrence we can execute more statements with a single database operation.

Now we need only to define the corresponding message type and we are ready to access our table. Here is some practical mapping example for the most common operations:

1. SELECT statement: SELECT field_1_string  &nbspFROM table &nbspWHERE field_2_integer = &nbspKEYVAL_1    &nbspOR field_1_string  = 'KEYVAL_2'
   • Set the (access) structure to any constant value in order to set the occurrence to 1. Here we used the default empty constant
   • Disable (right mouse button) the fields that are not part of the SELECT
   • Set the fields that you want to retrieve to an empty constant
   • Duplicate the (key) subtree (right mouse button) as needed
   • Set each (key) structure to any constant value in order to set the occurrence to 1
   • For each (key) structure disable the fields not used and set the key value for the others
2. INSERT statement: INSERT INTO table (field_1_string, field_2_integer)        VALUES     ('INSVAL_1', INSVAL_2)
   • Set the (access) structure to any constant value in order to set the occurrence to 1.
   • Disable (right mouse button) the fields that are not part of the INSERT Note that if these fields are declared NOT NULL in the table definition you will receive an error.
   • Set the other fields to their desired value
   • Disable the (key) structure
3. DELETE statement: DELETE FROM table &nbspWHERE field_1_string  = 'DELVAL_1'   &nbspAND field_3_boolean = &nbspDELVAL_2
   • Disable the (access) structure
   • Duplicate the (key) structure if you need a WHERE clause with OR operator
   • For each (key) structure disable the unnecessary fields and set the key value for the others
4. UPDATE statement: UPDATE table   &nbspSET &nbspfield_1_string  = 'UPDVAL_1',         field_2_integer = &nbspUPDVAL_2 &nbspWHERE (field_1_string  = 'KEYVAL_1'   &nbspAND &nbspfield_2_integer = &nbspKEYVAL_2)    &nbspOR &nbspfield_3_boolean = &nbspKEYVAL_3
   • Set the (access) structure to any constant value in order to set the occurrence to 1.
   • Disable in the (access) structure the fields that you don't need to update, and set the others to their update value
   • Duplicate the (key) structure if you need a WHERE clause with OR operator, and set each structure to any constant value
   • For each (key) structure disable the unnecessary fields and set the key value for the others

Now you have all the elements to build your own complex operations: For instance, you can use a 2-statement message to SELECT some row and then UPDATE the same rows with a flag to avoid duplicate selection. To summarize, the technique described in this weblog allows you to:

• Use a single Message Type for all the operations on a table
• Execute many statement with a single database call
• Execute different statements within the same database transaction

Moreover, you can extend this technique and build more complex message types that can handle statements for more than a table. Finally, remember also that for complex joins or non-standard statements you have the opportunity to execute a raw query using the SQL_QUERY and SQL_DML message protocols. See the previous reference for more details.