Обсуждение: Port Bug Report: Unable to insert large strings into a text datatype.
============================================================================
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:
---------
--------------------------------------------------------------------------
Re: [PORTS] Port Bug Report: Unable to insert large strings into a text datatype.
От
Bruce Momjian
Дата:
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