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

Re: [xml-dbms] Table not found error

Expand Messages
  • 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 1 of 12 , May 25 10:02 AM
    • 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 2 of 12 , May 25 2:07 PM
      • 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 3 of 12 , May 25 2:21 PM
        • 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 4 of 12 , May 25 3:15 PM
          • 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 5 of 12 , May 25 6:29 PM
            • 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 6 of 12 , May 25 10:13 PM
              • 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 7 of 12 , May 26 5:27 PM
                • 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 8 of 12 , May 26 7:51 PM
                  • 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 9 of 12 , May 31 11:36 PM
                    • 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 10 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 11 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.