I'm using SQLUnit to run comparisons against two different databases. Diff works really well, except it also checks the type data returned. I'm in a positon where I don't care.
e.g. I'm comparing Sybase ASE against IQ.
Row count in ASE is returned as an integer, but in IQ this is bigint.
comparing the statement
<b>select 'hello'</b>
also is erroneous because IQ returns this as CHAR but ASE returns it as VARCHAR.
Basically the question is - is it possible to compare values without depending on what the datatype returned is?
Many thanks for your help
Chris
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Changing the core code:
The default assert is "equals", which resolves to a series of sub-asserts, one of which is "resultset-equal". We can replace the "resultset-equal" assert with "resultset-equal-typeless" and build an assert method which does the same thing as the assertResultSetEqual but ignores the type.
Extending using a Matcher:
Another option would be to create a matcher which ignores the type but only compares the String values returned from each datatype.
I will have more info for you once I get to look at the code again this evening.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You say:
We can replace the "resultset-equal" assert with "resultset-equal-typeless" and build an assert method which does the same thing as the assertResultSetEqual but ignores the type.
I think this is a good idea.
As it stands now, there are 3 ways to connect to ASA databases:
iAnywhere JDBC driver (type 2)
Sun JDBC-ODBC bridge
Sybase jConnect driver (type 4)
The types resolve slightly differently between these drivers. So my tests have to be driven for a particular driver, rather than the data itself.
Having the typeless check would make it more vanilla. For most people it is the data that is important, not the type anyway (IMHO).
Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I actually went with the Matcher approach (see my response to Chris), but the point you make is valid, so I will implement the resultset-equals-typeless assertion as well.
I will update this thread when I have done that.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-09-15
Hi,
Thanks for your help. In the meantime I have created my own matcher which is a combination of your PercentageRangeMatcher and a simple equals matcher (because I can't be bothered specifying which results I'm expecting are going to be floats). In addition, I was having trouble because my differences were very small (10^-8) and float wasn't big enough to hold enough of the decimal digits to spot the difference. I've converted all the floats to doubles.
However, because of the firewall at work (either that or I'm being thick) I can't access your CVS to stick it in. Are you interested in it? If you are, is the another way I can get code to you? If you're not interested in it that's fine too...
Chris
PS - I notice that if you use a matcher it is impossible to do any matching on the type as that information is stuck in the column object. This is fine for me as I don't care on the type, but may be a problem in the future?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I wrote one too, last evening, but that just did a String equal() and returned the result. But yours sounds more detailed, so definitely, I am interested in it. You can send it to me either as a patch (which allows you to attach the file) or via email to my sourceforge address, and I can put this in to CVS for you. CVS is actually writeable only to project team members, although you can download code from it using :pserver:.
And you are correct that matchers only allow you to match values and not types. Not sure if that was a concious choice, but it seems to work out ok, since matchers can only be used with diff tags, and diffs are typically (though not always) used across databases or across different database servers, where type issues like you described tend to muddy the results.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I addressed Dave's requirement by passing the columns to the TypelessMatcher implicitly. Ok, I guess I kind of cheated, but it makes more sense to pass off work to things that already exist, rather than writing it again yourself. You can access this functionality by setting the assert attribute of the test tag to "resultset-values-equal". Its a bit like a special convenience method for what is described below.
I have also added the capability to use zero or more embedded match tags within a test tag, similar to the diff tag. I realized that since the refactoring, the problems associated with passing a matcher to the test tag do not exist anymore, so it is easy enough to allow for its use in the test tag now. Matchers can be chained, so you may want the column values to pass through multiple matchers before being certified as matched.
The error message you will see if the match fails is the expected value !~ returned value followed by the matcher class name in parens. This is the same with the implicit case as well.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I'm using SQLUnit to run comparisons against two different databases. Diff works really well, except it also checks the type data returned. I'm in a positon where I don't care.
e.g. I'm comparing Sybase ASE against IQ.
Row count in ASE is returned as an integer, but in IQ this is bigint.
comparing the statement
<b>select 'hello'</b>
also is erroneous because IQ returns this as CHAR but ASE returns it as VARCHAR.
Basically the question is - is it possible to compare values without depending on what the datatype returned is?
Many thanks for your help
Chris
Hi Chris,
There are two ways to do this.
Changing the core code:
The default assert is "equals", which resolves to a series of sub-asserts, one of which is "resultset-equal". We can replace the "resultset-equal" assert with "resultset-equal-typeless" and build an assert method which does the same thing as the assertResultSetEqual but ignores the type.
Extending using a Matcher:
Another option would be to create a matcher which ignores the type but only compares the String values returned from each datatype.
I will have more info for you once I get to look at the code again this evening.
-sujit
You say:
We can replace the "resultset-equal" assert with "resultset-equal-typeless" and build an assert method which does the same thing as the assertResultSetEqual but ignores the type.
I think this is a good idea.
As it stands now, there are 3 ways to connect to ASA databases:
iAnywhere JDBC driver (type 2)
Sun JDBC-ODBC bridge
Sybase jConnect driver (type 4)
The types resolve slightly differently between these drivers. So my tests have to be driven for a particular driver, rather than the data itself.
Having the typeless check would make it more vanilla. For most people it is the data that is important, not the type anyway (IMHO).
Dave
Hi Dave,
I actually went with the Matcher approach (see my response to Chris), but the point you make is valid, so I will implement the resultset-equals-typeless assertion as well.
I will update this thread when I have done that.
-sujit
Hi,
Thanks for your help. In the meantime I have created my own matcher which is a combination of your PercentageRangeMatcher and a simple equals matcher (because I can't be bothered specifying which results I'm expecting are going to be floats). In addition, I was having trouble because my differences were very small (10^-8) and float wasn't big enough to hold enough of the decimal digits to spot the difference. I've converted all the floats to doubles.
However, because of the firewall at work (either that or I'm being thick) I can't access your CVS to stick it in. Are you interested in it? If you are, is the another way I can get code to you? If you're not interested in it that's fine too...
Chris
PS - I notice that if you use a matcher it is impossible to do any matching on the type as that information is stuck in the column object. This is fine for me as I don't care on the type, but may be a problem in the future?
Hi Chris,
I wrote one too, last evening, but that just did a String equal() and returned the result. But yours sounds more detailed, so definitely, I am interested in it. You can send it to me either as a patch (which allows you to attach the file) or via email to my sourceforge address, and I can put this in to CVS for you. CVS is actually writeable only to project team members, although you can download code from it using :pserver:.
And you are correct that matchers only allow you to match values and not types. Not sure if that was a concious choice, but it seems to work out ok, since matchers can only be used with diff tags, and diffs are typically (though not always) used across databases or across different database servers, where type issues like you described tend to muddy the results.
-sujit
I addressed Dave's requirement by passing the columns to the TypelessMatcher implicitly. Ok, I guess I kind of cheated, but it makes more sense to pass off work to things that already exist, rather than writing it again yourself. You can access this functionality by setting the assert attribute of the test tag to "resultset-values-equal". Its a bit like a special convenience method for what is described below.
I have also added the capability to use zero or more embedded match tags within a test tag, similar to the diff tag. I realized that since the refactoring, the problems associated with passing a matcher to the test tag do not exist anymore, so it is easy enough to allow for its use in the test tag now. Matchers can be chained, so you may want the column values to pass through multiple matchers before being certified as matched.
The error message you will see if the match fails is the expected value !~ returned value followed by the matcher class name in parens. This is the same with the implicit case as well.
-sujit