setString 32 character limit? - String data right truncation

[jameswhiteley]

Hi

I have a Java stored procedure that breaks when I enter more than 32
characters for one of the parameters (The parameter in question is
called "subject").

The stored procedure takes in several parameters. One of those is a
parameter called "subject" which is of type String.

My Java application includes a CallableStatement and it calls the
stored procedure and specifies a type of VARCHAR for this subject
parameter.

Within the stored procedure I have a PreparedStatement and I use the
setString method to populate the sql string in the stored procedure.
This sql string is performing an INSERT into a table called BOOKINGS.

Within the BOOKINGS table there is a field called subject of type
VARCHAR(500)

This is some code from the stored procedure.

PreparedStatement stmt = null;
stmt.setString( 2, subject );

When I enter more than 32 characters for the subject I get the error:
e=COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0109E String
data right truncation. SQLSTATE=22001

Why would I get this error when the BOOKINGS table in the database
specifies that subject is of type VARCHAR and length 500?

I expect this to be some sort of mapping problem in terms of the
VARCHAR declared in my Java application, the String subject passed in
as a parameter to the stored procedure and the VARCHAR(500) type in the
table in the database)

Can anybody help?

Thanks in advance.

James

(Full Stored Procedure code below





/**
* JDBC Stored Procedure ADMINMISTRATOR.SAVE_UPDATE_BOOKING
* @param bookingID
* @param chairID
* @param ownerID
* @param roomID
* @param startDate
* @param endDate
* @param description
* @param subject
* @param emailFacilities
* @param emailCatering
* @param emailSecurity
* @param newID
*/
import java.sql.*; // JDBC classes
//import java.io.*;
import java.text.SimpleDateFormat;

public class SAVE_UPDATE_BOOKING
{
private static SimpleDateFormat dayFormatter = new
SimpleDateFormat("yyyy-MM-dd");
private static SimpleDateFormat timeFormatter = new
SimpleDateFormat("HH.mm");
private static SimpleDateFormat logFormatter = new
SimpleDateFormat("yyyy-MM-dd HH.mm");

public static void sAVE_UPDATE_BOOKING ( String bookingID,
String chairID,
int ownerID,
int roomID,
java.sql.Timestamp
startDate,
java.sql.Timestamp
endDate,
String description,
String subject,
String emailFacilities,
String emailCatering,
String emailSecurity,
int status,
int[] newID,
ResultSet[] rs1 ) throws
SQLException, Exception
{
ResultSet selectResults = null;
ResultSet checkResults = null;
Connection con = null;
PreparedStatement stmt = null;
PreparedStatement stmt2 = null;
PreparedStatement selectStmt = null;
Statement identityStmt = null;
PreparedStatement checkStmt = null;

//PrintWriter out = new PrintWriter( new FileWriter
("/tmp/GSRB_Java_SAVE_OR_UPDATE_BOOKING.log"), true);
//out.println("Started v4.0");

try{
// Get connection to the database
con =
DriverManager.getConnection("jdbc:default:connection");

//convert bookingID to int
Integer bookingIdInt = null;
//out.println("Supplied BOOKING_ID (as string) = "+
bookingID);
if( bookingID != null && !bookingID.trim().equals("")){
try{
bookingIdInt = new Integer(bookingID.trim());
}catch(NumberFormatException e){
bookingIdInt = null;
}
}
//out.println("Supplied BOOKING_ID (as Int) = "+
bookingIdInt);

//convert bookingID to int
Integer chairIDInt = null;

//parse chair id
if( chairID != null && !chairID.trim().equals("")){
chairIDInt = new Integer(chairID.trim());
}




boolean bFlag;
String sql, selectSql, updateSql, insertSql, checkSql_start,
checkSql_end;

selectSql = "SELECT BOOKING_ID, START_TIME, END_TIME FROM
ADMINISTRATOR.BOOKINGS WHERE BOOKING_ID = ?";

insertSql = "INSERT INTO ADMINISTRATOR.BOOKINGS (ROOM_ID,
OWNER_PERSON_ID, CHAIR_PERSON_ID, START_TIME, END_TIME, BOOKING_STATUS,
DESCRIPTION, SUBJECT, FACILITIES__EMAIL, CATERING__EMAIL,
SECURITY__EMAIL, REPEAT)"
+ " "
+ " VALUES (?, ? , ? , ? , ? , ? , ? , ? , ? , ?, ?,
-1)";


updateSql = "UPDATE ADMINISTRATOR.BOOKINGS SET ROOM_ID = ?,
OWNER_PERSON_ID = ?, CHAIR_PERSON_ID = ?, START_TIME = ?, END_TIME = ?,
BOOKING_STATUS = ?, DESCRIPTION = ?, SUBJECT = ?, FACILITIES__EMAIL =
?, CATERING__EMAIL = ?, SECURITY__EMAIL = ?, REPEAT = -1 "
+ " "
+ " WHERE BOOKING_ID = ?";

