Hi
this must be easy but it is giving me grief - I am trying to test an SP I have just written using Datastudio 2.2.1.0 :

CREATE OR REPLACE PROCEDURE SP_1
(
IN P_GUID VARCHAR(37),
OUT P_FILE_UID VARCHAR(37),
OUT P_SERVICE_ID VARCHAR(10),
OUT P_ERROR VARCHAR(4000)
)
...

This is supposed to:
delete the row from the table identified by 'NAME.1', but return the file uid and service id values from the row before it does.
If there is an error then P_ERROR should be populated.

I have written the SP and it compiles. Now I want to test it with something like:
BEGIN
DECLARE P_FILE_UID VARCHAR(37);
DECLARE P_SERVICE_ID VARCHAR(10);
DECLARE P_ERROR VARCHAR(4000);
CALL SP_1('NAME.1', P_FILE_UID, P_SERVICE_ID, P_ERROR);
P1: BEGIN ATOMIC
SELECT P_FILE_UID, P_SERVICE_ID, P_ERROR FROM STATUS_MSG;
END P1;
END<<


( where << is our statement termination tag)

this fails to compile with the follwoing error msg:
An unexpected token "SELECT P_FILE_UID, P_SERVICE_ID, P_ERROR " was found following ")". Expected tokens may include: "
P1: BEGIN ATOMIC
".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65

I have tried BEGIN ATOMIC instead of begin - but to no avail

HELP!!!

Many thanks

10Pints


DETAILS of SP:

CREATE OR REPLACE PROCEDURE SP_1
(
IN P_GUID VARCHAR(37),
OUT P_FILE_UID VARCHAR(37),
OUT P_SERVICE_ID VARCHAR(10),
OUT P_ERROR VARCHAR(4000)
)
SPECIFIC SP_1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 P_ERROR = MESSAGE_TEXT;
SET P_ERROR = SQLCODE;
END;

SET P_ERROR = 0;
SET P_FILE_UID = 0; -- NOT FOUND

SELECT FILE_UID, SERVICE_ID INTO P_FILE_UID, P_SERVICE_ID FROM TBL_STATUS_MSG WHERE R_GUID = P_GUID;

IF( P_FILE_UID <> 0)
THEN
DELETE FROM STATUS_MSG WHERE GUID = P_GUID;
ELSE
SET P_SERVICE_ID = 0;
END IF;
END<<

INSERT INTO STATUS_MSG (FILE_UID, R_GUID, SERVICE_ID) VALUES('FILE_UID.1', 'R.1', 'SERVICE.1')<<
INSERT INTO IMS_RTCA_STATUS_MSG (FILE_UID, R_GUID, SERVICE_ID) VALUES('FILE_UID.2', 'R.2', 'SERVICE.2')<<
SELECT * FROM STATUS_MSG<<

What is wrong with using Data Studio to test the Stored Procedure?

Andy

Hi
right
I need a way in datastudio to do the call and then display the parameters
I have just (in desperation) tried this from the DB2CMD window - and that gives me the ouT paramas for free.
If I do (in a DB CMS window)
DB2 CALL SP_1('NAME.1', ?,?,?);

I get
Value of output parameters
--------------------------
Parameter Name : P_FILE_UID
Parameter Value :

Parameter Name : P_SERVICE_ID
Parameter Value :

Parameter Name : P_ERROR
Parameter Value :

Return Status = 0

But I had been labouring to Get Data studio to do same - must be a way
any tips?

T

I no longer use that version, but it should be just right-click and select "Run". A dialog should come up for the input parameters. When it completes, it should give you the out parameters.

Andy

Hi Andy
thanks for your help
I have tried that but still get problem; when I do that I get the initial parameter dialog pop up
I then populate the one in parameter
and datastudio goes of and reports the operation succeeded in the output window (as below)...
and no ouptut params anywhere to be seen.

(the name in the actual outut is diferent from my example - as I wanted to keep the example as simple as possible)
e.g.:
WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Run started.
Data returned in result sets is limited to the first 500 rows.
Data returned in result set columns is limited to the first 500 bytes or characters.
WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Calling the stored procedure.
WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Run completed.

WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Execution Time => 0 ms


Strange - this ought to be simple especially since the command window does automatically.

There should be a parameter tab right behind where the status is.

Andy

mm attached scrnshot-params.JPG is the dialog i get when I right click the SP in the data soruce explorer window.

there is a tabbed dialog if I right click as above and chose run settings
see scrnshot-params 2.JPG
but that one only has an editor for the post run - so have same problem:
I guess i need to bae able to do something like
VALUES(P_FILE_UID, P_SERVICE_ID)

Right behind where this comes out, should be a tab with the parameters.

Andy

thanks for help andy - thi s is much appreciated -
Supposed to have uploaded 2 images then sorry - try the second one now
This is when i right click SP and select run settings:

Forget the dialog. After the SP executes, it gives you the status of what it did and how long. There should be a tab behind that with the parameters used and returned.

Andy

AHHH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
the light ... it has dawned ... I had "Display results in a single tab" set - so no parameter tab

I now at last get a parameters tab:
Name Type Data type Value Value (OUT)
------------ ------ --------- ----- -----------
P_RTCA_GUID INPUT VARCHAR x
P_FILE_UID OUTPUT VARCHAR *NULL*
P_SERVICE_ID OUTPUT VARCHAR *NULL*
P_ERROR OUTPUT VARCHAR *NULL*


thanks Andy you have most patient - I think I owe you a beer
are you near stevenage?
10Pints

I do not drink. Give the money to your favorite charity instead.

Andy

You don't drink?
You must have some vice ...

thanks again