How about a LIMIT clause (LIMIT row_count OFFSET offset) that can be used to
constrain the number of rows returned by the SELECT statement like MySQL,
PostgreSQL.

Dorn B.


----- Original Message -----
From: "Sosnowski Robert" <robert.sosnowski@bzwbk.pl>
To: <informix-list@iiug.org>
Sent: Wednesday, February 11, 2004 05:14 AM
Subject: Re: Whatcha' wanta have?????


> As for 9.6 I see 2 important things:
> 1. Web services
> 2. BLOB literals
>
> ad. 1
> Idea behind stored procedures in SQL database is very similar to that of
Web
> services. So it seems quite natural that database should expose its stored
> procedures as WEB services.
> I see that this is:
> - useful even in Intranet,
> - becoming standard.
> Currently in my company we will migrate our database application to .NET.
> Even for me as the beginner in .NET concept of exposing application API in
> Web Services instead of stored procedures seems straightforward. It would
be
> even easier if database would support it natively.
>
> It is becoming standard. DB2 and Oracle already have it. Also BEA Tuxedo
> middleware. MSSQL and Sybase announced. Don't let Informix be an isolated
> data island!
>
> There are some peculiarities with Web services: there are two different
> reference platforms:
> - .Net
> - Apache-Tomcat (java).
> As Informix works not only on NT then choice is clear. I'm only afraid
about
> java: I would prefer implementation not based on java if possible. I just
> prefer simpler but faster and more reliable solution.
>
> DB2 also have interesting extension: you can use web service similar as
> stored procedure.
> In Informix it extends stored procedure implementation method. Currently
> Informix have:
> - SPL;
> - J/SQL (Internet Foundation only);
> - C databalades.
> I want to add to this list:
> - Web Services.
>
> ad 2.
> For BYTE, TEXT, CLOB, and BLOB types there are no literals. Why? It just
> complicates simple things.
> For TEXT and CLOB it would be the same literal as for char(30000) column.
> For BYTE and BLOB it have to be some hex literal.
>
>
>
> Best Regards,
>
>
> Robert Sosnowski
>
> IT specialist CK BZ WBK,
> e-mail: Robert.Sosnowski@bzwbk.pl
> ul. Pl. Andersa 5, 61-894 Poznañ
> phone +48 61 856 54 09
> fax +48 61 856 52 36
>
> >>As a mild diversion from the IDS-DB2 conversion thread, its time for one
> of
> >>my more favorite exercises. ;-)
> >>
> >>We are nearing the end of the coding cycle for IDS 9.5. We've got a
whole
> >>bunch of really cool stuff in place and - well - its time to get input
> from
> >>you guys as to what you want to see in the 9.6 release.
> >>
> >>Now, I know that everyone's favorite thing is going to be "marketing",
but
> >>I'm in development. So I need to talk features and functionality. So
> feel
> >>free to send them on in.
> >>
> >>Just an FYI - I'll be away for a while and won't be able to get email
via
> my
> >>comcast email address. But, I'll be following the newsgroup rather
> closely.
> >>
> >>Also, next week I'll be in some planning meeting. So getting responses
> back
> >>fairly quickly would really help.
> >>
> >>Thanks
> >>
> >>M.Pruet
>
>
> ===========================
> Wiadomo¶æ ta oraz wszelkie za³±czone do niej pliki s± poufne i mog± byæ
prawnie chronione.
> Je¿eli nie jest Pan/Pani zamierzonym adresatem niniejszej wiadomo¶ci, nie
mo¿e Pan/Pani jej ujawniaæ,
> kopiowaæ, dystrybuowaæ ani te¿ w ¿aden inny sposób udostêpniaæ lub
wykorzystywaæ.
> O b³êdnym zaadresowaniu wiadomo¶ci prosimy niezw³ocznie poinformowaæ
nadawcê i usun±æ wiadomo¶æ.
> ===========================
> This email and any attached files are confidential and may be legally
privileged.
> If you are not the intended recipient, any disclosure, reproduction,
copying, distribution,
> or other dissemination or use of this communication is strictly
prohibited. If you have received this transmission in error please notify
the sender immediately and then delete this email.
>
>
> sending to informix-list
>
>
> sending to informix-list
> sending to informix-list
>


