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--EXPECT--
167array(2) {
168  ["id"]=>
169  string(1) "1"
170  ["label"]=>
171  string(1) "a"
172}
173array(2) {
174  ["id"]=>
175  string(1) "1"
176  ["label"]=>
177  string(1) "a"
178}
179array(2) {
180  ["id"]=>
181  string(1) "1"
182  ["label"]=>
183  string(1) "a"
184}
185array(2) {
186  ["id"]=>
187  string(1) "1"
188  ["label"]=>
189  string(1) "a"
190}
191array(1) {
192  ["id"]=>
193  string(1) "1"
194}
195done!
196