Hi all,

when I try the following update in the EDB-Manager it works as expected;

UPDATE ANZAHL SET _2ER = 1 WHERE
     (IF(Z1 IN(27,29), 1, 0) +
     IF(Z2 IN(27,29), 1, 0) +
     IF(Z3 IN(27,29), 1, 0) +
     IF(Z4 IN(27,29), 1, 0) +
     IF(Z5 IN(27,29), 1, 0) +
     IF(Z6 IN(27,29), 1, 0))= 2

but when I try this in my procedure

BEGIN
DECLARE STATCURSOR CURSOR WITH RETURN FOR STMT;

EXECUTE IMMEDIATE'UPDATE ANZAHL SET _2ER =0';

PREPARE STMT FROM'UPDATE ANZAHL SET _2ER = 1 WHERE
     (IF(Z1 IN(?,?), 1, 0) +
     IF(Z2 IN(?,?), 1, 0) +
     IF(Z3 IN(?,?), 1, 0) +
     IF(Z4 IN(?,?), 1, 0) +
     IF(Z5 IN(?,?), 1, 0) +
     IF(Z6 IN(?,?), 1, 0))= 2';
EXECUTE STMT USING A1,A2;

update failed.

Please help me, what do I wrong?
TIA
Dieter

Dieter


The only thing I can think of is the parameterized IN statements - the parser may have trouble figuring out what they are. Try building the statement yourself.


PREPARE STMT FROM'UPDATE ANZAHL SET _2ER = 1 WHERE
     (IF(Z1 IN(' +CAST(param1 AS VARCHAR)+ ','+CAST(param2 AS VARCHAR)+'), 1, 0) +
....
....
= 2';

and see if that works

Roy Lambert [Team Elevate]

Dieter,

The update is not failing, what is happening is you are executing the statement passing 2 parameter values only when the statement expects 12 values.

Instead of:
 EXECUTE STMT USING A1,A2;

Your should be calling it as:
 EXECUTE STMT USING A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 ;

or build it yourself as Roy suggested instead of using parameters , if you find this calling statement weird .

--
Fernando Dias
[Team Elevate]

Thanks Roy,
I tried it with Fernandos tip and it works.





Roy Lambert wrote:

Dieter


The only thing I can think of is the parameterized IN statements - the parser may have trouble figuring out what they are. Try building the statement yourself.


PREPARE STMT FROM'UPDATE ANZAHL SET _2ER = 1 WHERE
     (IF(Z1 IN(' +CAST(param1 AS VARCHAR)+ ','+CAST(param2 AS VARCHAR)+'), 1, 0) +
....
....
= 2';

and see if that works

Roy Lambert [Team Elevate]

Fernando

>The update is not failing, what is happening is you are executing the statement passing 2 parameter values only when the statement expects 12 values.

Arrrgh - another one of the "minor" differences between TEDBQuery and the SQL/PSM language

Roy Lambert

Fernando, thanks for your help.
It works perfect.
Dieter




Fernando Dias wrote:

Dieter,

The update is not failing, what is happening is you are executing the statement passing 2 parameter values only when the statement expects 12 values.

Instead of:
 EXECUTE STMT USING A1,A2;

Your should be calling it as:
 EXECUTE STMT USING A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 , A1,A2 ;

or build it yourself as Roy suggested instead of using parameters , if you find this calling statement weird .

--
Fernando Dias
[Team Elevate]

Roy,

<< Arrrgh - another one of the "minor" differences between TEDBQuery and the
SQL/PSM language >>

Actually, the two are the same in this respect, but the TParams makes it
appear as if there are only two parameters, in terms of their unique names.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Actually, the two are the same in this respect, but the TParams makes it
>appear as if there are only two parameters, in terms of their unique names.

And in this world, as Microsoft spotted, appearance is much more important than reality

Roy Lambert