> >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.

Why? Can you explain that?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com