<sqlUpdate> Examples
Inserting, Updating or Deleting Records
To invoke SQL statements that update database records, define the datasource. Specify the SQL code in a statement attribute and define an outputvariable to receive the result.
For example:
<sqlUpdate name="myDataSource"
statement="insert into
credentials(id, user_id, password, cred_type)
values('1005', :user, :password, :type)"
outputvariable="$insertResult"/>
...
<sqlUpdate name="myDataSource"
statement="delete from credentials where id = :id"
outputvariable="$delResult"/>
...
<sqlUpdate name="myDataSource"
statement="update credentials set
user_id = 'newuser' where id = '1004'"
outputvariable="$updateResult"/>
If the datasource does not return a result, you can omit the outputvariable attribute.
Handling Table or Column Names with Spaces
For some databases, table, veiw of column names can include spaces. This can cause errors in queries or other SQL statements in EMML code.
The solution is to enclose these names in quote marks. Since the SQL statements are contained as attribute values in EMML, however, simply typing the quote marks to add them causes syntax errors as quote marks and apostrophes are both XML delimiters.
To avoid this, you must use the escaped characters (the XML entities) to enclose names with spaces. See
XML Escaping in URLs and Expressions for a complete list of entities for XML delimeters.
For example:
...
<sql name="auditDS"
query="select count(*) as countAll from "Audit Table"
where "Service ID" = :serviceId"
outputvariable="$cnt"/>
...
During parsing, the " entities are replaced with quote mark (") characters to ensure the SQL runs successfully.
Explicitly Declaring the Datasource
You use the name attribute to identify a named datasource for the SQL statement. In almost all cases, it is a best practice to configure datasources in the MashZone NextGen Server and simply refer to them by name in EMML code. This simplifies configuration and ensures that database credentials are securely encrypted.
If rare cases, it is useful to explicitly declare the datasource in the mashup script, although this is less secure. Although optional, you should also provide names for explicit datasources as this allows the MashZone NextGen Server to cache connections and enhance performance.
If you omit name in <sql>, the SQL statement is invoked against the default datasource identified in an unnamed <datasource> declaration in the mashup script.
Tip: Datasource names for a given connection must be unique across all mashup scripts hosted in a MashZone NextGen Server.
These two insertions are invoked against different databases:
<!-- customer data source -->
<datasource url="jdbc:hsqldb:hsql://234.20.1.65:9001"
username="system" password="sa" name="customerDS"/>
<!-- inventory data source -->
<datasource url="jdbc:hsqldb:hsql://234.20.2.35:9001"
username="system" password="sa" name="inventoryDS"/>
...
<sqlUpdate statement="insert into
customers(cust_id, name, contact, phone)
values('1005', 'TRG, Inc.', 'Mark Walberg', '4152345678')"
name="customerDS" outputvariable="$custInsert"/>
...
<sqlUpdate statement="insert into
contacts(cust_id, firstName, lastName, title, email)
values('1005', 'Mark', 'Walberg', 'Purchasing Manager',
'walberg@trg.com')"
name="contactsDS" outputvariable="$contactInsert"/>
You can also get transaction handling using the SQL transaction commands (see
SQL Transactions), but mashups do not support distributed transactions.