setString 32 character limit? - String data right truncation

Discussion in 'Java' started by jameswhiteley@hbosplc.com, Oct 11, 2006.

  1. Guest

    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;}
    }
    }
    }
    , Oct 11, 2006
    #1
    1. Advertising

  2. Mike Beaty Guest

    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
    Mike Beaty, Oct 11, 2006
    #2
    1. Advertising

  3. Guest

    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 wrote:

    > 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
    , Oct 11, 2006
    #3
  4. Mike Beaty Guest

    No problem James, good luck with your app!

    -Mike
    wrote:
    > 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 wrote:
    >
    > > 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
    Mike Beaty, Oct 11, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Edward A Thompson

    setString for char field

    Edward A Thompson, Jan 9, 2004, in forum: Java
    Replies:
    2
    Views:
    339
    David Zimmerman
    Jan 10, 2004
  2. Marcelo

    MySql Data Truncation

    Marcelo, Dec 19, 2005, in forum: Java
    Replies:
    3
    Views:
    17,132
    Roedy Green
    Dec 21, 2005
  3. Brand Bogard

    8 bit character string to 16 bit character string

    Brand Bogard, May 25, 2006, in forum: C Programming
    Replies:
    8
    Views:
    717
    those who know me have no need of my name
    May 28, 2006
  4. Replies:
    5
    Views:
    438
    Roedy Green
    Nov 13, 2007
  5. david

    no warning for data truncation?

    david, Oct 23, 2008, in forum: C Programming
    Replies:
    1
    Views:
    322
    david
    Oct 24, 2008
Loading...

Share This Page