[#CASTOR-2221] HighLowKeyGenerator throws an Exception on SQL Server 2005 - jira.codehaus.org

castor

HighLowKeyGenerator throws an Exception on SQL Server 2005

Created: 30/Nov/07 02:43 PM Updated: 30/Dec/09 04:21 AM Resolved: 18/Mar/09 05:40 PM

Component/s:

JDO

Affects Version/s:

1.1.2.1

Fix Version/s:

1.3.1

Time Tracking:

Not Specified

File Attachments:

patchCASTOR-2221.txt (0.7 kB)

Environment:

Windows XP, SQL Server 2005 Express, Java 1.6.0_03, jTDS 1.2 (JDBC Driver)

Issue Links:

Related

This issue relates to:

~~CASTOR-2477~~ Let KeyGenerator taken over role of SQLSatementCreate

Description

« Hide

I have an application that runs fine on SQL Server 2000. When I try to run it on SQL Server 2005 Express the following Exception is thrown:

org.exolab.castor.jdo.PersistenceException: SQL exception in the key generator 
org.exolab.castor.jdo.keygen.HighLowKeyGenerator: java.sql.SQLException: Incorrect syntax near 'holdlock'. 
If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server
Books Online for proper syntax..: Incorrect syntax near 'holdlock'. If this is intended as a part of a table hint, A
WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. 
        at org.exolab.castor.jdo.keygen.HighLowKeyGenerator.generateKey(HighLowKeyGenerator.java:361) 
        at org.exolab.castor.jdo.engine.SQLStatementCreate.generateKey(SQLStatementCreate.java:392) 
        at org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLStatementCreate.java:193) 
        at org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:314) 
        at org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:691) 
        at org.exolab.castor.persist.LockEngine.create(LockEngine.java:547) 
        at org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated(AbstractTransactionContext.java:816) 
        at org.castor.persist.AbstractTransactionContext.create(AbstractTransactionContext.java:776) 
        at org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractDatabaseImpl.java:313)

Sort Order:

[ Permalink | « Hide ]

Ralf Joachim added a comment - 30/Nov/07 07:24 PM

Searching online docs of SQL server 2005 I found that the syntax should be 'WITH (HOLDLOCK)'. The problem is that I could not find out if this syntax will also work with older versions of SQL server. In addition I found a remark that the HOLDLOCK and SERIALIZABLE hints may be removed in future versions of SQL server but they didn't suggest an alternative.

See:
http://technet.microsoft.com/de-de/library/ms174335.aspx
http://technet.microsoft.com/de-de/library/ms187373.aspx

Brian, are you able to help me with the open questions.

Having said that I will also need your help for testing as I do not have access to SQL server.

[ Permalink | « Hide ]

Brian Schlining added a comment - 01/Dec/07 06:59 PM

Ralf,

Yes I can help with the SQL Server testing. If you happen to have examples of the SQL being executed, could you send them to me? If not let me know and I'll dig through the code to piece it together and run the 2 different SQL calls on both SQL 2000 and 2005.

[ Permalink | « Hide ]

Ralf Joachim added a comment - 03/Dec/07 10:36 AM

Will try to post an example SQL statement for you to test.

[ Permalink | « Hide ]

Ralf Joachim added a comment - 31/Jan/09 05:41 PM

Working at ~~CASTOR-2631~~ Udai recognized that TEST972 of new cpactf seams to be failing according to this issue. The error message is:

Incorrect syntax near 'holdlock'. If this is intended as a part of a table hint, A WITH keyword and 
parenthesis are now required. See SQL Server Books Online for proper syntax. while executing SELECT

Test is excluded at the moment and should be enabled when this issue got resolved.

[ Permalink | « Hide ]

Udai Gupta added a comment - 01/Feb/09 11:46 AM

There are two tests which are failing due this issue

TEST972
TEST1379
I will exclude SQL_SERVER engine from these tests until this issue get resolved.

[ Permalink | « Hide ]

Ralf Joachim added a comment - 17/Mar/09 12:09 PM

This have to be fixed in SQLServerQueryExpression.getStatement().

[ Permalink | « Hide ]

Udai Gupta added a comment - 18/Mar/09 10:34 AM

The above two tests don't give 'holdlock' error after the patch.

[ Permalink | « Hide ]

Ralf Joachim added a comment - 18/Mar/09 11:25 AM

Thanks for the patch and for testing things but I have to reopen the issue as the changes have to be committed before we set the issue to resolved.

[ Permalink | « Hide ]

Ralf Joachim added a comment - 18/Mar/09 11:34 AM

One more question. Why does the patch not enable test972 and test1379 if they work now?

[ Permalink | « Hide ]

Udai Gupta added a comment - 18/Mar/09 11:38 AM

I haven't enabled them because those are facing ~~CASTOR-2648~~ issue. I will make them enable in ~~CASTOR-2648~~ patch.