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

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

Anonymous — Sat, 16 Nov 2024 14:25:44 GMT

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.

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

Anonymous — Fri, 14 Mar 2008 20:08:05 GMT

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

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

Anonymous — Mon, 17 Mar 2008 14:21:54 GMT

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.

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

Anonymous — Mon, 17 Mar 2008 20:52:40 GMT

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

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

Anonymous — Mon, 17 Mar 2008 21:39:11 GMT

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

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

Anonymous — Mon, 17 Mar 2008 21:44:14 GMT

What do you mean Volker ?

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

Anonymous — Mon, 17 Mar 2008 22:01:58 GMT

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

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

Anonymous — Mon, 17 Mar 2008 22:42:42 GMT

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.

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

Anonymous — Mon, 17 Mar 2008 22:54:35 GMT

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).

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

Anonymous — Tue, 18 Mar 2008 12:00:50 GMT

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

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

Anonymous — Tue, 18 Mar 2008 12:09:45 GMT

Thank you very much. It worked fine.

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

Anonymous — Mon, 29 Dec 2008 07:42:18 GMT

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

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

Anonymous — Mon, 29 Dec 2008 10:02:36 GMT

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

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

_AnonymousUser — Wed, 25 Feb 2009 16:08:07 GMT

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

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

Anonymous — Wed, 25 Feb 2009 16:27:10 GMT

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}

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

_AnonymousUser — Wed, 25 Feb 2009 19:37:44 GMT

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

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

_AnonymousUser — Wed, 03 Jun 2009 10:22:54 GMT

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

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

Anonymous — Wed, 03 Jun 2009 12:09:51 GMT

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?

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

_AnonymousUser — Wed, 03 Jun 2009 15:37:28 GMT

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

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

_AnonymousUser — Wed, 17 Feb 2016 20:55:50 GMT

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