RE: [JDBC] next() and PreparedStatement
i believe that when you close the stmt, the rs is automatically closed. you have to leave the stmt open until you're done with the rs. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mano lito Sent: Tuesday, August 28, 2001 7:14 AM To: [EMAIL PROTECTED] Subject: [JDBC] next() and PreparedStatement Hi! We are developing a project using PostgreSQL 7.1.2 and JDBC drivers to exchange information with the database. Here is a piece of code to explain our problem: --- PreparedStatement pstmt; Connection connection; ResultSet rs = null; String query = new String( SELECT count(*) FROM foo_table WHERE foo_code=?; ); pstmt = connection.prepareStatement( query ); pstmt.setString( 1, foo ); rs = pstmt.executeQuery( query ); pstmt.close(); return rs.next(); -- the next() method will cause a java.lang.NullPointerException when the ResultSet comes from a PreparedStatement!!! Is this problem originated by the JDBC driver? We downloaded it from jdbc.fastcrypt.com but this error occurs again and again and in jdbc.postgresql.org we see 7.0.x drivers available but none 7.1 nor 7.1.2 exists... We'd appreciate any help you could give us.If your address is not the correct place to ask this questions please tell us. Thanks in advance. _ Descargue GRATUITAMENTE MSN Explorer en http://explorer.msn.es/intl.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [JDBC] Fastpath error on solaris 2.8 pgsql 7.1.3
So I wanted to thank you. Indeed I did have a thread problem in my application that caused the same connection to be used by more than one thread at time. Thus the occassional problem due to transactions being out of sync. t.r. missner level(3) communications Chris -- you can explicity call myStatement.execute(BEGIN); and myStatement.execute(COMMIT); or you can simply use myConnection.setAutoCommit(true); or false when finished. This accomplishes the same thing as BEGIN/COMMIT. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Monday, August 27, 2001 2:43 PM To: Missner, T. R. Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] Fastpath error on solaris 2.8 pgsql 7.1.3 [EMAIL PROTECTED] writes: FastPath call returned ERROR: lo_write: invalid large obj descriptor (0) Usually this indicates that you didn't have the lo_open ... lo_write ... lo_close sequence wrapped in a transaction block (BEGIN/COMMIT SQL commands). Since it's erratic for you, I'd bet that some of your application control paths have the BEGIN and some don't. regards, tom lane ---(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
[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.
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] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[JDBC] Re: next() and PreparedStatement
i believe that when you close the stmt, the rs is automatically closed. you have to leave the stmt open until you're done with the rs. Exactly. If you read the Java documentation for interface java.sql.Statement, method close(), it tells you the Resultset, if any, also is closed on that call. PreparedStatement extends Statement, CallableStatement extends PreparedStatement. It's important to notice that the Postgres implementation of Resultset et al is meant to work as described in the standard Java documentation; that's the whole point of the interfaces. Thus a lot of these questions can be answered by carefully reading through the standard Java documentation. Yours, ///Jens Carlberg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[JDBC] JDBC version
I've been experimenting with some versions of PostgreSQL JDBC trying to find
a version that suits the our application:
1) It has to support portuguese characters (like áéíóú etc) directly in SQL
queries.
2) It is desiderable that it supports SQL statements longer than 8k.
Unfortunately the only version I found that seems to address both problems
above (version 7.1-1.2 from jdbc.postgresql.org) seems to be broken in
relation to data types (not gone deeper into that, yet).
Anyway what I would like to ask is if there is a way to use Large Object
code to overcome statement length restrictions in the 7.0 series drivers
(which work ok except for that). A detail is that I use FUNCTIONs for
performing INSERT/UPDATE (as if the FUNCTION was a stored procedure, that
does UPDATE or INSERT when appropriate):
SELECT registerData('my_primary_key', 'a very very long text string')
Is there a way I can do that using
PreparedStatement ps = conn.prepareStatement(SELECT registerData(?,?));
ps.SetString(1, my_primary_key);
ps.SetString(2, a very very very very long string);
That way I could use a more stable driver with long datasets.
Is it possible? Are the LOB functions stable?
Thanks a lot!
--
Ricardo Pardini
[EMAIL PROTECTED]
---(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] JDBC version
I've read this twice and I have great difficulty understanding
what the real problem is. Your posting touches on so many
subjects: character encoding, statement length, driver versions,
large objects, calling functions, large parameter values,
stability...
Could you please summarize what the problem is when you use the
latest and greatest driver (e.g. jdbc7.1-1.3.jar from
http://jdbc.fastcrypt.com) and when you use JDBC in the way its
intended?
Regards,
René Pijlman
On Tue, 28 Aug 2001 17:46:05 -0300, you wrote:
I've been experimenting with some versions of PostgreSQL JDBC trying to find
a version that suits the our application:
1) It has to support portuguese characters (like áéíóú etc) directly in SQL
queries.
2) It is desiderable that it supports SQL statements longer than 8k.
Unfortunately the only version I found that seems to address both problems
above (version 7.1-1.2 from jdbc.postgresql.org) seems to be broken in
relation to data types (not gone deeper into that, yet).
Anyway what I would like to ask is if there is a way to use Large Object
code to overcome statement length restrictions in the 7.0 series drivers
(which work ok except for that). A detail is that I use FUNCTIONs for
performing INSERT/UPDATE (as if the FUNCTION was a stored procedure, that
does UPDATE or INSERT when appropriate):
SELECT registerData('my_primary_key', 'a very very long text string')
Is there a way I can do that using
PreparedStatement ps = conn.prepareStatement(SELECT registerData(?,?));
ps.SetString(1, my_primary_key);
ps.SetString(2, a very very very very long string);
That way I could use a more stable driver with long datasets.
Is it possible? Are the LOB functions stable?
Thanks a lot!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[JDBC] Patch for broken JDBC's getColumn()
Hi,
Attached is a patch for JDBC's getColumn() function that was broken /
flawed in the following ways:
1. Only returned columns that had a default value defined, rather than all
columns in a table
2. Used 2 * N + 1 queries to find out attributes, comments and typenames
for N columns.
By using some outer join syntax it is possible to retrieve all necessary
information in just one SQL statement. This means this version is only
suitable for PostgreSQL = 7.1. Don't know whether that's a problem.
I've tested this function with current sources and 7.1.3 and patched both
jdbc1 and jdbc2. I haven't compiled nor tested the jdbc1 version though, as
I have no JDK 1.1 available.
Note the discussion in http://fts.postgresql.org/db/mw/msg.html?mid=1029626
regarding differences in obtaining comments on database object in 7.1 and
7.2. I was unable to use the following syntax (or similar ones):
select
...,
description
from
...
left outer join col_description(a.attrelid, a.attnum) description
order by
c.relname, a.attnum;
(the error was parse error at or near '(') so I had to paste the actual
code for the col_description function into the left outer join. Maybe
someone who is more knowledgable about outer joins might provide me with a
better SQL statement.
Please review.
Regards,
Jeroen
DatabaseMetaData.diff.gz
---(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]
Re: [JDBC] Re: Proposal to fix Statement.executeBatch()
No problem. Just checking. Patch will remain in the queue and be applied. Bruce, I think the existing patch can be applied as is. The issues I raised below are further improvements in the functionality that can be done and don't directly relate to the patch that was submitted. Sorry if I confused things. --Barry Bruce Momjian wrote: Can someone suggest what is to be done with the propsed patch? What exactly is the behaviour of the backend in that scenario? Does it commit every separate SQL statement in the semicolon-separated list, or does it commit the list as a whole? Does it abort processing the statement list when an error occurs in one statement? And if it continues, does it return an error when only one statement in the middle of the list had an error? I do not know what the server does if you have autocommit enabled and you issue multiple statements in one try. However, I would be OK with the driver issuing the statements one by one with autocommit on. If you are running in this mode you just wouldn't get any performance improvement. However, it would mean a change in behaviour of the driver that may break existing JDBC applications: the driver will no longer return update counts for all statements in a batch like it currently does, it will return unknown for most statements. I'm not sure if the performance improvement justifies this non-backwardly-compatible change, though I agree this is the intention of the feature. What do you think? I wouldn't worry about this 'change in behavior' because if the caller is JDBC complient it should be coded to handle the new behavior as it is complient with the spec. thanks, --Barry Rene Pijlman wrote: On Mon, 27 Aug 2001 11:07:55 -0700, you wrote: [executeBatch() implemented as one round trip] Here is how I would suggest this be done in a way that is spec compliant (Note: that I haven't looked at the patch you submited yet, so forgive me if you have already done it this way, but based on your comments in this email, my guess is that you have not). Indeed, I have not implemented this. Statements should be batched together in a single statement with semicolons separating the individual statements (this will allow the backend to process them all in one round trip). The result array should return an element with the row count for each statement, however the value for all but the last statement will be '-2'. (-2 is defined by the spec to mean the statement was processed successfully but the number of affected rows is unknown). Ah, I see. I hadn't thought of that solution. In the event of an error, then the driver should return an array the size of the submitted batch with values of -3 for all elements. -3 is defined by the spec as the corresponding statement failed to execute successfully, or for statements that could not be processed for some reason. Since in postgres when one statement fails (in non-autocommit mode), the entire transaction is aborted this is consistent with a return value of -3 in my reading of the spec. Not quite. A statement in a batch may also fail because its a succesful SELECT as far as the server is concerned (can't have select's in a batch). But that situation can also be handled correctly by setting the update count for that particular statement to -3. Its then up to the application to decide if it wants to rollback, I would say. But what to do when an error occurs with autocommit enabled? This is not recommended, but allowed by the spec, if I understand it correctly. What exactly is the behaviour of the backend in that scenario? Does it commit every separate SQL statement in the semicolon-separated list, or does it commit the list as a whole? Does it abort processing the statement list when an error occurs in one statement? And if it continues, does it return an error when only one statement in the middle of the list had an error? I believe this approach makes the most sense because: 1) It implements batches in one round trip (the intention of the feature) 2) It is complient with the standard 3) It is complient with the current functionality of the backend If we can come up with an acceptable solution for an error with autocommit enabled, I agree. Otherwise, I'm not sure. However, it would mean a change in behaviour of the driver that may break existing JDBC applications: the driver will no longer return update counts for all statements in a batch like it currently does, it will return unknown for most statements. I'm not sure if the performance improvement justifies this non-backwardly-compatible change, though I agree this is the intention of the feature. What do you think? Regards, Ren? Pijlman ---(end of broadcast)--- TIP 3: if posting/reading through Usenet,
[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])
[JDBC] Re: Escape Processing problems
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.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[JDBC] Regarding vacuumdb
hi, I am getting the following error while doing vacuumdb, ERROR: mdopen: couldn't open test1: No such file or directory vacuumdb: database vacuum failed on db1. Here 'db1' is the database and 'test1' is a table. When, displaying the structure of the table 'test1', it comes correctly. But I can't drop the table. What could be wrong? Any help. Thanks shan. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Re: Unterminated quoted string error.
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] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
