<sqlcall>
Use <sqlcall> to invoke stored procedures in a named datasource that has been configured by a MashZone NextGen administrator in the MashZone NextGen Server or a datasource that you have explicitly declared in the mashup script. Stored procedures may return a result set or not.
Note: In previous releases, the <sql> and <sqlUpdate> statements were used to invoke stored procedures. This usage is still supported but deprecated.
You must first define configuration information and name datasources in the MashZone NextGen 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 <sqlcall> statement to prevent anyone from using direct SQL queries in a mashup.
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 stored procedure. In most cases, this is the name of a datasource that has been configured in the MashZone NextGen Server, although the datasource can be declared directly in the mashup script. If omitted, the stored procedure is invoked in the default datasource. |
query | yes | The stored procedure to execute. |
params | | A comma-separated list of variables to send as input parameters, if any, to the stored procedure. The name(s) of these variables must match the input parameter names in the stored procedure. |
types | | A comma-separated list of database datatypes, one for each input parameter in the params atttribute. List the datatypes in the same order that corresponds to the input parameters. See Examples. |
hasreturn | | Either true or false indicating whether this stored procedure returns a result set or not. |
outputvariable | yes | The required variable to accept the output of this statement. |
Examples
The following example executes a stored procedure that has one input parameter on the customerdb datasource that has been configured by a MashZone NextGen administrator in the MashZone NextGen Server. This procedure has a result set:
...
<!-- input parameter for stored procedure -->
<variable name="custId" type="string" />
<!-- variable for result set -->
<variable name="invoices" type="document" />
<!-- invoke stored procedure against named datasource -->
<sqlcall name="customerdb" query="sp_customer_invoices" params="customId"
types="VARCHAR" hasreturn="true" outputvariable="$invoices" />
...
The next example shows a stored procedure that inserts a new record in the same data source as the previous example. This stored procedure has no result set.
...
<!-- input parameters for stored procedure -->
<variable name="custId" type="string" />
<variable name="customerName" type="string" />
<variable name="invoices" type="string" />
<!-- invoke stored procedure -->
<sqlcall name="customerdb" query="sp_customer_insert"
params="custId, customerName, invoices" type="VARCHAR, VARCHAR, VARCHAR"
hasreturn="false" outputvariable="$result" />
...