Skip site navigation (1) Skip section navigation (2)

Re: very interesting JDBC problem ...

From: Hans-Jürgen Schönig <hs(at)cybertec(dot)at>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>,"pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: very interesting JDBC problem ...
Date: 2004-04-01 09:52:53
Message-ID: 406BE675.3050107@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-jdbc
Oliver Jowett wrote:
> 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.

Oh, I see.
Somehow this little symbol got in there. That's the problem. I did not 
think about that because obviously my logging function has done it 
right. I did not JDBC to behave differently.
The message returned by PostgreSQL made me think of a JDBC bug.

You have saved me some JDBC-internal work :).

	Thanks a lot,

		Hans

-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at


In response to

pgsql-jdbc by date

Next:From: Rajat KatyalDate: 2004-04-01 13:19:55
Subject: JDBC driver returning the numeric dataype size as -1
Previous:From: Oliver JowettDate: 2004-04-01 09:37:51
Subject: patch: enforce the requirements for scrollable resultsets

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group