1--TEST-- 2mysqli_query() - Stored Procedures 3--SKIPIF-- 4<?php 5require_once('skipif.inc'); 6require_once('skipifconnectfailure.inc'); 7require_once('connect.inc'); 8if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket)) { 9 die(sprintf('skip Cannot connect to MySQL, [%d] %s.', mysqli_connect_errno(), mysqli_connect_error())); 10} 11if (mysqli_get_server_version($link) <= 50000) { 12 die(sprintf('skip Needs MySQL 5.0+, found version %d.', mysqli_get_server_version($link))); 13} 14?> 15--FILE-- 16<?php 17 require_once('connect.inc'); 18 require_once('table.inc'); 19 20 if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p')) 21 printf("[001] [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 22 23 if (mysqli_query($link, 'CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id, label FROM test ORDER BY id ASC; 24END;')) { 25 /* stored proc which returns one result set */ 26 if (mysqli_multi_query($link, 'CALL p()')) { 27 do { 28 if ($res = mysqli_use_result($link)) { 29 // skip results, don't fetch all from server 30 var_dump(mysqli_fetch_assoc($res)); 31 mysqli_free_result($res); 32 } 33 } while (mysqli_more_results($link) && mysqli_next_result($link)); 34 35 } else { 36 printf("[003] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 37 } 38 39 if (mysqli_multi_query($link, 'CALL p()')) { 40 do { 41 if ($res = mysqli_store_result($link)) { 42 // fetch all results from server, but skip on client side 43 var_dump(mysqli_fetch_assoc($res)); 44 mysqli_free_result($res); 45 } 46 } while (mysqli_more_results($link) && mysqli_next_result($link)); 47 48 } else { 49 printf("[004] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 50 } 51 52 if (mysqli_multi_query($link, 'CALL p()')) { 53 do { 54 if ($res = mysqli_store_result($link)) { 55 // fetch all results from server, but skip on client side 56 var_dump(mysqli_fetch_assoc($res)); 57 while (mysqli_fetch_assoc($res)) 58 ; 59 mysqli_free_result($res); 60 } 61 } while (mysqli_more_results($link) && mysqli_next_result($link)); 62 63 } else { 64 printf("[005] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 65 } 66 67 } else { 68 printf("[002] Cannot create SP, [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 69 } 70 71 if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p')) 72 printf("[006] [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 73 74 if (mysqli_query($link, 'CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id, label FROM test ORDER BY id ASC; SELECT id FROM test ORDER BY id ASC; END;')) { 75 /* stored proc which returns two result sets */ 76 77 if (mysqli_multi_query($link, 'CALL p()')) { 78 do { 79 if ($res = mysqli_store_result($link)) { 80 // fetch all results from server, but skip on client side 81 var_dump(mysqli_fetch_assoc($res)); 82 mysqli_free_result($res); 83 } 84 } while (mysqli_more_results($link) && mysqli_next_result($link)); 85 86 } else { 87 printf("[008] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 88 } 89 90 } else { 91 printf("[007] Cannot create SP, [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 92 } 93 94 if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p')) 95 printf("[009] [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 96 97 if (mysqli_real_query($link, 'CREATE PROCEDURE p(OUT ver_param VARCHAR(25)) BEGIN SELECT VERSION() INTO ver_param; END;')) { 98 /* no result set, just output parameter */ 99 if (!mysqli_query($link, 'CALL p(@version)')) 100 printf("[011] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 101 102 if (!mysqli_query($link, "SET @version = 'unknown'")) 103 printf("[012] Cannot reset user variable, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 104 105 if (!mysqli_query($link, 'CALL p(@version)')) 106 printf("[013] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 107 108 if (!$res = mysqli_query($link, 'SELECT @version as _vers')) 109 printf("[014] Cannot fetch user variable, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 110 111 if (!$row = mysqli_fetch_assoc($res) || 112 $row['_vers'] == 'unknown') 113 printf("[015] Results seem wrong, got %s, [%d] %s\n", 114 $row['_vers'], 115 mysqli_errno($link), mysqli_error($link)); 116 mysqli_free_result($res); 117 118 } else { 119 printf("[010] Cannot create SP, [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 120 } 121 122 if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p')) 123 printf("[016] [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 124 125 if (mysqli_real_query($link, 'CREATE PROCEDURE p(IN ver_in VARCHAR(25), OUT ver_out VARCHAR(25)) BEGIN SELECT ver_in INTO ver_out; END;')) { 126 /* no result set, one input, one output parameter */ 127 if (!mysqli_query($link, "CALL p('myversion', @version)")) 128 printf("[018] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 129 130 if (!mysqli_query($link, "SET @version = 'unknown'")) 131 printf("[019] Cannot reset user variable, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 132 133 if (!mysqli_query($link, "CALL p('myversion', @version)")) 134 printf("[020] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 135 136 if (!$res = mysqli_query($link, 'SELECT @version as _vers')) 137 printf("[021] Cannot fetch user variable, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 138 139 if (!$row = mysqli_fetch_assoc($res) || 140 $row['_vers'] == 'myversion') 141 printf("[022] Results seem wrong, got %s, [%d] %s\n", 142 $row['_vers'], 143 mysqli_errno($link), mysqli_error($link)); 144 mysqli_free_result($res); 145 146 } else { 147 printf("[017] Cannot create SP, [%d] %s.\n", mysqli_errno($link), mysqli_error($link)); 148 } 149 150 mysqli_close($link); 151 print "done!"; 152?> 153--CLEAN-- 154<?php 155require_once("connect.inc"); 156if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket)) 157 printf("[c001] [%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()); 158 159if (!mysqli_query($link, "DROP TABLE IF EXISTS test")) 160 printf("[c002] Cannot drop table, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); 161 162@mysqli_query($link, "DROP PROCEDURE IS EXISTS p"); 163 164mysqli_close($link); 165?> 166--EXPECTF-- 167array(2) { 168 [%u|b%"id"]=> 169 %unicode|string%(1) "1" 170 [%u|b%"label"]=> 171 %unicode|string%(1) "a" 172} 173array(2) { 174 [%u|b%"id"]=> 175 %unicode|string%(1) "1" 176 [%u|b%"label"]=> 177 %unicode|string%(1) "a" 178} 179array(2) { 180 [%u|b%"id"]=> 181 %unicode|string%(1) "1" 182 [%u|b%"label"]=> 183 %unicode|string%(1) "a" 184} 185array(2) { 186 [%u|b%"id"]=> 187 %unicode|string%(1) "1" 188 [%u|b%"label"]=> 189 %unicode|string%(1) "a" 190} 191array(1) { 192 [%u|b%"id"]=> 193 %unicode|string%(1) "1" 194} 195done!
