I have a stored procedure that performs an INSERT on a table. That table has multiple DEFAULT values associated with columns. After I perform the insert I want to set the default values in OUT parameters. However, when I execute the JDBC call I get an exception with the message "Result consisted of more than one row".
If I remove the SELECT column INTO out_parameter FROM... the procedure will work as expected.
1. How can I modify the SP so that I can retrieve the default value from the column and set it in the OUT parameter to avoid this exception from being thrown?
2. [side question] Why is the second select returning a result set when I am setting it to a variable? Is there a way to prevent the additional result set?
Stored Procedure
CREATE PROCEDURE `INS_REQUESTDATA`(
OUT RTN_VAL INTEGER,
OUT DMS_REQUEST_ID INTEGER,
IN P_STATUS VARCHAR(15),
IN P_SRC_RQST_ID VARCHAR(128),
IN P_REQUEST TEXT,
IN P_RESULT TEXT,
OUT P_CREATE_DATE TIMESTAMP,
IN P_CREATE_USER VARCHAR(20),
OUT P_MOD_DATE TIMESTAMP
)
BEGIN
DECLARE TMP_TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- Declare an exit handler for a duplicate key constraint
DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN SET RTN_VAL = -1; END;
-- Declare an exit handler for a foreign key constraint
DECLARE EXIT HANDLER FOR 1452 BEGIN SET RTN_VAL = -2; END;
SET RTN_VAL = 0;
SET DMS_REQUEST_ID = -1;
INSERT INTO REQUESTDATA (status, src_rqst_id, request, result, create_date,
create_user, mod_date)
VALUES(P_STATUS, P_SRC_RQST_ID, P_REQUEST, P_RESULT, TMP_TS, P_CREATE_USER,
P_MOD_DATE);
SET DMS_REQUEST_ID = LAST_INSERT_ID();
SET P_CREATE_DATE = TMP_TS;
-- Removing this statement will remove the extra result set, but then I do not
-- have the default value in the OUT parameter
SELECT mod_date
INTO P_MOD_DATE
FROM requestdata
WHERE dms_request_id = DMS_REQUEST_ID;
END