Why does PreparedStatement insert change space to empty string?
Breck Carter
single space to the empty string, whereas an INSERT executed via a
Statement work OK?
The class at the end of the message is a standalone demonstration; it
even creates the table it uses :)
After running the class, use dbisql to see the rows it inserted:
begin
select id, name, string ( '[', name, ']' ) from test;
select * from test where name = ' ';
select * from test where name = '';
end
Breck Using 7.0.3.2082
=====
import java.sql.*; // JDBC
import com.sybase.jdbc.*; // Sybase jConnect
import java.util.Properties; // Properties
import sybase.sql.*; // Sybase utilities
public class StringTest3{
private static Connection connection1;
public static void main( String args[] ){
System.out.println( "main() started" );
connection1 = getASAConnection
( "localhost", "2638", "SYSADM", "SYSADM" );
if ( connection1 != null ) {
System.out.println( "Connection 1 successful" );
} else {
System.out.println( "Connection 1 failed" );
}
try {
selectTest ( connection1 );
}
catch ( Exception exception ) {
System.out.println( "Error: " + exception.getMessage() );
exception.printStackTrace();
}
} // main()
private static Connection getASAConnection
( String machine,
String port,
String userID,
String password ) {
Connection connection;
String url;
Properties properties;
connection = null;
url = "jdbc:sybase:Tds:" + machine + ":" + port;
properties = new Properties();
properties.put ( "user", userID );
properties.put ( "password", password );
try {
Class.forName ( "com.sybase.jdbc.SybDriver" )
.newInstance();
connection = DriverManager.getConnection
( url, properties );
connection.setAutoCommit( false ) ;
}
catch ( Exception exception ) {
System.out.println ( "Error: " + exception.getMessage() );
exception.printStackTrace();
}
System.out.println ( "Connection url: '" + url + "'" );
return connection;
} // getASAConnection()
private static void selectTest
( Connection connection ) throws Exception {
ResultSet rs = null;
PreparedStatement pstatement = null;
Statement statement = null;
String space = " ";
System.out.println ( "selectTest() called" );
if ( connection != null ) {
statement = connection.createStatement();
statement.execute( "CREATE TABLE TEST( id INTEGER NOT NULL, name
VARCHAR ( 1024 ), PRIMARY KEY ( id ))");
System.out.println ( "CREATE done" );
statement.executeUpdate( "INSERT INTO TEST VALUES ( 1, ' ')");
System.out.println ( "INSERT 1 done" );
pstatement = connection.prepareStatement( "INSERT INTO TEST
values ( 2, ?)");
pstatement.setString(1 , " ");
pstatement.executeUpdate( );
System.out.println ( "INSERT 2 done" );
pstatement = connection.prepareStatement( "INSERT INTO TEST
values ( 3, ?)");
pstatement.setString(1 , space);
pstatement.executeUpdate( );
System.out.println ( "INSERT 3 done" );
pstatement = connection.prepareStatement( "SELECT * FROM TEST
WHERE NAME = ' '" );
rs = pstatement.executeQuery( );
while ( rs.next() ) {
System.out.println ( "Found iid = " + rs.getInt( "id") );
}
statement.execute( "COMMIT");
} // if ( connection != null )
} // selectTest()
} // class StringTest3
bca...@risingroad.com
Mobile and Distributed Enterprise Database Applications
-----
Visit http://www.risingroad.com for Tips and Techniques
[TeamSybase]
Jason Hinsperger
returns a string
with a single blank character in it (ala ASE). The reason for this is that ASE
does not distinguish
between a NULL string and an empty string, but a string with a single blank in
it is equivelent to
the empty string in ASE. Unfortunately this behaviour was causing some users
great grief
and since the TDS protocol does not allow for both a NULL string and a true
empty string to be
returned, we now have an option which will return an empty string as a NULL
string for TDS
connections ONLY.
The option is called: "TDS_EMPTY_STRING_IS_NULL", available as of 7.0.2 and is
by default set to OFF.
When set to OFF empty strings will be returned as a string with one blank in it
for TDS connections.
This behaviour has been verified to be consistent with ASE.
When the option is set to ON empty strings will be returned as NULL strings for
TDS connections.
This behaviour has been verified to be consistent with Oracle.
Again be advised that the option only affects TDS connections (ie. Open client
and JConnect). Non-TDS connections will always distinguish empty strings from
NULL strings.
Note that jConnect 5.5 and up implements a new version of the TDS specification
which allows the
server to return an optional status byte to indicate to the client if the string
is NULL or empty. ASA 8.0 and up will return this status byte to clients using
jConnect 5.5 and up provided the client is willing
to deal with that information. In this case, the empty string will be returned
as an empty string and
the NULL string will be returned as NULL and the above option will be ignored.
The above option
will still be supported in 8.0 and up for clients using an earlier version of
jConnect or for clients who
do not want the status byte information returned.
--
Jason Hinsperger
International and Sustaining Engineering
Adaptive Server Anywhere
Breck Carter
Java class below doesn't even *do* a SELECT :)
Here's how to reproduce the problem:
(1) Compile and execute StringTest3.java below, against an ASA 7.0.3
database. You may have to edit the userid and password.
(2) Use DBISQL to look at what it inserted, as follows...
begin
select id, name, string ( '[', name, ']' ) from test;
select * from test where name = ' ';
select * from test where name = '';
end
(3) Explain why row 1 gets ' ' in the name column but rows 2 and 3 get
'' (empty). In all three cases the INSERT specified a single space.
This is a real-world issue, should I open a case?
Breck
===== StringTest3.java starts here...
System.out.println( "main() started" );
} // main()
statement.execute( "COMMIT");
} // selectTest()
} // class StringTest3
Jason Hinsperger
your problem resolved.
--
Jason Hinsperger
International and Sustaining Engineering
Adaptive Server Anywhere
Heinz Huber
> Why does an INSERT executed via a Java PreparedStatement change a
> single space to the empty string, whereas an INSERT executed via a
> Statement work OK?
AFAIK, Jason's answer was correct though he used the wrong context. The single
space trick works both ways! On one hand, you get a single space for an empty
string; on the other hand, ASA interprets a single space (over a TDS connection)
as an empty string.
Therefore, setting the option might help in this case, too.
Regards,
Heinz