Board index » delphi » Problem with Stored Proc, Generators, and IBX 4.62

Problem with Stored Proc, Generators, and IBX 4.62

This may sound like a stupid problem, but it has me stumped right now. I'm
probably looking right at it and don't see it. I have the following stored
procedure that I use to return a generator value:

CREATE PROCEDURE GET_NEW_USER_LOGON_ID returns (USER_LOGON_ID Integer)
AS
begin
  USER_LOGON_ID = GEN_ID(GEN_USER_LOGON_ID,1);
end

If I return USER_LOGON_ID as an Integer, everything works fine. If I change
the procedure to return Numeric(18,0) (which I understand is what you should
return for a generator value), when I execute the procedure using a
TIBStoredProcedure component I get an exception raised indicating that an
"Invalid Data Conversion" occurred. The Params property of the
TIBStoredProcedure has the one output parameter defined as DataType =
ftLargeInt. If I alter the stored procedure to return an integer everything
works fine. What the heck am I missing? How do you get the full 64 bit
integer the generator provides so you can use it?

Thanks,
Lee

 

Re:Problem with Stored Proc, Generators, and IBX 4.62


TStoredProc uses TParams.  TParams uses MS Variants.  MS Variants can not handle
Int64.  Use an IBSQL and do an "Execute Procedure ..." SQL statement.

Quote
Lee Griffin wrote:

> This may sound like a stupid problem, but it has me stumped right now. I'm
> probably looking right at it and don't see it. I have the following stored
> procedure that I use to return a generator value:

> CREATE PROCEDURE GET_NEW_USER_LOGON_ID returns (USER_LOGON_ID Integer)
> AS
> begin
>   USER_LOGON_ID = GEN_ID(GEN_USER_LOGON_ID,1);
> end

> If I return USER_LOGON_ID as an Integer, everything works fine. If I change
> the procedure to return Numeric(18,0) (which I understand is what you should
> return for a generator value), when I execute the procedure using a
> TIBStoredProcedure component I get an exception raised indicating that an
> "Invalid Data Conversion" occurred. The Params property of the
> TIBStoredProcedure has the one output parameter defined as DataType =
> ftLargeInt. If I alter the stored procedure to return an integer everything
> works fine. What the heck am I missing? How do you get the full 64 bit
> integer the generator provides so you can use it?

> Thanks,
> Lee

--
Jeff Overcash (TeamB)   I don't think there are any Russians
(Please do not email    And there ain't no Yanks
 me directly unless     Just corporate criminals
 asked.  Thank You)     Playing with tanks.  (Michael Been)

Re:Problem with Stored Proc, Generators, and IBX 4.62


I can't get this to work. I get an error:

Dynamic SQL Error
SQL Error Code =-104
Token Unknown - Line 1 Character 48
RETURNING_VALUES

According to the Interbase documentation Language Reference page 113 the
syntax for and Execute Procedure command should be:

DSQL form:
EXECUTE PROCEDURE name [ param [, param .]]
[RETURNING_VALUES param [, param .]]

My SQL statement looks like this:

EXECUTE PROCEDURE GET_USER_LOGIN_ID RETURNING_VALUES :UserLogonID

What am I doing wrong?

Jeff Overcash (TeamB) <overc...@onramp.net> wrote in message
news:3B7DD80F.EAE95A5D@onramp.net...

Quote
> TStoredProc uses TParams.  TParams uses MS Variants.  MS Variants can not
handle
> Int64.  Use an IBSQL and do an "Execute Procedure ..." SQL statement.

> Lee Griffin wrote:

> > This may sound like a stupid problem, but it has me stumped right now.
I'm
> > probably looking right at it and don't see it. I have the following
stored
> > procedure that I use to return a generator value:

> > CREATE PROCEDURE GET_NEW_USER_LOGON_ID returns (USER_LOGON_ID Integer)
> > AS
> > begin
> >   USER_LOGON_ID = GEN_ID(GEN_USER_LOGON_ID,1);
> > end

> > If I return USER_LOGON_ID as an Integer, everything works fine. If I
change
> > the procedure to return Numeric(18,0) (which I understand is what you
should
> > return for a generator value), when I execute the procedure using a
> > TIBStoredProcedure component I get an exception raised indicating that
an
> > "Invalid Data Conversion" occurred. The Params property of the
> > TIBStoredProcedure has the one output parameter defined as DataType =
> > ftLargeInt. If I alter the stored procedure to return an integer
everything
> > works fine. What the heck am I missing? How do you get the full 64 bit
> > integer the generator provides so you can use it?

> > Thanks,
> > Lee

> --
> Jeff Overcash (TeamB)   I don't think there are any Russians
> (Please do not email    And there ain't no Yanks
>  me directly unless     Just corporate criminals
>  asked.  Thank You)     Playing with tanks.  (Michael Been)

Re:Problem with Stored Proc, Generators, and IBX 4.62


Don't include RETURNING clause.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Other Threads