Question

Use Token to Build SQL Query (not parameters) for Custom Query Activity

Workflow

Updated February 13, 2014

asked on February 12, 2014

In Workflow 9.1 I have a document that needs to query a SQL database using various queries depending on different template values. I've created a routing decision for the 10+ different scenarios, custom query activities for each of the queries, conditional statements to error check each completed query, and then assign activities to update template fields based on what is returned. This has created quite a bit of work as the columns the queries return are the same for all, only the syntax used to obtain them is different.

Assuming the queries return the same columns from the SELECT portion and use the same data source, what I would like is to be able to build the SQL query as a token, lets call the token %(tkQuery) for this, and then pass the final %(tkQuery) into the Custom Query activity as the entire query syntax. Then I would need to only setup the Custom Query activity, the error checking conditional statement, and the assign activity once for all current and future scenarios.

I've tried to put only a parameter in the entry box and then assign %(tkQuery) to that but it puts '' around it at runtime and thus SQL fails because of incorrect format.

I've tried various types of parameters and understand why what I am trying to accomplish fails with the way this activity currently functions.

Additionally, the custom query activity does not provide an obvious way to pass a token directly into the Custom Query entry box. I've found that if I

put in a valid SQL query with all columns I need returned (assuming they are the same for all variations of the query token),
run the test on the activity so it populates the query's column output tokens,
and then replace the entire query with %(tkQuery),

that it will let me use a token as my query at runtime. Obviously I can't test the custom query activity this way but at least it works at runtime for my issue.

I also know I can write my own custom script but I would prefer to use out of the box tools to accomplish this. I understand this may be a niche need but has anyone found a better way to pass a dynamically built SQL query token into the Custom Query Activity? For Laserfiche, is there any plan to easily allow tokens to be used directly within a SQL query, without the need to pass them in as parameters?

0 0

Answer

SELECTED ANSWER

replied on February 12, 2014

The activity will not let you inject SQL queries through parameters. But it will still resolve tokens. So you can build the query as a token and use that the way you mentioned it above.

4 0

replied on February 12, 2014 • Show version history

I don't mind using this method but it does not appear to be designed to work this way since there is no built-in button or right-click to add tokens to the query and since the test for the activity fails. Any chance in the future to allow it to use tokens to build the query inherently within the activity?

0 0

replied on February 12, 2014

This isn't necessarily what you'll be using it for, but I wanted to extend one caution. Being able to piece together a SQL query from strings without proper parameterization (which the Custom Query tool does) is opening up SQL injection vulnerabilities. It may not be an issue for internal users, but if any of the tokens were coming from a public facing Forms instance there could trouble.

2 0

replied on February 12, 2014

I definitely agree with you there and appreciate that you clarified that. In this case the template fields I am pulling from are numeric only and I always verify any template fields used in a query with regular expressions to verify their accuracy. Great idea though to mention that before someone goes down the wrong path and opens up a huge vulnerability.

0 0

Replies

replied on February 12, 2014

Beau,

The approach I was trying to take was something like this;

SELECT * FROM table WHERE field1 LIKE ? AND field2 LIKE ?

and then tokenize the two parameters. You could then pass a valid token to the first parameter and pass an '%' to the second parameter and get all of the records matching your first field. Or you could pass valid values to both parameters and get your second field included in the WHERE clause.

The theory falls apart with the OR statement though as I can't get the syntax correct to include that third parameter. And, as you know, the LIKE clause will include any substring matches. (Which is not good).

My first and most often fallback is the script activity. More often than not I can code up a script faster than I can work out an issue with a workflow.

But then to a developer everything is easier when you code it from scratch!

1 0

replied on February 12, 2014

I do remember the days of having to write any database lookups with the Script (VB.NET) activity and it was nice to be able to customize everything through there. For this project I need to use out-of-the-box activities to allow non-coders to maintain/update as needed. But yes, I do like writing from scratch so it does exactly what I want. Thanks for working to find an answer!

0 0

replied on February 12, 2014

Beau - Quick question; what differentiates the 10 different queries? My guess is that you are doing some type of 'WHERE' clause on the query to differentiate them. If so, is it a single field that is contained in the WHERE clause or several?

Without being in front of the Workflow Designer I would say that you should be able to build your standard SELECT query and then put the parameters (tokens) in the WHERE clause. If you can provide specific examples I am willing to help with the syntax.

0 0

replied on February 12, 2014

The Custom Query activity does not inherently allow you to use tokens in the query directly. Additionally, it does not let you successfully pass column names as parameters, only the values of those columns can be passed as parameters.

This query returns a single row as it should
This same query passing the column name as a parameter returns 0 rows

It appears this activity treats parameters as it should in a SQL query by only allowing them to only replace a comparison value and not an actual portion of the statement.

As an example, let's say I have a table (tblA) with 6 columns (colA, colB, colC, colD, colE, & colF) and I have 3 separate queries that need to pull the same columns but with different queries.

SELECT * FROM tblA WHERE colD='12345'
SELECT * FROM tblA WHERE colD='12345' AND colE='789'
SELECT * FROM tblA WHERE colD='12345' OR colF='abc'

In this scenario I would have to build 3 separate custom query activities to use Workflow in the way it is designed. With the method I mentioned above I would

create a token to house the SQL query
build the SQL query in the token using routing decisions/conditional sequences
create a single custom query activity
set the query in that activity to SELECT * FROM tblA
use the test portion of the activity to populate the output column tokens
replace the SELECT statement in the activity with my query token
it wont test successfully in the activity but it will work correctly at runtime

I am looking to see if anyone has found a better way to dynamically build a query (joins, from different tables, different where values, ...) assuming all queries return the same columns and use the same datasource.

0 0

replied on February 12, 2014

Ok, beating this to death. How about this;

using these tokens

So for your examples;

SELECT * FROM tblA WHERE colD='12345'
SELECT * FROM tblA WHERE colD='12345' AND colE='789'
SELECT * FROM tblA WHERE colD='12345' OR colF='abc'

For query 1 set token 'colD' to 12345, token 'colE' to an empty string, and token 'colF' to an empty string

For query 2 set token 'colD' to 12345, token 'colE' to 789, and token 'colF' to an empty string

For query 3 set token 'colD' to 12345, token 'colE' to an empty string, and token 'colF' to abc

I don't have a good database to test this against so I am making some assumptions...

0 0

replied on February 13, 2014

I like your enthusiasm and appreciate your desire to provide solutions. Your method is a great way to handle my example however my example over-simplified my situation. The queries I am running actually query many different tables, use joins, and have unique column names depending on which query the document qualifies for. I am writing the queries to return the unique column names as static alias's to allow them to be returned consistently as the same token name by the Custom Query script. I think I will stick to using routing decisions for each query just to keep from having customization to support. Thanks again.

0 0

You are not allowed to follow up in this post.

Question

Question

Use Token to Build SQL Query (not parameters) for Custom Query Activity

Answer

Replies

Sign in to reply to this post.