Re: Obtaining precision and scale of NUMERIC types

Greetings,

My first post.

I am attempting to describe the table "donnie" from JDBC and I'm unable
to determine the precision and scale of numeric types. I've tried the
stable and beta versions of the JDBC driver and receive same behavior.

DB version string
PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)

I'm using the following code...

try {
DatabaseMetaData dbms = basicCon.driverConnection.getMetaData();
ResultSet columnNames;
columnNames = dbms.getColumns(null, null, "donnie", null);

while (columnNames.next()) {
System.out.print(columnNames.getString("COLUMN_NAME") + " ");

switch (columnNames.getInt("DATA_TYPE")) {
case java.sql.Types.INTEGER:
System.out.print("INTEGER");
break;
case java.sql.Types.VARCHAR:
System.out.print("VARCHAR (");
System.out.print(columnNames.getInt("COLUMN_SIZE") + ")");
break;
case java.sql.Types.NUMERIC:
System.out.print("NUMERIC (");
System.out.print(columnNames.getInt("COLUMN_SIZE") + ",");
System.out.print(columnNames.getInt("DECIMAL_DIGITS") +
")");
break;
}

System.out.println("");
}

} catch (SQLException e) {
System.out.print(e);
}
}

The output from the java program
a INTEGER
b VARCHAR (100)
c NUMERIC (65535,65535)
d NUMERIC (0,0)
The description from pgsql
Table "donnie"
Column | Type | Modifiers
--------+------------------------+-----------
a | integer |
b | character varying(100) |
c | numeric(7,3) |
d | numeric(12,4) |

Any help?
Donnie

Attached is patch to fix this problem. The driver was using the wrong
column from it's query to determine the size and digits of the result.

Kris Jurka

On Thu, 6 Feb 2003, Donald J. Armstrong wrote:

> Greetings,
>
> My first post.
>
> I am attempting to describe the table "donnie" from JDBC and I'm unable
> to determine the precision and scale of numeric types. I've tried the
> stable and beta versions of the JDBC driver and receive same behavior.
>
> DB version string
> PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
> 20020903 (Red Hat Linux 8.0 3.2-7)
>
> I'm using the following code...
>
> try {
> DatabaseMetaData dbms = basicCon.driverConnection.getMetaData();
> ResultSet columnNames;
> columnNames = dbms.getColumns(null, null, "donnie", null);
>
> while (columnNames.next()) {
> System.out.print(columnNames.getString("COLUMN_NAME") + " ");
>
> switch (columnNames.getInt("DATA_TYPE")) {
> case java.sql.Types.INTEGER:
> System.out.print("INTEGER");
> break;
> case java.sql.Types.VARCHAR:
> System.out.print("VARCHAR (");
> System.out.print(columnNames.getInt("COLUMN_SIZE") + ")");
> break;
> case java.sql.Types.NUMERIC:
> System.out.print("NUMERIC (");
> System.out.print(columnNames.getInt("COLUMN_SIZE") + ",");
> System.out.print(columnNames.getInt("DECIMAL_DIGITS") +
> ")");
> break;
> }
>
> System.out.println("");
> }
>
> } catch (SQLException e) {
> System.out.print(e);
> }
> }
>
> The output from the java program
> a INTEGER
> b VARCHAR (100)
> c NUMERIC (65535,65535)
> d NUMERIC (0,0)
> The description from pgsql
> Table "donnie"
> Column | Type | Modifiers
> --------+------------------------+-----------
> a | integer |
> b | character varying(100) |
> c | numeric(7,3) |
> d | numeric(12,4) |
>
> Any help?
> Donnie
>

Patch applied by Dave to cvs head.

--Barry

Kris Jurka wrote:
> Attached is patch to fix this problem. The driver was using the wrong
> column from it's query to determine the size and digits of the result.
>
> Kris Jurka
>
> On Thu, 6 Feb 2003, Donald J. Armstrong wrote:
>
>
>>Greetings,
>>
>>My first post.
>>
>>I am attempting to describe the table "donnie" from JDBC and I'm unable
>>to determine the precision and scale of numeric types. I've tried the
>>stable and beta versions of the JDBC driver and receive same behavior.
>>
>>DB version string
>>PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
>>20020903 (Red Hat Linux 8.0 3.2-7)
>>
>>I'm using the following code...
>>
>> try {
>> DatabaseMetaData dbms = basicCon.driverConnection.getMetaData();
>> ResultSet columnNames;
>> columnNames = dbms.getColumns(null, null, "donnie", null);
>>
>> while (columnNames.next()) {
>> System.out.print(columnNames.getString("COLUMN_NAME") + " ");
>>
>> switch (columnNames.getInt("DATA_TYPE")) {
>> case java.sql.Types.INTEGER:
>> System.out.print("INTEGER");
>> break;
>> case java.sql.Types.VARCHAR:
>> System.out.print("VARCHAR (");
>> System.out.print(columnNames.getInt("COLUMN_SIZE") + ")");
>> break;
>> case java.sql.Types.NUMERIC:
>> System.out.print("NUMERIC (");
>> System.out.print(columnNames.getInt("COLUMN_SIZE") + ",");
>> System.out.print(columnNames.getInt("DECIMAL_DIGITS") +
>>")");
>> break;
>> }
>>
>> System.out.println("");
>> }
>>
>> } catch (SQLException e) {
>> System.out.print(e);
>> }
>> }
>>
>>The output from the java program
>>a INTEGER
>>b VARCHAR (100)
>>c NUMERIC (65535,65535)
>>d NUMERIC (0,0)
>>The description from pgsql
>> Table "donnie"
>> Column | Type | Modifiers
>>--------+------------------------+-----------
>> a | integer |
>> b | character varying(100) |
>> c | numeric(7,3) |
>> d | numeric(12,4) |
>>
>>Any help?
>>Donnie
>>
>>
>>
>>------------------------------------------------------------------------
>>
>>Index: src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
>>===================================================================
>>RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
>>retrieving revision 1.16
>>diff -c -r1.16 AbstractJdbc1DatabaseMetaData.java
>>*** src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java 2003/02/04 09:20:08 1.16
>>--- src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java 2003/02/06 18:44:17
>>***************
>>*** 2352,2358 ****
>> }
>> else if (pgType.equals("numeric") || pgType.equals("decimal"))
>> {
>>! int attypmod = rs.getInt(8) - VARHDRSZ;
>> tuple[6] = Integer.toString( ( attypmod >> 16 ) & 0xffff ).getBytes();
>> tuple[8] = Integer.toString(attypmod & 0xffff).getBytes();
>> tuple[9] = "10".getBytes();
>>--- 2352,2358 ----
>> }
>> else if (pgType.equals("numeric") || pgType.equals("decimal"))
>> {
>>! int attypmod = rs.getInt("atttypmod") - VARHDRSZ;
>> tuple[6] = Integer.toString( ( attypmod >> 16 ) & 0xffff ).getBytes();
>> tuple[8] = Integer.toString(attypmod & 0xffff).getBytes();
>> tuple[9] = "10".getBytes();
>>
>>
>>------------------------------------------------------------------------
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster