Re: [JDBC] DatabaseMetaData.getTables()
On Sat, 27 Oct 2001 19:25:49 -0500, you wrote: There seems to be a problem with DatabaseMetaData.getTables() [...] It throws a NullPointerException: This may have been fixed by now: http://fts.postgresql.org/db/mw/msg.html?mid=1021572 Regards, René Pijlman [EMAIL PROTECTED] ---(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] JDBC SQLCodes or Error Numbers - Or how to handle errors
On Sun, 28 Oct 2001 00:33:07 -0700, you wrote: I'm used to just doing the insert and catching the SQLCODE -803 on DB2 to know that the row I'm trying to insert already exists. Is there an easy way to do this using Postgresql and JDBC? I'm afraid not. SQLException.getErrorCode() always returns 0. The PostgreSQL backend currently doesn't provide error codes. This issue is on the TODO list (Admin: 'Allow elog() to return error codes'). Once it is fixed in the backend and the FE/BE protocol, it can be fixed in the JDBC driver as well. http://lab.applinet.nl/postgresql-jdbc/#SQLException Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Error: ClassNotFoundException
On Fri, 26 Oct 2001 21:34:37 -0600, you wrote: I have successfully installed PostgreSQL on my Mac G4 running Mac OS X 10.1. I have no experience with this platform, but... Class.forName(org.postgresql.Driver); returns the error java.lang.ClassNotFoundException: org.postgresql.Driver. ... typically means that the driver's jar file is not found or cannot be read by the Java runtime. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] alternative driver jxDBCon
On Wed, 24 Oct 2001 12:58:55 +0200, you wrote: has anyone made some experience using the jdbc driver jxDBCon for PostgreSQL? it is an alternative to the driver released by PostgreSQL. (http://jxdbcon.sourceforge.net) Its been mentioned a couple of times on this list, but I haven't seen any report of anyone actually trying to use it. That's a pitty, because it does look interesting. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Does PG's JDBC support prepared statements at all?
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 '?' :-) Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] PreparedStatement parameters question
On 20 Oct 2001 22:34:05 -, you wrote: PreparedStatement st = db.prepareStatement(UPDATE foo SET ? = '?' + WHERE number = ?); Whenever I use this I get a Parameter index out of range error. I am assuming that what this means is that I can't use a ? as a column name, but it can only be used as a parameter. Am I correct in this? You can only use ? parameters to supply values, not table names, column names or syntactical elements of SQL. Should I basically do it like this: PreparedStatement st = db.prepareStatement(UPDATE foo SET + colname + = '?' WHERE number = ?); Is that the only way to do this? Yes. I don't think you need the single quotes though. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Does PG's JDBC support prepared statements at all?
On 20 Oct 2001 23:14:22 -, you 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. I suggest you read some basic JDBC documentation. This is really not PostgreSQL specific. Checkout http://www.javaskyline.com/learnjdbc.html Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] JDBC test suite patch
On Thu, 20 Sep 2001 16:24:45 -0400, you wrote: . failure in TimestampTest (testSetTimestamp) fixed. The failure is because testSetTimestamp was inserting a timestamp with hour 7 but checkTimeTest was expecting a timestamp with hour 8. AFAICS, there are no issues wrt daylight savings time and timestamps being pushed in and pulled out (but more explicit tests should be added in the future) . failure in TimeTest (testGetTime) fixed. Times to be inserted were interpreted in the localtime zone but checking was done with the assumption that the insertion was done in GMT. Unfortunately, I'm still getting one failure: [junit] Time: 2.716 [junit] There was 1 failure: [junit] 1) testGetTimestamp(org.postgresql.test.jdbc2.TimestampTest)junit.framework.AssertionFailedError [junit] at org.postgresql.test.jdbc2.TimestampTest.timestampTest(TimestampTest.java:112) [junit] at org.postgresql.test.jdbc2.TimestampTest.testGetTimestamp(TimestampTest.java:54) [junit] [junit] FAILURES!!! [junit] Tests run: 43, Failures: 1, Errors: 0 I haven't looked into it yet. Will do later. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] java.lang.OutOfMemory Exception with a large number of inserts
You wrote: Here's the offending loop. BTW, I messed up with the total number of rows, it's ~6,000,000, not 600,000. [...] while(dbResult.next()) Unfortunately, PostgreSQL clients always retrieve the entire resultset from the server before it is processed by the .next() loop. In your case this means that the 6.000.000 rows are first read by the Java client into one huge ResultSet. This will no doubt require much more than 50M of memory. Can't you perform this operation in one or more SQL statements or procedurally in PLSQL on the server, instead of retrieving all data to the client? Cheers, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Updated jdbc.postgresql.org website now in place
On Mon, 1 Oct 2001 15:10:04 -0400, you wrote: now that CLASSPATH is deprecated Que? Did I miss something? Regards, René Pijlman [EMAIL PROTECTED] ---(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] Updated jdbc.postgresql.org website now in place
On Mon, 01 Oct 2001 11:46:27 -0700, you wrote: The subject pretty much says it all. I have updated the jdbc website to contain more current information. It certainly could contain more information, but my primary goal was to get it current. If you have any suggestions, changes or additions please send them to the mail list. Good work, thanks! Some suggestions for improvement... - relating to the JDBC driver - relating to the JDBC driver for a href=http://www.postgresql.org/;PostgreSQL/a - download pre-compiled jar files - download the pre-compiled driver in a jar file - you should send email to [EMAIL PROTECTED]: perhaps this needs an explanation for newbies that this is a volunteer mailing list, rather than a support department. For one thing, I guess someone who posts to the list is expected to join it first so she will see all followups. - I suggest to change the title of the FAQ simply to Documentation. It really contains the basic information one needs to use the driver, rather than questions asked about it. - I find http://jdbc.postgresql.org/download.html very confusing. It would get much better if the latest stable and recommended driver was more clearly presented at the top, and the development and archived versions in a lookup structure below. - On http://jdbc.postgresql.org/download.html I suggest to link Check the main site for details of obtaining the PostgreSQL source to http://developer.postgresql.org/ Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] blob setBinaryStream
On Mon, 24 Sep 2001 09:46:49 -0500, you wrote: PreparedStatement ps = con.prepareStatement(update= userpreferences set image='?' and time=? where user='?'); I guess the single quotes around the question mark placeholder are the cause of your problem. Regards, René Pijlman [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] isNullable()
Attached is a patch that fixes ResultSetMetaData.isNullable() in
the JDBC driver.
This method is currently unimplemented and always returns
ResultSetMetaData.columnNullable. This is obviously incorrect
when a column is defined with NOT NULL or PRIMARY KEY. And we
have to think of check constraints, views, functions etc.
The patch simply changes the return value to
ResultSetMetaData.columnNullableUnknown. This is until someone
comes up with a real implementation of course.
On Fri, 14 Sep 2001 17:53:50 +0200, Tomisaw Kityñski wrote:
Hello there,
could someone tell me, please, do I have any chance to get
proper implementation of above method in JDBC (1.1+) soon?
Current return 1 works fine on most tables, however it seems
to be a little bit incorrect with some of them ;)
Regards,
René Pijlman [EMAIL PROTECTED]
Index: org/postgresql/jdbc1/ResultSetMetaData.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/ResultSetMetaData.java,v
retrieving revision 1.5
diff -c -r1.5 ResultSetMetaData.java
*** org/postgresql/jdbc1/ResultSetMetaData.java 2001/09/06 20:43:39 1.5
--- org/postgresql/jdbc1/ResultSetMetaData.java 2001/09/15 19:35:09
***
*** 136,144
}
/**
!* Can you put a NULL in this column? I think this is always
!* true in 6.1's case. It would only be false if the field had
!* been defined NOT NULL (system catalogs could be queried?)
*
* @param column the first column is 1, the second is 2...
* @return one of the columnNullable values
--- 136,142
}
/**
!* Indicates the nullability of values in the designated column.
*
* @param column the first column is 1, the second is 2...
* @return one of the columnNullable values
***
*** 146,154
*/
public int isNullable(int column) throws SQLException
{
! return columnNullable;// We can always put NULL in
}
!
/**
* Is the column a signed number? In PostgreSQL, all numbers
* are signed, so this is trivial. However, strings are not
--- 144,157
*/
public int isNullable(int column) throws SQLException
{
! /*
!* TODO This needs a real implementation, taking into account columns
!* defined with NOT NULL or PRIMARY KEY, CHECK constraints, views,
!* functions etc.
!*/
! return columnNullableUnknown;
}
!
/**
* Is the column a signed number? In PostgreSQL, all numbers
* are signed, so this is trivial. However, strings are not
Index: org/postgresql/jdbc2/ResultSetMetaData.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSetMetaData.java,v
retrieving revision 1.5
diff -c -r1.5 ResultSetMetaData.java
*** org/postgresql/jdbc2/ResultSetMetaData.java 2001/09/06 20:43:39 1.5
--- org/postgresql/jdbc2/ResultSetMetaData.java 2001/09/15 19:35:09
***
*** 131,139
}
/**
!* Can you put a NULL in this column? I think this is always
!* true in 6.1's case. It would only be false if the field had
!* been defined NOT NULL (system catalogs could be queried?)
*
* @param column the first column is 1, the second is 2...
* @return one of the columnNullable values
--- 131,137
}
/**
!* Indicates the nullability of values in the designated column.
*
* @param column the first column is 1, the second is 2...
* @return one of the columnNullable values
***
*** 141,147
*/
public int isNullable(int column) throws SQLException
{
! return columnNullable;// We can always put NULL in
}
/**
--- 139,150
*/
public int isNullable(int column) throws SQLException
{
! /*
!* TODO This needs a real implementation, taking into account columns
!* defined with NOT NULL or PRIMARY KEY, CHECK constraints, views,
!* functions etc.
!*/
! return columnNullableUnknown;
}
/**
---(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] Using boolean '1' in jdbc2
On 13 Sep 2001 22:58:23 -0500, you wrote: I have a schema data from Oracle that uses a 'fake' boolean column. (Stupid oracle doesn't support type boolean afaik - uses number). I find the easiest way to adapt the schema data to postgresql is to *not* convert the 'fake' boolean fields (enforced in Oracle using constraints) to postgresql boolean but to use either numeric(1) or integer. [...] So - the submitted patch allows a boolean field to be implemented using either numeric or integer fields - I think this is pretty common. Ah, I see. Well, for what its worth, this is allowed by the JDBC spec. The getXXX()/datatypes mapping table at the end of the JDBC book says mapping getBoolean() to SQL BIT is recommended, but calling it on TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, CHAR, VARCHAR, LONGVARCHAR is also allowed. In fact, I think this means our driver is required to support these mappings. The book doesn't say how the values in these types should map to true and false though. I guess this means there is no objection to the patch. But personally, I would prefer to do it right the first time for all datatypes, rather than extending the if statement for every request. Regards, René Pijlman [EMAIL PROTECTED] ---(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] Using boolean '1' in jdbc2
On Fri, 14 Sep 2001 11:22:53 +0200, you wrote: Because the 1 features is not suppossed to be in the spécification I think it is. See my other posting. To be more precise: the int-boolean mapping is in the spec, but the spec doesn't say how int values should be mapped to true and false. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Transaction size limit Data Truncation
You wrote: I can see that very long transaction are not supported. Where or how did you see that? As far as transaction is concerned, my question was if there is any limit to the number of statements (select/insert/update) that can be in one single transaction. Is is possible postgresql buffer size influence transaction limit? I had a transaction with thousands of statements that I neeeded to split it into a number smaller transaction in order to be able to store it. What does that mean exactly? What went wrong with the original long transaction? Was it an executeBatch() with a lot of statements, or a lot of individual statements? What version of the PostgreSQL driver and backend are you running? Let's say that I have a field in a table having tha type VARCHAR(100) And I store to this field a value having 200 characters. Only the first 100 characters of the value will be stored. I think that in this case according to JDBC, a Data Truncation warning should be reported. Our driver does not yet support DataTruncation warnings. That's documented on http://lab.applinet.nl/postgresql-jdbc/ Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] DatabaseMetadata problems
[JDBC driver ignores catalog and schema] On Fri, 07 Sep 2001 09:58:36 -0400, Tom Lane wrote: I think it'd be a mistake to expend much effort on this issue in JDBC right now. We will eventually implement SQL92-compliant schemas in the backend, and once that happens it'll be possible to do something reasonable with the catalog metadata routines in JDBC. If you try to make a half-usable implementation now, you'll just create backwards- compatibility issues for the real implementation later. So my counsel is: acknowledge that it's broken, but ignore it for now. I agree. I've added this to http://lab.applinet.nl/postgresql-jdbc/: PostgreSQL currently does not support SQL92 compliant catalogs and schemas. With a number of methods (getColumns, getTables, getProcedures and such) the JDBC driver ignores the catalog and schema or schemaPattern parameters. getColumns() returns an empty string (should be null) in the fields TABLE_CAT and TABLE_SCHEM. getTables() returns null in TABLE_CAT and TABLE_SCHEM. getProcedures() returns null in PROCEDURE_CAT and PROCEDURE_SCHEM. Regards, René Pijlman [EMAIL PROTECTED] ---(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] Need help with JDBC driver. Problem, - getExportedKeys=
On Sat, 01 Sep 2001 17:43:39 +0200, you wrote: I've tried the jxdbcon driver now and it gives another view of the actual database... I am not a PostgreSQL guy so I am quite confused here. Anyway, this one seems to give the most appropriate view of the database since the system tables are listed which the pgsql drivers did not list. Yes, I noticed that DatabaseMetaData.getColumns() in the standard PostgreSQL driver returns the columns of the system tables, whereas getTables() does not return those tables. I guess that should be fixed. So documented on http://lab.applinet.nl/postgresql-jdbc/ Could anyone point me to a place where I can download/get some simple SQL statements to create at least relationsships between two tables? See the section Reference constraint on http://www.postgresql.org/users-lounge/docs/7.1/reference/sql-createtable.html Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] Regarding Error installing jdbc7.0-1.2.jar
On Sun, 09 Sep 2001 14:25:38 +0530, you wrote: Then, I tried to install the jar file for jdbc 'jdbc7.0-1.2. jar' and extracted it to the directory /usr/lib/pgsql. Never extract the contents of these jar files! http://jdbc.postgresql.org/docs/#install Put it in your CLASSPATH instead. 7.0 is pretty old by the way. You may want to get the latest release of the driver from http://jdbc.fastcrypt.com/. But then again, expect some problems when you use the latest release of the driver against a 7.0 backend (7.1 is OK). Regards, René Pijlman [EMAIL PROTECTED] ---(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] Regarding Error installing jdbc7.0-1.2.jar
I'm not sure if you get this message via the list, but if you post an a mailing list it would be nice if your address worked. - Transcript of session follows - ... while talking to mail.ceedees.com.: RCPT To:[EMAIL PROTECTED] 550 [EMAIL PROTECTED]... Relaying denied 550 [EMAIL PROTECTED]... User unknown Regards, René Pijlman [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] [HACKERS] JDBC pg_description update needed for CVS tip
On Sun, 09 Sep 2001 14:48:41 +0200, you wrote: It is of course a performance improvement if it uses only 1 SQL statement rather than N+1 with N being the number of columns reported. E.g. if you list all columns of all tables in a big database, this would be a huge win. I think that can only be decided by measurement. What you're saying is: 1 * c1 (N + 1) * c2 but that can only be decided if we know c1 and c2 (meaning: the execution times of two different queries, including round trip overhead). That doesn't mean I'm opposed to the change, on the contrary. As a rule, I find a complex SQL statement more elegant than the same 'algorithm' in procedural code. But in this case I wasn't sure how to construct it. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[JDBC] Fix DatabaseMetaDataTest in JDBC test suite
Attached is a patch that fixes DatabaseMetaDataTest in the JDBC
driver's test suite. With previous patches applied, this reduces
the number of failures of the test suite from 6 to 4. The patch
fixes the test case itself, rather than the driver.
Details:
1) The driver correctly provided DatabaseMetaData about the sort
order of NULLs. This was confirmed by Peter Eisentraut on
pgsql-hackers. I fixed the test to accept/require the current
behaviour, and made it dependent on the backend version. See
nullsAreSortedAtStart(), nullsAreSortedAtEnd(),
nullsAreSortedHigh() and nullsAreSortedLow().
2) DatabaseMetaData.supportsOrderByUnrelated() correctly
returned true (an ORDER BY clause can contain columns that are
not in the SELECT clause), but the test case required false.
Fixed that.
3) Replaced deprecated assert() of junit.framework.TestCase by
assertEquals(), assertTrue() and assertNotNull(). This is
because assert will be a new keyword in Java 1.4.
4) Replaced assert(message,false) by the more elegant
fail(message).
Regards,
René Pijlman [EMAIL PROTECTED]
Index: org/postgresql/test/jdbc2/DatabaseMetaDataTest.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java,v
retrieving revision 1.1
diff -c -r1.1 DatabaseMetaDataTest.java
*** org/postgresql/test/jdbc2/DatabaseMetaDataTest.java 2001/02/13 16:39:05 1.1
--- org/postgresql/test/jdbc2/DatabaseMetaDataTest.java 2001/09/09 14:04:42
***
*** 29,39
Connection con = JDBC2Tests.openDB();
DatabaseMetaData dbmd = con.getMetaData();
! assert(dbmd!=null);
JDBC2Tests.closeDB(con);
} catch(SQLException ex) {
! assert(ex.getMessage(),false);
}
}
--- 29,39
Connection con = JDBC2Tests.openDB();
DatabaseMetaData dbmd = con.getMetaData();
! assertNotNull(dbmd);
JDBC2Tests.closeDB(con);
} catch(SQLException ex) {
! fail(ex.getMessage());
}
}
***
*** 45,76
Connection con = JDBC2Tests.openDB();
DatabaseMetaData dbmd = con.getMetaData();
! assert(dbmd!=null);
! assert(dbmd.allProceduresAreCallable()==true);
! assert(dbmd.allTablesAreSelectable()==true); // not true all the time
// This should always be false for postgresql (at least for 7.x)
! assert(!dbmd.isReadOnly());
// does the backend support this yet? The protocol does...
! assert(!dbmd.supportsMultipleResultSets());
// yes, as multiple backends can have transactions open
! assert(dbmd.supportsMultipleTransactions());
! assert(dbmd.supportsMinimumSQLGrammar());
! assert(!dbmd.supportsCoreSQLGrammar());
! assert(!dbmd.supportsExtendedSQLGrammar());
! assert(!dbmd.supportsANSI92EntryLevelSQL());
! assert(!dbmd.supportsANSI92IntermediateSQL());
! assert(!dbmd.supportsANSI92FullSQL());
! assert(!dbmd.supportsIntegrityEnhancementFacility());
JDBC2Tests.closeDB(con);
} catch(SQLException ex) {
! assert(ex.getMessage(),false);
}
}
--- 45,76
Connection con = JDBC2Tests.openDB();
DatabaseMetaData dbmd = con.getMetaData();
! assertNotNull(dbmd);
! assertTrue(dbmd.allProceduresAreCallable());
! assertTrue(dbmd.allTablesAreSelectable()); // not true all the time
// This should always be false for postgresql (at least for 7.x)
! assertTrue(!dbmd.isReadOnly());
// does the backend support this yet? The protocol does...
! assertTrue(!dbmd.supportsMultipleResultSets());
// yes, as multiple backends can have transactions open
! assertTrue(dbmd.supportsMultipleTransactions());
! assertTrue(dbmd.supportsMinimumSQLGrammar());
! assertTrue(!dbmd.supportsCoreSQLGrammar());
! assertTrue(!dbmd.supportsExtendedSQLGrammar());
! assertTrue(!dbmd.supportsANSI92EntryLevelSQL());
! assertTrue(!dbmd.supportsANSI92IntermediateSQL());
! assertTrue(!dbmd.supportsANSI92FullSQL());
! assertTrue(!dbmd.supportsIntegrityEnhancementFacility());
JDBC2Tests.closeDB(con);
} catch(SQLException ex) {
! fail(ex.getMessage());
}
}
***
*** 80,94
Connection con = JDBC2Tests.openDB();
DatabaseMetaData dbmd = con.getMetaData();
! assert(dbmd!=null);
! assert(dbmd.supportsOuterJoins());
! assert(dbmd.supportsFullOuterJoins());
! assert(dbmd.supportsLimitedOuterJoins());
JDBC2Tests.closeDB(con);
} catch(SQLException ex) {
! assert(ex.getMessage(),false);
}
}
--- 80,94
Connection con = JDBC2Tests.openDB();
DatabaseMetaData dbmd = con.getMetaData();
! assertNotNull(dbmd);
!
Re: [HACKERS] [JDBC] Troubles using German Umlauts with JDBC
On Sun, 9 Sep 2001 10:24:32 -0400 (EDT), Bruce Momjian wrote: I can add something if people agree there is an issue here. IMO the issue is twofold. Without multibyte compiled in: 1) the server cannot tell the client which single byte character encoding is being used, so a client like JDBC cannot properly convert to its native encoding 2) its not possible to create a database with a single byte encoding other than ASCII (see my posting http://fts.postgresql.org/db/mw/msg.html?mid=1029462) I'm not sure to what extent these issues are related. Also, client/server character conversion is coupled to multibyte support (see Peter's reply to my posting). This may be a limitation for other clients, but I'm not sure about that. Basically, it seems that multibyte support is adding features that are needed in single byte environents as well. Perhaps the problem can be solved by documentation (recommending to enable multibyte support in non-ASCII singlebyte environments), perhaps by an alias (--enable-character-encoding), perhaps the functionality needs to be split into a true multibyte part and a generic part. I don't know what's best, this probably depends on the price of compiling in multibyte support. Regards, René Pijlman I've added a new section Character encoding to http://lab.applinet.nl/postgresql-jdbc/, based on the information from Dave and Barry. I haven't seen a confirmation from pgsql-hackers or Bruce yet that this issue will be added to the Todo list. I'm under the impression that the backend developers don't see this as a problem. Regards, Ren? Pijlman On Tue, 04 Sep 2001 10:40:36 -0700, Barry Lind wrote: I would like to add one additional comment. In current sources the jdbc driver detects (through a hack) that the server doesn't have multibyte enabled and then ignores the SQL_ASCII return value and defaults to the JVM's character set instead of using SQL_ASCII. The problem boils down to the fact that without multibyte enabled, the server has know way of specifiying which 8bit character set is being used for a particular database. Thus a client like JDBC doesn't know what character set to use when converting to UNICODE. Thus the best we can do in JDBC is use our best guess (JVM character set is probably the best default), and allow the user to explicitly specify something else if necessary. thanks, --Barry Rene Pijlman wrote: [forwarding to pgsql-hackers and Bruce as Todo list maintainer, see comment below] [insert with JDBC converts Latin-1 umlaut to ?] On 04 Sep 2001 09:54:27 -0400, Dave Cramer wrote: You have to set the encoding when you make the connection. Properties props = new Properties(); props.put(user,user); props.put(password,password); props.put(charSet,encoding); Connection con = DriverManager.getConnection(url,props); where encoding is the proper encoding for your database For completeness, I quote the answer Barry Lind gave yesterday. [the driver] asks the server what character set is being used for the database. Unfortunatly the server only knows about character sets if multibyte support is compiled in. If the server is compiled without multibyte, then it always reports to the client that the character set is SQL_ASCII (where SQL_ASCII is 7bit ascii). Thus if you don't have multibyte enabled on the server you can't support 8bit characters through the jdbc driver, unless you specifically tell the connection what character set to use (i.e. override the default obtained from the server). This really is confusing and I think PostgreSQL should be able to support single byte encoding conversions without enabling multi-byte. To the very least there should be a --enable-encoding-conversion or something similar, even if it just enables the current multibyte support. Bruce, can this be put on the TODO list one way or the other? This problem has appeared 4 times in two months or so on the JDBC list. Regards, Ren? Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(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: [HACKERS] [JDBC] Troubles using German Umlauts with JDBC
I've added a new section Character encoding to http://lab.applinet.nl/postgresql-jdbc/, based on the information from Dave and Barry. I haven't seen a confirmation from pgsql-hackers or Bruce yet that this issue will be added to the Todo list. I'm under the impression that the backend developers don't see this as a problem. Regards, René Pijlman On Tue, 04 Sep 2001 10:40:36 -0700, Barry Lind wrote: I would like to add one additional comment. In current sources the jdbc driver detects (through a hack) that the server doesn't have multibyte enabled and then ignores the SQL_ASCII return value and defaults to the JVM's character set instead of using SQL_ASCII. The problem boils down to the fact that without multibyte enabled, the server has know way of specifiying which 8bit character set is being used for a particular database. Thus a client like JDBC doesn't know what character set to use when converting to UNICODE. Thus the best we can do in JDBC is use our best guess (JVM character set is probably the best default), and allow the user to explicitly specify something else if necessary. thanks, --Barry Rene Pijlman wrote: [forwarding to pgsql-hackers and Bruce as Todo list maintainer, see comment below] [insert with JDBC converts Latin-1 umlaut to ?] On 04 Sep 2001 09:54:27 -0400, Dave Cramer wrote: You have to set the encoding when you make the connection. Properties props = new Properties(); props.put(user,user); props.put(password,password); props.put(charSet,encoding); Connection con = DriverManager.getConnection(url,props); where encoding is the proper encoding for your database For completeness, I quote the answer Barry Lind gave yesterday. [the driver] asks the server what character set is being used for the database. Unfortunatly the server only knows about character sets if multibyte support is compiled in. If the server is compiled without multibyte, then it always reports to the client that the character set is SQL_ASCII (where SQL_ASCII is 7bit ascii). Thus if you don't have multibyte enabled on the server you can't support 8bit characters through the jdbc driver, unless you specifically tell the connection what character set to use (i.e. override the default obtained from the server). This really is confusing and I think PostgreSQL should be able to support single byte encoding conversions without enabling multi-byte. To the very least there should be a --enable-encoding-conversion or something similar, even if it just enables the current multibyte support. Bruce, can this be put on the TODO list one way or the other? This problem has appeared 4 times in two months or so on the JDBC list. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] NULLs and sort order
On Sun, 9 Sep 2001 15:25:17 +0200 (CEST), you wrote: That is correct. Thanks. Would it be useful to add this information to the documentation, e.g. the documentation of ORDER BY in SELECT? Most likely. I'll post it on the docs list. Regards, René Pijlman [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
[JDBC] Timezones and time/timestamp values in FE/BE protocol
I'm working on a problem in the JDBC driver that's related to timezones. How does PostgreSQL handle timezones in the FE/BE protocol exactly? When a client sends a time or timestamp value to the server via the FE/BE protocol, should that be: 1) a value in the client's timezone? 2) a value in the server's timezone? 3) a value in a common frame of reference (GMT/UTC)? 4) any value with an explicit timezone? And how should a time or timestamp value returned by the server be interpreted in the client interface? And how does this all depend on the timezone setting of the server? Regards, René Pijlman [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] Timezones and time/timestamp values in FE/BE protocol
On Sun, 09 Sep 2001 13:38:52 -0700, you wrote: [...] Thanks for your explanation. This helps a lot. If you could post a bit more about the issue you are having I might be able to be more specific. I'm looking at the 4 remaining failures of our own JDBC test suite. They all have to do with timestamps and times, and they are all caused by a 1 hour shift between the expected value and the actual value. I run both the backend and the JVM on the same Linux test server. Its located in Amsterdam, The Netherlands, Central European Daylight Savings Time (CETDST, UTC+2, GMT+2). I always thought I was in CET=GMT+1, but now the offset is 2, because of daylight saving time (whoever invented that should be #!$^). Perhaps I should go live in Greenwich, they don't seem to have daylight saving time overthere. In psql I see: show timezone; NOTICE: Time zone is unset Here is some detailed information about the failures. I'm refering to line numbers in 7.2 current CVS: TimeTest.java revision 1.1 TimestampTest.java revision 1.2 1) TimeTest.java:89 getHours(t) expected 1, actual 0 t.toString() returns the expected 01:02:03, but this is because java.sql.Time.toString() converts to the JVM's timezone. 2) TimeTest.java:96 getHours(t) expected 23, actual 0 t.toString returns 00:59:59 3) TimestampTest.java:115 Expected: getTimestamp(1970,6,2,8,13,0) returns 1970-06-02 08:13:00.0 Actual: t.toString() returns 1970-06-02 09:13:00.0 4) TimestampTest.java:115 (second time around) Expected: getTimestamp(1970,6,2,8,13,0) returns 1970-06-02 08:13:00.0 Actual: t.toString() returns 1970-06-02 07:13:00.0 My first impression is that in all cases a timezone shift is applied in only one direction (store vs. retrieve). The cause might also be a problem with daylight saving time, there are some comments about that in TimestampTest.java. Up till now I've managed without a graphical debugger, but to get a good feel for what's happening between the test code and the wire I think it'll be easier to setup JBuilder with the driver and step through the code. But now its almost bedtime in my timezone, and you never know with these mailing lists. Sometimes the solution is in your inbox when you wake up :-) Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[JDBC] Fix JDBC test suite, set/get transaction isolation level test in ConnectionTest
Attached is a patch that fixes ConnectionTest.testTransactionIsolation() in the JDBC driver's test suite. This reduces the number of failures of the test suite from 7 to 6. The patch fixes the test case itself, rather than the driver. In addition to the change described in my posting below, I fixed the part of the test with autocommit enabled. The author of the test assumed that setting the transaction isolation level would have no effect, but in fact it does. Perhaps the test case worked with pre-7.1 behaviour, when the JDBC driver set the isolation level in every transaction, instead of using set session characteristics. Anyway, now it works with a backend built from current CVS and the behaviour is JDBC compliant. I also extended the test case by changing the isolation level before beginning a transaction and verifying it inside the transaction. Regards, René Pijlman On Fri, 7 Sep 2001 17:56:59 +0200, I wrote on pgsql-jdbc: The ConnectionTest test case in our own jdbc2 test suite fails to set and get the transaction isolation level correctly. After looking at the implementation I've come to the conclusion that the test case itself is flawed, but I wanted to check my conclusion with this list. What the test case does is: con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) ; assertEquals(Connection.TRANSACTION_SERIALIZABLE, con.getTransactionIsolation()); And this assertion fails because con.getTransactionIsolation() returns TRANSACTION_READ_COMMITTED. The cause of this problem is that first a new transaction is started (because of the setAutoCommit(false)) and then the isolation level for this connection is changed. Internally (since I tested against a 7.1 backend which supports SET SESSION) the driver generates: set session characteristics as transaction isolation level serializable; And this changes only the default isolation level for future transactions on this session, not the isolation level of the current transaction. Therefore, getTransactionIsolation() in the same transaction returns the still current isolation level READ COMMITTED. Reading through JDBC documentation from Sun I found the best explanation in the JDBC 3.0 Spec, final draft 3 (relevant section quoted below). This says It is recommended that drivers implement the setTransactionIsolation method to change the isolation level starting with the next transaction, and this is in fact what our driver does. It also says Committing the current transaction to make the effect immediate is also a valid implementation, but I see no reason to change the current behaviour to this alternative implementation. And it says The return value of the method getTransactionIsolation should reflect the change in isolation level when it actually occurs, and again, this is in fact what our driver does. Note that applications can avoid this complication simply by setting the transaction isolation level before starting a transaction (before calling setAutoCommit(false)), as recommended by JDBC. So I'm inclined to change the test case to allow (in fact, require) the current behaviour. Any comments? -+-+- Quote from the JDBC 3.0 Specification, Proposed Final Draft 3 http://java.sun.com/products/jdbc/download.html 10.2.1 Using the setTransactionIsolation Method The default transaction level for a Connection object is determined by the driver supplying the connection. Typically, it is the default transaction level supported by the underlying data source. The Connection method setTransactionIsolation is provided to allow JDBC clients to change the transaction isolation level for a given Connection object. The new isolation level remains in effect for the remainder of the session or until the next invocation of the setTransactionIsolation method. The result of invoking the method setTransactionIsolation in the middle of a transaction is implementation-defined. The return value of the method getTransactionIsolation should reflect the change in isolation level when it actually occurs. It is recommended that drivers implement the setTransactionIsolation method to change the isolation level starting with the next transaction. Committing the current transaction to make the effect immediate is also a valid implementation. It is possible for a given JDBC driver to not support all four transaction isolation levels (not counting TRANSACTION_NONE). If a driver does not support the isolation level specified in an invocation of setTransactionIsolation, it is allowed to substitute a higher, more restrictive transaction isolation level. If a driver is unable to substitute a higher transaction level, it throws an SQLException. The DatabaseMetaData method supportsTransactionIsolationLevel may be used to determine whether or not the driver supports a given level. -+-+- Regards, René Pijlman ---(end of broadcast)--- TIP 6: Have you searched our list archives?
Re: [JDBC] DatabaseMetadata problems
On Thu, 6 Sep 2001 22:30:58 +0100, you wrote: The problems are: 1. No Index information from the getIndexInfo method Hmmm... looking at the source, it apppears that DatabaseMetaData.getIndexInfo() is implemented. If it doesn't work for you, we would need more information about the problem to be able to fix it. 2. Indexes included as tables in getTables Again, can you provide us with more detailed information about the problem? A small test case would be nice, which creates a few tables and indexes and shows incorrect output. 3. Only one column in the entire database is returned in getColumns getColumns() was fixed recently. It returned only columns with a comment (IIRC). This should be fixed in release 7.2. 4. The catalog name is not returned in getTables The PostgreSQL driver intentionally ignores the catalog parameter of the metadata methods, and it returns null for the catalog in the metadata it returns. I don't know if this could be implemented and how. Also, any interpretation of this concept we come up with has to be consistent with the backend's understanding of the SQL standard's concept 'catalog'. Reading the book Understanding the SQL standard it seems that what the standard calls a schema is a database in PostgreSQL. A catalog is a set of schemas. Following the standard, a session is supposed to have access to a catalog, but in PostgreSQL it has access to a database. Perhaps this feature has not yet been implemented because of such fundamental mapping problems. 5. Comment columns contain 'No Comment' instead of null That was fixed recently in getTables(), getColumns() and getProcedures(). The fix wil appear in release 7.2. Where are the sources, as I am willing to take a look at them to try at least identify the problems in more detail http://developer.postgresql.org/ I would also like to point you to our JDBC compliance effort: http://lab.applinet.nl/postgresql-jdbc/. However, the DatabaseMetaData section is still very much under construction. I'll be glad to document any deviations of the standard you may find (please post new issues on this list). Since this is a volunteer open source project, we cannot guarantee if and when the problems will be fixed. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[JDBC] Fix set/get transaction isolation level test in ConnectionTest
The ConnectionTest test case in our own jdbc2 test suite fails to set and get the transaction isolation level correctly. After looking at the implementation I've come to the conclusion that the test case itself is flawed, but I wanted to check my conclusion with this list. What the test case does is: con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) ; assertEquals(Connection.TRANSACTION_SERIALIZABLE, con.getTransactionIsolation()); And this assertion fails because con.getTransactionIsolation() returns TRANSACTION_READ_COMMITTED. The cause of this problem is that first a new transaction is started (because of the setAutoCommit(false)) and then the isolation level for this connection is changed. Internally (since I tested against a 7.1 backend which supports SET SESSION) the driver generates: set session characteristics as transaction isolation level serializable; And this changes only the default isolation level for future transactions on this session, not the isolation level of the current transaction. Therefore, getTransactionIsolation() in the same transaction returns the still current isolation level READ COMMITTED. Reading through JDBC documentation from Sun I found the best explanation in the JDBC 3.0 Spec, final draft 3 (relevant section quoted below). This says It is recommended that drivers implement the setTransactionIsolation method to change the isolation level starting with the next transaction, and this is in fact what our driver does. It also says Committing the current transaction to make the effect immediate is also a valid implementation, but I see no reason to change the current behaviour to this alternative implementation. And it says The return value of the method getTransactionIsolation should reflect the change in isolation level when it actually occurs, and again, this is in fact what our driver does. Note that applications can avoid this complication simply by setting the transaction isolation level before starting a transaction (before calling setAutoCommit(false)), as recommended by JDBC. So I'm inclined to change the test case to allow (in fact, require) the current behaviour. Any comments? -+-+- Quote from the JDBC 3.0 Specification, Proposed Final Draft 3 http://java.sun.com/products/jdbc/download.html 10.2.1 Using the setTransactionIsolation Method The default transaction level for a Connection object is determined by the driver supplying the connection. Typically, it is the default transaction level supported by the underlying data source. The Connection method setTransactionIsolation is provided to allow JDBC clients to change the transaction isolation level for a given Connection object. The new isolation level remains in effect for the remainder of the session or until the next invocation of the setTransactionIsolation method. The result of invoking the method setTransactionIsolation in the middle of a transaction is implementation-defined. The return value of the method getTransactionIsolation should reflect the change in isolation level when it actually occurs. It is recommended that drivers implement the setTransactionIsolation method to change the isolation level starting with the next transaction. Committing the current transaction to make the effect immediate is also a valid implementation. It is possible for a given JDBC driver to not support all four transaction isolation levels (not counting TRANSACTION_NONE). If a driver does not support the isolation level specified in an invocation of setTransactionIsolation, it is allowed to substitute a higher, more restrictive transaction isolation level. If a driver is unable to substitute a higher transaction level, it throws an SQLException. The DatabaseMetaData method supportsTransactionIsolationLevel may be used to determine whether or not the driver supports a given level. -+-+- Regards, René Pijlman ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] DatabaseMetadata problems
On Fri, 07 Sep 2001 10:12:39 +0200, I wrote: getColumns() was fixed recently. It returned only columns with a comment (IIRC). No, it returned only columns with a default value, according to Jeroen van Vianen who fixed it (in 7.2). See http://fts.postgresql.org/db/mw/msg.html?mid=1032468 Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] jdbc ResultSetMetaData::isWritable()
On Mon, 3 Sep 2001 22:01:17 -0500, you wrote:
public boolean isWritable(int column) throws SQLException
{
if (isReadOnly(column))
return true;
else
return false;
}
The author probably intended:
public boolean isWritable(int column) throws SQLException
{
return !isReadOnly(column);
}
And if he would have coded it this way he wouldn't have made
this mistake :-)
hence, isWritable() will always return false. this is something
of a problem :)
Why exactly? In a way, true is just as incorrect as false, and
perhaps it should throw not implemented. But I guess that
would be too non-backwardly-compatible.
let me know if i can provide further information.
Will you submit a patch?
Regards,
René Pijlman [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] Why JDBC 1?
On 04 Sep 2001 13:22:33 -0400, Dave Cramer wrote: Isn't the JDBC 2 driver also an implementation of JDBC 1? Yes, it is, but many of the libraries, and methods which are used in version 2 of the driver aren't available in jdk1 so it won't compile or run. Ah, I see. And I assume we support JDBC 1 for applets that need to run in popular browsers with a JVM 1. Has it been discussed before when and how we can stop maintaining two versions? Are people really still using JDBC 1? Regards, René Pijlman [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] SocketException on connect, busy server
On Sun, 02 Sep 2001 23:41:49 +0200, you wrote: On a busy server, serving web pages using tomcat and apache, I get this error sometimes: java.net.SocketException: errno: 48, error: Address already in use for fd: 168 at java.net.PlainSocketImpl.socketConnect(Native Method) This means the underlying connect(2) socket call returns errno EADDRINUSE. On a connect(), this means that the 5-tuple protocol,local address,local port,remote address,remote port would not be unique if the operation had been allowed. Since protocol, local address, remote address and remote port are fixed, this occurs if the local socket implementation cannot provide a local port number that is still available with respect to the 4 fixed parts. The port number is a 16 bit number, ports 0-1024 are usually not available as dynamically allocated, leaving 64511 ports available. TCP defines a 4 minute TIME_WAIT state, so there can be no more than 64511 / 4 * 60 = 268 connections per second from a single client IP address to one particular service. This is usually only a problem with load balancing HTTP servers and other stateless protocols, not with database servers, since database connections are typically pooled. Is your application opening and closing PostgreSQL connections at such a high rate? In any case, you should probably attack this problem at the TCP implementation level of your OS. It is most likely not PostgreSQL specific. Google gives some interesting hits: http://www.google.com/search?q=time%5Fwait Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] SocketException on connect, busy server
On Fri, 07 Sep 2001 02:37:36 +0200, I wrote: Is your application opening and closing PostgreSQL connections at such a high rate? Or, I should add, is it opening that many connections? Regards, René Pijlman [EMAIL PROTECTED] ---(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] jdbc ResultSetMetaData::isWritable()
On Thu, 6 Sep 2001 14:26:49 -0400 (EDT), you wrote:
Well, if it is that easy, I can do it. Patch attached and applied.
On Mon, 3 Sep 2001 22:01:17 -0500, you wrote:
public boolean isWritable(int column) throws SQLException
{
return !isReadOnly(column);
}
Actually, I think this change has a consequence for this method
in the same class:
public boolean isDefinitelyWritable(int column)
throws SQLException
{
return isWritable(column);
}
This is from the JDBC spec
(http://java.sun.com/j2se/1.3/docs/api/java/sql/ResultSetMetaData.html):
isReadOnly() - Indicates whether the designated column is
definitely not writable.
isWritable() - Indicates whether it is possible for a write on
the designated column to succeed.
isDefinitelyWritable() - Indicates whether a write on the
designated column will definitely succeed.
At this time we don't really implement the fine semantics of
these methods. I would suggest the following defaults:
isReadOnly() false
isWritable() true
isDefinitelyWritable() false
And that would mean that your patch is correct, but
isDefinitelyWritable() would need to be patched accordingly:
public boolean isDefinitelyWritable(int column)
throws SQLException
{
return false;
}
Again, both in jdbc1 and jdbc2.
Regards,
René Pijlman [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[JDBC] Why JDBC 1?
Perhaps this is a silly question, but why do we have separate JDBC 1 and 2 drivers? Isn't the JDBC 2 driver also an implementation of JDBC 1? Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Troubles using German Umlauts with JDBC
[forwarding to pgsql-hackers and Bruce as Todo list maintainer, see comment below] [insert with JDBC converts Latin-1 umlaut to ?] On 04 Sep 2001 09:54:27 -0400, Dave Cramer wrote: You have to set the encoding when you make the connection. Properties props = new Properties(); props.put(user,user); props.put(password,password); props.put(charSet,encoding); Connection con = DriverManager.getConnection(url,props); where encoding is the proper encoding for your database For completeness, I quote the answer Barry Lind gave yesterday. [the driver] asks the server what character set is being used for the database. Unfortunatly the server only knows about character sets if multibyte support is compiled in. If the server is compiled without multibyte, then it always reports to the client that the character set is SQL_ASCII (where SQL_ASCII is 7bit ascii). Thus if you don't have multibyte enabled on the server you can't support 8bit characters through the jdbc driver, unless you specifically tell the connection what character set to use (i.e. override the default obtained from the server). This really is confusing and I think PostgreSQL should be able to support single byte encoding conversions without enabling multi-byte. To the very least there should be a --enable-encoding-conversion or something similar, even if it just enables the current multibyte support. Bruce, can this be put on the TODO list one way or the other? This problem has appeared 4 times in two months or so on the JDBC list. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] ? (question mark) characters
On 02 Sep 2001 03:35:29 +0200, you wrote: You don't need multibyte for iso-8859-1. That's what I thought. But with current CVS (7.2) creating a database with -E LATIN1 fails without multibyte support. See the link in one of my previous postings in this thread. Regards, René Pijlman [EMAIL PROTECTED] ---(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] JAVA vs PERL : PERL wins to postgreSQL
On Mon, 03 Sep 2001 07:47:29 +0200, you wrote: I ran a few bench marks on JAVA writing to a postgreSQL table using and found that for the same number of records added to the table as a similar PERL routine the following results : PERL 39 seconds : JAVA 45 Seconds. In a similar experiment where PERL and JAVA did treir output to the screen and not to a table, Can you tell us some more about the algorithm? Is it one process with one connection, or are you restarting the Java application many times? What are the queries it executes? Is autocommit on or off? What JDK/JRE did you use? I've heard that the IBM JDK performs well. It would be interesting to give it a try. Its a free download on http://www-106.ibm.com/developerworks/java/ Regards, René Pijlman [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] RE : ? (question mark) characters
On Mon, 03 Sep 2001 09:49:27 -0700, you wrote: Yes this is exactly what the driver does. It asks the server what character set is being used for the database. Unfortunatly the server only knows about character sets if multibyte support is compiled in. If the server is compiled without multibyte, then it always reports to the client that the character set is SQL_ASCII (where SQL_ASCII is 7bit ascii). Thus if you don't have multibyte enabled on the server you can't support 8bit characters through the jdbc driver, unless you specifically tell the connection what character set to use (i.e. override the default obtained from the server). Excellent explanation, thanks. This would be great info for the FAQ. I've seen this issue appear three times or so in the relatively short time I'm on this list. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] ? (question mark) characters
On Thu, 30 Aug 2001 11:06:13 -0300, you wrote: Thank you all for your help: Dia Thursday, 30 de August, 2001 10:42, Rene Pijlman wrote: like áéíóú (aeiou with accent)... they are replaced by question marks (?)... any ideas? What's the character encoding of the database? It doesn't matter. I have tried it with SQL_ASCII, ISO-8859-1, and with an installation on Solaris which doesn't have encodings (psql -l doesnt list the Encoding field). Have you tried it with a database which was created with -E LATIN1 and with an installation configured with --enable-multibyte? If so, can you post a small Java program that reproduces the problem? Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] RE : ? (question mark) characters
I'm forwarding this workaround to the list. Can someone shed some light on this? Java uses Unicode and should be able to represent all ISO Latin-1 (8859-1) encoded characters. Why would we need to tell the driver what character set the backend is sending us? Can't it ask the backend dynamically? On Sat, 01 Sep 2001 22:34:49 +0200, Quentin Delance wrote: I ve just read that you were having questions about non ascii characters support when accessing a posgresql DB through JDBC. As I am french (éèà...), I got the same problem but here is the solution (found on an old mailling list). While connecting to the db, don't use the DriverManager.getConnection(url,login,passwd) API but instead DriverManager.getConnection(url,properties) and set your charSet in these properties. Example : Properties info = new Properties(); info.put(user,login); info.put(password,passwd); info.put(charSet,ISO8859_1); dbCon = DriverManager.getConnection(dbURL,info); While calling getString on a ResultSet, you should not get '?' chars any more. Hope this helps, Quentin Regards, René Pijlman [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] Re: Escape Processing problems
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. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] Re: Proposal to fix Statement.executeBatch()
On Mon, 27 Aug 2001 22:57:13 -0700, Barry Lind wrote: I do not know what the server does if you have autocommit enabled and you issue multiple statements in one try. As you know, Peter Eisentraut said on hackers that all statements in a semicolon-separated query string are processed as one single transaction. So, if in S1;S2;S3 S2 fails, both S1, S2 and S3 are rolled back and in effect they have all failed. I think this means we can implement your proposal. I've removed the paragraph saying that we can't and I've added the following to http://lab.applinet.nl/postgresql-jdbc/#Batch -+-+- The current implementation of Statement.executeBatch() in the JDBC driver does not provide any performance improvement compared to processing statements individually. This is because the driver executes the statements one-by-one when executeBatch() is called, using one round trip per statement. We intend to reimplement executeBatch() in the following way. Statement.executeBatch() will send all statements in a single semicolon separated query string, with only one round trip to the backend. This will provide a performance improvement, as intended by the JDBC specification. The updateCounts array will be set as described below. Note that the JDBC spec defines the meaning of the following special values: -2 the statement was executed successfully but the number of affected rows is unknown -3 the statement failed to execute successfully If all statements succeed, executeBatch() returns an updateCounts array with a row count for each statement in the batch, however the value for all but the last statement will be -2. The value for the last statement will be a proper update count. If a statement fails, executeBatch() throws a BatchUpdateException containing an updateCounts array with a row count of -3 for each statement in the batch. Note that the behaviour will be the same when autocommit is enabled and when it is disabled. Even with autocommit enabled, the backend will commit or rollback all statements in the semicolon-separated query string as a unit. The new implementation of executeBatch() will cause a change in behaviour of the driver: the driver will no longer return update counts for all statements in a batch like it currently does, it will return -2 (unknown) for most statements. However, this behaviour is allowed by the JDBC spec and applications should be prepared to handle it. -+-+- I see two more issues we need to decide on... 1) The JDBC spec requires Statement.executeBatch() to throw a BatchUpdateException if any of the statements does not return an update count (e.g. is a SELECT). How can we implement this? Do we need to parse the statements in the JDBC driver to detect SELECT's? It is a matter of interpretation, but it seems OK to me to just ignore this and return -2/-3 for SELECT's as well. In fact, perhaps we should allow SELECT's for function calls!? 2) The reimplementation may cause the driver to send very long statements to the backend. I heard something about an 8K limit. In what version of the backend was this limitation removed? I guess we should implement the new algorithm conditionally, so we'll only send multi-statement query strings to a backend that has no statement length limitation. Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] truncated InputStream returned with getAsciiInputStream()
On Thu, 30 Aug 2001 18:24:30 -0400, you wrote: So now, to store my data in the database, I use BLOB instead of TEXT. It works fine, my data is stored and retreived without any problem with method set and getAsciiStream()... until there is a byte with value -1 in my Stream. If the 43rd byte is -1, the stream has a length of 42. Can you post a small program that reproduces the problem? Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [JDBC] ? (question mark) characters
Ricardo Pardini wrote: they're all broken considering Portuguese characters like áéíóú (aeiou with accent)... they are replaced by question marks (?)... any ideas? What's the character encoding of the database? See http://postgresql.demunnikservices.nl/users-lounge/docs/7.1/admi n/multibyte.html (this documentation is about character encoding, not just multibyte support like the title suggests). And see also http://fts.postgresql.org/db/mw/msg.html?mid=1029464 about how multibyte support relates to character encoding support. Are these special characters converted when you store them in the database, or when you retrieve them from he database? Should I try to compile my own drivers? No, that won't fix it. Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Transaction Isolation - read uncommitted
On Mon, 27 Aug 2001 15:25:23 -0700, Ralf Reissig wrote: I there any support in postgres for reading uncommited data without a read lock ? No, there isn't. A dirty read is not possible with PostgreSQL. This is not JDBC specific. See http://www.postgresql.org/idocs/index.php?sql-set-transaction.html http://www.postgresql.org/idocs/index.php?transaction-iso.html Regards, René Pijlman [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
Re: [JDBC] JDBC changes for 7.2 - wish list item
On Mon, 27 Aug 2001 08:48:52 +1000 (EST), you wrote: It's been mentioned before, but a set of error numbers for database errors would make trapping exceptions and dealing with them gracefully a LOT simpler. I have java code that runs against Oracle, Informix, PostgreSQL, MS SQL Server and Cloudscape. All(?) the others have an error code as well as an error message and it's a lot easier to get the error code. I agree. Its on the list on http://lab.applinet.nl/postgresql-jdbc/#SQLException. This requires new functionality in the backend. Of course, they all have *different* error codes for the same error (ie primary key violation). Nothing is ever simple. Perhaps the SQLState string in SQLException can make this easier (if we can support this with PostgreSQL). This is supposed to contain a string identifying the exception, following the Open Group SQLState conventions. I'm not sure how useful these are. Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Re: Proposal to fix Statement.executeBatch()
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 4: Don't 'kill -9' the postmaster
[JDBC] (fwd) What should Statement.executeBatch() do with an empty batch?
FYI ... I posted this question on the Sun JDBC-interest list. This affects the PostgreSQL driver as well. Any thoughts? Should Statement.executeBatch() reset the list of commands to empty? The JDBC 2.0 API specification (1) doesn't mention this, but the Java tutorial does (2). 1) http://java.sun.com/j2se/1.3/docs/api/java/sql/Statement.html#executeBatch() 2) http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html Regards, René Pijlman ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[JDBC] JDBC Statement.executeBatch patch
Attached is a patch for current CVS, consisting of a cvs diff -c
for the changed files and a few new files:
- test/jdbc2/BatchExecuteTest.java
- util/MessageTranslator.java
- jdbc2/PBatchUpdateException.java
As an aside, is this the best way to submit a patch consisting
of both changed and new files? Or is there a smarter cvs command
which gets them all in one patch file?
This patch fixes batch processing in the JDBC driver to be
JDBC-2 compliant. Specifically, the changes introduced by this
patch are:
1) Statement.executeBatch() no longer commits or rolls back a
transaction, as this is not prescribed by the JDBC spec. Its up
to the application to disable autocommit and to commit or
rollback the transaction. Where JDBC talks about executing the
statements as a unit, it means executing the statements in one
round trip to the backend for better performance, it does not
mean executing the statements in a transaction.
2) Statement.executeBatch() now throws a BatchUpdateException()
as required by the JDBC spec. The significance of this is that
the receiver of the exception gets the updateCounts of the
commands that succeeded before the error occurred. In order for
the messages to be translatable, java.sql.BatchUpdateException
is extended by org.postgresql.jdbc2.PBatchUpdateException() and
the localization code is factored out from
org.postgresql.util.PSQLException to a separate singleton class
org.postgresql.util.MessageTranslator.
3) When there is no batch or there are 0 statements in the batch
when Statement.executeBatch() is called, do not throw an
SQLException, but silently do nothing and return an update count
array of length 0. The JDBC spec says Throws an SQLException if
the driver does not support batch statements, which is clearly
not the case. See testExecuteEmptyBatch() in
BatchExecuteTest.java for an example. The message
postgresql.stat.batch.empty is removed from the language
specific properties files.
4) When Statement.executeBatch() is performed, reset the
statement's list of batch commands to empty. The JDBC spec isn't
100% clear about this. This behaviour is only documented in the
Java tutorial
(http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html).
Note that the Oracle JDBC driver also resets the statement's
list in executeBatch(), and this seems the most reasonable
interpretation.
5) A new test case is added to the JDBC test suite which tests
various aspects of batch processing. See the new file
BatchExecuteTest.java.
Regards,
René Pijlman
Index: src/interfaces/jdbc/org/postgresql/errors.properties
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors.properties,v
retrieving revision 1.6
diff -c -r1.6 errors.properties
*** src/interfaces/jdbc/org/postgresql/errors.properties2001/08/21 00:37:23
1.6
--- src/interfaces/jdbc/org/postgresql/errors.properties2001/08/26 18:20:15
***
*** 61,67
postgresql.serial.noclass:No class found for {0}.
postgresql.serial.table:The table for {0} is not in the database. Contact the DBA,
as the database is in an inconsistent state.
postgresql.serial.underscore:Class names may not have _ in them. You supplied {0}.
- postgresql.stat.batch.empty:The batch is empty. There is nothing to execute.
postgresql.stat.batch.error:Batch entry {0} {1} was aborted.
postgresql.stat.maxfieldsize:An attempt to setMaxFieldSize() failed - compile time
default in force.
postgresql.stat.noresult:No results were returned by the query.
--- 61,66
Index: src/interfaces/jdbc/org/postgresql/errors_de.properties
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/errors_de.properties,v
retrieving revision 1.1
diff -c -r1.1 errors_de.properties
*** src/interfaces/jdbc/org/postgresql/errors_de.properties 2001/07/09 20:25:44
1.1
--- src/interfaces/jdbc/org/postgresql/errors_de.properties 2001/08/26 18:20:15
***
*** 65,71
postgresql.serial.noclass:Keine Klasse für Typ »{0}« gefunden
postgresql.serial.table:Keine Tabelle für Typ »{0}« in der Datenbank gefunden. Die
Datenbank ist in einem unbeständigen Zustand.
postgresql.serial.underscore:Zu serialisierende Klassennamen dürfen keine
Unterstriche (_) enthälten. Der angegebene Name war »{0}«.
- postgresql.stat.batch.empty:Der Anweisungs-Batch ist leer.
postgresql.stat.batch.error:Batch-Anweisung Nummer {0} ({1}) wurde abgebrochen.
postgresql.stat.maxfieldsize:setMaxFieldSize() is nicht möglich; die Grenze ist fest
eingebaut.
postgresql.stat.noresult:Die Abfrage ergab kein Ergebnis.
--- 65,70
Index: src/interfaces/jdbc/org/postgresql/errors_it.properties
===
RCS file:
Re: [JDBC] Status of JDBC test suite?
On Sun, 26 Aug 2001 21:30:45 +0200 (CEST), Anders Bengtsson wrote: Since it's non-obvious how to initialize it and how to use it, very few have. :-) 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? Most recently some updates in DatabaseMetaData broke some test cases. :-( 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'm not sure if that would be useful. Its a reasonable requirement for a database driver test suite that there actually is a database to test against :-) Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Re: Couple of patches for jdbc driver
On Thu, 23 Aug 2001 10:46:18 -0400 (EDT), Bruce Momjian wrote: Uh, CHANGELOG? :-) No, I haven't been doing that, figuring I would update it in the main release notes. However, I haven't started doing that yet, and in fact I don't think I know enough about jdbc to know how to describe the items. I think you should require us to post a release note with every patch. That would make great documentation for reviewing and evaluating the patch as well. Regards, René Pijlman ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[JDBC] Re: [PATCHES] JDBC patch for util.Serialize and jdbc2.PreparedStatement
On Thu, 23 Aug 2001 14:37:27 -0400, you wrote: a patch [...] that fixes the ability to serialize a simple java class into a postgres table. The current cvs seems completely broken in this support, so the patch puts it into working condition, granted that there are many limitations with serializing java classes into Postgres. I would appreciate it if you would explain to us a little more what does and doesn't work before and after applying this patch. [jdbc-list added to the CC] Regards, René Pijlman ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Accessing multiple Databases at the same time
On Mon, 20 Aug 2001 16:08:31 -0600, you wrote: Is there a way to SELECT tables or create JOINs from multiple databases through psql? [...] would this have to be handled programmatically with multiple database connections? Yes, you need multiple connections for that. By the way, if you really meant psql you're on the wrong list. This list is about Java DataBase Connectivity (JDBC). Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Unable to connect to database
On Fri, 17 Aug 2001 15:11:57 -0400, you wrote: Try a newer JDBC driver (from CVS). Standard support answer #1 :-) The driver is AFAIK 100% pure Java. I don't think there is a fix for a JVM segmentation fault in the driver, do you? the problem could be your JVM That's better advice :-) Seriously, I don't think we should be telling people to upgrade to the latest CVS version, unless there is a good and specific reason to assume that this might solve the problem. Regards, René Pijlman ---(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] No results exception on executeQuery()
On Thu, 16 Aug 2001 12:47:10 -0400, you wrote: I am seeing MANY of the following exception: You'd better tell us something about the code and the query that's throwing these exceptions. Regards, René Pijlman ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] select on multiple tables
On Wed, 15 Aug 2001 16:43:31 -0500, Ben Carterette wrote: I have a query like SELECT * FROM table1, table2 and I want to read values out of a ResultSet. What if the two tables have column names in common and I can't predict the column numbers? Is there any way to get table1.id and table2.id? rs.getString tells me The column name table1.id not found. Does this also happen when you explicitly name the columns? SELECT table1.id, ..., table2.id, ... FROM table1, table2 Or if that doesn't help, try if a column label with the AS clause works: SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...] http://www.postgresql.org/idocs/index.php?sql-select.html SELECT table.id AS id1, ..., table2.id AS id2 FROM table1, table2 And then rs.getString(id1); I think both solutions should work. Please let us know if they don't. Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] JDBC pg_description update needed for CVS tip
Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:
1) When connected to a backend = 7.2: use obj_description() and
col_description() instead of direct access to pg_description.
2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
no remarks.
Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: String object
containing an explanatory comment on the table/column/procedure,
which may be null. The default string no remarks was strictly
speaking incorrect, as it could not be distinguished from a real
user comment no remarks. So I removed the default string
completely.
Change 2 might break existing code that doesn't follow the JDBC
spec and isn't prepared to handle a null in the REMARKS column
of getTables()/getColumns()/getProcedures.
Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.
Regards,
René Pijlman
On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:
Would some JDBC hacker develop a patch for the following issue? The
change is just barely large enough that I don't want to commit untested
code for it --- but not having a Java development environment at hand,
I can't test the updated code.
The problem is in DatabaseMetaData.java (same code in both jdbc1 and
jdbc2, looks like). It does direct access to pg_description that isn't
right anymore. In getTables, instead of
java.sql.ResultSet dr = connection.ExecSQL(select description from
pg_description where objoid=+r.getInt(2));
it should be
java.sql.ResultSet dr = connection.ExecSQL(select
obj_description(+r.getInt(2)+,'pg_class'));
In getColumns, the change is a little more involved, because
pg_attribute doesn't have an OID column anymore. The initial query
can't fetch a.oid, but should fetch a.attrelid instead, and then the
pg_description query should become
java.sql.ResultSet dr = connection.ExecSQL(select
col_description(+r.getInt(1)+,+r.getInt(5)+));
(col_description takes the table OID and the column's attnum).
The reason this is more than a 3-line change is that it should be done
either the old way or the new way depending on whether server version =
7.2 or not, for backwards-compatibility of the driver.
It's possible there are other similar changes needed that I missed in a
quick lookover.
So, would some enterprising person fix the JDBC code to work with CVS
tip, and submit a patch?
thanks, 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
Regards,
René Pijlman
Index: src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
retrieving revision 1.24
diff -c -r1.24 DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java 2001/08/04
19:32:04 1.24
--- src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java 2001/08/13
17:31:51
***
*** 43,52
static final int iInt4Oid = 23; // OID for int4
static final int VARHDRSZ = 4; // length for int4
- // This is a default value for remarks
- private static final byte defaultRemarks[]=no remarks.getBytes();
-
-
public DatabaseMetaData(Connection conn)
{
this.connection = conn;
--- 43,48
***
*** 1517,1524
java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
- byte remarks[] = defaultRemarks;
-
f[0] = new Field(connection, PROCEDURE_CAT, iVarcharOid, 32);
f[1] = new Field(connection, PROCEDURE_SCHEM, iVarcharOid, 32);
f[2] = new Field(connection, PROCEDURE_NAME, iVarcharOid, 32);
--- 1513,1518
***
*** 1540,1546
tuple[1] = null;// Schema name
tuple[2] = r.getBytes(1); // Procedure name
tuple[3] = tuple[4] = tuple[5] = null; // Reserved
! tuple[6] = remarks; // Remarks
if (r.getBoolean(2))
tuple[7] =
Integer.toString(java.sql.DatabaseMetaData.procedureReturnsResult).getBytes();
--- 1534,1540
tuple[1] = null;// Schema name
tuple[2] = r.getBytes(1); // Procedure name
tuple[3] = tuple[4] = tuple[5] = null; // Reserved
!
Re: [JDBC] DatabaseMetaData
On Mon, 13 Aug 2001 12:07:59 -0400, you wrote: Ok, I see what you're looking at. However, the book states the escapes should only be supported when the underlying DBMS supports them and that escape syntaxes must be included for stored procedures etc where appropriate. Does PostgreSQL support stored procedures using a stored procedures escape syntax? If yes, we should return yes, if no, we should return no until that functionality is added. Both cases are allowed, AFAICS. The book doesn't mention that drivers should always return true for this procedure. You're right. Java 2 EE Specification, v1.2 Ah, I see, this is one of the subbooks in my book. Well, I think we'll have to check for J2EE conformance as well, since I guess the driver will be used on J2EE platforms more and more. Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[JDBC] Re: [PATCHES] JDBC Array Support, Take III
On Mon, 13 Aug 2001 16:16:27 -0400, you wrote: Thanks for your feedback (and patience). Enclosed is my third attempt at a patch to 7.1.2 to support Array. Thank you. I'll be glad to give it some testing later this week. [I think I've solved the mangled patch problem. Hotmail seems to try to format the text file, so gzipping it should solve this problem.] Yes, the patch format problem is gone. Unfortunately, patch rejects some of the changes in jdbc2/ResultSet.java. I guess that means this file has been changed in current CVS since you took your snapshot. Let me know if I'm getting closer to something useful. Absolutely :-) Regards, René Pijlman ---(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] cast in JDBC update
On 07 Aug 2001 13:39:09 +0200, you wrote: I am trying to update a field which contains an array of type INT4 via JDBC. Java source code? UPDATE statement? Table description? Regards, René Pijlman ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Retrieving field of type bigint[]
On Sun, 5 Aug 2001 23:08:16 +0900, you wrote: I am trying to retrieve field of type bigint[] from postgres DB. I tried most of getXXX methods of ResultSet abut I could not do it. The JDBC spec recommends ResultSet.getLong() for the JDBC type BIGINT: long getLong(int columnIndex) throws SQLException long getLong(String columnName) throws SQLException What is the problem precisely? I could not do it is not very informative. What's your source code? What's the column type? What versions of what are you using? What exception did you get? Regards, René Pijlman ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Problem building jdbc jars
On Wed, 1 Aug 2001 19:40:06 -0700, you wrote:
I get 100+ errors building. Many of the messages are complaining
that they cannot find javax.transaction.xa.Xid.
This is part of the Java Transaction API in J2EE:
http://java.sun.com/j2ee/transactions.html. Download and install
J2EE 1.2.1 from http://java.sun.com/j2ee/download.html#sdk and
that will probably fix it.
There is a line in the Ant build file
src/interfaces/jdbc/build.xml that's probably supposed to catch
this situation:
exclude name=${package}/xa/** unless=jdk1.2e+ /
I'm not sure why it doesn't in your case.
Next time you post such a problem, please quote the (first)
error message you get from the compiler, including the line
number.
Regards,
René Pijlman
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] What needs to be done?
On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote: The problem is that, as the PostgreSQL JDBC driver doesn't follow JDBC Standard I had to write some specific code for use it with PostgreSQL DB. So what exactly are the deviations from the standard that you encountered? Regards, René Pijlman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
