Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] Re: Re: Data truncation on insert not an error?

Expand Messages
  • Ronald Bourret
    ... I played around with the code below [1] and discovered the following. When you pass a 256 character string to EasySoft with PreparedStatement.setString,
    Message 1 of 1 , Jun 23, 2000
    • 0 Attachment
      >Unfortunately I still get the error, and I'm now
      >absolutely sure it's due to a string length problem somewhere. I get the
      >error on insert with the ParaPCDATA in document_in.xml which starts
      >"XML-DBMS is a set of ...". If I chop this line so the PCDATA text is 255
      >characters long it inserts ok, if I then add one more character I get the
      >error so I'm pretty definite about this. Also I turned on ODBC tracing and
      >the prepared statement that fails is one to insert values into ParaPCDATA.
      >The problem doesn't seem to be in the database (MS Access) or the Easysoft
      >JDBC-ODBC bridge as I can insert strings of more than 255 characters in
      >length using the Easysoft DB browser sample applet. The only thing I can
      >think of at the moment is the JDK runtime stuff that gets used when I'm
      >running a browser applet is different to the one I'd use at the command
      >line. Apart from that I'm a bit stumped as to what to try next. What JDK
      >version and platform do you use?

      I played around with the code below [1] and discovered the following. When
      you pass a 256 character string to EasySoft with
      PreparedStatement.setString, you get an error (SQLSTATE 37000, SQL syntax
      error). When you pass the string with PreparedStatement.setAsciiString, it
      works. This is probably what the EasySoft applet is doing.

      This leads me to believe that there is a limitation either in the ODBC
      driver or in Access about setting more than 255 characters through a call to
      the ODBC functions SQLBindParamter/SQLExecute. That is, if you want to pass
      more than 255 characters, the ODBC application needs to call SQLPutData
      (which is what I assume setAsciiString does).

      The easiest way to test this limitation is to translate the code below to
      ODBC and try passing a buffer larger than 255 bytes to SQLBindParameter.
      (Note that the error won't occur until SQLExecute is called.)

      In theory, XML-DBMS is not at fault here, since the JDBC spec allows you to
      pass any size string to setString. In practice, many JDBC drivers are likely
      to implement this badly, so I should probably modify the code to call
      setUnicodeString when the length of a string exceeds the legal length for a
      VARCHAR. (Or just hard-code this to 255, which should work in virtually all
      cases.)

      EasySoft should probably do the same, although in their case, they would
      call the ODBC function SQLPutData instead.

      Note that you can work around this bug for the time being with the following
      (uncompiled, untested) code. (I am not integrating this into XML-DBMS yet
      due to time constraints on the 1.01 release. Furthermore, EasySoft does not
      yet support setUnicodeStream and I need this to be sure that XML-DBMS code
      is international.)

      Parameters.java:

      all setXxxxxParameter methods -- especially setStringParameter:

      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
      str = value.toString(); // Declare at top of class
      len = str.length(); // Declare at top of class
      if (len > 255)
      p.setAsciiStream(number, new
      ByteArrayInputStream(str.getBytes()), len);
      else
      p.setString(number, value.toString());
      break;


      [1] Code for inserting a 256 character string:

      import java.io.ByteArrayInputStream;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.Types;

      public class stringbug
      {
      public static void main (String[] argv) throws Exception
      {
      Connection conn = null;
      PreparedStatement p = null;
      // String url = "jdbc:odbc:ts";
      String url = "jdbc:easysoft://localhost:8831/ts";
      String str =
      "1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901
      23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
      4567890123456789012345678901234567890123456789012345678901234567890123456";

      try
      {
      // Connect to the database and get the result set
      // Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Class.forName("easysoft.sql.jobDriver");
      conn = DriverManager.getConnection(url);
      p = conn.prepareStatement("INSERT INTO foo (bar) VALUES (?)");
      p.setString(1, str);
      // p.setAsciiStream(1, new ByteArrayInputStream(str.getBytes()),
      256);
      p.executeUpdate();

      }
      finally
      {
      if (p != null) p.close();
      if (conn != null) conn.close();
      }
      }
      }
      ________________________________________________________________________
      Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
    Your message has been successfully submitted and would be delivered to recipients shortly.