| Accessing different resultsets that were created by a mysql stored procedure [message #169643] |
Tue, 21 September 2010 12:06  |
Jan Mielke
Messages: 2
Registered: September 2010
Karma:
|
Junior Member |
|
|
Hi everone,
I am just learning how to connect to a mysql server using php. I am
familiar to use mysql with stored procedures and java as frontend and I
want to use the same procedures (the same database) in php, but I can
not find a solution for the following problem.
Mainly all of my stored procedures return three resultsets (RS).
1.) RS1: contains an error-code
2.) RS2: contains an error-message
3.) RS3: contains the result I am interested in
In java it is possible to first take a look at the RS1 and then either
on RS2 or RS3 depending on the code in RS1 (procedure produced errors
or not...)
Here is an example for the idea of such a stored procedure:
MYSQL:
======
DROP PROCEDURE IF EXISTS `uspBeispiel` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `uspBeispiel`()
BEGIN
DECLARE rResultMsg varchar(255);
DECLARE rResultInt int;
SET rResultMsg = 'OK';
SET rResultInt = 1;
-- Executing some code. If the code produces errors[1] I store
-- an errormessage and an error code in rResultMsg and rResultInt.
-- [1] (Imagine you are working on a a treestructure and you want to
-- ensure that all children that are added by your procedure
-- have unique names regarding to the parentnode)
IF(rResultInt = 1) THEN
-- IF needed I return the information I am interested in.
-- (for example the complete treestructure. Here very simple:)
SELECT "Beispiel" AS Test;
END IF;
SELECT rResultMsg AS ResultMsg;
SELECT rResultInt AS ResultInt;
END $$
PHP:
====
My class connects to the database using the following lines in the
constructor:
$this->connection = mysql_connect(MYSQL_SERVER,
MYSQL_SERVER_USER,
MYSQL_SERVER_USER_PWD,
true,
65536);
if(!$this->connection) {
throw new Exception(mysql_error());
}
$this->db = mysql_select_db(MYSQL_DATABASE, $this->connection);
if(!$this->db) {
throw new Exception(mysql_error());
}
and here is the code that I use to call the procedure:
function beispiel() {
$sql = "CALL uspBeispiel()";
$result = mysql_query($sql) or die(mysql_error());
// And at this point I have the problem, that i only can access
// the first resultset, that was created by my procedure.
//
// That would be:
// SELECT "Beispiel" AS Test
// if no error occured (I have not changed rResultInt in
// uspBeispiel())
}
I hope I described my problem in an understandable way. English is not
my native language.
Best regards,
Jan
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]