<sql> Examples>

Queries put the SELECT statement in a query attribute and set an outputvariable to receive the result set. Identify the datasource by name, matching a datasource configured in the MashZone NextGen Server.

...
<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 name="auditDS"
query="select count(*) as countAll from AUDITABLE_EVENT where
SERVICE_ID = :serviceId" outputvariable="$cnt"/>
<sql name="customerDS"
query="select CUST_ID, CUST_NAME from CUSTOMERS
where CUST_ID = :customerId" outputvariable="$customers" />

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.

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

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

<datasource url="jdbc:hsqldb:hsql://localhost:9001"
username="system" password="sa"/>
...
<sql query="select count(*) as countAll from AUDITABLE_EVENT where
SERVICE_ID = :serviceId" outputvariable="$cnt"/>

<datasource url="jdbc:hsqldb:hsql://234.20.1.65:9001"
username="system" password="sa" name="customerDS"/>
...
<sql query="select name from category order by name desc"
name="customerDS" outputvariable="$customers" />

And this last query.would be invoked against the datasource named inventoryDS whose connection information is provided dynamically using MashZone NextGen global attributes.

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" name="auditDS"/>

Important: For information on security concerns for query parameters, see Guarding Against SQL Injection Attacks.

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

Using input parameters in a mashup script to provide query parameters or to build dynamic queries can potentially allow SQL injection attacks in some cases. EMML uses Java Prepared Statements to execute queries in <sql> statements. The form of the dynamic portions of the statement determine whether a SQL injection attack is possible.

Queries that use the :variable-name syntax to supply only the value of a condition in a WHERE clause are not vulnerable to SQL injection because the variable is not interpreted as SQL code. Queries built as shown in Passing Query Parameters have no risk of an injection attack.

There is some risk of SQL injection, however, in dynamic queries built from <template> or <assign> statements where some portion of the resulting query comes from an input parameter. The example shown in Using Templates to Query Tables Dynamically:

Clearly can include SQL code other than a simple table name in the input parameter and thus could be open to a SQL injection attack.

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.

If the SQL query is working with a large dataset or potentially returning a large result set, you can use the fetchSize attribute to optimize mashup performance and prevent out of memory issues. This attribute defines a maximum number of rows to fetch from the database in one chunk.

The mashup must actually iterate through the first 15,000 rows to build the result set with rows 10,000 through 14,999. A request to obtain all 15,000 rows might fail, for lack of memory, or take an unacceptable amount of time.

Instead, the result set is built with several requests to the database, each returning 2,000 rows. Any rows not needed in the result set for the mashup are simply discarded.

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

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:

Important: For information on security for dynamic queries, see Guarding Against SQL Injection Attacks.