Question

DB2 variable declaration in workflow

Workflow Laserfiche Version 10 Administration How To

Updated July 25, 2016

asked on July 21, 2016

Hi,

I'm trying to see what would be different in the Workflow Custom Query activity when declaring Variables to use in a Query against a DB2 database. If I test my query using no Variables and type each value, I get the expected result but when I try and make use of a Variable, then I get the below error:

Normal Query including static values:

SELECT * FROM LIVDTA.AAAXCPP 
WHERE AXTGT3 ='Document' 
AND AXLFCE = '123456'

Query using a Replacement value for one of the fields:

SELECT * FROM LIVDTA.AAAXCPP 
WHERE AXTGT3 ='Document' 
AND AXLFCE = @M

Screenshot of Query config in WF:

Note that I tried with putting '' around the values under "Parameter Values" and without

Screenshot of the error:

If I put '' around the Variable in the query ie '@M' then the query runs but does not return a result which it is supposed to.

Any suggestions?

0 0

Replies

replied on July 21, 2016

Based on the screenshot, it looks like you're using an ODBC driver for DB2? ODBC drivers don't allow for naming parameters. They have to all be "?" and their values are used based on the order they're specified.

0 0

replied on July 21, 2016

Hi Miruna,

thank you for the feedback. for my understanding, when you say that "ODBC drivers don't allow for naming parameters" is this specific to the DB2 ODBC driver?

0 0

replied on July 22, 2016

I meant all ODBC drivers, not specific to Laserfiche's use of ODBC. This issue is document in Workflow here.

0 0

replied on July 22, 2016 • Show version history

Miruna,

I think I figured out what you explained and got it to work in the initial Sample sent above. If one has to provide them in the order of how they are used, how does this effect Nested queries using the same variable in multiple places in the query?

Consider the following query:

BEGIN ATOMIC
IF (EXISTS(SELECT * FROM LIVDTA.AAAXCPP WHERE AXTGT3 ='MVA FOLDER' AND AXLFCE = @Mva))
THEN 
UPDATE LIVDTA.AAAXCPP
SET AXUMN4 = @FolderID
WHERE AXTGT3 ='MVA FOLDER' AND AXLFCE = @Mva;

ELSE

INSERT INTO  LIVDTA.AAAXCPP
(AXTGT3, AXLFCE, AXUMN4, AXUSR, AXWISS, AXTRT3, AXTST3, AXTTT3)
VALUES
('MVA FOLDER', @Mva, @FolderID, 'SCAN', '5', @User, @Date, @Time);
END IF;
END

Does it mean that in the above query, I would replace all the "@name" variables with "?" and then for a variable like "@Mva" I would in the Parameter Configuration have 3 separate "?" lines being stipulated?

So for this query I would have a total of 8 "?" Parameter lines configured?

0 0

replied on July 22, 2016

Miruna, based on my understanding of what you explained I did the following change and tested it with the full query:

However, this did not work and gave the following error:

If I go back to just a normal Select query with only one variable that is referenced by a "?" then it works and a result is returned.

0 0

replied on July 22, 2016

As far as the other error you're getting, it's a driver error, so it's not something we can troubleshoot. Quick Google search seems to indicate a possible bug in the driver, but you'd probably get a better answer on IBM's forums.

http://www-01.ibm.com/support/docview.wss?uid=nas2d92a05533068719c86257ab00041fc86
ftp://ftp.software.ibm.com/as400/products/clientaccess/win32/v5r4m0/servicepack/si24893/readmesp.txt

That said, in the screenshot above, you have AXUMN4 twice with different values (position 2 and 5 in the parameter list). Is that intended? Is it possibly triggering some constraint violation on the table?

0 0

replied on July 25, 2016

Hi Miruna,

Thank you for the feedback and links. I'll do some more digging and see if I can get to the bottom of the issue. To answer you regarding the the different values, yes, it is intended for my test so that I could see the Update work. Running the SQL script without any variables works, it is only when the variable element is introduced that it then generates the error.

0 0

You are not allowed to follow up in this post.

Question

Question

DB2 variable declaration in workflow

Replies

Sign in to reply to this post.