Is a Postgres/SQL UPDATE statement limited to a maximum length?
Hi all,
I am using a sql update statement like this in a Groovy Out connector:
"sql.execute("UPDATE table SET col3=$bdmvar3, [and another 40 variables...].WHERE col1=$bdmvar1")"
The length of this statement is approximately 1300 characters. When I try to execute it, I get this syntax error:
Caused by: org.bonitasoft.engine.connector.exception.SConnectorException: java.util.concurrent.ExecutionException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "is" Position: 588
However, when I split it into 2 separate update statements (with each 20 variables and the same WHERE clause), it works fine.
Two lines further down the script I use an INSERT statement of about 1300 characters and that works fine...
Anyone any idea?
Thanks in advance,
Stefan
There is no artificial limit, I've seen some test results at 20M SQL queries...
However is it simply possible that there is a syntax error in the statement as per the error? I know you say splitting it into two is OK, but no-matter-what two is not the same as one.
Have you fully debugged the statement (and executed it in the III client)?
Does it work there?
regards
Seán
PS: If this reply answers your question, please mark a resolved.
Comments
Thanks Seán. Your answer gave me just enough strength to continue debugging ;-)
The variables I am using to update the SQL row are bdm variables. Two of them were not assigned a value before (they shouldn't be in the update statement, I know :-)). I expected that they would be treated as null.
When I set these bdm variables with an empty string, or I leave them out the statement, it works.
Makes me still wonder though, why it was also working when I split the statement in two, but I think I am going to leave that for now :-)
Many thanks for your help,
Stefan