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

Table not found error

Expand Messages
  • Scott Bainbridge
    Hi, I am trying to insert the contents of an XML file into an Oracle table using the Transfer program that comes with XMLDBMS V2 and am getting the following
    Message 1 of 12 , May 25, 2005
    • 0 Attachment
      Hi,



      I am trying to insert the contents of an XML file into an Oracle table
      using the Transfer program that comes with XMLDBMS V2 and am getting the
      following error:



      org.xmlmiddleware.utils.XMLMiddlewareException: Table not found:
      "Default"."".""."xform_notes"

      at
      org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.getTable(MapCompile
      r.java:2065)

      at
      org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.processToClassTable
      (MapCompiler.java:1461)

      at
      org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.startElement(MapCom
      piler.java:455)

      at
      org.apache.xerces.parsers.AbstractSAXParser.startElement(Unknown Source)

      at
      org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown
      Source)

      at
      org.apache.xerces.impl.dtd.XMLDTDValidator.emptyElement(Unknown Source)

      at
      org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown
      Source)

      at
      org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDis
      patcher.dispatch(Unknown Source)

      at
      org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unkno
      wn Source)

      at
      org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)

      at
      org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)

      at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)

      at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown
      Source)

      at
      org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.compile(MapCompiler
      .java:225)

      at
      org.xmlmiddleware.xmldbms.tools.transfer.createMap(transfer.java:1513)

      at
      org.xmlmiddleware.xmldbms.tools.transfer.storeDocumentInternal(transfer.
      java:830)

      at
      org.xmlmiddleware.xmldbms.tools.transfer.storeDocument(transfer.java:480
      )



      Ok, the details:



      The code I am using is:



      Try{



      Properties dbprops = new Properties();

      dbprops.setProperty("DBName","prod");


      dbprops.setProperty("DataHandlerClass","org.xmlmiddleware.xmldbms.dataha
      ndlers.GenericHandler");


      dbprops.setProperty("DataSourceClass","org.xmlmiddleware.db.JDBC1DataSou
      rce");


      dbprops.setProperty("Driver","oracle.jdbc.driver.OracleDriver");


      dbprops.setProperty("URL","jdbc:oracle:thin:@137.8.33.25:1521:prod");



      dbprops.setProperty("User","user1");

      dbprops.setProperty("Password","pass1");



      Properties transprops = new Properties();



      transprops.setProperty("Method","StoreDocument");


      transprops.setProperty("XMLFile","C:\\scottsstuff\\xfactor\\xfactor\\src
      \\jsp\\forms\\note1.xml");


      transprops.setProperty("MapFile","C:\\scottsstuff\\xfactor\\xfactor\\src
      \\jsp\\forms\\note.map");


      transprops.setProperty("ActionFile","C:\\scottsstuff\\xfactor\\xfactor\\
      src\\jsp\\forms\\note.act");


      transprops.setProperty("ParserUtilsClass","org.xmlmiddleware.xmlutils.ex
      ternal.ParserUtilsXerces");

      transprops.setProperty("PrimaryKeyKeyGenerator","NID");





      transfer trans = new transfer(transprops);

      trans.setDatabaseProperties(dbprops);


      trans.storeDocument(transprops,"C:\\scottsstuff\\xfactor\\xfactor\\src\\
      jsp\\forms\\note1.xml","C:\\scottsstuff\\xfactor\\xfactor\\src\\jsp\\for
      ms\\note.map","C:\\scottsstuff\\xfactor\\xfactor\\src\\jsp\\forms\\note.
      act");



      }catch(Exception e){

      System.out.println("error");

      e.printStackTrace();

      }



      I am using the Oracle thin jdbc driver (which I know works), the SID is
      'prod' and the table that I want the data to go into is:



      SQL> desc xform_notes;

      Name Null? Type

      ------------------------------- -------- ----

      NOTE_ID NOT NULL VARCHAR2(24)

      NOTE_TO NOT NULL VARCHAR2(24)

      NOTE_FROM NOT NULL VARCHAR2(24)

      NOTE_HEADING VARCHAR2(200)

      NOTE_BODY VARCHAR2(250)



      SQL>



      There is a primary key on the note_id field.



      The XML file is a simple one that looks like:



      <?xml version="1.0" encoding="ISO-8859-1" ?>

      <note xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <id>1</id>

      <to>Scott</to>

      <from>Me</from>

      <heading>Reminder</heading>

      <body>What are you doing</body>

      </note>



      The Map file is:



      <?xml version='1.0' ?>



      <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >



      <XMLToDBMS Version="2.0"
      xmlns="http://www.xmlmiddleware.org/xmldbms/v2">

      <Options>

      <SimpleDateFormat Pattern="dd-MMM-yyyy" DefaultForTypes="DATE" />

      </Options>

      <Databases>

      <Database Name="prod">

      <Catalog>

      <Schema>

      <Table Name="xform_notes">

      <Column Name="note_id" DataType="VARCHAR" Length="24"
      Nullable="No"/>

      <Column Name="note_to" DataType="VARCHAR" Length="24"
      Nullable="No"/>

      <Column Name="note_from" DataType="VARCHAR"
      Length="24" Nullable="No" />

      <Column Name="note_heading" DataType="VARCHAR"
      Length="200" Nullable="Yes"/>

      <Column Name="note_body" DataType="VARCHAR"
      Length="250" Nullable="Yes"/>

      <PrimaryKey KeyGenerator="Database">

      <UseColumn Name="note_id"/>

      </PrimaryKey>

      </Table>

      </Schema>

      </Catalog>

      </Database>

      </Databases>

      <Maps>

      <ClassMap>

      <ElementType Name="note"/>

      <ToClassTable Name="xform_notes"/>

      <PropertyMap>

      <Attribute Name="id"/>

      <ToColumn Name="note_id"/>

      </PropertyMap>

      <PropertyMap>

      <ElementType Name="to"/>

      <ToColumn Name="note_to"/>

      </PropertyMap>

      <PropertyMap>

      <ElementType Name="from"/>

      <ToColumn Name="note_from"/>

      </PropertyMap>

      <PropertyMap>

      <ElementType Name="heading"/>

      <ToColumn Name="note_heading"/>

      </PropertyMap>

      <PropertyMap>

      <ElementType Name="body"/>

      <ToColumn Name="note_body"/>

      </PropertyMap>

      </ClassMap>

      </Maps>

      </XMLToDBMS>



      And the action file is:



      <!DOCTYPE Actions SYSTEM "actions.dtd">

      <Actions Version="2.0"
      xmlns="http://www.xmlmiddleware.org/xmldbms/actions/v2">

      <DefaultAction>

      <Insert />

      </DefaultAction>

      <Action>

      <ElementType Name="note" />

      <UpdateOrInsert />

      </Action>

      </Actions>





      Any idea what I am doing wrong??? The code compiles Ok and the database
      connect stuff seems Ok but not sure why it can't find the table.



      Many thanks!







      Scott Bainbridge

      AIMS





      ------------------------------------------------------------------------
      The information contained in this communication is for the use of the
      individual or entity to whom it is addressed, and may contain
      information which is the subject of legal privilege and/or copyright.
      If you have received this communication in error, please notify the
      sender by return E-Mail and delete the transmission, together with any
      attachments, from your system. Thank you.
      -------------------------------------------------------------------------



      [Non-text portions of this message have been removed]
    • Ronald Bourret
      Hello, Try using the table name XFORM_NOTES instead of xform_notes. XML-DBMS treats all table and column names in the map file as quoted identifiers, so they
      Message 2 of 12 , May 25, 2005
      • 0 Attachment
        Hello,

        Try using the table name XFORM_NOTES instead of xform_notes. XML-DBMS
        treats all table and column names in the map file as quoted identifiers,
        so they have to match the case in which the names are stored in the
        database. I believe that Oracle stores unquoted identifiers in upper
        case. (In other words, your table name is actually XFORM_NOTES and
        XML-DBMS is looking for xform_notes, which it can't find.)

        -- Ron

        Scott Bainbridge wrote:

        > Hi,
        >
        >
        >
        > I am trying to insert the contents of an XML file into an Oracle table
        > using the Transfer program that comes with XMLDBMS V2 and am getting the
        > following error:
        >
        >
        >
        > org.xmlmiddleware.utils.XMLMiddlewareException: Table not found:
        > "Default"."".""."xform_notes"
      • sctt_bainbridge
        Hi, I tried that and I still get the same error... Thanks for your help. Cheers, Scott. ... DBMS ... identifiers, ... the ... upper ... table ... getting the
        Message 3 of 12 , May 25, 2005
        • 0 Attachment
          Hi,

          I tried that and I still get the same error...

          Thanks for your help.


          Cheers,



          Scott.



          --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
          wrote:
          > Hello,
          >
          > Try using the table name XFORM_NOTES instead of xform_notes. XML-
          DBMS
          > treats all table and column names in the map file as quoted
          identifiers,
          > so they have to match the case in which the names are stored in
          the
          > database. I believe that Oracle stores unquoted identifiers in
          upper
          > case. (In other words, your table name is actually XFORM_NOTES and
          > XML-DBMS is looking for xform_notes, which it can't find.)
          >
          > -- Ron
          >
          > Scott Bainbridge wrote:
          >
          > > Hi,
          > >
          > >
          > >
          > > I am trying to insert the contents of an XML file into an Oracle
          table
          > > using the Transfer program that comes with XMLDBMS V2 and am
          getting the
          > > following error:
          > >
          > >
          > >
          > > org.xmlmiddleware.utils.XMLMiddlewareException: Table not found:
          > > "Default"."".""."xform_notes"
        • Ronald Bourret
          Hello, I believe I ve found your problem. In your map document, you declare the xform_notes table as being in the prod database: But
          Message 4 of 12 , May 25, 2005
          • 0 Attachment
            Hello,

            I believe I've found your problem. In your map document, you declare the
            xform_notes table as being in the prod database:

            <Database Name="prod">

            But when you map the note element to this table, you refer to the
            xform_notes table in the Default database:

            <!-- Default value for the Database attribute is Default -->
            <ToClassTable Name="xform_notes"/>

            The simplest solution is to:

            a) Remove the Name attribute from the Database element.
            b) Do not set the DBName property in your code.

            As a general rule, you never need to use database names in XML-DBMS.
            These are only used when you are working with multiple databases and
            need to identify each of them separately, such as when constructing an
            XML document from the data in multiple databases.

            -- Ron

            sctt_bainbridge wrote:

            > Hi,
            >
            > I tried that and I still get the same error...
          • sctt_bainbridge
            Thanks, that seems to have solved it! I am now talking to the database - getting a null error so I have still some more work to do but the error is an SQL one
            Message 5 of 12 , May 25, 2005
            • 0 Attachment
              Thanks, that seems to have solved it! I am now talking to the
              database - getting a null error so I have still some more work to do
              but the error is an SQL one so I am talking to the database now
              which is great.

              Thanks for your help!


              Cheers,


              Scott.


              --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
              wrote:
              > Hello,
              >
              > I believe I've found your problem. In your map document, you
              declare the
              > xform_notes table as being in the prod database:
              >
              > <Database Name="prod">
              >
              > But when you map the note element to this table, you refer to the
              > xform_notes table in the Default database:
              >
              > <!-- Default value for the Database attribute is Default -->
              > <ToClassTable Name="xform_notes"/>
              >
              > The simplest solution is to:
              >
              > a) Remove the Name attribute from the Database element.
              > b) Do not set the DBName property in your code.
              >
              > As a general rule, you never need to use database names in XML-
              DBMS.
              > These are only used when you are working with multiple databases
              and
              > need to identify each of them separately, such as when
              constructing an
              > XML document from the data in multiple databases.
              >
              > -- Ron
              >
              > sctt_bainbridge wrote:
              >
              > > Hi,
              > >
              > > I tried that and I still get the same error...
            • sctt_bainbridge
              Hi, I have it working, the reasons I was getting a NULL error was that my XML file looked like:
              Message 6 of 12 , May 25, 2005
              • 0 Attachment
                Hi,

                I have it working, the reasons I was getting a NULL error was that
                my XML file looked like:


                <?xml version="1.0" encoding="ISO-8859-1" ?>
                <note>
                <id>1234</id>
                <to>Scott</to>
                <from>Me</from>
                <heading>Reminder</heading>
                <body>What are you doing</body>
                </note>

                when I changed this to:

                <?xml version="1.0" encoding="ISO-8859-1" ?>
                <note id="1234">
                <to>Scott</to>
                <from>Me</from>
                <heading>Reminder</heading>
                <body>What are you doing</body>
                </note>

                it works, that is the ID (which is the primary key) has been moved
                into the Note tag.

                The XML I am storing comes from an XForm (via the Chiba system), is
                there anyway of geting it to look in the body of the XML file for
                the ID value or does it have to be up in the root tag??

                Not a big deal as I can fix this with a bit of programming but
                wondered if I had missed anything.

                Anyway - fatastic to be up and running and I look forward to using
                the package more - well done!

                Scott.

                --- In xml-dbms@yahoogroups.com, "sctt_bainbridge"
                <s.bainbridge@a...> wrote:
                > Thanks, that seems to have solved it! I am now talking to the
                > database - getting a null error so I have still some more work to
                do
                > but the error is an SQL one so I am talking to the database now
                > which is great.
                >
                > Thanks for your help!
                >
                >
                > Cheers,
                >
                >
                > Scott.
                >
                >
                > --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
                > wrote:
                > > Hello,
                > >
                > > I believe I've found your problem. In your map document, you
                > declare the
                > > xform_notes table as being in the prod database:
                > >
                > > <Database Name="prod">
                > >
                > > But when you map the note element to this table, you refer to
                the
                > > xform_notes table in the Default database:
                > >
                > > <!-- Default value for the Database attribute is Default -->
                > > <ToClassTable Name="xform_notes"/>
                > >
                > > The simplest solution is to:
                > >
                > > a) Remove the Name attribute from the Database element.
                > > b) Do not set the DBName property in your code.
                > >
                > > As a general rule, you never need to use database names in XML-
                > DBMS.
                > > These are only used when you are working with multiple databases
                > and
                > > need to identify each of them separately, such as when
                > constructing an
                > > XML document from the data in multiple databases.
                > >
                > > -- Ron
                > >
                > > sctt_bainbridge wrote:
                > >
                > > > Hi,
                > > >
                > > > I tried that and I still get the same error...
              • Ronald Bourret
                1) In case you missed it, the reason it wasn t working before was that you had mapped id as an attribute, but it was an element in your document. So if you
                Message 7 of 12 , May 25, 2005
                • 0 Attachment
                  1) In case you missed it, the reason it wasn't working before was that
                  you had mapped id as an attribute, but it was an element in your
                  document. So if you really want an element, use the ElementType element
                  in the PropertyMap for id instead of the Attribute element.

                  2) XML-DBMS has limited abilities to retrieve a value from deep inside
                  an XML document. For details, see the description of the InlineMap
                  element in xmldbms2.dtd. See also:

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

                  Depending on the structure of your XML document, this may or may not
                  work for you.

                  -- Ron

                  sctt_bainbridge wrote:

                  > Hi,
                  >
                  > I have it working, the reasons I was getting a NULL error was that
                  > my XML file looked like:
                  >
                  >
                  > <?xml version="1.0" encoding="ISO-8859-1" ?>
                  > <note>
                  > <id>1234</id>
                  > <to>Scott</to>
                  > <from>Me</from>
                  > <heading>Reminder</heading>
                  > <body>What are you doing</body>
                  > </note>
                  >
                  > when I changed this to:
                  >
                  > <?xml version="1.0" encoding="ISO-8859-1" ?>
                  > <note id="1234">
                  > <to>Scott</to>
                  > <from>Me</from>
                  > <heading>Reminder</heading>
                  > <body>What are you doing</body>
                  > </note>
                  >
                  > it works, that is the ID (which is the primary key) has been moved
                  > into the Note tag.
                  >
                  > The XML I am storing comes from an XForm (via the Chiba system), is
                  > there anyway of geting it to look in the body of the XML file for
                  > the ID value or does it have to be up in the root tag??
                  >
                  > Not a big deal as I can fix this with a bit of programming but
                  > wondered if I had missed anything.
                  >
                  > Anyway - fatastic to be up and running and I look forward to using
                  > the package more - well done!
                  >
                  > Scott.
                • sctt_bainbridge
                  Ahhh - silly mistake on my part, changed the id to an element and it worked. Again thanks. Next step is to read the data back from the database - any tips??!!
                  Message 8 of 12 , May 26, 2005
                  • 0 Attachment
                    Ahhh - silly mistake on my part, changed the id to an element and it
                    worked. Again thanks.

                    Next step is to read the data back from the database - any tips??!!


                    Thanks,



                    Scott.


                    --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
                    wrote:
                    > 1) In case you missed it, the reason it wasn't working before was
                    that
                    > you had mapped id as an attribute, but it was an element in your
                    > document. So if you really want an element, use the ElementType
                    element
                    > in the PropertyMap for id instead of the Attribute element.
                    >
                    > 2) XML-DBMS has limited abilities to retrieve a value from deep
                    inside
                    > an XML document. For details, see the description of the InlineMap
                    > element in xmldbms2.dtd. See also:
                    >
                    > http://groups.yahoo.com/group/xml-dbms/message/3345
                    >
                    > Depending on the structure of your XML document, this may or may
                    not
                    > work for you.
                    >
                    > -- Ron
                    >
                    > sctt_bainbridge wrote:
                    >
                    > > Hi,
                    > >
                    > > I have it working, the reasons I was getting a NULL error was
                    that
                    > > my XML file looked like:
                    > >
                    > >
                    > > <?xml version="1.0" encoding="ISO-8859-1" ?>
                    > > <note>
                    > > <id>1234</id>
                    > > <to>Scott</to>
                    > > <from>Me</from>
                    > > <heading>Reminder</heading>
                    > > <body>What are you doing</body>
                    > > </note>
                    > >
                    > > when I changed this to:
                    > >
                    > > <?xml version="1.0" encoding="ISO-8859-1" ?>
                    > > <note id="1234">
                    > > <to>Scott</to>
                    > > <from>Me</from>
                    > > <heading>Reminder</heading>
                    > > <body>What are you doing</body>
                    > > </note>
                    > >
                    > > it works, that is the ID (which is the primary key) has been
                    moved
                    > > into the Note tag.
                    > >
                    > > The XML I am storing comes from an XForm (via the Chiba system),
                    is
                    > > there anyway of geting it to look in the body of the XML file
                    for
                    > > the ID value or does it have to be up in the root tag??
                    > >
                    > > Not a big deal as I can fix this with a bit of programming but
                    > > wondered if I had missed anything.
                    > >
                    > > Anyway - fatastic to be up and running and I look forward to
                    using
                    > > the package more - well done!
                    > >
                    > > Scott.
                  • sctt_bainbridge
                    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
                    Message 9 of 12 , May 26, 2005
                    • 0 Attachment
                      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.


                      Regards,



                      SCott.
                    • 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 10 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 11 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 12 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.