MySQL Forums
Forum List  »  Stored Procedures

Result consisted of more than one row
Posted by: Michael Peremsky
Date: March 18, 2014 06:49AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Result consisted of more than one row
10362
March 18, 2014 06:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.