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!

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.