roblem with stored procedure after migration
Hello,
After the migration (dbimport) from Informix 7.31 11.5 we have trouble with
stored procedures.
Every time we starts the stored procedure with our application the procedure
crash with error code 674 Routine (pi_stellen) can not be resolved!!!
The fault is that, every procedure (not only this example) crashes when the
stored procedure makes an insert into with byte values!! In informix 7.31
the procedures runs without any problems.
We have tried the following:
Changed from byte into text also did not help. The error code is the same!
We have tested the other attributes and everything is OK
If we change the byte value into a varchar value the application runs! But
this is no solution for us.
Import the values via load runs too.
Sorry for my bad English and thanks!!!
Here is one of the stored procedure, p_Langnotiz is the problem:
CREATE PROCEDURE pi_stellen(
p_Kapitel SMALLINT,
p_ID SMALLINT ,
p_Begin DATETIME YEAR TO FRACTION(5) ,
p_Kurznotiz VARCHAR(100,30) ,
p_Langnotiz references byte ,
p_Stellentyp_id integer ,p_Abgang INTEGER
RETURNING INTEGER;
DEFINE GLOBAL gvsRepID char(4) DEFAULT NULL;
DEFINE HNr integer;
LET HNr = 0;
IF gvsRepId IS NULL THEN
RAISE EXCEPTION -746, 0, 'Replikations-ID fehlt';
ELSE
CALL pG_Sys_NrServer( p_ID) RETURNING HNr;
IF HNr > 0 THEN
INSERT INTO HH_Stellen
(Kapitel, ID, Begin, Kurznotiz, Langnotiz, Stellentyp_Id, Abgang )
VALUES
(p_Kapitel, p_ID, p_Begin, p_Kurznotiz, p_Langnotiz, p_Stellentyp_Id,
p_Abgang);
END IF;
END IF;
RETURN HNr;
END PROCEDURE
----
The table for the insert:
----
create table stellen
(
Kapitel smallint,
ID smallint,
Begin date not null ,
Kurznotiz varchar(100,30),
Langnotiz byte,
Stellentyp_id integer not null ,
Abgang integer
) extent size 16 next size 16 lock mode row;
There's a missing closing parenthesis at the end of the procedure's argument
list. Is that a type here or an indicator that the procedure really fails
to be created?
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Dec 1, 2009 at 1:51 AM, FABIAN HORN <fabian.horn@zivit.de> wrote:
> Hello,
>
> After the migration (dbimport) from Informix 7.31 11.5 we have trouble
> with
> stored procedures.
> Every time we starts the stored procedure with our application the
> procedure
> crash with error code 674 Routine (pi_stellen) can not be resolved!!!
> The fault is that, every procedure (not only this example) crashes when the
> stored procedure makes an insert into with byte values!! In informix 7.31
> the procedures runs without any problems.
>
> We have tried the following:
>
> Changed from byte into text also did not help. The error code is the
> same!
> We have tested the other attributes and everything is OK
> If we change the byte value into a varchar value the application runs!
> But
> this is no solution for us.
> Import the values via load runs too.
>
> Sorry for my bad English and thanks!!!
>
> Here is one of the stored procedure, p_Langnotiz is the problem:
>
> CREATE PROCEDURE pi_stellen(
> p_Kapitel SMALLINT,
> p_ID SMALLINT ,
> p_Begin DATETIME YEAR TO FRACTION(5) ,
> p_Kurznotiz VARCHAR(100,30) ,
> p_Langnotiz references byte ,
> p_Stellentyp_id integer ,> p_Abgang INTEGER
>
> RETURNING INTEGER;
>
> DEFINE GLOBAL gvsRepID char(4) DEFAULT NULL;
> DEFINE HNr integer;
> LET HNr = 0;
>
> IF gvsRepId IS NULL THEN
>
> RAISE EXCEPTION -746, 0, 'Replikations-ID fehlt';
>
> ELSE
>
> CALL pG_Sys_NrServer( p_ID) RETURNING HNr;
>
> IF HNr > 0 THEN
>
> INSERT INTO HH_Stellen>
> (Kapitel, ID, Begin, Kurznotiz, Langnotiz, Stellentyp_Id, Abgang )
>
> VALUES
>
> (p_Kapitel, p_ID, p_Begin, p_Kurznotiz, p_Langnotiz, p_Stellentyp_Id,
> p_Abgang);
>
> END IF;
> END IF;
> RETURN HNr;
>
> END PROCEDURE
>
> ----
>
> The table for the insert:
>
> ----
>
> create table stellen
> (>
> Kapitel smallint,
>
> ID smallint,
>
> Begin date not null ,
>
> Kurznotiz varchar(100,30),
>
> Langnotiz byte,
>
> Stellentyp_id integer not null ,
>
> Abgang integer
>
> ) extent size 16 next size 16 lock mode row;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747bfe22258630479abb9c2
Thanks Art,
but it is only a mistake by creating this example procedure. In the "real"
procedure is a parenthesis. Here is the corrected version:
CREATE PROCEDURE pi_stellen(
p_Kapitel SMALLINT,
p_ID SMALLINT ,
p_Begin DATETIME YEAR TO FRACTION(5) ,
p_Kurznotiz VARCHAR(100,30) ,
p_Langnotiz references byte ,
p_Stellentyp_id integer ,
p_Abgang INTEGER )
RETURNING INTEGER;
DEFINE GLOBAL gvsRepID char(4) DEFAULT NULL;
DEFINE HNr integer;
LET HNr = 0;
IF gvsRepId IS NULL THEN
RAISE EXCEPTION -746, 0, 'Replikations-ID fehlt';
ELSE
CALL pG_Sys_NrServer( p_S_Kap_Id) RETURNING HNr;
IF HNr > 0 THEN
INSERT INTO HH_Stellen
(Kapitel, ID, Begin, Kurznotiz, Langnotiz, Stellentyp_Id, Abgang )
VALUES
(p_Kapitel, p_ID, p_Begin, p_Kurznotiz, p_Langnotiz, p_Stellentyp_Id,
p_Abgang);
END IF;
END IF;
RETURN HNr;