4 Replies Latest reply on Jun 10, 2003 5:05 AM by csanche7

    MSSQL configuration

    multiplex77

      Hi,

      I'm very new to JBoss and J2EE. I've been trying to get my JSPs to access my MSSQL database.

      Here's what I've done:
      1. Downloaded and installed the MSSQL 2000 JDBC driver.
      2. Edited the \server\default\deploy\mssql-ds.xml file:
      -----------------------------------

      <local-tx-datasource>
      <jndi-name>MSSQLDS</jndi-name>
      <connection-url>jdbc:microsoft:sqlserver://server1:1433;DatabaseName=Test;User=sa;Password=passwd;SelectMethod=Cursor</connection-url>
      <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
      Test
      <user-name>sa</user-name>
      passwd
      <min-pool-size>100</min-pool-size>
      <max-pool-size>1000</max-pool-size>
      <blocking-timeout-millis>5000</blocking-timeout-millis>
      </local-tx-datasource>


      3. I edited the "C:\jboss\bin\run.bat" file and inserted into the second line:
      "set CLASSPATH=c:\mssql_jdbc_driver\lib\mbase.jar;c:\mssql_jdbc_driver\lib\msutil.jar;c:\mssql_jdbc_driver\lib\mssqlserver.jar;"

      where C:\mssql_jdbc_driver is the folder where my *.jar files are located.

      4. I created a .jsp page like this:
      -----Start test.jsp -----------
      <%
      try {
      String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
      String sConnStr = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=multiplex;User=sa;Password=secret;";

      Class.forName(sDBDriver);
      java.sql.Connection conn = java.sql.DriverManager.getConnection(sConnStr);
      java.sql.Statement stmt = conn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select 'Multiplex' As Name");
      while (rs.next()) { %>
      <%= rs.getString("Name") %>
      <%}
      }
      catch (ClassNotFoundException cnfe)
      {
      System.err.println("Unable to load MSSQL database driver!");
      System.err.println("ClassNotFoundException: " + cnfe.getMessage());
      }
      %>
      ------End test.jsp -----------------------

      5. But then when I run test.jsp, I get a ClassNotFoundException saying its not able to load the MSSQL driver.

      Have I missed out some configuration step? I've been searching all avenues of support for 3 days but still can't get it working. Can someone help, please?

      Thanks!

        • 1. Re: MSSQL configuration
          drcharris

          Have you tried copying all those jars into server/default/lib of your JBoss install? And get rid of the modifications to CLASSPATH in the batch file - you won't need them.

          IIRC the batch file just sets up a little boot classpath to bootstrap the server - then it uses its own internal classloading scheme.

          • 2. Re: MSSQL configuration
            mgariepy

            Put the MS SQL drivers into the JBoss server/default/lib directory.

            The 3 jar files that make up the driver are msbase.jar, mssqlserver.jar and msutil.jar. Then restart JBoss.

            • 3. Re: MSSQL configuration
              multiplex77

              I tried putting them in that folder but it didnt work. But I finally managed to get it to work by putting them in the C:\jBoss\server\default\deploy\jmx-console.war\WEB-INF\lib
              (my .jsp files are in C:\jBoss\server\default\deploy\jmx-console.war)

              Why do you think that was the case? In any case it works now. Thanks so much for all your help.

              • 4. Re: MSSQL configuration
                csanche7

                Hi,

                You should use the pool you have created in JBoss. The jsp code should be something like this:

                InitialContext ctx = new InitialContext();
                ds = (DataSource)ctx.lookup(pool);
                conn = ds.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT ......");

                Hope this helps...