[
https://issues.apache.org/jira/browse/DBUTILS-45?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Frank Hampshire closed DBUTILS-45.
----------------------------------
Resolution: Invalid
Meant to be raised against DDLUtils not DBUtils
> ON UPDATE, ON DELETE errors for MS Sql Server - does not implement default
> "RESTRICT"
> -------------------------------------------------------------------------------------
>
> Key: DBUTILS-45
> URL: https://issues.apache.org/jira/browse/DBUTILS-45
> Project: Commons DbUtils
> Issue Type: Bug
> Affects Versions: 1.2
> Environment: MS SQL Server 2005
> Reporter: Frank Hampshire
> Fix For: 1.2
>
>
> When exporting a database out of SQL Server 2005, a foreign key constrain
> onUpdate and onDelete values, when not set, are set as "restrict"
> Eg:
> In the table below for the table fktest ON DELETE has been set to 'cascade',
> while ON UPDATE has not been defined (defaults to NO ACTION), but the
> outputted onUpdate is 'restrict'
> <table name="fktest">
> <column name="ID" primaryKey="true" required="true" type="INTEGER"
> size="10" autoIncrement="false" />
> <column name="fktarget_id" primaryKey="false" required="false"
> type="INTEGER" size="10" autoIncrement="false" />
> <foreign-key foreignTable="fktarget" name="FK_fktest_fktarget"
> onUpdate="restrict" onDelete="cascade">
> <reference local="fktarget_id" foreign="fktarget_id" />
> </foreign-key>
> </table>
> Now, when it comes time to create this table from the XML, back into a MSSQL
> Server 2005 database, DDLUtils throws the following Exception.
> org.apache.ddlutils.DatabaseOperationException: Error while executing SQL
> ALTER TABLE fktest
> ADD CONSTRAINT FK_fktest_fktarget FOREIGN KEY (fktarget_id) REFERENCES
> fktarget (fktarget_id) ON DELETE CASCADE ON UPDATE RESTRICT
> at
> org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:358)
> at
> org.apache.ddlutils.platform.PlatformImplBase.createModel(PlatformImplBase.java:499)
> at
> com.haley.foundation.db.migrate.TestDdlUtils.testFK(TestDdlUtils.java:37)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at junit.framework.TestCase.runTest(TestCase.java:168)
> at junit.framework.TestCase.runBare(TestCase.java:134)
> at junit.framework.TestResult$1.protect(TestResult.java:110)
> at junit.framework.TestResult.runProtected(TestResult.java:128)
> at junit.framework.TestResult.run(TestResult.java:113)
> at junit.framework.TestCase.run(TestCase.java:124)
> at junit.framework.TestSuite.runTest(TestSuite.java:232)
> at junit.framework.TestSuite.run(TestSuite.java:227)
> at
> org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:76)
> at
> org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
> at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
> near the keyword 'RESTRICT'.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(Unknown Source)
> at
> org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:336)
> ... 21 more
> This is clearly because SQL Server 2005 does not recognise "RESTRICT" as a
> valid ON UPDATE value. Essentially the problem is that the methods
> writeForeignKeyOnDeleteAction and writeForeignKeyOnUpdateAction for the class
> automatically use the RESTRICT value while writing the SQL for the ALTER
> TABLE.
> So, there seem to be 2 parts to the solution here:
> 1. Writing the schema XML for ON UPDATE and ON DELETE for MSSQL Servers
> should probably default to "NO ACTION" rather than "RESTRICT"
> 2. When read in a schema if the onDelete and onUpdate values are "restrict"
> the MSSQLBuilder should Interpret these as "NO ACTION"
> I am new to the code base, but I would like to propose the following fixes
> for 1 and 2:
> * Override method in in JdbcModelReader: protected CascadeActionEnum
> convertAction(Short jdbcActionValue) in the subclass MSSqlModelReader so that
> it returns the value CascadeActionEnum.NONE by default for MSSsql
> * Change the methods writeForeignKeyOnDeleteAction and
> writeForeignKeyOnUpdateAction from private to protected so that MSSqlBuilder
> can override them to provide a correct implementation of "RESTRICT"(throw
> error or interpret to "NO ACTION"
> I notice that the methods writeForeignKeyOnDeleteAction and
> writeForeignKeyOnUpdateAction are private methods and so cannot be overriden.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
