i'm executing the below code with the command

db2 -td! -v -fmysqlcode.sql
_________________________________

connect to APPLE USER webuser using webuser!
CREATE PROCEDURE insert_event_f
(connector_id_p VARCHAR(40),
object_key_p VARCHAR(80),
object_name_p VARCHAR(40),
object_verb_p VARCHAR(40),
event_priority_p DECIMAL(5),
v_SimilarCounts DECIMAL)

LANGUAGE SQL

BEGIN
DECLARE tmp_object_verb VARCHAR(30);
set tmp_object_verb = object_verb_p;
set v_SimilarCounts = (select count(*) <-- LINE 16
FROM webuser.WBIA_EVENTS
WHERE object_name = object_name_p
AND object_verb = object_verb_p
AND object_key = object_key_p
AND event_status = 0); <---what could be wrong with this?
IF object_verb_p = 'Physical Delete' THEN

DELETE FROM webuser.WBIA_EVENTS
WHERE object_name = object_name_p
AND object_key = object_key_p
AND event_status != 3;
set tmp_object_verb = 'Delete';
END IF;

IF (v_SimilarCounts = 0) THEN

INSERT INTO webuser.WBIA_EVENTS
( connector_id,
object_key,
object_name,
object_verb,
event_priority,
event_status)
VALUES
(connector_id_p,
object_key_p,
object_name_p,
tmp_object_verb,
event_priority_p,
0);
END IF;
RETURN(1);
END!
_______________________________________

but i get this anoying error:
_______________________________________
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0401N The data types of the operands for the operation "=" are not compatible. LINE NUMBER=16. SQLSTATE=42818

regards, lehare

Ehrm... event_status is not character, maybe? I would find it rather funny if it is.

try SELECT COUNT(*) INTO v_SimilarCounts FROM webuser.WBIA_EVENTS etc...

also, I don't know for sure, but wouldn't the != be a problem when using ! as a command terminator?

It's indeed the " ! "
and so it's not line 16, but line 26. Use <> instead.

cheers,
Rob.

................................posted twise

i changed the code to

connect to APPLE USER webuser using webuser@
CREATE PROCEDURE insert_event_f
(connector_id_p VARCHAR(40),
object_key_p VARCHAR(80),
object_name_p VARCHAR(40),
object_verb_p VARCHAR(40),
event_priority_p DECIMAL(5),
v_SimilarCounts INTEGER)
LANGUAGE SQL

BEGIN
DECLARE tmp_object_verb VARCHAR(30);
set tmp_object_verb = object_verb_p;

select count(*) INTO v_SimilarCounts
FROM WBIA_EVENTS
WHERE object_name = object_name_p
AND object_verb = object_verb_p
AND object_key = object_key_p
AND event_status = 0;
IF object_verb_p = 'Physical Delete' THEN

DELETE FROM WBIA_EVENTS
WHERE object_name = object_name_p
AND object_key = object_key_p
AND event_status <> 3;
set tmp_object_verb = 'Delete';
END IF;

IF (v_SimilarCounts = 0) THEN

INSERT INTO WBIA_EVENTS
( connector_id,
object_key,
object_name,
object_verb,
event_priority,
event_status)
VALUES
(connector_id_p,
object_key_p,
object_name_p,
tmp_object_verb,
event_priority_p,
0);
END IF;
RETURN(1);

END@

______________________

used the @ [as a terminator], and replaced line 26 from ' != ' to ' <> ' and changed line 14 to (select count(*) INTO etc ...

but i still get EXACTLY the same error

by the way i'm using windows 2000 [fixpac 4], db2 version 8.1.8

Maybe it's not the content of line 16; it could be something else.
If you (for test purposes) leave out "AND event_status = 0"
and retry, is it still protesting, and does it now point to line number 15?

Very often the nature of the error message is misleading - please check!
cheers,
Rob.

Post the DDL, it could be a type conversion thing.

save below code in a file as file.sql

and run db2 -tvf file.sql

I hope this will work



connect to APPLE USER webuser using webuser;
CREATE PROCEDURE insert_event_f
(connector_id_p VARCHAR(40),
object_key_p VARCHAR(80),
object_name_p VARCHAR(40),
object_verb_p VARCHAR(40),
event_priority_p DECIMAL(5),
v_SimilarCounts DECIMAL)

LANGUAGE SQL

BEGIN
DECLARE tmp_object_verb VARCHAR(30);
set tmp_object_verb = object_verb_p;
set v_SimilarCounts = (select count(*) FROM webuser.WBIA_EVENTS
WHERE object_name = object_name_p
AND object_verb = object_verb_p
AND object_key = object_key_p
AND event_status = 0);
IF object_verb_p = 'Physical Delete' THEN

DELETE FROM webuser.WBIA_EVENTS
WHERE object_name = object_name_p
AND object_key = object_key_p
AND event_status != 3;
set tmp_object_verb = 'Delete';
END IF;

IF (v_SimilarCounts = 0) THEN

INSERT INTO webuser.WBIA_EVENTS
( connector_id,
object_key,
object_name,
object_verb,
event_priority,
event_status)
VALUES
(connector_id_p,
object_key_p,
object_name_p,
tmp_object_verb,
event_priority_p,
0);
END IF;
RETURN(1);
END;