Dynamic SELECT query which decides whether to use the WHERE clause in Mule 4
up vote
0
down vote
favorite
I am using Mule 4 and Anypoint 7 and want to setup the database connector to SELECT all customers from my SQL server database table but if the customerName query parameter is populated in the request then I want to add the WHERE clause to only return customers with the same name as the customerName query parameter otherwise it should just return all customers.
My code is below but I am struggling to get the syntax correct.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d" config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql >SELECT TOP 10 * FROM MYDB.dbo.Customer $(if (attributes.queryParams.customerName != null and isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "")</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
How can I do this?
Thanks
mule anypoint-studio dataweave
add a comment |
up vote
0
down vote
favorite
I am using Mule 4 and Anypoint 7 and want to setup the database connector to SELECT all customers from my SQL server database table but if the customerName query parameter is populated in the request then I want to add the WHERE clause to only return customers with the same name as the customerName query parameter otherwise it should just return all customers.
My code is below but I am struggling to get the syntax correct.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d" config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql >SELECT TOP 10 * FROM MYDB.dbo.Customer $(if (attributes.queryParams.customerName != null and isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "")</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
How can I do this?
Thanks
mule anypoint-studio dataweave
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am using Mule 4 and Anypoint 7 and want to setup the database connector to SELECT all customers from my SQL server database table but if the customerName query parameter is populated in the request then I want to add the WHERE clause to only return customers with the same name as the customerName query parameter otherwise it should just return all customers.
My code is below but I am struggling to get the syntax correct.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d" config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql >SELECT TOP 10 * FROM MYDB.dbo.Customer $(if (attributes.queryParams.customerName != null and isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "")</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
How can I do this?
Thanks
mule anypoint-studio dataweave
I am using Mule 4 and Anypoint 7 and want to setup the database connector to SELECT all customers from my SQL server database table but if the customerName query parameter is populated in the request then I want to add the WHERE clause to only return customers with the same name as the customerName query parameter otherwise it should just return all customers.
My code is below but I am struggling to get the syntax correct.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d" config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql >SELECT TOP 10 * FROM MYDB.dbo.Customer $(if (attributes.queryParams.customerName != null and isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "")</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
How can I do this?
Thanks
mule anypoint-studio dataweave
mule anypoint-studio dataweave
edited Jul 19 at 10:30
asked Jul 18 at 9:33
user3165854
309935
309935
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You were on the right path. I think you were only missing the evaluation tags around the SQL in the db:sql element.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d"
config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql>#["SELECT TOP 10 * FROM MYDB.dbo.Customer
$(if (isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "") "]</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
It is easier to debug things like this with variables, so that you can see the individual values. FWIW heres my test code:
<set-variable variableName="additionalWhereClause"
value='#[if ( isEmpty(attributes.queryParams.email) == false)
"WHERE Email = :emailParm"
else "" ]' />
<set-variable variableName="selectSql"
value="#['SELECT FirstName, LastName, Email
FROM User
$( vars.additionalWhereClause )
ORDER BY Email LIMIT 10']" />
<logger level="INFO" message="queryParams: #[attributes.queryParams]" doc:id="96c62f84-2c98-4df6-829c-e00c9fcec9ca" />
<logger level="INFO" message="additionalWhereClause #[vars.additionalWhereClause]" doc:id="0d3611b4-34ae-4ebb-b931-6d31ce3804c1" />
<logger level="INFO" message="selectSql #[vars.selectSql]" doc:id="5c56342d-9674-4891-9d7e-bb32319f4ad0" />
<db:select doc:name="MySQL Query" doc:id="e60be3e6-9b51-4b3b-9dfa-4ee0af65cb03"
config-ref="mysql-config">
<ee:repeatable-file-store-iterable />
<db:sql>#[ vars.selectSql ]</db:sql>
<db:input-parameters><![CDATA[#[{'emailParm' : attributes.queryParams.email}]]]></db:input-parameters>
</db:select>
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You were on the right path. I think you were only missing the evaluation tags around the SQL in the db:sql element.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d"
config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql>#["SELECT TOP 10 * FROM MYDB.dbo.Customer
$(if (isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "") "]</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
It is easier to debug things like this with variables, so that you can see the individual values. FWIW heres my test code:
<set-variable variableName="additionalWhereClause"
value='#[if ( isEmpty(attributes.queryParams.email) == false)
"WHERE Email = :emailParm"
else "" ]' />
<set-variable variableName="selectSql"
value="#['SELECT FirstName, LastName, Email
FROM User
$( vars.additionalWhereClause )
ORDER BY Email LIMIT 10']" />
<logger level="INFO" message="queryParams: #[attributes.queryParams]" doc:id="96c62f84-2c98-4df6-829c-e00c9fcec9ca" />
<logger level="INFO" message="additionalWhereClause #[vars.additionalWhereClause]" doc:id="0d3611b4-34ae-4ebb-b931-6d31ce3804c1" />
<logger level="INFO" message="selectSql #[vars.selectSql]" doc:id="5c56342d-9674-4891-9d7e-bb32319f4ad0" />
<db:select doc:name="MySQL Query" doc:id="e60be3e6-9b51-4b3b-9dfa-4ee0af65cb03"
config-ref="mysql-config">
<ee:repeatable-file-store-iterable />
<db:sql>#[ vars.selectSql ]</db:sql>
<db:input-parameters><![CDATA[#[{'emailParm' : attributes.queryParams.email}]]]></db:input-parameters>
</db:select>
add a comment |
up vote
0
down vote
You were on the right path. I think you were only missing the evaluation tags around the SQL in the db:sql element.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d"
config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql>#["SELECT TOP 10 * FROM MYDB.dbo.Customer
$(if (isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "") "]</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
It is easier to debug things like this with variables, so that you can see the individual values. FWIW heres my test code:
<set-variable variableName="additionalWhereClause"
value='#[if ( isEmpty(attributes.queryParams.email) == false)
"WHERE Email = :emailParm"
else "" ]' />
<set-variable variableName="selectSql"
value="#['SELECT FirstName, LastName, Email
FROM User
$( vars.additionalWhereClause )
ORDER BY Email LIMIT 10']" />
<logger level="INFO" message="queryParams: #[attributes.queryParams]" doc:id="96c62f84-2c98-4df6-829c-e00c9fcec9ca" />
<logger level="INFO" message="additionalWhereClause #[vars.additionalWhereClause]" doc:id="0d3611b4-34ae-4ebb-b931-6d31ce3804c1" />
<logger level="INFO" message="selectSql #[vars.selectSql]" doc:id="5c56342d-9674-4891-9d7e-bb32319f4ad0" />
<db:select doc:name="MySQL Query" doc:id="e60be3e6-9b51-4b3b-9dfa-4ee0af65cb03"
config-ref="mysql-config">
<ee:repeatable-file-store-iterable />
<db:sql>#[ vars.selectSql ]</db:sql>
<db:input-parameters><![CDATA[#[{'emailParm' : attributes.queryParams.email}]]]></db:input-parameters>
</db:select>
add a comment |
up vote
0
down vote
up vote
0
down vote
You were on the right path. I think you were only missing the evaluation tags around the SQL in the db:sql element.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d"
config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql>#["SELECT TOP 10 * FROM MYDB.dbo.Customer
$(if (isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "") "]</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
It is easier to debug things like this with variables, so that you can see the individual values. FWIW heres my test code:
<set-variable variableName="additionalWhereClause"
value='#[if ( isEmpty(attributes.queryParams.email) == false)
"WHERE Email = :emailParm"
else "" ]' />
<set-variable variableName="selectSql"
value="#['SELECT FirstName, LastName, Email
FROM User
$( vars.additionalWhereClause )
ORDER BY Email LIMIT 10']" />
<logger level="INFO" message="queryParams: #[attributes.queryParams]" doc:id="96c62f84-2c98-4df6-829c-e00c9fcec9ca" />
<logger level="INFO" message="additionalWhereClause #[vars.additionalWhereClause]" doc:id="0d3611b4-34ae-4ebb-b931-6d31ce3804c1" />
<logger level="INFO" message="selectSql #[vars.selectSql]" doc:id="5c56342d-9674-4891-9d7e-bb32319f4ad0" />
<db:select doc:name="MySQL Query" doc:id="e60be3e6-9b51-4b3b-9dfa-4ee0af65cb03"
config-ref="mysql-config">
<ee:repeatable-file-store-iterable />
<db:sql>#[ vars.selectSql ]</db:sql>
<db:input-parameters><![CDATA[#[{'emailParm' : attributes.queryParams.email}]]]></db:input-parameters>
</db:select>
You were on the right path. I think you were only missing the evaluation tags around the SQL in the db:sql element.
<db:select doc:name="Select Customers" doc:id="98a4aa2f-b0b6-4fb5-ab27-d70489fd532d"
config-ref="db-config">
<ee:repeatable-file-store-iterable />
<db:sql>#["SELECT TOP 10 * FROM MYDB.dbo.Customer
$(if (isEmpty(attributes.queryParams.customerName) == false) "WHERE Name = :customerName" else "") "]</db:sql>
<db:input-parameters ><![CDATA[#[{'customerName' : attributes.queryParams.customerName}]]]></db:input-parameters>
</db:select>
It is easier to debug things like this with variables, so that you can see the individual values. FWIW heres my test code:
<set-variable variableName="additionalWhereClause"
value='#[if ( isEmpty(attributes.queryParams.email) == false)
"WHERE Email = :emailParm"
else "" ]' />
<set-variable variableName="selectSql"
value="#['SELECT FirstName, LastName, Email
FROM User
$( vars.additionalWhereClause )
ORDER BY Email LIMIT 10']" />
<logger level="INFO" message="queryParams: #[attributes.queryParams]" doc:id="96c62f84-2c98-4df6-829c-e00c9fcec9ca" />
<logger level="INFO" message="additionalWhereClause #[vars.additionalWhereClause]" doc:id="0d3611b4-34ae-4ebb-b931-6d31ce3804c1" />
<logger level="INFO" message="selectSql #[vars.selectSql]" doc:id="5c56342d-9674-4891-9d7e-bb32319f4ad0" />
<db:select doc:name="MySQL Query" doc:id="e60be3e6-9b51-4b3b-9dfa-4ee0af65cb03"
config-ref="mysql-config">
<ee:repeatable-file-store-iterable />
<db:sql>#[ vars.selectSql ]</db:sql>
<db:input-parameters><![CDATA[#[{'emailParm' : attributes.queryParams.email}]]]></db:input-parameters>
</db:select>
answered Nov 10 at 19:18
A Mack
60455
60455
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51398528%2fdynamic-select-query-which-decides-whether-to-use-the-where-clause-in-mule-4%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown