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