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

Re: XMLDBMS and Qualified Schemas

Expand Messages
  • 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 1 of 5 , Oct 22, 2004
    View Source
    • 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.