topic Re: Postgres globalMap query help in Talend Studio

Postgres globalMap query help

Anonymous — Mon, 04 Nov 2019 22:01:26 GMT

I am trying to update two columns based on globalMap (which gets the jobs ID).

This is my query that I have in query editor:

UPDATE jobs
SET succ_flag = 'success',
	last_exec_complete = current_timestamp
WHERE job_id ='" + ((String)globalMap.get("myJob")) +"'

But that gives me this error:

ERROR: invalid input syntax for integer: "" + ((String)globalMap.get("myJob")) +""
Position: 94

How am I supposed to add globalMap to my query?

Re: Postgres globalMap query help

TRF — Mon, 04 Nov 2019 22:21:20 GMT

The way the query is constructed is ok (except the end which should be "'") but the column job_id is a number. Change the global type to Integer or use a syntax like this one:
WHERE job_id = to_number('" + ((String)globalMap.get("myJob")) + "')"

(to_number or corresponding function for your database)

Re: Postgres globalMap query help

Anonymous — Mon, 04 Nov 2019 22:31:40 GMT

Thank you, that makes sense. However, when I tried the to_number option I still receive an error.

This time the error is:

Unterminated identifier started at position 147 in SQL UPDATE jobs
SET succ_flag = 'success',
	last_exec_complete = current_timestamp
WHERE job_id = to_number('" + ((String)globalMap.get("myJob")) + "')". Expected " char

Are you familiar with this error or is there a way I can find out where exactly is position 147?

Re: Postgres globalMap query help

TRF — Mon, 04 Nov 2019 22:38:42 GMT

Try from outside the Studio to find the correct syntax

Re: Postgres globalMap query help

Anonymous — Tue, 05 Nov 2019 16:36:51 GMT

I can get it to work from outside easily. Just not inside with ((String)globalMap.get("myJob"))

I might have to think another way around it.

Re: Postgres globalMap query help

TRF — Tue, 05 Nov 2019 16:56:08 GMT

Your query must be entered as a string in the Query field.

So it must be included between "" like this:

"UPDATE jobs
SET succ_flag = 'success',
	last_exec_complete = current_timestamp
WHERE job_id = to_number('" + ((String)globalMap.get("myJob")) + "')"

Don't forget the " at the beginning.

Re: Postgres globalMap query help

Anonymous — Tue, 05 Nov 2019 17:29:43 GMT

I have all that, which is why I am thinking now I am messing up somewhere else or need to try a different approach.

This is what I tried

I even tried to copy and paste what you put into the bottom query box (so it doesn't have the \'s) but still no luck.

It gives me this error:

I tried other options such as:

UPDATE jobs
SET succ_flag = 'success',
	last_exec_complete = current_timestamp
WHERE job_id = to_number('" + ((String)globalMap.get("myJob")) + "', '99')

and to use CAST or ::integer but no luck.

Re: Postgres globalMap query help

TRF — Tue, 05 Nov 2019 17:47:42 GMT

What if you try this for the where clause?
"... WHERE job_id = " + ((String)globalMap.get("myJob"))

Re: Postgres globalMap query help

Anonymous — Tue, 05 Nov 2019 18:03:33 GMT

That seems to be more in the right direction but still no luck.

It just tells me:

ERROR: syntax error at or near "17"
  Position: 99

I believe it means job id 17 since there is no other 17 in the query but that shouldn't cause any issues since it is almost identical to other jobs in this test scenario.

Re: Postgres globalMap query help

TRF — Tue, 05 Nov 2019 18:52:25 GMT

What's the datatype for the job_id column un thé database?

Re: Postgres globalMap query help

Anonymous — Tue, 05 Nov 2019 19:15:51 GMT

int4

Re: Postgres globalMap query help

TRF — Tue, 05 Nov 2019 19:30:27 GMT

Strange...
I suggest you to construct the query into a global variable and to display it before the tDBRow component

Re: Postgres globalMap query help

Anonymous — Tue, 05 Nov 2019 20:58:14 GMT

Yep, that helped me see the issue. It basically just showed me that in this case I didn't even have to use the global variable. Thanks.

Re: Postgres globalMap query help

TRF — Tue, 05 Nov 2019 22:41:24 GMT

Great!
Thanks to mark your case as solved.