select count(*) from tablename where key = <parameter>

[Frans1]

I want to do this simple query in Talend, but until now (2 days later) I haven't make this query in Talend working. What I want is the amout of records with a where clause with only one parameter. This parameter is available in context, and I want to work with the result, the amout of records futher on in the flow. How can I do this. 

I am new within Talend so I think it is an simple question four the experienced users among us but not fot me please help !!

[Anonymous]

Hi
Quick question, what is the data type of parameter? Date or Integer or String?
Assuming you are using Mysql database, you can use a tMysqlInput component to execute this query, eg:
"select count(*) from tableName where key="+context.parameterName

Regards
Shong

[Frans1]

What a quick response great !!!

We are using a Postgres database and the parameter is a string.  

This is what I use at this moment:

PostgresInput.docx

[Anonymous]

Hi,

    I could see that equal to character is missing in your query.

Also you are using escape character and you have not closed the original query.

select count(*) as AANTRECS from tablename where cipversienummer =\""+context.variable+"\""

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Frans1]

Hi Nikhil,

I have changed the statement as you suggested I just added one " at the begin because otherwise I get errors, this is the SQL-statement what is executed.

"select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer =\" "+context.VRSNUM +"\""

And I get the next response with the error that 010108 is not a column. '010108' is the value of context.VRSNUM and that is what want but at runtime I get an error. How can I solve this, so that the query will be excuted with the value from context.VRSNUM

Starting job ibz_cipers_read_versieinformatie1 at 10:44 21/11/2019.

[statistics] connecting to socket on port 3498
[statistics] connected
tContextLoad_4 set key "VRSNUM" with value "010108"
Exception in component tPostgresqlInput_2
org.postgresql.util.PSQLException: ERROR: column " 010108" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tPostgresqlInput_2Process(ibz_cipers_read_versieinformatie1.java:4142)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tMsgBox_2Process(ibz_cipers_read_versieinformatie1.java:3110)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tMsgBox_1Process(ibz_cipers_read_versieinformatie1.java:2989)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tAS400Input_1Process(ibz_cipers_read_versieinformatie1.java:2846)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tFileInputDelimited_2Process(ibz_cipers_read_versieinformatie1.java:1944)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tFileInputDelimited_1Process(ibz_cipers_read_versieinformatie1.java:1323)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.runJobInTOS(ibz_cipers_read_versieinformatie1.java:5723)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.main(ibz_cipers_read_versieinformatie1.java:5432)
[statistics] disconnected
Job ibz_cipers_read_versieinformatie1 ended at 10:44 21/11/2019. [exit code=1]

Regards,

Frans 

[Frans1]

Hi Nikhil,

I have changed the statement as you suggested I just added one " at the begin because otherwise I get errors, this is the SQL-statement what is executed.

"select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer =\" "+context.VRSNUM +"\""

And I get the next response with the error that 010108 is not a column. '010108' is the value of context.VRSNUM and that is what want but at runtime I get an error. How can I solve this, so that the query will be excuted with the value from context.VRSNUM

Starting job ibz_cipers_read_versieinformatie1 at 10:44 21/11/2019.

[statistics] connecting to socket on port 3498
[statistics] connected
tContextLoad_4 set key "VRSNUM" with value "010108"
Exception in component tPostgresqlInput_2
org.postgresql.util.PSQLException: ERROR: column " 010108" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tPostgresqlInput_2Process(ibz_cipers_read_versieinformatie1.java:4142)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tMsgBox_2Process(ibz_cipers_read_versieinformatie1.java:3110)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tMsgBox_1Process(ibz_cipers_read_versieinformatie1.java:2989)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tAS400Input_1Process(ibz_cipers_read_versieinformatie1.java:2846)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tFileInputDelimited_2Process(ibz_cipers_read_versieinformatie1.java:1944)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tFileInputDelimited_1Process(ibz_cipers_read_versieinformatie1.java:1323)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.runJobInTOS(ibz_cipers_read_versieinformatie1.java:5723)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.main(ibz_cipers_read_versieinformatie1.java:5432)
[statistics] disconnected
Job ibz_cipers_read_versieinformatie1 ended at 10:44 21/11/2019. [exit code=1]

Regards,

Frans 

[Anonymous]

Hi,

   It seems you have not added the escape character before the starting of the string. Could you please add it as shown in my previous post?

    Please also try to print and see the entire select statement using System.out function in a tjava to make sure that the select statement is correctly generated.

Warm Regards,

Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Frans1]

Hi Nikhil,

I think i have solved the problem.

"select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer = '" + context.VRSNUM + "'" 

result in on runtime:

select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer = '010108'

and the query seems to be working, but where can I find the result of the query?

I expected this in context.AANTRECS but this still 'null' after the excecute, how can I acces the value of the result of the query I should expect AANTRECS

but this still 'null' after the query but to contence shoud be 1 or higher 

Thank you in advance 

Frans 

[Anonymous]

Hi,

     You are right. I overlooked it. Ideally you need to use single quotes.  Now coming to your current issue, I would recommend to add a tLogrow after tDBInput to check whether data is coming out from DB component. Once its, confirmed, you can check whether you are passing the data properly. You do not need any component like tMsgBox and tJava between Context Load and tMap. Once the data is mapped correctly, it will be loaded to corresponding context variable.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Frans1]

Hi Nikhil,

First of all sorry for all the questions !!

I have added tLogRow and then I see a result from the query, in the picture is this 0 and that is correct. As you can see I have added tContextload and after that I want to execute the next job when the value of context.AANTRECS = 0 (null). But that isn't working this is component value of the if-statement after the contextload:

context.AANTRECS.equals('0')

Can you tell me what I'am doing wrong?