Thread
-
severe jdbc metadata problem
Tim Holloway <mtsinc@southeast.net> — 2000-02-04T00:50:44Z
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.