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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

What do you mean Volker ?

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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