Jump to content

How to get OUT parameter details from SPs in PHP


dabbsy

Recommended Posts

I've got a stored procedure, which takes values, and inserts into a table quite merrily.  I'm now trying to  extract the @p_information_id (derived from LAST_INSERT_ID() in the stored procedure).  The PHP is shown below.

$query = "CALL sp_ins_application ($an, $et, '$en', '$av', '$dv', '$au', '$sp','$not', @p_information_id, @p_error)";
$result = mysql_query($query) or die ("ERROR: Query failed: " . mysql_error());

How do I use PHP to extract the information held in these OUT parameters.

If it helps, the full SP is shown below ( I know the error checking isn't particularly sophisticated, but it seems to work!).

DROP PROCEDURE IF EXISTS sp_ins_application;
DELIMITER //

CREATE PROCEDURE sp_ins_application(
/* insert a new application and then pass back the new id. */
IN p_app_id MEDIUMINT,
IN p_environment_type_id SMALLINT,
IN p_environment_name VARCHAR(50),
IN p_app_version VARCHAR(50),
IN p_db_version VARCHAR(25),
IN p_application_url VARCHAR(200),
IN p_sql_port VARCHAR(40),
IN p_notes VARCHAR(250),
OUT p_information_id MEDIUMINT,
OUT p_error VARCHAR(200)
)
BEGIN
/* Declarations section */
DECLARE exit handler for sqlexception SET p_error := 'An error has occured trying to call sp_ins_application - either database or data issues';

INSERT INTO information (app_id, environment_type_id, environment_name, app_version, db_version, application_url, sql_port, notes)
VALUES ( p_app_id, p_environment_type_id, p_environment_name, p_app_version, p_db_version, p_application_url, p_sql_port, p_notes);

IF ROW_COUNT() = 0 THEN
SET p_error = 'Unable to insert new application.';
ELSE
SET p_error = 'OK';
SELECT LAST_INSERT_ID() INTO p_information_id;
END IF;
END;
//
delimiter ;

Thanks for any help anyone can offer!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.