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 Solved : JTDS MSSQL Pagination problem. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Solved : JTDS MSSQL Pagination problem." Watch "Solved : JTDS MSSQL Pagination problem." New topic
Author

Solved : JTDS MSSQL Pagination problem.

Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
I'm using jTDS 1.2.2 driver connecting to a MSSQL 2005 server.

When I execute this sql through a Statement object I get an SQLException. If someone could help me out that would be cool.

Thanks :-)

SQL = "SQL SELECT * (SELECT TOP 950 * FROM (SELECT TOP 955 * FROM tbl_vodaphone ORDER BY feedId DESC) ORDER BY feedId ASC) ORDER BY feedId DESC"

Result:

[ July 25, 2008: Message edited by: Darren Wilkinson ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
The characters "SQL" at the beginning of the query string look strange to me. Also, the first SELECT doesn't have an associated FROM. Or is that SQLServer-specific syntax?
[ July 23, 2008: Message edited by: Ulf Dittmer ]
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Oh sorry the SQL is my output.

Should have been SQL = SELECT * (SELECT TOP 950 * FROM (SELECT TOP 955 * FROM tbl_vodaphone ORDER BY feedId DESC) ORDER BY feedId ASC) ORDER BY feedId DESC

Also I made the change you suggested but with the same error.

SELECT * FROM (SELECT TOP 950 * FROM (SELECT TOP 955 * FROM tbl_vodaphone ORDER BY feedId DESC) ORDER BY feedId ASC) ORDER BY feedId DESC

Thanks for responding though :-)

[ July 23, 2008: Message edited by: Darren Wilkinson ]
[ July 23, 2008: Message edited by: Darren Wilkinson ]
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Sorry - should have replied rather than edited
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
In that case I'd run the query through the command line client that comes with SQLServer, and simplify it's clear which "ORDER" it is that's causing the problem.
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
I ran the following through SQuirrel SQL Client,

SELECT * FROM (SELECT TOP 950 * FROM (SELECT TOP 955 * FROM tbl_vodaphone))

and got the following,

Error: Line 1: Incorrect syntax near ')'.
SQLState: 37000
ErrorCode: 170

SQUirrel table syntax is different so I tried,

SELECT * FROM (SELECT TOP 950 * FROM (SELECT TOP 955 * FROM [dt-data].[tbl_vodaphone]))

and got the following,

Error: Line 1: Incorrect syntax near ')'.
SQLState: 37000
ErrorCode: 170

I am clearly no expert but is it the table name?!?!
[ July 23, 2008: Message edited by: Darren Wilkinson ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
Can you run

How about


Actually, using TOP doesn't make sense without using ORDER, so the error you're getting now is probably SQLServer telling you that it's looking for an ORDER clause where it sees a ")".
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Using SELECT * FROM (SELECT TOP 950 * FROM (SELECT TOP 955 * FROM tbl_vodaphone ORDER BY feedId DESC) ORDER BY feedId ASC) ORDER BY feedId DESC

I get...

Error: Incorrect syntax near the keyword 'ORDER'.
SQLState: S1000
ErrorCode: 156
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Try using aliases. e.g :


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Using SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 5 * FROM tbl_vodaphone ORDER BY 'feedId' DESC) AS tbl_vodaphone_alias_a ORDER BY 'feedId' ASC) AS tbl_vodaphone_alias_b ORDER BY feedId DESC

all the previous errors go away - however I don't get the results I was expecting - I always get the top 5 records but... I think this is because the value of feedId is the same for every record (as they come from the same feed) and (now the errors are gone) it seems to me that this sql works by getting a set of records, reversing the order then get the top sub-set of records - or something like that.

Anyway, my tbl_vodaphone doesn't have a auto inc unique id for each record to order with so I will put one in there and try that.

Thanks for all your help. I will post back when I have it working so it may help someone else.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
(SELECT TOP 10 * FROM (SELECT TOP 5 * FROM tbl_vodaphone ORDER BY 'feedId' DESC) AS tbl_vodaphone_alias_a ORDER BY 'feedId' ASC)

I may be missing something obvious, but since the inner SELECT returns 5 rows, what purpose does the "TOP 10" in the outer SELECT have? It will still be 5 rows.
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
I don't know for sure. I got the basic sql from an MSSQL forum where I had asked how to do pagination with MSSQL like you can easily with MySQL. I usually use MySQL but the client has their data in MSSQL.

Assuming SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 5 * FROM tbl_vodaphone ORDER BY 'id' DESC) AS tbl_vodaphone_alias_a ORDER BY 'id' ASC) AS tbl_vodaphone_alias_b ORDER BY feedId DESC

I think it is supposed to work by:

1. Getting 10 records.
2. Reversing the order of those records by id (primary key 1, 2, 3, etc).
3. Then get the top 5 records of the set that were returned.
4. Then reverse those records - to put them back into the right order.

