MuleSoft Connector for Excel Services

Build 20.0.7587

Parameterized Queries

You can use MEL (MuleSoft expression language) expressions to build parameterized queries and dynamic queries. The following sections provide more information on executing and configuring both types of queries.

Executing Parameterized and Dynamic Queries

  • Parameterized: By default the connector executes parameterized statements, which make use of SQL parameters to pass data to the statement. Evaluated expressions are passed as the input parameter values.
  • Dynamic: Dynamic queries are built through string replacement of Mule variables in the SQL query at runtime.

    Correspondingly, dynamic queries do not prevent SQL injection attacks, unlike parameterized queries. However, dynamic queries provide more flexibility than SQL parameterized statements -- you can use expressions to generate any part of the query, such as the table name.

Configuring Parameterized Queries

Follow the steps below to configure a parameterized query like the following:

select * from Account where Industry = #[flowVars.Industry]

Visual Editor

To configure a parameterized query in the Visual Editor, select the connector inside your flow and specify the following connector properties.

  • Connector Configuration: Select a connection you created. See Establishing a Connection for more information.
  • Operation: Choose the operation corresponding to the SQL statement.
  • Query Type: Leave the default selection, Parameterized.
  • SQL Query: Enter the SQL command.

XML Editor

The XML below configures a parameterized query:

<cdata-excelservices:select-advanced config-ref="CData_ExcelServices_Configuration" query="SELECT * FROM Account WHERE Industry = #[flowVars.Industry]" doc:name="CData ExcelServices"/>

See Querying Data for all of the available fields in the <cdata-excelservices:select-advanced> element.

Configuring Dynamic Queries

Follow the steps below to configure a dynamic query like the following:

select * from #[flowVars.table]

Note that a dynamic query requires the use of escapes, unlike parameterized queries. The following dynamic query must use string escapes in the WHERE clause, unlike the parameterized example:

select * from Account where Industry = '#[flowVars.Industry]'

Visual Editor

To configure a dynamic query in the Visual Editor, select the connector inside your flow and specify the following connector properties.

  • Connector Configuration: Select a connection you created. See Establishing a Connection for more information.
  • Operation: Choose the operation corresponding to the SQL statement.
  • Query Type: Choose Dynamic.
  • SQL Query: Enter the SQL command.

XML Editor

To configure a dynamic query in XML, set the queryType attribute to "Dynamic" in the connector configuration element.

<cdata-excelservices:select-advanced config-ref="CData_ExcelServices_Configuration" query="SELECT * FROM #[flowVars.table]" doc:name="CData ExcelServices" queryType="Dynamic"/>

See Querying Data for all of the available fields in the <cdata-excelservices:select-advanced> element.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587