Getting empty resultSet for simple query

Question

gurus, I am new to Java SQL, and need some help. I'm trying to get a parameter from MS SQL Server 2008. The data is definitely there - it is a current and valid DB, and I'm trying to use the users records to get cridentials for another application.

I asserted the following query:

 String query = "SELECT [USER].qc_number FROM [USER] WHERE "[USER].login_name = '"
 + userNameInput + "' AND [USER].password = '" + passWordInput + "';";

Where userNameInput and passWordInput are received from the user. The URL, query and driver class are definitely correct: I checked the DB schema both from the application and from the server views. Furthermore, I verified all the Exceptions systems by changing parameters one by one, resulting in correct Exceptions messages. However, I get a resultSet with 1 column and 0 rows.

The code is below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class trOdbc 
{// database URL                              
    final String DB_URL = "***";
    final String Class_URL = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    private Connection connection = null; // manages connection
    private Statement statement = null; // query statement
    private ResultSet resultSet = null; // manages results
    private Boolean connectedToDatabase = false;

// ----------------------------------------------------------

        public void createJdbcConnection() 
    { // connect to database books and query database
        if (connectedToDatabase)
        {   return; }
        try 
                { // connectedToDatabase is false - establish the connection
            Class.forName(Class_URL); 
            connection = DriverManager.getConnection
                (DB_URL, "***", "***" );
            statement = connection.createStatement
                (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                 ResultSet.CONCUR_READ_ONLY); 
            connectedToDatabase = true;
        } 
        catch (SQLException ex) 
            { System.out.println ("SQL Exception in connection establishment: " + ex); }
        catch (ClassNotFoundException ex) 
            { System.out.println ("Class not found exception in query process: " + ex); }
    }

// ----------------------------------------------------------

    public String [][] processJdbcQuery (String query)
    {                
        createJdbcConnection ();
        if (!connectedToDatabase)
                {   return null; }// the connection wasn't established 

        try 
        {// query database
            resultSet = statement.executeQuery(query);
            int columns = resultSet.getMetaData().getColumnCount();
                        int rows = 0;
            if (resultSet != null)
            {
                resultSet.beforeFirst();
                resultSet.last();
                rows = resultSet.getRow();
            }
            String [][] tempData = new String[rows][columns];
            resultSet.beforeFirst();
                        rows = 0;
                        while (resultSet.next())
            {
                for (int x = 1; x <= columns; x++) 
                {
                    tempData [rows][x - 1] = resultSet.getString (x);
                }
                                rows++;
            } 
            CloseJdbcConnection ();
            return tempData;
        }
        catch (SQLException ex) 
                {   
            System.out.println ("SQL Exception in query process: " + ex);
            CloseJdbcConnection ();
            return null; 
                }
    } // end processJdbcQuery

// ----------------------------------------------------------

        public void CloseJdbcConnection()
    {              
        if ( connectedToDatabase )                  
        {// close Statement and Connection. resultSet is closed automatically.
            try 
            {
                statement.close();                        
                connection.close();                            
                connectedToDatabase = false;
            } 
            catch (SQLException ex) 
                { System.out.println ("SQL Exception in connection closure: " + ex); }
        } // end if
    } // end method CloseJdbcConnection          
} // end class trOdbc

Answer 1

Why don't you use Prepared Statement instead ?

Here is a good tutorial for using prepared statement in java

In your case it would be :

String query = "SELECT [USER].qc_number FROM [USER] " +
        "WHERE [USER].login_name = ? AND [USER].password = ?;";

And then set it with different values each time you execute it like :

PreparedStatement ps = connection.prepareStatement(query);

ps.setString(1, userNameInput);
ps.setString(2, passWordInput);

resultSet = ps.executeQuery();