Hans-Jürgen Schönig wrote:
> String dsql = "UPDATE t_consoleserver "
> + "SET data = data || '" + newdata
> + "' WHERE procid = '" + procid + "' ";
> Elog.Elog("DSQL: " + dsql);
>
> stmt.executeUpdate(dsql);
> The interesting thing here is that I have used the same code with the
> JDBC driver which is included in 7.3. It did not work as well but the
> error message was different. "tail -f postgres_log" told me that only
> half of the query has been transmitted to the server:
>
> UPDATE t_consoleserver SET data = data || '021011014504
>
> The rest has been discarded silently. PostgreSQL complains that there is
> a syntax error or course.
> Removing "|| '021011014504' " solved the problem and the entire query
> was transmitted as well.
>
> By the way: "invalid message format" is sent by build 301.
> Did anybody encounter similar problem?
> I coded SQL statement number 1.0000000000000....000000 in this
> application but the only statement making use of || seems to cause
> problems.
'newdata' contains a literal \0 and you are not escaping it correctly.
The v2 protocol treats the \0 as a message separator and much confusion
ensues. Note that you probably won't see the \0 in your debug logs --
I've noticed that it can get silently removed when encoding String ->
actual output encoding. You'll see the \0 if you print the individual
values of the array returned by newdata.toCharArray() as integers.
Improving the driver code so it detects and rejects this type of query
with a more useful error message is on my todo list.
I'd suggest that you use a PreparedStatement for parameterized queries;
the driver knows how to correctly escape parameters (which you're not
doing at all -- better hope that newdata doesn't contain a single quote
or backslash!) and will reject a String containing \0s with a useful error.
-O
