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

Re: [xml-dbms] Re: Table not found error

Expand Messages
  • Ronald Bourret
    Hello, I see two potential problems: 1) The data type of the NOTE_ID column isn t really VARCHAR, as you ve declared in the map document. (Your SQL statement
    Message 1 of 12 , May 31, 2005
    • 0 Attachment
      Hello,

      I see two potential problems:

      1) The data type of the NOTE_ID column isn't really VARCHAR, as you've
      declared in the map document. (Your SQL statement includes the WHERE
      clause "NOTE_ID > 0", which makes me wonder if NOTE_ID actually a
      numeric type.) This would cause problems, as the VARCHAR declaration in
      the map document causes XML-DBMS to attempt to cast the retrieved value
      as a String. Not sure why this wouldn't cause similar problems in the
      filter case, though.

      2) You use a SELECT * statement as your SQL statement. If the table
      includes non-VARCHAR columns not declared in your map document, this
      would likely cause problems due to a known bug. For more information, see:

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

      Hope this helps,

      -- Ron

      sctt_bainbridge wrote:

      > Hi,
      >
      > I have another problem...
      >
      > I am having trouble with the RetreiveDocumentbySQL method in the
      > Transfer program.
      >
      > If I use the following I get mutiple records back as per the filter:
      >
      > Properties retreiveProps = new Properties();
      > retreiveProps.setProperty("MapFile",filePath
      > + "note.map");
      > retreiveProps.setProperty("FilterFile",filePath
      > + "note1.ftr");
      > retreiveProps.setProperty
      > ("Method","RetrieveDocumentByFilter");
      > retreiveProps.setProperty
      > ("ParserUtilsClass","org.xmlmiddleware.xmlutils.external.ParserUtilsX
      > erces");
      >
      > Properties selectProp = new Properties();
      > selectProp.setProperty("Select","select * from
      > xform_notes where note_id=2");
      >
      >
      > transfer trans = new transfer(retreiveProps);
      > trans.setDatabaseProperties(dbprops);
      >
      > String xmlstuff = trans.retrieveDocument
      > (retreiveProps,filePath + "note.map", filePath + "note1.ftr",null);
      >
      >
      >
      > The filter file is:
      >
      > <?xml version='1.0' ?>
      > <!DOCTYPE FilterSet SYSTEM "filters.dtd" >
      >
      > <FilterSet Version="2.0"
      > xmlns="http://www.xmlmiddleware.org/xmldbms/filters/v2">
      > <Options>
      > <Wrapper Name="XFORM_NOTES"/>
      > </Options>
      > <Filters>
      > <Filter>
      > <RootFilter>
      > <Table Name="XFORM_NOTES"/>
      > <Where Condition="NOTE_ID > 0"/>
      > </RootFilter>
      > </Filter>
      > </Filters>
      > </FilterSet>
      >
      > This works fine.
      >
      > But if I use the following:
      >
      >
      > Properties retreiveProps = new Properties();
      > retreiveProps.setProperty("MapFile",filePath
      > + "note.map");
      > retreiveProps.setProperty("FilterFile",filePath
      > + "note1.ftr");
      > retreiveProps.setProperty
      > ("Method","RetrieveDocumentBySQL");
      > retreiveProps.setProperty
      > ("ParserUtilsClass","org.xmlmiddleware.xmlutils.external.ParserUtilsX
      > erces");
      >
      > Properties selectProp = new Properties();
      > selectProp.setProperty("Select","SELECT * FROM
      > XFORM_NOTES WHERE NOTE_ID > 0");
      >
      >
      > transfer trans = new transfer(retreiveProps);
      > trans.setDatabaseProperties(dbprops);
      >
      > String xmlstuff = trans.retrieveDocument
      > (retreiveProps,filePath + "note.map", selectProp, filePath
      > + "note1.ftr",null);
      >
      >
      > with the filter file:
      >
      > <!DOCTYPE FilterSet SYSTEM "filters.dtd" >
      > <FilterSet Version="2.0"
      > xmlns="http://www.xmlmiddleware.org/xmldbms/filters/v2">
      > <Options>
      > <Wrapper Name="XFORM_NOTES" />
      > </Options>
      > <Filters>
      > <Filter>
      > <ResultSetInfo Table="XFORM_NOTES" />
      > </Filter>
      > </Filters>
      > </FilterSet>
      >
      > I get the error:
      >
      > ava.sql.SQLException: [XML-DBMS] Conversion error: Object must be a
      > String.
      > at org.xmlmiddleware.xmldbms.DBMSToDOM.getStringValue
      > (DBMSToDOM.java:1057)
      > at org.xmlmiddleware.xmldbms.DBMSToDOM.processColumn
      > (DBMSToDOM.java:455)
      > at org.xmlmiddleware.xmldbms.DBMSToDOM.processColumns
      > (DBMSToDOM.java:435)
      > at org.xmlmiddleware.xmldbms.DBMSToDOM.processClassResultSet
      > (DBMSToDOM.java:402)
      > at org.xmlmiddleware.xmldbms.DBMSToDOM.retrieveResultSetData
      > (DBMSToDOM.java:343)
      > at org.xmlmiddleware.xmldbms.DBMSToDOM.retrieveDocument
      > (DBMSToDOM.java:280)
      > at
      > org.xmlmiddleware.xmldbms.tools.transfer.retrieveDocumentInternal
      > (transfer.java:892)
      > at org.xmlmiddleware.xmldbms.tools.transfer.retrieveDocument
      > (transfer.java:602)
      > at xfactor.db.tester2.main(tester2.java:55)
      > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
      > Method)
      > at sun.reflect.NativeMethodAccessorImpl.invoke
      > (NativeMethodAccessorImpl.java:39)
      > at sun.reflect.DelegatingMethodAccessorImpl.invoke
      > (DelegatingMethodAccessorImpl.java:25)
      > at java.lang.reflect.Method.invoke(Method.java:324)
      > at com.intellij.rt.execution.application.AppMain.main
      > (AppMain.java:78)
      >
      > Now I know that the Sql command is being read as if I miss-type
      > something then it complains, the map file must be Ok as it is the
      > same one being used for single records so it must be in the filter
      > file or...
      >
      > Any ideas would be appreciated.
    • sctt_bainbridge
      Hi, I have checked the map and filter files and made sure everything matches. I have just re-done the table as all varchar s and it works but it seems to be
      Message 2 of 12 , Jun 1, 2005
      • 0 Attachment
        Hi,

        I have checked the map and filter files and made sure everything
        matches. I have just re-done the table as all varchar's and it works
        but it seems to be mapping the wrong fields to the wrong elements.

        Using the SAME map file and the RetreiveDocumentbyFilter method all
        is fine, if I then try RetreiveDocumentbySQL the elements are miss-
        matched. The error was becuase it was trying to map an integer field
        to a string element. I tried specifying the order with:

        Select field1,field2... but this makes no difference. I am totally
        sure I am using the same map file for both the filter retreive and
        the sql retreive.

        At this moment I can use the RetreiveDocumentbyFilter so I can do
        what I want to do so not a show stopper but it would be nice to have
        teh SQL functionality. Should I do the chnages to the Transfer calss
        as per the messages to fix this. All of the fields in the table are
        mapped in the map file and it works with the Filter just not with
        the Sql.

        It's a great product and as I said I am up and running but wanted to
        see if the Sql stuff would work for me.

        Thanks,



        Scott.

        --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
        wrote:
        > Hello,
        >
        > I see two potential problems:
        >
        > 1) The data type of the NOTE_ID column isn't really VARCHAR, as
        you've
        > declared in the map document. (Your SQL statement includes the
        WHERE
        > clause "NOTE_ID > 0", which makes me wonder if NOTE_ID actually a
        > numeric type.) This would cause problems, as the VARCHAR
        declaration in
        > the map document causes XML-DBMS to attempt to cast the retrieved
        value
        > as a String. Not sure why this wouldn't cause similar problems in
        the
        > filter case, though.
        >
        > 2) You use a SELECT * statement as your SQL statement. If the
        table
        > includes non-VARCHAR columns not declared in your map document,
        this
        > would likely cause problems due to a known bug. For more
        information, see:
        >
        > http://groups.yahoo.com/group/xml-dbms/message/3632
        > http://groups.yahoo.com/group/xml-dbms/message/3140
        >
        > Hope this helps,
        >
        > -- Ron
        >
        > sctt_bainbridge wrote:
        >
        > > Hi,
        > >
        > > I have another problem...
        > >
        > > I am having trouble with the RetreiveDocumentbySQL method in the
        > > Transfer program.
        > >
        > > If I use the following I get mutiple records back as per the
        filter:
        > >
        > > Properties retreiveProps = new Properties();
        > > retreiveProps.setProperty("MapFile",filePath
        > > + "note.map");
        > > retreiveProps.setProperty("FilterFile",filePath
        > > + "note1.ftr");
        > > retreiveProps.setProperty
        > > ("Method","RetrieveDocumentByFilter");
        > > retreiveProps.setProperty
        > >
        ("ParserUtilsClass","org.xmlmiddleware.xmlutils.external.ParserUtilsX
        > > erces");
        > >
        > > Properties selectProp = new Properties();
        > > selectProp.setProperty("Select","select * from
        > > xform_notes where note_id=2");
        > >
        > >
        > > transfer trans = new transfer(retreiveProps);
        > > trans.setDatabaseProperties(dbprops);
        > >
        > > String xmlstuff = trans.retrieveDocument
        > > (retreiveProps,filePath + "note.map", filePath
        + "note1.ftr",null);
        > >
        > >
        > >
        > > The filter file is:
        > >
        > > <?xml version='1.0' ?>
        > > <!DOCTYPE FilterSet SYSTEM "filters.dtd" >
        > >
        > > <FilterSet Version="2.0"
        > > xmlns="http://www.xmlmiddleware.org/xmldbms/filters/v2">
        > > <Options>
        > > <Wrapper Name="XFORM_NOTES"/>
        > > </Options>
        > > <Filters>
        > > <Filter>
        > > <RootFilter>
        > > <Table Name="XFORM_NOTES"/>
        > > <Where Condition="NOTE_ID > 0"/>
        > > </RootFilter>
        > > </Filter>
        > > </Filters>
        > > </FilterSet>
        > >
        > > This works fine.
        > >
        > > But if I use the following:
        > >
        > >
        > > Properties retreiveProps = new Properties();
        > > retreiveProps.setProperty("MapFile",filePath
        > > + "note.map");
        > > retreiveProps.setProperty("FilterFile",filePath
        > > + "note1.ftr");
        > > retreiveProps.setProperty
        > > ("Method","RetrieveDocumentBySQL");
        > > retreiveProps.setProperty
        > >
        ("ParserUtilsClass","org.xmlmiddleware.xmlutils.external.ParserUtilsX
        > > erces");
        > >
        > > Properties selectProp = new Properties();
        > > selectProp.setProperty("Select","SELECT * FROM
        > > XFORM_NOTES WHERE NOTE_ID > 0");
        > >
        > >
        > > transfer trans = new transfer(retreiveProps);
        > > trans.setDatabaseProperties(dbprops);
        > >
        > > String xmlstuff = trans.retrieveDocument
        > > (retreiveProps,filePath + "note.map", selectProp, filePath
        > > + "note1.ftr",null);
        > >
        > >
        > > with the filter file:
        > >
        > > <!DOCTYPE FilterSet SYSTEM "filters.dtd" >
        > > <FilterSet Version="2.0"
        > > xmlns="http://www.xmlmiddleware.org/xmldbms/filters/v2">
        > > <Options>
        > > <Wrapper Name="XFORM_NOTES" />
        > > </Options>
        > > <Filters>
        > > <Filter>
        > > <ResultSetInfo Table="XFORM_NOTES" />
        > > </Filter>
        > > </Filters>
        > > </FilterSet>
        > >
        > > I get the error:
        > >
        > > ava.sql.SQLException: [XML-DBMS] Conversion error: Object must
        be a
        > > String.
        > > at org.xmlmiddleware.xmldbms.DBMSToDOM.getStringValue
        > > (DBMSToDOM.java:1057)
        > > at org.xmlmiddleware.xmldbms.DBMSToDOM.processColumn
        > > (DBMSToDOM.java:455)
        > > at org.xmlmiddleware.xmldbms.DBMSToDOM.processColumns
        > > (DBMSToDOM.java:435)
        > > at org.xmlmiddleware.xmldbms.DBMSToDOM.processClassResultSet
        > > (DBMSToDOM.java:402)
        > > at org.xmlmiddleware.xmldbms.DBMSToDOM.retrieveResultSetData
        > > (DBMSToDOM.java:343)
        > > at org.xmlmiddleware.xmldbms.DBMSToDOM.retrieveDocument
        > > (DBMSToDOM.java:280)
        > > at
        > > org.xmlmiddleware.xmldbms.tools.transfer.retrieveDocumentInternal
        > > (transfer.java:892)
        > > at org.xmlmiddleware.xmldbms.tools.transfer.retrieveDocument
        > > (transfer.java:602)
        > > at xfactor.db.tester2.main(tester2.java:55)
        > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
        > > Method)
        > > at sun.reflect.NativeMethodAccessorImpl.invoke
        > > (NativeMethodAccessorImpl.java:39)
        > > at sun.reflect.DelegatingMethodAccessorImpl.invoke
        > > (DelegatingMethodAccessorImpl.java:25)
        > > at java.lang.reflect.Method.invoke(Method.java:324)
        > > at com.intellij.rt.execution.application.AppMain.main
        > > (AppMain.java:78)
        > >
        > > Now I know that the Sql command is being read as if I miss-type
        > > something then it complains, the map file must be Ok as it is
        the
        > > same one being used for single records so it must be in the
        filter
        > > file or...
        > >
        > > Any ideas would be appreciated.
      • Ronald Bourret
        Mismatched elements is due to this bug: http://groups.yahoo.com/group/xml-dbms/message/3632 Briefly, XML-DBMS accesses internal column information by column
        Message 3 of 12 , Jun 2, 2005
        • 0 Attachment
          Mismatched elements is due to this bug:

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

          Briefly, XML-DBMS accesses internal column information by column number.
          When it constructs the select statements, it knows the column numbers
          (so RetrieveDocumentByFilter works). When you construct the select
          statement, it needs to retrieve the column numbers from the result set
          metadata; the alpha 3 version of Transfer didn't do this and the message
          shows how to fix the code.

          -- Ron

          sctt_bainbridge wrote:
          > Hi,
          >
          > I have checked the map and filter files and made sure everything
          > matches. I have just re-done the table as all varchar's and it works
          > but it seems to be mapping the wrong fields to the wrong elements.
          >
          > Using the SAME map file and the RetreiveDocumentbyFilter method all
          > is fine, if I then try RetreiveDocumentbySQL the elements are miss-
          > matched. The error was becuase it was trying to map an integer field
          > to a string element. I tried specifying the order with:
          >
          > Select field1,field2... but this makes no difference. I am totally
          > sure I am using the same map file for both the filter retreive and
          > the sql retreive.
          >
          > At this moment I can use the RetreiveDocumentbyFilter so I can do
          > what I want to do so not a show stopper but it would be nice to have
          > teh SQL functionality. Should I do the chnages to the Transfer calss
          > as per the messages to fix this. All of the fields in the table are
          > mapped in the map file and it works with the Filter just not with
          > the Sql.
          >
          > It's a great product and as I said I am up and running but wanted to
          > see if the Sql stuff would work for me.
          >
          > Thanks,
          >
          >
          >
          > Scott.
        Your message has been successfully submitted and would be delivered to recipients shortly.