Обсуждение: getColumnDisplayWidth() returns 0x7fffffff for char varying
This may not be a JDBC issue, but I encountered it using JDBC and a remote host
My installation is:
DBMS: PostgreSQL 8.3.4 (Windows XP)
JDBC Driver: PostgreSQL Native Driver PostgreSQL 8.4devel JDBC4 (build 700)
I have a current build and have run the test suite:
runtest:
[junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite
[junit] Tests run: 270, Failures: 0, Errors: 0, Time elapsed: 99.485 sec
[junit] Testsuite: org.postgresql.test.jdbc2.optional.OptionalTestSuite
[junit] Tests run: 40, Failures: 0, Errors: 0, Time elapsed: 8.125 sec
[junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite
[junit] Tests run: 65, Failures: 0, Errors: 0, Time elapsed: 7.859 sec
[junit] Testsuite: org.postgresql.test.xa.XATestSuite
[junit] Tests run: 8, Failures: 0, Errors: 0, Time elapsed: 1.407 sec
[junit] Testsuite: org.postgresql.test.jdbc4.Jdbc4TestSuite
[junit] Tests run: 24, Failures: 0, Errors: 0, Time elapsed: 2.296 sec
test:
BUILD SUCCESSFUL
My problem is that getColumnDisplayWidth() returns the widths
column(1) uname(2147483647)
column(2) pass(2147483647)
column(3) timestamp(11)
column(4) icon1(11)
column(5) icon2(11)
column(6) icon3(11)
column(7) type(11)
column(8) creation(11)
column(9) until(11)
column(10) canxfer(1)
for the following table:
CREATE TABLE vcnmaster
(
uname character varying NOT NULL,
pass character varying,
"timestamp" integer,
icon1 integer,
icon2 integer,
icon3 integer,
"type" integer,
creation integer,
"until" integer,
canxfer boolean,
CONSTRAINT vcnmasterkey PRIMARY KEY (uname)
)
WITH (OIDS=FALSE);
Is this expected behavior?
Thanks.
Dan
PS. I can provide an Ethereal capture of the interaction if needed.
> This may not be a JDBC issue, but I encountered it using JDBC and a > remote host > > My installation is: > DBMS: PostgreSQL 8.3.4 (Windows XP) > JDBC Driver: PostgreSQL Native Driver PostgreSQL 8.4devel JDBC4 (build > 700) > > I have a current build and have run the test suite: > > runtest: > [junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite > [junit] Tests run: 270, Failures: 0, Errors: 0, Time elapsed: > 99.485 sec > > [junit] Testsuite: > org.postgresql.test.jdbc2.optional.OptionalTestSuite > [junit] Tests run: 40, Failures: 0, Errors: 0, Time elapsed: 8.125 sec > > [junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite > [junit] Tests run: 65, Failures: 0, Errors: 0, Time elapsed: 7.859 sec > > [junit] Testsuite: org.postgresql.test.xa.XATestSuite > [junit] Tests run: 8, Failures: 0, Errors: 0, Time elapsed: 1.407 sec > > [junit] Testsuite: org.postgresql.test.jdbc4.Jdbc4TestSuite > [junit] Tests run: 24, Failures: 0, Errors: 0, Time elapsed: 2.296 sec > > > test: > > BUILD SUCCESSFUL > > My problem is that getColumnDisplayWidth() returns the widths > > column(1) uname(2147483647) > column(2) pass(2147483647) > column(3) timestamp(11) > column(4) icon1(11) > column(5) icon2(11) > column(6) icon3(11) > column(7) type(11) > column(8) creation(11) > column(9) until(11) > column(10) canxfer(1) > > for the following table: > > CREATE TABLE vcnmaster > ( > uname character varying NOT NULL, > pass character varying, > "timestamp" integer, > icon1 integer, > icon2 integer, > icon3 integer, > "type" integer, > creation integer, > "until" integer, > canxfer boolean, > CONSTRAINT vcnmasterkey PRIMARY KEY (uname) > ) > WITH (OIDS=FALSE); > > Is this expected behavior? > > Thanks. > > Dan > > PS. I can provide an Ethereal capture of the interaction if needed. Hello I can not answer to the expected behavior, but I maintain an eye on various output for data types from the PostgreSQL JDBC for the MyJSQLView application. Though I have not run the current driver here is data from the last JDBC driver tested. The last number is getColumnDisplaySize() since it looks like the Java API only specifies this method for ResultSetMetaData, not getColumnDisplayWidth(). PostgreSQL 8.3-603 1 data_type_id Data Type Id java.lang.Integer int4 11 2 smallint_type Smallint Type java.lang.Integer int2 6 3 int_type Int Type java.lang.Integer int4 11 4 bigint_type Bigint Type java.lang.Long int8 20 5 decimal_type Decimal Type java.math.BigDecimal numeric 18 6 numeric_type Numeric Type java.math.BigDecimal numeric 12 7 real_type Real Type java.lang.Float float4 14 8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24 9 serial_type Serial Type java.lang.Integer int4 11 10 bigserial_type Bigserial Type java.lang.Long int8 20 11 varchar_type Varchar Type java.lang.String varchar 30 12 char_type Char Type java.lang.String bpchar 30 13 text_type Text Type java.lang.String text 2147483647 14 bytea_type Bytea Type [B bytea 2147483647 15 date_type Date Type java.sql.Date date 13 16 time_type Time Type java.sql.Time time 15 17 timetz_type Timetz Type java.sql.Time timetz 21 18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29 19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35 20 interval_type Interval Type org.postgresql.util.PGInterval interval 49 21 boolean_type Boolean Type java.lang.Boolean bool 1 22 point_type Point Type org.postgresql.geometric.PGpoint point 2147483647 23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg lseg 2147483647 24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647 25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647 26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon polygon 2147483647 27 circle_type Circle Type org.postgresql.geometric.PGcircle circle 2147483647 28 cidr_type Cidr Type java.lang.Object cidr 2147483647 29 inet_type Inet Type java.lang.Object inet 2147483647 30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647 31 bit2_type Bit2 Type java.lang.Boolean bit 2 32 bitvarying5_type Bitvarying5 Type java.lang.Object varbit 5 danap
> > >> This may not be a JDBC issue, but I encountered it using JDBC and a >> remote host >> >> My installation is: >> DBMS: PostgreSQL 8.3.4 (Windows XP) >> JDBC Driver: PostgreSQL Native Driver PostgreSQL 8.4devel JDBC4 >> (build 700) >> >> I have a current build and have run the test suite: >> >> runtest: >> [junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite >> [junit] Tests run: 270, Failures: 0, Errors: 0, Time elapsed: >> 99.485 sec >> >> [junit] Testsuite: >> org.postgresql.test.jdbc2.optional.OptionalTestSuite >> [junit] Tests run: 40, Failures: 0, Errors: 0, Time elapsed: 8.125 >> sec >> >> [junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite >> [junit] Tests run: 65, Failures: 0, Errors: 0, Time elapsed: 7.859 >> sec >> >> [junit] Testsuite: org.postgresql.test.xa.XATestSuite >> [junit] Tests run: 8, Failures: 0, Errors: 0, Time elapsed: 1.407 sec >> >> [junit] Testsuite: org.postgresql.test.jdbc4.Jdbc4TestSuite >> [junit] Tests run: 24, Failures: 0, Errors: 0, Time elapsed: 2.296 >> sec >> >> >> test: >> >> BUILD SUCCESSFUL >> >> My problem is that getColumnDisplayWidth() returns the widths >> >> column(1) uname(2147483647) >> column(2) pass(2147483647) >> column(3) timestamp(11) >> column(4) icon1(11) >> column(5) icon2(11) >> column(6) icon3(11) >> column(7) type(11) >> column(8) creation(11) >> column(9) until(11) >> column(10) canxfer(1) >> >> for the following table: >> >> CREATE TABLE vcnmaster >> ( >> uname character varying NOT NULL, >> pass character varying, >> "timestamp" integer, >> icon1 integer, >> icon2 integer, >> icon3 integer, >> "type" integer, >> creation integer, >> "until" integer, >> canxfer boolean, >> CONSTRAINT vcnmasterkey PRIMARY KEY (uname) >> ) >> WITH (OIDS=FALSE); >> >> Is this expected behavior? >> >> Thanks. >> >> Dan >> >> PS. I can provide an Ethereal capture of the interaction if needed. > > > > Hello I can not answer to the expected behavior, but I maintain an eye on > various output for data types from the PostgreSQL JDBC for the MyJSQLView > application. Though I have not run the current driver here is data from > the last JDBC driver tested. The last number is getColumnDisplaySize() > since > it looks like the Java API only specifies this method for > ResultSetMetaData, > not getColumnDisplayWidth(). > > PostgreSQL 8.3-603 > > 1 data_type_id Data Type Id java.lang.Integer int4 11 > 2 smallint_type Smallint Type java.lang.Integer int2 6 > 3 int_type Int Type java.lang.Integer int4 11 > 4 bigint_type Bigint Type java.lang.Long int8 20 > 5 decimal_type Decimal Type java.math.BigDecimal numeric 18 > 6 numeric_type Numeric Type java.math.BigDecimal numeric 12 > 7 real_type Real Type java.lang.Float float4 14 > 8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24 > 9 serial_type Serial Type java.lang.Integer int4 11 > 10 bigserial_type Bigserial Type java.lang.Long int8 20 > 11 varchar_type Varchar Type java.lang.String varchar 30 > 12 char_type Char Type java.lang.String bpchar 30 > 13 text_type Text Type java.lang.String text 2147483647 > 14 bytea_type Bytea Type [B bytea 2147483647 > 15 date_type Date Type java.sql.Date date 13 > 16 time_type Time Type java.sql.Time time 15 > 17 timetz_type Timetz Type java.sql.Time timetz 21 > 18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29 > 19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35 > 20 interval_type Interval Type org.postgresql.util.PGInterval interval 49 > 21 boolean_type Boolean Type java.lang.Boolean bool 1 > 22 point_type Point Type org.postgresql.geometric.PGpoint point > 2147483647 > 23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg > lseg 2147483647 > 24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647 > 25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647 > 26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon > polygon 2147483647 > 27 circle_type Circle Type org.postgresql.geometric.PGcircle circle > 2147483647 > 28 cidr_type Cidr Type java.lang.Object cidr 2147483647 > 29 inet_type Inet Type java.lang.Object inet 2147483647 > 30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647 > 31 bit2_type Bit2 Type java.lang.Boolean bit 2 > 32 bitvarying5_type Bitvarying5 Type java.lang.Object varbit 5 > > danap > I apologize for convoluting the subject futher, but clarification on the data shown with an update to table used to generate with character varying included. 11 varchar_type_30 Varchar Type 30 java.lang.String varchar 30 *12 charvarying_type Charvarying Type java.lang.String varchar 2147483647 13 char_type Char Type java.lang.String bpchar 30 14 text_type Text Type java.lang.String text 2147483647 -- -- MyJSQLView SQL Dump -- Version: 3.02 -- WebSite: http://myjsqlview.org -- -- Host: 127.0.0.1 -- Generated On: 2009.06.07 AD at 10:25:40 MDT -- SQL version: PostgreSQL 8.3.3 -- Database: postgresql_datatypes -- -- ------------------------------------------ -- -- Table structure for table "public"."postgresqltypes" -- DROP TABLE IF EXISTS "public"."postgresqltypes"; CREATE TABLE "public"."postgresqltypes" ( "data_type_id" serial NOT NULL, "smallint_type" smallint DEFAULT NULL, "int_type" integer DEFAULT NULL, "bigint_type" bigint DEFAULT NULL, "decimal_type" numeric(16,2) DEFAULT NULL, "numeric_type" numeric(10,2) DEFAULT NULL, "real_type" real DEFAULT NULL, "doubleprecision_type" double precision DEFAULT NULL, "serial_type" serial NOT NULL, "bigserial_type" bigserial NOT NULL, "varchar_type_30" varchar(30) DEFAULT NULL, "charvarying_type" character varying DEFAULT NULL, "char_type" char(30) DEFAULT NULL, "text_type" text DEFAULT NULL, "bytea_type" bytea DEFAULT NULL, "date_type" date DEFAULT NULL, "time_type" time without time zone DEFAULT NULL, "timetz_type" time with time zone DEFAULT NULL, "timestamp_type" timestamp without time zone DEFAULT '2007-10-14 00:00:00', "timestamptz_type" timestamp with time zone DEFAULT NULL, "interval_type" interval DEFAULT NULL, "boolean_type" boolean DEFAULT NULL, "point_type" point DEFAULT NULL, "linesegment_type" lseg DEFAULT NULL, "box_type" box DEFAULT NULL, "path_type" path DEFAULT NULL, "polygon_type" polygon DEFAULT NULL, "circle_type" circle DEFAULT NULL, "cidr_type" cidr DEFAULT NULL, "inet_type" inet DEFAULT NULL, "macaddr_type" macaddr DEFAULT NULL, "bit2_type" bit(2) DEFAULT NULL, "bitvarying5_type" bit varying(5) DEFAULT NULL, PRIMARY KEY ("data_type_id") ); danap
dmp wrote: >> >> >>> This may not be a JDBC issue, but I encountered it using JDBC and a >>> remote host >>> >>> My installation is: >>> DBMS: PostgreSQL 8.3.4 (Windows XP) >>> JDBC Driver: PostgreSQL Native Driver PostgreSQL 8.4devel JDBC4 >>> (build 700) >>> >>> I have a current build and have run the test suite: ... clip ... >>> >>> My problem is that getColumnDisplayWidth() returns the widths >>> >>> column(1) uname(2147483647) >>> column(2) pass(2147483647) >>> column(3) timestamp(11) >>> column(4) icon1(11) >>> column(5) icon2(11) >>> column(6) icon3(11) >>> column(7) type(11) >>> column(8) creation(11) >>> column(9) until(11) >>> column(10) canxfer(1) >>> >>> for the following table: >>> >>> CREATE TABLE vcnmaster >>> ( >>> uname character varying NOT NULL, >>> pass character varying, >>> "timestamp" integer, >>> icon1 integer, >>> icon2 integer, >>> icon3 integer, >>> "type" integer, >>> creation integer, >>> "until" integer, >>> canxfer boolean, >>> CONSTRAINT vcnmasterkey PRIMARY KEY (uname) >>> ) >>> WITH (OIDS=FALSE); >>> >>> Is this expected behavior? >>> >>> Thanks. >>> >>> Dan >>> >>> PS. I can provide an Ethereal capture of the interaction if needed. >> >> >> >> Hello I can not answer to the expected behavior, but I maintain an eye on >> various output for data types from the PostgreSQL JDBC for the MyJSQLView >> application. Though I have not run the current driver here is data from >> the last JDBC driver tested. The last number is getColumnDisplaySize() >> since >> it looks like the Java API only specifies this method for >> ResultSetMetaData, >> not getColumnDisplayWidth(). >> >> PostgreSQL 8.3-603 >> >> 1 data_type_id Data Type Id java.lang.Integer int4 11 >> 2 smallint_type Smallint Type java.lang.Integer int2 6 >> 3 int_type Int Type java.lang.Integer int4 11 >> 4 bigint_type Bigint Type java.lang.Long int8 20 >> 5 decimal_type Decimal Type java.math.BigDecimal numeric 18 >> 6 numeric_type Numeric Type java.math.BigDecimal numeric 12 >> 7 real_type Real Type java.lang.Float float4 14 >> 8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24 >> 9 serial_type Serial Type java.lang.Integer int4 11 >> 10 bigserial_type Bigserial Type java.lang.Long int8 20 >> 11 varchar_type Varchar Type java.lang.String varchar 30 >> 12 char_type Char Type java.lang.String bpchar 30 >> 13 text_type Text Type java.lang.String text 2147483647 >> 14 bytea_type Bytea Type [B bytea 2147483647 >> 15 date_type Date Type java.sql.Date date 13 >> 16 time_type Time Type java.sql.Time time 15 >> 17 timetz_type Timetz Type java.sql.Time timetz 21 >> 18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29 >> 19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35 >> 20 interval_type Interval Type org.postgresql.util.PGInterval interval 49 >> 21 boolean_type Boolean Type java.lang.Boolean bool 1 >> 22 point_type Point Type org.postgresql.geometric.PGpoint point >> 2147483647 >> 23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg >> lseg 2147483647 >> 24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647 >> 25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647 >> 26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon >> polygon 2147483647 >> 27 circle_type Circle Type org.postgresql.geometric.PGcircle circle >> 2147483647 >> 28 cidr_type Cidr Type java.lang.Object cidr 2147483647 >> 29 inet_type Inet Type java.lang.Object inet 2147483647 >> 30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647 >> 31 bit2_type Bit2 Type java.lang.Boolean bit 2 >> 32 bitvarying5_type Bitvarying5 Type java.lang.Object varbit 5 >> >> danap >> > > I apologize for convoluting the subject futher, but clarification on the > data shown > with an update to table used to generate with character varying included. > > 11 varchar_type_30 Varchar Type 30 java.lang.String varchar 30 > *12 charvarying_type Charvarying Type java.lang.String varchar 2147483647 > 13 char_type Char Type java.lang.String bpchar 30 > 14 text_type Text Type java.lang.String text 2147483647 > > -- > -- MyJSQLView SQL Dump > -- Version: 3.02 > -- WebSite: http://myjsqlview.org > -- > -- Host: 127.0.0.1 > -- Generated On: 2009.06.07 AD at 10:25:40 MDT > -- SQL version: PostgreSQL 8.3.3 > -- Database: postgresql_datatypes > -- > > -- ------------------------------------------ > > -- > -- Table structure for table "public"."postgresqltypes" > -- > > DROP TABLE IF EXISTS "public"."postgresqltypes"; > CREATE TABLE "public"."postgresqltypes" ( > "data_type_id" serial NOT NULL, > "smallint_type" smallint DEFAULT NULL, > "int_type" integer DEFAULT NULL, > "bigint_type" bigint DEFAULT NULL, > "decimal_type" numeric(16,2) DEFAULT NULL, > "numeric_type" numeric(10,2) DEFAULT NULL, > "real_type" real DEFAULT NULL, > "doubleprecision_type" double precision DEFAULT NULL, > "serial_type" serial NOT NULL, > "bigserial_type" bigserial NOT NULL, > "varchar_type_30" varchar(30) DEFAULT NULL, > "charvarying_type" character varying DEFAULT NULL, > "char_type" char(30) DEFAULT NULL, > "text_type" text DEFAULT NULL, > "bytea_type" bytea DEFAULT NULL, > "date_type" date DEFAULT NULL, > "time_type" time without time zone DEFAULT NULL, > "timetz_type" time with time zone DEFAULT NULL, > "timestamp_type" timestamp without time zone DEFAULT '2007-10-14 > 00:00:00', > "timestamptz_type" timestamp with time zone DEFAULT NULL, > "interval_type" interval DEFAULT NULL, > "boolean_type" boolean DEFAULT NULL, > "point_type" point DEFAULT NULL, > "linesegment_type" lseg DEFAULT NULL, > "box_type" box DEFAULT NULL, > "path_type" path DEFAULT NULL, > "polygon_type" polygon DEFAULT NULL, > "circle_type" circle DEFAULT NULL, > "cidr_type" cidr DEFAULT NULL, > "inet_type" inet DEFAULT NULL, > "macaddr_type" macaddr DEFAULT NULL, > "bit2_type" bit(2) DEFAULT NULL, > "bitvarying5_type" bit varying(5) DEFAULT NULL, > PRIMARY KEY ("data_type_id") > ); > > danap If understand your output, you are seeing the same thing I am. getColumnDisplayWidth() is a ResultSetMetaData method, andResultSetMetaData is acquired from a ResultSet by getMetaData(). But it appears that instead of getting metadata aboutthe result set, getColumnDisplayWidth() is providing metadata about the table schema (DatabaseMetaData). Of your fouritems: 11 varchar_type_30 Varchar Type 30 java.lang.String varchar 30 12 charvarying_type Charvarying Type java.lang.String varchar 2147483647 13 char_type Char Type java.lang.String bpchar 30 14 text_type Text Type java.lang.String text 2147483647 Three of them (11, 12, and 14) could potentially have column widths anywhere from 0 up to 30 in the case of 11, and 0 upto anything in the case of 12 and 14. But the code in AbstractJdbc2ResultSetMetaData appears not to be scanning the ResultSet. If there is a consensus that this is incorrect, I'd be happy to undertake a fix. Dan
Dan Evans wrote: > My problem is that getColumnDisplayWidth() returns the widths > > column(1) uname(2147483647) This has been discussed at some length previously, check the list archives. (quick summary from memory: for an unbounded character varying type, there's nothing really sensible we can return - that column could have a gigabyte of text for all we know. We originally returned -1 but that broke some common RowSet implementations. Now we return Integer.MAX_VALUE) -O
Oliver Jowett wrote: > Dan Evans wrote: > >> My problem is that getColumnDisplayWidth() returns the widths >> >> column(1) uname(2147483647) > > This has been discussed at some length previously, check the list archives. > > (quick summary from memory: for an unbounded character varying type, > there's nothing really sensible we can return - that column could have a > gigabyte of text for all we know. We originally returned -1 but that > broke some common RowSet implementations. Now we return Integer.MAX_VALUE) > > -O > I did check the archives before posting, but the hits on getColumnDisplayWidth() did not seem relevant. If you can pointto the approximate date, I'd appreciate it. For any ResultSet that is completely contained in the address space of the requestor, the return value can be the maximumlength of the values for the requested column in the result set. If the ResultSet is not complete in the requestor'saddress space at the time of the getColumnDisplayWidth() call, that might be the time to return Integer.MAX_VALUEsignifying no useful information. Most ResultSet's are reasonably small, so I would hope that doesn't happenoften. But even then, some useful information can be provided to the caller. The maximum length across the receivedrows can be returned, with a negative value indicating that this is only an estimate based on the received rows.However, if negative values break some implementations, then that is precluded. Dan