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.
