Postgres globalMap query help

[Anonymous]

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?

[TRF]

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)

[Anonymous]

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?

[TRF]

Try from outside the Studio to find the correct syntax

[Anonymous]

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.

[TRF]

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.

[Anonymous]

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.

[TRF]

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

[Anonymous]

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.

[TRF]

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