Am I right in assuming that sqlunit does not support the Unicode types (NVARCHAR, NCHAR, NTEXT)?
Whenever I encounter an NVARCHAR in my result set, I get an "Type -9 is not a valid SQL type" error. NTEXT is even worse-- it outputs the string "null" and the test aborts.
Any ideas or workarounds?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
What is the java.sql.Types for this? I can make it support it quite easily, I think, but the databases I was working with at the time did not have these. I have done something for Oracle which adds on new types that are Oracle specific, so if you tell me which database you are using and the type ids, I can build something for your database along these lines.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have looked into this more, and found that the issue here is that MS is using some non-standard codes to represent their Unicode types. What's worse, there's some ID overlap between the MS extensions and the Oracle extensions, which means that it will not be possible to have one list of types that handles both platforms. The codes SQL 2000 is using are:
NCHAR = -8 (same as ROWID in Oracle)
NVARCHAR = -9
NTEXT = -10 (same as CURSOR in Oracle)
I have temporarily hacked up sqlunit to recognize these new codes, and am in the process of getting my same Unicode tests running against a couple of other platforms to see what codes they are using for these types. Once I have this figured out we can discuss what it will take to add this support...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks fot the codes. I was leaning towards a bunch of type classes who know what they are, etc. Its a fair amount of work, but it will make the code cleaner and more manageable by removing the need for the giant switch statements in SqlTypeUtils.
We may need to have to specify a vendor attribute in the test tags to work with non-standard codes, and have some methods in these type classes that do the translation behind the scenes.
Let me know what you think or if you have another approach in mind.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
That sounds like a good approach, but I agree it's a lot of work. Definitely worth it in the long run, however.
Loading the extensions seems like it should be pretty straightforward-- we can extend the <sqlunit> task to provide a new attribute to indicate what extensions should be loaded, a la:
<sqlunit useExtendedTypes="MSSQL2K|ORACLE">
This would have the side effect of breaking existing oracle tests, however-- not good. Perhaps default to use Oracle extensions if not otherwise specified?
I'd be happy to help code/test this for you if you need it...
ralph
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks Ralph, I may need your help once I have set up the infrastructure for this. Basically, this is what I have in mind:
An IType interface which extends Comparable (for Jorge's sorting feature) and has the following methods definitions:
IType parse(String);
String format(IType);
Each SQL datatype will have to provide an implementation for this interface.
The mapping will be controlled by a types.properties with the following key value pairs (for example):
INTEGER.class = net.sf.sqlunit.types.IntegerType
INTEGER.type = java.sql.Types.INTEGER
NUMERIC.class = net.sf.sqlunit.types.NumericType
NUMERIC.type = java.sql.Types.NUMERIC
which can be overriden by vendor specific definitions in the same file:
postgresql.NUMERIC.class = net.sf.sqlunit.types.BigDecimalType
or even:
oracle.CURSOR.class =
net.sf.sqlunit.types.CursorType
oracle.CURSOR.type = -10
or in the file usertypes.properties which needs to be in the classpath and can be supplied by the user to override the definitions in the types.properties file, so for example in your case, your usertypes.properties could contain:
and that would automatically override whatever there was for that type.
This would allow us to make quick site specific fixes without having to update the code and recompile. We can then fold in the usertypes.properties into types.properties in the next release.
Let me know what you think, and if you have other ideas to enhance this mechanism.
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This looks really nice. I like basing it on prop files instead of hardcoding extensions, since this avoids the issues with having to rework the <sqlunit> element to specify the extensions to use. I assume that if there are no extensions specified, we will fall back to the standard java.sql type definitions?
The only change I might suggest is something that allows you to load extensions explicitly, rather than implicitly by DB type. This way I can define a single test suite that executes across DB types (I do this now with connections, and it's pretty sweet for my purposes).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, it should fall back to the standard java.sql.Types if there are no extensions specified.
And with what I am suggesting, you could also define a type in the col element as something like:
<col id="1" name="c1" type="NCHAR">foo</col>
as well as:
<col id="1" name="c1" type="sqlserver.NCHAR">foo</col>
Is that what you wanted, or did you want to bypass the usertypes.properties altogether and put in something like this:
<col id="1" name="c1" type="JAVA_OBJECT" class="com.mycompany.someapplication.types.MyType">foo</col>
I can do the second one as well too, not sure if that would be used too much though.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Also, someone pointed out that I could use the DatabaseMetaData to autodetect which database SQLUnit is connecting to. Just something to keep in mind, although then I will have to have the exact same names in the properties files.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Loading extensions based on the metadata would do what I need. My test environment makes it tough for me to load a properties file (classpath is dynamic, and directories come and go), so having the right extensions appear automatically would be a big help.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for sending me the code. I have based the code that is currently in CVS on it, although I have made some changes to support the sortability feature I mentioned earlier. I am going to be working on that over the next week or so.
The code is tested against my mock database (overriding the CURSOR with oracle.CURSOR) and against my PostgresSQL database, testing that the NUMERIC override to BigDecimal for PostgreSQL worked as expected (without specifying a server-name attribute in the connection to see if it will be picked up automatically).
If you could run the code against your test cases and see if they work, that would be really helpful.
I will write the docs as I find some time, but the net.sourceforge.sqlunit.types.properties contain mappings for pre-defined and some extension types, including the NCHAR, NVARCHAR and NTEXT that you told me about.
The name of the database server is derived from:
Connection.getMetaData().getDatabaseProductName()
Not sure if microsoft is what you get back from SQL Server, you may want to check that. (There is some code in test/java/VendorTest.java that you could use for that). You should also be able to just specify this as an attribute of your connection element as @server-name.
Let me know if you find anything.
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
We're getting pretty close-- Once I figured out that the ant Sqlunit task had moved, I was able to get everything up and running again. There were a couple of changes needed, however...
The big problem was that the server name returned by the jdbc driver I am using (com.inet.tds.TDSDriver) is "Microsoft Sql Server". The spaces in the name mean that it is not usable as a properties key. I had to hack up the server name handing in ConnectionRegistry to replace the spaces with underscores in order to get it to work.
The good news is that once I did that, I was able to change the types.properties to use the new name and everything worked really slick-- much easier than before.
I have not yet been able to test with my Oracle international DB yet (need to find my DBA), but it should be pretty straightforward.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry about moving the SqlUnitTask, had to do this to make things look more tied together. There are some more changes, I actually moved some of the files around yesterday night, the interfaces and the factories, since I wanted to minimize impact to existing external code, but forgot to change the package names. I will check them in in a few minutes.
I will also do the additional transformation on the database server name also when I check in the code.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Not a problem-- it just took me a few minutes to figure out what happened...
I was also able to run through my same set of tests against Oracle. Looks pretty good, but I did uncover another minor issue with the new type stuff. Oracle uses BigDecimal for NUMERIC, so you need to do the same sort of override that you do for postgres in the types.properties file.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
There is a JUnit test that tries the datatypes against a mock database. Its here:
/sqlunit/test/java/TypesTest.java
I have also tested to see that it runs against the existing SQLUnit tests (without changes) on my postgresql database. Ralph has tested against MS SQL Server (which should be similar to ASA?) and Oracle.
There are no tests for bound conditions, my assumption is that the database should know what its doing when sending back various data with the corresponding sql types, so I am simply mapping the type classes against the sql codes.
If you could update the SQLUnit tests for ASA that actually do these tests then it would be great, since we could use this as a template for generating tests for other databases as well.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
ant "junit-test" -Dtest.class=java
ant junit-test -Dtest.class=java
I always get messages:
Ant could not find the task or a class this task relies upon.
This is common and has a number of causes; the usual
solutions are to read the manual pages then download and
install needed JAR files, or fix the build file:
- You have misspelt 'junit'.
Fix: check your spelling.
- The task needs an external JAR file to execute
and this is not found at the right place in the classpath.
Fix: check the documentation for dependencies.
Fix: declare the task.
- The task is an Ant optional task and optional.jar is absent
Fix: look for optional.jar in ANT_HOME/lib, download if needed
- The task was not built into optional.jar as dependent
libraries were not found at build time.
Fix: look in the JAR to verify, then rebuild with the needed
libraries, or download a release version from apache.org
- The build file was written for a later version of Ant
Fix: upgrade to at least the latest release version of Ant
- The task is not an Ant core or optional task
and needs to be declared using <taskdef>.
Anyway, somehow I have to tell it connection information on how to connect to ASA, and so on, and I didnt see where I enter that.
Any tips...
Thanks,
Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
To run the TypesTest, you will need to do this:
ant junit-test -Dtest.class=net.sourceforge.sqlunit.test.TypesTest
You would typically instantiate a connection directly in the JUnit test, see the VendorNameTest.java for an example. Since you do not want to open and destroy the connection over and over, you may consider doing this using the TestSetup decorator as used in TypesTest.java.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hmm, perhaps I am a bit dense.
But in VendorNameTest.java ( $Id: VendorNameTest.java,v 1.1) I only see this method that has anything remotely similar to connection information:
public void testWhatItLooksLike() throws Exception {
It does not appear to make a connection for the test suite.
In TestTypes.java ($Id: TypesTest.java,v 1.1),
public void setUp() throws Exception {
// :TODO: use TestSetup decorator here to have this done
// only once.
TypeMapper mapper = TypeMapper.getTypeMapper();
}
This does not seem to perform any form of connection. So where does this suite actually make the connection to the database?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I was referring to the code in VendorNameTest.java:
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/sqlunitdb",
"defaultuser", "defaultuser");
which is the generic pattern for getting a Connection using JDBC.
Your test will probably consist of running a SELECT from a table which has columns of all kinds of datatypes defined in it, and then accessing each one by one. Now that I think about this some more, you probably dont even need to do a JUnit test, simply create a SQLUnit test case with this kind of SELECT and then verify that all the values came back as expected.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Am I right in assuming that sqlunit does not support the Unicode types (NVARCHAR, NCHAR, NTEXT)?
Whenever I encounter an NVARCHAR in my result set, I get an "Type -9 is not a valid SQL type" error. NTEXT is even worse-- it outputs the string "null" and the test aborts.
Any ideas or workarounds?
What is the java.sql.Types for this? I can make it support it quite easily, I think, but the databases I was working with at the time did not have these. I have done something for Oracle which adds on new types that are Oracle specific, so if you tell me which database you are using and the type ids, I can build something for your database along these lines.
-sujit
Thanks Sujit!
I have looked into this more, and found that the issue here is that MS is using some non-standard codes to represent their Unicode types. What's worse, there's some ID overlap between the MS extensions and the Oracle extensions, which means that it will not be possible to have one list of types that handles both platforms. The codes SQL 2000 is using are:
NCHAR = -8 (same as ROWID in Oracle)
NVARCHAR = -9
NTEXT = -10 (same as CURSOR in Oracle)
I have temporarily hacked up sqlunit to recognize these new codes, and am in the process of getting my same Unicode tests running against a couple of other platforms to see what codes they are using for these types. Once I have this figured out we can discuss what it will take to add this support...
Thanks fot the codes. I was leaning towards a bunch of type classes who know what they are, etc. Its a fair amount of work, but it will make the code cleaner and more manageable by removing the need for the giant switch statements in SqlTypeUtils.
We may need to have to specify a vendor attribute in the test tags to work with non-standard codes, and have some methods in these type classes that do the translation behind the scenes.
Let me know what you think or if you have another approach in mind.
-sujit
That sounds like a good approach, but I agree it's a lot of work. Definitely worth it in the long run, however.
Loading the extensions seems like it should be pretty straightforward-- we can extend the <sqlunit> task to provide a new attribute to indicate what extensions should be loaded, a la:
<sqlunit useExtendedTypes="MSSQL2K|ORACLE">
This would have the side effect of breaking existing oracle tests, however-- not good. Perhaps default to use Oracle extensions if not otherwise specified?
I'd be happy to help code/test this for you if you need it...
ralph
Thanks Ralph, I may need your help once I have set up the infrastructure for this. Basically, this is what I have in mind:
An IType interface which extends Comparable (for Jorge's sorting feature) and has the following methods definitions:
IType parse(String);
String format(IType);
Each SQL datatype will have to provide an implementation for this interface.
The mapping will be controlled by a types.properties with the following key value pairs (for example):
INTEGER.class = net.sf.sqlunit.types.IntegerType
INTEGER.type = java.sql.Types.INTEGER
NUMERIC.class = net.sf.sqlunit.types.NumericType
NUMERIC.type = java.sql.Types.NUMERIC
which can be overriden by vendor specific definitions in the same file:
postgresql.NUMERIC.class = net.sf.sqlunit.types.BigDecimalType
or even:
oracle.CURSOR.class =
net.sf.sqlunit.types.CursorType
oracle.CURSOR.type = -10
or in the file usertypes.properties which needs to be in the classpath and can be supplied by the user to override the definitions in the types.properties file, so for example in your case, your usertypes.properties could contain:
sqlserver.NCHAR.class = net.sf.sqlunit.types.StringType
sqlserver.NCHAR.type = -8 (or something).
and that would automatically override whatever there was for that type.
This would allow us to make quick site specific fixes without having to update the code and recompile. We can then fold in the usertypes.properties into types.properties in the next release.
Let me know what you think, and if you have other ideas to enhance this mechanism.
Thanks
Sujit
This looks really nice. I like basing it on prop files instead of hardcoding extensions, since this avoids the issues with having to rework the <sqlunit> element to specify the extensions to use. I assume that if there are no extensions specified, we will fall back to the standard java.sql type definitions?
The only change I might suggest is something that allows you to load extensions explicitly, rather than implicitly by DB type. This way I can define a single test suite that executes across DB types (I do this now with connections, and it's pretty sweet for my purposes).
Yes, it should fall back to the standard java.sql.Types if there are no extensions specified.
And with what I am suggesting, you could also define a type in the col element as something like:
<col id="1" name="c1" type="NCHAR">foo</col>
as well as:
<col id="1" name="c1" type="sqlserver.NCHAR">foo</col>
Is that what you wanted, or did you want to bypass the usertypes.properties altogether and put in something like this:
<col id="1" name="c1" type="JAVA_OBJECT" class="com.mycompany.someapplication.types.MyType">foo</col>
I can do the second one as well too, not sure if that would be used too much though.
-sujit
Also, someone pointed out that I could use the DatabaseMetaData to autodetect which database SQLUnit is connecting to. Just something to keep in mind, although then I will have to have the exact same names in the properties files.
-sujit
Loading extensions based on the metadata would do what I need. My test environment makes it tough for me to load a properties file (classpath is dynamic, and directories come and go), so having the right extensions appear automatically would be a big help.
Hi Ralph,
Thanks for sending me the code. I have based the code that is currently in CVS on it, although I have made some changes to support the sortability feature I mentioned earlier. I am going to be working on that over the next week or so.
The code is tested against my mock database (overriding the CURSOR with oracle.CURSOR) and against my PostgresSQL database, testing that the NUMERIC override to BigDecimal for PostgreSQL worked as expected (without specifying a server-name attribute in the connection to see if it will be picked up automatically).
If you could run the code against your test cases and see if they work, that would be really helpful.
I will write the docs as I find some time, but the net.sourceforge.sqlunit.types.properties contain mappings for pre-defined and some extension types, including the NCHAR, NVARCHAR and NTEXT that you told me about.
The name of the database server is derived from:
Connection.getMetaData().getDatabaseProductName()
Not sure if microsoft is what you get back from SQL Server, you may want to check that. (There is some code in test/java/VendorTest.java that you could use for that). You should also be able to just specify this as an attribute of your connection element as @server-name.
Let me know if you find anything.
Thanks
Sujit
We're getting pretty close-- Once I figured out that the ant Sqlunit task had moved, I was able to get everything up and running again. There were a couple of changes needed, however...
The big problem was that the server name returned by the jdbc driver I am using (com.inet.tds.TDSDriver) is "Microsoft Sql Server". The spaces in the name mean that it is not usable as a properties key. I had to hack up the server name handing in ConnectionRegistry to replace the spaces with underscores in order to get it to work.
The good news is that once I did that, I was able to change the types.properties to use the new name and everything worked really slick-- much easier than before.
I have not yet been able to test with my Oracle international DB yet (need to find my DBA), but it should be pretty straightforward.
Is there a test that tries all the various datatypes against a database?
Is there a test that tries various bound conditions (ie integers between -2 billion and +2 billion) against a database.
If there this, I can adapt it to the ASA suite to make sure it will function appropriately.
Dave
Sorry about moving the SqlUnitTask, had to do this to make things look more tied together. There are some more changes, I actually moved some of the files around yesterday night, the interfaces and the factories, since I wanted to minimize impact to existing external code, but forgot to change the package names. I will check them in in a few minutes.
I will also do the additional transformation on the database server name also when I check in the code.
-sujit
Not a problem-- it just took me a few minutes to figure out what happened...
I was also able to run through my same set of tests against Oracle. Looks pretty good, but I did uncover another minor issue with the new type stuff. Oracle uses BigDecimal for NUMERIC, so you need to do the same sort of override that you do for postgres in the types.properties file.
Hi Dave,
There is a JUnit test that tries the datatypes against a mock database. Its here:
/sqlunit/test/java/TypesTest.java
I have also tested to see that it runs against the existing SQLUnit tests (without changes) on my postgresql database. Ralph has tested against MS SQL Server (which should be similar to ASA?) and Oracle.
There are no tests for bound conditions, my assumption is that the database should know what its doing when sending back various data with the corresponding sql types, so I am simply mapping the type classes against the sql codes.
If you could update the SQLUnit tests for ASA that actually do these tests then it would be great, since we could use this as a template for generating tests for other databases as well.
-sujit
Trying to figure out how to run this test:
I updated from CVS.
ant install
ant "junit-test" -Dtest.class=java
ant junit-test -Dtest.class=java
I always get messages:
Ant could not find the task or a class this task relies upon.
This is common and has a number of causes; the usual
solutions are to read the manual pages then download and
install needed JAR files, or fix the build file:
- You have misspelt 'junit'.
Fix: check your spelling.
- The task needs an external JAR file to execute
and this is not found at the right place in the classpath.
Fix: check the documentation for dependencies.
Fix: declare the task.
- The task is an Ant optional task and optional.jar is absent
Fix: look for optional.jar in ANT_HOME/lib, download if needed
- The task was not built into optional.jar as dependent
libraries were not found at build time.
Fix: look in the JAR to verify, then rebuild with the needed
libraries, or download a release version from apache.org
- The build file was written for a later version of Ant
Fix: upgrade to at least the latest release version of Ant
- The task is not an Ant core or optional task
and needs to be declared using <taskdef>.
Anyway, somehow I have to tell it connection information on how to connect to ASA, and so on, and I didnt see where I enter that.
Any tips...
Thanks,
Dave
To run the TypesTest, you will need to do this:
ant junit-test -Dtest.class=net.sourceforge.sqlunit.test.TypesTest
You would typically instantiate a connection directly in the JUnit test, see the VendorNameTest.java for an example. Since you do not want to open and destroy the connection over and over, you may consider doing this using the TestSetup decorator as used in TypesTest.java.
-sujit
BTW, Ralph has also sent me the types.properties file after his tests, which I will update into CVS.
-sujit
Hmm, perhaps I am a bit dense.
But in VendorNameTest.java ( $Id: VendorNameTest.java,v 1.1) I only see this method that has anything remotely similar to connection information:
public void testWhatItLooksLike() throws Exception {
It does not appear to make a connection for the test suite.
In TestTypes.java ($Id: TypesTest.java,v 1.1),
public void setUp() throws Exception {
// :TODO: use TestSetup decorator here to have this done
// only once.
TypeMapper mapper = TypeMapper.getTypeMapper();
}
This does not seem to perform any form of connection. So where does this suite actually make the connection to the database?
I was referring to the code in VendorNameTest.java:
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/sqlunitdb",
"defaultuser", "defaultuser");
which is the generic pattern for getting a Connection using JDBC.
Your test will probably consist of running a SELECT from a table which has columns of all kinds of datatypes defined in it, and then accessing each one by one. Now that I think about this some more, you probably dont even need to do a JUnit test, simply create a SQLUnit test case with this kind of SELECT and then verify that all the values came back as expected.
-sujit