• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Solved : JTDS MSSQL Pagination problem.

 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry - should have replied rather than edited
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try using aliases. e.g :
 
Darren Wilkinson
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

(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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Glad you've got it working!
 
Darren Wilkinson
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Darren,
Thanks for sharing the solution.
 
reply
    Bookmark Topic Watch Topic
  • New Topic