[JDBC] Todo/missing? (was Re: [ADMIN] High memory usage [PATCH])
On Mon, Jun 25, 2001 at 09:32:38PM -0400, Bruce Momjian wrote: As far as procedures and voting, I also believe that something needs to be done for the JDBC code base. Since Peter has apparently disappeared (I haven't seen a post from him in about two months, and the jdbc.postgresql.org website hasn't been updated for about 4 mounths - it doesn't even have the 7.1 code yet) I think the core group needs to step in and provide some direction for the JDBC code base. Whether that is appointing someone new as the official/unofficial JDBC guru or adopting some other process, I don't know. What I do know is that the JDBC code base is suffering from lack of attention and direction. Let me add I have been concerned about the jdbc interface for the past year. With you people involved, I am no longer concerned. Hi, I've only been reading this list for about a week or so now. Joined it to find out what was happening with this piece of s/w that I was relying on. I think there is need to push on a bit since as remarked above it seems that Peter has disappeared. I think a main step is to find out where we are on the driver, for example its very hard to find out what isn't implemented yet without reading the code or trying it out. I haven't found a todo list. A great place to keep all this is on the web too which needs an update badly. Who has access to update these pages? I don't care about tutorials or stuff like that yet, but just info on known bugs, what works and what doesn't, the recommended versions to run for each database etc and somesort of changelog. I'm willing to help :) Cheers, Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] Todo/missing? (was Re: [ADMIN] High memory usage [PATCH])
On Mon, Jun 25, 2001 at 10:56:18PM -0400, Dave Cramer wrote: I have to agree, we need to compile a todo list. Mine would include: 1) Comprehensive test suite. This may be available already. 2) Updateable resultSet 3) Improved DatabaseMetaData 4) Compatible blob support That looks pretty good from where I stand. I would perhaps separate out also, the fact that the ResultSet.insertXXX() methods also need to be implemented as well as the updateXXX methods. I guess this comes under the updateable resultSet but it's not clear. What about current known bugs or compliance issues? How about the web site? Stuff I'd like added would be, where to find this list, where to find the cvs version, updated binaries, I know someone on this list has made them available. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Methode is not yet implemented.
On Wed, Jul 11, 2001 at 08:43:51PM +0100, Jörg Sommer wrote: Hello! If I send a SELECT query to PostgreSQL from a JDBC connection of my programm about a lokal network I get this message: This methode is not yet implemented. The SELECT query is very simple: SELECT * FROM table; I use S.u.S.E. 7.2. The driver and the PostgreSQL files are from this distribution. I can't believe that there is a stable driver that can't query simple SELECT... We can't believe it either. Send a java code segment. You're probably doing something wrong. It should be something like... ... java.sql.Statement stmt = con.createStatement(); java.sql.ResultSet rs = stmt.executeQuery(SELECT * from table); ... Where con is an open connection to your database. Also, try grabbing a driver from: http://jdbc.fastcrypt.com/ Cheers, Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] NullPointerException on next()
Check that you didn't close the statement before making this call to next() in the ResultSet object. Tom. On Thu, Aug 09, 2001 at 09:34:51PM -0400, Dave Cramer wrote: Joseph, Can you post some code to reproduce the error please? Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Joseph Shraibman Sent: August 9, 2001 9:27 PM To: [EMAIL PROTECTED] Subject: [JDBC] NullPointerException on next() Using the jdbc 7.1.2 drivers: Exception in thread main java.lang.NullPointerException at org.postgresql.jdbc2.ResultSet.next(ResultSet.java:116) that line is: if (++current_row = rows.size()) -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[JDBC] Unterminated quoted string error.
Hi all, I'm currently chasing down a bug. Wonder if anyone can throw some light on it. I get the following exception. An I/O error has occured while flushing the output - Exception: java.io.IOException: Connection reset by peer Stack Trace: java.io.IOException: Connection reset by peer at java.net.SocketOutputStream.socketWrite(Native Method) at java.net.SocketOutputStream.write(SocketOutputStream.java:83) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:72) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:130) at org.postgresql.PG_Stream.flush(PG_Stream.java:414) at org.postgresql.Connection.ExecSQL(Connection.java:479) at org.postgresql.jdbc2.Statement.execute(Statement.java:294) at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78) at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122) And in the postgresql.log file I get... ERROR: Unterminated quoted string FATAL 1: Socket command type unknown But I'm pretty sure that my strings are quoted properly. That is to say that there are about 90 escaped single quotes in a string I'm inserting also though. Anyone seen this before? I'm currently using a version of the driver that I compiled from cvs on the 18th of Jun. Was anything patched since that might effect this? Anyway, I've been digging around for quite a while now so I thought I'd shoot the list a mail before going to bed. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[JDBC] Escape Processing problems
Hi all,
The Connection.EscapeSQL() routine is broken IMHO . Actually, I'm not
sure why it is trying to fix strings starting with {d in the first place?
Anyway, currently I've turned it off in the statement with
setEscapeProcessing(false)
The problem I'm having is that {d appears in the data that I'm trying
to store and its not a date. So data like the following...
.blahhh}; {blahhh }; {docs=};
is turning into...
.blahhh}; {blahhh }; ocs= ;
^^ ^
What's more is if I have something like {d. and there is no ending
brace, it will throw a StringIndexOutOfBoundsException as the return
value of the indexOf() looking for the closing brace will not find one
and thus setCharAt() will use an illegal index of -1 :(
The routine is below for reference... Can anyone explain why it is trying
to do this on me in the first place. I would think escape processing would
do something a little different like watching my single quotes etc.
public String EscapeSQL(String sql) {
//if (DEBUG) { System.out.println (parseSQLEscapes called); }
// If we find a {d, assume we have a date escape.
//
// Since the date escape syntax is very close to the
// native Postgres date format, we just remove the escape
// delimiters.
//
// This implementation could use some optimization, but it has
// worked in practice for two years of solid use.
int index = sql.indexOf({d);
while (index != -1) {
//System.out.println (escape found at index: + index);
StringBuffer buf = new StringBuffer(sql);
buf.setCharAt(index, ' ');
buf.setCharAt(index + 1, ' ');
buf.setCharAt(sql.indexOf('}', index), ' ');
sql = new String(buf);
index = sql.indexOf({d);
}
//System.out.println (modified SQL: + sql);
return sql;
}
Cheers,
Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [JDBC] Unterminated quoted string error.
Thats exactly what I'm doing :) My preparedStatement is like. You'll notice its a PreparedStatement if you look at the stack trace below. con.prepareStatement(INSERT INTO A VALUES(?)); ps.setString(1, stringdata); ps.executeUpdate(); I'm wondering is it a length problem since the field is type text and the string is length 42,460. Any ideas? Tom. On Tue, Aug 28, 2001 at 11:18:25AM -0600, [EMAIL PROTECTED] wrote: I had a similar problem and fixed it by using a preparedStatement and setting the parameter of my string data using myPreparedStatement.setString(myString) t.r. missner level(3) communications -Original Message- From: Thomas O'Dowd [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 28, 2001 9:23 AM To: [EMAIL PROTECTED] Subject: [JDBC] Unterminated quoted string error. Hi all, I'm currently chasing down a bug. Wonder if anyone can throw some light on it. I get the following exception. An I/O error has occured while flushing the output - Exception: java.io.IOException: Connection reset by peer Stack Trace: java.io.IOException: Connection reset by peer at java.net.SocketOutputStream.socketWrite(Native Method) at java.net.SocketOutputStream.write(SocketOutputStream.java:83) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:72) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:130) at org.postgresql.PG_Stream.flush(PG_Stream.java:414) at org.postgresql.Connection.ExecSQL(Connection.java:479) at org.postgresql.jdbc2.Statement.execute(Statement.java:294) at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78) at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java: 122) And in the postgresql.log file I get... ERROR: Unterminated quoted string FATAL 1: Socket command type unknown But I'm pretty sure that my strings are quoted properly. That is to say that there are about 90 escaped single quotes in a string I'm inserting also though. Anyone seen this before? I'm currently using a version of the driver that I compiled from cvs on the 18th of Jun. Was anything patched since that might effect this? Anyway, I've been digging around for quite a while now so I thought I'd shoot the list a mail before going to bed. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[JDBC] Re: Unterminated quoted string error.
Thanks Barry, I turned on debugging in postgresql. I found that the query is being truncated and is not fully making it to the backend, therefore I'm getting the Unterminated string error. I'll have a look into why and report back if I find anything. Cheers, Tom. On Tue, Aug 28, 2001 at 12:56:50PM -0700, Barry Lind wrote: Thomas, If you turn on debug messages on the server to print out the SQL statements it receives you should be able to get the exact string that the server is receiving from the client and failing on. That might help you find the problem. thanks, --Barry Thomas O'Dowd wrote: Hi all, I'm currently chasing down a bug. Wonder if anyone can throw some light on it. I get the following exception. An I/O error has occured while flushing the output - Exception: java.io.IOException: Connection reset by peer Stack Trace: java.io.IOException: Connection reset by peer at java.net.SocketOutputStream.socketWrite(Native Method) at java.net.SocketOutputStream.write(SocketOutputStream.java:83) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:72) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:130) at org.postgresql.PG_Stream.flush(PG_Stream.java:414) at org.postgresql.Connection.ExecSQL(Connection.java:479) at org.postgresql.jdbc2.Statement.execute(Statement.java:294) at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78) at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122) And in the postgresql.log file I get... ERROR: Unterminated quoted string FATAL 1: Socket command type unknown But I'm pretty sure that my strings are quoted properly. That is to say that there are about 90 escaped single quotes in a string I'm inserting also though. Anyone seen this before? I'm currently using a version of the driver that I compiled from cvs on the 18th of Jun. Was anything patched since that might effect this? Anyway, I've been digging around for quite a while now so I thought I'd shoot the list a mail before going to bed. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[JDBC] Re: Escape Processing problems
Hi Barry,
I found the part in the spec that talks about escape processing for
date and time. Thanks for pointing that out. I believe the drivers
implementation is wrong as it is a) changing random text data instead
of data of a defined format to its escape sequence and b) it can throw
a out of bounds exception if there is no closing }.
Perhaps, I'll write a patch later in the day to fix this for at least
the date escape as that is the only one that is implemented.
So just to clarify my understanding of what should happen...
SELECT a, b from c where t={d '-mm-dd'} and a=1
should be changed to:
SELECT a, b from c where t='-mm-dd' and a=1
and something like
INSERT INTO test VALUES('don't change this {d '-mm-dd'} as its correct.
should be left alone. ie, if we're in a string escape processing should
not be done. Right now it looks for anything with {d in the query and
starts changing it.
Cheers,
Tom.
On Tue, Aug 28, 2001 at 12:55:19PM -0700, Barry Lind wrote:
Thomas,
This is doing exactly what it is supposed to according to the JDBC Spec.
In fact there are a bunch of other '{X }' things that the Spec
defines that it should also be handling.
thanks,
--Barry
Thomas O'Dowd wrote:
Hi all,
The Connection.EscapeSQL() routine is broken IMHO . Actually, I'm not
sure why it is trying to fix strings starting with {d in the first place?
Anyway, currently I've turned it off in the statement with
setEscapeProcessing(false)
The problem I'm having is that {d appears in the data that I'm trying
to store and its not a date. So data like the following...
.blahhh}; {blahhh }; {docs=};
is turning into...
.blahhh}; {blahhh }; ocs= ;
^^ ^
What's more is if I have something like {d. and there is no ending
brace, it will throw a StringIndexOutOfBoundsException as the return
value of the indexOf() looking for the closing brace will not find one
and thus setCharAt() will use an illegal index of -1 :(
The routine is below for reference... Can anyone explain why it is trying
to do this on me in the first place. I would think escape processing would
do something a little different like watching my single quotes etc.
public String EscapeSQL(String sql) {
//if (DEBUG) { System.out.println (parseSQLEscapes called); }
// If we find a {d, assume we have a date escape.
//
// Since the date escape syntax is very close to the
// native Postgres date format, we just remove the escape
// delimiters.
//
// This implementation could use some optimization, but it has
// worked in practice for two years of solid use.
int index = sql.indexOf({d);
while (index != -1) {
//System.out.println (escape found at index: + index);
StringBuffer buf = new StringBuffer(sql);
buf.setCharAt(index, ' ');
buf.setCharAt(index + 1, ' ');
buf.setCharAt(sql.indexOf('}', index), ' ');
sql = new String(buf);
index = sql.indexOf({d);
}
//System.out.println (modified SQL: + sql);
return sql;
}
Cheers,
Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Re: Escape Processing problems
On Wed, Aug 29, 2001 at 09:18:26AM +0900, Thomas O'Dowd wrote:
INSERT INTO test VALUES('don't change this {d '-mm-dd'} as its correct.
Opps. I didn't backslash the single quotes here but you know what I mean...
Tom.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [JDBC] Re: Unterminated quoted string error.
I found problem. My string has a null character in the middle of it. I noticed from the Connection.java code that the null character idicates end of query so I guess that is what is happening. I'll strip out my null strings in the mean time as they are not needed before sending them to the driver but I'm wondering if the preparedStatement.setString() shouldn't escape nulls or something. It already escapes single quotes and backslashes. What do people think? Cheers, Tom. On Wed, Aug 29, 2001 at 08:53:31AM +0900, Thomas O'Dowd wrote: Thanks Barry, I turned on debugging in postgresql. I found that the query is being truncated and is not fully making it to the backend, therefore I'm getting the Unterminated string error. I'll have a look into why and report back if I find anything. Cheers, Tom. On Tue, Aug 28, 2001 at 12:56:50PM -0700, Barry Lind wrote: Thomas, If you turn on debug messages on the server to print out the SQL statements it receives you should be able to get the exact string that the server is receiving from the client and failing on. That might help you find the problem. thanks, --Barry Thomas O'Dowd wrote: Hi all, I'm currently chasing down a bug. Wonder if anyone can throw some light on it. I get the following exception. An I/O error has occured while flushing the output - Exception: java.io.IOException: Connection reset by peer Stack Trace: java.io.IOException: Connection reset by peer at java.net.SocketOutputStream.socketWrite(Native Method) at java.net.SocketOutputStream.write(SocketOutputStream.java:83) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:72) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:130) at org.postgresql.PG_Stream.flush(PG_Stream.java:414) at org.postgresql.Connection.ExecSQL(Connection.java:479) at org.postgresql.jdbc2.Statement.execute(Statement.java:294) at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78) at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122) And in the postgresql.log file I get... ERROR: Unterminated quoted string FATAL 1: Socket command type unknown But I'm pretty sure that my strings are quoted properly. That is to say that there are about 90 escaped single quotes in a string I'm inserting also though. Anyone seen this before? I'm currently using a version of the driver that I compiled from cvs on the 18th of Jun. Was anything patched since that might effect this? Anyway, I've been digging around for quite a while now so I thought I'd shoot the list a mail before going to bed. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [JDBC] Re: Unterminated quoted string error.
Hi Barry, I looked in the postgresql documentation and couldn't find any mention of a bytea type. Well actually, I found .. $ grep -i bytea * bki-commands.html:bytea/TT catalog-pg-proc.html:bytea/TT xfunc-c.html:bytea/TD xfunc-c.html:(bytea *)/TD but no real info on what it is, or no mention of it in the main types page. Anyway, I think I'm fine for now, stipping the null chars from my data. Tom. On Tue, Aug 28, 2001 at 09:17:19PM -0700, Barry Lind wrote: Thomas, The text datatypes in postgres (i.e. char, varchar, text) do not support storing null characters. If your data contains nulls then you need to use the binary datatype bytea. Unfortunately the JDBC drivers do not currently support the bytea datatype. thanks, --Barry Thomas O'Dowd wrote: I found problem. My string has a null character in the middle of it. I noticed from the Connection.java code that the null character idicates end of query so I guess that is what is happening. I'll strip out my null strings in the mean time as they are not needed before sending them to the driver but I'm wondering if the preparedStatement.setString() shouldn't escape nulls or something. It already escapes single quotes and backslashes. What do people think? Cheers, Tom. On Wed, Aug 29, 2001 at 08:53:31AM +0900, Thomas O'Dowd wrote: Thanks Barry, I turned on debugging in postgresql. I found that the query is being truncated and is not fully making it to the backend, therefore I'm getting the Unterminated string error. I'll have a look into why and report back if I find anything. Cheers, Tom. On Tue, Aug 28, 2001 at 12:56:50PM -0700, Barry Lind wrote: Thomas, If you turn on debug messages on the server to print out the SQL statements it receives you should be able to get the exact string that the server is receiving from the client and failing on. That might help you find the problem. thanks, --Barry Thomas O'Dowd wrote: Hi all, I'm currently chasing down a bug. Wonder if anyone can throw some light on it. I get the following exception. An I/O error has occured while flushing the output - Exception: java.io.IOException: Connection reset by peer Stack Trace: java.io.IOException: Connection reset by peer at java.net.SocketOutputStream.socketWrite(Native Method) at java.net.SocketOutputStream.write(SocketOutputStream.java:83) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:72) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:130) at org.postgresql.PG_Stream.flush(PG_Stream.java:414) at org.postgresql.Connection.ExecSQL(Connection.java:479) at org.postgresql.jdbc2.Statement.execute(Statement.java:294) at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78) at org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122) And in the postgresql.log file I get... ERROR: Unterminated quoted string FATAL 1: Socket command type unknown But I'm pretty sure that my strings are quoted properly. That is to say that there are about 90 escaped single quotes in a string I'm inserting also though. Anyone seen this before? I'm currently using a version of the driver that I compiled from cvs on the 18th of Jun. Was anything patched since that might effect this? Anyway, I've been digging around for quite a while now so I thought I'd shoot the list a mail before going to bed. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[JDBC] Re: Escape Processing problems
Hi all,
I found some time this morning to write and test a new EscapeSQL() method.
I didn't make a patch for the driver yet as I'd like to hear some
comments. It's a tad longer than the original code as it only replaces
escape codes which appear in the SQL code and not inside strings.
It's attached as a separate java program which you can run to test with
various strings. Let me know if you think it is okay. It seems to work
with what I've tested it with.
Example:
$ /usr/local/java/jdk1.3/bin/java esc insert into test values ({d '2000-12-01'},
'string of\\ \' {d }', {t '12:12:12'})
insert into test values ( '2000-12-01', 'string of\\ \' {d }', {t '12:12:12'})
Do you think we should expand it to handle the other codes like {t and {ts ?
The old routine only handles {d.
Tom.
On Tue, Aug 28, 2001 at 06:24:06PM -0700, Barry Lind wrote:
Thomas,
I can see where there might be bugs in the implementation of this
escaping stuff. I don't think it is used very often. I believe your
understanding of how this is supposed to work is correct.
thanks,
--Barry
Thomas O'Dowd wrote:
Hi Barry,
I found the part in the spec that talks about escape processing for
date and time. Thanks for pointing that out. I believe the drivers
implementation is wrong as it is a) changing random text data instead
of data of a defined format to its escape sequence and b) it can throw
a out of bounds exception if there is no closing }.
Perhaps, I'll write a patch later in the day to fix this for at least
the date escape as that is the only one that is implemented.
So just to clarify my understanding of what should happen...
SELECT a, b from c where t={d '-mm-dd'} and a=1
should be changed to:
SELECT a, b from c where t='-mm-dd' and a=1
and something like
INSERT INTO test VALUES('don't change this {d '-mm-dd'} as its correct.
should be left alone. ie, if we're in a string escape processing should
not be done. Right now it looks for anything with {d in the query and
starts changing it.
Cheers,
Tom.
On Tue, Aug 28, 2001 at 12:55:19PM -0700, Barry Lind wrote:
Thomas,
This is doing exactly what it is supposed to according to the JDBC Spec.
In fact there are a bunch of other '{X }' things that the Spec
defines that it should also be handling.
thanks,
--Barry
Thomas O'Dowd wrote:
Hi all,
The Connection.EscapeSQL() routine is broken IMHO . Actually, I'm not
sure why it is trying to fix strings starting with {d in the first place?
Anyway, currently I've turned it off in the statement with
setEscapeProcessing(false)
The problem I'm having is that {d appears in the data that I'm trying
to store and its not a date. So data like the following...
.blahhh}; {blahhh }; {docs=};
is turning into...
.blahhh}; {blahhh }; ocs= ;
^^ ^
What's more is if I have something like {d. and there is no ending
brace, it will throw a StringIndexOutOfBoundsException as the return
value of the indexOf() looking for the closing brace will not find one
and thus setCharAt() will use an illegal index of -1 :(
The routine is below for reference... Can anyone explain why it is trying
to do this on me in the first place. I would think escape processing would
do something a little different like watching my single quotes etc.
public String EscapeSQL(String sql) {
//if (DEBUG) { System.out.println (parseSQLEscapes called); }
// If we find a {d, assume we have a date escape.
//
// Since the date escape syntax is very close to the
// native Postgres date format, we just remove the escape
// delimiters.
//
// This implementation could use some optimization, but it has
// worked in practice for two years of solid use.
int index = sql.indexOf({d);
while (index != -1) {
//System.out.println (escape found at index: + index);
StringBuffer buf = new StringBuffer(sql);
buf.setCharAt(index, ' ');
buf.setCharAt(index + 1, ' ');
buf.setCharAt(sql.indexOf('}', index), ' ');
sql = new String(buf);
index = sql.indexOf({d);
}
//System.out.println (modified SQL: + sql);
return sql;
}
Cheers,
Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
public class esc
{
private static final short CODE = 0;
private static final short STRING = 1;
private static final short BACKSLASH =2;
private static final short ESC_DATE = 3;
public static String EscapeSQL(String sql)
{
// look through the sql statement for escape codes. Since escape
// codes can only appear in SQL CODE, we keep track of if we are
// inside or outside a string
Re: [JDBC] Re: Escape Processing problems
On Sat, Sep 01, 2001 at 11:02:47PM +0200, Rene Pijlman wrote:
On Thu, 30 Aug 2001 11:46:16 +0900, Thomas O'Dowd wrote:
I found some time this morning to write and test a new EscapeSQL()
method. I didn't make a patch for the driver yet as I'd like to
hear some comments.
To what extent is this implementation JDBC compliant?
The spec is in section 40.1.5 of the JDBC API Tutorial and
Reference, 2nd edition. Its also documented on
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame11.html#7105.
I think it is as compliant as what's in the current driver. It just
solves the corruption problem with some strings containing {d. If you
want to support {t and {ts then that should also be easily added. As
for supporting the other escapes sequences mentioned in section 11, I'm
not familar with the usage enough to comment on how it should be implemented.
My interest in patching this method is to solve the current data
corruption problems that I'm getting with the current implementation.
Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Re: Escape Processing problems
Hi Bruce,
I guess that wasn't exactly a patch that I sent in but rather a test
program with the new routine embeded so that other people could try
it out easily.
I'll mail in the patch later. I'm a little distracted right now with
something else. For the moment just delete the patch that you added
to the unapplied list.
Cheers,
Tom.
On Tue, Sep 04, 2001 at 10:57:10AM -0400, Bruce Momjian wrote:
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
Hmmm, after a little more testing, I fixed a problem with backslashes.
New code attached.
Tom.
On Thu, Aug 30, 2001 at 11:46:16AM +0900, Thomas O'Dowd wrote:
Hi all,
I found some time this morning to write and test a new EscapeSQL() method.
I didn't make a patch for the driver yet as I'd like to hear some
comments. It's a tad longer than the original code as it only replaces
escape codes which appear in the SQL code and not inside strings.
It's attached as a separate java program which you can run to test with
various strings. Let me know if you think it is okay. It seems to work
with what I've tested it with.
Example:
$ /usr/local/java/jdk1.3/bin/java esc insert into test values ({d
'2000-12-01'}, 'string of\\ \' {d }', {t '12:12:12'})
insert into test values ( '2000-12-01', 'string of\\ \' {d }', {t
'12:12:12'})
Do you think we should expand it to handle the other codes like {t and {ts ?
The old routine only handles {d.
Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
--
Thomas O'Dowd. - Nooping - http://nooper.com
[EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[JDBC] Patch for Statement Escape Processing problems
Hi all,
I'm attaching a patch which fixes the corruption in strings caused
by escape processing in the SQL statement. I've tested this for a
while now and it appears to work well. Previously string data
with {d was getting corrupt as the {d was being stripped regardless
of whether it was an escape code or not.
I also added checking for time and timestamp escape processing strings
as per 11.3 in the specification. The patch is against the latest
CVS.
Cheers,
Tom.
Index: org/postgresql/Statement.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Statement.java,v
retrieving revision 1.2
diff -c -r1.2 Statement.java
*** org/postgresql/Statement.java 2001/08/10 14:42:07 1.2
--- org/postgresql/Statement.java 2001/09/10 08:16:48
***
*** 39,44
--- 39,49
protected boolean escapeProcessing = true;
+ // Static variables for parsing SQL when escapeProcessing is true.
+ private static final short IN_SQLCODE = 0;
+ private static final short IN_STRING = 1;
+ private static final short BACKSLASH =2;
+ private static final short ESC_TIMEDATE = 3;
public Statement() {
}
***
*** 226,251
}
/**
! * This is an attempt to implement SQL Escape clauses
! */
! protected static String escapeSQL(String sql) {
! // If we find a {d, assume we have a date escape.
! //
! // Since the date escape syntax is very close to the
! // native Postgres date format, we just remove the escape
! // delimiters.
! //
! // This implementation could use some optimization, but it has
! // worked in practice for two years of solid use.
! int index = sql.indexOf({d);
! while (index != -1) {
! StringBuffer buf = new StringBuffer(sql);
! buf.setCharAt(index, ' ');
! buf.setCharAt(index + 1, ' ');
! buf.setCharAt(sql.indexOf('}', index), ' ');
! sql = new String(buf);
! index = sql.indexOf({d);
! }
! return sql;
}
}
--- 231,306
}
/**
! * Filter the SQL string of Java SQL Escape clauses.
! *
! * Currently implemented Escape clauses are those mentioned in 11.3
! * in the specification. Basically we look through the sql string for
! * {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
! * them, we just strip the escape part leaving only the xxx part.
! * So, something like select * from x where d={d '2001-10-09'} would
! * return select * from x where d= '2001-10-09'.
! */
! protected static String escapeSQL(String sql)
! {
! // Since escape codes can only appear in SQL CODE, we keep track
! // of if we enter a string or not.
! StringBuffer newsql = new StringBuffer();
! short state = IN_SQLCODE;
!
! int i = -1;
! int len = sql.length();
! while(++i len)
! {
! char c = sql.charAt(i);
! switch(state)
! {
! case IN_SQLCODE:
! if(c == '\'')// start of a string?
! state = IN_STRING;
! else if(c == '{')// start of an escape code?
! if(i+1 len)
! {
! char next = sql.charAt(i+1);
! if(next == 'd')
! {
! state = ESC_TIMEDATE;
! i++;
! break;
! }
! else if(next == 't')
! {
! state = ESC_TIMEDATE;
! i += (i+2 len sql.charAt(i+2) == 's') ? 2 : 1;
! break;
! }
! }
! newsql.append(c);
! break;
!
! case IN_STRING:
! if(c == '\'') // end of string?
! state = IN_SQLCODE;
! else if(c == '\\')// a backslash?
! state = BACKSLASH;
!
! newsql.append(c);
! break;
!
! case BACKSLASH:
! state = IN_STRING;
!
! newsql.append(c);
! break;
!
! case ESC_TIMEDATE:
! if(c == '}')
! state = IN_SQLCODE; // end of escape code.
! else
! newsql.append(c);
! break;
! } // end switch
! }
!
! return newsql.toString();
}
}
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [JDBC] Patch for Statement Escape Processing problems
Hi all,
Wonder if anyone had a look at this patch yet? I've been using my locally
patched version for a while now and it works fine. Again just to make it
clear what it does...
- It fixes an Exception which will be thrown with the following insert.
insert into values ('{d');
- The current statement implementation also corrupts string data with '{d'
characters by removing them. This patch fixes this.
- The patch also adds support for {t and {ts escapes which are covered
in section 11.3 of the specification. Does anyone on the list use
these types of escapes???
Right now I'm mostly after fixing the corruption of strings as a lot of
my data contains the {d character combination and its quite annoying.
For those of you who don't know, the escapeSQL() method is called
everytime you execute a Statement or PreparedStatement if setEscapeProcessing
is set true (which is the default).
Tom.
On Mon, Sep 10, 2001 at 05:27:17PM +0900, Thomas O'Dowd wrote:
Hi all,
I'm attaching a patch which fixes the corruption in strings caused
by escape processing in the SQL statement. I've tested this for a
while now and it appears to work well. Previously string data
with {d was getting corrupt as the {d was being stripped regardless
of whether it was an escape code or not.
I also added checking for time and timestamp escape processing strings
as per 11.3 in the specification. The patch is against the latest
CVS.
Cheers,
Tom.
Index: org/postgresql/Statement.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Statement.java,v
retrieving revision 1.2
diff -c -r1.2 Statement.java
*** org/postgresql/Statement.java 2001/08/10 14:42:07 1.2
--- org/postgresql/Statement.java 2001/09/10 08:16:48
***
*** 39,44
--- 39,49
protected boolean escapeProcessing = true;
+ // Static variables for parsing SQL when escapeProcessing is true.
+ private static final short IN_SQLCODE = 0;
+ private static final short IN_STRING = 1;
+ private static final short BACKSLASH =2;
+ private static final short ESC_TIMEDATE = 3;
public Statement() {
}
***
*** 226,251
}
/**
! * This is an attempt to implement SQL Escape clauses
! */
! protected static String escapeSQL(String sql) {
! // If we find a {d, assume we have a date escape.
! //
! // Since the date escape syntax is very close to the
! // native Postgres date format, we just remove the escape
! // delimiters.
! //
! // This implementation could use some optimization, but it has
! // worked in practice for two years of solid use.
! int index = sql.indexOf({d);
! while (index != -1) {
! StringBuffer buf = new StringBuffer(sql);
! buf.setCharAt(index, ' ');
! buf.setCharAt(index + 1, ' ');
! buf.setCharAt(sql.indexOf('}', index), ' ');
! sql = new String(buf);
! index = sql.indexOf({d);
! }
! return sql;
}
}
--- 231,306
}
/**
! * Filter the SQL string of Java SQL Escape clauses.
! *
! * Currently implemented Escape clauses are those mentioned in 11.3
! * in the specification. Basically we look through the sql string for
! * {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
! * them, we just strip the escape part leaving only the xxx part.
! * So, something like select * from x where d={d '2001-10-09'} would
! * return select * from x where d= '2001-10-09'.
! */
! protected static String escapeSQL(String sql)
! {
! // Since escape codes can only appear in SQL CODE, we keep track
! // of if we enter a string or not.
! StringBuffer newsql = new StringBuffer();
! short state = IN_SQLCODE;
!
! int i = -1;
! int len = sql.length();
! while(++i len)
! {
! char c = sql.charAt(i);
! switch(state)
! {
! case IN_SQLCODE:
! if(c == '\'')// start of a string?
! state = IN_STRING;
! else if(c == '{')// start of an escape code?
! if(i+1 len)
! {
! char next = sql.charAt(i+1);
! if(next == 'd')
! {
! state = ESC_TIMEDATE;
! i++;
! break;
! }
! else if(next == 't')
! {
! state = ESC_TIMEDATE;
! i += (i+2 len sql.charAt(i+2) == 's
Re: [JDBC] TIMESTAMP
Can you post a sample of your code that reproduces the problem. What timezone are you in? I think there is a problem with some timezones which are on the half hour. Can you post the exception you get also. Tom. On Mon, Oct 01, 2001 at 02:15:39PM -0700, David Siebert wrote: I am having problems with getTimestamp. I keep getting an invalid format error. I am using the latest JDBC driver. Help please. I have searched the arcives and I have found others seem to have the same issue but none of the fixes provided seemed to work. __ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] [PATCHES] Ant configuration
* In Ant the build file will be much less complex than a Makefile with same functionality. And same time in Ant it is much easier to check local Java internal setup. I agree that this is true, but I think a much better job can be done to minimize the added complexity. I have looked at the old makefile and it was quite complex. But I have ideas on how to reduce that complexity. Basically I want to trade off a little more complexity on the part of the developers of jdbc, for less complexity for users to install and build. I don't mind either way really, but I don't see why the makefile should need to be so complex. One option is also since Postgresql uses configure to build, we can use configure to help us detect whatever we need to if --with-java is turned on and set suitable options/paths etc in the makefile. That said I haven't really looked at the current build process for the driver. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Does PG's JDBC support prepared statements at all?
On Sat, Oct 20, 2001 at 11:14:22PM -, Dr. Evil wrote: There is a bunch of documentation for prepared statements in PG's JDBC, it seems that the only thing prepared statements do is throw exceptions. Here's some code I'm trying: String newvalue = This is a new value; int accountnumber = 54; String qstring = UPDATE foo SET message = '?' WHERE number = ?; PreparedStatement st = db.prepareStatement(qstring); st.setString(1, newvalue); st.setInt(2, accountnumber); st.execute(); st.clearParameters(); st.close(); and I always get a Parameter index out of range error, which seems impossible. Any idea what's going on? You shouldn't quote the ? for the string. By calling the setString() method, it will add the quotes for you. So I guess what is happening is that the preparedstatement parser ignores quoted question marks and just finds 1 variable, when you call setstring on 1 it sets the number= part, and then when you call setInt(2) you are getting the index out of range. The proper qstring should be: UPDATE foo SET message = ? WHERE number = ? Cheers, Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Does PG's JDBC support prepared statements at all?
On Sun, Oct 21, 2001 at 02:06:21PM +0200, Rene Pijlman wrote: On Sun, 21 Oct 2001 11:37:29 +0900, you wrote: So I guess what is happening is that the preparedstatement parser ignores quoted question marks I hope not. I hope it sets the field to a literal '?' :-) I meant ignores them in terms of not handling them as normal variable placement holders or whatever the correct terminology is :) But I guess that wasn't crystal clear in the way that I phrased it. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com [EMAIL PROTECTED] - Testing - http://nooper.co.jp/labs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
