Bookmark and Share Share

<sqlUpdate>

Use <sqlUpdate> to execute any SQL statement that is not a query against a data source that you have declared in the mashup script. This includes basic statements to update database records or stored procedures that do not return result sets.

For more information and examples, see Inserting, Updating or Deleting Records and Identifying the Datasource. For examples of invoking stored procedures, see Invoking Stored Procedures.

Can Contain Empty
Allowed In mashup, else, elseif, for, foreach, if, macro, operation, sequence, while,

Attributes

Name Required Description
name  

The name of the data source for this query. If omitted, the query is sent to the default data source.

statement yes

The SQL statement to execute. The actual SQL syntax supported depends on the database and driver class for this data source.

outputvariable  

An optional variable to accept the output of this SQL command.

<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:

<datasource url="jdbc:oracledb:osql://localhost:9001" 
  driverclassname="oracle.jdbc.driver.OracleDriver" 
  username="system" password=""/> 
... 
<sqlUpdate statement="insert into 
    credentials(id, user_id, password, cred_type) 
    values('1005', :user, :password, :type)" 
    outputvariable="$insertResult"/> 
... 
  <sqlUpdate statement="delete from credentials where id = :id" 
    outputvariable="$delResult"/> 
... 
  <sqlUpdate 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.

Identifying the Datasource

You use the name attribute to identify a named datasource for the SQL statement. If you omit name, the SQL statement is invoked against the default datasource - if the mashup script has an unnamed <datasource> declaration.

Tip: named data sources allows a mashup script to work with multiple databases. They are also a best practice to enhance performance as connections to named data sources are cached.

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.