JDBC Configuration with MSSQL

[camdebuck]

I'm trying to do a very simple test with roo (ten minute sample but using MSSQL as the database instead of HYPERSONIC_INMEMORY). My first issue is trying to get the JDBC driver working. I did search the forums and it sounded like I need to issue the following command to get the JDBC driver setup in maven:

mvn install:install-file -DgroupId=microsoft -DartifactId=sqljdbc -Dversion=1.0.2531 -Dpackaging=jar -Dfile="C:\temp\jdbcdriver\sqljdbc_2.0\enu\sqljdbc4 .jar"

However, when I issue a "perform tests" in roo I get the following message:

Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver '

When I try using "mvn tomcat:run" I basically get the same error message.

What am I missing and how do I resolve it?

Thank you for your assistance.

[Andrew Swan]

mvn install:install-file -DgroupId=microsoft -DartifactId=sqljdbc -Dversion=1.0.2531 -Dpackaging=jar -Dfile="C:\temp\jdbcdriver\sqljdbc_2.0\enu\sqljdbc4.jar"

If you read the doco for the Maven install plugin, you'll see that the above command installs the JDBC driver into your local Maven repository. This is a necessary step because most commercial JDBC drivers are not available from any public repositories.

However that command by itself does not add the driver to your project; to do that, you need to declare it as a dependency in your pom.xml file as follows:

<project ...>
    ...
    <dependencies>
        ...
        <dependency>
            <!-- These are the cooordinates you used when installing the JAR file into your local repo -->
            <groupId>microsoft</groupId>
            <artifactId>sqljdbc</artifactId>
            <version>1.0.2531</version>
        </dependency>
    <dependencies>
    ...
</project>

If you want to use Roo, it's really worth while taking some time to get familiar with Maven (IMO it's the best build tool anyway, but that's another discussion).

[Andrew Swan]

That's odd; I just created a test project with MSSQL as the "--database" argument, and it added the JTDS driver to the POM:

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>com.springsource.net.sourceforge.jtds</artifactId>
    <version>1.2.2</version>
</dependency>

This should work straight out of the box, as the JTDS driver is an open source SQL Server driver that's available from the public Maven repository. In other words, you should never have had to download or install the Microsoft driver. Are you using Roo 1.0.1?

[camdebuck]

Thank you for the information....

Instead of using the Microsoft JDBC driver I've crated another test project using the JTDS JDBC driver and got it to work connecting to a remote server.

My original reason for trying to use the Microsoft JDBC driver was I couldn't get the JTDS driver to work. However, as usual, it was a user error. The hardest part always seems to be trying to find *all* of the information to make everything work.

My error with the JTDS driver was that I didn't change the database.* properties. In ROO you can issue the "database properties list" command and it will show you what the current settings (you can also change them as well).

Just so others out there may find this useful, here is what I did to finally get it to work in a ROO shell from beginning to end:

project --topLevelPackage com.test3
persistence setup --provider HIBERNATE --database MSSQL
database properties set --key database.driverClassName --value net.sourceforge.jtds.jdbc.Driver
database properties set --key database.url --value jdbc:jtds:sqlserver:/localhost:1433/MyDatabase
database properties set --key database.username --value myuser
database properties set --key database.password --value mypassword
entity --class ~.Timer --testAutomatically
field string --fieldName message --notNull
controller all --package ~.web
selenium test --controller ~.web.TimerController
perform tests
perform package
perform eclipse
exit
mvn tomcat:run


I still would like to get the Microsoft JDBC driver to work. I'll give your recommendations about changing the pom.xml file and see what happens and report later.

Again, thank you for pointing me in the right direction.

[camdebuck]

By adding the dependency that you suggested to my pom.xml file I was able to make my test application connect to my database using the Microsoft JDBC driver.

Again, thank you for pointing me in the right direction. I'm now able to connect to a SQL Server database using two different JDBC drivers.

[Ben Alex]

Is there any opinion on making JTDS an extra JDBC option for MSSQL in the Roo tab completion options? It might save others from this problem.

[Rod Johnson]

Makes sense to support the possibility of multiple drivers in general.

[Ben Alex]

Makes sense to support the possibility of multiple drivers in general.

Logged as https://jira.springsource.org/browse/ROO-617

[msenocak]

That's odd; I just created a test project with MSSQL as the "--database" argument, and it added the JTDS driver to the POM:

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>com.springsource.net.sourceforge.jtds</artifactId>
    <version>1.2.2</version>
</dependency>

This should work straight out of the box, as the JTDS driver is an open source SQL Server driver that's available from the public Maven repository. In other words, you should never have had to download or install the Microsoft driver. Are you using Roo 1.0.1?

Shouldn't the artifactId just "jtds" instead of "com.springsource.net.sourceforge.jtds"?

[Alan Stewart]

The correct dependency which gets installed if your run the persistence setup command using MSSQL as the database is

 <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.2.4</version>
            <classifier/>
        </dependency>