[ 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.