Initially I configured the tests and ran them against MySQL 5.0 using to check if the behavior of my local repository.

Old tests run without any error/fail.

New tests:- TEST 1355, 1379, 31, 972 failed, TEST 2550 no mysql script, Test 30 OQLPassThrough tests fails, Test 87 fails(7 fails 8 errors)

Now I will setup and run these tests against MS SQL Server 2008 Express

Test 2550 can not be executed on mysql as it uses sequence key generator which is not supported by mysql. According to this it is excluded from test execution for mysql.

All other tests should work without failures. At least they do for me. Can you please telll me about mysql server and driver version you are using.

MySQL Server 5.0
JDBC - mysql-connector-java-5.0.4

Here is the exception stack trace attached as "ErrorMySqlNewCPACTFRun.txt" being thrown by Test 87, I am getting this particular type of error in most of the cases.

I'm using:

Server: 5.0.51a
Driver: mysql-connector-java-3.1.13-bin.jar

It seams that mysql again has problems with their drivers. I guess I have to test different drivers once again as I did some years ago. It may also be possible that we need to change something at the way we use them, especially at the update statement.

Ralf, Udai, can you please switch to one of the more recent 5.1.x drivers ? We have been using them for some time now in various client projects with e.g. Hibernate and Spring ORM, and have not had any problems. Btw, the number of the JDBC driver does not correspond to the server versions in any way.

Somehow I figured out that it was the classpath problem which was taking the 5.0.4 version from some local directory (which by mistake I did a long time ago and forgot [:p] ).

I run the tests against these JDBC drivers

1) mysql-connector-java-3.0.17-ga-bin.jar
2) mysql-connector-java-3.1.14-bin.jar
3) mysql-connector-java-5.0.4-bin.jar
4) mysql-connector-java-5.1.5-bin.jar

1) & 2) worked fine as expected i.e. 95/100. But, 2) & 3) were giving the same problem as discussed earlier(above).

ERROR:

earlier was : 1) & 2) worked fine as expected i.e. 95/100. But, 2) & 3) were giving the same problem as discussed earlier(above).

changed to: 1) & 4) worked fine as expected i.e. 95/100. But, 2) & 3) were giving the same problem as discussed earlier(above).

Created the issue http://jira.codehaus.org/browse/CASTOR-2633 to resolve this incompatibility problem of CPACTF with MySQL JDBC versions.

This patch contains the change which I made to run the NEW CPACTF tests
earlier MSSQL scripts were using drop if exist which gives error if the table doesn't exist
Changed the datatype from Double(which is not supported in MSSQL) to REAL in table test1355_golf_course_tees
This is the Description of some tests which failed or have some exception.

• Error with TEST 2527 while creating tables

  "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude
   the timestamp column, or insert a DEFAULT into the timestamp column.

• TEST 30,31,87 failed giving 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. while executing SELECT

• TEST 972 failed with this error message.

  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

Others runs without any problem.

Hi Udai,
as you may have expected I have some comments regarding your patch.

@TEST 1355: Have you tried 'DOUBLE PRECISION' instead of 'DOUBLE'? Having said that 'REAL' is also fine even if its resolution is smaller. Also add to the comment in test case that mssql should be enable when CASTOR-2584 is resolved.

@TEST 972: This error seams to be related to CASTOR-2221. I assigned this issue to you as you should be able to fix this. For the time being exclude the test and add a comment to the test class why it has been excluded. For example: Temporary disabled test for mssql until CASTOR-2221 is resolved.

@TEST 2527: Can you try to use 'DATETIME' instead of 'TIMESTAMP' datatype?

@TEST 2550: Add to comment that mssql is also excluded because it does not support sequence.

@TEST 30, 31, 87: Create a new issue 'Use ANSI outer join operators for Microsoft SQL engine'. Add comment to the new issue that this 3 tests relate to the issue. Exclude the issues from test execution for mssql and add comment to the test case that they have been excluded because of the this new issue.

@cpactf-conf.xml: You don't need to keep the old datasource definitions or comment the class change in the file.

@sql scripts: I though a bit about the sql scripts you provided. On one hand it is very good that you added the conditional execution of the scripts but on the other they will make another improvement we have in mind much harder. Let me explain on this. Later on we intend to use DDLGEN to generate create and drop scripts for cpactf. As mysql offers such a simple 'IF EXISTS' clause we use it for mysql but this iscomplicated or imposible for most of the other database engines. I would also prefer if we can omit 'USE' and 'GRANT' statements from the script and only include what's absolutely required. For example the script of TEST 1158:

USE [cpactf]
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[test1002_prod]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [test1002_prod](
	[id] [int] NOT NULL,
	[name] [varchar](200) NOT NULL
) ON [PRIMARY]
END
GO

INSERT [test1002_prod] ([id], [name]) VALUES (1, CONVERT(TEXT, N'This is the test object.'))

should better look like:

DROP TABLE [test1002_prod]
GO
CREATE TABLE [test1002_prod](
	[id] int NOT NULL,
	[name] varchar(200) NOT NULL
)
GO
INSERT [test1002_prod] ([id], [name]) VALUES (1, 'This is the test object.')
GO

The reason for this is, that it will be much easier to compare the output of DDLGEN with the simpler scripts. Having said that I am aware that all drop statements will cause errors when tables are not available. Same happens with create statements when there already is a table with the same name. According to the drop statements for constraints you could check if mssql offers something similar to oracle (DROP TABLE <name> CASCADE CONSTRAINTS).

I have to note that I'm aware that my comments are a bit picky and that I could have fixed most of the things mentioned in a shorter time then writing this comment. Background is that I think it will help you more if you know about all the things and fix them yourself. My idea is that it should enable you to provide patches with less of this small things next time, which I hope you will do .

Thank you for your great work.
Ralf

I have made the changes in SQL scripts as suggested by Ralf.

DROP TABLE <TNAME> CASCADE CONSTRAINT is not supported by Microsoft SQL Server. The only alternate is to drop the constraint FK before droping the table

There are two test which are failing due to issue CASTOR-2221 (TEST972, TEST1379)

Created CASTOR-2634 issue for non-ANSI outer join operators clearly mentioning the Test cases which are failing due to this.

Added appropriate comments which are being excluded.

PatchCastor2631v2.diff is attached after these changes

Committed last patch of Udai almost as is.