MQSeries.net :: View topic

hornbeam123 · Posted: Mon May 24, 2004 6:42 am Post subject:

All,
Pretty basic query but I can not find any examples in the mqsi books.
Query, see below, is how to deal with row not found on SELECT of a database within a COMPUTE node and UPDATE/DELETE within DATABASE node. One column (Name) is designated as primary key.
Hornbeam123

SELECT dbms in COMPUTE node
===========================
SET OutputRoot.XML.PeopleOnDatabase.Person =
THE (SELECT gripes.Name, gripes.Age
FROM Database.MQSI AS gripes
WHERE gripes.Name = InputBody.PersonalDetails.Person.Name);

How do we test if the SELECT returned from the dbms call 'DATA NOT FOUND' ?

i.e. OutputRoot.XML.PeopleOnDatabase.Person.Name is empty

+++++++++++++++++++++++++++++++++++++++++++

DELETE and UPDATE dbms in DATABASE node
=======================================
IF Body.PersonalDetails.Action = 'Delete' THEN

-- this deletes the row on the database that matches the Name from the incoming XML
DELETE FROM Database.MQSI AS gripes
WHERE gripes.Name = Body.PersonalDetails.Person.Name;

-- Using UPDATE function change the Age field on the database with the NewAge field supplied
ELSE
IF Body.PersonalDetails.Action = 'Update' THEN
UPDATE Database.MQSI as gripes
SET Age = Body.PersonalDetails.Person.NewAge
WHERE gripes.Name = Body.PersonalDetails.Person.Name;
END IF;
END IF;

How can I test if either of the DELETE or UPDATE functions returned Data not found ?[/i]