checkSql_start = "SELECT ADMINISTRATOR.IS_ROOM_AVAILABLE(";
checkSql_end = " ) AS AVAILABILITY FROM ADMINISTRATOR.ROOM_TYPES
FETCH FIRST 1 ROWS ONLY";

boolean idExists = false;
Timestamp currentStartTime = null;
Timestamp currentEndTime = null;

if(bookingID != null){
selectStmt = con.prepareStatement( selectSql );
selectStmt.setObject( 1, bookingIdInt, Types.INTEGER);
bFlag = selectStmt.execute();
selectResults = selectStmt.getResultSet();

idExists = selectResults.next();

if(idExists){
currentStartTime = selectResults.getTimestamp(2);
currentEndTime = selectResults.getTimestamp(3);
}

try{ selectStmt.close(); }catch(Exception e2){
selectStmt = null; }
}

// out.println("Booking exists with this BOOKING_ID = " +
idExists);

//first check wether this room is avaiavble for the requested slot
boolean available = false;
StringBuffer checkBuf = new StringBuffer();
checkBuf.append(" TIMESTAMP('");
checkBuf.append( dayFormatter.format( startDate ));
checkBuf.append("', '");
checkBuf.append( timeFormatter.format( startDate ));
checkBuf.append("'), TIMESTAMP('");
checkBuf.append( dayFormatter.format( endDate ));
checkBuf.append("', '");
checkBuf.append( timeFormatter.format( endDate ));
checkBuf.append("'), ");
checkBuf.append(roomID);

String fullStmt = checkSql_start + checkBuf.toString() +
checkSql_end;
//out.println("Check statement is '" + fullStmt +"'");
checkStmt = con.prepareStatement(fullStmt);

//out.println("Prepared Check statement..");
checkStmt.execute();
checkResults = checkStmt.getResultSet();
checkResults.next();
//out.println("Executed check statement..");

int avaiablityCount = checkResults.getInt(1);
try{ checkStmt.close(); }catch(Exception e3){ checkStmt = null; }

if( avaiablityCount == 1 ){
available = true;
}

//out.println("Avaliability Count = " + avaiablityCount);
//out.println("This slot/room is avaiabale = " + available);


if(idExists){

//booking id exists so this is an update
// out.println("Supplied BookingID
exists...thius is an update");

//we only need to check wether the traget slot
is avaiable if it is nopt the same as the current booking slot
boolean timeChanged = true;
// out.println("Current start time: "+
logFormatter.format(currentStartTime));
//out.println("Requested start time: "+
logFormatter.format(startDate));
//out.println("difference = " +
Math.abs(startDate.getTime() - currentStartTime.getTime()));
if( Math.abs(startDate.getTime() -
currentStartTime.getTime()) < 59999 ) timeChanged = false;

// out.println("Current end time: "+
logFormatter.format(currentEndTime));
// out.println("Requested end time: "+
logFormatter.format(endDate));
// out.println("difference = " +
Math.abs(endDate.getTime() - currentEndTime.getTime()));
if( Math.abs(endDate.getTime() -
currentEndTime.getTime()) < 59999 ) timeChanged = false;


//out.println("Time has changed? " +
timeChanged);

