You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Frank Hampshire (JIRA)" <ji...@apache.org> on 2008/08/04 08:03:44 UTC
[jira] Commented: (DBUTILS-45) ON UPDATE, ON DELETE errors for MS
Sql Server - does not implement default "RESTRICT"
[ https://issues.apache.org/jira/browse/DBUTILS-45?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12619421#action_12619421 ]
Frank Hampshire commented on DBUTILS-45:
----------------------------------------
Sorry about this. This Bug was meant to be raised Against DDLUtils, NOT DBUTILS
Please disregard
> 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.