Quoting uwendel@stripped:
> Author: uwendel
> Date: 2008-04-18 11:43:33 +0200 (Fri, 18 Apr 2008)
> New Revision: 1498
>
> Added:
> trunk/tests/ext/mysqli/mysqli_stmt_execute_stored_proc_multi.phpt
> Log:
> Andrey, you might need to run it several times to get this result.
> Like with the PDO test its not deterministic. Also, please fix the
> EXPECTF and code. I don't know how to use the PHP API for what I'm
> trying to accomplish.
>
> Warning: Packets out of order. Expected 1 received 8. Packet size=7.
> PID=18222 in
> /home/nixnutz/php53/ext/mysqli/tests/mysqli_stmt_execute_stored_proc_multi.php on
> line
> 230
>
> Warning: mysqli_stmt_execute(): MySQL server has gone away in
> /home/nixnutz/php53/ext/mysqli/tests/mysqli_stmt_execute_stored_proc_multi.php on
> line
> 230
>
>
>
>
> Added: trunk/tests/ext/mysqli/mysqli_stmt_execute_stored_proc_multi.phpt
> ===================================================================
> ---
> trunk/tests/ext/mysqli/mysqli_stmt_execute_stored_proc_multi.phpt
> (rev 0)
> +++
> trunk/tests/ext/mysqli/mysqli_stmt_execute_stored_proc_multi.phpt 2008-04-18
> 09:43:33 UTC (rev 1498)
> @@ -0,0 +1,281 @@
> +--TEST--
> +mysqli_stmt_execute() - Stored Procedures
> +--SKIPIF--
> +<?php
> +require_once('skipif.inc');
> +require_once('skipifconnectfailure.inc');
> +require_once('connect.inc');
> +if (!$link = mysqli_connect($host, $user, $passwd, $db, $port, $socket)) {
> + die(sprintf('skip Cannot connect to MySQL, [%d] %s.',
> mysqli_connect_errno(), mysqli_connect_error()));
> +}
> +if (mysqli_get_server_version($link) <= 50000) {
> + die(sprintf('skip Needs MySQL 5.0+, found version %d.',
> mysqli_get_server_version($link)));
> +}
> +?>
> +--FILE--
> +<?php
> + require_once('connect.inc');
> + require_once('table.inc');
> +
> + // single result, fetching first row only
> + if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p'))
> + printf("[001] [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
> +
> + if (mysqli_real_query($link, 'CREATE PROCEDURE p() BEGIN SELECT id
> FROM test ORDER BY id; END;')) {
> + /* no result set, one output parameter */
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[003] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + if (!mysqli_stmt_execute($stmt))
> + printf("[004] Cannot execute CALL, [%d] %s\n",
> mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> + $id = null;
> + if (!mysqli_stmt_bind_result($stmt, $id) ||
> + !mysqli_stmt_fetch($stmt))
> + printf("[005] [%d] %s\n", mysqli_stmt_errno($stmt),
> mysqli_stmt_error($stmt));
> +
> + if (1 !== $id)
> + printf("[005] Expecting int/1 got %s, [%d] %s\n",
> + var_export($id, true),
> + mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> + mysqli_stmt_free_result($stmt);
free_result without store_result? interesting
> + mysqli_stmt_close($stmt);
> +
> + } else {
> + printf("[002] Cannot create SP with single SELECT, [%d] %s.\n",
> mysqli_errno($link), mysqli_error($link));
> + }
> +
> + // single result, no fetch, no free result
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[006] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + if (!mysqli_stmt_execute($stmt))
> + printf("[007] Cannot execute CALL, [%d] %s\n",
> mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> + mysqli_stmt_close($stmt);
> +
> + // Out of sync
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[008] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + mysqli_close($link);
> + $link = mysqli_connect($host, $user, $passwd, $db, $port, $socket);
> +
> + // single result, fetching no rows, dumping object
> +
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[009] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + if (!mysqli_stmt_execute($stmt))
> + printf("[010] Cannot execute CALL, [%d] %s\n",
> mysqli_stmt_errno($stmt), ysqli_stmt_error($stmt));
> +
> + var_dump($stmt);
> +
> + mysqli_stmt_free_result($stmt);
> + mysqli_stmt_close($stmt);
> +
> + // multiple results - likely improper use of more_results
> + if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p'))
> + printf("[011] [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
> +
> + if (mysqli_real_query($link, 'CREATE PROCEDURE p() BEGIN SELECT id
> FROM test ORDER BY id LIMIT 1; SELECT id FROM test ORDER BY id
> LIMIT 1; END;')) {
> + /* no result set, one output parameter */
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[012] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + if (!mysqli_stmt_execute($stmt))
> + printf("[013] Cannot execute CALL, [%d] %s\n",
> mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> + $id = null;
> + if (!mysqli_stmt_bind_result($stmt, $id) ||
> + !mysqli_stmt_fetch($stmt))
> + printf("[014] [%d] %s\n", mysqli_stmt_errno($stmt),
> mysqli_stmt_error($stmt));
> +
> + if (1 !== $id)
> + printf("[015] Expecting int/1 got %s, [%d] %s\n",
> + var_export($id, true),
> + mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> + // TODO - Andrey: How to fetch the next rowset?
if (true !== mysqli_stmt_more_results($stmt))
> + if (true !== mysqli_more_results($link))
> + printf("[017] Expecting more results\n");
> +
> + // not sure, what do I have to do?
> + do {
> + printf("More results? %s\n",
> + ((mysqli_more_results($link) ? 'yes' : 'no')));
> +
> + printf("Can call store_result? %s\n",
> + ((is_resource($res = mysqli_store_result($link))) ? 'yes' : 'no'));
> +
> + printf("Can call use_result? %s\n",
> + ((is_resource($res == mysqli_use_result($link))) ? 'yes' : 'no'));
> +
> + $id = null;
> + printf("Can call stmt_fetch (id = %s)? %s\n",
> + ((mysqli_stmt_fetch($stmt)) ? 'yes' : 'no'), $id);
> +
> + } while (true === mysqli_next_result($link));
mysqli_stmt_next_result($stmt)
> + mysqli_stmt_free_result($stmt);
> + mysqli_stmt_close($stmt);
> +
> + } else {
> + printf("[016] Cannot create SP with single SELECT, [%d] %s.\n",
> mysqli_errno($link), mysqli_error($link));
> + }
> +
> + // multiple results
> + if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p'))
> + printf("[017] [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
> +
> + if (mysqli_real_query($link, 'CREATE PROCEDURE p() BEGIN SELECT id
> FROM test ORDER BY id LIMIT 1; SELECT id FROM test ORDER BY id
> LIMIT 1; END;')) {
> + /* no result set, one output parameter */
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[018] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + if (!mysqli_stmt_execute($stmt))
> + printf("[019] Cannot execute CALL, [%d] %s\n",
> mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> + $id = null;
> + if (!mysqli_stmt_bind_result($stmt, $id))
> + printf("[020] [%d] %s\n", mysqli_stmt_errno($stmt),
> mysqli_stmt_error($stmt));
> +
> + do {
> + while (mysqli_stmt_fetch($stmt)) {
> + printf("Can call stmt_fetch (id = %s)? yes\n", $id);
> + $id = null;
> + }
> +
> + printf("More results? %s\n",
> + ((mysqli_more_results($link) ? 'yes' : 'no')));
> +
> + } while (true === mysqli_next_result($link));
see above.
> + mysqli_stmt_free_result($stmt);
> + mysqli_stmt_close($stmt);
> +
> + } else {
> + printf("[016] Cannot create SP with single SELECT, [%d] %s.\n",
> mysqli_errno($link), mysqli_error($link));
> + }
> +
> + //
> + if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p'))
> + printf("[017] [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
> +
> + if (mysqli_real_query($link, 'CREATE PROCEDURE p() BEGIN IF RAND()
> > 0.5 THEN SELECT id FROM test ORDER BY id LIMIT 1; ELSE SELECT id,
> label FROM test ORDER BY id LIMIT 2; END IF; END;')) {
> + /* no result set, one output parameter */
> + if (!$stmt = mysqli_prepare($link, 'CALL p()'))
> + printf("[018] Cannot prepare CALL, [%d] %s\n",
> mysqli_errno($link), mysqli_error($link));
> +
> + if (!mysqli_stmt_execute($stmt))
> + printf("[019] Cannot execute CALL, [%d] %s\n",
> mysqli_stmt_errno($stmt), mysqli_stmt_error($stmt));
> +
> +
> +
> + $last_field_count = $field_count = null;
> + while ($last_field_count == $field_count) {
> +
> + $last_field_count = $field_count;
> + $field_count = mysqli_stmt_field_count($stmt);
> +
> + switch ($field_count) {
> + case 1:
> + $id = null;
> + if (!mysqli_stmt_bind_result($stmt, $id)) {
> + printf("[020] [%d] %s\n", mysqli_stmt_errno($stmt),
> mysqli_stmt_error($stmt));
> + mysqli_stmt_free_result($stmt);
> + break 2;
> + }
> +
> + $num_rows = 0;
> + while (mysqli_stmt_fetch($stmt)) {
> + $num_rows++;
> + if ($num_rows > 1) {
> + printf("[021] Got more than one row!\n");
> + mysqli_stmt_free_result($stmt);
> + break 3;
> + }
> + if ($id !== 1) {
> + printf("[022] Expecting id = int/1, got %s/%s\n",
> + gettype($id), $id);
> + mysqli_stmt_free_result($stmt);
> + break 3;
> + }
> + }
> + break;
> +
> + case 2:
> + $id = $label = null;
> + if (!mysqli_stmt_bind_result($stmt, $id, $label)) {
> + printf("[023] [%d] %s\n", mysqli_stmt_errno($stmt),
> mysqli_stmt_error($stmt));
> + mysqli_stmt_free_result($stmt);
> + break 2;
> + }
> +
> + $num_rows = 0;
> + while (mysqli_stmt_fetch($stmt)) {
> + $num_rows++;
> + if ($num_rows > 2) {
> + printf("[024] Got more than one row!\n");
> + mysqli_stmt_free_result($stmt);
> + break 3;
> + }
> + if ($id !== 1 && $id !== 2) {
> + printf("[025] Expecting id = int/1 or int/2, got %s/%s\n",
> + gettype($id), $id);
> + mysqli_stmt_free_result($stmt);
> + break 3;
> + }
> + if ($label !== 'a' && $label !== 'b') {
> + printf("[026] Expecting label = string/a or string/b, got %s/%s\n",
> + gettype($label), $label);
> + mysqli_stmt_free_result($stmt);
> + break 3;
> + }
> + }
> + break;
> +
> + default:
> + printf("[027] Expecting field count to be either 1 or 2, got
> %s\n", $field_count);
> + mysqli_stmt_free_result($stmt);
> + break 2;
> + }
> + mysqli_stmt_free_result($stmt);
> + mysqli_stmt_execute($stmt);
> + }
> +
> + mysqli_stmt_free_result($stmt);
> + mysqli_stmt_close($stmt);
> +
> + } else {
> + printf("[028] Cannot create SP with two SELECTs, [%d] %s.\n",
> mysqli_errno($link), mysqli_error($link));
> + }
> +
> +
> + mysqli_close($link);
> + print "done!";
> +?>
> +--EXPECTF--
> +[008] Cannot prepare CALL, [%d] %s
> +object(mysqli_stmt)#%d (9) {
> + ["affected_rows"]=>
> + int(-1)
> + ["insert_id"]=>
> + int(0)
> + ["num_rows"]=>
> + int(0)
> + ["param_count"]=>
> + int(0)
> + ["field_count"]=>
> + int(1)
> + ["errno"]=>
> + int(0)
> + ["error"]=>
> + string(0) ""
> + ["sqlstate"]=>
> + string(5) "00000"
> + ["id"]=>
> + int(1)
> +}
> +done!
> \ No newline at end of file
>
>
> --
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe: http://lists.mysql.com/commits?unsub=1
>
>
