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

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

Expand Messages
  • Marcel Ruff
    Jul 21, 2000
    • 0 Attachment
      Ronald Bourret wrote:
      >
      > 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
      > database.
      >
      > 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
      > "http://www.informatik.tu-darmstadt.de/DVS1/staff/bourret/xmldbms/xmldbms.dtd",
      > 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
      > >de.tudarmstadt.ito.xmldbms.DBMSToDOM.retrieveDocument(DBMSToDOM.java:197)
      > > 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
      Hi Ron,

      thanks for your clarifications.
      I'm currently overloaded with other work,
      but i will come back to this topic
      in some weeks.

      regards,

      Marcel


      --
      Marcel Ruff
      ruff@...
      http://www.lake.de/home/lake/swand/
      http://www.xmlBlaster.org
    • Show all 3 messages in this topic