Обсуждение: 8.2dev-500.jdbc3 driver: PreparedStatement.toString() is buggy
Hi: There seems to be a bug with the 8.2dev-500 driver, running against a postgres 8.1 database. If I have a PreparedStatement, then when I say: System.out.println(ps) the the prepared statement that is printed is *missing* single quotes around data/time AND character/string values. This means that I cannot copy that preparedstatment and paste it directly into a psql session etc. For example, this is what a ps.toString() looks like: -------------------------------------- INSERT into alltypes (smallint_val, int_val, bigint_val, float_val, double_val, numeric_val, char_val, varchar_val, timestamp_val, bit_val, varbinary_val) values (5, 6, 7000, 3.14, 3.141, 11.32, foo2, bar2, 2005-12-19 09:55:23.464000 -0500, 1, <stream of 5 bytes>) ------------------------------------- Note, this is not valid sql since no quotes around 'foo2', 'bar2', '2005...' etc (which are respectively char and date types). This is definitely a regression, this did not happen with earlier drivers. Best regards, --j __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
j.random.programmer wrote: > There seems to be a bug with the 8.2dev-500 > driver, running against a postgres 8.1 database. > > If I have a PreparedStatement, then when I say: > > System.out.println(ps) > > the the prepared statement that is printed is > *missing* single quotes around data/time AND > character/string values. This means that I > cannot copy that preparedstatment and paste > it directly into a psql session etc. I don't think the driver or the spec has ever claimed that toString() gives you a valid SQL statement. -O
Oliver: > I don't think the driver or the spec has ever > claimed that toString() > gives you a valid SQL statement. True :-) However, note that it *used* to do so. This is a regression from the past driver. And it would be nice to have anyway, won't it ? Also note in the past, booleans would get converted to '1' and stored in BIT columns. With the latest driver, java booleans are sent as the number 1 (no quotes) and thus cannot be stored in a BIT column, in direct contradiction to the JDBC spec. (that's a different issue, see my other post but I have a feeling it may somehow be related). Best regards, --j __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
j.random.programmer wrote: > Oliver: > > >>I don't think the driver or the spec has ever >>claimed that toString() >>gives you a valid SQL statement. > > > True :-) > > However, note that it *used* to do so. This is a > regression from the past driver. And it would > be nice to have anyway, won't it ? Perhaps, but there are some implementation hurdles that mean it's not really worth it (for example: what if a parameter is actually a binary stream, and we're not reading it ahead of time?) If you have a patch to do this, though, we can certainly look at applying it. -O
Hi:
I am using the LATEST postgres 8 JDBC driver against
PostgresQL 8.1.x.
I have a O/R mapping tool that uses JDBC meta data to
generate Java
code. When using tables with a column whose datatype
is a domain,
the driver returns a typecode of 1111 (other).
Is there anyway to get information about the domain ?
I've tried
a) calling DataBaseMetaData.getAttributes(..) but
it's not implemented.
b) DataBaseMetaData.getUDTs(...) is not helpful
either.
A similar question was posted on this list about 18
months ago.
* Subject: meta data information returned for
domains
* Date: Fri, 3 Dec 2004 12:13:13 +0900
However, there wasn't a resolution then.
Is this problem solved/fixable ? Any suggestions ?
Here is the specific SQL.
--------------------------------------------------------
CREATE DOMAIN sex AS CHARACTER(1) CHECK (VALUE in
('m', 'f'));
CREATE TABLE subject (
subject_id SERIAL CONSTRAINT NN_subject_id NOT
NULL,
fname CHARACTER VARYING(99),
lname CHARACTER VARYING(99) CONSTRAINT NN_lname
NOT NULL,
dob DATE,
sex sex CONSTRAINT NN_sex NOT NULL,
CONSTRAINT PK_subject PRIMARY KEY (subject_id)
);
------------------------------------------------------
It all breaks for the 'sex' column. (and no, not using
domains is
not an option since the actual database is very very
complex).
:-)
Best regards,
--j
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
j.random.programmer wrote: > * Subject: meta data information returned for > domains > * Date: Fri, 3 Dec 2004 12:13:13 +0900 > > However, there wasn't a resolution then. I couldn't find this in the archives. Do you have a link? How would you expect domain info to be returned via the metadata interfaces? -O
On Sun, 18 Jun 2006, Oliver Jowett wrote: > j.random.programmer wrote: > >> * Subject: meta data information returned for >> domains >> * Date: Fri, 3 Dec 2004 12:13:13 +0900 >> >> However, there wasn't a resolution then. > > I couldn't find this in the archives. Do you have a link? > > How would you expect domain info to be returned via the metadata interfaces? > I vaguely recall a discussion of this suggesting the base type of a domain should be returned in the SOURCE_DATA_TYPE column of DatabaseMetaData.getColumns. This column was added in JDBC3 and we currently only support the JDBC2 columns. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String) Kris Jurka
> I couldn't find this in the archives. Do you have a > link? Here it is: http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00008.php > How would you expect domain info to be returned via > the metadata interfaces? I don't know :-) You and Kris figure it out. :-) But it's not a show-stopper if it's too hard to do. I worked around this by telling Datanamic Dezign (my modelling tool) to not write out the domain information seperately and if a column uses a domain, then create that column with the underlying type that the domain uses (as opposed to the domain itself). Best regards, --j __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
j.random.programmer wrote: >> How would you expect domain info to be returned via >> the metadata interfaces? > > I don't know :-) > > You and Kris figure it out. Well, without knowing what information you actually want to extract about the type it's a bit pointless taking this further. Is SOURCE_DATA_TYPE sufficient? How is whatever-data-it-is-that-you-need returned by other DB drivers? (I am not going to have time to actually do any implementation of this, I'm just trying to get enough information for someone else to consider implementing it) -O