You are viewing limited content. For full access, please sign in.

Discussion

Discussion

Incorrect Syntax in custom query WF activity

Workflow Version 11 Integrations How To
Updated May 16
posted on May 14

Hello Everyone, 

I can't get this custom query to work and I'm calling for help. I believe it's an issue with WF adding quotes to my parameter. I have tried almost every datatype I can it just keeps giving me an error. It's a really simple query that is a workaround I found to try and import more than 1000 records to a file. 

Here is my query in WF. 

INSERT INTO SaleNameNumber(division, saleNumber,saleName)
@D

it is a direct connection to a Azure DB and it works if I use hard-coded values that I'm trying to pass. For example, this works.

INSERT INTO SaleNameNumber(division, saleNumber,saleName)
SELECT 1,1154,'DM DF I' UNION ALL
SELECT 1,1155,'GM WF I' UNION ALL
SELECT 1,1157,'GM DF I' UNION ALL
SELECT 1,1158,'DM WF I' UNION ALL
SELECT 1,1159,'DM PP I' UNION ALL
SELECT 1,1161,'GM SP I'

My parameter is a multivalue token I'm building that I use an index on with separating the values with a space. 

Even if I pass it only 1 value, I keep getting this. 

To me, it looks like WF is adding the ' on the outside of my token which is changing what I want to run. Anyone have any ideas on how to get my parameter in there without the quotes? 

 

0 0
replied on May 14

You can pass dynamic values, but you can't pass in dynamic subqueries/parameters; basically, it has to be something SQL could recognized as-is without any "injected" pieces.

The tokens are treated as variables not plain text, and for good reason; you wouldn't want it to break because you had the word "select" or worse "delete" in part of a text value.

You'll need to find another approach. Based on your query, I think it would be easier to just use the built-in Insert activity in a loop to add each row of data, or I suppose you could try to write a Stored Procedure in SQL that could accept a list of values to insert.

2 0
replied on May 16

I get what you're saying about LF trying to make things easy and reduce the change of error. I was able to do the SQL statement I was trying eventually. With the help from this post, 
https://answers.laserfiche.com/questions/135532/Quickest-way-to-insert-multiple-rows-into-a-database-table.

I did make some changes to my original statement however, I was able to insert 1000 records at a time, which is what the goal was. I needed this solution because running a workflow and doing an insert as many times as I needed would make the workflow run for a couple hours. This now runs closer to 30 minutes. This is my custom query used in the workflow. 

DECLARE @SQL AS VARCHAR(max)

SET @SQL = 'INSERT INTO SaleNameNumber(division,saleNumber,saleName) 
                        VALUES' + @Values

EXEC(@SQL)

My @Values parameter is a multi-valued token to which I apply an Index with the token format, and I use the "All values separated by Comma".
This is an example of the data used in my Values parameter. 
(1,1154,'DM DF I')
(1,1155,'DM DF I')
(1,1157,'DM DF I')
(1,1158,'DM DF I')
(1,1159,'DM DF I')
Since the Token Editor adds commas, the statement runs perfectly. 

Thanks for the help. 

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...