Обсуждение: JDBC 'Unterminated quoted string'
I am migrating a Java application from a commercial RDBMS to Postgresql. The transition has been near-seamless, except I am getting a troublesome SQLException when executing one of my PreparedStatement objects. The SQLException is 'Unterminated quoted string', and it occurs on a simple SQL insert statement. I have other insert statements which execute flawlessly, and this particular statement worked fine on a different database. I searched the archives and the only information I could find on this error had to do with quoting question-mark (?) literals with ODBC. Since the JDBC PreparedStatement uses ?, I was wondering if there was a problem parsing the PreparedStatement. The only thing that distinguishes the problematic PreparedStatement is that I am inserting a large number of columns (21). I'm using the jdbc7.0-1.1.jar, and running Posgresql 7.0.2. Thanks if you can help! ---- Christopher Farley Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 www.northernbrewer.com
Christopher, I would suggest that you examine the query that arrives at the backend. (Start the postmaster with the -d2 switch) This should at least give you the source of the unterminated string, and presumably, we can then trace it through the execution of the JDBC driver. Offhand, I would suggest that one of the string fields in the insert has a ' character inside. AFAIK the postgresql jdbc driver does not automatically escape this character. The correct escape would be ''. Regards, Grant Christopher Farley wrote: > I am migrating a Java application from a commercial RDBMS to > Postgresql. The transition has been near-seamless, except I am getting a > troublesome SQLException when executing one of my PreparedStatement > objects. > > The SQLException is 'Unterminated quoted string', and it occurs on a > simple SQL insert statement. I have other insert statements which execute > flawlessly, and this particular statement worked fine on a different > database. > > I searched the archives and the only information I could find on this > error had to do with quoting question-mark (?) literals with ODBC. Since > the JDBC PreparedStatement uses ?, I was wondering if there was a problem > parsing the PreparedStatement. The only thing that distinguishes the > problematic PreparedStatement is that I am inserting a large number of > columns (21). > > I'm using the jdbc7.0-1.1.jar, and running Posgresql 7.0.2. > > Thanks if you can help! > ---- > Christopher Farley > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > www.northernbrewer.com -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa
Grant, Thanks for the -d2 tip, it will make my life a lot easier for quite some time! Actually, I already tested the 'unescaped single quote' theory, and the JDBC driver *does* escape them for you. The problem in my case seems to be that the JDBC driver is throwing the 'unterminated quoted string' because I am inserting a null value into a char(1) field. I do not have any 'not null' constraints on the field. I can easily work around this problem now that Iunderstand it, but I'm pretty sure this is not correct behavior for the JDBC driver. Is this the proper forum to report bugs, if this is indeed a bug? ---- Christopher Farley Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 www.northernbrewer.com On Thu, 9 Nov 2000, Grant Finnemore wrote: > Christopher, > > I would suggest that you examine the query that arrives at the backend. > (Start the postmaster with the -d2 switch) > > This should at least give you the source of the unterminated string, and > presumably, we can then trace it through the execution of the JDBC driver. > > Offhand, I would suggest that one of the string fields in the insert has a ' > character inside. AFAIK the postgresql jdbc driver does not automatically > escape this character. The correct escape would be ''. > > Regards, > Grant > > Christopher Farley wrote: > > > I am migrating a Java application from a commercial RDBMS to > > Postgresql. The transition has been near-seamless, except I am getting a > > troublesome SQLException when executing one of my PreparedStatement > > objects. > > > > The SQLException is 'Unterminated quoted string', and it occurs on a > > simple SQL insert statement. I have other insert statements which execute > > flawlessly, and this particular statement worked fine on a different > > database. > > > > I searched the archives and the only information I could find on this > > error had to do with quoting question-mark (?) literals with ODBC. Since > > the JDBC PreparedStatement uses ?, I was wondering if there was a problem > > parsing the PreparedStatement. The only thing that distinguishes the > > problematic PreparedStatement is that I am inserting a large number of > > columns (21). > > > > I'm using the jdbc7.0-1.1.jar, and running Posgresql 7.0.2. > > > > Thanks if you can help! > > ---- > > Christopher Farley > > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > > www.northernbrewer.com > > -- > > Poorly planned software requires a genius to write it > > and a hero to use it. > > Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) > Software Engineer Universal Computer Services > Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa > Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein > Fax (+27)(11)339-3421 Johannesburg, South Africa > >
Christopher, Yep, this is the correct place to discuss bugs... I tried the following code, and it seems to work. Is this similar to what you are trying to do? import java.sql.*; public class JDBCTest { public static void main(String[] args) throws Exception { Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/test", "jms", "jms"); /* Beforerunning this class, ensure that the database is created. * CREATE DATABASE test; * CREATE TABLE t ( aa char(1)); */ PreparedStatement ps = conn.prepareStatement("insert into t values (?)"); ps.setString(1,null); ps.executeUpdate(); } } Also, what version of the driver are you using - is it off CVS, the version 7.0.x tarballs, something else? Regards, Grant Christopher Farley wrote: > Grant, > Thanks for the -d2 tip, it will make my life a lot easier for quite > some time! > Actually, I already tested the 'unescaped single quote' theory, and the > JDBC driver *does* escape them for you. > The problem in my case seems to be that the JDBC driver is throwing the > 'unterminated quoted string' because I am inserting a null value into a > char(1) field. I do not have any 'not null' constraints on the field. > I can easily work around this problem now that I understand it, but I'm > pretty sure this is not correct behavior for the JDBC driver. Is this the > proper forum to report bugs, if this is indeed a bug? > > ---- > Christopher Farley > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > www.northernbrewer.com -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa
If you setString(1,null), it does work. I am actually doing a setString(1,String.valueOf('\0')), which causes the unterminated string error. Although this didn't fail under the previous RDBMS (PervasiveSQL), I have considered that setting a Java char to '\0' is just bad programming practice. However: - Both Postgresql and Java evaluate '\0' as ''. - You can't set a Java char to ''. - Many people have a need to map chars to a RDBMS So it seems reasonable to me that the JDBC driver should escape NULL chars as '\\0' rather than pass the character unparsed where it will be treated as a NUL terminator when it appears in a C string. Damn these primitive types in Java! ---- Christopher Farley Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 www.northernbrewer.com On Thu, 9 Nov 2000, Grant Finnemore wrote: > Christopher, > > Yep, this is the correct place to discuss bugs... > > I tried the following code, and it seems to work. Is this similar to what you are > trying to do? > > import java.sql.*; > > public class JDBCTest { > public static void main(String[] args) throws Exception { > Class.forName("org.postgresql.Driver"); > Connection conn = > DriverManager.getConnection("jdbc:postgresql://localhost/test", > "jms", "jms"); > /* Before running this class, ensure that the database is created. > * CREATE DATABASE test; > * CREATE TABLE t ( aa char(1) ); > */ > PreparedStatement ps = > conn.prepareStatement("insert into t values (?)"); > ps.setString(1, null); > ps.executeUpdate(); > } > } > > Also, what version of the driver are you using - is it off CVS, the version 7.0.x > tarballs, something else? > > Regards, > Grant > > Christopher Farley wrote: > > > Grant, > > Thanks for the -d2 tip, it will make my life a lot easier for quite > > some time! > > Actually, I already tested the 'unescaped single quote' theory, and the > > JDBC driver *does* escape them for you. > > The problem in my case seems to be that the JDBC driver is throwing the > > 'unterminated quoted string' because I am inserting a null value into a > > char(1) field. I do not have any 'not null' constraints on the field. > > I can easily work around this problem now that I understand it, but I'm > > pretty sure this is not correct behavior for the JDBC driver. Is this the > > proper forum to report bugs, if this is indeed a bug? > > > > ---- > > Christopher Farley > > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > > www.northernbrewer.com > > -- > > Poorly planned software requires a genius to write it > > and a hero to use it. > > Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) > Software Engineer Universal Computer Services > Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa > Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein > Fax (+27)(11)339-3421 Johannesburg, South Africa > >
Christopher, A patch for the postgresql driver - class PreparedStatement.java in both jdbc1 and jdbc2. 274,285c274 < < /* If the string contains a null zero, when it reaches the < * backend, the C routines to manipulate a string will < * assume that the string is finished at this point. This < * will result in an 'Unterminated string constant' error < * from the backend. Hence, before inserting the character, < * check if it is a null zero, and if so, escape it. < */ < if (c != '\0') < b.append(c); < else < b.append("\\0"); --- > b.append(c); Regards, Grant Christopher Farley wrote: > If you setString(1,null), it does work. > > I am actually doing a setString(1,String.valueOf('\0')), which causes the > unterminated string error. > > Although this didn't fail under the previous RDBMS (PervasiveSQL), I > have considered that setting a Java char to '\0' is just bad programming > practice. However: > > - Both Postgresql and Java evaluate '\0' as ''. > > - You can't set a Java char to ''. > > - Many people have a need to map chars to a RDBMS > > So it seems reasonable to me that the JDBC driver should escape NULL chars > as '\\0' rather than pass the character unparsed where it will be treated > as a NUL terminator when it appears in a C string. > > Damn these primitive types in Java! > > ---- > Christopher Farley > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > www.northernbrewer.com > > On Thu, 9 Nov 2000, Grant Finnemore wrote: > -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa
Hmmm, there may be. I'll check shortly... -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Christopher Farley [mailto:chris@northernbrewer.com] Sent: Wednesday, November 08, 2000 7:31 PM To: pgsql-interfaces@postgresql.org Subject: [INTERFACES] JDBC 'Unterminated quoted string' I am migrating a Java application from a commercial RDBMS to Postgresql. The transition has been near-seamless, except I am getting a troublesome SQLException when executing one of my PreparedStatement objects. The SQLException is 'Unterminated quoted string', and it occurs on a simple SQL insert statement. I have other insert statements which execute flawlessly, and this particular statement worked fine on a different database. I searched the archives and the only information I could find on this error had to do with quoting question-mark (?) literals with ODBC. Since the JDBC PreparedStatement uses ?, I was wondering if there was a problem parsing the PreparedStatement. The only thing that distinguishes the problematic PreparedStatement is that I am inserting a large number of columns (21). I'm using the jdbc7.0-1.1.jar, and running Posgresql 7.0.2. Thanks if you can help! ---- Christopher Farley Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 www.northernbrewer.com
Yes this is the correct place to report bugs in JDBC. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Christopher Farley [mailto:chris@northernbrewer.com] Sent: Thursday, November 09, 2000 5:50 AM To: Grant Finnemore Cc: pgsql-interfaces@postgresql.org Subject: Re: [INTERFACES] JDBC 'Unterminated quoted string' Grant, Thanks for the -d2 tip, it will make my life a lot easier for quite some time! Actually, I already tested the 'unescaped single quote' theory, and the JDBC driver *does* escape them for you. The problem in my case seems to be that the JDBC driver is throwing the 'unterminated quoted string' because I am inserting a null value into a char(1) field. I do not have any 'not null' constraints on the field. I can easily work around this problem now that Iunderstand it, but I'm pretty sure this is not correct behavior for the JDBC driver. Is this the proper forum to report bugs, if this is indeed a bug? ---- Christopher Farley Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 www.northernbrewer.com On Thu, 9 Nov 2000, Grant Finnemore wrote: > Christopher, > > I would suggest that you examine the query that arrives at the backend. > (Start the postmaster with the -d2 switch) > > This should at least give you the source of the unterminated string, and > presumably, we can then trace it through the execution of the JDBC driver. > > Offhand, I would suggest that one of the string fields in the insert has a ' > character inside. AFAIK the postgresql jdbc driver does not automatically > escape this character. The correct escape would be ''. > > Regards, > Grant > > Christopher Farley wrote: > > > I am migrating a Java application from a commercial RDBMS to > > Postgresql. The transition has been near-seamless, except I am getting a > > troublesome SQLException when executing one of my PreparedStatement > > objects. > > > > The SQLException is 'Unterminated quoted string', and it occurs on a > > simple SQL insert statement. I have other insert statements which execute > > flawlessly, and this particular statement worked fine on a different > > database. > > > > I searched the archives and the only information I could find on this > > error had to do with quoting question-mark (?) literals with ODBC. Since > > the JDBC PreparedStatement uses ?, I was wondering if there was a problem > > parsing the PreparedStatement. The only thing that distinguishes the > > problematic PreparedStatement is that I am inserting a large number of > > columns (21). > > > > I'm using the jdbc7.0-1.1.jar, and running Posgresql 7.0.2. > > > > Thanks if you can help! > > ---- > > Christopher Farley > > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > > www.northernbrewer.com > > -- > > Poorly planned software requires a genius to write it > > and a hero to use it. > > Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) > Software Engineer Universal Computer Services > Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa > Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein > Fax (+27)(11)339-3421 Johannesburg, South Africa > >
Agreed. I'm doing another blitz on JDBC tomorrow, so I'll do the deed then. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Christopher Farley [mailto:chris@northernbrewer.com] Sent: Thursday, November 09, 2000 7:53 AM To: Grant Finnemore Cc: pgsql-interfaces@postgresql.org Subject: Re: [INTERFACES] JDBC 'Unterminated quoted string' If you setString(1,null), it does work. I am actually doing a setString(1,String.valueOf('\0')), which causes the unterminated string error. Although this didn't fail under the previous RDBMS (PervasiveSQL), I have considered that setting a Java char to '\0' is just bad programming practice. However: - Both Postgresql and Java evaluate '\0' as ''. - You can't set a Java char to ''. - Many people have a need to map chars to a RDBMS So it seems reasonable to me that the JDBC driver should escape NULL chars as '\\0' rather than pass the character unparsed where it will be treated as a NUL terminator when it appears in a C string. Damn these primitive types in Java! ---- Christopher Farley Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 www.northernbrewer.com On Thu, 9 Nov 2000, Grant Finnemore wrote: > Christopher, > > Yep, this is the correct place to discuss bugs... > > I tried the following code, and it seems to work. Is this similar to what you are > trying to do? > > import java.sql.*; > > public class JDBCTest { > public static void main(String[] args) throws Exception { > Class.forName("org.postgresql.Driver"); > Connection conn = > DriverManager.getConnection("jdbc:postgresql://localhost/test", > "jms", "jms"); > /* Before running this class, ensure that the database is created. > * CREATE DATABASE test; > * CREATE TABLE t ( aa char(1) ); > */ > PreparedStatement ps = > conn.prepareStatement("insert into t values (?)"); > ps.setString(1, null); > ps.executeUpdate(); > } > } > > Also, what version of the driver are you using - is it off CVS, the version 7.0.x > tarballs, something else? > > Regards, > Grant > > Christopher Farley wrote: > > > Grant, > > Thanks for the -d2 tip, it will make my life a lot easier for quite > > some time! > > Actually, I already tested the 'unescaped single quote' theory, and the > > JDBC driver *does* escape them for you. > > The problem in my case seems to be that the JDBC driver is throwing the > > 'unterminated quoted string' because I am inserting a null value into a > > char(1) field. I do not have any 'not null' constraints on the field. > > I can easily work around this problem now that I understand it, but I'm > > pretty sure this is not correct behavior for the JDBC driver. Is this the > > proper forum to report bugs, if this is indeed a bug? > > > > ---- > > Christopher Farley > > Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105 > > www.northernbrewer.com > > -- > > Poorly planned software requires a genius to write it > > and a hero to use it. > > Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) > Software Engineer Universal Computer Services > Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa > Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein > Fax (+27)(11)339-3421 Johannesburg, South Africa > >
Peter, Joseph Shraibman contacted me yesterday about whether or not the patch that I sent actually does the correct thing. I enclose my response below. In brief, there seems to be some problem at the backend inserting a \0. I have tested the query from the modified jdbc driver, psql and a standalone backend. Regards, Grant I tested by doing the following:- In psql... CREATE TABLE t ( aa char(1) ); INSERT INTO t VALUES ('\0'); In java test program... PreparedStatement ps = conn.prepareStatement("insert into t values (?)"); ps.setString(1,String.valueOf('\0')); ps.executeUpdate(); In psql... test=# select * from t;aa ---- (2 rows) test=# select ascii(aa) from t;ascii ------- 32 32 (2 rows) This is wierd! Both psql and the java app insert the same value and both are not 0. However, test=# select ascii('\0');ascii ------- 0 (1 row) This appears correct, so the problem would appear to be in the insert. -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa
Yes it does look like it's the insert that's at fault. I've tested it on a current backend here and it has the same problem. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Grant Finnemore [mailto:gaf@ucs.co.za] Sent: Friday, November 10, 2000 10:21 AM To: Peter Mount Cc: Interfaces Subject: Re: [INTERFACES] JDBC 'Unterminated quoted string' Peter, Joseph Shraibman contacted me yesterday about whether or not the patch that I sent actually does the correct thing. I enclose my response below. In brief, there seems to be some problem at the backend inserting a \0. I have tested the query from the modified jdbc driver, psql and a standalone backend. Regards, Grant I tested by doing the following:- In psql... CREATE TABLE t ( aa char(1) ); INSERT INTO t VALUES ('\0'); In java test program... PreparedStatement ps = conn.prepareStatement("insert into t values (?)"); ps.setString(1,String.valueOf('\0')); ps.executeUpdate(); In psql... test=# select * from t;aa ---- (2 rows) test=# select ascii(aa) from t;ascii ------- 32 32 (2 rows) This is wierd! Both psql and the java app insert the same value and both are not 0. However, test=# select ascii('\0');ascii ------- 0 (1 row) This appears correct, so the problem would appear to be in the insert. -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa
Grant Finnemore <gaf@ucs.co.za> writes: > In brief, there seems to be some problem at the backend inserting a > \0. char(n) does not support embedded nulls. Offhand I believe that bytea is the only datatype that does, and even then you have to write 'em with a backslash-escape --- ie, send "\000" not an actual null. Fixing this is not likely to happen soon, if ever, because it would involve a massive overhaul of the datatype I/O system, with such side-effects as breaking every user-defined datatype in existence. Textual representations of datatypes are C strings --- ie, null-terminated --- and all datatype I/O routines depend on that. regards, tom lane
If it's going to break that many interfaces, then it's not going to be worth the hassle. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Friday, November 10, 2000 2:55 PM To: Grant Finnemore Cc: Peter Mount; Interfaces Subject: Re: [INTERFACES] JDBC 'Unterminated quoted string' Grant Finnemore <gaf@ucs.co.za> writes: > In brief, there seems to be some problem at the backend inserting a > \0. char(n) does not support embedded nulls. Offhand I believe that bytea is the only datatype that does, and even then you have to write 'em with a backslash-escape --- ie, send "\000" not an actual null. Fixing this is not likely to happen soon, if ever, because it would involve a massive overhaul of the datatype I/O system, with such side-effects as breaking every user-defined datatype in existence. Textual representations of datatypes are C strings --- ie, null-terminated --- and all datatype I/O routines depend on that. regards, tom lane
On Fri, 10 Nov 2000, Grant Finnemore wrote: > Peter, > > Joseph Shraibman contacted me yesterday about whether or not the patch that I > sent actually does the correct thing. I enclose my response below. I think the patch does do the correct thing. Remember, in Java, String.valueOf('\0')==null returns false, so Java developers should not be surprised when their data shows up in a postgres column as '' rather than null. I just think that '\0' should be parsed or stripped from Java strings so that it doesn't cause weird unterminated string errors.