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*