cryptic error message: Length of Bind host variable exceeds MaxLength

Question

Hi,

I've encountered a cryptic error message thrown by Sybase IQ server.

com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -1001019: Function not supported on varchars longer than 255 Length of Bind host variable exceeds MaxLength , -- (df_Heap.cxx 2145) at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636) at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996) at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187) at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1642) at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1625) at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:91) at ibs.dao.CM3RM1DAO.updateToTable(CM3RM1DAO.java:197) at ibs.dao.CM3RM1DAO.isXMLProcessed(CM3RM1DAO.java:88) at ibs.xml.parser.XMLParser.parsingXMLIntoBO(XMLParser.java:2125) at ibs.common.util.MainClass.main(MainClass.java:74)

We have several columns (DESCRIPTION etc.) which are of type varchar(4000). However I can update them directly without having any error. And, I don't see any code specifying any bind variables, so I have no idea where the message comes from.

This is the code (I've modified it a bit):

    String sql = "UPDATE TABLEX SET " + 
    "COMPANY = ?, CUSTOMER_REFERENCE= ?, " +
    "STATUS = ?, CONTACT_FIRST_NAME = ?, CONTACT_LAST_NAME = ?, " +
    "SEVERITY = ?, PRIORITY_CODE = ?, REQUESTEDDATE = ?, " +
    "CLOSE_TIME = ?, LEAD_TIME = ?, CHANGE_REASON = ?, MODTIME = ? WHERE NUMBER = ?";

    stmt = conn.prepareStatement(sql);

    for loop here
    {
        stmt.setString(...);
        .
        .
        stmt.executeUpdate();
    }

Any help is appreciated

Answer 1

The question marks are bind variables. One of your stmt.set...(...) statements is trying to set a value longer than the database column will allow. Try displaying the length of each Java String that you are setting.

Answer 2

Finally I was able to reproduce the error with the sample data sent by customer.

Data type of one of the fields was VarChar(4000). However the user was trying to update this field with data which has 32k characters!

Once I truncate the data, the data was updated successfully.

I thought Sybase automatically truncate the data, but apparently it doesn't.