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] List archives moved and cleaned up ...
[ repeat posting to -announce ] Finally figuring that enough is enough, I've been spending the past few days working on the list archives ... I've reformatted, so far, the following lists into a cleaner format: pgsql-interfaces pgsql-jdbc pgsql-odbc pgsql-novice pgsql-hackers pgsql-sql pgsql-bugs pgsql-general pgadmin-hackers pgadmin-support With more lists to be worked on over the next few days ... Major changes include the following: Replaced the wide banner in the center with two smaller, 120x120 banners in the corners ... Provide a search facility incorporated into each page that searches the mhonarc pages themselves ... Change the colors to better match the main site ... Moved the archives to its own URL/Domain so that it is no longer part of the general mirror of the site ... There is still alot of work that I'm planning on doing on this, but I want to get all of the archives moved first ... To access any of the archives that have been moved, go to: http://archives.postgresql.org/list I've been modifying the links from the main web site for those lists that I've moved, as I've moved them, so getting there through 'normal channels' should also work ... Once finished, there will also be links to the OpenFTS search facility that we have online, which uses a different way of formatting/displaying the messages, so you will have the choice of using either ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Status of JDBC test suite?
On Sun, Aug 26, 2001 at 09:14:49PM +0200, Rene Pijlman wrote: Before I dive into this and fix the test suite, I just wanted to check if someone else has already looked into this. Any information about the status of the test suite? Not sure if it's the same thing, but I've noticed that often, the first time the test harness runs a particular test bucket, most or all of the tests fail. The second and third passes return work properly (insofar as there's support in the JDBC driver/PostgreSQL). I haven't had time to look into it yet. Liam -- Liam Stewart :: Red Hat Canada, Ltd. :: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[JDBC] max connections
Hi, The documentation states that the Connection object is thread-safe... I took this to mean I can create a connection as a singleton and pass it out to all my threads with no worries. When I did this I noticed that a SimpleObjectPool was being created, and I would occasionally get a max connections exceeded error from the database. I assume this means the implementation actually creates a connection pool and passes connections out to threads as needed. My question is... what is the proper way to handle postgres connections in a multithreaded environment? is it OK to create a singleton of the connection? if so, is there a way to limit the number of connections the pool makes to the database so that the maximum is not exceeded? ___ Simon Stanlake Harmony Integrated Systems Inc. http://www.hi.ca ---(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: Couple of patches for jdbc driver
On Thu, Aug 23, 2001 at 06:42:56PM -0700, Barry Lind wrote: So going forward, whose responsibility is it to maintain this file? Is it the responsibility of the person submitting the patch (i.e. each patch should contain an update to CHANGELOG), or is it the responsibility of the person applying the patch? I think that whenever a person submits a patch, they should include an associated changelog entry. It's quite easy to do with emacs (M-x add-change-log-entry). The CVS commit message can then be taken/adapted from the changelog patch. -- Liam Stewart :: Red Hat Canada, Ltd. :: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] Re: Unterminated quoted string error.
Thomas, You are correct about the poor documentation for bytea. I hear this is fixed in 7.2 docs, but haven't verified. I learned about it myself by looking at the internal pg_* tables and seeing how they used it. I'm glad to hear that you have a workaround for your original issue. thanks, --Barry Thomas O'Dowd wrote: 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] ---(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] Status of JDBC test suite?
On Sun, Aug 26, 2001 at 09:56:09PM +0200, Anders Bengtsson wrote: On Sun, 26 Aug 2001, Rene Pijlman wrote: This is probably true, but once you have your environment setup (including a test database, password etc.) its just a matter of typing make check. Would a brief How-to help? Yes! :) I assume that you're wondering about the JDBC 2 test suite from Sun. I've attached a note that has some detailed instructions on getting the suite to work with PostgreSQL. Rene, do you want to post this on your PostgreSQL JDBC page? Ideally we should be able to extract parts of the test suite that could be run without connecting to a backend. It would then be possible to at least run those parts of the tests in make. I think that most, if not all, of the tests in the JDBC suite need to connect to a database. It's not as crazy as it sounds. :-) The driver as a whole must of course be tested against a real database. But many of the classes that it is made up of could be verified on their own with unit tests. This kind of testing catches most of the small silly bugs that seem to be the most common kind of bug. Maybe it would be worthwhile to build our own small unit test suite? Can you explain your idea in more detail? Liam -- Liam Stewart :: Red Hat Canada, Ltd. :: [EMAIL PROTECTED] Supplemental instructions for running the JDBC test suite against PostgreSQL. 1. Configure PostgreSQL so that it accepts TCP/IP connections and start the server. Prepare PostgreSQL by creating two users (cts1 and cts2) and two databases (DB1 and DB2) in the cluster that is going to be used for JDBC testing. 2. Download the latest release versions of the J2EE, J2SE, and JDBC test suite from Sun's Java site (http://java.sun.com), and install according to Sun's documentation. 3. The following environment variables should be set: CTS_HOME=path where JDBC test suite installed (eg: /usr/local/jdbccts) J2EE_HOME=path where J2EE installed (eg: /usr/local/j2sdkee1.2.1) JAVA_HOME=path where J2SE installed (eg: /usr/local/jdk1.3.1) NO_JAVATEST=Y LOCAL_CLASSES=path to PostgreSQL JDBC driver jar 4. In $J2EE_HOME/config/default.properties: jdbc.drivers=org.postgresql.Driver jdbc.datasources=jdbc/DB1|jdbc:postgresql://localhost:5432/DB1|jdbc/DB2|jdbc:postgresq://localhost:5432/DB2 Of course, if PostgreSQL is running on a computer different from the one running the application server, localhost should be changed to the proper host. Also, 5432 should be changed to whatever port PostgreSQL is listening on (5432 is the default). In $J2EE_HOME/bin/userconfig.sh: Add $CTS_HOME/lib/harness.jar, $CTS_HOME/lib/moo.jar, $CTS_HOME/lib/util.jar to J2EE_CLASSPATH. Also add the path to the PostgreSQL JDBC jar to J2EE_CLASSPATH. Set the JAVA_HOME variable to where you installed the J2SE. You should end up with something like this: CTS_HOME=/home/liams/linux/java/jdbccts J2EE_CLASSPATH=/home/liams/work/inst/postgresql-7.1.2/share/java/postgresql.jar:$CTS_HOME/lib/harness.jar:$CTS_HOME/lib/moo.jar:$CTS_HOME/lib/util.jar export J2EE_CLASSPATH JAVA_HOME=/home/liams/linux/java/jdk1.3.1 export JAVA_HOME In $CTS_HOME/bin/cts.jte: webServerHost=localhost webServerPort=8000 servletServerHost=localhost servletServerPort=8000 5. Start the application server (j2ee): $ cd $J2EE_HOME $ bin/j2ee -verbose The server can be stopped after the tests have finished: $ cd $J2EE_HOME $ bin/j2ee -stop 6. Run the JDBC tests: $ cd $CTS_HOME/tests/jdbc/ee $ make jdbc-tests ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[JDBC] Re: Status of JDBC test suite?
Yes I have seen this behavior as well (first run has many failures, but later ones succeed). One reason that I noticed had to do with test setup code. Many of the tests do the following: drop table foobar; create table foobar (...); The first time this is run on a clean database the drop table fails because the table does not exist. The next time this is run it succeds because the table now exists. The behavior of the test harness seems to be that it keeps running the same set of tests over until it gets the same number of failures in two consecutive runs, but I could be wrong about that. thanks, --Barry Liam Stewart wrote: On Sun, Aug 26, 2001 at 09:14:49PM +0200, Rene Pijlman wrote: Before I dive into this and fix the test suite, I just wanted to check if someone else has already looked into this. Any information about the status of the test suite? Not sure if it's the same thing, but I've noticed that often, the first time the test harness runs a particular test bucket, most or all of the tests fail. The second and third passes return work properly (insofar as there's support in the JDBC driver/PostgreSQL). I haven't had time to look into it yet. Liam ---(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] Status of JDBC test suite?
On Wed, 29 Aug 2001, Liam Stewart wrote: Would a brief How-to help? Yes! :) I assume that you're wondering about the JDBC 2 test suite from Sun. I've attached a note that has some detailed instructions on getting the suite to work with PostgreSQL. Rene, do you want to post this on your PostgreSQL JDBC page? Actually, we're not talking about Sun's test suite. :-) But now that you mention it... I think that most, if not all, of the tests in the JDBC suite need to connect to a database. Could Sun's tests be used as a kind of regression tests as well? Then their tests could be the only black-box test suite we'll need! Maybe it would be worthwhile to build our own small unit test suite? Can you explain your idea in more detail? There is already a small unit test suite in the CVS. But it haven't been kept up to date with recent changes in the drivers, and it's a bit hard to use (thus the need for a how-to). The tests use the tool JUnit (http://www.junit.org), that is originally intended for one-class-at-a-time testing. On their site is a lot of good texts about this kind of unit testing. /Anders _ A n d e r s B e n g t s s o n [EMAIL PROTECTED] Stockholm, Sweden _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Status of JDBC test suite?
On Wed, Aug 29, 2001 at 08:08:58PM +0200, Anders Bengtsson wrote: Actually, we're not talking about Sun's test suite. :-) But now that you mention it... Ok, I see the one that you're talking about. I never knew it was there. Now to play with it :) Could Sun's tests be used as a kind of regression tests as well? Then their tests could be the only black-box test suite we'll need! Ehmm..I've been looking Sun's test suite, but one has to realize that their test suite is testing for JDBC compliance at the J2EE level, which is more stringent than normal JDBC 2. It's useful to have, but it's definately not the only suite we'll need. There is already a small unit test suite in the CVS. But it haven't been kept up to date with recent changes in the drivers, and it's a bit hard to use (thus the need for a how-to). The tests use the tool JUnit (http://www.junit.org), that is originally intended for one-class-at-a-time testing. On their site is a lot of good texts about this kind of unit testing. I'll take a look at these. Thanks for the pointers, Liam -- Liam Stewart :: Red Hat Canada, Ltd. :: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go 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.
[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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
