severe jdbc metadata problem - Mailing list pgsql-bugs

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :    Tim Holloway
Your email address    : mtsinc@leading.nete


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Linux 2.2

  PostgreSQL version (example: PostgreSQL-6.5)  :   PostgreSQL-6.5.2

  Compiler used (example:  gcc 2.8.0)        : N/A


Please enter a FULL description of your problem:
------------------------------------------------

jdbc: postgresql.Driver - Invalid column width returned from
 resultsetmetadata getColumnDisplaySize() method.




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Sun, in their infinite wisdom, did not provide a way to get abstract
metadata from a resultset without actually issuing a query. Symantec's
Visual Cafe finesses this issue by modifying the model query with "WHERE 1 > 2"
to return an empty resultset (I can appreciate this - one of my tables would
return over 5 MB everytime the metadata changed if data was actually retrieved
wholesale).

As near as I can read from the driver source, however, the postgresql jdbc driver
doesn't return abstract metadata - it returns metadata based on the
actual results OF THAT QUERY. The algorithm appears to compute the widest display(?)
value of each column counting down through the rows and then taking either that
number OR the column's name length in characters, whichever is greater. For an
mpty resultset, this means that the "column display widths" are always returned as
the widths of the column names.

While the above mentioned algorithm has its uses - e.g.: it works well for \dt-type
displays - I believe it is not the appropriate one for jdbc. The metadata is "impure"
in that it varies with the results of the query. The column name's length is not
neccesarily going to be displayed in the same location as the colum data in a Java
app. Scanning each otem to determine display width is potentially expensive,

Most importantly, one of the most popular Internet database application
generators in the industry is rendered almost completey useless.

Model code:
========== tear here

    // The following code can be used as a template.  Simply
    // substitute the appropriate url, login, and password, and then substitute the
    // SQL statement you want to send to the database.



    //----------------------------------------------------------------------------
    //
    // Module:      SimpleSelect.java
    //
    // Description: Test program for ODBC API interface.  This java application
    // will connect to a JDBC driver, issue a select statement
    // and display all result columns and rows
    //
    // Product:     JDBC to ODBC Bridge
    //
    // Author:      Karl Moss
    //
    // Date:        February, 1996
    //
    // Copyright:   1990-1996 INTERSOLV, Inc.
    // This software contains confidential and proprietary
    // information of INTERSOLV, Inc.
    //----------------------------------------------------------------------------

// NOTE: Adapted for postgresql jdbc Driver - tfh

import java.applet.*;
import java.net.URL;
import java.sql.*;

public class SimpleSelect extends java.applet.Applet  {


    public static void main (String args[]) {
    //public void init() {
        //super.init();

            String url   = "jdbc:postgresql://master/RandyScott";
            String query = "SELECT * FROM ARTISTS";
            try {
                Class.forName("postgresql.Driver");
            } catch ( ClassNotFoundException e ) {
                System.out.println("ERROR: postgresql.Driver not found!\n");
                return;
            }
System.out.println("Got class.forName");
                try {
System.out.println("Trying for connection A" );
            Connection con;
                  DriverManager.setLogStream(System.out);
System.out.println("Trying for connection B");
                    con = DriverManager.getConnection (
                        url, "apache", "HSD9511");
System.out.println("Didn't fault on connection.");



                    // Attempt to connect to a driver.  Each one
                    // of the registered drivers will be loaded until
                    // one is found that can process this URL

                    // If we were unable to connect, an exception
                    // would have been thrown.  So, if we get here,
                    // we are successfully connected to the URL

                    // Check for, and display and warnings generated
                    // by the connect.

                    checkForWarning (con.getWarnings ());

                    // Get the DatabaseMetaData object and display
                    // some information about the connection

                    DatabaseMetaData dma = con.getMetaData ();

                    System.out.println("\nConnected to " + dma.getURL());
                    System.out.println("Driver       " +
                            dma.getDriverName());
                    System.out.println("Version      " +
                            dma.getDriverVersion());
                    System.out.println("");

                    // Create a Statement object so we can submit
                    // SQL statements to the driver

                    Statement stmt = con.createStatement ();

                    // Submit a query, creating a ResultSet object

                    ResultSet rs = stmt.executeQuery (query);

                    // Display all columns and rows from the result set

                    dispResultSet (rs);

                    // Close the result set

                    rs.close();

                    // Close the statement

                    stmt.close();

                    // Close the connection

                    con.close();
            }
            catch (SQLException ex) {

                    // A SQLException was generated.  Catch it and
                    // display the error information.  Note that there
                    // could be multiple error objects chained
                    // together

            System.out.println ("\n*** SQLException caught ***\n");

            while (ex != null) {
                    System.out.println ("SQLState: " +
                                    ex.getSQLState ());
                    System.out.println ("Message:  " + ex.getMessage ());
                    System.out.println ("Vendor:   " +
                                    ex.getErrorCode ());
                    ex = ex.getNextException ();
                    System.out.println ("");
                    }
            }
            catch (java.lang.Exception ex) {

                    // Got some other type of exception.  Dump it.

                    ex.printStackTrace ();
            }
    }

    //-------------------------------------------------------------------
    // checkForWarning
    // Checks for and displays warnings.  Returns true if a warning
    // existed
    //-------------------------------------------------------------------

    private static boolean checkForWarning (SQLWarning warn)
                    throws SQLException  {
            boolean rc = false;

            // If a SQLWarning object was given, display the
            // warning messages.  Note that there could be
            // multiple warnings chained together

            if (warn != null) {
                    System.out.println ("\n *** Warning ***\n");
                    rc = true;
                    while (warn != null) {
                            System.out.println ("SQLState: " +
                                    warn.getSQLState ());
                            System.out.println ("Message:  " +
                                    warn.getMessage ());
                            System.out.println ("Vendor:   " +
                                    warn.getErrorCode ());
                            System.out.println ("");
                            warn = warn.getNextWarning ();
                    }
            }
            return rc;
    }

    //-------------------------------------------------------------------
    // dispResultSet
    // Displays all columns and rows in the given result set
    //-------------------------------------------------------------------

    private static void dispResultSet (ResultSet rs)
            throws SQLException
    {
            int i;

            // Get the ResultSetMetaData.  This will be used for
            // the column headings

            ResultSetMetaData rsmd = rs.getMetaData ();

            // Get the number of columns in the result set

            int numCols = rsmd.getColumnCount ();

            // Display column headings

            for (i=1; i<=numCols; i++) {
                    if (i > 1) System.out.print(",");
                    System.out.print(rsmd.getColumnLabel(i));
                    /// Display COLUMN WIDTH ////
                    System.out.print("(" + String.valueOf(rsmd.getColumnDisplayWidth(i)) + ") ");
            }
            System.out.println("");

            // Display data, fetching until end of the result set

            boolean more = rs.next ();
            while (more) {

                    // Loop through each column, getting the
                    // column data and displaying

                    for (i=1; i<=numCols; i++) {
                            if (i > 1) System.out.print(",");
                            System.out.print(rs.getString(i));
                    }
                    System.out.println("");

                    // Fetch the next result set row

                    more = rs.next ();
            }
    }

}



========== tear here



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Apparently Java's definition of metadata is not the same as other querents.
Thus a Java-conforming type of metadata needs to be sent back from the
server when so requested.