Bookmark and Share Share

<sql>

Use <sql> to issue individual SQL queries to a data source that you have declared in the mashup script. You can also use this command to invoke stored procedures that return result sets.

For more information and examples, see <sql> Basics, Identifying the Datasource for the Query, Passing Query Parameters, Valid SQL Syntax, Defining a Subset of Rows to Return, Result Sets and Using Templates to Query Tables Dynamically.

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.

query yes

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

startrow  

Optionally, the index for the first row in the result set to return. If omitted, this defaults to one.
rowcount  

Optionally, the total number of rows in the result set to return. If omitted, this defaults to the end of the result set.

outputvariable yes

The required variable to accept the output of this statement.

<sql> Examples>

<sql> Basics

Queries put the SELECT statement in a query attribute and set an outputvariable to receive the result set. For example: 

<datasource url="jdbc:hsqldb:hsql://localhost:9001" 
  username="system" password="sa" name="localDB"/> 
... 
<variables> 
  <variable name=serviceId" type="string" default="CustomerDAO"/> 
  <variable name="cnt" type="number"/> 
  <variable name="customerId" type="number" default="1001"/> 
  <variable name="customers" type="document"/> 
</variables> 
... 
<sql query="select count(*) as countAll from AUDITABLE_EVENT where 
 SERVICE_ID = :serviceId" outputvariable="$cnt"/> 
<sql query="select CUST_ID, CUST_NAME from CUSTOMERS 
  where CUST_ID = :customerId" outputvariable="$customers" />

The output variable is typically a document type, to receive a result set, but can be any type.

As this example also shows, you must define the data source for the query with a <datasource> declaration. It is also a best practice to name all data sources.

Identifying the Datasource for the Query

You use the name attribute to identify a named datasource for the query. If you omit name, the query 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.

The first query shown below would be invoked against the default datasource for the mashup script: 

<!-- default data source --> 
<datasource url="jdbc:hsqldb:hsql://localhost:9001" 
  username="system" password="sa"/> 
<!-- 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"/> 
... 
<sql query="select count(*) as countAll from AUDITABLE_EVENT where 
 SERVICE_ID = :serviceId" outputvariable="$cnt"/> 
... 
<sql query="select name from category order by name desc" 
  name="customerDS" outputvariable="$customers" /> 
... 
<sql query="select item from inventory order by PART_NO" 
  name="inventoryDS" outputvariable="$items"/>

The second query, however, would be invoked against the datasource named customerDS and the third query.would be invoked against the datasource named inventoryDS.

Passing Query Parameters

To use parameters in the SQL query, you must declare <variable>s and use the variable name in the SQL query in the form :variable-name. For example:

... 
  <variable name=serviceId" type="string" default="CustomerDAO"/> 
... 
<sql query="select count(*) as countAll from AUDITABLE_EVENT where 
 SERVICE_ID = :serviceId" outputvariable="$cnt"/>

Valid SQL Syntax

EMML has no specific requirements about the syntax of the SQL you use in queries. What is valid depends on the data source the mashup connects to and the driver class used for the connection.

Defining a Subset of Rows to Return

You can also define the specific rows you want returned from the query using the startrow and rowcount attributes. Both of these attributes are optional.

To see the third through the twelfth result, for example, you would do something like this: 

<sql query="select name from category order by name desc" 
  startrow="3" rowcount="10" name="customerDS" 
  outputvariable="$customers" />

You can use variables with these attributes and omit either startrow or rowcount. For example: 

<variables> 
  <variable name="hundred" type="number" default="100"/> 
  <variable name="hundredone" type="number" default="101"/> 
</variables> 
... 
<!-- row 101 or greater --> 
<sql query="select name from category order by name desc" 
  startrow="$hundredone" name="customerDS" 
  outputvariable="$customers" /> 
... 
<!-- first 100 rows --> 
<sql query="select name from category order by name desc" 
  rowcount="$hundred" name="customerDS" 
  outputvariable="$customers" />

Result Sets

The structure of a direct SQL query has this form:

<records> 
  <record> 
    <columnA-name>value</columnA-name> 
    <columnB-name>value</columnB-name> 
    ... 
  </record> 
</records>

Column names are the SQL names from the database.

Using Templates to Query Tables Dynamically

You can also use <template> declarations to query tables dynamically. The <template> declaration allows you to build the SQL query from input variables and then use that within a <sql> command. For example: 

<input name="table" type="string"/> 
<output name="result" type="document"/> 
<variables> 
  <variable name="query" type="string"/> 
</variables> 
... 
<!-- build dynamic query SQL --> 
<template expr="select * from {$table}" outputvariable="$query"/> 
<!-- invoke dynamic query --> 
<sql name="myDataSource" query="$query" outputvariable="$result"/>