Now the ORDER BY ASC, DESC, ASC doesn't seem to me to do that. It suggests to me that it would do the reverse.

That's how I think it is supposed to work and the MSSQL guys all say "yeah this is what we do" even though it is seems to me to get less efficient as you get closer to the end of a large table.

I may have completely misunderstood everything :-)
[ July 23, 2008: Message edited by: Darren Wilkinson ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
I think what it actually does is:

- get the top 5 records sorted by "id"
- get the top 10 of those -in other words, still the original 5- but reversed in order
- and then sort those 5 records by feedId
[ July 23, 2008: Message edited by: Ulf Dittmer ]
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Oh slight typo - the last feedId should be id

But yes, running it through SQuirrel it just keeps returning the top 5 records.

This is one of the links the MSSQL guys kept pointing me at MSSQL Pagination Tip

I'm finishing work now but I will be looking at this at home and at work tomorrow if I haven't fixed it :-(

Thanks for all your help.
[ July 23, 2008: Message edited by: Darren Wilkinson ]
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
I'm getting really puzzled by this - I never had these problems using MySQL.

I am running SELECT * FROM tbl_vodaphone ORDER BY 'id' DESC

I believe (but may be wrong) that this should return every record in tbl_vodaphone in reverse order, using id (an auto increment primary key) for the ordering... so the records should be returned in reverse order.

They are not! They are returned in the order they are before the sql is run.

I have found out that the database is MSSQL 2000 and I am running the sql via the MSSQL 2000 database admin tool.

Am I missing some vital piece of info here? Or am I making a simple, dumb mistake? I really don't understand why this is turning into such a faff. Not an MS rant or anything but my god I wish this project used a MySQL database :-(

Can someone please help?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I believe (but may be wrong) that this should return every record in tbl_vodaphone in reverse order, using id (an auto increment primary key) for the ordering... so the records should be returned in reverse order.

You are correct. This will return the contents of that table ordered by id (from the max to the min). If you are seeing different behaviour this is very odd - SQL Server (and all other databases) have always behaved as expected.


Not an MS rant or anything but my god I wish this project used a MySQL database :-(

Without wanting to fuel a flame war; in SQL Server's defense it is a much better RDBMS than MySQL. MySQL has over the years implemented "interesting" design choices, like a database engine that parses but doesn't honour FK constraints! Relational data without the relational part!
[ July 24, 2008: Message edited by: Paul Sturrock ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


SELECT * FROM tbl_vodaphone ORDER BY 'id' DESC

Why have you put your order by field name in single quotes? A quick test on SQL Server 2000 suggests this will order by the literal value 'id'...
[ July 24, 2008: Message edited by: Paul Sturrock ]
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Thank you, thank you, thank you, thank you... all so much!

SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP 10 * FROM tbl_vodaphone ORDER BY id ASC) AS tbl_vodaphone_alias_a ORDER BY id DESC) AS tbl_vodaphone_alias_b ORDER BY id ASC

This works.

P.S. Why was 'id' in quotes - I don't know? Put it down to pressure of having to get this working, lack of knowledge, mild panic, desperation...

Everyone who helped on this - thank you so much :-D
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Glad you've got it working!
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
I spoke too soon :-(

I set up the following Unit Test. Both tests are support to succeed with 5 records but both fail. They produce 950 records.

Below is my Unit Test - Could someone help me on this again please?


[ July 25, 2008: Message edited by: Ulf Dittmer ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42919
    
  68
The inner SELECT specifically asks to retrieve 955 records. The next layer then trims that down to 950. (The outer layer then just reverses the order.) So getting 950 records is what I would expect.
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Thanks Ulf,

I understand what you are saying but I don't understand, given that this approach is what MSSQL users keep telling me to use, why it doesn't return only five records.

Unless I am not seeing the wood for the trees, I do not see what is different about this code to, for example, the article above I provided a link to - this is where I am getting confused.

Is the example in the article wrong?

Thanks for your patience and help Ulf :-)
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
I think I have worked it out!

I swapped the ASC DESC part and changed 950 to 5 and it returns 5 records.

Just not sure they are the correct records - checking that now.
Darren Wilkinson
Ranch Hand

Joined: May 22, 2007
Posts: 35
Finally got there!

The ORDER BY parts need to be in this order: ASC, DESC, ASC

The first value is saying how many records to return i.e. 20

The second value is defining the upper limit i.e. 900

So the above returns records 881 through to 900 :-)

Here's the sql for anyone else who searches for a solution to this problem.



Returns records 881 through to 900

I got there in the end with your help guys - thanks very much.

[ July 25, 2008: Message edited by: Darren Wilkinson ]
[ July 25, 2008: Message edited by: Darren Wilkinson ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

Darren,
Thanks for sharing the solution.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Solved : JTDS MSSQL Pagination problem.