if(!timeChanged || available){

//out.println("Requested slot is either
available or time hasn't changed...continuing with update");

stmt = con.prepareStatement( updateSql );
stmt.setInt( 1, roomID );
stmt.setInt( 2, ownerID );
if(chairIDInt == null){
stmt.setObject( 3, null, Types.INTEGER );
}else{
stmt.setInt( 3, chairIDInt.intValue() );
}

stmt.setTimestamp( 4, startDate );
stmt.setTimestamp( 5, endDate );
stmt.setString( 7, description );
stmt.setInt ( 6, status );
stmt.setString( 8, subject );
stmt.setString( 9, emailFacilities );
stmt.setString( 10, emailCatering );
stmt.setString( 11, emailSecurity );
stmt.setObject( 12, bookingIdInt, Types.INTEGER);

newID[0] = bookingIdInt.intValue();


bFlag = stmt.execute();
rs1[0] = stmt.getResultSet();
con.commit();
//out.println("Executed: '"+ updateSql
+"'");

}else{
// out.println("Requested slot is for
new time and this time is NOT available...aborting update");
//this is an existing booking and the
slot is unavailable, so reject
newID[0] = -1000;
}

}else{

//id doesn't exist, so this is a NEW booking
//out.println("Supplied BookingID does NOT exists...thus is a
create");

//check that the slot is available
if(available) {

//out.println("Requested slot is
available...continuing with create");

stmt = con.prepareStatement( insertSql
);
stmt.setInt( 1, roomID );
stmt.setInt( 2, ownerID );
if(chairIDInt == null){
stmt.setObject( 3, null, Types.INTEGER
);
}else{
stmt.setInt( 3, chairIDInt.intValue() );
}
stmt.setTimestamp( 4, startDate );
stmt.setTimestamp( 5, endDate );
stmt.setInt ( 6, status );
stmt.setString( 7, description );
stmt.setString( 8, subject );
stmt.setString( 9, emailFacilities );
stmt.setString( 10, emailCatering );
stmt.setString( 11, emailSecurity );
bFlag = stmt.execute();
rs1[0] = stmt.getResultSet();

//out.println("Executed: '"+ insertSql +"'");

//now retrieve the Identity column value from the
system table
ResultSet identityRS;
identityStmt = con.createStatement();
int idntVal = -1;
identityRS =
identityStmt.executeQuery("SELECT IDENTITY_VAL_LOCAL() FROM
SYSIBM.SYSDUMMY1");
// Get the result table from the query.
// This is a single row with the most
// recent identity column value.
//out.println("Called IDENTITY
func...");
while (identityRS.next()) {

idntVal = identityRS.getInt(1);
// Retrieve column value

//out.println("getInt(1)="+idntVal);
}

try{ identityStmt.close();
}catch(Exception e3){ identityStmt = null; }
con.commit();
//out.println("getInt(1)="+idntVal);
newID[0] = idntVal;
}else{
//out.println("Requested slot is NOT
available...aborting create");
//this is a new booking and the slot is
unavailable, so reject
newID[0] = -1000;
}
}

con.commit();
//now tidy up the persons table
sql = "DELETE FROM ADMINISTRATOR.PERSONS AS PERSONS "
+
"WHERE PERSONS.PERSON_ID NOT IN (SELECT
ATTENDEES.PERSON_ID FROM ADMINISTRATOR.ATTENDEES AS ATTENDEES) AND " +
"PERSONS.PERSON_ID NOT IN (SELECT
BOOKINGS.CHAIR_PERSON_ID FROM ADMINISTRATOR.BOOKINGS AS BOOKINGS ) AND
" +
"PERSONS.PERSON_ID NOT IN (SELECT
BOOKINGS.OWNER_PERSON_ID FROM ADMINISTRATOR.BOOKINGS AS BOOKINGS ) " ;

stmt2 = con.prepareStatement( sql );
bFlag = stmt2.execute();
con.commit();
//out.println("Executed: '"+ sql +"'");
//out.println("Output param=*" + newID[0] + "*");

}catch(Exception e){
//MAYBE LOG HERE - performance cost?
//out.println("AN EXCEPTION
OCCURED--------------------------------");
//e.printStackTrace(out);

}finally{
con.commit();
try{ stmt.close();}catch(Exception e1){stmt = null;}
try{ stmt2.close();}catch(Exception e2){stmt2 = null;}
try{ selectStmt.close(); }catch(Exception e3){ selectStmt =
null; }
try{ identityStmt.close(); }catch(Exception e4){
identityStmt = null; }
try {checkStmt.close(); }catch(Exception e5){ checkStmt =
null; }
try{ con.close();}catch(Exception e5){con = null;}
//try{out.close();}catch(Exception e6){out = null;}
}
}
}

 

[Mike Beaty]

James

I would first look at the stored procedure declaration and make sure
that it is not causing the 32 character limit. There's nothing in the
setString() method that would cause this...in other words, it's not the
Java it's the DB. You can use setString() on a BLOB field and pass in
as much data as the DB can handle.

-Mike

 

[jameswhiteley]

Mike

Thanks for your quick reply. I looked at the SP declaration as you
suggested.
The SPs were created using the wizard in IBM's development centre.

I right clicked on the SP within Development Cantre
Clicked on properties
Clicked on the parameters tab and found the "subject" parameter.

The Java Type of this parameter was String but the SQL Type was
CHARACTER(32).
Here lies the problem. I guess this is the default mapping of data
types.

There is an option to change this and I should be able to change it to
VARCHAR and specify the length in order for it to match up to the
datatype of subject in my BOOKINGS table.



Thanks for your help.

James

 

[Mike Beaty]

No problem James, good luck with your app!

-Mike

Mike

Thanks for your quick reply. I looked at the SP declaration as you
suggested.
The SPs were created using the wizard in IBM's development centre.

I right clicked on the SP within Development Cantre
Clicked on properties
Clicked on the parameters tab and found the "subject" parameter.

The Java Type of this parameter was String but the SQL Type was
CHARACTER(32).
Here lies the problem. I guess this is the default mapping of data
types.

There is an option to change this and I should be able to change it to
VARCHAR and specify the length in order for it to match up to the
datatype of subject in my BOOKINGS table.



Thanks for your help.

James