Why this Stores Procedure is not working - puzzled

Here is the SP:
--#SET TERMINATOR !
CREATE PROCEDURE execute_immediate (IN in_stmt VARCHAR(1000)
,OUT out_sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE sqlcode INTEGER;
DECLARE EXIT HANDLER FOR sqlexception
SET out_sqlcode = sqlcode;
EXECUTE IMMEDIATE in_stmt;
SET out_sqlcode = sqlcode;
RETURN;
END!

I have field HIREDATE char(10)
SELECT DATE( HIREDATE) FROM EMP_SCREEN_EDIT;
1
----------
12/27/1999

1 record(s) selected.
I updated Hiredate using SP - Maked it invalid:
CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET HIREDATE =
''12/77/1999''',?)

Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : 0

Return Status = 0

After Update:
SELECT HIREDATE FROM EMP_SCREEN_EDIT

HIREDATE
----------
12/77/1999

SELECT DATE(HIREDATE) FROM EMP_SCREEN_EDIT
the string representation of a datetime value is out of range
sqlcode: -181

Now 2 selects using SP:

CALL execute_immediate('SELECT HIREDATE FROM EMP_SCREEN_EDIT',?)

Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : -84

Return Status = 0

CALL execute_immediate('SELECT DATE(HIREDATE) FROM EMP_SCREEN_EDIT',?)

Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : -84

Return Status = 0

Why Stored Procedure returns in both cases -84
What is going on???

--
Message posted via http://www.dbmonster.com

jefftyzzer

Guest

n/a Posts

July 17th, 2008
09:35 PM
#2

Re: Why this Stores Procedure is not working - puzzled

On Jul 17, 1:20 pm, "lenygold via DBMonster.com" <u41482@uwewrote:

Quote:

Here is the SP:
--#SET TERMINATOR !
CREATE PROCEDURE execute_immediate (IN in_stmt VARCHAR(1000)
,OUT out_sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE sqlcode INTEGER;
DECLARE EXIT HANDLER FOR sqlexception
SET out_sqlcode = sqlcode;
EXECUTE IMMEDIATE in_stmt;
SET out_sqlcode = sqlcode;
RETURN;
END!
>
I have field HIREDATE char(10)
SELECT DATE( HIREDATE) FROM EMP_SCREEN_EDIT;
1
----------
12/27/1999
>
1 record(s) selected.
I updated Hiredate using SP - Maked it invalid:
CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET HIREDATE =
''12/77/1999''',?)
>
Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : 0
>
Return Status = 0
>
After Update:
SELECT HIREDATE FROM EMP_SCREEN_EDIT
>
HIREDATE
----------
12/77/1999
>
SELECT DATE(HIREDATE) FROM EMP_SCREEN_EDIT
the string representation of a datetime value is out of range
sqlcode: -181
>
Now 2 selects using SP:
>
CALL execute_immediate('SELECT HIREDATE FROM EMP_SCREEN_EDIT',?)
>
Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : -84
>
Return Status = 0
>
CALL execute_immediate('SELECT DATE(HIREDATE) FROM EMP_SCREEN_EDIT',?)
>
Value of output parameters
--------------------------
Parameter Name : OUT_SQLCODE
Parameter Value : -84
>
Return Status = 0
>
Why Stored Procedure returns in both cases -84
What is going on???
>
--
Message posted viahttp://www.dbmonster.com

EXECUTE IMMEDIATE can't be used for SELECTs. You'll need to use a
CURSOR instead, e.g.,

<stuff here>
DECLARE C_RETURN CURSOR WITH RETURN FOR S_RETURN;--
PREPARE S_RETURN FROM in_stmt;--
OPEN C_RETURN;--
<stuff here>

--Jeff

Serge Rielau

Guest

n/a Posts

July 18th, 2008
01:55 AM
#3

Re: Why this Stores Procedure is not working - puzzled

db2 =? SQL0084;

SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
statement.

Explanation:

A SELECT or VALUES statement was used in an EXECUTE IMMEDIATE statement.

The statement cannot be processed.

User response:

The implied function is not supported. Prepare the SELECT or VALUES
statement. Then use OPEN, FETCH, and CLOSE.

sqlcode: -84

sqlstate: 42612
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

lenygold via DBMonster.com

Guest

n/a Posts

July 18th, 2008
12:55 PM
#4

Re: Why this Stores Procedure is not working - puzzled

Thank you very much for your help.
I was able to create a trigger which handilng any dates entered on the
screen. Just replaced select with update.
Here is part of the trigger for dates edit:
--#SET TERMINATOR !
CREATE TRIGGER EMPSCREDIT
AFTER INSERT ON EMP_SCREEN_EDIT
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason VARCHAR(70);
DECLARE OUT_SQLCODE1 INTEGER;
DECLARE OUT_SQLCODE2 INTEGER;
CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET HIREDATE = hiredate
where CHECK_DATE(replace(hiredate,''-'','''')) IN (''DATE
IS VALID'')',OUT_SQLCODE1);
CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET BIRTHDATE = BIRTHDATE
where CHECK_DATE(replace(BIRTHDATE,''-'','''')) IN (''DATE
IS VALID'')',OUT_SQLCODE2);
SET reason =
CASE WHEN OUT_SQLCODE1 = 100
THEN '292 INVALID HIREDATE'
WHEN OUT_SQLCODE2 = 100
THEN '343 INVALID BIRTHDATE'

CHECJ_DATE IS UDF FOR DATE EDIT IN FORMAT 'YYYYMMDD'
AND IT IS RETURNIG MESSAGE INVALID DATE OR VALID DATE.
Thank you Tonkuma for this UDF.

Serge Rielau wrote:

Quote:

>db2 =? SQL0084;
>
>SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
statement.
>
>Explanation:
>
>A SELECT or VALUES statement was used in an EXECUTE IMMEDIATE statement.
>
>The statement cannot be processed.
>
>User response:
>
>The implied function is not supported. Prepare the SELECT or VALUES
>statement. Then use OPEN, FETCH, and CLOSE.
>
sqlcode: -84
>
sqlstate: 42612

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums...bm-db2/200807/1