In the process of writing my data type tester for ASA I ran into this issue.
Here is the SQL I am sending to the database:
select CAST(1 AS BIT) as "bit"
, CAST(10 AS TINYINT) as "tinyint"
, CAST(100 AS SMALLINT) as "smallint"
, CAST(1000 AS INTEGER) as "integer"
, CAST(10000 AS UNSIGNED INTEGER) as "unsigned_integer"
, CAST(100000 AS BIGINT) as "bigint"
, CAST(1000000 AS UNSIGNED BIGINT) as "unsigned_bigint"
, CAST('char' AS CHAR(4)) as "char"
, CAST('varchar' AS VARCHAR(7)) as "varchar"
, CAST('long varchar' AS LONG VARCHAR) as "long_varchar"
, CAST('binary' AS BINARY(6)) as "binary"
, CAST('varbinary' AS VARBINARY(9)) as "varbinary"
, CAST('long binary' AS LONG BINARY) as "long_binary"
, CAST('xml' AS XML) as "xml"
, CAST('1900/1/1' AS DATE) as "date"
, CAST('1900/1/1 13:02:55' AS DATETIME) as "datetime"
, CAST('1901/1/1' AS TIMESTAMP) as "timestamp"
, CAST('1902/1/1' AS SMALLDATETIME) as "smalldatetime"
, CAST('300.12' AS NUMERIC(5,2)) as "numeric"
, CAST('301.13' AS DECIMAL(5,2)) as "decimal"
, CAST('402.14' AS FLOAT) as "float"
, CAST('403.15' AS REAL) as "real"
, CAST('404.15' AS DOUBLE) as "double"
from dummy
Now the results I get back are:
[sqlunit] <col id="12" name="c12" type="CLOB">md5:7094a93cc83901dd6cb4ce93f12cf921</col>
[sqlunit] <col id="13" name="c13" type="BINARY">md5:9d7183f16acce70658f686ae7f1a4d20</col>
[sqlunit] <col id="14" name="c14" type="VARBINARY">md5:48d3ba792850178dbffe63ab2b0dfac3</col>
[sqlunit] <col id="15" name="c15" type="BLOB">md5:254d53fd270c1c1adac16596c9279440</col>
[sqlunit] <col id="16" name="c16" type="CLOB">md5:0f635d0e0f3874fff8b581c132e6c7a7</col>
Is there anyway to turn off the md5 and just compare the straight strings coming back?
I thought we addressed this a little while back, but my searches were fruitless.
Thanks,
Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, that is what the new custom type system is supposed to address, among other things.
I counted the lines in the SQL and came up with these:
col 12: CAST('varbinary' AS VARBINARY(9)) as "varbinary"
The database returns this as a CLOB, and SQLUnit tries to use the ClobType, which does an md5 conversion. You could override the mapping to be StringType which should not convert to MD5.
col 13: CAST('long binary' AS LONG BINARY) as "long_binary"
The database returns this as a BINARY, which is mapped to ByteArrayType, which also does an MD5 conversion. Not sure what you would override this to, perhaps have a new type implementation called NonDigestingByteArrayType which will return the new String(byte[]) for it.
col 14: CAST('xml' AS XML) as "xml"
The database returns this as a VARBINARY, which is mapped to BinaryType, which gets an InputStream and digests it. We could perhaps supply yet another NonDigestingBinaryType which will return the new String(byte[]) from it.
col 15: CAST('1900/1/1' AS DATE) as "date"
The database returns this as a BLOB? A little surprising, unless the SQL type code used by ASA for DATE is the same as that used by java.sql.Type for BLOB. The solution would be to override the DATE like so:
adaptive_server_enterprise.DATE.class = net.sourceforge.sqlunit.types.DateType
adaptive_server_enterprise.DATE.type = 2004
col 16: CAST('1900/1/1 13:02:55' AS DATETIME) as "datetime"
The database returns this as a CLOB? Again a little surprising, unless the SQL type code used by ASA is the same as that used by CLOB in java.sql.Types. We could do something along these lines:
adaptive_server_anywhere.DATETIME.class =
net.sourceforge.sqlunit.types.TimestampType
adaptive_server_anywhere.DATETIME.type = 2005
Actually if you could send me the type codes for each of these datatypes returned by ASA, that would give me more info to go on. I can understand the second and third cases, but the first one seems strange, and so does 4 and 5.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In the process of writing my data type tester for ASA I ran into this issue.
Here is the SQL I am sending to the database:
select CAST(1 AS BIT) as "bit"
, CAST(10 AS TINYINT) as "tinyint"
, CAST(100 AS SMALLINT) as "smallint"
, CAST(1000 AS INTEGER) as "integer"
, CAST(10000 AS UNSIGNED INTEGER) as "unsigned_integer"
, CAST(100000 AS BIGINT) as "bigint"
, CAST(1000000 AS UNSIGNED BIGINT) as "unsigned_bigint"
, CAST('char' AS CHAR(4)) as "char"
, CAST('varchar' AS VARCHAR(7)) as "varchar"
, CAST('long varchar' AS LONG VARCHAR) as "long_varchar"
, CAST('binary' AS BINARY(6)) as "binary"
, CAST('varbinary' AS VARBINARY(9)) as "varbinary"
, CAST('long binary' AS LONG BINARY) as "long_binary"
, CAST('xml' AS XML) as "xml"
, CAST('1900/1/1' AS DATE) as "date"
, CAST('1900/1/1 13:02:55' AS DATETIME) as "datetime"
, CAST('1901/1/1' AS TIMESTAMP) as "timestamp"
, CAST('1902/1/1' AS SMALLDATETIME) as "smalldatetime"
, CAST('300.12' AS NUMERIC(5,2)) as "numeric"
, CAST('301.13' AS DECIMAL(5,2)) as "decimal"
, CAST('402.14' AS FLOAT) as "float"
, CAST('403.15' AS REAL) as "real"
, CAST('404.15' AS DOUBLE) as "double"
from dummy
Now the results I get back are:
[sqlunit] <col id="12" name="c12" type="CLOB">md5:7094a93cc83901dd6cb4ce93f12cf921</col>
[sqlunit] <col id="13" name="c13" type="BINARY">md5:9d7183f16acce70658f686ae7f1a4d20</col>
[sqlunit] <col id="14" name="c14" type="VARBINARY">md5:48d3ba792850178dbffe63ab2b0dfac3</col>
[sqlunit] <col id="15" name="c15" type="BLOB">md5:254d53fd270c1c1adac16596c9279440</col>
[sqlunit] <col id="16" name="c16" type="CLOB">md5:0f635d0e0f3874fff8b581c132e6c7a7</col>
Is there anyway to turn off the md5 and just compare the straight strings coming back?
I thought we addressed this a little while back, but my searches were fruitless.
Thanks,
Dave
Yes, that is what the new custom type system is supposed to address, among other things.
I counted the lines in the SQL and came up with these:
col 12: CAST('varbinary' AS VARBINARY(9)) as "varbinary"
The database returns this as a CLOB, and SQLUnit tries to use the ClobType, which does an md5 conversion. You could override the mapping to be StringType which should not convert to MD5.
col 13: CAST('long binary' AS LONG BINARY) as "long_binary"
The database returns this as a BINARY, which is mapped to ByteArrayType, which also does an MD5 conversion. Not sure what you would override this to, perhaps have a new type implementation called NonDigestingByteArrayType which will return the new String(byte[]) for it.
col 14: CAST('xml' AS XML) as "xml"
The database returns this as a VARBINARY, which is mapped to BinaryType, which gets an InputStream and digests it. We could perhaps supply yet another NonDigestingBinaryType which will return the new String(byte[]) from it.
col 15: CAST('1900/1/1' AS DATE) as "date"
The database returns this as a BLOB? A little surprising, unless the SQL type code used by ASA for DATE is the same as that used by java.sql.Type for BLOB. The solution would be to override the DATE like so:
adaptive_server_enterprise.DATE.class = net.sourceforge.sqlunit.types.DateType
adaptive_server_enterprise.DATE.type = 2004
col 16: CAST('1900/1/1 13:02:55' AS DATETIME) as "datetime"
The database returns this as a CLOB? Again a little surprising, unless the SQL type code used by ASA is the same as that used by CLOB in java.sql.Types. We could do something along these lines:
adaptive_server_anywhere.DATETIME.class =
net.sourceforge.sqlunit.types.TimestampType
adaptive_server_anywhere.DATETIME.type = 2005
Actually if you could send me the type codes for each of these datatypes returned by ASA, that would give me more info to go on. I can understand the second and third cases, but the first one seems strange, and so does 4 and 5.
-sujit
You are right, I have the test passing now without errors. I had to choose some unusual data types to get the test to pass.
I think that is a bug in our driver, so I will pursue it from this end.
Thanks,
Dave
Forget this thread I see SQLUnit is doing what I wanted it to.
It shows the md5 string, but it converts each string to a md5. So the test results that I provide are still the strings that I wanted to.
Sorry for the noise.
Dave