CODEWITHSUNDEEP
javasqljdbc
decal
decal

Reputation: 1017

Connecting to Oracle with JDBC causes queries to return zero rows.

So I have been playing around with querying databases using the standard 

Statement s = connection.createStatement();
ResultSet rs = s.executQuery(queryString);
ResultSetMetadata rsmd = rs.getMetaData();

while(rs.next)){
   String code = "";
   String value = "";

   for(int i = 1; i <= rsmd.getColumnCount(); i++){
      Object obj = rs.getObject(i);
      if(i == 1){
          code = obj.toString():
      }
      else{
          label = obj.toString();
      }
   }
   //Store code and labels in map
}

...go on to close statement and move on.

I am trying to select two columns from a table in each instance.

For the most part this works well. When working with MySql & Microsoft Sql databases I get a result set full of data in the table. However when I try to do this with an Oracle database I get an empty result set.

I have tested my query string in the SQL Developer application and it works fine, returns my data. But the result set doesnt contain anything. The resultSet metadata says that it has two columns though. Is there anything I need to do when interacting with an Oracle Database that is different from the other two? Thanks.

Upvotes: 1

Views: 1348

Answers (2)

Sonu
Sonu

Reputation: 732

The database table has records but the JDBC client can't retrieve the records. Means the JDBC client doesn't have the select privileges. Please run the below query on command line:

grant  all on emp to hr;

Upvotes: -1

matt forsythe
matt forsythe

Reputation: 3922

If your query works when you run it against the Oracle database, and you know the code works since you've run it against MySQL, then some other things to try are:

1.) Make sure your JDBC connection URL is correct. Are you sure you are connecting to the database that you intend to? (i.e. - the one that would return the rows you expect?)

2.) Take into account credentials. Make sure you are using the same credentials through JDBC that you are when connecting to Oracle directly.

3.) Make sure both connections are being made from the same machine and with the same environment. Oracle drivers rely on environment variables to find a file (I believe it is called tnsnames.ora, or something like that) that contains the alias & connection info. Getting different versions of that file could point you to different Oracle instances.

4.) Try manually specifying your schema name in the query. So instead of select * from my_table use select * from my_schema.my_table. Sometimes Oracle clients will configure their sessions to have default schemas set up in their preferences.

5.) If your are attempting to select data that you've inserted with your Oracle client, make sure you've committed the transaction in your Oracle client so that the data is visible to other sessions.

One last debugging tool to use is to try connecting via the Squirrel DB client. Squirrel is a 100% pure java SQL client that connects to any DB using JDBC. It would be a good test to make sure your JDBC Driver, Connection URL, etc. are all valid.

Upvotes: 2

Related Questions