topic Re: Context variables in Talend Studio

Context variables

vincent5 — Mon, 03 Dec 2018 06:51:07 GMT

Hi,

I'm using Talend V6.4.1 Integration with an Informix database.
I would like to concatenate 2 context variables in a tELTInformixMap.
For example :
My first variable is equal to 'CM0' (context.CM0)
My second variable is equal to '87' (context.CAISSE)
I would like to get 'CM087' to insert it in an informix table.
I tried all kinds of writing but none give me the expected result.
The last try is the following script: 'context.CM0 + context.CAISSE'
Which gives as result: 'CM0+87' (incorrect)
Thanks for your help.

Re: Context variables

Anonymous — Mon, 03 Dec 2018 06:55:52 GMT

Hi,

Please remove single quotes. Just try context.CM0 + context.CAISSE

Since it is a variable, you should not add any quotes.

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: Context variables

manodwhb — Mon, 03 Dec 2018 06:57:17 GMT

@vincent,if you were using a tmap in your data flow ,you can use in tmap below way.in tjavarow also you can do .

Re: Context variables

vincent5 — Mon, 03 Dec 2018 07:04:50 GMT

Hi,

The job crashes with this message:

java.sql.SQLException: Column (cm0) not found in any table in the query (or SLV is undefined).

Regards,

Vincent

Re: Context variables

Anonymous — Mon, 03 Dec 2018 07:05:27 GMT

Hi,

Keep in mind that when you're using ELT components you're basically writing SQL code instead of Java.

You'll need to use the CONCAT(expr1,expr2) function as defined in informix SQL. In your example :

CONCAT(context.CM0,context.CAISSE)

The mapper will automatically take care of escaping your context variables.

Let me know it this works

Kind regards,

Michael

Re: Context variables

Anonymous — Mon, 03 Dec 2018 07:09:59 GMT

Hi Vincent,

If you dont mind, could you publish the entire SQL you are using in the Informix? A sample query is also fine. Just want to make sure that you are using the SQL and the context variables in the right way.

Warm Regards,

Nikhil Thampi

Re: Context variables

vincent5 — Mon, 03 Dec 2018 07:14:39 GMT

Hi,

I do not know how it converted the variables but I have a crash when inserting the data in database:

Exception in component agELTInformixOutput_2 (CAC07_TB_1_101_Alim_tmp_collecte_AC_CCOL)
java.sql.SQLException: Column (cm0) not found in any table in the query (or SLV is undefined).
   at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
   at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3770)
   at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2576)
   at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2492)
   at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1360)
   at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:320)
   at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:300)
   at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:170)
   at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:6523)
   at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2334)
   at

Re: Context variables

vincent5 — Mon, 03 Dec 2018 07:16:41 GMT

Hi,

The sql code with the concat gives this:

"SELECT
distinct substr('" +context.DATE_TRT+ "',1,4)-1, CONCAT (" +context.CM0+ ", " +context.CAISSE+ "), opt.nsirent, opt.lsirent, opt.noc, nvl(dlu.ndpt_lin_unq,'" +context.CAISSE+ "'), opt.npdt, ident.nent, ident.neta, adh.badhcol, adh.fadhcol, opt.noptcol, opt.voptcol, case when ( to_char(adh.fadhcol,'%Y') >= substr('" +context.DATE_TRT+ "',1,4)) then 1 else 0 end, case when ( to_char(adh.badhcol,'%Y') = substr('" +context.DATE_TRT+ "',1,4)-1) then 1 else 0 end, case when ( to_char(adh.fadhcol,'%Y') = substr('" +context.DATE_TRT+ "',1,4)-1) then 1 else 0 end, '" +context.DATE_TRT+ "'
FROM
cac87:acgestopt opt INNER JOIN cac87:acgestadh adh ON( adh.nordcle = opt.nordcle )
INNER JOIN cac87:acgestident ident ON( ident.nidtadhcol = adh.nidtadhcol AND ident.norgrsp = adh.norgrsp )
LEFT OUTER JOIN com87:dlu dlu ON( dlu.nide = ident.nent )
WHERE
opt.npdt IS NOT NULL
AND (to_char(adh.badhcol,'%Y') >= substr('" +context.DATE_TRT+ "',1,4)-1 OR to_char(adh.fadhcol,'%Y') >= substr('" +context.DATE_TRT+ "',1,4)-1 )"

Re: Context variables

manodwhb — Mon, 03 Dec 2018 07:17:59 GMT

@vincent,i do not understand why you are giving contexts in single quotes,quotes are not needed.

example:-

substing(context.abc,1,4)

Re: Context variables

Anonymous — Mon, 03 Dec 2018 07:18:02 GMT

@vincent

Please refer the places where you are having issues.

Warm Regards,

Nikhil Thampi

Re: Context variables

vincent5 — Mon, 03 Dec 2018 07:20:51 GMT

Hi,

In the "ELTInformixMap '" I do not put a single or double quotation mark, they are added by Talend in the SQL code.

Regards,

Vincent

Re: Context variables

vincent5 — Mon, 03 Dec 2018 07:23:14 GMT

Hi,

The context variables used alone work very well, but when I want to concatenate two of them, it does not work.
The problem lies in the screen copy in the concat.

Regards,

Vincent

Re: Context variables

Anonymous — Mon, 03 Dec 2018 07:23:54 GMT

Hi Vincent,

I would suggest you to see the code tab also so that you can understand the places where you are having the java errors. The errors are happening because the current code is having single quotes for variables. You will have to remove them and verify the code before running the job.

Warm Regards,

Nikhil Thampi

Re: Context variables

vincent5 — Mon, 03 Dec 2018 07:31:23 GMT

Hi,

The Informix components have been developed specifically for us, I do not know if they have been made available in Talend for everyone.
Should we review this component to remove quotes or double quotes?
We have just migrated to version 6.4.1 and in the previous version (5.2.2) we did not have this problem.

The sql code generated by the comoposant talend of CONCAT (context.CM0, context.CAISSE):

GlobalMap
.put ("tELTInformixMap_1" + "QUERY" + "table1",
"SELECT distinct substr ('"
+ context.DATE_TRT
+ "', 1.4) -1, CONCAT ("
+ context.CM0
+ ","
+ context.CAISSE
+ "),

Regards,

Vincent