Calling stored function
Hello,
I have following function in my Informix database:
CREATE PROCEDURE climbertops(c varchar(128))
RETURNING row
DEFINE result row(mountain varchar(128),climber varchar(128),year
int);
FOREACH
SELECT *
INTO result
FROM ontop
WHERE climber=c
RETURN result
WITH RESUME; END FOREACH;
END PROCEDURE;
But when trying too execute this (execute procedure climbertops('Josef
Rakoncaj');) I get the message:
9628: Type () not found.I've tried to declare host variable to execute function into this
variable:
EXEC SQL BEGIN DECLARE SECTION;
row(varchar(128), varchar(128), int) climber;
EXEC SQL END DECLARE SECTION;
execute function climbertops('Josef Rakoncaj') into :climber;but I always get " 201: A syntax error has occurred." error.
Even simple copy and paste from Informix tutorial (
EXEC SQL BEGIN DECLARE SECTION;
int p_int;
EXEC SQL END DECLARE SECTION;
) causes the same error.
Can anybody help me with this (for sure) easy problem?
Agata
Informix deoe not support a generic row type as return type of a
stored procedure. You need to specify the explicit row type
definition. For example,
CREATE PROCEDURE climbertops(c varchar(128))
RETURNING row(mountain varchar(128) , climber varchar(128) , yearint );
thanx
Prasad
On Jul 28, 4:31 am, agad <a...@seznam.cz> wrote:
> Hello,
> I have following function in my Informix database:
> CREATE PROCEDURE climbertops(c varchar(128))
> RETURNING row>
> DEFINE result row(mountain varchar(128),climber varchar(128),year
> int);
>
> FOREACH
> SELECT *
> INTO result
> FROM ontop
> WHERE climber=c> RETURN result
> WITH RESUME;
> END FOREACH;
>
> END PROCEDURE;
>
> But when trying too execute this (execute procedure climbertops('Josef
> Rakoncaj');) I get the message:
> 9628: Type () not found.> I've tried to declare host variable to execute function into this
> variable:
> EXEC SQL BEGIN DECLARE SECTION;
> row(varchar(128), varchar(128), int) climber;
> EXEC SQL END DECLARE SECTION;
> execute function climbertops('Josef Rakoncaj') into :climber;> but I always get " 201: A syntax error has occurred." error.
> Even simple copy and paste from Informix tutorial (
> EXEC SQL BEGIN DECLARE SECTION;
> int p_int;
> EXEC SQL END DECLARE SECTION;
> ) causes the same error.
>
> Can anybody help me with this (for sure) easy problem?
>
> Agata
Hello, thank you for the answer. It works, when I add variable
initialization:
LET result = ROW(NULL::VARCHAR(128), NULL::VARCHAR(128),
NULL::INTEGER);
Without it I get an error:
696: Variable (result) has undefined value.
Another solution I got, was to declare new row type:
CREATE ROW TYPE result_rt (mountain varchar(128),climber varchar
(128),year int);
On 28 čnc, 23:47, Prasad <pras...@us.ibm.com> wrote:
> Informix deoe not support a generic row type as return type of a
> stored procedure. You need to specify the explicit row type
> definition. For example,
> CREATE PROCEDURE climbertops(c varchar(128))
> RETURNING row(mountain varchar(128) , climber varchar(128) , year> int );
>
> thanx
> Prasad
>
> On Jul 28, 4:31 am, agad <a...@seznam.cz> wrote:
>
> > Hello,
> > I have following function in my Informix database:
> > CREATE PROCEDURE climbertops(c varchar(128))
> > RETURNING row>
> > DEFINE result row(mountain varchar(128),climber varchar(128),year
> > int);
>
> > FOREACH
> > SELECT *
> > INTO result
> > FROM ontop
> > WHERE climber=c> > RETURN result
> > WITH RESUME;
> > END FOREACH;
>
> > END PROCEDURE;
>
> > But when trying too execute this (execute procedure climbertops('Josef
> > Rakoncaj');) I get the message:
> > 9628: Type () not found.> > I've tried to declare host variable to execute function into this
> > variable:
> > EXEC SQL BEGIN DECLARE SECTION;
> > row(varchar(128), varchar(128), int) climber;
> > EXEC SQL END DECLARE SECTION;
> > execute function climbertops('Josef Rakoncaj') into :climber;> > but I always get " 201: A syntax error has occurred." error.
> > Even simple copy and paste from Informix tutorial (
> > EXEC SQL BEGIN DECLARE SECTION;
> > int p_int;
> > EXEC SQL END DECLARE SECTION;
> > ) causes the same error.
>
> > Can anybody help me with this (for sure) easy problem?
>
> > Agata