 next
|
  Subject: Is SQL/XML Extension usage limited by the database?
Author: Nick Forthman
Date: 22 Feb 2006 12:50 PM
|
I am using the Stylus Studio DB-to-XML Editor connected to a MySQL Server 4.1 database.
When my SELECT window has a SELECT * FROM <table_reference> everything works as it should and I get the rows expected in the Preview window.
When I attempt to use the default SQL/XML extensions based query:
SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "user_id",t.user_id),
XMLELEMENT(name "login_name",t.login_name),
...
I receive the following error:
java.sql.SQLException: [StylusStudio][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(name "row", XMLELEMENT(name "user_id",t.user_id), XMLELEMENT(name "login_name",' at line 1; at ZC.jdbc.jxtr.JXTRDriverConfig.translateSQLException(Unknown Source);
at ZC.jdbc.jxtr.JXTRDriverConfig.createSQLException(Unknown Source);
at ZC.jdbc.jxtr.JXTRDriverPassThruPreparedStatement.executeQuery(Unknown Source);
at com.stylusstudio.DataDirectStub.select(DataDirectStub.java:286);
at com.stylusstudio.SQLXFile.executeQuery(SQLXFile.java:533);
Do SQL/XML Extensions rely on support from the underlying database?
When I researched XMLELEMENT on MySQL it appears to not be supported natively, and that MySQL only starts getting into native support with XPATH in MySQL 5.1...
|
|
next
|
Subject: Is SQL/XML Extension usage limited by the database?
Author: Nick Forthman
Date: 22 Feb 2006 04:56 PM
|
Ok, in the process of making a clean example that can be shared with the world, I think I found the problem, but first the SQL/XML:
SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "id",t.id),
XMLELEMENT(name "name",t.name)
)
FROM test.user t
The above statement causes the same error as my original post and I believe that the problem is in the table reference: test.user
In that I think "user" is getting interpreted as a reserved word rather than as a table name. ( I didn't invent the table, just merely attempting to access it ;) ).
When I create the exact same table but with a name of user1 and change the table reference to test.user1 everything works fine.
Now according to the MySQL documentation, "A word that follows a period in a qualifed name must be an identifier, so it is not necessary to quote it, even if it is a reserved word." - http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html
So either this is a possible bug, or DataDirect Connect for SQL/XML requires an identifier quote character.
Note that in MySql the backtick (`) is the identifier quote character, but alas this does not seem to be honored (attempted `test.user` and test.`user`)
Please advise on bug or quote character (I have attempted with `, ', and " ).
|
|
top