You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Jun Li <al...@gmail.com> on 2006/02/08 05:57:29 UTC
Problem with reading XML.
Hi all,
Just tried ddlutils today. The XML it generates from database looks greate.
But I have problem with reading XML.
When I was trying to read the XML file generated back to an empty
database(SQL Server), there are exceptions which say: The column nr 5 in
table XXX has no type. However, the XML file really had type attribute and
value for all the columns.
What's the problem there? How could I fix it?
Another problem, I also tied to read a database into model and then write to
an empty database. The exceptions are as follow.
Exception in thread "main" *org.apache.ddlutils.DynaSqlException*: Error
while executing SQL CREATE TABLE "EventSource"
(
"EventSourceID" NUMERIC(9,0) NOT NULL,
"EventSourceTypeFK" NUMERIC(9,0) NOT NULL,
"Name" VARCHAR(100) NOT NULL,
"LoggedBy" VARCHAR(50) DEFAULT 'N'iCMS'' NOT NULL,
"LicenseeFK" NUMERIC(8,0) NOT NULL,
PRIMARY KEY ("EventSourceID")
)
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(*
PlatformImplBase.java:218*)
at org.apache.ddlutils.platform.PlatformImplBase.createTables(*
PlatformImplBase.java:311*)
at org.apache.ddlutils.platform.PlatformImplBase.createTables(*
PlatformImplBase.java:296*)
at Engine.db2xmlEngine.writeXMLToDatabase(*db2xmlEngine.java:71*)
at Engine.db2xmlEngine.main(*db2xmlEngine.java:42*)
Caused by: *java.sql.SQLException*: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Line 6: Incorrect syntax near 'iCMS'.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdate(Unknown Source)
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(*
PlatformImplBase.java:202*)
... 4 more
Can anyone give me some confidence that ddlutils really works on SQL Server.
I mean can create tables, insert records from reading XML file which
generated from other databases.
Re: Problem with reading XML.
Posted by Guy Davis <da...@guydavis.ca>.
Thomas Dudziak wrote:
> I'm currently working on the Sql Server part, should be ready in a
> couple of days.
> Btw, what version of Sql Server and JDBC driver are you using ?
Hi Tom,
Thanks for your help with Oracle data dumping and loading yesterday.
I'm at the stage where I'd like to take the schema and data dump (XML)
from my Oracle 9i DB and load it into an MS SQL Server Express 2005.
I'm using the SQL Server 2005 JDBC adapter (sqljdbc.jar).
Please respond to this post when you commit the SQL Server changes you
mention above. I would like to try them out as soon as they're ready.
Guy
Re: Problem with reading XML.
Posted by Jun Li <al...@gmail.com>.
Anyone else have the same unique key problem?
I mean when you import xml into a database (SQL Server), it creates an
unnecessary unique key for every table.
Tom, how can I do the filter in program?
On 2/16/06, Jun Li <al...@gmail.com> wrote:
>
>
>
> On 2/14/06, Thomas Dudziak <to...@gmail.com> wrote:
> >
> > On 2/13/06, Jun Li <al...@gmail.com> wrote:
> > > The problem with unique key still exists.
> >
> > That's strange. Every unit test uses a primary key and DdlUtils always
> > filters the unique indices for them. Could you try with the Sql Server
> > 2005 JDBC driver (I'm not sure what version 2.2.0022 is, but I guess
> > its the Sql Server 2000 driver)..
>
>
> I did use the latest Sql Server 2005 JDBC driver. The problem hasn't been
> solved.
>
>
> > And the problem with single apostrophe appeared again.
> >
> > I didn't work on that yet (issue is still open) so no surprise there.
>
>
> But it worked once, so I thought you've finished this.
>
>
> > One more question, can I export data into XML file using DdlUtils, if
> > yes,
> > > how?
> >
> > Yep, with the writeDataToFile sub task of the DatabaseToDdlTask task
> > (http://db.apache.org/ddlutils/ant-tasks.html#Subtask%3A+writeDataToFile
> > )
>
>
>
> How can I use it in java program?
>
> >
>
>
>
>
> --
> --------------
> Cheers,
> Jun
--
--------------
Cheers,
Jun
Re: Problem with reading XML.
Posted by Jun Li <al...@gmail.com>.
On 2/14/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 2/13/06, Jun Li <al...@gmail.com> wrote:
> > The problem with unique key still exists.
>
> That's strange. Every unit test uses a primary key and DdlUtils always
> filters the unique indices for them. Could you try with the Sql Server
> 2005 JDBC driver (I'm not sure what version 2.2.0022 is, but I guess
> its the Sql Server 2000 driver)..
I did use the latest Sql Server 2005 JDBC driver. The problem hasn't been
solved.
> And the problem with single apostrophe appeared again.
>
> I didn't work on that yet (issue is still open) so no surprise there.
But it worked once, so I thought you've finished this.
> One more question, can I export data into XML file using DdlUtils, if yes,
> > how?
>
> Yep, with the writeDataToFile sub task of the DatabaseToDdlTask task
> (http://db.apache.org/ddlutils/ant-tasks.html#Subtask%3A+writeDataToFile)
How can I use it in java program?
>
--
--------------
Cheers,
Jun
Re: Problem with reading XML.
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/13/06, Jun Li <al...@gmail.com> wrote:
> The problem with unique key still exists.
That's strange. Every unit test uses a primary key and DdlUtils always
filters the unique indices for them. Could you try with the Sql Server
2005 JDBC driver (I'm not sure what version 2.2.0022 is, but I guess
its the Sql Server 2000 driver)..
> And the problem with single apostrophe appeared again.
I didn't work on that yet (issue is still open) so no surprise there.
> One more question, can I export data into XML file using DdlUtils, if yes,
> how?
Yep, with the writeDataToFile sub task of the DatabaseToDdlTask task
(http://db.apache.org/ddlutils/ant-tasks.html#Subtask%3A+writeDataToFile)
Tom
Re: Problem with reading XML.
Posted by Jun Li <al...@gmail.com>.
The problem with unique key still exists. And the problem with single
apostrophe appeared again.
One more question, can I export data into XML file using DdlUtils, if yes,
how?
On 2/12/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 2/10/06, Jun Li <al...@gmail.com> wrote:
> > Once you fix this, please let us know.
>
> I've worked a bit more at the Sql Server platform, so please give it a
> try.
>
> Tom
>
--
--------------
Cheers,
Jun
Re: Problem with reading XML.
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/10/06, Jun Li <al...@gmail.com> wrote:
> Once you fix this, please let us know.
I've worked a bit more at the Sql Server platform, so please give it a try.
Tom
Re: Problem with reading XML.
Posted by Jun Li <al...@gmail.com>.
Once you fix this, please let us know.
Thanks.
On 2/9/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 2/9/06, Jun Li <al...@gmail.com> wrote:
> > Good. Now it works. However the new problem is, the databases are not
> > identical. I mean the database generated from the XML file has some
> > differences with the database which generates the XML file. I found the
> > difference simply by comparing the XML file.
>
> <snip>
>
> > Look at the hightlighted part. It happened to every table in the
> database. A
> > newly added unique constraint.
>
> As I said, I'm currently working on the Sql Server support. What
> you're seeing is the unique index that Sql Server automatically
> creates for primary keys and which DdlUtils has to filter out.
>
> Tom
>
--
--------------
Cheers,
Jun
Re: Problem with reading XML.
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/9/06, Jun Li <al...@gmail.com> wrote:
> Good. Now it works. However the new problem is, the databases are not
> identical. I mean the database generated from the XML file has some
> differences with the database which generates the XML file. I found the
> difference simply by comparing the XML file.
<snip>
> Look at the hightlighted part. It happened to every table in the database. A
> newly added unique constraint.
As I said, I'm currently working on the Sql Server support. What
you're seeing is the unique index that Sql Server automatically
creates for primary keys and which DdlUtils has to filter out.
Tom
Re: Problem with reading XML.
Posted by Jun Li <al...@gmail.com>.
Good. Now it works. However the new problem is, the databases are not
identical. I mean the database generated from the XML file has some
differences with the database which generates the XML file. I found the
difference simply by comparing the XML file.
The part of the original XML file.
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="PeriodFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="FileName" primaryKey="false" required="true"
type="VARCHAR" size="50" autoIncrement="false"/>
<column name="AccruedByFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="AccruedOn" primaryKey="false" required="true"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<foreign-key foreignTable="Employee" name="FK_Accrual_Employee">
<reference local="AccruedByFK" foreign="EmployeeID"/>
</foreign-key>
<foreign-key foreignTable="Period" name="FK_Accrual_Period">
<reference local="PeriodFK" foreign="PeriodID"/>
</foreign-key>
<unique name="PK_Accrual">
<unique-column name="AccrualID"/>
</unique>
<index name="IX_Accrual_Employee">
<index-column name="AccruedByFK"/>
</index>
<index name="IX_Accrual_Period">
<index-column name="PeriodFK"/>
</index>
</table>
The new XML file generated from the new database which creates from the
above XML file.
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="PeriodFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="FileName" primaryKey="false" required="true"
type="VARCHAR" size="50" autoIncrement="false"/>
<column name="AccruedByFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="AccruedOn" primaryKey="false" required="true"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<foreign-key foreignTable="Employee" name="FK_Accrual_Employee">
<reference local="AccruedByFK" foreign="EmployeeID"/>
</foreign-key>
<foreign-key foreignTable="Period" name="FK_Accrual_Period">
<reference local="PeriodFK" foreign="PeriodID"/>
</foreign-key>
<unique name="PK__Accrual__379B24DB">
<unique-column name="AccrualID"/>
</unique>
<unique name="PK_Accrual">
<unique-column name="AccrualID"/>
</unique>
<index name="IX_Accrual_Employee">
<index-column name="AccruedByFK"/>
</index>
<index name="IX_Accrual_Period">
<index-column name="PeriodFK"/>
</index>
</table>
Look at the hightlighted part. It happened to every table in the database. A
newly added unique constraint.
On 2/9/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 2/9/06, Jun Li <al...@gmail.com> wrote:
>
> > OK, Here is the info.
>
> <snip>
>
> > Here are the exceptions:
> >
> > ........
> >
> > 9/02/2006 09:57:04 org.apache.commons.betwixt.expression.ContextpopOptions
> >
> > INFO: Cannot pop options off empty stack
>
>
> You seem to be running an older version of DdlUtils (this particular
> INFO message comes from an older commons-betwixt version which we
> replaced a couple of weeks ago).
> Could you update DdlUtils to newest SVN and try again ?
>
> Tom
>
Re: Problem with reading XML.
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/9/06, Jun Li <al...@gmail.com> wrote:
> OK, Here is the info.
<snip>
> Here are the exceptions:
>
> ........
>
> 9/02/2006 09:57:04 org.apache.commons.betwixt.expression.Context popOptions
>
> INFO: Cannot pop options off empty stack
You seem to be running an older version of DdlUtils (this particular
INFO message comes from an older commons-betwixt version which we
replaced a couple of weeks ago).
Could you update DdlUtils to newest SVN and try again ?
Tom
Re: Problem with reading XML.
Posted by Jun Li <al...@gmail.com>.
On 2/8/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 2/8/06, Jun Li <al...@gmail.com> wrote:
>
> > Just tried ddlutils today. The XML it generates from database looks
> greate.
> > But I have problem with reading XML.
> > When I was trying to read the XML file generated back to an empty
> > database(SQL Server), there are exceptions which say: The column nr 5 in
> > table XXX has no type. However, the XML file really had type attribute
> and
> > value for all the columns.
>
> Please post some more info, e.g. the stacktrace and generated model.
OK, Here is the info.
XML schema:
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="build">
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="PeriodFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="FileName" primaryKey="false" required="true"
type="VARCHAR" size="50" autoIncrement="false"/>
<column name="AccruedByFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="AccruedOn" primaryKey="false" required="true"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<foreign-key foreignTable="Employee" name="FK_Accrual_Employee">
<reference local="AccruedByFK" foreign="EmployeeID"/>
</foreign-key>
<foreign-key foreignTable="Period" name="FK_Accrual_Period">
<reference local="PeriodFK" foreign="PeriodID"/>
</foreign-key>
<unique name="PK_Accrual">
<unique-column name="AccrualID"/>
</unique>
<index name="IX_Accrual_Employee">
<index-column name="AccruedByFK"/>
</index>
<index name="IX_Accrual_Period">
<index-column name="PeriodFK"/>
</index>
</table>
Here are the exceptions:
........
9/02/2006 09:57:04 org.apache.commons.betwixt.expression.Context popOptions
INFO: Cannot pop options off empty stack
9/02/2006 09:57:04 org.apache.commons.betwixt.expression.Context popOptions
INFO: Cannot pop options off empty stack
9/02/2006 09:57:04 org.apache.commons.betwixt.expression.Context popOptions
INFO: Cannot pop options off empty stack
Exception in thread "main" *org.apache.ddlutils.model.ModelException*: The
column nr. 5 in table Accrual has no type
at org.apache.ddlutils.model.Database.initialize(*Database.java:301*)
at org.apache.ddlutils.io.DatabaseIO.read(*DatabaseIO.java:152*)
at Engine.db2xmlEngine.readDatabaseFromXML(*db2xmlEngine.java:61*)
at Engine.db2xmlEngine.writeXMLToDatabase(*db2xmlEngine.java:65*)
at Engine.db2xmlEngine.main(*db2xmlEngine.java:42*)
> Another problem, I also tied to read a database into model and then write
> to
> > an empty database. The exceptions are as follow.
> >
> > Exception in thread "main" *org.apache.ddlutils.DynaSqlException*: Error
> > while executing SQL CREATE TABLE "EventSource"
> > (
> > "EventSourceID" NUMERIC(9,0) NOT NULL,
> > "EventSourceTypeFK" NUMERIC(9,0) NOT NULL,
> > "Name" VARCHAR(100) NOT NULL,
> > "LoggedBy" VARCHAR(50) DEFAULT 'N'iCMS'' NOT NULL,
>
> Here's the problem: the default value string contains a single
> apostroph which also is the quotation sign which naturally gives
> problems.
> In short, the apostroph needs to be escaped (DdlUtils does not yet do
> that but I have added issue DDLUTILS-66).
> If you have control over the schema (its not auto-generated), then for
> now please add the escape character yourself in the default value
> (sorry, don't now it for Sql Server right now).
<snip>
>
> > Can anyone give me some confidence that ddlutils really works on SQL
> Server.
> > I mean can create tables, insert records from reading XML file which
> > generated from other databases.
>
> I'm currently working on the Sql Server part, should be ready in a
> couple of days.
> Btw, what version of Sql Server and JDBC driver are you using ?
>
> Tom
>
Re: Problem with reading XML.
Posted by Allan Jun Li <al...@gmail.com>.
Hi Tom,
So glad to get your response this quickly.
I'll post more info for the first problem tomorrow morning.
Just from my memory, the exception raised for the very first table in the XML file generated, the table has five columns, the attributes and the values in the XML file look fine (will post the table schema tomorrow). When I tried to reading the XML file back into a model, the exception raised as "The column nr 5 in table XXX has no type", I tried to comment out that column, and then the exception changed to "The column nr 4 in table XXX has no type".
Another problem that I found was the table generated from the model lost it's relationships. Will post more info on this.
I'm using SQL Server 2000 with JDBC 2.2.0022.
Thanks.
----- Original Message -----
From: "Thomas Dudziak" <to...@gmail.com>
To: <dd...@db.apache.org>
Sent: Wednesday, February 08, 2006 7:04 PM
Subject: Re: Problem with reading XML.
On 2/8/06, Jun Li <al...@gmail.com> wrote:
> Just tried ddlutils today. The XML it generates from database looks greate.
> But I have problem with reading XML.
> When I was trying to read the XML file generated back to an empty
> database(SQL Server), there are exceptions which say: The column nr 5 in
> table XXX has no type. However, the XML file really had type attribute and
> value for all the columns.
Please post some more info, e.g. the stacktrace and generated model.
> Another problem, I also tied to read a database into model and then write to
> an empty database. The exceptions are as follow.
>
> Exception in thread "main" *org.apache.ddlutils.DynaSqlException*: Error
> while executing SQL CREATE TABLE "EventSource"
> (
> "EventSourceID" NUMERIC(9,0) NOT NULL,
> "EventSourceTypeFK" NUMERIC(9,0) NOT NULL,
> "Name" VARCHAR(100) NOT NULL,
> "LoggedBy" VARCHAR(50) DEFAULT 'N'iCMS'' NOT NULL,
Here's the problem: the default value string contains a single
apostroph which also is the quotation sign which naturally gives
problems.
In short, the apostroph needs to be escaped (DdlUtils does not yet do
that but I have added issue DDLUTILS-66).
If you have control over the schema (its not auto-generated), then for
now please add the escape character yourself in the default value
(sorry, don't now it for Sql Server right now).
<snip>
> Can anyone give me some confidence that ddlutils really works on SQL Server.
> I mean can create tables, insert records from reading XML file which
> generated from other databases.
I'm currently working on the Sql Server part, should be ready in a
couple of days.
Btw, what version of Sql Server and JDBC driver are you using ?
Tom
Re: Problem with reading XML.
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/8/06, Jun Li <al...@gmail.com> wrote:
> Just tried ddlutils today. The XML it generates from database looks greate.
> But I have problem with reading XML.
> When I was trying to read the XML file generated back to an empty
> database(SQL Server), there are exceptions which say: The column nr 5 in
> table XXX has no type. However, the XML file really had type attribute and
> value for all the columns.
Please post some more info, e.g. the stacktrace and generated model.
> Another problem, I also tied to read a database into model and then write to
> an empty database. The exceptions are as follow.
>
> Exception in thread "main" *org.apache.ddlutils.DynaSqlException*: Error
> while executing SQL CREATE TABLE "EventSource"
> (
> "EventSourceID" NUMERIC(9,0) NOT NULL,
> "EventSourceTypeFK" NUMERIC(9,0) NOT NULL,
> "Name" VARCHAR(100) NOT NULL,
> "LoggedBy" VARCHAR(50) DEFAULT 'N'iCMS'' NOT NULL,
Here's the problem: the default value string contains a single
apostroph which also is the quotation sign which naturally gives
problems.
In short, the apostroph needs to be escaped (DdlUtils does not yet do
that but I have added issue DDLUTILS-66).
If you have control over the schema (its not auto-generated), then for
now please add the escape character yourself in the default value
(sorry, don't now it for Sql Server right now).
<snip>
> Can anyone give me some confidence that ddlutils really works on SQL Server.
> I mean can create tables, insert records from reading XML file which
> generated from other databases.
I'm currently working on the Sql Server part, should be ready in a
couple of days.
Btw, what version of Sql Server and JDBC driver are you using ?
Tom