1

I'm new to STRUTS and JDBC, my application tries to connect to a simple DB that has 3 tables, right now all is doing is trying to query 1 table that only stores "first, last names and a Id field"

System.out.println("-------- Oracle JDBC Connection Testing ------");

            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
            } catch (ClassNotFoundException e) {
                System.out.println("Where is your Oracle JDBC Driver?");
                e.printStackTrace();
                return null;
            }

            System.out.println("Oracle JDBC Driver Registered!");

            try {
                connection = 
                    DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","david","changeit");
            } catch (SQLException e) {
                System.out.println("Connection Failed! Check output console");
                e.printStackTrace();
                return null;
            }

            if (connection != null) {
                System.out.println("You made it, take control your database now!");
            } else {
                System.out.println("Failed to make connection!");
            }

where I would like to get the result of 1 column if a match occurs:

            String sql = "SELECT S_ID FROM Students WHERE firstname=? AND lastname=?";

            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, firstname);
            ps.setString(2, lastname);

            rs = ps.executeQuery();

            while (rs.next()) {
                studentid = rs.getString(1);
                ret = SUCCESS;
            }
        } catch (Exception e) { ...

As far as I can tell the connection is made, the SQL query

Select s_id from Students where firstname='first' and lastname='last';

when run on SQL Dev. works and gives me a single result.

I don't really get a stack trace the code just jumps from right before the 'while (rs.next()) {..' directly into the finally block

} catch (Exception e) {
        e.printStackTrace();
        ret = ERROR;
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
            }
        }
    }
9
  • Which line is line 83? Commented Jan 12, 2015 at 20:30
  • hmm actually is a number, but when ran the code doesn't even go inside the while loop while (rs.next()) { ... Commented Jan 12, 2015 at 20:36
  • yes firstname and lastname are populated correctly I debugged it. Commented Jan 12, 2015 at 20:37
  • Did you try debugging to ensure firstname and lastname are populated correctly? Commented Jan 12, 2015 at 20:37
  • Regarding your comment about not going into the while(rs.next()) loop, where else are you calling getString() from? Because that is where the error seems to be coming from. Commented Jan 12, 2015 at 20:41

2 Answers 2

1

I'm not sure how Oracle drivers work. But below statement is what i see on Oracle site. Are you getting a non empty resultset ? As you are not getting a nullpointerexception on .next(), i'm wondering if Oracle drivers return an empty ResultSet, which may lead to this problem.

http://docs.oracle.com/cd/B28359_01/java.111/b31224/getsta.htm

In case of a standard JDBC driver, if the SQL string being executed does not return a ResultSet object, then the executeQuery method throws a SQLException exception. In case of an Oracle JDBC driver, the executeQuery method does not throw a SQLException exception even if the SQL string being executed does not return a ResultSet object.

Sign up to request clarification or add additional context in comments.

6 Comments

I think this might be the problem. It seems that the query is fine.. when I ran it on SQLdev and the connection seems fine (it fails if I enter the wrong password). I don't know what it could be :(
Does the SQL returns at least single row when you run on SQLDev ? Please bear with me, your code i do not see any issue. Can you put a break point at rs = ps.executeQuery(); and see the value being assigned to rs ?
yes I can, this is the state of ps right before that line: i.imgur.com/OUpN64q.png
and here we can look at the first and last name inside ps>preparedStatement>parameterString: i.imgur.com/N4D3Jn1.png
Glad that i could be of some help. Please add your findings here if possible. It might help someone else.
|
1

Like I said I'm new at using this. The problem was that my schema didn't have the CONNECT role assigned to it.

Solution log in as 'SYSTEM' and grant the role to my schema

grant connect to MY_SCHEMA;

1 Comment

Glad you figured it out!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.