Обсуждение: how to handle data containing '\0'
Hi experts,
I need to handle String data containing '\0' in my Java + JDBC program. Such data can happen at other systems and be
sentto
PostgreSQL. Here is a sample to simulate it in Java code.
char c = '\0';
String val = "abc" + c + "def";
Now I have to support two versions of drivers, 7.4 and 8.1, but the behavior of driver depends on versions when I
insertthis String
value.
=== pg74.216.jdbc3.jar ===
java.lang.IllegalArgumentException: \0 not allowed
at org.postgresql.jdbc1.AbstractJdbc1Statement.escapeString(AbstractJdbc1Statement.java:1152)
at org.postgresql.jdbc1.AbstractJdbc1Statement.setString(AbstractJdbc1Statement.java:1133)
at org.postgresql.jdbc1.AbstractJdbc1Statement.setString(AbstractJdbc1Statement.java:1116)
at Bug5391.main(Bug5391.java:24)
=== postgresql-8.1-405.jdbc3.jar ===
Successfully finished without exception but the string is truncated at '\0' and only the first three characters, "abc",
isinserted.
(I verified this with psql on the server side)
I understand the current protocol does not allow '\0' in String and basically I should remove '\0' before I try to
insertbut I and
team members may forget it somewhere. I'm feeling the 8.1 behavior is worse than 7.4 because user data is truncated
withoutnotice.
For the sample above, I expect "abcdef" rather than "abc".
What do you think about this?
My idea is to introduce new connection parameter to determine how to behave against '\0'.
Options may be
- throws an exception
- truncates at '\0'
- removes '\0'
Thanks,
ebi
"EBIHARA, Yuichiro" <ebihara@iplocks.co.jp> writes: > > I need to handle String data containing '\0' in my Java + JDBC program. Such data can happen at other systems and be sentto > PostgreSQL. Here is a sample to simulate it in Java code. As far as I know, it is not possible to store such strings in a postgreSQL database (whatever the client interface used). The reason seems to be that postgreSQL is written in C, and most C functions use \0 as a string terminator.
Marc Herbert wrote: > "EBIHARA, Yuichiro" <ebihara@iplocks.co.jp> writes: > >> >> I need to handle String data containing '\0' in my Java + JDBC >> program. Such data can happen at other systems and be sent to >> PostgreSQL. Here is a sample to simulate it in Java code. > > As far as I know, it is not possible to store such strings in a > postgreSQL database (whatever the client interface used). The reason > seems to be that postgreSQL is written in C, and most C functions use > \0 as a string terminator. > the bytea datatype handles such strings. LER -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: Larry.Rosenman@pervasive.com Web: www.pervasive.com
Thank you for your quick response. I understand '\0' cannot be stored in VARCHAR/CHAR columns. I just wonder whether JDBC driver should truncate String without notice or not. Throwing exception is understandable butthe behavior was changed in 8.1. This may be a compatibility issue. Thanks, ebi > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Larry Rosenman > Sent: Wednesday, May 17, 2006 10:48 PM > To: Marc Herbert; pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] how to handle data containing '\0' > > > Marc Herbert wrote: > > "EBIHARA, Yuichiro" <ebihara@iplocks.co.jp> writes: > > > >> > >> I need to handle String data containing '\0' in my Java + JDBC > >> program. Such data can happen at other systems and be sent to > >> PostgreSQL. Here is a sample to simulate it in Java code. > > > > As far as I know, it is not possible to store such strings in a > > postgreSQL database (whatever the client interface used). > The reason > > seems to be that postgreSQL is written in C, and most C > functions use > > \0 as a string terminator. > > > the bytea datatype handles such strings. > > LER > > -- > Larry Rosenman > Database Support Engineer > > PERVASIVE SOFTWARE. INC. > 12365B RIATA TRACE PKWY > 3015 > AUSTIN TX 78727-6531 > > Tel: 512.231.6173 > Fax: 512.231.6597 > Email: Larry.Rosenman@pervasive.com > Web: www.pervasive.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > >
On Wed, 17 May 2006, EBIHARA, Yuichiro wrote: > I need to handle String data containing '\0' in my Java + JDBC program. > > Now I have to support two versions of drivers, 7.4 and 8.1, but the > behavior of driver depends on versions when I insert this String value. > This was changed between 7.4 and 8.0 when prepared statements changed from dynamically creating a sql string from the parameters to passing the parameters separately. When creating a sql string the parameters had to be carefully checked for ' and \ so checking for \0 was also done. When passing the parameters separately there is no need for the ' and \ checks so the \0 check was removed as well either unintentionally or for perfomance reasons. New server releases for all versions will be out this week that fix the problem on the server side by explicitly rejecting \0 rather than truncating. Kris Jurka
Thanks, Kris. I understand the behavior is server-side issue, not JDBC, and we are to be forced to explicitely remove '\0' with the latestserver releases. It's not necessarily the best for me but better than now. Finally, do you know the thread containing the discussion that triggered the change of server behavior? Thanks, ebi > -----Original Message----- > From: Kris Jurka [mailto:books@ejurka.com] > Sent: Monday, May 22, 2006 4:29 PM > To: EBIHARA, Yuichiro > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] how to handle data containing '\0' > > > > > On Wed, 17 May 2006, EBIHARA, Yuichiro wrote: > > > I need to handle String data containing '\0' in my Java + JDBC > > program. > > > > Now I have to support two versions of drivers, 7.4 and 8.1, but the > > behavior of driver depends on versions when I insert this > String value. > > > > This was changed between 7.4 and 8.0 when prepared statements > changed from > dynamically creating a sql string from the parameters to passing the > parameters separately. When creating a sql string the > parameters had to > be carefully checked for ' and \ so checking for \0 was also > done. When > passing the parameters separately there is no need for the ' > and \ checks > so the \0 check was removed as well either unintentionally or for > perfomance reasons. > > New server releases for all versions will be out this week > that fix the > problem on the server side by explicitly rejecting \0 rather than > truncating. > > Kris Jurka > > > >
On Mon, 22 May 2006, EBIHARA, Yuichiro wrote: > I understand the behavior is server-side issue, not JDBC, and we are to > be forced to explicitely remove '\0' with the latest server releases. > > It's not necessarily the best for me but better than now. > > Finally, do you know the thread containing the discussion that triggered > the change of server behavior? > Original thread starts here: http://archives.postgresql.org/pgsql-hackers/2006-02/msg00743.php Commit message here: http://archives.postgresql.org/pgsql-committers/2006-05/msg00206.php Kris Jurka