MashZone NextGen 10.2 | Appendix | Legacy Presto components | Mashables and Mashups | Mashups in EMML | Writing Mashups in EMML | Issuing SQL Statements Directly to a Datasource | <sql> | <sql> Examples>
 
<sql> Examples>
<sql> Basics
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.
For example:
...
<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" />
The output variable is typically a document type, to receive a result set, but can be any type.
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 &quot;Audit Table&quot;
where &quot;Service ID&quot; = :serviceId"
outputvariable="$cnt"/>
...
During parsing, the &quot; 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 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.
The 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"/>
...
<sql query="select count(*) as countAll from AUDITABLE_EVENT where
SERVICE_ID = :serviceId" outputvariable="$cnt"/>
This second query, however, would be invoked against the datasource named customerDS.
<!-- customer data source -->
<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.
<variables>
<variable name="global.inventory.ds.url" type="string"/>
<variable name="global.inventory.ds.user" type="string"/>
<variable name="global.inventory.ds.pw" type="string"/>
</variables>
...
<!-- inventory data source -->
<datasource url="$global.inventory.ds.url"
username="$global.inventory.ds.user"
password="$global.inventory.ds.pw" name="inventoryDS"/>
...
<sql query="select item from inventory order by PART_NO"
name="inventoryDS" outputvariable="$items"/>
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" name="auditDS"/>
Important: For information on security concerns for query parameters, see Guarding Against SQL Injection Attacks.
Valid SQL Syntax
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.
Guarding Against SQL Injection Attacks
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:
<input name="table" type="string"/>
<variable name="query" type="string"/>
...
<!-- build dynamic query SQL -->
<template expr="select * from {$table}" outputvariable="$query"/>
<!-- invoke dynamic query -->
<sql name="myDataSource" query="$query" outputvariable="$result"/>
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.
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" />
Optimizing Performance for Large Datasets
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.
With this example:
<sql query="select name from category order by name desc"
startrow="10000" rowcount="5000" fetchSize="2000"
name="customerDS" outputvariable="$customers" />
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.
Result Sets
The structure of the result set for 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"/>
Important: For information on security for dynamic queries, see Guarding Against SQL Injection Attacks.
Working Samples
Two of the mashup samples for MashZone NextGen use the <sql> statement:
*DatabaseSampleJNDI (jndids.emml)
*DatabaseSample (sql.emml)
See Mashup Samples for a complete list of samples and where to find them.

Copyright © 2013-2018 | Software AG, Darmstadt, Germany and/or Software AG USA, Inc., Reston, VA, USA, and/or its subsidiaries and/or its affiliates and/or their licensors.
Innovation Release