Except Sylvain's opening requirement states that he must have this
directly at the SELECT level. He wants this on the fly as the result
of a query, so to use the identity column approach he would need to
dump the results of his query into a temporary table with an identity
column, and then do the select with the corresponding "WHERE rowid > x
AND rowid < y" clause to implement the equivalent of a LIMIT...OFFSET.
But Derby doesn't support identity columns in temporary tables, so
this rather complicated approach won't work.
Sylvain, I think your only real option is to handle this outside the
SELECT statement at the application layer. Worst-case scenario, you
implement your pager function by calling fetch() until you reach _x_,
then fetch() and keep rows until you reach _y_.
A pseudo-implementation in PHP (minus error-checking etc) would work
something like:
function pager($stmt, $limit, $offset) {
$counter = 0;
$rows = array();
while ($counter < $offset) {
db2_fetch_row($stmt); // simply advances result set pointer to the next row
$counter++;
}
$counter = 0;
while ($counter < $limit) {
$rows[] = db2_fetch_array($stmt); // add the next row to the results array
$counter++;
}
}
And for a worst-case scenario, it turns out that this isn't really all
that bad: there is almost no network traffic required to simply move
the fetch() pointer ahead by a row when you're not actually retrieving
a row.
Dan
On 2/20/06, Craig L Russell <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I think that most of the databases you might want to use allow you to define
> a column explicitly where the contents are managed by the database itself
> but can be used by the user to imbricate results.
>
> So if you are willing to forego
> a pseudo-columnn [sic] (let's say : "row") and instead use a real-column,
> then I think the answer is yes. Derby has the " MYROW INTEGER NOT NULL
> GENERATED ALWAYS AS IDENTITY" construct that generates row values for you.
>
> Craig
>
>
>
> On Feb 20, 2006, at 7:05 AM, Sylvain RICHET wrote:
> (few days later...)
>
> [Michael said :]
> >> Again, for what you want, rowId is not going to work....
> >> Imagine you have a table. You do a select on the table and you select
> rows 1,
> >> 5, 10, 11,13,17 ... How does this help you when you want to fetch the
> first n
> >> rows?
> >> I think you need to go back and rethink your design.
>
> The way i want to use the rowid would be in an imbricated select
>
> SELECT * FROM(SELECT ..., [rowid] AS n FROM ... WHERE ... ) WHERE n BETWEEN
> $start AND $end;
>
> For instance, this is possible with ORACLE, using its ROWNUM pseudo-column
> But certainly not the ROWID pseudo-column : because since rows can migrate
> from location-to-location when they are updated,
> ROWID should never be stored an never be counted on to be the same in any
> database.
>
> ... that's why Craig said :
> >> If you're using this for logging, and keeping track of which records you
> have already processed, this technique might work.
> >> Since the column is visible and won't change after insert, the same
> technique can be used with other databases (e.g. use a sequence on
> Oracle...)
>
>
> [Øystein said :]
> >> I am bit confused about what you need a "row number" pointer for.
> >> Oracle's RowID and MySql's LIMIT seems like quite different features.
> Sorry, i mentionned the ROWID instead of ROWNUM, which are both Oracle
> pseudo-columns.
>
>
> So my initial question should have been :
>
> Is DERBY implementing a pseudo-columnn (let's say : "row") which authorize
> to do something like :
> SELECT * FROM(SELECT ..., [row] AS n FROM ... WHERE ... ) WHERE n
> BETWEEN $start AND $end
> ... in order to get a paging system on the results ?
>
>
> On 2/19/06, Craig L Russell <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> >
> > You might consider using a column that the database automatically
> increments for each inserted row. Then you could select ranges of this
> column values.
> >
> >
> > It's not clear from your description whether you know in advance that you
> want a certain range of rows that were inserted, or exactly what.
> >
> >
> > If you're using this for logging, and keeping track of which records you
> have already processed, this technique might work. Since the column is
> visible and won't change after insert, the same technique can be used with
> other databases (e.g. use a sequence on Oracle...)
> >
> >
> > Craig
> >
> >
> >
> > On Feb 16, 2006, at 11:47 PM, Sylvain RICHET wrote:
> >
> > Hi everyone,
> >
> > In a selection statement, i would like to get blocks of records.
> > Thus, i need to filter records by a "row number", directly at the SELECT
> level.
> >
> > It seems that the way to address a row number is not (SQL) standard.
> > (different "proprietary" implementations)
> >
> > In Oracle, there is the "rowid".
> > In MySQL, the "LIMIT" clause can do it.
> > In SQL Server, i think there is the "ROW_NUMBER() OVER..."
> > In DB2 (on AS/400) , there is the "RRN" (Relative Record Number)...
> >
> > What about Derby database ?
> > How is it implemented on this server ?
> >
> > I know i could use
> >
> > Thanks in advance.
> >
> >
> > Craig Russell
> > Architect, Sun Java Enterprise System
> http://java.sun.com/products/jdo
> > 408 276-5638 mailto:[EMAIL PROTECTED]
> > P.S. A good JDO? O, Gasp!
> >
> >
>
>
>
>
> Craig Russell
>
> Architect, Sun Java Enterprise System
> http://java.sun.com/products/jdo
>
> 408 276-5638 mailto:[EMAIL PROTECTED]
>
> P.S. A good JDO? O, Gasp!
>
>
