Thanks Martijn, Thomas, Helen

It's working now.

Helen, the sooner your book gets here the better :-)


Cheers

Niall R Scott
Systems Engineer
I.S. Support
Cameron Controls
Aberdeen
+44 1224 282440





-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: 22 March 2004 12:05
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Access to Stored Procedure

At 04:26 AM 22/03/2004 -0600, you wrote:

>Hi
>I have created a stored procedure
>CREATE PROCEDURE "TESTPROC"
>RETURNS
>(
> "ID" CHAR(30) CHARACTER SET WIN1252
>)
>AS
>DECLARE VARIABLE xpart VARCHAR(30);
>DECLARE VARIABLE NewNum INTEGER;
>BEGIN
> NewNum = GEN_ID(CCPART_GEN,1);
> xPart = Cast(Newnum as INTEGER);
> WHILE (STRLEN(xPart)<6) DO
> BEGIN
> xPart = ('0'||xPart);
> END
> ID= ('CCS-'||Xpart);
>EXIT;
>END
>
>My query is how do access this from my application or IbConsole
>
>Using
>
>SELECT ID FROM TESTPROC
>
>activates the procedure and increments the generator but I get null as a
>result.

SELECT "ID" FROM TESTPROC would do it.

Get rid of the double quotes, or use them consistently. But there is no
possible reason to use them in made-up data, anyway.

CREATE PROCEDURE TESTPROC
RETURNS
(
ID CHAR(30) CHARACTER SET WIN1252
)
AS
/* casting should not be integer */
DECLARE VARIABLE xpart VARCHAR(11); /* 30 is too much for 32-bit int */
DECLARE VARIABLE NewNum INTEGER;
BEGIN
NewNum = GEN_ID(CCPART_GEN,1);
/* xPart = Cast(Newnum as INTEGER); */
xPart = cast(NewNum as varchar(11);
WHILE (STRLEN(xPart)<6) DO
BEGIN
xPart = ('0'||xPart);
END
ID= ('CCS-'||Xpart);
EXIT;
END





Yahoo! Groups Links





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This e-mail is confidential, may contain proprietary information
of the Cooper Cameron Corporation and its operating Divisions
and may be confidential or privileged.

This e-mail should be read, copied, disseminated and/or used only
by the addressee. If you have received this message in error please
delete it, together with any attachments, from your system.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~