Port Bug Report: Unable to insert large strings into a text datatype.

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

Your name : Steve Dyrdahl
Your email address : dyrdahl(at)reticular(dot)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:
---------

--------------------------------------------------------------------------