sending to informix-list


sending to informix-list

Dorn Bhechsonggram wrote:
> How about a LIMIT clause (LIMIT row_count OFFSET offset) that can be
> used to constrain the number of rows returned by the SELECT statement
> like MySQL, PostgreSQL.

We have

select first 100 * from ....

however there is no offset; noticable by its absence.

I can see why; it almost makes the engine pull up the first part of the
selection set anyway. I wonder if there is a smart way to do it in Informix.
How do other engines avoid fetching th first 100 records just to get to the
2nd lot of 100 records?

"Andrew Hamm" <ahamm@mail.com> wrote in message
news:c0f04v$16bb62$1@ID-79573.news.uni-berlin.de...
> Dorn Bhechsonggram wrote:
> > How about a LIMIT clause (LIMIT row_count OFFSET offset) that can be
> > used to constrain the number of rows returned by the SELECT statement
> > like MySQL, PostgreSQL.
>
> We have
>
> select first 100 * from ....
>
> however there is no offset; noticable by its absence.
>
> I can see why; it almost makes the engine pull up the first part of the
> selection set anyway. I wonder if there is a smart way to do it in
Informix.
> How do other engines avoid fetching th first 100 records just to get to
the
> 2nd lot of 100 records?
>
>

Paging through your result-set is a problem for the client - NOT the
database.

"Richard Harnden" <richard.harnden@lineone.net> wrote in message
news:402ec158$0$52400$65c69314@mercury.nildram.net ...
>
>
> Paging through your result-set is a problem for the client - NOT the
> database.
>

While that might be true, if this is provides functionality that would be
useful to a whole bunch of customers, we would consider implementing it.
We're really trying to be sensitive to customer needs.

Thanks for all the comments.

M.P.
>

Richard Harnden wrote:
>
> Paging through your result-set is a problem for the client - NOT the
> database.

I'd have to agree with that, yet MS-SQL and a few others (?) offer something
like

select first N offset O ......

but using their own syntax.

I have assumed that they all read the first 2000 rows before giving you your
desired 100 rows at offset 2000. However I think I've read that they use
some sort of persistence to implement this; I guess it's gambing on the fact
that you might call for the next block of rows if you've called for the
first. *shrug*

Was your comment a statement of a "should" that rejects this feature in the
engine, or was it a cryptic statement which says that the client-side of MS
engines does half the work?

"Andrew Hamm" <ahamm@mail.com> wrote in message
news:c0ov8i$18tm68$1@ID-79573.news.uni-berlin.de...
> Richard Harnden wrote:
> >
> > Paging through your result-set is a problem for the client - NOT the
> > database.
>
> I'd have to agree with that, yet MS-SQL and a few others (?) offer
something
> like
>
> select first N offset O ......
>
> but using their own syntax.

I could be wrong, but I think it's something like:
ORDER BY <col-list> [LIMIT <num-rows> [OFFSET <num-rows>] ]

ie, asking for the first n rows only makes any sense if you order your
results.

>
> I have assumed that they all read the first 2000 rows before giving you
your
> desired 100 rows at offset 2000. However I think I've read that they use
> some sort of persistence to implement this; I guess it's gambing on the
fact
> that you might call for the next block of rows if you've called for the
> first. *shrug*
>
> Was your comment a statement of a "should" that rejects this feature in
the
> engine, or was it a cryptic statement which says that the client-side of
MS
> engines does half the work?

That a select statement is supposed to return a relation, and that FIRST,
LIMIT-OFFSET (and CONNECT-BY) are operations that require a cursor. That
they only pretend to be set-operations. And that you shouldn't expect to be
able to do it in sql.

Cryptically, that sql should evolve towards Tutorial-D, not away from it.

