At 12:37 AM 1/11/2006, you wrote:

>I have a pretty simple query that works just fine when executed via
>IBExpert's SQL Editor, but when I put it into a stored procedure it fails
>and returns no results.
>
>This is in Firebird 1.5 Super Server on Linux.
>
>The stored procedure is:
>
>CREATE PROCEDURE SOUND_STORAGE_DETAILS_PRC (
> a_sound_no integer)
>returns (
> r_sound_name varchar(100) character set win1251,
> r_sound_desc varchar(5000) character set win1251,
> r_sound_server varchar(100) character set win1251,
> r_sound_volume varchar(100) character set win1251,
> r_sound_filename varchar(100) character set win1251,
> r_sound_duration time,
> r_sound_redirect varchar(250) character set win1251)
>as
>begin
> for
> select first 1 skip 0
> SOUND.SOUND_SHORT_NAME,
> SOUND.SOUND_DESC,
> SOUND_STORAGE.SS_SERVER,
> SOUND_STORAGE.SS_VOLUME,
> SOUND_STORAGE.SS_FILENAME,
> SOUND_STORAGE.SS_REDIRECT,
> SOUND.ITUNES_DURATION
>FROM
> SOUND
> INNER JOIN SOUND_STORAGE ON (SOUND.SOUND_NO = SOUND_STORAGE.SOUND_NO)
>WHERE
> (SOUND.SOUND_NO = :A_SOUND_NO)
> into :R_SOUND_NAME, :R_SOUND_DESC, :R_SOUND_SERVER, :R_SOUND_VOLUME,
>:R_SOUND_FILENAME, :r_sound_redirect, :R_SOUND_DURATION
> do
> begin
> suspend;
> end
>End
>
>---
>What's really strange is that it works fine 99.9% of the time. But with
>certain data in the tables, it doesn't find it. But if I run the exact same
>query manually it works just fine.

It's not the exact same query if you run it as a DSQL query. DSQL
isn't in a FOR loop.

>Does anyone have any idea as to why this could happen? All input and output
>arguments match exactly to the table specs and even if they didn't I would
>expect this to fail on all cases rather than just on some cases.

The problem here is that there is only one row output from SELECT
FIRST so your FOR loop is going to return zilch whenever the output
has multiple rows. Don't use SELECT FIRST in PSQL! Even when it
returns the "expected" result, it's unnecessarily noisy. PSQL can
get your one row (or any n rows) much more efficiently:

CREATE PROCEDURE SOUND_STORAGE_DETAILS_PRC (
a_sound_no integer)
returns (
r_sound_name varchar(100) character set win1251,
r_sound_desc varchar(5000) character set win1251,
r_sound_server varchar(100) character set win1251,
r_sound_volume varchar(100) character set win1251,
r_sound_filename varchar(100) character set win1251,
r_sound_duration time,
r_sound_redirect varchar(250) character set win1251)
as
declare variable counter integer = 0;
begin
for
select
SOUND.SOUND_SHORT_NAME,
SOUND.SOUND_DESC,
SOUND_STORAGE.SS_SERVER,
SOUND_STORAGE.SS_VOLUME,
SOUND_STORAGE.SS_FILENAME,
SOUND_STORAGE.SS_REDIRECT,
SOUND.ITUNES_DURATION
FROM
SOUND
INNER JOIN SOUND_STORAGE ON (SOUND.SOUND_NO = SOUND_STORAGE.SOUND_NO)
WHERE
(SOUND.SOUND_NO = :A_SOUND_NO)
into :R_SOUND_NAME, :R_SOUND_DESC, :R_SOUND_SERVER, :R_SOUND_VOLUME,
:R_SOUND_FILENAME, :r_sound_redirect, :R_SOUND_DURATION
do
begin
if (counter > 0) then exit;
suspend;
counter = counter + 1;
end
End

./heLen