File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Bug in JDBC ? Simple Select statement not returning a resultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Bug in JDBC ? Simple Select statement not returning a resultSet" Watch "Bug in JDBC ? Simple Select statement not returning a resultSet" New topic
Author

Bug in JDBC ? Simple Select statement not returning a resultSet

Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Hey guys!

Background info:
The database is a Microsoft SQL Server 2008
The java program is running on a Windows 2003 Server 64 bit
I'm using the latest verison of java off of oracles site

My problem is that I'm not getting a resultset when executing a SQL-statement, I really just can't figure out why.

Executing this string in the Microsoft SQL Server Management Studio gives the wanted resultset:
SELECT * FROM Database.dbo.Table WHERE KEY1 = 16775342;

However doing the exact same thing in java gives me an empty resultset ....


Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

What happens if you use:
rset = st.executeQuery(query);



And it's better not to use "SELECT *". Use the SELECT real column names".




OCUP UML fundamental and ITIL foundation
youtube channel
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Jan Cumps wrote:What happens if you use:
rset = st.executeQuery(query);

And it's better not to use "SELECT *". Use the SELECT real column names".



I've changed the code accordingly. I've also changed the select statement to only retrieve one column.
The resultset is still empty though, I can add that it works about 50-60% of the time and I'm trying to figure out why java ignores some rows in the database
where as it gladly accepts others ...

Just to be clear, when I execute these statements, depending on what ID I'm looking for java will either give me a resultset or it won't.
Where as the SQL Server Management Studio ALWAYS gives me a resultset ..
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

Have you committed all the inserts that you did in SQL Server Management Studio ?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

... and please don't change your original first post. Other people will now not understand what we were talking about.
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Jan Cumps wrote:Have you committed all the inserts that you did in SQL Server Management Studio ?


I'm afraid I don't know what you mean, the rows have been in the database for at least a week and the problem will persist no matter if I restart the server or not.
The only "commit" I know of is the one in java where you basically execute the changes you've made through an 'update' or the like.

As far as I can understand the data I'm asking the database for is committed since it's there after I've restarted both the sql server and the windows 2003 server.
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Jan Cumps wrote:... and please don't change your original first post. Other people will now not understand what we were talking about.


Alright, edited it again.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

SQL Server Management studio auto commits by default. If you are not sure what "commit" means in a database context I would recommend you stop coding and take a bit of time to read up on database concepts. Life will be far less painful if you do.

It might be an idea to use the SQL Server profiling tool to make sure you are running the SQL you believe you are. It could be you are using different schemas or your statement is subtly different.

As another aside, this:

kind of defeats one of the purposes of JDBC. You might want to change that call to use a String read from somewhere.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Paul Sturrock wrote:SQL Server Management studio auto commits by default. If you are not sure what "commit" means in a database context I would recommend you stop coding and take a bit of time to read up on database concepts. Life will be far less painful if you do.

It might be an idea to use the SQL Server profiling tool to make sure you are running the SQL you believe you are. It could be you are using different schemas or your statement is subtly different.

As another aside, this:

kind of defeats one of the purposes of JDBC. You might want to change that call to use a String read from somewhere.


I don't know what purpose it defeats but after some googling.. I've changed the connection call. I know what commit means, it's the means of making a transaction permanent. I just didn't know what you meant with "Have you committed all the inserts that you did in SQL Server Management Studio ?" because I haven't done any inserts there I've imported the database from an MS Access database that has its input from a CRM software. So I really have no control over what goes into the database, I'm just trying to learn how to read the data that the crm software populates the database with. I obviously should have told you that.. so that's my bad.

It's been working for quite some time now though and for some reason it just stopped working...

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

Can you check the type of the key column in SQLServer. Maybe when importing it from Access, the type is no longer numeric.

What happens if you query: from table where KEY1 = '16775342' ?
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Jan Cumps wrote:Can you check the type of the key column in SQLServer. Maybe when importing it from Access, the type is no longer numeric.

What happens if you query: from table where KEY1 = '16775342' ?


Things are just getting crazier for every test I do .. I have these larger SQL-statements that give me a resultset where as the simple select statement doesn't .....................

I'll try to explain, this larger statement basically:
1) fetches all the objects that belong to "today" and creates a temporary table and fills it with these objects.
2) the statement now fetches all the objects that belong to "me"(userID) out of this temporary table
3) now when we have all the objects that belong to me and the current date the statement will create new temporary tables for information about the attending entrepreneurs, insurance details, address etc, fetches the needed info and fills these temp tables.
4) to produce a resultset from these temp tables a simple select statement is made on each of them
5) drop all the temp tables

Now this larger sql statement doesn't give me any objects for today, even though management studio will, however it will give me objects for tomorrow..and the day after ..
Whereas the simple select statement I showed earlier won't give me anything, for any day ......

And you might be on to something about the type changing .. since both:
select * from table where KEY1 = '16775342'
select * from table where KEY1 = 16775342

works just fine in management studio, and makes no difference from java either. And when I look at the type in management studio
it says Key1(PK,int, not null) so it's supposed to be an integer, I don't understand why management studio nor java are complaining..
it sure does complain when I forget the '' for the Data field which is a (nvarchar(255),null)..

Where can I find an easy tutorial about the "sql server profiling tool" since I've never used it.

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

You say you get an empty ResultSet... but you would get the identical output from that code if an Exception were thrown, namely no output. You haven't eliminated that possibility, you've just completely ignored it. Replace line 35 in your original post by this:
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Paul Clapham wrote:You say you get an empty ResultSet... but you would get the identical output from that code if an Exception were thrown, namely no output. You haven't eliminated that possibility, you've just completely ignored it. Replace line 35 in your original post by this:


I changed to jtds and it started working, I've also changed a chunk of other code so.. I really don't know what made it work.. I can't wait to actually take some courses in software engineering cause I'm obviously not the type that can learn how to develop software well on my own.. The downside of just reading math and physics the first year of college I guess..

Either way I just wanted to say thank you for your time :)
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

So your code is working now? Here's a free software-engineering tip: it's still bad code if it ignores errors. Put in that line of code which notifies you of an exception, even if the exception didn't happen the last time you ran the code.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Bug in JDBC ? Simple Select statement not returning a resultSet