Announcement Announcement Module
Collapse
No announcement yet.
JDBC Configuration with MSSQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC Configuration with MSSQL

    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.

  • #2
    Add the driver to your project as a dependency

    Originally posted by camdebuck View Post
    Code:
    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:

    Code:
    <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).

    Comment


    • #3
      It should work by default...

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

      Code:
      <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?

      Comment


      • #4
        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.

        Comment


        • #5
          Microsoft JDBC Driver Worked

          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.

          Comment


          • #6
            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.

            Comment


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

              Comment


              • #8
                Originally posted by Rod Johnson View Post
                Makes sense to support the possibility of multiple drivers in general.
                Logged as https://jira.springsource.org/browse/ROO-617

                Comment


                • #9
                  Originally posted by Andrew Swan View Post
                  That's odd; I just created a test project with MSSQL as the "--database" argument, and it added the JTDS driver to the POM:

                  Code:
                  <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"?

                  Comment


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

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

                    Comment

                    Working...
                    X