| 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: 0
|
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
|
|
|
|
| Re: Accessing different resultsets that were created by a mysql stored procedure [message #169644 is a reply to message #169643] |
Tue, 21 September 2010 12:42  |
Robert Hairgrove
Messages: 19
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
Jan Mielke wrote:
> 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...)
You apparently need to use the more recent mysqli PHP extension for this
kind of functionality (never tried it myself, but since our school's
hosting server has upgraded recently, it is worth looking into this for me):
http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-23- 4-1-15
Also, make sure that the database client-server protocol version is 4.1
or above.
There is also an extra parameter which can be passed to the
mysql_connect() function in the "client_flags" option. It might work if
you connect like this:
// PHP code:
define("CLIENT_MULTI_RESULTS", 131072);
$res = mysql_connect (
DB_HOST, DB_USER, DB_PWD, false, CLIENT_MULTI_RESULTS );
I found this in the user comments at this page on the PHP manual site:
http://ch.php.net/manual/en/function.mysql-connect.php
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]