SQL Query works in the statement editor, but not when finished

Post Reply
User avatar
billy.olvestad
Member
Posts: 51
Joined: Mon Nov 19, 2018 10:08 pm
Location: Sweden
Contact:

SQL Query works in the statement editor, but not when finished

Post by billy.olvestad »

I am trying to add a value from an SQL database as private data.

I have an SQL Query that works as long as I am inside the statement builder ("Build SQL statement"-window), but when I finish and insert, the double quotation marks are confused by switch to mean the end of the SQL statement text.

I need the double quotation marks in the SQL statement though, because the columns in the database have spaces and dashes in their names (like for instance "Sell-To Name", that has both).

Is there a way to escape the double quotation marks in the SQL query text so that switch doesn't interpret them as an end to the "Database.Text:SQL"-variable?

This is the SQL Query that works in the statement builder, if I click "Validate statement":

Code: Select all

SELECT "Sell-To Name" FROM "åtta_45$PV Case" WHERE "Order No_" = '[Metadata.Text:Path="//field[tag='Ordernummer']/value",Dataset="Submit",Model="XML"]'
When it is inserted into the database variable however, I get errors related to the double quotation marks. This is the code that doesn't work:

Code: Select all

[Database.Text:SQL="SELECT "Sell-To Name" FROM "åtta_45$PV Case" WHERE "Order No_" = '[Metadata.Text:Path="//field[tag='Ordernummer']/value",Dataset="Submit",Model="XML"]'",Connection="printvis"]
mkayyyy
Member
Posts: 75
Joined: Mon Nov 21, 2016 6:31 pm
Location: UK

Re: SQL Query works in the statement editor, but not when finished

Post by mkayyyy »

You could define the SQL statement as private data
Image

Then just pass the private data variable into the database variable and that should work
Image
User avatar
billy.olvestad
Member
Posts: 51
Joined: Mon Nov 19, 2018 10:08 pm
Location: Sweden
Contact:

Re: SQL Query works in the statement editor, but not when finished

Post by billy.olvestad »

Hey! That's genious!
Why didn't I think of that?! :D

Thank you! I will be trying that tomorrow.
User avatar
billy.olvestad
Member
Posts: 51
Joined: Mon Nov 19, 2018 10:08 pm
Location: Sweden
Contact:

Re: SQL Query works in the statement editor, but not when finished

Post by billy.olvestad »

Sadly, that didn't work either.

I now have a privatedata variable named "queryKundnamn" that in my example evaluates to:

Code: Select all

SELECT "Sell-To Name" FROM "åtta_45$PV Case" WHERE "Order No_" = 'PO204331'
I then insert it to another privatedata variable:

Code: Select all

kundnamn=[Database.Text:SQL="[Job.PrivateData:Key="queryKundnamn"]",Connection="printvis"]
I now get the error:

Code: Select all

Error in line 1 of script Database.Text: Parse Error: expecting `error' or `'_'' or `';''
If I try the Query builder again with the string pasted, it works:
works.png
works.png (101.88 KiB) Viewed 5857 times
If I try inserting the variable "queryKundnamn" into the editor, which evaluates to the same string, it doesn't work:
error.png
error.png (159.41 KiB) Viewed 5857 times
Padawan
Advanced member
Posts: 358
Joined: Mon Jun 12, 2017 8:48 pm
Location: Belgium
Contact:

Re: SQL Query works in the statement editor, but not when finished

Post by Padawan »

Can you try this?

kundnamn=[Database.Text:SQL="[Job.PrivateData:Key='queryKundnamn']",Connection="printvis"]

I've changed the double quotes around the private data key to single quotes.
User avatar
billy.olvestad
Member
Posts: 51
Joined: Mon Nov 19, 2018 10:08 pm
Location: Sweden
Contact:

Re: SQL Query works in the statement editor, but not when finished

Post by billy.olvestad »

I tried it, but I got the same error message.

I have now solved it with scripting instead. Seems like in the script interface it works just fine.

Thanks for your help.
Post Reply