commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Frank Hampshire (JIRA)" <j...@apache.org>
Subject [jira] Closed: (DBUTILS-45) ON UPDATE, ON DELETE errors for MS Sql Server - does not implement default "RESTRICT"
Date Mon, 04 Aug 2008 06:05:44 GMT

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


Mime
View raw message