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 Shaun <sh...@shaunc.com> on 2006/06/23 16:34:38 UTC

Exceptions when using DdlUtils and Platform.readModelFromDatabase()

Hi all,

I've just found DdlUtils and I'm quite impressed by this package. I'm
running into a bit of trouble trying to export schema from MSSQL 
databases, though. Here's a class I'm using to test out DdlUtils (I'm
omitting the imports for the sake of brevity) 

public class DdlUtilTest {

    public static void main(String[] args) throws SQLException {

        JtdsDataSource ds = new JtdsDataSource();
        ds.setUser("sa");
        ds.setPassword("xxxxx");
        ds.setServerName("x.x.x.x");
        ds.setDatabaseName("MyDbName");
        
        Connection conn = ds.getConnection();
        DatabaseMetaData meta = conn.getMetaData();
        Platform p = PlatformFactory.createNewPlatformInstance(ds);
        
        String filename = "src\\schema\\MyDbName-schema.xml";
        new DatabaseIO().write(p.readModelFromDatabase("MyDbName"), filename);
    }
}

This works perfectly for some databases, but throws seemingly random
exceptions for others. For example, running it against "MyDbName" 
properly generates src\schema\MyDbName-schema.xml, which contains the
correct schema. However, trying to run it against "MySecondDb" throws
this exception:

Exception in thread "main" org.apache.ddlutils.DynaSqlException: java.sql.SQLException: Invalid column name 'Zip'.
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1684)
	at com.mycompany.test.ddlutiltest.DdlUtilTest.main(DdlUtilTest.java:69)

Trying to run it against "YetAnotherDb" throws this exception:

Exception in thread "main" org.apache.ddlutils.DynaSqlException: java.sql.SQLException: Incorrect syntax near the keyword 'Primary'.
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1684)
	at com.mycompany.test.ddlutiltest.DdlUtilTest.main(DdlUtilTest.java:69)

In both of these cases the exception is being thrown from this line:

	new DatabaseIO().write(p.readModelFromDatabase("MyDbName"),
filename);

I've tried several of the different signatures for
readModelFromDatabase() and I wind up with the same exceptions no matter
what I pass to it. Has anyone experienced similar issues? Am I using one
of the DdlUtils methods incorrectly, or are the exceptions being thrown
due to some problem with the database server?

Thanks,

Shaun

Re: Exceptions when using DdlUtils and Platform.readModelFromDatabase()

Posted by Thomas Dudziak <to...@gmail.com>.
On 6/23/06, Shaun <sh...@shaunc.com> wrote:
> On Fri, 23 Jun 2006 11:24:50 -0500
> Shaun <sh...@shaunc.com> wrote:
>
> > Exception in thread "main" org.apache.ddlutils.DynaSqlException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'Zip'.
> >       at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)
>
> Actually, this boils down to the same problem as the other exception.
> The column name is [Zip Code], not Zip (threw me off because we do have
> tables with a column named Zip), and the brackets are being stripped
> there, too.
>
> Apologies to the list - it appears this was not a DdlUtils issue.

Actually, your database uses delimited identifiers
(http://msdn2.microsoft.com/en-us/library/ms176027.aspx). So, in order
to properly read the database with DdlUtils, you must turn on
delimited identifiers support via the corresponding
useDelimitedSqlIdentifiers attribute of the Ant task
(http://db.apache.org/ddlutils/ant-tasks.html#DdlToDatabaseTask+reference).

Tom

Re: Exceptions when using DdlUtils and Platform.readModelFromDatabase()

Posted by Shaun <sh...@shaunc.com>.
On Fri, 23 Jun 2006 11:24:50 -0500
Shaun <sh...@shaunc.com> wrote:

> Exception in thread "main" org.apache.ddlutils.DynaSqlException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'Zip'.
> 	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)

Actually, this boils down to the same problem as the other exception.
The column name is [Zip Code], not Zip (threw me off because we do have
tables with a column named Zip), and the brackets are being stripped
there, too.

Apologies to the list - it appears this was not a DdlUtils issue.

Shaun

Re: Exceptions when using DdlUtils and Platform.readModelFromDatabase()

Posted by Shaun <sh...@shaunc.com>.
On Fri, 23 Jun 2006 17:34:10 +0200
"Thomas Dudziak" <to...@gmail.com> wrote:

> These are SQL Exceptions and thus come from the database or the
> driver. Unfortunately you left out the interesting part of the
> stacktrace - there should be more to either one as the line where the
> exception is thrown, PlatformImplBase.java:1706, is where the original
> SQL exception is wrapped into a DdlUtils exception. There more
> interesting place would be where this original SQL exception happens.
> Please also note that DdlUtils is untested with jTDS, so there may be
> some issues with the JDBC metadata capabilities of jTDS that result in
> problems with DdlUtils.
> Could you run your tests with the Microsoft Sql Server 2005 driver to
> see whether the error occurs there as well ?

Hi Tom,

Thanks for the swift response.

Firstly, I've tracked down one of the exceptions that I was getting;
there's a column in one of our tables named [Primary Buyer], and the
brackets are being stripped out of the column name at some point, thus
the word "Primary" shows up in the middle of the query. Great... :)

