different results from query in java code and on oracle db server: but no connection error

Question

I have java code that connects to a remote oracle 11g EE db server. If i run a particular query in sqlplus it returns one result

SQL> SELECT COURSENAME from COURSES where skillID=1;

COURSENAME
--------------------
basic

But if I run the same query from the java code below it returns no results. I can copy the query syntax out of the query variable in the java debugger and running it on oracle so I know there is no syntax issue with the query. Also, it is not SQL exceptions or class not found exceptions so it seems to be running the query successfully -- just returning zero results.

What might be going on?

    private String getCourseForSkill(int skillID){
    try{
        Class.forName("oracle.jdbc.OracleDriver"); 
        String query="SELECT COURSENAME from COURSES where skillID=" + skillID ; 
        con = DriverManager.getConnection(url, user, password);
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery(query);
        rs.next();
        return rs.getString("COURSENAME");
    }
    catch (ClassNotFoundException ex){
        System.out.println(ex.getMessage());
    }
    catch (SQLException ex) {
         System.out.println(ex.getMessage());
    }
    return null;
}

Did you try to debug that what are you passing for "knowledgeAreaName" in this method call ? try to log the value to see what is getting passed to this method call. — NullPointerException
– NullPointerException, Commented Apr 11, 2013 at 15:24
Is ur program not throwing ClassNotFoundException because according to me, in class.forName() u should use oracle.jdbc.driver.OracleDriver rather than oracle.jdbc.OracleDriver. — Jatin Khurana
– Jatin Khurana, Commented Apr 11, 2013 at 16:07
From the "is it plugged in" department - are you sure that the connection url is pointing to the same database as your manual query? Besides the issue that you are asking about, you are also probably leaking your database connection and statement objects. Also, it's probably ok in this one instance, but don't get into the habit of concatenating strings to build sql statements. Look up 'sql injection' to see why. — GreyBeardedGeek
– GreyBeardedGeek, Commented Apr 18, 2013 at 17:07
@GreyBeardedGeek the URL looks like "jdbc:oracle:thin:@website:port:orcl I get to the manual query by doing ssh@website, authenticating and then running command=sqlplus — bernie2436
– bernie2436, Commented Apr 18, 2013 at 19:32
Hypothesis: Some time ago you has been connected to the database with wrong user (such as system or some "default"/"debug" account) and created courses table. After that you connect as right user and created same table and fill it with data. Now while connecting manually by ssh you choose new good account, but while running program in some place wrong/default account stored and your query run against empty table owned by the wrong user. Please, check that ... — ThinkJet
– ThinkJet, Commented Apr 18, 2013 at 22:04

Glen Best · Accepted Answer · 2013-04-20 05:10:41Z

I think you're connecting to different Oracle instances, or more likely, as different Oracle users in the two cases

@GreyBeardedGeek the URL looks like "jdbc:oracle:thin:@website:port:orcl I get to the manual query by doing ssh@website, authenticating and then running command=sqlplus

Safer to run sqlplus <username>/<password>@<orainstancename>, because you can explicitly specify the oracle instance ID. In your case, it seems your program is using jdbc connection jdbc:oracle:thin:@website:port:orcl, so your orainstancename would be 'orcl' - just ensure that your tnsnames.ora file has the instance 'orcl' with the same 'port' as used by the jdbc connection

How to debug a little more

Run the following code:

con = DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
String insert="INSERT INTO COURSES (SKILLID, COURSE)"+ // can add other columns
             "values (?, ?) );"                       // add ? for other columns    
PreparedStatement ps = con.createPreparedStatement();
ps.setInt(1, 999);
ps.setString(2, "Complete Bullwarks");
// can set other columns
ps.executeUpdate(insert);
con.commit();

NOW connect manually, re-run your original select statement & see if the added row is there. If no error in java and no new row in Oracle: extremely likely you're using 2 different Oracle instances/schemas.

ALSO rerun your original java select code, but with SkillID=999 - extremely likely it will work.

Cheers

bernie2436 · Accepted Answer · 2013-04-25 15:17:18Z

2

I had to do a commit to add the rows. When I typed commit; into the sql plus terminal then the remote jdbc connection could 'see' the rows. I am used to SQL server where you don't have to explicitly do these kinds of commits when using linq-to-sql or sql management studio.

edited Apr 25, 2013 at 15:17

answered Apr 23, 2013 at 14:59

bernie2436

24.2k55 gold badges160 silver badges252 bronze badges

1 Comment

Frank Schmitt Over a year ago

Honestly, I seriously doubt that you don't have to commit in SQL server. Probably, the tool you're using either commits by default or sets the transaction level to read uncommitted, see stackoverflow.com/questions/1670673/…

RMAN Express · Accepted Answer · 2013-04-23 21:42:19Z

0

It can be three issues.

1) skillID <> 1 in your Java code. Add debug and check.

2a) You are connecting to another database.

2b) You are connecting to the same database but SELECTING from a table in another schema.

To check 2a and 2b:

select user from dual; -- connect username

select name from v$database; -- database name

select host_name from v$instance; -- host name database is running on

This query returns all three into one result.

select user || '' || d.name || '' || i.host_name from v$database d, v$instance i;

answered Apr 23, 2013 at 21:42

RMAN Express

4964 silver badges14 bronze badges

Comments

sehrope · Accepted Answer · 2013-04-24 08:21:20Z

Assuming you are actually connecting to the same database this is caused by not committing the INSERT in the sql*plus connection.

Oracle by default does not run in auto-commit mode when connecting via OCI (which sql*plus uses to connect). Any DML(INSERT ...) executed in sql*plus will not be visible to any other session until it is committed. This is because Oracle provides a read committed isolation level by default. The only thing visible to other users across sessions are write locks.

It doesn't matter if you connect the second connection via JDBC or OCI, it won't see the changes till you commit the first connection.

To test this out try opening 2 sql*plus connections and run the following:

-- Executing DDL in Oracle causes an implicit commit before and after the
-- command so the second connection will see the existence of this table:
CREATE TABLE foobar ( x VARCHAR(1) );

Execute this in connection #1 - you should get zero (we haven't inserted anything yet):

SELECT COUNT(*) FROM foobar;

Execute this in connection #2:

INSERT INTO foobar ( x ) VALUES ( 'A' );

Execute this in connection #1 - you should still get zero (INSERT is not committed so connection #1 cannot see it):

SELECT COUNT(*) FROM foobar;

Execute this in connection #2:

COMMIT;

Execute this in connection #1 - you should get 1 (it's committed now):

SELECT COUNT(*) FROM foobar;

Collectives™ on Stack Overflow

different results from query in java code and on oracle db server: but no connection error

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related