--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 10:12 PM 20/09/2003 +0000, you wrote:
>
> > >
> > > > execute procedure newrowid( 'person', 1 )
> > > >
> > > > I get this:
> > > >
> > > > ISC ERROR CODE:335544327
> > > >
> > > > ISC ERROR MESSAGE:
> > > > invalid request handle
> > > >
> > > > STATEMENT:
> > > > TIBOInternalDataset:
> > > > "<TApplication>.frmMain.dlgWisql.<TIBOQuery>.<
> >TIBOInternalDataset>."
>
> Spencer,
> I get exactly the same thing with IBOConsole, though I have an older

build

> than yours (1.1.2.21). It runs fine in IB_SQL.
>
> In IBOConsole, the generator does get updated, because the generator
> itself is outside of transaction control - it fires even though the SP
> itself fails (generators never roll back). In terms of its own
> architecture, it is set up to expect a result set in the return

structure

> and it excepts because the server can't return what it asked for.

(EXECUTE

> statements don't return multi-row resultsets, so the parameter

structure

> associated with the statement handle isn't valid).
>
> IBOConsole uses IBO for data access, as IB_SQL does. The difference is
> that IB_SQL uses IBO's "native" architecture which knows how to

handle the

> invalid procedure call and read the return values (if any). However,
> IBOConsole uses different data access components, based on Borland's
> TDataset class, for compatibility with IBConsole, also
> TDataset-descended. The IBO TDataset components *can* be configured to
> deal with the invalid procedure call but it appears that it hasn't been
> done - the reference to the TIBOInternalDataset is wtiness to that.

Maybe

> a later build of IBOConsole has fixed it...
>
> The old Interclient was built on the same assumptions as the original
> IBConsole code (on which IBOConsole was based) so the same "precious
> behaviour" from it isn't surprising. Both will play nice if you use

SELECT

> for a procedure call that has return parameters---your procedure

call won't

> except if you call it with SELECT instead of EXECUTE:
>
> SELECT "ID" from "NEWROWID" ('person', 1);
>
> However, because of the way you wrote the procedure, it will update the
> generator, but it won't return the value (you just get null). This

one will:

>
> CREATE PROCEDURE NEWROWID1
> (
> TABLENAME VARCHAR(31),
> STEP INTEGER
> )
> RETURNS
> (
> ID NUMERIC(18, 0)
> )
> AS
> BEGIN
> if ( tableName = 'person' ) then /* What if 'PERSON' comes

through? */

> BEGIN
> ID = GEN_ID(PERSONR_ID_GEN, :STEP );
> SUSPEND; /* the magic word */
> END
> END
>
> One more thing -- although it wasn't an issue here -- you need to

get to

> grips with these quoted identifiers. You don't need to use them for
> anything unless you want to use "illegal" or case-sensitive names. It
> makes no sense whatsoever to write stored procedures that have quoted
> variable names, or to declare generators with quotes...
>
> heLen

Thanks for the help - it sort of works as a select (not a stored
proc), but has the unfortunate side effect of invoking the generator
(or the proc - I can't tell which) twice! So every call to the proc
effectively increments the step by two. Wierd.

Also, I can remove the single quotes from the stored proc - no harm no
foul. But the other quotes were placed there by IBOConsole, not the
author.