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!
196