1/************************************************************************************
2 Copyright (C) 2012 Monty Program AB
3
4 This library is free software; you can redistribute it and/or
5 modify it under the terms of the GNU Library General Public
6 License as published by the Free Software Foundation; either
7 version 2 of the License, or (at your option) any later version.
8
9 This library is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 Library General Public License for more details.
13
14 You should have received a copy of the GNU Library General Public
15 License along with this library; if not see <http://www.gnu.org/licenses>
16 or write to the Free Software Foundation, Inc.,
17 51 Franklin St., Fifth Floor, Boston, MA 02110, USA
18 *************************************************************************************/
19
20#include "my_test.h"
21
22/* Utility function to verify the field members */
23
24
25static int test_multi_result(MYSQL *mysql)
26{
27 MYSQL_STMT *stmt;
28 MYSQL_BIND ps_params[3]; /* input parameter buffers */
29 MYSQL_BIND rs_bind[3];
30 int int_data[3]; /* input/output values */
31 my_bool is_null[3]; /* output value nullability */
32 int rc, i;
33
34 /* set up stored procedure */
35 rc = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
36 check_mysql_rc(rc, mysql);
37
38 rc = mysql_query(mysql,
39 "CREATE PROCEDURE p1("
40 " IN p_in INT, "
41 " OUT p_out INT, "
42 " INOUT p_inout INT) "
43 "BEGIN "
44 " SELECT p_in, p_out, p_inout; "
45 " SET p_in = 100, p_out = 200, p_inout = 300; "
46 " SELECT p_in, p_out, p_inout; "
47 "END");
48 check_mysql_rc(rc, mysql);
49
50 /* initialize and prepare CALL statement with parameter placeholders */
51 stmt = mysql_stmt_init(mysql);
52 if (!stmt)
53 {
54 diag("Could not initialize statement");
55 exit(1);
56 }
57 rc = mysql_stmt_prepare(stmt, "CALL p1(?, ?, ?)", 16);
58 check_stmt_rc(rc, stmt);
59
60 /* initialize parameters: p_in, p_out, p_inout (all INT) */
61 memset(ps_params, 0, sizeof (ps_params));
62
63 ps_params[0].buffer_type = MYSQL_TYPE_LONG;
64 ps_params[0].buffer = (char *) &int_data[0];
65 ps_params[0].length = 0;
66 ps_params[0].is_null = 0;
67
68 ps_params[1].buffer_type = MYSQL_TYPE_LONG;
69 ps_params[1].buffer = (char *) &int_data[1];
70 ps_params[1].length = 0;
71 ps_params[1].is_null = 0;
72
73 ps_params[2].buffer_type = MYSQL_TYPE_LONG;
74 ps_params[2].buffer = (char *) &int_data[2];
75 ps_params[2].length = 0;
76 ps_params[2].is_null = 0;
77
78 /* bind parameters */
79 rc = mysql_stmt_bind_param(stmt, ps_params);
80 check_stmt_rc(rc, stmt);
81
82 /* assign values to parameters and execute statement */
83 int_data[0]= 10; /* p_in */
84 int_data[1]= 20; /* p_out */
85 int_data[2]= 30; /* p_inout */
86
87 rc = mysql_stmt_execute(stmt);
88 check_stmt_rc(rc, stmt);
89
90 FAIL_IF(mysql_stmt_field_count(stmt) != 3, "expected 3 fields");
91
92 memset(rs_bind, 0, sizeof (MYSQL_BIND) * 3);
93 for (i=0; i < 3; i++)
94 {
95 rs_bind[i].buffer = (char *) &(int_data[i]);
96 rs_bind[i].buffer_length = sizeof (int_data);
97 rs_bind[i].buffer_type = MYSQL_TYPE_LONG;
98 rs_bind[i].is_null = &is_null[i];
99 }
100 rc= mysql_stmt_bind_result(stmt, rs_bind);
101 check_stmt_rc(rc, stmt);
102
103 rc= mysql_stmt_fetch(stmt);
104 check_stmt_rc(rc, stmt);
105
106 FAIL_IF(int_data[0] != 10 || int_data[1] != 20 || int_data[2] != 30,
107 "expected 10 20 30");
108 rc= mysql_stmt_next_result(stmt);
109 check_stmt_rc(rc, stmt);
110 rc= mysql_stmt_bind_result(stmt, rs_bind);
111
112 rc= mysql_stmt_fetch(stmt);
113 FAIL_IF(mysql_stmt_field_count(stmt) != 3, "expected 3 fields");
114 FAIL_IF(int_data[0] != 100 || int_data[1] != 200 || int_data[2] != 300,
115 "expected 100 200 300");
116
117 FAIL_IF(mysql_stmt_next_result(stmt) != 0, "expected more results");
118 rc= mysql_stmt_bind_result(stmt, rs_bind);
119
120 rc= mysql_stmt_fetch(stmt);
121 FAIL_IF(mysql_stmt_field_count(stmt) != 2, "expected 2 fields");
122 FAIL_IF(int_data[0] != 200 || int_data[1] != 300,
123 "expected 100 200 300");
124
125 FAIL_IF(mysql_stmt_next_result(stmt) != 0, "expected more results");
126 FAIL_IF(mysql_stmt_field_count(stmt) != 0, "expected 0 fields");
127
128 rc= mysql_stmt_close(stmt);
129 rc = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
130 check_mysql_rc(rc, mysql);
131 return OK;
132}
133
134int test_sp_params(MYSQL *mysql)
135{
136 int i, rc;
137 MYSQL_STMT *stmt;
138 int a[] = {10,20,30};
139 MYSQL_BIND bind[3];
140 const char *stmtstr= "CALL P1(?,?,?)";
141 char res[3][20];
142
143 rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
144 check_mysql_rc(rc, mysql);
145
146 rc= mysql_query(mysql, "CREATE PROCEDURE p1(OUT p_out VARCHAR(19), IN p_in INT, INOUT p_inout INT)"
147 "BEGIN "
148 " SET p_in = 300, p_out := 'This is OUT param', p_inout = 200; "
149 " SELECT p_inout, p_in, substring(p_out, 9);"
150 "END");
151 check_mysql_rc(rc, mysql);
152
153 stmt= mysql_stmt_init(mysql);
154 check_mysql_rc(rc, mysql);
155
156 rc= mysql_stmt_prepare(stmt,SL(stmtstr));
157 check_stmt_rc(rc, stmt);
158
159 FAIL_IF(mysql_stmt_param_count(stmt) != 3, "expected param_count=3");
160
161 memset(bind, 0, sizeof(MYSQL_BIND) * 3);
162 for (i=0; i < 3; i++)
163 {
164 bind[i].buffer= &a[i];
165 bind[i].buffer_type= MYSQL_TYPE_LONG;
166 }
167 bind[0].buffer_type= MYSQL_TYPE_NULL;
168 rc= mysql_stmt_bind_param(stmt, bind);
169 check_stmt_rc(rc, stmt);
170
171 rc= mysql_stmt_execute(stmt);
172 check_stmt_rc(rc, stmt);
173
174 memset(res, 0, 60);
175
176 memset(bind, 0, sizeof(MYSQL_BIND) * 3);
177 for (i=0; i < 3; i++)
178 {
179 bind[i].buffer_type= MYSQL_TYPE_STRING;
180 bind[i].buffer_length= 20;
181 bind[i].buffer= res[i];
182 }
183
184 do {
185 if (mysql->server_status & SERVER_PS_OUT_PARAMS)
186 {
187 diag("out param result set");
188 FAIL_IF(mysql_stmt_field_count(stmt) != 2, "expected 2 columns");
189 FAIL_IF(strcmp(stmt->fields[0].org_name, "p_out") != 0, "wrong field name");
190 FAIL_IF(strcmp(stmt->fields[1].org_name, "p_inout") != 0, "wrong field name");
191 rc= mysql_stmt_bind_result(stmt, bind);
192 check_stmt_rc(rc, stmt);
193 rc= mysql_stmt_fetch(stmt);
194 check_stmt_rc(rc, stmt);
195 FAIL_IF(strcmp(res[0],"This is OUT param") != 0, "comparison failed");
196 FAIL_IF(strcmp(res[1],"200") != 0, "comparison failed");
197 }
198 else
199 if (mysql_stmt_field_count(stmt))
200 {
201 diag("sp result set");
202 FAIL_IF(mysql_stmt_field_count(stmt) != 3, "expected 3 columns");
203 rc= mysql_stmt_bind_result(stmt, bind);
204 check_stmt_rc(rc, stmt);
205 rc= mysql_stmt_fetch(stmt);
206 check_stmt_rc(rc, stmt);
207 FAIL_IF(strcmp(res[0],"200") != 0, "comparison failed");
208 FAIL_IF(strcmp(res[1],"300") != 0, "comparison failed");
209 FAIL_IF(strcmp(res[2],"OUT param") != 0, "comparison failed");
210
211 }
212 } while (mysql_stmt_next_result(stmt) == 0);
213
214 rc= mysql_stmt_close(stmt);
215 return OK;
216}
217
218int test_sp_reset(MYSQL *mysql)
219{
220 int i, rc;
221 MYSQL_STMT *stmt;
222 int a[] = {10,20,30};
223 MYSQL_BIND bind[3];
224 const char *stmtstr= "CALL P1(?,?,?)";
225
226 rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
227 check_mysql_rc(rc, mysql);
228
229 rc= mysql_query(mysql, "CREATE PROCEDURE p1(OUT p_out VARCHAR(19), IN p_in INT, INOUT p_inout INT)"
230 "BEGIN "
231 " SET p_in = 300, p_out := 'This is OUT param', p_inout = 200; "
232 " SELECT p_inout, p_in, substring(p_out, 9);"
233 "END");
234 check_mysql_rc(rc, mysql);
235
236 stmt= mysql_stmt_init(mysql);
237 check_mysql_rc(rc, mysql);
238
239 rc= mysql_stmt_prepare(stmt,SL(stmtstr));
240 check_stmt_rc(rc, stmt);
241
242 FAIL_IF(mysql_stmt_param_count(stmt) != 3, "expected param_count=3");
243
244 memset(bind, 0, sizeof(MYSQL_BIND) * 3);
245 for (i=0; i < 3; i++)
246 {
247 bind[i].buffer= &a[i];
248 bind[i].buffer_type= MYSQL_TYPE_LONG;
249 }
250 bind[0].buffer_type= MYSQL_TYPE_NULL;
251 rc= mysql_stmt_bind_param(stmt, bind);
252 check_stmt_rc(rc, stmt);
253
254 rc= mysql_stmt_execute(stmt);
255 check_stmt_rc(rc, stmt);
256
257 rc= mysql_stmt_fetch(stmt);
258 check_stmt_rc(rc, stmt);
259
260 rc= mysql_stmt_reset(stmt);
261 check_stmt_rc(rc, stmt);
262
263 /*connection shouldn't be blocked now */
264
265 rc= mysql_query(mysql, "DROP PROCEDURE p1");
266 check_mysql_rc(rc, mysql);
267
268 rc= mysql_stmt_close(stmt);
269 return OK;
270}
271
272int test_sp_reset1(MYSQL *mysql)
273{
274 int rc;
275 MYSQL_STMT *stmt;
276 MYSQL_BIND bind[1];
277
278 char tmp[20];
279 const char *stmtstr= "CALL P1(?)";
280
281 rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
282 check_mysql_rc(rc, mysql);
283 rc= mysql_query(mysql, "CREATE PROCEDURE p1(OUT p_out VARCHAR(19))"
284 "BEGIN "
285 " SET p_out = 'foo';"
286 " SELECT 'foo' FROM DUAL;"
287 " SELECT 'bar' FROM DUAL;"
288 "END");
289 check_mysql_rc(rc, mysql);
290
291 stmt= mysql_stmt_init(mysql);
292 check_mysql_rc(rc, mysql);
293
294 rc= mysql_stmt_prepare(stmt,SL(stmtstr));
295 check_stmt_rc(rc, stmt);
296
297 memset(tmp, 0, sizeof(tmp));
298 memset(bind, 0, sizeof(MYSQL_BIND));
299 bind[0].buffer= tmp;
300 bind[0].buffer_type= MYSQL_TYPE_STRING;
301 bind[0].buffer_length= 4;
302
303 mysql_stmt_bind_param(stmt, bind);
304
305 rc= mysql_stmt_execute(stmt);
306 check_stmt_rc(rc, stmt);
307
308 rc= mysql_stmt_store_result(stmt);
309 check_stmt_rc(rc, stmt);
310
311 rc= mysql_stmt_next_result(stmt);
312 check_stmt_rc(rc, stmt);
313
314 rc= mysql_stmt_fetch(stmt);
315 check_stmt_rc(rc, stmt);
316
317 /* mysql_stmt_reset should set statement in prepared state.
318 * this means: all subsequent result sets should be flushed.
319 * Let's try!
320 */
321 rc= mysql_stmt_reset(stmt);
322 check_stmt_rc(rc, stmt);
323
324 rc= mysql_query(mysql, "DROP PROCEDURE p1");
325 check_mysql_rc(rc, mysql);
326
327 mysql_stmt_close(stmt);
328 return OK;
329}
330
331int test_sp_reset2(MYSQL *mysql)
332{
333 int rc, i;
334 MYSQL_STMT *stmt;
335 MYSQL_BIND bind[4];
336 long l[4];
337 const char *stmtstr= "CALL P1()";
338
339 memset(l, 0, sizeof(l));
340
341 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
342 check_mysql_rc(rc, mysql);
343 rc= mysql_query(mysql, "CREATE TABLE t1 (a int)");
344 check_mysql_rc(rc, mysql);
345
346 rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
347 check_mysql_rc(rc, mysql);
348
349 rc= mysql_query(mysql, "CREATE PROCEDURE p1()"
350 "BEGIN "
351 " SET @a:=1;"
352 " INSERT INTO t1 VALUES(1);"
353 " SELECT 1 FROM DUAL;"
354 " SELECT 2,3 FROM DUAL;"
355 " INSERT INTO t1 VALUES(2);"
356 " SELECT 3,4,5 FROM DUAL;"
357 " SELECT 4,5,6,7 FROM DUAL;"
358 "END");
359 check_mysql_rc(rc, mysql);
360
361 stmt= mysql_stmt_init(mysql);
362 check_mysql_rc(rc, mysql);
363
364 rc= mysql_stmt_prepare(stmt,SL(stmtstr));
365 check_stmt_rc(rc, stmt);
366
367 rc= mysql_stmt_execute(stmt);
368 check_stmt_rc(rc, stmt);
369
370 memset(bind, 0, sizeof(MYSQL_BIND) * 4);
371 for (i=0; i < 4; i++)
372 {
373 bind[i].buffer_type= MYSQL_TYPE_LONG;
374 bind[i].buffer= &l[i];
375 }
376
377 rc= mysql_stmt_bind_result(stmt, bind);
378 check_stmt_rc(rc, stmt);
379
380 while (rc != MYSQL_NO_DATA)
381 {
382 rc= mysql_stmt_fetch(stmt);
383 diag("l=%ld", l[0]);
384 }
385
386 rc= mysql_stmt_next_result(stmt);
387 check_stmt_rc(rc, stmt);
388
389 /* now rebind since we expect 2 columns */
390 rc= mysql_stmt_bind_result(stmt, bind);
391 check_stmt_rc(rc, stmt);
392
393 while (rc != MYSQL_NO_DATA)
394 {
395 rc= mysql_stmt_fetch(stmt);
396 diag("l=%ld l=%ld", l[0], l[1]);
397 }
398
399
400 rc= mysql_stmt_next_result(stmt);
401 check_stmt_rc(rc, stmt);
402
403 /* now rebind since we expect 2 columns */
404 rc= mysql_stmt_bind_result(stmt, bind);
405 check_stmt_rc(rc, stmt);
406
407 while (rc != MYSQL_NO_DATA)
408 {
409 rc= mysql_stmt_fetch(stmt);
410 diag("l=%ld l=%ld l=%ld", l[0], l[1], l[2]);
411 }
412
413 rc= mysql_stmt_close(stmt);
414
415
416 rc= mysql_query(mysql, "DROP PROCEDURE p1");
417 check_mysql_rc(rc, mysql);
418 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
419 check_mysql_rc(rc, mysql);
420
421 return OK;
422}
423
424int test_query(MYSQL *mysql)
425{
426 int rc;
427 int i;
428 MYSQL_STMT *stmt;
429 MYSQL_BIND bind[1];
430
431 char tmp[20];
432 const char *stmtstr= "CALL P1(?)";
433
434 rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
435 check_mysql_rc(rc, mysql);
436 rc= mysql_query(mysql, "CREATE PROCEDURE p1(OUT p_out VARCHAR(19))"
437 "BEGIN "
438 " SET p_out = 'foo';"
439 " SELECT 1 FROM DUAL;"
440 "END");
441 check_mysql_rc(rc, mysql);
442
443 stmt= mysql_stmt_init(mysql);
444 check_mysql_rc(rc, mysql);
445
446 rc= mysql_stmt_prepare(stmt,SL(stmtstr));
447 check_stmt_rc(rc, stmt);
448
449 for (i=0; i < 1000; i++)
450 {
451 int status;
452 memset(tmp, 0, sizeof(tmp));
453 memset(bind, 0, sizeof(MYSQL_BIND));
454 bind[0].buffer= tmp;
455 bind[0].buffer_type= MYSQL_TYPE_STRING;
456 bind[0].buffer_length= 4;
457
458 mysql_stmt_bind_param(stmt, bind);
459
460 rc= mysql_stmt_execute(stmt);
461 check_stmt_rc(rc, stmt);
462 do {
463 if (stmt->field_count)
464 {
465 mysql_stmt_bind_result(stmt, bind);
466 rc= mysql_stmt_store_result(stmt);
467 check_stmt_rc(rc, stmt);
468 while(mysql_stmt_fetch(stmt) == 0);
469
470 rc= mysql_stmt_free_result(stmt);
471 check_stmt_rc(rc, stmt);
472 }
473 status= mysql_stmt_next_result(stmt);
474 if (status == 1)
475 check_stmt_rc(status, stmt);
476 } while (status == 0);
477
478 rc= mysql_stmt_reset(stmt);
479 if (rc)
480 diag("reset failed after %d iterations", i);
481 check_stmt_rc(rc, stmt);
482 }
483 mysql_stmt_close(stmt);
484 rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
485 check_mysql_rc(rc, mysql);
486
487 return OK;
488}
489
490
491struct my_tests_st my_tests[] = {
492 {"test_query", test_query, TEST_CONNECTION_DEFAULT, CLIENT_MULTI_RESULTS , NULL , NULL},
493 {"test_sp_params", test_sp_params, TEST_CONNECTION_DEFAULT, CLIENT_MULTI_STATEMENTS, NULL , NULL},
494 {"test_sp_reset", test_sp_reset, TEST_CONNECTION_DEFAULT, CLIENT_MULTI_STATEMENTS, NULL , NULL},
495 {"test_sp_reset1", test_sp_reset1, TEST_CONNECTION_DEFAULT, CLIENT_MULTI_STATEMENTS, NULL , NULL},
496 {"test_sp_reset2", test_sp_reset2, TEST_CONNECTION_DEFAULT, CLIENT_MULTI_STATEMENTS, NULL , NULL},
497 {"test_multi_result", test_multi_result, TEST_CONNECTION_DEFAULT, CLIENT_MULTI_STATEMENTS, NULL , NULL},
498 {NULL, NULL, 0, 0, NULL, NULL}
499};
500
501int main(int argc, char **argv)
502{
503 if (argc > 1)
504 get_options(argc, argv);
505
506 get_envvars();
507
508 run_tests(my_tests);
509
510 return(exit_status());
511}
512