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