Quirky stuff with JNDI and MSSQL

My last post talked about getting Glassfish and MSSQL connected. By doing this, we effectively hand over the management of the our database connection to the application container. One less thing to deal with in code.

Now, we try to get some data and see if it works. I decided to try out JDBC first. I could just jump to Hibernate but remember Hibernate just glosses over the details. If JDBC works then I know that Hibernate will work. So my code started as:

        ....
        try{
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("MSSQL2008");
            
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            
            ResultSet rs = stmt.executeQuery("SELECT * FROM [TestDB].[dbo].[Users]");
            String temp = rs.getString("email");
            
            stmt.close();
            conn.close();
           
            return temp;
        }catch(Exception e){
            e.printStackTrace();
        }

That didn't go off without errors. I got a "SQLServerException: The result set has no current row." error. Apparently, the Microsoft JDBC driver I was using didn't move the cursor to the first row of the result set. Feature or bug?
To fix this we forcibly move the cursor to the first row by adding the line: rs.absolute(1).
The code then will look like:

        ....
        try{
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("MSSQL2008");
            
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            
            ResultSet rs = stmt.executeQuery("SELECT * FROM [TestDB].[dbo].[Users]");
            rs.absolute(1);
            String temp = rs.getString("email");
            
            stmt.close();
            conn.close();
           
            return temp;
            
        }catch(Exception e){
            e.printStackTrace();
        }

Save, compile and redeploy and.....another error. Typical. This time the error I got was: SQLServerException: The requested operation is not supported on forward only result sets. Can you get what's the error? So we need to NOT create a forward only result set. This is done by adding the line: createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY). 
The final code is then:

        ....
        try{
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("MSSQL2008");
            
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
            ResultSet rs = stmt.executeQuery("SELECT * FROM [TestDB].[dbo].[Users]");
            rs.absolute(1);
            String temp = rs.getString("email");
            
            stmt.close();
            conn.close();
           
            return temp;
            
        }catch(Exception e){
            e.printStackTrace();
        }

All should be OK now. Now to do some real damage. *evil grin*

Unholy union: Glassfish and MSSQL

I'm in a situation where the data is on a Microsoft SQL Server and I want to use Glassfish and J2EE. And all of this done inside Netbeans.

SQLServer + Netbeans?

Getting Netbean's version of Glassfish to work with MS SQLServer 2008 is a trick. When you start it up and open the Admin console webpage you'll head to the Resources > JDBC > JDBC Connection Pool and open make a new connection. You'll finish out the wizard until you try to ping the server. FAIL!

The problem is Netbean's version of Glassfish doesn't have the correct jars. Open C:\Program Files\glassfish-3.1.1\glassfish\lib; take a look and facepalm at this display of fail from Oracle.

Lets not dwell on that.

So we need a JDBC driver and a JDBC driver we shall get. Don't worry although its Microsoft, it won't cost you a thing. Documentation here. Driver here and just drop the jar file into that folder you just opened. Use the sqljdbc4.jar. Don't forget to restart the Glassfish server.

Now we redo the step in creating the JDBC connection pool resource. Here is what I did:

1. Named the Pool: MSSQL2008
2. Set the resource type to: java.sql.driver
3. Set the driver class name to: com.microsoft.sqlserver.jdbc.SQLServerDriver
4.  Set my properties to the following values:
• URL = jdbc:sqlserver://{iptoserver};instanceName={name};databaseName={defaultdb};integratedSecurity=false;
• user = my admin username
• password = duh!

Troubleshoot if the ping test fails.

Don't forget to create a JDBC resource after you succeed in creating your connection pool in Glassfish. You'll need it if you want to get database access via JNDI.