My sql stored procedures

OL Connect Professional Workflow
1

Hi there

I have a workflow which uses mysql a fair bit for the data work. I have combined queries into stored procedures and call them with the “database query” ie call tablename.loadprocess();

this seems to work, is there an issue doing this? will the procedure run completely before returning control back to WF or does it start the procedure and carry on the WF?

part of the routine truncates 4 tables and inserts data from the main file into them, later in the flow I’m running more database queries to select the data from these temp tables

so my problem is while debugging the WF everything works fine, when compiled and running in production mode it doesn’t always grab all the data from the 4 files (tells me the query produced no records)

i’ve now split the process into 2, process 1 does all the data work & when finished it creates a trigger text file to kick off the second part which only selects data from the temp tables and writes the data to CSV files, again, I don’t get all the files in production mode but i do in debug.

Because the 2nd part is simply selecting data in temp tables after it’s all done I can drop the trigger file back in the input directory to run the process again & it runs fine, producing all the files I’m expecting… I’m beginning to run out of ideas so I thought I’d ask the question

Thanks, Richard

2

Hi @richardTabram,

Can you please share more detailed information about the two Workflow processes? Can you for example share some screenshots of the workflow so that we can get a better understanding of which Workflow plugins the Workflow processes consist of?

I have a workflow which uses mysql a fair bit for the data work. I have combined queries into stored procedures and call them with the “database query” ie call tablename.loadprocess();

Are you executing these “database queries” by a Run Script Workflow plugin or by another Workflow plugin?

this seems to work, is there an issue doing this? will the procedure run completely before returning control back to WF or does it start the procedure and carry on the WF?

I am afraid that we cannot answer these questions without knowing how the two Workflow processes are set up.

so my problem is while debugging the WF everything works fine, when compiled and running in production mode it doesn’t always grab all the data from the 4 files (tells me the query produced no records)

I assume that by “from the 4 files” you are referring to the input files of the Workflow process(es), is that correct?

3

Hi Martin

Thanks for the reply, i’m happy to share the WF if that would help… ill try not to take up 2 much space in this message…

my source file comes from Shopify, it’s a CSV file where each line is a product, 6 products in total so a max 6 entries for any one order. the first record of each order holds the name, address etc and product 1, following entries just have the order no & product. I am manually loading this into mySQL and this is the starting point for PP.

im still writing it, currently, its started by a trigger file (empty text file)

image
image1055×721 26.9 KB

image
image1202×728 32 KB

image
image1199×715 28.6 KB

as you can see, all the database work is done using "database query " objects.
no4 query = call database.loadprocess();

loadprocess in my sql looks like :

update start set gcode = randString(16) where pk > 0 and isnull(status);
update start set gcode = concat_WS(“-”,trim(gcode),pk) where pk > 0 and isnull(status);
update start set loaddate = curdate() where pk > 0 and isnull(status);

INSERT INTO client.mhmaster
(Name,Email,Shipping Name,Shipping Street,Shipping Address1,Shipping Address2,Shipping Company,Shipping City,
Shipping Zip,Shipping Province,Shipping Country,Shipping Phone,PK,gcode,deliverymethod,status,working,loaddate)
select Name,Email,Shipping Name,Shipping Street,Shipping Address1,Shipping Address2,Shipping Company,Shipping City,
Shipping Zip,Shipping Province,Shipping Country,Shipping Phone,PK,gcode,deliverymethod,status,working,loaddate from start
where pk in (select min(pk) from client.start group by Name) and isnull(start.status);

insert into mhorder select * from start where isnull(start.status);
update start set start.status = 1 where pk > 0 and isnull(status);
update start set start.working = 0 where pk > 0 ;
call client.updatepacksize();

so 4 just runs queries and a couple of function calls
8 = simple select command, the result is a csv file used by 12 & 13 (paf cleans the data)
16 truncates a table ready for new data
17 captures the clean data from 13
18 - 21 runs through the clean data inserting into a table
25 normal select query
27, based on what, how many and how large the pack will be picks between Courier, NZ Post, DX

after all this, 29 selects data from the main table into 4 tables, courier,NZP,DX and a email file where we plan to email the receipent and direct them to a web page to update their address
the last part you cant see just creates a trigger file to kick off process 2

Process 2:

image
image752×522 18.2 KB

pretty much 4 x this, the query is simply
select * from client.nzp;

I split it here as the second part just grabs the data in the 4 tables and saves them as CSV files, when run, I am the NZP file missing. As I can drop the second trigger file again (it just grabs data from mySQL) when I do, I get all the files im expecting (i can do this till im blue in the face, correct ererytime, its just the first time that it doesnt work)

I’m still working on it… As I said, it’s correct every time in debug, just fails in production (unless i run it twice)

any help would be appreciated

thanks, Richard

4

apologies for misspelling your name Marten, sorry, the old eyes are getting tired.

5

Hi @richardTabram,

apologies for misspelling your name Marten, sorry, the old eyes are getting tired.

Don’t worry, it may happen.

Can you let me know please if both Workflow process 1 and Workflow process 2 are picking up the same kind of input data from the same input folder?

In your original forum post you let us know the following:

so my problem is while debugging the WF everything works fine, when compiled and running in production mode it doesn’t always grab all the data from the 4 files (tells me the query produced no records)

I assume that this issue does occur in the Branch on line two of Workflow process 2, is that correct?

6

yes, it seems like the first query in process 2 has the problem… i even copied the NZP branch and pasted it back in between the others and at the end - made no difference… it must be something ive missed somewhere BUT what I don’t understand is how I run process2 a second time and it works correctly from there…

There is no input data for either, process 1 is started by a trigger file (right click and create a new text file) at the end of this a new trigger file is created to start process 2 (these do start in the same folder), again all process 2 does is run 4 queries (all identical, just different tables) the selectec data is saved as CSV (these will go on to be the source files for the print production). All the data comes from a msql database

and again, when I run in debug it works correctly just the first production run that fails…

thanks for looking at this, I appreciate its not easy to solve as you only have this chat to go by

im happy to send the flow to you if that would help

7

sorry, thought i was onto something, i removed this as its not correct

8

just testing… i have set all the queries in process 2 to use the same data table it all seems to work…
(well i get 4 files so each query is working, and all queries return data)

I have all my query objects set to “expect record set” but not all will return data (the 2 source files i’m testing, each returns 3 of the 4) could this be something to do with the “expect record set” ticked and no data is returned? (if i don’t have it ticked it doesn’t return data)

9

i’ve sent this to the PP team to look at, ill post the answer when i have it, thanks

1 Like