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

XMLDBMS and Qualified Schemas

Expand Messages
  • speckers2004
    I ve read in the XMLDBMS documentation that it possible to qualify tables with schema names, but looking through the examples, I m not quite sure about the
    Message 1 of 5 , Oct 18, 2004
    • 0 Attachment
      I've read in the XMLDBMS documentation that it possible to qualify
      tables with schema names, but looking through the examples, I'm not
      quite sure about the syntax, or if I'm thinking about the same type
      of schema (XML schema vs. database schema).

      Anyway, I have an XML document containing data that needs to be
      transferred to a number of database schemas:

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE XMLToDBMS SYSTEM "../xmlDoco/xmldbms.dtd">
      <XMLToDBMS Version="1.0">
      <Options>
      <EmptyStringIsNull/>
      <DateTimeFormats>
      <Patterns Date="yyyy-MM-dd"/>
      </DateTimeFormats>
      </Options>
      <Maps>
      <IgnoreRoot>
      <ElementType Name="LivestockSale"/>
      <PseudoRoot>
      <ElementType Name="ProceedsTransaction"/>
      </PseudoRoot>
      <PseudoRoot>
      <ElementType Name="SalesAnalysisTransaction"/>
      </PseudoRoot>
      <PseudoRoot>
      <ElementType Name="GeneralLedgerTransaction"/>
      </PseudoRoot>
      <PseudoRoot>
      <ElementType Name="CommissionsTransaction"/>
      </PseudoRoot>
      </IgnoreRoot>

      <ClassMap>
      <ElementType Name="ProceedsTransaction"/>
      <ToClassTable>
      <Table Name="WFAUREP"/>
      </ToClassTable>
      .
      .
      .

      For example, the data in the element ProceedsTransaction will go a
      file WFAUREP in the database schema PMPWF01, while the data in
      GeneralLedgerTransaction will go to a file in database schema
      PMPFILES01.

      I've been able to set the default database schema (or library
      as it is known in the IBM AS/400 world) as part of my JDBC connection
      url:

      properties.put("libraries", "pmpwf01 pmpfiles01");

      // Get a JDBC driver.
      Class.forName("com.ibm.as400.access.AS400JDBCDriver").newInstance
      ();

      // Connect to the database.
      conn1 = DriverManager.getConnection(url, properties);
      conn2 = DriverManager.getConnection(url, properties);

      System.out.println("Successful connection.\n");

      // Create and initialize a key generator
      keyGenerator = new KeyGeneratorImpl(conn1);
      keyGenerator.initialize();

      // Create the Map object and open the XML document.
      map = createMap(mapFilename, conn2);

      System.out.println("About to transfer the data...\n");

      domToDBMS = new DOMToDBMS(map, keyGenerator, new NQ_DOM2());
      domToDBMS.storeDocument(xmlFileDoc);

      System.out.println("... done.\n");

      But, this only works as far as the first listed library. For example,
      when I try to run data into files in the library PMPFILES01, I'm
      getting an error message saying the files can't be found in PMPWF01.

      What is the proper way to qualify table names with their schemas?
    • Ronald Bourret
      The schema referred to by the XML-DBMS documentation in this context is a database schema, not an XML schema. In version 1.x (which you are using), all
      Message 2 of 5 , Oct 18, 2004
      • 0 Attachment
        The "schema" referred to by the XML-DBMS documentation in this context
        is a database schema, not an XML schema.

        In version 1.x (which you are using), all schemas must be on the same
        physical database. That is, when you connect to the database, you can
        access all of the schemas. In version 2.0, schemas can reside on
        different databases -- the application simply provides different
        connections for each database.

        The syntax for a schema in v1.x is <schema-name>.<table-name>. For example:

        <ClassMap>
        <ElementType Name="ProceedsTransaction"/>
        <ToClassTable>
        <Table Name="PMPWF01.WFAUREP"/>
        </ToClassTable>
        ...

        This is problematic if the schema name has a period in it, so schema
        names and table names are explicitly separated in v2.0. For example:

        <ClassMap>
        <ElementType Name="ProceedsTransaction"/>
        <ToClassTable Schema="PMPWF01" Name="WFAUREP" />
        ...

        For safety, you should probably include both schema names, even if you
        set the default schema during your connection. I don't think this will
        cause any problems, it will make your map more portable (it can then be
        used with connections that use a different schema), and it will make
        clear to the reader exactly which schema/table is being used.

        One final note is that XML-DBMS assumes that a period is used to
        separate schema names from table names when constructing SQL statements.
        If this is not true for the AS/400, let me know and I'll tell you how to
        modify the code.

        -- Ron

        speckers2004 wrote:

        > I've read in the XMLDBMS documentation that it possible to qualify
        > tables with schema names, but looking through the examples, I'm not
        > quite sure about the syntax, or if I'm thinking about the same type
        > of schema (XML schema vs. database schema).
        >
        > Anyway, I have an XML document containing data that needs to be
        > transferred to a number of database schemas:
        >
        > <?xml version="1.0" encoding="UTF-8"?>
        > <!DOCTYPE XMLToDBMS SYSTEM "../xmlDoco/xmldbms.dtd">
        > <XMLToDBMS Version="1.0">
        > <Options>
        > <EmptyStringIsNull/>
        > <DateTimeFormats>
        > <Patterns Date="yyyy-MM-dd"/>
        > </DateTimeFormats>
        > </Options>
        > <Maps>
        > <IgnoreRoot>
        > <ElementType Name="LivestockSale"/>
        > <PseudoRoot>
        > <ElementType Name="ProceedsTransaction"/>
        > </PseudoRoot>
        > <PseudoRoot>
        > <ElementType Name="SalesAnalysisTransaction"/>
        > </PseudoRoot>
        > <PseudoRoot>
        > <ElementType Name="GeneralLedgerTransaction"/>
        > </PseudoRoot>
        > <PseudoRoot>
        > <ElementType Name="CommissionsTransaction"/>
        > </PseudoRoot>
        > </IgnoreRoot>
        >
        > <ClassMap>
        > <ElementType Name="ProceedsTransaction"/>
        > <ToClassTable>
        > <Table Name="WFAUREP"/>
        > </ToClassTable>
        > .
        > .
        > .
        >
        > For example, the data in the element ProceedsTransaction will go a
        > file WFAUREP in the database schema PMPWF01, while the data in
        > GeneralLedgerTransaction will go to a file in database schema
        > PMPFILES01.
        >
        > I've been able to set the default database schema (or library
        > as it is known in the IBM AS/400 world) as part of my JDBC connection
        > url:
        >
        > properties.put("libraries", "pmpwf01 pmpfiles01");
        >
        > // Get a JDBC driver.
        > Class.forName("com.ibm.as400.access.AS400JDBCDriver").newInstance
        > ();
        >
        > // Connect to the database.
        > conn1 = DriverManager.getConnection(url, properties);
        > conn2 = DriverManager.getConnection(url, properties);
        >
        > System.out.println("Successful connection.\n");
        >
        > // Create and initialize a key generator
        > keyGenerator = new KeyGeneratorImpl(conn1);
        > keyGenerator.initialize();
        >
        > // Create the Map object and open the XML document.
        > map = createMap(mapFilename, conn2);
        >
        > System.out.println("About to transfer the data...\n");
        >
        > domToDBMS = new DOMToDBMS(map, keyGenerator, new NQ_DOM2());
        > domToDBMS.storeDocument(xmlFileDoc);
        >
        > System.out.println("... done.\n");
        >
        > But, this only works as far as the first listed library. For example,
        > when I try to run data into files in the library PMPFILES01, I'm
        > getting an error message saying the files can't be found in PMPWF01.
        >
        > What is the proper way to qualify table names with their schemas?
      • speckers2004
        Thanks for the details Ronald. I was still having some trouble getting the schema/file name combination to work even though the database metadata looks fairly
        Message 3 of 5 , Oct 20, 2004
        • 0 Attachment
          Thanks for the details Ronald.

          I was still having some trouble getting the schema/file name
          combination to work even though the database metadata looks fairly
          standard:

          meta.isCatalogAtStart() = true
          meta.getCatalogSeparator() = .
          meta.getCatalogTerm() = System

          Tracking back from the exception message ("Table not found..."), I
          focussed on method addColumnMetadata in Map.java. I pulled the method
          out into a little test routine and I found that when I used <Table
          Name="PMPWF01.WFTSP100"/>, PMPWF01 (the schema) was being taken as
          the the catalog name and so the call to meta.getColumns(catalog,
          schema, tableName, null) wasn't finding anything.

          So, I commented out the catalog test in addColumnMetadata

          /* if (meta.getCatalogTerm().length() != 0)
          {
          separatorIndex = tableName.indexOf(meta.getCatalogSeparator());
          if (separatorIndex != -1)
          {
          if (meta.isCatalogAtStart())
          {
          catalog = tableName.substring(0, separatorIndex);
          tableName = tableName.substring(separatorIndex + 1);
          }
          else
          {
          catalog = tableName.substring(separatorIndex + 1);
          tableName = tableName.substring(0, separatorIndex);
          }
          }
          }

          */

          After this, the call to getColumns() was going through as getColumns
          (null, PMPWF01, WFTSP100, null), and my little test routine was
          finding the file in DB2/400.

          But, I'm now getting a JDBC error:

          java.sql.SQLException: [SQL0204] PMPWF01.WFTSP100 in PMPWF01 type
          *FILE not found.
          at com.ibm.as400.access.JDError.throwSQLException
          (JDError.java:388)
          at com.ibm.as400.access.AS400JDBCStatement.commonPrepare
          (AS400JDBCStatement.java:1062)
          at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>
          (AS400JDBCPreparedStatement.java:207)
          at com.ibm.as400.access.AS400JDBCConnection.prepareStatement
          (AS400JDBCConnection.java:1405)
          at com.ibm.as400.access.AS400JDBCConnection.prepareStatement
          (AS400JDBCConnection.java:1357)
          at de.tudarmstadt.ito.xmldbms.Map.checkOutInsertStmt
          (Map.java:464)
          at de.tudarmstadt.ito.xmldbms.DOMToDBMS.insertRow
          (DOMToDBMS.java:701)
          at de.tudarmstadt.ito.xmldbms.DOMToDBMS.createClassRow
          (DOMToDBMS.java:359)
          at de.tudarmstadt.ito.xmldbms.DOMToDBMS.processRootElement
          (DOMToDBMS.java:312)
          at de.tudarmstadt.ito.xmldbms.DOMToDBMS.processRoot
          (DOMToDBMS.java:291)
          at de.tudarmstadt.ito.xmldbms.DOMToDBMS.storeDocument
          (DOMToDBMS.java:241)
          at sandbox.XMLtoRDB.doTransfer(XMLtoRDB.java:66)
          at sandbox.XMLcontroller.main(XMLcontroller.java:35)

          It seems that the whole of the contents of <Table
          Name="PMPWF01.WFTSP100"/> are being passed across as the file name.

          I'd like to carry on with version 1.x if possible. Am I on the right
          track?

          Craig.
        • Ronald Bourret
          1) With regard to your first change: (a) oops and (b) the fix you made is correct for your situation. 2) With regard to the second problem: (a) oops. It
          Message 4 of 5 , Oct 20, 2004
          • 0 Attachment
            1) With regard to your first change: (a) oops and (b) the fix you made
            is correct for your situation.

            2) With regard to the second problem: (a) oops. It appears you are the
            first person to ever use schema names with version 1.x, since I just
            checked the code and it clearly doesn't work.

            The problem is that XML-DBMS quotes all identifiers. I won't go into the
            reasons for this, but there are good ones. Unfortunately, the code that
            builds SQL statements doesn't consider the fact that table names from
            the map document might be qualified with catalog and schema names.
            Therefore, it quotes the entire, qualified name, resulting in statements
            that look like:

            INSERT INTO "PMPWF01.WFTSP100" ...

            Obviously, this doesn't work and results in the error you are getting.

            If you don't ever use table names that need to be quoted -- that is, you
            don't rely on case in table names and table names don't have "illegal"
            characters like spaces in them -- then the simplest solution is to
            modify the code in the methods in Map that build INSERT and SELECT
            statements. In particular, comment out the lines that quote the table
            names. For example:

            private String buildInsertString(Table t) throws SQLException
            {
            int i;
            StringBuffer istr;

            istr = new StringBuffer(1000);

            // Create the INSERT statement.
            // 6/9/00, Ruben Lainez, Ronald Bourret
            // Use the identifier quote character for the table name.

            istr.append(INSERT);
            // istr.append(quote);
            istr.append(t.name);
            // istr.append(quote);
            ...

            This will result in statements that look like:

            INSERT INTO PMPWF01.WFTSP100 ...

            You will need to make similar changes in buildCreateTableString and
            buildSelectString.

            If you do use quoted identifiers, then you will need to modify the code
            in these methods to parse table names and build identifiers of the form
            "<schema-name>"."<table-name>". This isn't quite as expensive as it
            sounds, as the strings are only built once per map creation and a map
            can be reused across multiple document insertions.

            Hopefully this makes sense -- give me a yell if it doesn't.

            -- Ron


            speckers2004 wrote:
            >
            > Thanks for the details Ronald.
            >
            > I was still having some trouble getting the schema/file name
            > combination to work even though the database metadata looks fairly
            > standard:
            >
            > meta.isCatalogAtStart() = true
            > meta.getCatalogSeparator() = .
            > meta.getCatalogTerm() = System
            >
            > Tracking back from the exception message ("Table not found..."), I
            > focussed on method addColumnMetadata in Map.java. I pulled the method
            > out into a little test routine and I found that when I used <Table
            > Name="PMPWF01.WFTSP100"/>, PMPWF01 (the schema) was being taken as
            > the the catalog name and so the call to meta.getColumns(catalog,
            > schema, tableName, null) wasn't finding anything.
            >
            > So, I commented out the catalog test in addColumnMetadata
            >
            > /* if (meta.getCatalogTerm().length() != 0)
            > {
            > separatorIndex = tableName.indexOf(meta.getCatalogSeparator());
            > if (separatorIndex != -1)
            > {
            > if (meta.isCatalogAtStart())
            > {
            > catalog = tableName.substring(0, separatorIndex);
            > tableName = tableName.substring(separatorIndex + 1);
            > }
            > else
            > {
            > catalog = tableName.substring(separatorIndex + 1);
            > tableName = tableName.substring(0, separatorIndex);
            > }
            > }
            > }
            >
            > */
            >
            > After this, the call to getColumns() was going through as getColumns
            > (null, PMPWF01, WFTSP100, null), and my little test routine was
            > finding the file in DB2/400.
            >
            > But, I'm now getting a JDBC error:
            >
            > java.sql.SQLException: [SQL0204] PMPWF01.WFTSP100 in PMPWF01 type
            > *FILE not found.
            > at com.ibm.as400.access.JDError.throwSQLException
            > (JDError.java:388)
            > at com.ibm.as400.access.AS400JDBCStatement.commonPrepare
            > (AS400JDBCStatement.java:1062)
            > at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>
            > (AS400JDBCPreparedStatement.java:207)
            > at com.ibm.as400.access.AS400JDBCConnection.prepareStatement
            > (AS400JDBCConnection.java:1405)
            > at com.ibm.as400.access.AS400JDBCConnection.prepareStatement
            > (AS400JDBCConnection.java:1357)
            > at de.tudarmstadt.ito.xmldbms.Map.checkOutInsertStmt
            > (Map.java:464)
            > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.insertRow
            > (DOMToDBMS.java:701)
            > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.createClassRow
            > (DOMToDBMS.java:359)
            > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.processRootElement
            > (DOMToDBMS.java:312)
            > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.processRoot
            > (DOMToDBMS.java:291)
            > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.storeDocument
            > (DOMToDBMS.java:241)
            > at sandbox.XMLtoRDB.doTransfer(XMLtoRDB.java:66)
            > at sandbox.XMLcontroller.main(XMLcontroller.java:35)
            >
            > It seems that the whole of the contents of <Table
            > Name="PMPWF01.WFTSP100"/> are being passed across as the file name.
            >
            > I'd like to carry on with version 1.x if possible. Am I on the right
            > track?
          • speckers2004
            Thanks again Ron, I made the changes you suggested and we re back in business. Thank you. Craig. ... made ... the ... just ... into the ... that ... from ...
            Message 5 of 5 , Oct 22, 2004
            • 0 Attachment
              Thanks again Ron,

              I made the changes you suggested and we're back in business. Thank
              you.

              Craig.


              --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
              wrote:
              > 1) With regard to your first change: (a) oops and (b) the fix you
              made
              > is correct for your situation.
              >
              > 2) With regard to the second problem: (a) oops. It appears you are
              the
              > first person to ever use schema names with version 1.x, since I
              just
              > checked the code and it clearly doesn't work.
              >
              > The problem is that XML-DBMS quotes all identifiers. I won't go
              into the
              > reasons for this, but there are good ones. Unfortunately, the code
              that
              > builds SQL statements doesn't consider the fact that table names
              from
              > the map document might be qualified with catalog and schema names.
              > Therefore, it quotes the entire, qualified name, resulting in
              statements
              > that look like:
              >
              > INSERT INTO "PMPWF01.WFTSP100" ...
              >
              > Obviously, this doesn't work and results in the error you are
              getting.
              >
              > If you don't ever use table names that need to be quoted -- that
              is, you
              > don't rely on case in table names and table names don't
              have "illegal"
              > characters like spaces in them -- then the simplest solution is to
              > modify the code in the methods in Map that build INSERT and SELECT
              > statements. In particular, comment out the lines that quote the
              table
              > names. For example:
              >
              > private String buildInsertString(Table t) throws SQLException
              > {
              > int i;
              > StringBuffer istr;
              >
              > istr = new StringBuffer(1000);
              >
              > // Create the INSERT statement.
              > // 6/9/00, Ruben Lainez, Ronald Bourret
              > // Use the identifier quote character for the table name.
              >
              > istr.append(INSERT);
              > // istr.append(quote);
              > istr.append(t.name);
              > // istr.append(quote);
              > ...
              >
              > This will result in statements that look like:
              >
              > INSERT INTO PMPWF01.WFTSP100 ...
              >
              > You will need to make similar changes in buildCreateTableString and
              > buildSelectString.
              >
              > If you do use quoted identifiers, then you will need to modify the
              code
              > in these methods to parse table names and build identifiers of the
              form
              > "<schema-name>"."<table-name>". This isn't quite as expensive as it
              > sounds, as the strings are only built once per map creation and a
              map
              > can be reused across multiple document insertions.
              >
              > Hopefully this makes sense -- give me a yell if it doesn't.
              >
              > -- Ron
              >
              >
              > speckers2004 wrote:
              > >
              > > Thanks for the details Ronald.
              > >
              > > I was still having some trouble getting the schema/file name
              > > combination to work even though the database metadata looks
              fairly
              > > standard:
              > >
              > > meta.isCatalogAtStart() = true
              > > meta.getCatalogSeparator() = .
              > > meta.getCatalogTerm() = System
              > >
              > > Tracking back from the exception message ("Table not found..."),
              I
              > > focussed on method addColumnMetadata in Map.java. I pulled the
              method
              > > out into a little test routine and I found that when I used
              <Table
              > > Name="PMPWF01.WFTSP100"/>, PMPWF01 (the schema) was being taken
              as
              > > the the catalog name and so the call to meta.getColumns(catalog,
              > > schema, tableName, null) wasn't finding anything.
              > >
              > > So, I commented out the catalog test in addColumnMetadata
              > >
              > > /* if (meta.getCatalogTerm().length() != 0)
              > > {
              > > separatorIndex = tableName.indexOf(meta.getCatalogSeparator
              ());
              > > if (separatorIndex != -1)
              > > {
              > > if (meta.isCatalogAtStart())
              > > {
              > > catalog = tableName.substring(0, separatorIndex);
              > > tableName = tableName.substring(separatorIndex +
              1);
              > > }
              > > else
              > > {
              > > catalog = tableName.substring(separatorIndex + 1);
              > > tableName = tableName.substring(0, separatorIndex);
              > > }
              > > }
              > > }
              > >
              > > */
              > >
              > > After this, the call to getColumns() was going through as
              getColumns
              > > (null, PMPWF01, WFTSP100, null), and my little test routine was
              > > finding the file in DB2/400.
              > >
              > > But, I'm now getting a JDBC error:
              > >
              > > java.sql.SQLException: [SQL0204] PMPWF01.WFTSP100 in PMPWF01 type
              > > *FILE not found.
              > > at com.ibm.as400.access.JDError.throwSQLException
              > > (JDError.java:388)
              > > at com.ibm.as400.access.AS400JDBCStatement.commonPrepare
              > > (AS400JDBCStatement.java:1062)
              > > at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>
              > > (AS400JDBCPreparedStatement.java:207)
              > > at com.ibm.as400.access.AS400JDBCConnection.prepareStatement
              > > (AS400JDBCConnection.java:1405)
              > > at com.ibm.as400.access.AS400JDBCConnection.prepareStatement
              > > (AS400JDBCConnection.java:1357)
              > > at de.tudarmstadt.ito.xmldbms.Map.checkOutInsertStmt
              > > (Map.java:464)
              > > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.insertRow
              > > (DOMToDBMS.java:701)
              > > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.createClassRow
              > > (DOMToDBMS.java:359)
              > > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.processRootElement
              > > (DOMToDBMS.java:312)
              > > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.processRoot
              > > (DOMToDBMS.java:291)
              > > at de.tudarmstadt.ito.xmldbms.DOMToDBMS.storeDocument
              > > (DOMToDBMS.java:241)
              > > at sandbox.XMLtoRDB.doTransfer(XMLtoRDB.java:66)
              > > at sandbox.XMLcontroller.main(XMLcontroller.java:35)
              > >
              > > It seems that the whole of the contents of <Table
              > > Name="PMPWF01.WFTSP100"/> are being passed across as the file
              name.
              > >
              > > I'd like to carry on with version 1.x if possible. Am I on the
              right
              > > track?
            Your message has been successfully submitted and would be delivered to recipients shortly.