Re: [PORTS] Port Bug Report: Unable to insert large strings into a text datatype.

I have cleaned up some of those boundary conditions.  You can check the
current snapshot.


>
> ============================================================================
>                         POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name               : Steve Dyrdahl
> Your email address      : dyrdahl@reticular.com
>
> Category                : runtime: front-end: Java
> Severity                : non-critical
>
> Summary: Unable to insert large strings into a text datatype.
>
> System Configuration
> --------------------
>   Operating System   : Solaris
>
>   PostgreSQL version : 6.5
>
>   Compiler used      :
>
> Hardware:
> ---------
> Sparc Ultra 10
>
> Versions of other tools:
> ------------------------
> Fetched a PostgreSQL ver6.5 binary for Solaris 7 from
> ftp://postgresql.nextpath.com/pub/postgresql/bindist/6.5/
> JDBC driver for Postgresql 6.5 JDK1.1.x downloaded from:
> http://www.retep.org.uk/postgres/
>
> --------------------------------------------------------------------------
>
> Problem Description:
> --------------------
> I was attempting to insert large strings into a text
> datatype field using Prepared Statements and JDBC. A summary
> of what happens based on the size of the text string:
>
> IF size <= 8132
> THEN No error occurs.  The tuple is inserted correctly.
>
> IF 8133 <= size <= 8151
> THEN The following Exception is thrown:
> java.sql.SQLException: ERROR:  Tuple is too big: size 8200
> (Note the size value is dependent upon the size of the supplied string.)
>
> IF size >= 8152
> THEN The following exception gets thrown:
> java.lang.IllegalArgumentException: Argument # > Arg length
>
>
> Obviously getting the SQL Exception telling the user that
> the supplied field is too large is not really a problem.
> However, the IllegalArguementException is not very intuitive
> for determining the problem.
>
>
> --------------------------------------------------------------------------
>
> Test Case:
> ----------
> // This problem can be repeated by executing the following
> // java program.  You will need to include the jdbc driver
> // in your classpath, set the databasename in the url
> // variable, and the username variable.
> // In addition, it is possible the the hard-coded sizes
> // may need to be changed depending upon the system?
> import java.sql.*;
> import java.util.*;
>
> public class Bug
> {
>    private static final String url      = "jdbc:postgresql:databasename";
>    private static final String username = "username";
>    private static final String password = "";
>
>    public static void main( String[] args )
>    {
>       // Register the jdbc driver.
>       try
>       {
>          Class.forName("postgresql.Driver");
>       }
>       catch( Exception e )
>       {
>          System.out.println( "Unable to load jdbc driver." );
>          return;
>       }
>       // Create a connection to the database.
>       Connection con = null;
>       try
>       {
>          con = DriverManager.getConnection( url, username, password );
>       }
>       catch( Exception e )
>       {
>          System.out.println( "Unable to create a connection to the database." );
>          return;
>       }
>
>       // Create a temporary table.
>       try
>       {
>          String create =
>             "CREATE TABLE t (id int8 CONSTRAINT pk_t PRIMARY KEY, value text)";
>          Statement stmt = con.createStatement();
>          stmt.executeUpdate( create );
>          stmt.close();
>       }
>       catch( Exception e )
>       {
>          System.out.println( e.toString() );
>          System.out.println( "Unable to create table." );
>          return;
>       }
>       // Build a string with a length of 8125.
>       StringBuffer value = new StringBuffer();
>       for( int i = 0; i < 8125; i++ )
>       {
>          value.append( "X" );
>       }
>       // Construct the prepared statement.
>       PreparedStatement ps = null;
>       try
>       {
>          String insert = "INSERT INTO t (id, value) VALUES(?, ?)";
>          ps = con.prepareStatement( insert );
>       }
>       catch( Exception e )
>       {
>          System.out.println( e.toString() );
>          System.out.println( "Unable prepare statement." );
>       }
>       // Attempt to insert strings with a length of 8125 to 8175.
>       for( int j = 0; j < 50; j++ )
>       {
>          boolean ok = true;
>          // Set the parameters.
>          try
>          {
>             ps.setInt(    1, j );
>             ps.setString( 2, value.toString() );
>          }
>          catch( Exception e )
>          {
>             System.out.println( e.toString() );
>             System.out.println( "SET VALUES FAILED => " + value.length() );
>             ok = false;
>          }
>          // If the parameter setting was successful, insert the values.
>          if( ok )
>          {
>             try
>             {
>                ps.executeUpdate();
>                System.out.println( "INSERT SUCCESS => " + value.length() );
>             }
>             catch( Exception e )
>             {
>                System.out.println( e.toString() );
>                System.out.println( "INSERT FAILED  => " + value.length() );
>             }
>          }
>          value.append( "X" );
>       }
>       // Drop the temporary table.
>       try
>       {
>          ps.close();
>          String    drop = "DROP TABLE t";
>          Statement stmt = con.createStatement();
>          stmt.executeUpdate( drop );
>          stmt.close();
>       }
>       catch( Exception e )
>       {
>          System.out.println( e.toString() );
>          System.out.println( "Unable to drop table." );
>       }
>    }
> }
>
>
> --------------------------------------------------------------------------
>
> Solution:
> ---------
>
>
> --------------------------------------------------------------------------
>
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026