One Star

SQL BUILDER - How can I include a context variable in the WHERE clause

Hi:
I want to use in an sql query a value prompted in the context. (I'm using an INFORMIX database).
I've created the variable called "myvariable" . When I run the job, it prompts for a value, just as I want.
When I want to include it in my query, I type Ctrl+SpaceBar and I can select my variable from a list (it was called "context.myvariable".
The query appears so:
"SELECT * FROM mytable
WHERE column1 = context.myvariable"
The system rejects my code saying "COLUMN context IS NOT FOUND IN ANY TABLE IN THE QUERY".
Please help me to mend my syntax.
Thank you very much in advance.
Juan Carlos.
21 REPLIES
Employee

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hello,
The correct syntax is "SELECT * FROM mytable WHERE column1 = "+context.myvariable
Regards,
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Thanks to mhirt for your answer.
I tried to use it, but it didn't work.
The variable context.PROVINCIA contained "JUJUY" (without the "").

This is my real query:
"SELECT provincias.codigo, provincias.nombre 
FROM provincias where provincias.nombre = "+context.PROVINCIA

And the program crashed with this message (the same problem as before):
Starting job Prueba3 at 14:08 17/03/2008.
Exception in component tInformixInput_2
java.sql.SQLException: Column (jujuy) not found in any table in the query (or SLV is undefined).
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3240)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3553)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2380)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2295)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1461)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1434)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:205)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
at prueba2.prueba3.Prueba3.tInformixInput_2Process(Prueba3.java:254)
at prueba2.prueba3.Prueba3.runJobInTOS(Prueba3.java:511)
at prueba2.prueba3.Prueba3.main(Prueba3.java:428)
Caused by: java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:397)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3558)
... 10 more
Job Prueba3 ended at 14:08 17/03/2008.

----------------
Please help me. Regards.
Employee

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hello,
Little update :
"SELECT * FROM mytable WHERE column1 = '"+context.myvariable + "'"
or
"SELECT * FROM mytable WHERE column1 LIKE '"+context.myvariable + "'"
HTH,
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

By the way: Is there a planned support for static SQL?
Employee

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

What do you mean Volker ?
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hello Michaël ,
actual the tXXXComponents uses dynamic sql (please correct me if I'm wrong). Maybe the tXXXOutput components are using static sql.
Based on a tXXXRow: In the component I would define my sql-string, all parameters / values are replaced by (db-specific) placeholders. There should be a table with the mapping between the placeholders and the row attributes . The sql will be prepared in the database and for each row executed.
Benefits: less overhead for the database and higher security (avoiding code injection).
Bye
Volker
Employee

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Based on a tXXXRow: In the component I would define my sql-string, all parameters / values are replaced by (db-specific) placeholders. There should be a table with the mapping between the placeholders and the row attributes . The sql will be prepared in the database and for each row executed.

This feature is present in Perl tMysqlOutput. Volker, I suggest you to create a feature request in the bugtracker so that you also have it in the Java component.
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Thanks for the fast answer. I personally do not need the prepared statement because I don't have many rows to handle and no security requirements. But It crosses my mind if I read the thread and I think it could be interesting for the "power user" (depending on the data to move).
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Thanks to Michel Hirt for your answer. I'll try it now.
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Thank you very much. It worked fine.
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

how can i use the below query in a proper...........when i use this it shows "connect to db error or in sql string"

"update paraminput set ProcessTag=1 where U_Id= "+context.myvariable
Employee

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hi
Did you create a context variable named myvariable in the context view (or context repository)?
Best regards;
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hi,
I have read this thread (and some others), but unfortunately are still not able to use a global variable within the SQL Builder.
I use Perl and PostgreSQL, and have the following query I would like to execute:
SELECT *
FROM adm.job_instance
WHERE job_id=.$_context{domain}

domain is a contextvariabel with the same type as job_id (being integer).
I have tried all the possible options as mentioned above, but simply can't get it to work.
Help would be appreciated.
Kind regards,
Michel
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

SELECT *
FROM adm.job_instance
WHERE job_id=.$_context{domain}

Do you have quotes around select statement?
'SELECT *
FROM adm.job_instance
WHERE job_id='.$_context{domain}
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hi Timson,
It works, thanks!
It looks like I was under the assumtion I did something wrong since I am not able to enter the SQL Builder when a context variable is part of my SQL statement. WHen there is a context variable in my SQL statement, and I try to enter the SQL builder I get the following message: Query Statement Error!. The details of the error are:
This query can not contain context parameters (like $_context) ,
or this query has not been enclosed by '.
or has some expressions. Please correct.
This made me think something was wrong.
Anyhow, many thanks.
Kind regards,
Michel
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hello:
If I write this sentence in the window "SQL Builder", I find an error.
I have defined Factura like context.
SELECT "public"."c_bpartner"."c_bpartner_id" FROM "public"."c_bpartner"
where c_bpartner_id in (+context.Factura)
group by "c_bpartner_id"

"ERROR: Falta una tabla para la entrada para la tabla context en la clausula FROM"
It seems that the program tray to reead context like a table. How to include in the sentence the content of the variable Factura?
Thanks
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

databases typically treat anything not in single quotes to be object names, such as column names, table names, etc. depending on where in the query you are. double quotes are also treated as object names.
a recurring issue (potentially) in all of the examples above are that quotes are not being used in the proper place. when referring to column names being generated from a context variable, the result should always be placed in quotes, unless it is a numeric value of course. not using a quote in your example makes the database think it is expecting the name of a table in the brackets, if u place this in single quotes instead, the database will then expect a value instead.
i think this makes sense?
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hello
Thanks for the answer. Problem was in the quote, certainly.
Another question. How can assign directly a group of values to a variable of context?
For example, I need sendto context some values of a camp from DB, in order to iterate after with this.
Thanks
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Ok I have read this full thread several time but same as the others there are so many variation that I am completely lost.
Here is my query (which does not work)
"select * WHERE full_path like '" + ((String)globalMap.get("tFileList_2_CURRENT_FILEPATH")) + "'"
Which does not work because of the " in the get function.
So I thought to escape the " like this
"select * WHERE full_path like '" + ((String)globalMap.get(\"tFileList_2_CURRENT_FILEPATH\")) + "'"
also a fail so how about this
"select * WHERE full_path like \"" + ((String)globalMap.get(\"tFileList_2_CURRENT_FILEPATH\")) + "\""
fail
"select * WHERE full_path like \"" + ((String)globalMap.get("tFileList_2_CURRENT_FILEPATH")) + "\""
fail
From the thread is appears clear that the issue is with the quote but which one.....
This is driving me mad, any suggestions
 
One Star

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

By the way I do know that the From is missing that is because it was very long
Highlighted
Moderator

Re: SQL BUILDER - How can I include a context variable in the WHERE clause

Hi chrisBolduc,
Can you make it success if you use the value directly without variable in query?
Would you mind elaborating your case with an example with input and expected output values?
Best regards
Sabrina

 
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.