I'd guess that this kind of functionality is most often requested by people
writing search forms for web pages, which makes persistence a problem. But
it's a problem for cgi, however inconvenient it might be.

Of course, if every other database vendor provides this functionality then
Informix doesn't really have any choice but to do so as well.

Richard Harnden wrote:

> "Andrew Hamm" <ahamm@mail.com> wrote in message
> news:c0ov8i$18tm68$1@ID-79573.news.uni-berlin.de...
>
>>Richard Harnden wrote:
>>
>>>Paging through your result-set is a problem for the client - NOT the
>>>database.
>>
>>I'd have to agree with that, yet MS-SQL and a few others (?) offer
>
> something
>
>>like
>>
>>select first N offset O ......
>>
>>but using their own syntax.
>
>
> I could be wrong, but I think it's something like:
> ORDER BY <col-list> [LIMIT <num-rows> [OFFSET <num-rows>] ]
>
> ie, asking for the first n rows only makes any sense if you order your
> results.
>
>
>>I have assumed that they all read the first 2000 rows before giving you
>
> your
>
>>desired 100 rows at offset 2000. However I think I've read that they use
>>some sort of persistence to implement this; I guess it's gambing on the
>
> fact
>
>>that you might call for the next block of rows if you've called for the
>>first. *shrug*
>>
>>Was your comment a statement of a "should" that rejects this feature in
>
> the
>
>>engine, or was it a cryptic statement which says that the client-side of
>
> MS
>
>>engines does half the work?
>
>
> That a select statement is supposed to return a relation, and that FIRST,
> LIMIT-OFFSET (and CONNECT-BY) are operations that require a cursor. That
> they only pretend to be set-operations. And that you shouldn't expect to be
> able to do it in sql.
>
> Cryptically, that sql should evolve towards Tutorial-D, not away from it.
>
> I'd guess that this kind of functionality is most often requested by people
> writing search forms for web pages, which makes persistence a problem. But
> it's a problem for cgi, however inconvenient it might be.
>
> Of course, if every other database vendor provides this functionality then
> Informix doesn't really have any choice but to do so as well.
>
>
I think for this requirement scrollable cursors are the right answer.
What happens there is that the resultset gets materialized on the server
and the client can then browse through it by position.
This allows the server to only materialzie to the current high
watermark, without having to redo the work for the next request, as it
would have to do with a pure SQL solution which would resubmit similar
statements over and over again.
(Scrollable cursor scan do other nifty things like optimistic locked etc..)

Within the realms of normal SQL I think the standard provides FETCH
FIRST n ROWS ONLY. I haven't heard of any offset clause. But all this is
really syntactic sugar around the row_number() over() OLAP function that
can be used to filter whatever you please..

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:
>>
> I think for this requirement scrollable cursors are the right answer.
> What happens there is that the resultset gets materialized on the
> server and the client can then browse through it by position.
> This allows the server to only materialzie to the current high
> watermark, without having to redo the work for the next request, as it
> would have to do with a pure SQL solution which would resubmit similar
> statements over and over again.
> (Scrollable cursor scan do other nifty things like optimistic locked
> etc..)

But when it's based on discrete statements, when does the engine know it's
ok to discard the result set? Or will it be dependent on the same prepared
cursor being used each time?

Andrew Hamm wrote:

> Serge Rielau wrote:
>
>>I think for this requirement scrollable cursors are the right answer.
>>What happens there is that the resultset gets materialized on the
>>server and the client can then browse through it by position.
>>This allows the server to only materialzie to the current high
>>watermark, without having to redo the work for the next request, as it
>>would have to do with a pure SQL solution which would resubmit similar
>>statements over and over again.
>>(Scrollable cursor scan do other nifty things like optimistic locked
>>etc..)
>
>
> But when it's based on discrete statements, when does the engine know it's
> ok to discard the result set? Or will it be dependent on the same prepared
> cursor being used each time?
>
>
Hmm, I think I see where you're going. You try to do this stateless?
I suppose this is where middleware comes into play....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab