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;}
       }
   }
}