|Subject||Re: [firebird-support] Strange behavior in stored procedure|
> >I have a pretty simple query that works just fine when executed viasame
> >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)
> > 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
> > SOUND
> > INNER JOIN SOUND_STORAGE ON (SOUND.SOUND_NO = SOUND_STORAGE.SOUND_NO)
> > (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
> >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
> >query manually it works just fine.output
> 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
> >arguments match exactly to the table specs and even if they didn't Iwould
> >expect this to fail on all cases rather than just on some cases.Why? Can you explain that?
> 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.
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Database development questions? Check the forum!