Here's a stack trace from the other exception, using the MSSQL driver
instead of Jtds:

Exception in thread "main" org.apache.ddlutils.DynaSqlException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'Zip'.
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1684)
	at com.mycompany.test.ddlutiltest.DdlUtilTest.main(DdlUtilTest.java:52)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'Zip'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
	at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteQuery(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
	at org.apache.ddlutils.platform.JdbcModelReader.determineAutoIncrementFromResultSetMetaData(JdbcModelReader.java:1028)
	at org.apache.ddlutils.platform.mssql.MSSqlModelReader.readTable(MSSqlModelReader.java:102)
	at org.apache.ddlutils.platform.JdbcModelReader.readTables(JdbcModelReader.java:505)
	at org.apache.ddlutils.platform.JdbcModelReader.getDatabase(JdbcModelReader.java:468)
	at org.apache.ddlutils.platform.JdbcModelReader.getDatabase(JdbcModelReader.java:428)
	at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1699)

Thanks,

Shaun

Re: Exceptions when using DdlUtils and Platform.readModelFromDatabase()

Posted by Thomas Dudziak <to...@gmail.com>.
On 6/23/06, Shaun <sh...@shaunc.com> wrote:

> I've just found DdlUtils and I'm quite impressed by this package. I'm
> running into a bit of trouble trying to export schema from MSSQL
> databases, though.

<snip>

> Exception in thread "main" org.apache.ddlutils.DynaSqlException: java.sql.SQLException: Invalid column name 'Zip'.
>         at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)
>         at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1684)
>         at com.mycompany.test.ddlutiltest.DdlUtilTest.main(DdlUtilTest.java:69)
>
> Trying to run it against "YetAnotherDb" throws this exception:
>
> Exception in thread "main" org.apache.ddlutils.DynaSqlException: java.sql.SQLException: Incorrect syntax near the keyword 'Primary'.
>         at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1706)
>         at org.apache.ddlutils.platform.PlatformImplBase.readModelFromDatabase(PlatformImplBase.java:1684)
>         at com.mycompany.test.ddlutiltest.DdlUtilTest.main(DdlUtilTest.java:69)
>
> In both of these cases the exception is being thrown from this line:
>
>         new DatabaseIO().write(p.readModelFromDatabase("MyDbName"),
> filename);
>
> I've tried several of the different signatures for
> readModelFromDatabase() and I wind up with the same exceptions no matter
> what I pass to it. Has anyone experienced similar issues? Am I using one
> of the DdlUtils methods incorrectly, or are the exceptions being thrown
> due to some problem with the database server?

These are SQL Exceptions and thus come from the database or the
driver. Unfortunately you left out the interesting part of the
stacktrace - there should be more to either one as the line where the
exception is thrown, PlatformImplBase.java:1706, is where the original
SQL exception is wrapped into a DdlUtils exception. There more
interesting place would be where this original SQL exception happens.
Please also note that DdlUtils is untested with jTDS, so there may be
some issues with the JDBC metadata capabilities of jTDS that result in
problems with DdlUtils.
Could you run your tests with the Microsoft Sql Server 2005 driver to
see whether the error occurs there as well ?

Tom