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

Re: FW: Re: How to set the length of column when creating the tables?

Expand Messages
  • Ronald Bourret
    I haven t compiled or tested this, but the following should work. In de.tudarmstadt.ito.xmldbms.Parameters, change the following lines in the
    Message 1 of 4 , Dec 14, 2000
    • 0 Attachment
      I haven't compiled or tested this, but the following should work. In
      de.tudarmstadt.ito.xmldbms.Parameters, change the following lines in the
      setStringParameter method:

      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
      p.setString(number, value);
      break;

      to:

      case Types.CHAR:
      case Types.VARCHAR:
      p.setString(number, value);
      break;

      case Types.LONGVARCHAR:
      ByteArrayInputStream inputStream = new
      ByteArrayInputStream(value.getBytes());
      p.setAsciiStream(2, inputStream, value.length());
      break;

      (For completeness, this should actually be fixed in the setXxxxParameter
      methods, but this should work in with the current code -- if I remember
      correctly setStringParameter is the only method called. I'll implement a
      complete solution in the next version of XML-DBMS.)

      You will also need to add the following to the list of imported classes:

      import java.io.ByteArrayInputStream;

      An even better solution would be to use
      PreparedStatement.setUnicodeStream, but you'll have to check if the
      drivers support it first. I wouldn't be surprised if they didn't.

      By the way, what is the easysoft.sql.jobDriver? I'm having trouble
      imagining what ODBC function it's calling that isn't supported.

      -- Ron

      P.S. If you want to complain to the driver writers, this is a bug in the
      drivers. In particular, the Getting Started manual for JDBC says:

      "Java programmers do not need to distinguish among the three types of
      JDBC strings, CHAR, VARCHAR, and LONGVARCHAR. Each can be expressed as a
      Java String, and it is possible to read and write an SQL statement
      correctly without knowing the exact data type that was expected."

      But that's life with JDBC drivers :)


      x.yang@... wrote:
      >
      > Ron,
      >
      > Thanks for your help.
      >
      > I have done the test on three drivers and the results are:
      >
      > 1>. Insert a row using a String for the memo column.
      > (sun.jdbc.odbc.JdbcOdbcDriver)
      >
      > Result: Error
      >
      > java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]String data,
      > right truncated (null)
      > at sun.jdbc.odbc.JdbcOdbc.createSQLException(Compiled Code)
      > at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246)
      > at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:1211)
      > at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Compiled Code)
      > at
      > sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatem
      > ent.java:150)
      > at dbtest.main(dbtest.java:31)
      >
      > 2>. Insert a row using an InputStream for the memo column.
      > (sun.jdbc.odbc.JdbcOdbcDriver)
      >
      > Result: OK
      >
      > 3>. Insert a row using a String for the memo column.
      > (com.ms.jdbc.odbc.JdbcOdbcDriver)
      >
      > Result: Error
      >
      > java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]String data,
      > right truncated (null)
      > at com/ms/jdbc/odbc/JdbcOdbc.createSQLException (JdbcOdbc.java)
      > at com/ms/jdbc/odbc/JdbcOdbc.standardError (JdbcOdbc.java)
      > at com/ms/jdbc/odbc/JdbcOdbc.SQLExecute (JdbcOdbc.java)
      > at com/ms/jdbc/odbc/JdbcOdbcPreparedStatement.execute
      > (JdbcOdbcPreparedStatement.java)
      > at com/ms/jdbc/odbc/JdbcOdbcPreparedStatement.executeUpdate
      > (JdbcOdbcPreparedStatement.java)
      > at dbtest.main (dbtest.java:38)
      >
      > 4>. Insert a row using an InputStream for the memo column.
      > (com.ms.jdbc.odbc.JdbcOdbcDriver)
      >
      > Result: OK
      >
      > 5>. Insert a row using a String for the memo column.
      > (easysoft.sql.jobDriver)
      >
      > Result: Error
      >
      > java.sql.SQLException: [Microsoft][ODBC Driver Manager] Driver does not
      > support this function
      > [Microsoft][ODBC Driver Manager] Driver does not support this function
      > [Microsoft][ODBC Microsoft Access Driver]Invalid precision value
      >
      > at easysoft/rpc/Client.callRemote
      > at easysoft/rpc/Client.callRemote
      > at easysoft/rpc/Stub.jdbc_setString
      > at easysoft/sql/jobPreparedStatement.setString
      > at dbtest.main (dbtest.java:36)
      >
      > 6>. Insert a row using an InputStream for the memo column.
      > (easysoft.sql.jobDriver)
      >
      > Result: OK
      >
      > Based on above results, all three drivers work ok when using an InputStream
      > for inserting operation, and all of them failed when using a String for
      > inserting operation.
      >
      > Can you tell me which method do you use behind the
      > domToDBMS.storeDocument(doc) operation?
      >
      > best wishes to you for finding out a solution soon,
      >
      > George
      >
      > -----Original Message-----
      > From: Ronald Bourret [mailto:rpbourret@...]
      > Sent: 14 December 2000 19:51
      > To: x.yang@...
      > Subject: Re: FW: Re: How to set the length of column when creating the
      > tables?
      >
      > Sorry to have taken so long to reply. I'm not sure what is happening
      > with the Easysoft bridge, but I'd like to isolate the problem to see if
      > it is in XML-DBMS or a driver -- I believe it is in the driver.
      > Therefore, could you run the following program with both the Sun and
      > Easysoft JDBC-ODBC bridges?
      >
      > You will need to create a table named foo with two columns: a long
      > integer column named keyvalue and a memo column named memovalue. You
      > will also need to point the xmldbms ODBC data source at the database
      > containing this table.
      >
      > This program attempts to insert a 300 character value into the table
      > using a String and an InputStream. When I run it here with Sun's bridge,
      > the String is truncated but the InputStream is not. This is a bug in the
      > bridge that I can probably work around in XML-DBMS. I'd like to know how
      > the Easysoft bridge works. (Note that if you get an error inserting the
      > value as a String, comment this out and try to insert it as an
      > InputStream.)
      >
      > Also, do you have any other JDBC drivers for Access? If so, could you
      > test them? Thanks. I think we're close to fixing the problem.
      >
      > -- Ron
      >
      > PROGRAM:
      >
      > import java.sql.*;
      > import java.io.*;
      >
      > public class dbtest
      > {
      >
      > public static void main (String[] argv)
      > {
      > boolean tableFound = false;
      >
      > try
      > {
      > Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      > Connection conn =
      > DriverManager.getConnection("jdbc:odbc:xmldbms");
      > PreparedStatement stmt = conn.prepareStatement("INSERT INTO foo
      > VALUES(?, ?)");
      >
      > // Create a string in which the first 255 characters are a's
      > and the next
      > // 45 characters are b's.
      >
      > String string300 =
      > "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" +
      >
      > "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" +
      >
      > "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" +
      >
      > "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" +
      >
      > "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" +
      >
      > "aaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";
      >
      > // Insert a row using a String for the memo column.
      >
      > stmt.setInt(1, 1);
      > stmt.setString(2, string300);
      > stmt.executeUpdate();
      >
      > // Insert a row using an InputStream for the memo column.
      >
      > stmt.setInt(1, 2);
      > stmt.setAsciiStream(2, new
      > ByteArrayInputStream(string300.getBytes()), string300.length());
      > stmt.executeUpdate();
      >
      > conn.close();
      > }
      > catch(Exception e)
      > {
      > e.printStackTrace();
      > }
      > }
      > }
      >
      > x.yang@... wrote:
      > >
      > > Ron,
      > >
      > > I have tried the JDBC-ODBC driver from www.easysoft.com. It still doesn't
      > > work, but the error message (see following) is slightly different. If I
      > cut
      > > the string values shorter, there is no problem. That proves the driver
      > works
      > > ok and the problem solely caused by the long string values.
      > >
      > > rgds,
      > >
      > > George
      > >
      > > --------ERROR Message--------------------
      > >
      > > java.sql.SQLException: [Microsoft][ODBC Driver Manager] Driver does not
      > > support this function
      > > [Microsoft][ODBC Driver Manager] Driver does not support this function
      > > [Microsoft][ODBC Driver Manager] Driver does not support this function
      > > [Microsoft][ODBC Microsoft Access Driver]Invalid precision value
      > >
      > > at easysoft/rpc/Client.callRemote
      > > at easysoft/rpc/Client.callRemote
      > > at easysoft/rpc/Stub.jdbc_setString
      > > at easysoft/sql/jobPreparedStatement.setString
      > > at de/tudarmstadt/ito/xmldbms/Parameters.setStringParameter
      > > (Parameters.java:973)
      > > at de/tudarmstadt/ito/xmldbms/Parameters.setParameter
      > > (Parameters.java:158)
      > > at de/tudarmstadt/ito/xmldbms/Parameters.setParameters
      > > (Parameters.java:130)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.insertRow
      > > (DOMToDBMS.java:703)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.createClassRow
      > > (DOMToDBMS.java:360)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.processFKNodes
      > > (DOMToDBMS.java:407)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.createClassRow
      > > (DOMToDBMS.java:361)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.processRootElement
      > > (DOMToDBMS.java:312)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.processRoot
      > > (DOMToDBMS.java:273)
      > > at de/tudarmstadt/ito/xmldbms/DOMToDBMS.storeDocument
      > > (DOMToDBMS.java:241)
      > > at Transfer.toDBMS (Transfer.java:168)
      > > at Transfer.main (Transfer.java:121)
      >
      > --
      > Ronald Bourret
      > Programming, Writing, and Training
      > XML, Databases, and Schemas
      > http://www.rpbourret.com

      --
      Ronald Bourret
      Programming, Writing, and Training
      XML, Databases, and Schemas
      http://www.rpbourret.com
    • alx@whoever.com
      Just thought people would be interested in an update, I tried to implement this to no evail, against MS Access 2000. Continued to run into the same error,
      Message 2 of 4 , Oct 15, 2001
      • 0 Attachment
        Just thought people would be interested in an update, I tried to
        implement this to no evail, against MS Access 2000. Continued to run
        into the same error, then I check the type that was being returned,
        and it turned out to be VARCHAR and never LONGVARCHAR type. Also the
        p.setAsciiStream had 2 hardcoded for the index, and it should have
        been the variable number.


        > case Types.CHAR:
        > case Types.VARCHAR:
        > p.setString(number, value);
        > break;
        >
        > case Types.LONGVARCHAR:
        > ByteArrayInputStream inputStream = new
        > ByteArrayInputStream(value.getBytes());
        > p.setAsciiStream(2, inputStream, value.length());
        > break;

        to

        case Types.CHAR:
        case Types.VARCHAR:
        case Types.LONGVARCHAR:
        ByteArrayInputStream inputStream = new
        ByteArrayInputStream(value.getBytes());
        p.setAsciiStream(number, inputStream, value.length());
        break;


        The tables require MEMO fields instead of TEXT fields, which are the
        default statements generated by the package for creating tables. You
        need to make sure your create Table statements are creating MEMO
        fields instead of the Text fields (255 limitation). A simple
        substitution in the statement before executing it to create the
        tables is sufficient.

        Thought this might be useful to others.

        Alex
      • Ronald Bourret
        Thanks for the update and the fix. For those of you who don t know, this is in reply to: http://groups.yahoo.com/group/xml-dbms/message/518 -- Ron ... --
        Message 3 of 4 , Oct 15, 2001
        • 0 Attachment
          Thanks for the update and the fix. For those of you who don't know, this
          is in reply to:

          http://groups.yahoo.com/group/xml-dbms/message/518

          -- Ron

          alx@... wrote:
          >
          > Just thought people would be interested in an update, I tried to
          > implement this to no evail, against MS Access 2000. Continued to run
          > into the same error, then I check the type that was being returned,
          > and it turned out to be VARCHAR and never LONGVARCHAR type. Also the
          > p.setAsciiStream had 2 hardcoded for the index, and it should have
          > been the variable number.
          >
          >
          > > case Types.CHAR:
          > > case Types.VARCHAR:
          > > p.setString(number, value);
          > > break;
          > >
          > > case Types.LONGVARCHAR:
          > > ByteArrayInputStream inputStream = new
          > > ByteArrayInputStream(value.getBytes());
          > > p.setAsciiStream(2, inputStream, value.length());
          > > break;
          >
          > to
          >
          > case Types.CHAR:
          > case Types.VARCHAR:
          > case Types.LONGVARCHAR:
          > ByteArrayInputStream inputStream = new
          > ByteArrayInputStream(value.getBytes());
          > p.setAsciiStream(number, inputStream, value.length());
          > break;
          >
          > The tables require MEMO fields instead of TEXT fields, which are the
          > default statements generated by the package for creating tables. You
          > need to make sure your create Table statements are creating MEMO
          > fields instead of the Text fields (255 limitation). A simple
          > substitution in the statement before executing it to create the
          > tables is sufficient.
          >
          > Thought this might be useful to others.
          >
          > Alex
          >
          >
          > To post a message, send it to: xml-dbms@yahoogroups.com
          > To unsubscribe, send a blank message to: xml-dbms-unsubscribe@yahoogroups.com
          > Or join the newsgroup at: news://news.barkto.com/homeless.xml.xml-dbms
          >
          > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

          --
          Ronald Bourret
          Programming, Writing, and Training
          XML, Databases, and Schemas
          http://www.rpbourret.com
        Your message has been successfully submitted and would be delivered to recipients shortly.