castor

Use ANSI outer join operators in query for Microsoft SQL engine

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.3 rc1
  • Fix Version/s: 1.3.1
  • Component/s: JDO queries
  • Labels:
    None
  • Number of attachments :
    3

Description

While executing new CPACTF based Tests some tests are throwing exceptions, which are

  • In Test87 Test case TestExtended
  • In Test31 Test case TestPersistanceWithExtends
  • In Test30 Test case TestOQLCondition
  • In Test1196 Test case TestLongTransaction(changeComplexBidirectional, removeComplexBidirectional)
  • Test2527

With this error message 

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, 
please set the compatibility level for current  database to 80, using the SET COMPATIBILITY_LEVEL option of 
ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT 
OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be 
supported even in backward-compatibility modes.

Attachments

  1. Text File
    patch-C2648-20090318.txt
    18/Mar/09 6:16 PM
    21 kB
    Ralf Joachim
  2. Text File
    patchCASTOR-2648v1.txt
    18/Mar/09 4:43 PM
    20 kB
    Udai Gupta
  3. Text File
    patchCASTOR-2648v1+Addditional.txt
    19/Mar/09 12:49 AM
    2 kB
    Udai Gupta

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Ralf Joachim added a comment -

SQLServerQueryExpression.getStatement() overwrites JDBCQueryExpression.getStatement() to produce SQL statements with Miocrosoft specific syntax. A peek at both methods should that SSQE uses some MS specific syntax elements but uses wrong join syntax. On the other hand JQE seams to produce queries with the join syntax MS requires.

Show
Ralf Joachim added a comment - SQLServerQueryExpression.getStatement() overwrites JDBCQueryExpression.getStatement() to produce SQL statements with Miocrosoft specific syntax. A peek at both methods should that SSQE uses some MS specific syntax elements but uses wrong join syntax. On the other hand JQE seams to produce queries with the join syntax MS requires.
Hide
Permalink
Udai Gupta added a comment -

patchCASTOR-2648v1.txt : It resolves the non ANSI outer join and the holdlock issue(CASTOR-2221). It make test 1073, 1217, 241 able to run with mssql.

After this patch 108/118 test were running successfully.
1 failure at org.castor.cpa.test.test2527.LogExceptionEntry due to...

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from varchar to CLOB is unsupported.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
	at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:893)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:1832)
	at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:165)
	at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:113)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1835)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1820)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getClob(SQLServerResultSet.java:2306)
	at org.castor.jdo.engine.SQLTypeInfos.getValue(SQLTypeInfos.java:274)
	at org.exolab.castor.jdo.engine.SQLStatementLoad.executeStatement(SQLStatementLoad.java:375)
	at org.exolab.castor.jdo.engine.SQLEngine.load(SQLEngine.java:342)
	at org.exolab.castor.persist.ClassMolder.load(ClassMolder.java:524)
	at org.exolab.castor.persist.LockEngine.load(LockEngine.java:440)
Show
Udai Gupta added a comment - patchCASTOR-2648v1.txt : It resolves the non ANSI outer join and the holdlock issue(CASTOR-2221). It make test 1073, 1217, 241 able to run with mssql. After this patch 108/118 test were running successfully. 1 failure at org.castor.cpa.test.test2527.LogExceptionEntry due to... 
com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from varchar to CLOB is unsupported.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
	at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:893)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:1832)
	at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:165)
	at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:113)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1835)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1820)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getClob(SQLServerResultSet.java:2306)
	at org.castor.jdo.engine.SQLTypeInfos.getValue(SQLTypeInfos.java:274)
	at org.exolab.castor.jdo.engine.SQLStatementLoad.executeStatement(SQLStatementLoad.java:375)
	at org.exolab.castor.jdo.engine.SQLEngine.load(SQLEngine.java:342)
	at org.exolab.castor.persist.ClassMolder.load(ClassMolder.java:524)
	at org.exolab.castor.persist.LockEngine.load(LockEngine.java:440)
Hide
Permalink
Ralf Joachim added a comment -

Patch looks very good. I only created a new patch against SVN head after commit of CASTOR-2221 and addition of release notes. There are 2 things I wonder about:

1. You said that test2527 fails according to varchar - clob conversion but this test is still enabled for sql server. If it fails shouln't it be commented out? Before doing so could you please test if this test passes if you change column type of STACKTRACE column in TEST2527_LOG_EXCEPTION table from VARCHAR(2000) to CLOB? If this works we change the column type to CLOB and leave the test enabled.

2. Could you please also try to enable test303 which has been added since your first tests against SQL server? I guess you did not care on this test as it has not been mentioned in the comment to this issue.

Show
Ralf Joachim added a comment - Patch looks very good. I only created a new patch against SVN head after commit of CASTOR-2221 and addition of release notes. There are 2 things I wonder about: 1. You said that test2527 fails according to varchar - clob conversion but this test is still enabled for sql server. If it fails shouln't it be commented out? Before doing so could you please test if this test passes if you change column type of STACKTRACE column in TEST2527_LOG_EXCEPTION table from VARCHAR(2000) to CLOB? If this works we change the column type to CLOB and leave the test enabled. 2. Could you please also try to enable test303 which has been added since your first tests against SQL server? I guess you did not care on this test as it has not been mentioned in the comment to this issue.
Hide
Permalink
Udai Gupta added a comment -

patchCASTOR-2648v1+Addditional.txt:

Enables Test 303 which ran successfully against MSSQL. In Test2527 : Changed VARCHAR(2000) to TEXT as suggested.

LAST run of CPACTF against MSSQL 2008.
113/118 tests are running successfully, 0 Error, 0 Failure

Show
Udai Gupta added a comment - patchCASTOR-2648v1+Addditional.txt: Enables Test 303 which ran successfully against MSSQL. In Test2527 : Changed VARCHAR(2000) to TEXT as suggested. LAST run of CPACTF against MSSQL 2008. 113/118 tests are running successfully, 0 Error, 0 Failure
Hide
Permalink
Ralf Joachim added a comment -

Commited both patches together as they are.

Show
Ralf Joachim added a comment - Commited both patches together as they are.

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Codehaus. Try JIRA - bug tracking software for your team.