aspose file tools*
The moose likes JDBC and the fly likes JDBC doesn't retrieve all the results available in the MSSQL database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC doesn Watch "JDBC doesn New topic
Author

JDBC doesn't retrieve all the results available in the MSSQL database

Alexander Selling
Greenhorn

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

I really have no clue what's going on at all.. I've noticed lately how sometimes I won't get as many results from a query in Java as I get from doing the exact same SQL query from within the MSSQL Server Management Studio.
I've tried to include the MSSQL manager output and the java output, with the output data omitted. And my class that connects to the database is also pasted in there.
Basically the same query retrieves 26 results from within the MSSQL manager and only 16 from java ?!
Please point me in the right direction here

Thanks in advance !

Edited to remove screenshot: please post text.
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
Before you edited your post, you had two different select statements. One had "where key=XXXX" and the other had "where key=YYYY"
Misha Ver
Ranch Hand

Joined: Mar 03, 2008
Posts: 470
I would suggest to post code by using code tags, instead of screen shots.
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

I'm removing the screenshot. Please post text.
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Tom Reilly wrote:Before you edited your post, you had two different select statements. One had "where key=XXXX" and the other had "where key=YYYY"


Yeah I noticed that, I just took the picture earlier and happened to paste that one, it is the exact same query though.
I just took the screenshots cause I thought it would make things clearer, seeing how the MSSQL manager gets more results even though the sql syntax is exactly the same.

David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

Now the SQL and JDBC query results...
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
I don't really know how to describe the output, I'm getting 26 rows of results from the MSSQL manager and only 16 rows from java, I guess I was more asking if you guys had ever experienced anything like it ?
Since it is a very simple query merely a select statement, how could there be a difference ?
I'll give you some more (smaller) screenshots if you'd like ?
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

No, I think we've been pretty clear that we want text--this shouldn't be difficult. No, I've never had his issue, but I'm always suspicious of delimited names, and have no idea how they'll interact with the JDBC driver. Have you tried something similar with a non-delimited column name?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60056
    
  65

Please read PostTextNotScreenshots.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
Assuming that your two queries are the same (which I'm not yet convinced), could you be accessing different databases? different schemas? different user?
Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16
Have you tried something similar with a non-delimited column name?


Thing is I'm using an MSAccess database that came with a product that the family company bought, so I can't change their column names. However I imported their database into my MSSQL server and changed the column names on my copy of it, and just like you said, it will retrieve less results every time due to the space in the column name, because when I changed it from [KEY 1] to just KEY1, suddenly all the results came just fine. Now I don't know how to solve this, since I can't change the actual database that I want to read from, but at least now I know why

Thanks a lot!
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

It's even worse than that, because the JDBC-ODBC drivers have a lot of issues, so accessing the ironically-named Access may lead to even *more* troubles :)
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
How about creating views to "wrap" the tables?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC doesn't retrieve all the results available in the MSSQL database
 
Similar Threads
rules engine decision table book or web page
Database problem
Comparing large BIGINT fields in MySQL on Windows
HQL help needed
Query Result Caching