Error in Stored proc (spent 6 hours on it, please help)

[ermagarden]

I've tried whatever i can think of to figure out why i get this error...

get error SQL State: 22001, Message: [SQL0303] Host variable *N not compatible. Cause . . . . . : A FETCH, SELECT, CALL, SET, VALUES INTO, or GET DIAGNOSTICS cannot be performed because the data type of host variable *N is not compatible with the data type of the corresponding list item....

I looked here:

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/tstudio/db2_400/CLI/sqlmsg/allhtml.htm

It says to make sure data types are compatible. I've used different fields, took the Where clause out, that field is char(10) , brain is fried, you name it, i have tried it and still dont know why i get this. This is part of my stored proc:

---------------------

Create Procedure .InterTest4
  (
  OUT myValue char(1)
  )
  LANGUAGE SQL
 Not Deterministic
 --RESULT SETS 1
BEGIN
Declare EndOfData INT Default 0;
Declare Field12 Char(10);

 DECLARE c1 CURSOR FOR
   Select   LEFT(Field1,10) as Field1
     from .Table
    where 
      RTRIM(Field3)  <> ''    ;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
         SET myValue = '2';  [B]-- [COLOR="Red"]when I remove this, I get that error detail above[/COLOR][/B]

    Declare Continue Handler For Not Found Set EndOfData = 1 ;
    Open c1;
   WHILE EndOfData = 0 DO
     FETCH NEXT FROM c1 INTO Field12;
            set myValue = Field12;   [B]-- [COLOR="Red"]this is a test line . Wanted to see if I get here[/COLOR][/B]
   END WHILE;
 Close c1;
END

[B.Hauser]

Re: Error in Stored proc (spent 6 hours on it, please help)

Hi,

Where die you define your variable myvar?

Birgitta

[ermagarden]

Re: Error in Stored proc (spent 6 hours on it, please help)

I defined myValue as :

OUT myValue char(1)

--Output parameter in the procedure's parameters.

I tried another field but still the same error!!

[ermagarden]

Re: Error in Stored proc (spent 6 hours on it, please help)

This is resolved now. I removed myValue, the field, added them back in , one by one, tested after each add-in and now it works!

Strange but now i can move on with my life.