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) ||
111                $row['_vers'] == 'unknown')
112            printf("[015] Results seem wrong, got %s, [%d] %s\n",
113                $row['_vers'],
114                mysqli_errno($link), mysqli_error($link));
115        mysqli_free_result($res);
116
117    } else {
118        printf("[010] Cannot create SP, [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
119    }
120
121    if (!mysqli_query($link, 'DROP PROCEDURE IF EXISTS p'))
122        printf("[016] [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
123
124    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;')) {
125        /* no result set, one input, one output parameter */
126        if (!mysqli_query($link, "CALL p('myversion', @version)"))
127            printf("[018] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link));
128
129        if (!mysqli_query($link, "SET @version = 'unknown'"))
130            printf("[019] Cannot reset user variable, [%d] %s\n", mysqli_errno($link), mysqli_error($link));
131
132        if (!mysqli_query($link, "CALL p('myversion', @version)"))
133            printf("[020] Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link));
134
135        if (!$res = mysqli_query($link, 'SELECT @version as _vers'))
136            printf("[021] Cannot fetch user variable, [%d] %s\n", mysqli_errno($link), mysqli_error($link));
137
138        if (!$row = mysqli_fetch_assoc($res) ||
139                $row['_vers'] == 'myversion')
140            printf("[022] Results seem wrong, got %s, [%d] %s\n",
141                $row['_vers'],
142                mysqli_errno($link), mysqli_error($link));
143        mysqli_free_result($res);
144
145    } else {
146        printf("[017] Cannot create SP, [%d] %s.\n", mysqli_errno($link), mysqli_error($link));
147    }
148
149    mysqli_close($link);
150    print "done!";
151?>
152--CLEAN--
153<?php
154require_once 'connect.inc';
155if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
156   printf("[c001] [%d] %s\n", mysqli_connect_errno(), mysqli_connect_error());
157
158if (!mysqli_query($link, "DROP TABLE IF EXISTS test"))
159    printf("[c002] Cannot drop table, [%d] %s\n", mysqli_errno($link), mysqli_error($link));
160
161@mysqli_query($link, "DROP PROCEDURE IS EXISTS p");
162
163mysqli_close($link);
164?>
165--EXPECT--
166array(2) {
167  ["id"]=>
168  string(1) "1"
169  ["label"]=>
170  string(1) "a"
171}
172array(2) {
173  ["id"]=>
174  string(1) "1"
175  ["label"]=>
176  string(1) "a"
177}
178array(2) {
179  ["id"]=>
180  string(1) "1"
181  ["label"]=>
182  string(1) "a"
183}
184array(2) {
185  ["id"]=>
186  string(1) "1"
187  ["label"]=>
188  string(1) "a"
189}
190array(1) {
191  ["id"]=>
192  string(1) "1"
193}
194done!
195