Task: implement paging of database records appropriate for various RDBMS. Method should work with mainstream engines - MSSQL2000+, Oracle, MySql, etc.
Do not publish RDBMS specific solutions, I understand how to implement this for the majority of the modern database engines. I am searching for the universal solution. Temporary tables based solutions come in my opinion right now.
I am searching for SQL solution, not third party library.
Probably the most natural and efficient method of doing paging is applying the LIMIT/OFFSET (TOP in Sybase world) construct. A DBindependent way would need to know which engine it's running on and apply the correct SQL construct.
A minimum of, this is the way I have seen it completed in DB independent libraries' code. You are able to abstract away the paging logic when you obtain the data in the engine using the specific query.
If you're really searching for just one, one SQL sentence solution, would you show that which you are thinking about? Such as the SQL for that temp table solution. That will most likely enable you to get more relevant suggestions.
I needed to determine what had you been thinking because I could not see a method to get it done with temp tables and never make use of a engine specific construct. You used specific constructs within the example. I still aren't seeing a method to implement paging within the database with only (implemented) standard SQL. You can bring the entire table in standard SQL and page within the application, but that's clearly stupid.
Therefore the question would certainly be a lot more like "It is possible to method to implement paging without needing LIMIT/OFFSET or equivalent?" and that i reckon that the reply is "Sanely, no." You could attempt using cursors but you'll be taken in by database specific sentences/behavior there too.
A wacko (read stupid) concept that just happened in my experience is always to give a page column towards the table, say create table test (id int, title varchar, phone varchar, page int) and you could possibly get the first page with choose * from table where page = 1. But which means needing to add code to keep that column, which, again could only be carried out by either getting the entire database or using database specific constructs. That besides needing to give a different column per each possible ordering and several other defects.
I can not provide proof, however i think you simply can't get it done sanely.
There will be a universal solution if SQL specifications had incorporated paging like a standard. The requirement of any RDBMS language to become known as an RDBMS language doesn't include paging support too.
Many database items support SQL with proprietary extensions towards the standard language. A number of them support paging like MySQL using the limit clause, Rowid with Oracle each handled in a different way. Other DBMS's will have to give a area known as rowid or something like that like this.
I dont think you'll have a universal solution (anybody is free of charge to prove me wrong hereopen to talk about) unless of course it's included in the database system itself or unless of course there's a business say ABC that utilizes Oracle, MySQL, SQL Server plus they choose to have the various database systems provide their very own implementation of paging by their database designers supplying a universal interface for that code that utilizes it.
Proceed as always:
Begin by applying it based on the standard. After which handle the corner cases, i.e. the DBMSes which don't implement the conventional. How to deal with the corner cases is dependent in your development atmosphere.
You're searching for a "universal" approach. Probably the most universal method to paginate is by using cursors, but cursor-based pagination don't fit perfectly having a non-stateful atmosphere just like a web application.
I have discussed the conventional and also the implementations (including cursors) here: http://troels.arvin.dk/db/rdbms/#select-limit-offset
SubSonic can perform this for you personally should you if you're able to tolerate Free... http://subsonicproject.com/querying/webcast-using-paging/
Apart from that I understand NHib does too
JPA allows you're doing so using the Query class:
Query q = ...; q.setFirstResult (0); q.setMaxResults (10);
provides you with the very first 10 leads to the end result set.
If you prefer a DBMS independent raw SQL solution, I am afraid you are at a complete loss. All of the suppliers get it done in a different way.