setString 32 character limit? - String data right truncation
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;}
}
}
}