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

149Re: [xml-dbms] Oracle 8i and XML-DBMS on Linux

Expand Messages
  • Ronald Bourret
    Jul 13, 2000
      Sorry it took so long to answer this. You've got a number of hard-to-answer
      questions, so I'm afraid I put them off for longer than I should have.

      >i tried today xmldbms with my Oracle8i installation
      >and Suns XML parser on my Linux box.
      >I had a hard time to get started ;-)

      Unfortunately, you're not the only one :(

      As a general comment, many of the problems you had occurred in the sample
      programs. Please note that these are *not* part of XML-DBMS -- they are just
      sample programs to help you understand how to use XML-DBMS. Because they are
      samples, I have spent less time on them than on the rest of XML-DBMS; I have
      also not attempted to make them parser or database-independent.

      However, this does raise a general question about how the sample programs
      are being used and how they can be made easier. I have addressed this in a
      separate email.

      >These code changes helped:
      >1. In /opt/xmldbms101/samples/*.java
      > DriverManager.getConnection(url, loginName, password);
      > I added loginName and password here since i don't know how
      > to pass them with the URL.

      Good point -- I forgot about this in the list of changes people might need
      to make to the samples. I will add it to the code and to the readme file.

      Note that there is no general solution to this problem. The reason is that
      different databases need different information to make a connection. For the
      sample programs, I chose to use the simplest connection method, which just
      takes a URL. In the future, I may be able to solve this by using the
      DataSource objects in JDBC 2; however, most people still seem to be shipping
      JDBC 1 drivers, so I don't want to use JDBC 2 until it is more widely used.

      >2. GenerateMap.java added at line 183
      > sqlFile.write(';');
      > To have a semicolon in the generated xy.sql file.
      > I had to replace the '"' in xy.sql as well, to allow
      > feeding it (after adjusting VARCHAR sizes):
      > cat xy.sql | sqlplus user/password

      Again, there is no general solution to this problem. The point of the .sql
      file generated by GenerateMap is to show people how Map objects can generate
      CREATE TABLE statements and what tables they need to store XML documents for
      a certain DTD. It seemed like a better idea to place these in a file, where
      people could modify them and use them elsewhere, than to just print them to
      the screen.

      Unfortunately, there is no guarantee that the statements will even be
      useable. There are two problems here. The first is that people need to
      change data type names and lengths. I can get data type names from JDBC -- I
      just haven't had time to implement the code yet. There is no solution to the
      length problem with DTDs, since DTDs can't specify lengths. The long-term
      solution to this problem is a map factory for XML Schemas.

      The second problem, which is what you attempted to solve, is how to separate
      multiple SQL statements. As far as I know, there is no standard (ISO) way of
      doing this. That is, I am assuming that the semi-colon is Oracle-specific,
      so adding it will work for Oracle but break other databases. (Some databases
      won't even accept more than one SQL statement at a time.) So if somebody can
      point me to a standard separator, I will be happy to use it. Otherwise, I
      will leave this as it is.

      >3. Setting the JDBC driver/url, xml parser could be
      > put in a properties file, or set from commandline?

      Setting the driver and url from the command line or in a properties file is
      a good idea. I will look into it for the next release.

      I am less interested in setting the parser. The problem is that there are
      some parser operations, such as getting a DOM tree, constructing the parser,
      and printing a DOM tree, for which there are no standard calls. This means
      that applications need to contain parser-specific code for these operations.

      Personally, I view writing this code as a one-time operation. That is, I
      expect most people to choose a parser and use it. While I have tried to make
      this process easier for beginners by including the parser-specific code I
      use during testing, I do not feel it is worth my time to do more than this
      -- I'd rather spend my time on the database parts of XML-DBMS.

      (On the other hand, if somebody wants to implement this in the samples,
      including writing the parser-specific code, I'd be happy to include it in
      the distribution. One good solution is to put the parser-specific code into
      a single class and have the samples inherit this class, so there is only one
      copy of the code.)

      >4.In de.tudarmstadt.ito.xmldbms.Map.java
      > for (int ii=0; ii<3; ii++) {
      > rs = meta.getColumns(catalog, schema, tableName, null);
      > while (rs.next())
      > {
      > tableFound = true;
      > // Get the next row of metadata and get the column name. If
      >the column
      > // isnt mapped, continue to the following row.
      > try
      > {
      > column = table.getColumn(rs.getString(4));
      > }
      > catch (InvalidMapException ime)
      > {
      > continue;
      > }
      > // Set the type and length.
      > column.type = (int)rs.getShort(5);
      > fixDateTimeType(column);
      > column.length = rs.getInt(7);
      > }
      > // Close the result set.
      > rs.close();
      > if (tableFound) {
      > System.out.println("Meta access for table '" + tableName +
      >"' successful");
      > break;
      > }
      > if (ii==0)
      > tableName = tableName.toUpperCase();
      > else if (ii==1)
      > tableName = tableName.toLowerCase();
      > }
      > I have added a loop, to first check with the given casesensitiv
      > table name, and if it fails checking all lower case and than all
      > upper case.
      > (The default behaviour is not changed and still same performing)
      > This is a hack, but it safes frustration.

      Unfortunately, this solution doesn't work. The problem is as follows.

      Identifiers (names) can be stored in the database in upper, lower, or mixed
      case. Furthermore, they can be case sensitive or insensitive. In a
      SQL-92-compliant database (which I assume Oracle is), unquoted identifiers
      are case-insensitive (and therefore can be stored in upper, lower, or mixed
      case -- Oracle stores them in upper case) and quoted identifiers are
      case-sensitive (and therefore stored in mixed case).

      Thus, in a SQL-92 compliant database that stores unquoted identifiers in
      upper case, the following statements create two different tables:

      CREATE TABLE FOO ... // Creates table FOO
      CREATE TABLE Foo ... // Creates table FOO
      CREATE TABLE foo ... // Creates table FOO
      CREATE TABLE "Foo" ... // Creates table Foo

      Now, suppose a map document specifies that element foo is mapped to table
      Foo. Ideally, XML-DBMS should convert this to the correct case before
      comparing it to the metadata retrieved from the database.

      The problem is that it is impossible to determine the correct case -- while
      JDBC returns information about how the database stores quoted and unquoted
      identifiers, there is no way to determine if the identifier was quoted in
      the CREATE TABLE statement or not. Thus, there is no way to determine if Foo
      in the map document refers to the FOO or the Foo table.

      There are two work-around for this. The first is to introduce identifier
      quoting in the map file -- that is, users would quote quoted identifiers and
      XML-DBMS would convert them accordingly. For example, it would convert the
      name Foo to FOO and leave the name "Foo" alone. The second is to require
      users to use exactly the same case in the map document as is used in the

      Since both of these solutions require the user to know how the name is
      stored in the database and the second requires less processing and is
      already implemented, that is what XML-DBMS uses.

      (If anybody can find a flaw in my logic or come up with a solution to this
      problem, I would be happy to implement it. It causes more problems than just
      about anything else...)

      (Note that this does point out a bug in MapFactory_DTD. This should call
      JDBC to determine how quoted identifiers are stored in the database and
      convert them accordingly before creating table and column names. This would
      guarantee that the CREATE TABLE statements created by a Map would work with
      that Map. However, it won't solve the general case where somebody else
      writes the map.)

      >5.In de.tudarmstadt.ito.xmldbms.Map.java
      > // Stripping '"', line 464:
      > String stmt = insertStrings[table.number];
      > stmt = stmt.replace('"', ' ');
      > System.out.println("Preparing Statement: " + stmt);
      > return conn.prepareStatement(stmt);
      > i had to remove the quotes around the table/column names
      > otherwise it would not feed Oracle using Transfer.java.

      This problem is a result of the change made in (5).

      XML-DBMS quotes all identifiers so that special characters (such as spaces)
      that otherwise would cause syntax errors do not cause problems. However,
      this works in all cases only if the quoted identifier exactly matches the
      identifier as it is stored in the database.

      Since the change in (5) allows identifiers to be used that shouldn't be
      (according to the case solution used by XML-DBMS), this change is
      necessitated by the change in (5). (Without the quotes, Oracle case-folds
      the names so that they match the names in the database.)

      Note also that this change does not work in all cases. In particular, it no
      longer distinguishes between case-sensitive and insensitive identifiers and
      will cause problems with special characters in identifiers.

      >6. Prefeed the database should be documented explicitly:
      > echo "CREATE TABLE XMLDBMSKey (HighKey INTEGER);" | sqlplus mrf/mrf
      > echo "INSERT INTO XMLDBMSKey VALUES (0);" | sqlplus mrf/mrf
      > Add a little ASCII 'INSTALL' file in the sample directory?

      This is documented, but it's very hard to find -- the last line of section
      7.1 tells you to read the documentation for KeyGeneratorImpl. I'll fix this
      in the next revision.

      I think an INSTALL file is a good idea -- it should create the tables used
      by all the samples and initialize the XMLDBMSKey table. Since the commands
      to do this are database-specific (and probably operating system specific as
      well), I will need a separate file for each database/OS. If you could send
      me one for Oracle (and other people could send me ones for other
      databases/OSs), I will include them in the next release.

      >7. You need to copy xmldbms.dtd to the current directory
      > is necessary.
      > Document it in 'INSTALL'?

      I think I'll just ship xmldbms.dtd in the top-level and the samples
      directories. It's needed in the top-level directory because it is an
      integral part of XML-DBMS; it's needed in the samples directory because the
      maps generated by GenerateMap assume it will be there.

      (Note that this is a specific case of a bigger problem -- what URL should
      Map objects use in the DOCTYPE statement when they are serializing
      themselves? I could have used a remote URL, such as
      but this would have required applications to connect to the Web just to
      parse/compile a map document. Instead, I chose to assume that xmldbms.dtd
      was in the same file as the map document and use the local URL
      "xmldbms.dtd". Obviously, this doesn't work in all cases, but I couldn't
      think of anything else.)

      >8. Now i was able to transfer some data.
      > java Transfer -todbms xy.map myData.xml

      Whew! :)

      >9: But i could not extract the data from Orcale:
      > java TransferResultSet -t requirement requirement.map out.xml
      > java.lang.NullPointerException:
      > at de.tudarmstadt.ito.xmldbms.Map.getRootTableMap(Map.java:428)
      > at
      > at TransferResultSet.toXML(TransferResultSet.java:145)
      > at TransferResultSet.main(TransferResultSet.java:104)
      > Note that the line numbers have changed due to code changes
      > it is the if() statement:
      > rootTableMap = (RootTableMap)rootTableMaps.get(rootTable);
      > if (rootTableMap.tableMap.elementType == null)
      > throw new InvalidMapException("Table not mapped as a root
      >table: " + rootTable);

      I don't know what is happening here -- could you send me the .map document
      and the database schema you used for this? I can't figure out what the
      problem is from the information you sent.

      >Is it possible to make the first steps with xmldbms simpler?

      I wish there was. Like all products where the user has to write code, there
      is no way to cover all possibilities. I think that one thing that might
      avoid some of the problems you had is to warn at the start of the Samples
      section of the readme file, as well as in the sample .map documents, that
      the case of table and column names must match the case in the database.

      If you have other ideas (such as those above), I am open to them.
      Unfortunately, XML-DBMS will never be a 100% out-of-the-box solution -- some
      work will always be required.

      Thanks for your input, and sorry again for not answering sooner.

      -- Ron
      Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
    • Show all 3 messages in this topic