At 11:20 AM 13-08-02 +0200, you wrote:

>Hi everyone! I have the following problem:
>
>I have a stored procedure which retrieves news from table NEWS. This
>procedure
>is called from a Php script. I want it to give me FIRST N SKIP M rows, but I
>need to pass those N and M values as input parameters to the stored
>procedure. I have tried something like this:
>
>SELECT FIRST :P_FIRST SKIP :P_SKIP *
>FROM NEWS
>WHERE [..]
>
>But I get a token unknown error. What's wrong? Can't I parametrize SELECT
>fields? I mean, those values (:P_FIRST and :P_SKIP) will change as user click
>the link labeled "Older news", so they will be usually N = 5 and M a multiple
>of 5, but I could change that value depending on the web design or anything.
>
>Any hints?

Your statement worked fine for me in IB_SQL as an interactive
statement. You can get your set by passing the parameters directly to the
prepared statement. However, in this procedure, it gives the same error
you report:

create procedure get_some_rows (m_first integer, n_skip integer)
returns (oid integer, registration varchar(8))
as
begin
for select first m_first skip n_skip * from aircraft
into :oid, :registration do
begin
suspend;
end
end

I don't expect it to work. for...select is a cursor, operating on one row
at a time as it is output. The query returns a number of rows together,
not rows one-by-one. If you have some real reason for wanting to use a
stored proc, do it this way:

create procedure get_some_rows (m_first integer, n_skip integer)
returns (oid integer, registration varchar(8))
as
declare variable rows_returned integer;
declare variable rows_skipped integer;
begin
rows_returned = 0;
rows_skipped = 0;
for select * from aircraft
ORDER BY registration
into :oid, :registration do
begin
if (rows_skipped >= n_skip) then
begin
if (rows_returned > m_first) then
exit;
else
rows_returned = rows_returned + 1;
suspend;
end
else
rows_skipped = rows_skipped + 1;
end
end



All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________