Mashables and Mashups : Mashups in EMML : Writing Mashups in EMML : Issuing SQL Statements Directly to a Datasource : <sql>
<sql>
 
<sql> Examples>
Use <sql> to issue individual SQL queries to a named datasource that has been configured by a Business Analytics administrator in the Business Analytics Server or a datasource that you have explicitly declared in the mashup script.
Note:  
In previous releases, the <sql> and <sqlUpdate> statements were also used to invoke stored procedures. This usage is still supported but deprecated.
You must first define configuration information and name datasources in the Business Analytics Server. This ensures that drivers are available in the classpath. It also keeps database credentials securely encrypted and simplifies EMML code.
You can disable the <sql> command to prevent anyone from using direct SQL queries in a mashup.
For more information and examples, see:
*<sql> Basics
*Handling Table or Column Names with Spaces
*Explicitly Declaring the Datasource
*Passing Query Parameters
*Valid SQL Syntax
*Guarding Against SQL Injection Attacks
*Defining a Subset of Rows to Return
*Optimizing Performance for Large Datasets
*Result Sets
*Using Templates to Query Tables Dynamically
See also Working Samples for information on working samples you can review.
Can Contain
Empty
Allowed In
mashup | catch | else | elseif | for | foreach | if | macro | operation | sequence | try | while
Attributes
Name
Required
Description
name
The name of the datasource for this query. In most cases, this is the name of a datasource that has been configured in the Business Analytics Server, although the datasource can be declared directly in the mashup script. If omitted, the query is sent to the default datasource.
query
yes
The query to execute. The actual SQL syntax supported depends on the database and driver class for this datasource.
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.
fetchSize
Optionally, the amount of memory, expressed as an optimal number of rows, that the mashup should request from the datasource at one time. If the total number of rows requested by this SQL statement is larger than this number, the mashup uses multiple requests to the datasource to obtain the full result set to return.
This is most useful with SQL statements that may work with a large dataset in the database to optimize performance. This attribute can be used with or without the rowcount attribute.
stream
An extension attribute for use with other extensions from the Real-Time Analytics Query Language. See RAQL Extensions to EMML Statements for Streaming for more information.
outputvariable
yes
The required variable to accept the output of this statement.
Copyright © 2013-2016 Software AG, Darmstadt, Germany.
Product LogoContact Support   |   Community   |   Feedback