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