1/*
2Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved.
3
4The MySQL Connector/C is licensed under the terms of the GPLv2
5<http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most
6MySQL Connectors. There are special exceptions to the terms and
7conditions of the GPLv2 as it is applied to this software, see the
8FLOSS License Exception
9<http://www.mysql.com/about/legal/licensing/foss-exception.html>.
10
11This program is free software; you can redistribute it and/or modify
12it under the terms of the GNU General Public License as published
13by the Free Software Foundation; version 2 of the License.
14
15This program is distributed in the hope that it will be useful, but
16WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
17or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
18for more details.
19
20You should have received a copy of the GNU General Public License along
21with this program; if not, write to the Free Software Foundation, Inc.,
2251 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
23*/
24#include "my_test.h"
25
26/* Utility function to verify the field members */
27
28static int test_conc97(MYSQL *mysql)
29{
30 MYSQL_STMT *stmt;
31 int rc;
32
33 diag("Please run this test manually");
34 return SKIP;
35 stmt= mysql_stmt_init(mysql);
36
37 mysql_close(mysql);
38
39 rc= mysql_stmt_reset(stmt);
40 FAIL_IF(!rc, "Error expected while resetting stmt");
41
42 rc= mysql_stmt_close(stmt);
43 check_stmt_rc(rc, stmt);
44
45 mysql= mysql_init(NULL);
46
47 return OK;
48}
49
50static int test_conc83(MYSQL *unused __attribute__((unused)))
51{
52 MYSQL_STMT *stmt;
53 int rc;
54 MYSQL *mysql= mysql_init(NULL);
55 my_bool reconnect= 1;
56
57 const char *query= "SELECT 1,2,3 FROM DUAL";
58
59 stmt= mysql_stmt_init(mysql);
60
61 mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
62 FAIL_IF(!(my_test_connect(mysql, hostname, username, password,
63 schema, port, socketname, 0)), "my_test_connect failed");
64
65 /* 1. Status is inited, so prepare should work */
66
67 rc= mysql_kill(mysql, mysql_thread_id(mysql));
68
69 rc= mysql_ping(mysql);
70 check_mysql_rc(rc, mysql);
71
72 rc= mysql_stmt_prepare(stmt, SL(query));
73 check_stmt_rc(rc, stmt);
74 diag("Ok");
75
76 /* 2. Status is prepared, execute should fail */
77 rc= mysql_kill(mysql, mysql_thread_id(mysql));
78
79 rc= mysql_stmt_execute(stmt);
80 FAIL_IF(!rc, "Error expected");
81
82 mysql_stmt_close(stmt);
83 mysql_close(mysql);
84 return OK;
85}
86
87
88static int test_conc60(MYSQL *mysql)
89{
90 MYSQL_STMT *stmt;
91 int rc;
92 const char *query= "SELECT * FROM agendas";
93 my_bool x= 1;
94
95 stmt= mysql_stmt_init(mysql);
96
97 rc= mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void *)&x);
98
99 rc= mysql_stmt_prepare(stmt, SL(query));
100 if (rc && mysql_stmt_errno(stmt) == 1146) {
101 diag("Internal test - customer data not available");
102 mysql_stmt_close(stmt);
103 return SKIP;
104 }
105 check_stmt_rc(rc, stmt);
106
107 rc= mysql_stmt_execute(stmt);
108 check_stmt_rc(rc, stmt);
109
110 rc= mysql_stmt_store_result(stmt);
111 check_stmt_rc(rc, stmt);
112
113 rc= mysql_stmt_free_result(stmt);
114 check_stmt_rc(rc, stmt);
115
116 mysql_stmt_close(stmt);
117
118 return OK;
119}
120
121static int test_prepare_insert_update(MYSQL *mysql)
122{
123 MYSQL_STMT *stmt;
124 int rc;
125 int i;
126 const char *testcase[]= {
127 "CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B))",
128 "INSERT t1 VALUES (1,2,10), (3,4,20)",
129 "INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE c=c+100",
130 "SELECT * FROM t1",
131 "INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0",
132 "SELECT * FROM t1",
133 "INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a)",
134 NULL};
135 const char **cur_query;
136
137 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
138 check_mysql_rc(rc, mysql);
139
140 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
141 check_mysql_rc(rc, mysql);
142
143 for (cur_query= testcase; *cur_query; cur_query++)
144 {
145 char query[MAX_TEST_QUERY_LENGTH];
146 strcpy(query, *cur_query);
147 stmt= mysql_stmt_init(mysql);
148 FAIL_IF(!stmt, mysql_error(mysql));
149 rc= mysql_stmt_prepare(stmt, SL(query));
150 check_stmt_rc(rc, stmt);
151
152 FAIL_IF(mysql_stmt_param_count(stmt) != 0, "Paramcount is not 0");
153 rc= mysql_stmt_execute(stmt);
154 check_stmt_rc(rc, stmt);
155
156 /* try the last query several times */
157 if (!cur_query[1])
158 {
159 for (i=0; i < 3;i++)
160 {
161 rc= mysql_stmt_execute(stmt);
162 check_stmt_rc(rc, stmt);
163 rc= mysql_stmt_execute(stmt);
164 check_stmt_rc(rc, stmt);
165 }
166 }
167 mysql_stmt_close(stmt);
168 }
169
170 rc= mysql_commit(mysql);
171 check_mysql_rc(rc, mysql);
172
173 return OK;
174}
175
176/*
177 Generalized conversion routine to handle DATE, TIME and DATETIME
178 conversion using MYSQL_TIME structure
179*/
180
181static int test_bind_date_conv(MYSQL *mysql, uint row_count)
182{
183 MYSQL_STMT *stmt= 0;
184 uint rc, i, count= row_count;
185 MYSQL_BIND my_bind[4];
186 my_bool is_null[4]= {0,0,0,0};
187 MYSQL_TIME tm[4];
188 ulong second_part;
189 uint year, month, day, hour, minute, sec;
190
191 stmt= mysql_stmt_init(mysql);
192 FAIL_IF(!stmt, mysql_error(mysql));
193 rc= mysql_stmt_prepare(stmt, SL("INSERT INTO test_date VALUES(?, ?, ?, ?)"));
194 check_stmt_rc(rc, stmt);
195
196 FAIL_IF(mysql_stmt_param_count(stmt) != 4, "param_count != 4");
197
198 /*
199 We need to bzero bind structure because mysql_stmt_bind_param checks all
200 its members.
201 */
202 memset(my_bind, '\0', sizeof(my_bind));
203
204 my_bind[0].buffer_type= MYSQL_TYPE_TIMESTAMP;
205 my_bind[1].buffer_type= MYSQL_TYPE_TIME;
206 my_bind[2].buffer_type= MYSQL_TYPE_DATETIME;
207 my_bind[3].buffer_type= MYSQL_TYPE_DATETIME;
208
209 for (i= 0; i < (int) array_elements(my_bind); i++)
210 {
211 my_bind[i].buffer= (void *) &tm[i];
212 my_bind[i].is_null= &is_null[i];
213 my_bind[i].buffer_length= sizeof(MYSQL_TIME);
214 }
215
216 second_part= 0;
217
218 year= 2000;
219 month= 01;
220 day= 10;
221
222 hour= 11;
223 minute= 16;
224 sec= 20;
225
226 rc= mysql_stmt_bind_param(stmt, my_bind);
227 check_stmt_rc(rc, stmt);
228
229 for (count= 0; count < row_count; count++)
230 {
231 for (i= 0; i < (int) array_elements(my_bind); i++)
232 {
233 memset(&tm[i], 0, sizeof(MYSQL_TIME));
234 tm[i].neg= 0;
235 tm[i].second_part= second_part+count;
236 if (my_bind[i].buffer_type != MYSQL_TYPE_TIME)
237 {
238 tm[i].year= year+count;
239 tm[i].month= month+count;
240 tm[i].day= day+count;
241 }
242 else
243 tm[i].year= tm[i].month= tm[i].day= 0;
244 if (my_bind[i].buffer_type != MYSQL_TYPE_DATE)
245 {
246 tm[i].hour= hour+count;
247 tm[i].minute= minute+count;
248 tm[i].second= sec+count;
249 }
250 else
251 tm[i].hour= tm[i].minute= tm[i].second= 0;
252 }
253 rc= mysql_stmt_execute(stmt);
254 check_stmt_rc(rc, stmt);
255 }
256
257 rc= mysql_commit(mysql);
258 check_mysql_rc(rc, mysql);
259
260 mysql_stmt_close(stmt);
261
262 rc= my_stmt_result(mysql, "SELECT * FROM test_date");
263 FAIL_UNLESS(row_count == rc, "rowcount != rc");
264
265 stmt= mysql_stmt_init(mysql);
266 FAIL_IF(!stmt, mysql_error(mysql));
267 rc= mysql_stmt_prepare(stmt, SL("SELECT * FROM test_date"));
268 check_stmt_rc(rc, stmt);
269
270 rc= mysql_stmt_bind_result(stmt, my_bind);
271 check_stmt_rc(rc, stmt);
272
273 rc= mysql_stmt_execute(stmt);
274 check_stmt_rc(rc, stmt);
275
276 rc= mysql_stmt_store_result(stmt);
277 check_stmt_rc(rc, stmt);
278
279 for (count= 0; count < row_count; count++)
280 {
281 rc= mysql_stmt_fetch(stmt);
282 FAIL_UNLESS(rc == 0 || rc == MYSQL_DATA_TRUNCATED, "rc != 0 | rc != MYSQL_DATA_TRUNCATED");
283
284 for (i= 0; i < array_elements(my_bind); i++)
285 {
286 FAIL_UNLESS(tm[i].year == 0 || tm[i].year == year+count, "wrong value for year");
287 FAIL_UNLESS(tm[i].month == 0 || tm[i].month == month+count, "wrong value for month");
288 FAIL_UNLESS(tm[i].day == 0 || tm[i].day == day+count, "wrong value for day");
289 FAIL_UNLESS(tm[i].hour == 0 || tm[i].hour % 24 == 0 || tm[i].hour % 24 == hour+count, "wrong value for hour");
290 FAIL_UNLESS(tm[i].minute == 0 || tm[i].minute == minute+count, "wrong value for minute");
291 FAIL_UNLESS(tm[i].second == 0 || tm[i].second == sec+count, "wrong value for second");
292 FAIL_UNLESS(tm[i].second_part == 0 ||
293 tm[i].second_part == second_part+count, "wrong value for second_part");
294 }
295 }
296 rc= mysql_stmt_fetch(stmt);
297 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
298
299 mysql_stmt_close(stmt);
300 return OK;
301}
302
303
304/* Test simple prepares of all DML statements */
305
306static int test_prepare_simple(MYSQL *mysql)
307{
308 MYSQL_STMT *stmt;
309 int rc;
310 char query[MAX_TEST_QUERY_LENGTH];
311
312 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_simple");
313 check_mysql_rc(rc, mysql);
314
315 rc= mysql_query(mysql, "CREATE TABLE test_prepare_simple("
316 "id int, name varchar(50))");
317 check_mysql_rc(rc, mysql);
318
319 /* insert */
320 strcpy(query, "INSERT INTO test_prepare_simple VALUES(?, ?)");
321 stmt= mysql_stmt_init(mysql);
322 FAIL_IF(!stmt, mysql_error(mysql));
323 rc= mysql_stmt_prepare(stmt, SL(query));
324 check_stmt_rc(rc, stmt);
325
326 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount is not 2");
327 mysql_stmt_close(stmt);
328
329 /* update */
330 strcpy(query, "UPDATE test_prepare_simple SET id=? "
331 "WHERE id=? AND CONVERT(name USING utf8)= ?");
332 stmt= mysql_stmt_init(mysql);
333 FAIL_IF(!stmt, mysql_error(mysql));
334 rc= mysql_stmt_prepare(stmt, SL(query));
335 check_stmt_rc(rc, stmt);
336
337 FAIL_IF(mysql_stmt_param_count(stmt) != 3, "Paramcount is not 3");
338 mysql_stmt_close(stmt);
339
340 /* delete */
341 strcpy(query, "DELETE FROM test_prepare_simple WHERE id=10");
342 stmt= mysql_stmt_init(mysql);
343 FAIL_IF(!stmt, mysql_error(mysql));
344 rc= mysql_stmt_prepare(stmt, SL(query));
345 check_stmt_rc(rc, stmt);
346
347 FAIL_IF(mysql_stmt_param_count(stmt) != 0, "Paramcount is not 0");
348
349 rc= mysql_stmt_execute(stmt);
350 check_stmt_rc(rc, stmt);
351 mysql_stmt_close(stmt);
352
353 /* delete */
354 strcpy(query, "DELETE FROM test_prepare_simple WHERE id=?");
355 stmt= mysql_stmt_init(mysql);
356 FAIL_IF(!stmt, mysql_error(mysql));
357 rc= mysql_stmt_prepare(stmt, SL(query));
358 check_stmt_rc(rc, stmt);
359
360 FAIL_IF(mysql_stmt_param_count(stmt) != 1, "Paramcount != 1");
361
362 mysql_stmt_close(stmt);
363
364 /* select */
365 strcpy(query, "SELECT * FROM test_prepare_simple WHERE id=? "
366 "AND CONVERT(name USING utf8)= ?");
367 stmt= mysql_stmt_init(mysql);
368 FAIL_IF(!stmt, mysql_error(mysql));
369 rc= mysql_stmt_prepare(stmt, SL(query));
370 check_stmt_rc(rc, stmt);
371
372 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
373
374 mysql_stmt_close(stmt);
375
376 /* now fetch the results ..*/
377 rc= mysql_commit(mysql);
378 check_mysql_rc(rc, mysql);
379 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_simple");
380 check_mysql_rc(rc, mysql);
381
382 return OK;
383}
384
385static int test_prepare_field_result(MYSQL *mysql)
386{
387 MYSQL_STMT *stmt;
388 MYSQL_RES *result;
389 int rc;
390 char query[MAX_TEST_QUERY_LENGTH];
391
392 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_field_result");
393 check_mysql_rc(rc, mysql);
394
395 rc= mysql_query(mysql, "CREATE TABLE test_prepare_field_result(int_c int, "
396 "var_c varchar(50), ts_c timestamp, "
397 "char_c char(4), date_c date, extra tinyint)");
398 check_mysql_rc(rc, mysql);
399
400 /* insert */
401 strcpy(query, "SELECT int_c, var_c, date_c as date, ts_c, char_c FROM "
402 " test_prepare_field_result as t1 WHERE int_c=?");
403 stmt= mysql_stmt_init(mysql);
404 FAIL_IF(!stmt, mysql_error(mysql));
405 rc= mysql_stmt_prepare(stmt, SL(query));
406 check_stmt_rc(rc, stmt);
407
408 FAIL_IF(mysql_stmt_param_count(stmt) != 1, "Paramcount != 1");
409
410 result= mysql_stmt_result_metadata(stmt);
411 FAIL_IF(!result, mysql_stmt_error(stmt));
412
413 if (verify_prepare_field(result, 0, "int_c", "int_c", MYSQL_TYPE_LONG,
414 "t1", "test_prepare_field_result", schema, 11, 0))
415 goto error;
416 if (verify_prepare_field(result, 1, "var_c", "var_c", MYSQL_TYPE_VAR_STRING,
417 "t1", "test_prepare_field_result", schema, 50, 0))
418 goto error;
419 if (verify_prepare_field(result, 2, "date", "date_c", MYSQL_TYPE_DATE,
420 "t1", "test_prepare_field_result", schema, 10, 0))
421 goto error;
422 if (verify_prepare_field(result, 3, "ts_c", "ts_c", MYSQL_TYPE_TIMESTAMP,
423 "t1", "test_prepare_field_result", schema, 19, 0))
424 goto error;
425 if (verify_prepare_field(result, 4, "char_c", "char_c",
426 (mysql_get_server_version(mysql) <= 50000 ?
427 MYSQL_TYPE_VAR_STRING : MYSQL_TYPE_STRING),
428 "t1", "test_prepare_field_result", schema, 4, 0))
429 goto error;
430
431 FAIL_IF(mysql_num_fields(result) != 5, "Paramcount != 5");
432 mysql_free_result(result);
433 mysql_stmt_close(stmt);
434 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_field_result");
435 check_mysql_rc(rc, mysql);
436
437 return OK;
438
439error:
440 mysql_free_result(result);
441 mysql_stmt_close(stmt);
442 return FAIL;
443}
444
445
446/* Test simple prepare field results */
447
448static int test_prepare_syntax(MYSQL *mysql)
449{
450 MYSQL_STMT *stmt;
451 int rc;
452 char query[MAX_TEST_QUERY_LENGTH];
453
454 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_syntax");
455 check_mysql_rc(rc, mysql);
456
457 rc= mysql_query(mysql, "CREATE TABLE test_prepare_syntax("
458 "id int, name varchar(50), extra int)");
459 check_mysql_rc(rc, mysql);
460
461 strcpy(query, "INSERT INTO test_prepare_syntax VALUES(?");
462 stmt= mysql_stmt_init(mysql);
463 FAIL_IF(!stmt, mysql_error(mysql));
464 rc= mysql_stmt_prepare(stmt, SL(query));
465 FAIL_IF(!rc, "error expected");
466
467 strcpy(query, "SELECT id, name FROM test_prepare_syntax WHERE id=? AND WHERE");
468 FAIL_IF(!stmt, mysql_error(mysql));
469 rc= mysql_stmt_prepare(stmt, SL(query));
470 FAIL_IF(!rc, "error expected");
471
472 /* now fetch the results ..*/
473 rc= mysql_commit(mysql);
474 check_mysql_rc(rc, mysql);
475
476 mysql_stmt_close(stmt);
477 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_syntax");
478 check_mysql_rc(rc, mysql);
479
480 return OK;
481}
482
483static int test_prepare(MYSQL *mysql)
484{
485 MYSQL_STMT *stmt;
486 int rc, i;
487 int int_data, o_int_data;
488 char str_data[50], data[50];
489 char tiny_data, o_tiny_data;
490 short small_data, o_small_data;
491 longlong big_data, o_big_data;
492 float real_data, o_real_data;
493 double double_data, o_double_data;
494 ulong length[7], len;
495 my_bool is_null[7];
496 MYSQL_BIND my_bind[7];
497 char query[MAX_TEST_QUERY_LENGTH];
498
499 rc= mysql_autocommit(mysql, TRUE);
500 check_mysql_rc(rc, mysql);
501
502 rc= mysql_query(mysql, "DROP TABLE IF EXISTS my_prepare");
503 check_mysql_rc(rc, mysql);
504
505 rc= mysql_query(mysql, "CREATE TABLE my_prepare(col1 tinyint, "
506 "col2 varchar(15), col3 int, "
507 "col4 smallint, col5 bigint, "
508 "col6 float, col7 double )");
509 check_mysql_rc(rc, mysql);
510
511 /* insert by prepare */
512 strcpy(query, "INSERT INTO my_prepare VALUES(?, ?, ?, ?, ?, ?, ?)");
513 stmt= mysql_stmt_init(mysql);
514 FAIL_IF(!stmt, mysql_error(mysql));
515 rc= mysql_stmt_prepare(stmt, SL(query));
516 check_stmt_rc(rc, stmt);
517
518 FAIL_IF(mysql_stmt_param_count(stmt) != 7, "Paramcount != 7");
519
520 memset(my_bind, '\0', sizeof(my_bind));
521
522 /* tinyint */
523 my_bind[0].buffer_type= MYSQL_TYPE_TINY;
524 my_bind[0].buffer= (void *)&tiny_data;
525 /* string */
526 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
527 my_bind[1].buffer= (void *)str_data;
528 my_bind[1].buffer_length= 1000; /* Max string length */
529 /* integer */
530 my_bind[2].buffer_type= MYSQL_TYPE_LONG;
531 my_bind[2].buffer= (void *)&int_data;
532 /* short */
533 my_bind[3].buffer_type= MYSQL_TYPE_SHORT;
534 my_bind[3].buffer= (void *)&small_data;
535 /* bigint */
536 my_bind[4].buffer_type= MYSQL_TYPE_LONGLONG;
537 my_bind[4].buffer= (void *)&big_data;
538 /* float */
539 my_bind[5].buffer_type= MYSQL_TYPE_FLOAT;
540 my_bind[5].buffer= (void *)&real_data;
541 /* double */
542 my_bind[6].buffer_type= MYSQL_TYPE_DOUBLE;
543 my_bind[6].buffer= (void *)&double_data;
544
545 for (i= 0; i < (int) array_elements(my_bind); i++)
546 {
547 my_bind[i].length= &length[i];
548 my_bind[i].is_null= &is_null[i];
549 is_null[i]= 0;
550 }
551
552 rc= mysql_stmt_bind_param(stmt, my_bind);
553 check_stmt_rc(rc, stmt);
554
555 int_data= 320;
556 small_data= 1867;
557 big_data= 1000;
558 real_data= 2;
559 double_data= 6578.001;
560
561 /* now, execute the prepared statement to insert 10 records.. */
562 for (tiny_data= 0; tiny_data < 100; tiny_data++)
563 {
564 length[1]= sprintf(str_data, "MySQL%d", int_data);
565 rc= mysql_stmt_execute(stmt);
566 check_stmt_rc(rc, stmt);
567 int_data += 25;
568 small_data += 10;
569 big_data += 100;
570 real_data += 1;
571 double_data += 10.09;
572 }
573
574 mysql_stmt_close(stmt);
575
576 /* now fetch the results ..*/
577 rc= mysql_commit(mysql);
578 check_mysql_rc(rc, mysql);
579
580 /* test the results now, only one row should exist */
581 rc= my_stmt_result(mysql, "SELECT * FROM my_prepare");
582 FAIL_UNLESS(rc != 1, "rowcount != 1");
583
584 stmt= mysql_stmt_init(mysql);
585 FAIL_IF(!stmt, mysql_error(mysql));
586 rc= mysql_stmt_prepare(stmt, "SELECT * FROM my_prepare", 25);
587 check_stmt_rc(rc, stmt);
588
589 rc= mysql_stmt_bind_result(stmt, my_bind);
590 check_stmt_rc(rc, stmt);
591
592 /* get the result */
593 rc= mysql_stmt_execute(stmt);
594 check_stmt_rc(rc, stmt);
595
596 o_int_data= 320;
597 o_small_data= 1867;
598 o_big_data= 1000;
599 o_real_data= 2;
600 o_double_data= 6578.001;
601
602 /* now, execute the prepared statement to insert 10 records.. */
603 for (o_tiny_data= 0; o_tiny_data < 100; o_tiny_data++)
604 {
605 len= sprintf(data, "MySQL%d", o_int_data);
606
607 rc= mysql_stmt_fetch(stmt);
608 check_stmt_rc(rc, stmt);
609
610 FAIL_UNLESS(tiny_data == o_tiny_data, "Wrong value for tiny_data");
611 FAIL_UNLESS(is_null[0] == 0, "Wrong value for is_null");
612 FAIL_UNLESS(length[0] == 1, "length != 0");
613
614 FAIL_UNLESS(int_data == o_int_data, "Wrong value for int_data");
615 FAIL_UNLESS(length[2] == 4, "length != 4");
616
617 FAIL_UNLESS(small_data == o_small_data, "Wrong value for small_data");
618 FAIL_UNLESS(length[3] == 2, "length != 2");
619
620 FAIL_UNLESS(big_data == o_big_data, "Wrong value for big_data");
621 FAIL_UNLESS(length[4] == 8, "length != 8");
622
623 FAIL_UNLESS(real_data == o_real_data, "Wrong value for real_data");
624 FAIL_UNLESS(length[5] == 4, "length != 4");
625
626 FAIL_UNLESS(double_data == o_double_data, "Wrong value for double_data");
627 FAIL_UNLESS(length[6] == 8, "length != 8");
628
629 FAIL_UNLESS(strcmp(data, str_data) == 0, "Wrong value for data");
630 FAIL_UNLESS(length[1] == len, "length != len");
631
632 o_int_data += 25;
633 o_small_data += 10;
634 o_big_data += 100;
635 o_real_data += 1;
636 o_double_data += 10.09;
637 }
638
639 rc= mysql_stmt_fetch(stmt);
640 FAIL_UNLESS(rc == MYSQL_NO_DATA, "MYSQL_NO_DATA expected");
641
642 mysql_stmt_close(stmt);
643 rc= mysql_query(mysql, "DROP TABLE IF EXISTS my_prepare");
644 check_mysql_rc(rc, mysql);
645
646 return OK;
647}
648
649static int test_prepare_multi_statements(MYSQL *mysql)
650{
651 MYSQL_STMT *stmt;
652 char query[MAX_TEST_QUERY_LENGTH];
653 int rc;
654
655 strcpy(query, "select 1; select 'another value'");
656
657 stmt= mysql_stmt_init(mysql);
658 FAIL_IF(!stmt, mysql_error(mysql));
659 rc= mysql_stmt_prepare(stmt, SL(query));
660 FAIL_IF(!rc, "Error expected");
661
662 mysql_stmt_close(stmt);
663
664 return OK;
665}
666
667static int test_prepare_ext(MYSQL *mysql)
668{
669 MYSQL_STMT *stmt;
670 int rc;
671 char *sql;
672 int nData= 1;
673 char tData= 1;
674 short sData= 10;
675 longlong bData= 20;
676 int rowcount= 0;
677 MYSQL_BIND my_bind[6];
678 char query[MAX_TEST_QUERY_LENGTH];
679
680 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_ext");
681 check_mysql_rc(rc, mysql)
682
683 sql= (char *)"CREATE TABLE test_prepare_ext"
684 "("
685 " c1 tinyint,"
686 " c2 smallint,"
687 " c3 mediumint,"
688 " c4 int,"
689 " c5 integer,"
690 " c6 bigint,"
691 " c7 float,"
692 " c8 double,"
693 " c9 double precision,"
694 " c10 real,"
695 " c11 decimal(7, 4),"
696 " c12 numeric(8, 4),"
697 " c13 date,"
698 " c14 datetime,"
699 " c15 timestamp,"
700 " c16 time,"
701 " c17 year,"
702 " c18 bit,"
703 " c19 bool,"
704 " c20 char,"
705 " c21 char(10),"
706 " c22 varchar(30),"
707 " c23 tinyblob,"
708 " c24 tinytext,"
709 " c25 blob,"
710 " c26 text,"
711 " c27 mediumblob,"
712 " c28 mediumtext,"
713 " c29 longblob,"
714 " c30 longtext,"
715 " c31 enum('one', 'two', 'three'),"
716 " c32 set('monday', 'tuesday', 'wednesday'))";
717
718 rc= mysql_query(mysql, sql);
719 check_mysql_rc(rc, mysql)
720
721 /* insert by prepare - all integers */
722 strcpy(query, "INSERT INTO test_prepare_ext(c1, c2, c3, c4, c5, c6) VALUES(?, ?, ?, ?, ?, ?)");
723 stmt= mysql_stmt_init(mysql);
724 FAIL_IF(!stmt, mysql_error(mysql));
725 rc= mysql_stmt_prepare(stmt, SL(query));
726 check_stmt_rc(rc, stmt);
727
728 FAIL_IF(mysql_stmt_param_count(stmt) != 6, "Paramcount != 6");
729
730 memset(my_bind, '\0', sizeof(my_bind));
731
732 /*tinyint*/
733 my_bind[0].buffer_type= MYSQL_TYPE_TINY;
734 my_bind[0].buffer= (void *)&tData;
735
736 /*smallint*/
737 my_bind[1].buffer_type= MYSQL_TYPE_SHORT;
738 my_bind[1].buffer= (void *)&sData;
739
740 /*mediumint*/
741 my_bind[2].buffer_type= MYSQL_TYPE_LONG;
742 my_bind[2].buffer= (void *)&nData;
743
744 /*int*/
745 my_bind[3].buffer_type= MYSQL_TYPE_LONG;
746 my_bind[3].buffer= (void *)&nData;
747
748 /*integer*/
749 my_bind[4].buffer_type= MYSQL_TYPE_LONG;
750 my_bind[4].buffer= (void *)&nData;
751
752 /*bigint*/
753 my_bind[5].buffer_type= MYSQL_TYPE_LONGLONG;
754 my_bind[5].buffer= (void *)&bData;
755
756 rc= mysql_stmt_bind_param(stmt, my_bind);
757 check_stmt_rc(rc, stmt);
758
759 /*
760 * integer to integer
761 */
762 for (nData= 0; nData<10; nData++, tData++, sData++, bData++)
763 {
764 rc= mysql_stmt_execute(stmt);
765 check_stmt_rc(rc, stmt);
766 }
767 mysql_stmt_close(stmt);
768
769 /* now fetch the results ..*/
770
771 strcpy(query, "SELECT c1, c2, c3, c4, c5, c6 FROM test_prepare_ext");
772 stmt= mysql_stmt_init(mysql);
773 FAIL_IF(!stmt, mysql_error(mysql));
774 rc= mysql_stmt_prepare(stmt, SL(query));
775 check_stmt_rc(rc, stmt);
776
777 /* get the result */
778 rc= mysql_stmt_execute(stmt);
779 check_stmt_rc(rc, stmt);
780
781 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
782 rowcount++;
783
784 FAIL_UNLESS(nData == rowcount, "Invalid rowcount");
785
786 mysql_stmt_close(stmt);
787 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_ext");
788 check_mysql_rc(rc, mysql)
789
790 return OK;
791}
792
793static int test_prepare_alter(MYSQL *mysql)
794{
795 MYSQL_STMT *stmt;
796 MYSQL *mysql_new;
797 int rc, id;
798 MYSQL_BIND my_bind[1];
799 my_bool is_null;
800 char query[MAX_TEST_QUERY_LENGTH];
801
802 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prep_alter");
803 check_mysql_rc(rc, mysql);
804
805 rc= mysql_query(mysql, "CREATE TABLE test_prep_alter(id int, name char(20))");
806 check_mysql_rc(rc, mysql);
807
808 rc= mysql_query(mysql, "INSERT INTO test_prep_alter values(10, 'venu'), (20, 'mysql')");
809 check_mysql_rc(rc, mysql);
810
811 strcpy(query, "INSERT INTO test_prep_alter VALUES(?, 'monty')");
812 stmt= mysql_stmt_init(mysql);
813 FAIL_IF(!stmt, mysql_error(mysql));
814 rc= mysql_stmt_prepare(stmt, SL(query));
815 check_stmt_rc(rc, stmt);
816
817 FAIL_IF(mysql_stmt_param_count(stmt) != 1, "Paramcount != 1");
818
819 memset(my_bind, '\0', sizeof(my_bind));
820
821 is_null= 0;
822 my_bind[0].buffer_type= MYSQL_TYPE_SHORT;
823 my_bind[0].buffer= (void *)&id;
824 my_bind[0].is_null= &is_null;
825
826 rc= mysql_stmt_bind_param(stmt, my_bind);
827 check_stmt_rc(rc, stmt);
828
829 id= 30;
830 rc= mysql_stmt_execute(stmt);
831 check_stmt_rc(rc, stmt);
832
833 mysql_new= mysql_init(NULL);
834 FAIL_IF(!mysql_new, "mysql_init failed");
835 FAIL_IF(!(my_test_connect(mysql_new, hostname, username, password,
836 schema, port, socketname, 0)), "my_test_connect failed");
837 rc= mysql_query(mysql_new, "ALTER TABLE test_prep_alter change id id_new varchar(20)");
838 diag("Error: %d %s", mysql_errno(mysql_new), mysql_error(mysql_new));
839 check_mysql_rc(rc, mysql_new);
840 mysql_close(mysql_new);
841
842 is_null= 1;
843 rc= mysql_stmt_execute(stmt);
844 check_stmt_rc(rc, stmt);
845
846 rc= my_stmt_result(mysql, "SELECT * FROM test_prep_alter");
847 FAIL_UNLESS(rc == 4, "rowcount != 4");
848
849 mysql_stmt_close(stmt);
850 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prep_alter");
851 check_mysql_rc(rc, mysql);
852
853 return OK;
854}
855
856static int test_prepare_resultset(MYSQL *mysql)
857{
858 MYSQL_STMT *stmt;
859 int rc;
860 MYSQL_RES *result;
861 char query[MAX_TEST_QUERY_LENGTH];
862
863 rc= mysql_autocommit(mysql, TRUE);
864 check_mysql_rc(rc, mysql);
865
866 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_resultset");
867 check_mysql_rc(rc, mysql);
868
869 rc= mysql_query(mysql, "CREATE TABLE test_prepare_resultset(id int, \
870 name varchar(50), extra double)");
871 check_mysql_rc(rc, mysql);
872
873 stmt= mysql_stmt_init(mysql);
874 strcpy(query, "SELECT * FROM test_prepare_resultset");
875 rc= mysql_stmt_prepare(stmt, SL(query));
876 check_stmt_rc(rc, stmt);
877
878 FAIL_IF(mysql_stmt_param_count(stmt), "Paramcount != 0");
879
880 result= mysql_stmt_result_metadata(stmt);
881 FAIL_IF(!result, "Invalid resultset");
882 mysql_free_result(result);
883 mysql_stmt_close(stmt);
884 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prepare_resultset");
885 check_mysql_rc(rc, mysql);
886
887 return OK;
888}
889
890/* Test the direct query execution in the middle of open stmts */
891
892static int test_open_direct(MYSQL *mysql)
893{
894 MYSQL_STMT *stmt;
895 MYSQL_RES *result;
896 int rc;
897 char query[MAX_TEST_QUERY_LENGTH];
898
899 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_open_direct");
900 check_mysql_rc(rc, mysql);
901
902 rc= mysql_query(mysql, "CREATE TABLE test_open_direct(id int, name char(6))");
903 check_mysql_rc(rc, mysql);
904
905 strcpy(query, "INSERT INTO test_open_direct values(10, 'mysql')");
906 stmt= mysql_stmt_init(mysql);
907 FAIL_IF(!stmt, mysql_error(mysql));
908 rc= mysql_stmt_prepare(stmt, SL(query));
909 check_stmt_rc(rc, stmt);
910
911 rc= mysql_query(mysql, "SELECT * FROM test_open_direct");
912
913 result= mysql_store_result(mysql);
914 FAIL_IF(!result, "invalid resultset");
915
916 FAIL_IF(mysql_num_rows(result), "rowcount != 0");
917 mysql_free_result(result);
918
919 rc= mysql_stmt_execute(stmt);
920 check_stmt_rc(rc, stmt);
921
922 FAIL_IF(mysql_stmt_affected_rows(stmt) != 1, "affected rows != 1");
923
924 rc= mysql_query(mysql, "SELECT * FROM test_open_direct");
925 check_mysql_rc(rc, mysql);
926
927 result= mysql_store_result(mysql);
928 FAIL_IF(!result, "invalid resultset");
929
930 FAIL_IF(mysql_num_rows(result) != 1, "rowcount != 1");
931 mysql_free_result(result);
932
933 rc= mysql_stmt_execute(stmt);
934 check_stmt_rc(rc, stmt);
935
936 FAIL_IF(mysql_stmt_affected_rows(stmt) != 1, "affected rows != 1");
937
938 rc= mysql_query(mysql, "SELECT * FROM test_open_direct");
939 check_mysql_rc(rc, mysql);
940
941 result= mysql_store_result(mysql);
942 FAIL_IF(!result, "Invalid resultset");
943 FAIL_IF(mysql_num_rows(result) != 2, "rowcount != 2");
944
945 mysql_free_result(result);
946
947 mysql_stmt_close(stmt);
948
949 /* run a direct query in the middle of a fetch */
950
951 strcpy(query, "SELECT * FROM test_open_direct");
952 stmt= mysql_stmt_init(mysql);
953 FAIL_IF(!stmt, mysql_error(mysql));
954 rc= mysql_stmt_prepare(stmt, SL(query));
955 check_stmt_rc(rc, stmt);
956
957 rc= mysql_stmt_execute(stmt);
958 check_stmt_rc(rc, stmt);
959
960 rc= mysql_stmt_fetch(stmt);
961 check_stmt_rc(rc, stmt);
962
963 rc= mysql_query(mysql, "INSERT INTO test_open_direct(id) VALUES(20)");
964 FAIL_IF(!rc, "Error expected");
965
966 rc= mysql_stmt_close(stmt);
967 check_stmt_rc(rc, stmt);
968
969 rc= mysql_query(mysql, "INSERT INTO test_open_direct(id) VALUES(20)");
970 check_mysql_rc(rc, mysql);
971
972 /* run a direct query with store result */
973 stmt= mysql_stmt_init(mysql);
974 FAIL_IF(!stmt, mysql_error(mysql));
975 rc= mysql_stmt_prepare(stmt, SL(query));
976 check_stmt_rc(rc, stmt);
977
978 rc= mysql_stmt_execute(stmt);
979 check_stmt_rc(rc, stmt);
980
981 rc= mysql_stmt_store_result(stmt);
982 check_stmt_rc(rc, stmt);
983
984 rc= mysql_stmt_fetch(stmt);
985 check_stmt_rc(rc, stmt);
986
987 rc= mysql_query(mysql, "drop table test_open_direct");
988 check_mysql_rc(rc, mysql);
989
990 rc= mysql_stmt_close(stmt);
991 check_stmt_rc(rc, stmt);
992
993 return OK;
994}
995
996static int test_select_show(MYSQL *mysql)
997{
998 MYSQL_STMT *stmt;
999 int rc;
1000 char query[MAX_TEST_QUERY_LENGTH];
1001 int rowcount;
1002
1003 rc= mysql_autocommit(mysql, TRUE);
1004 check_mysql_rc(rc, mysql);
1005
1006 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_show");
1007 check_mysql_rc(rc, mysql);
1008
1009 rc= mysql_query(mysql, "CREATE TABLE test_show(id int(4) NOT NULL primary "
1010 " key, name char(2))");
1011 check_mysql_rc(rc, mysql);
1012
1013 strcpy(query, "show columns from test_show");
1014 stmt= mysql_stmt_init(mysql);
1015 FAIL_IF(!stmt, mysql_error(mysql));
1016 rc= mysql_stmt_prepare(stmt, SL(query));
1017 check_stmt_rc(rc, stmt);
1018
1019 FAIL_IF(mysql_stmt_param_count(stmt) != 0, "Paramcount != 0");
1020
1021 rc= mysql_stmt_execute(stmt);
1022 check_stmt_rc(rc, stmt);
1023
1024 rowcount= 0;
1025 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
1026 rowcount++;
1027 FAIL_IF(rowcount != 2, "rowcount != 2");
1028
1029 mysql_stmt_close(stmt);
1030
1031 strcpy(query, "show tables from mysql like ?");
1032 stmt= mysql_stmt_init(mysql);
1033 FAIL_IF(!stmt, mysql_error(mysql));
1034 rc= mysql_stmt_prepare(stmt, SL(query));
1035 FAIL_IF(!rc, "Error expected");
1036
1037 strcpy(query, "show tables like \'test_show\'");
1038 FAIL_IF(!stmt, mysql_error(mysql));
1039 rc= mysql_stmt_prepare(stmt, SL(query));
1040 check_stmt_rc(rc, stmt);
1041
1042 rc= mysql_stmt_execute(stmt);
1043 check_stmt_rc(rc, stmt);
1044
1045 rowcount= 0;
1046 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
1047 rowcount++;
1048 FAIL_IF(rowcount != 1, "rowcount != 1");
1049 mysql_stmt_close(stmt);
1050
1051 strcpy(query, "describe test_show");
1052 stmt= mysql_stmt_init(mysql);
1053 FAIL_IF(!stmt, mysql_error(mysql));
1054 rc= mysql_stmt_prepare(stmt, SL(query));
1055 check_stmt_rc(rc, stmt);
1056
1057 rc= mysql_stmt_execute(stmt);
1058 check_stmt_rc(rc, stmt);
1059
1060 rowcount= 0;
1061 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
1062 rowcount++;
1063 FAIL_IF(rowcount != 2, "rowcount != 2");
1064 mysql_stmt_close(stmt);
1065
1066 strcpy(query, "show keys from test_show");
1067 stmt= mysql_stmt_init(mysql);
1068 FAIL_IF(!stmt, mysql_error(mysql));
1069 rc= mysql_stmt_prepare(stmt, SL(query));
1070 check_stmt_rc(rc, stmt);
1071
1072 rc= mysql_stmt_execute(stmt);
1073 check_stmt_rc(rc, stmt);
1074
1075 rowcount= 0;
1076 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
1077 rowcount++;
1078 FAIL_IF(rowcount != 1, "rowcount != 1");
1079
1080 mysql_stmt_close(stmt);
1081 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_show");
1082 check_mysql_rc(rc, mysql);
1083
1084 return OK;
1085}
1086
1087static int test_simple_update(MYSQL *mysql)
1088{
1089 MYSQL_STMT *stmt;
1090 int rc;
1091 char szData[25];
1092 int nData= 1;
1093 MYSQL_RES *result;
1094 MYSQL_BIND my_bind[2];
1095 ulong length[2];
1096 int rowcount= 0;
1097 char query[MAX_TEST_QUERY_LENGTH];
1098
1099 rc= mysql_autocommit(mysql, TRUE);
1100 check_mysql_rc(rc, mysql);
1101
1102 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_update");
1103 check_mysql_rc(rc, mysql);
1104
1105 rc= mysql_query(mysql, "CREATE TABLE test_update(col1 int, "
1106 " col2 varchar(50), col3 int )");
1107 check_mysql_rc(rc, mysql);
1108
1109 rc= mysql_query(mysql, "INSERT INTO test_update VALUES(1, 'MySQL', 100)");
1110 check_mysql_rc(rc, mysql);
1111
1112 FAIL_IF(mysql_affected_rows(mysql) != 1, "Affected rows != 1");
1113
1114 rc= mysql_commit(mysql);
1115 check_mysql_rc(rc, mysql);
1116
1117 /* insert by prepare */
1118 strcpy(query, "UPDATE test_update SET col2= ? WHERE col1= ?");
1119 stmt= mysql_stmt_init(mysql);
1120 FAIL_IF(!stmt, mysql_error(mysql));
1121 rc= mysql_stmt_prepare(stmt, SL(query));
1122 check_stmt_rc(rc, stmt);
1123
1124 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1125
1126 memset(my_bind, '\0', sizeof(my_bind));
1127
1128 nData= 1;
1129 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
1130 my_bind[0].buffer= szData; /* string data */
1131 my_bind[0].buffer_length= sizeof(szData);
1132 my_bind[0].length= &length[0];
1133 length[0]= sprintf(szData, "updated-data");
1134
1135 my_bind[1].buffer= (void *) &nData;
1136 my_bind[1].buffer_type= MYSQL_TYPE_LONG;
1137
1138 rc= mysql_stmt_bind_param(stmt, my_bind);
1139 check_stmt_rc(rc, stmt);
1140
1141 rc= mysql_stmt_execute(stmt);
1142 check_stmt_rc(rc, stmt);
1143 FAIL_IF(mysql_stmt_affected_rows(stmt) != 1, "Affected_rows != 1");
1144
1145 mysql_stmt_close(stmt);
1146
1147 /* now fetch the results ..*/
1148 rc= mysql_commit(mysql);
1149 check_mysql_rc(rc, mysql);
1150
1151 /* test the results now, only one row should exist */
1152 rc= mysql_query(mysql, "SELECT * FROM test_update");
1153 check_mysql_rc(rc, mysql);
1154
1155 /* get the result */
1156 result= mysql_store_result(mysql);
1157 FAIL_IF(!result, "Invalid resultset");
1158
1159 while (mysql_fetch_row(result))
1160 rowcount++;
1161
1162 FAIL_IF(rowcount != 1, "rowcount != 1");
1163
1164 mysql_free_result(result);
1165 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_update");
1166 check_mysql_rc(rc, mysql);
1167
1168 return OK;
1169}
1170
1171
1172/* Test simple long data handling */
1173
1174static int test_long_data(MYSQL *mysql)
1175{
1176 MYSQL_STMT *stmt;
1177 int rc, int_data;
1178 char *data= NullS;
1179 MYSQL_RES *result;
1180 MYSQL_BIND my_bind[3];
1181 int rowcount;
1182 char query[MAX_TEST_QUERY_LENGTH];
1183
1184 rc= mysql_autocommit(mysql, TRUE);
1185 check_mysql_rc(rc, mysql);
1186
1187 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_long_data");
1188 check_mysql_rc(rc, mysql);
1189
1190 rc= mysql_query(mysql, "CREATE TABLE test_long_data(col1 int, "
1191 " col2 long varchar, col3 long varbinary)");
1192 check_mysql_rc(rc, mysql);
1193
1194 strcpy(query, "INSERT INTO test_long_data(col1, col2) VALUES(?)");
1195 stmt= mysql_stmt_init(mysql);
1196 FAIL_IF(!stmt, mysql_error(mysql));
1197 rc= mysql_stmt_prepare(stmt, SL(query));
1198 FAIL_IF(!rc, "Error expected");
1199 rc= mysql_stmt_close(stmt);
1200 check_stmt_rc(rc, stmt);
1201
1202 strcpy(query, "INSERT INTO test_long_data(col1, col2, col3) VALUES(?, ?, ?)");
1203 stmt= mysql_stmt_init(mysql);
1204 FAIL_IF(!stmt, mysql_error(mysql));
1205 rc= mysql_stmt_prepare(stmt, SL(query));
1206 check_stmt_rc(rc, stmt)
1207
1208 FAIL_IF(mysql_stmt_param_count(stmt) != 3, "Paramcount != 3");
1209
1210 memset(my_bind, '\0', sizeof(my_bind));
1211
1212 my_bind[0].buffer= (void *)&int_data;
1213 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
1214
1215 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
1216
1217 my_bind[2]= my_bind[1];
1218 rc= mysql_stmt_bind_param(stmt, my_bind);
1219 check_stmt_rc(rc, stmt);
1220
1221 int_data= 999;
1222 data= (char *)"Michael";
1223
1224 /* supply data in pieces */
1225 rc= mysql_stmt_send_long_data(stmt, 1, SL(data));
1226 check_stmt_rc(rc, stmt);
1227 data= (char *)" 'Monty' Widenius";
1228 rc= mysql_stmt_send_long_data(stmt, 1, SL(data));
1229 check_stmt_rc(rc, stmt);
1230 rc= mysql_stmt_send_long_data(stmt, 2, "Venu (venu@mysql.com)", 4);
1231 check_stmt_rc(rc, stmt);
1232
1233 /* execute */
1234 rc= mysql_stmt_execute(stmt);
1235 check_stmt_rc(rc, stmt);
1236
1237 rc= mysql_commit(mysql);
1238 check_mysql_rc(rc, mysql);
1239
1240 /* now fetch the results ..*/
1241 rc= mysql_query(mysql, "SELECT * FROM test_long_data");
1242 check_mysql_rc(rc, mysql);
1243
1244 /* get the result */
1245 result= mysql_store_result(mysql);
1246 FAIL_IF(!result, "Invalid result set");
1247
1248 rowcount= 0;
1249 while (mysql_fetch_row(result))
1250 rowcount++;
1251 FAIL_IF(rowcount != 1, "rowcount != 1");
1252 mysql_free_result(result);
1253
1254 if (verify_col_data(mysql, "test_long_data", "col1", "999"))
1255 goto error;
1256 if (verify_col_data(mysql, "test_long_data", "col2", "Michael 'Monty' Widenius"))
1257 goto error;
1258 if (verify_col_data(mysql, "test_long_data", "col3", "Venu"))
1259 goto error;
1260
1261 mysql_stmt_close(stmt);
1262 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_long_data");
1263 check_mysql_rc(rc, mysql);
1264 return OK;
1265
1266error:
1267 mysql_stmt_close(stmt);
1268 return FAIL;
1269}
1270
1271
1272/* Test long data (string) handling */
1273
1274static int test_long_data_str(MYSQL *mysql)
1275{
1276 MYSQL_STMT *stmt;
1277 int rc, i, rowcount= 0;
1278 char data[255];
1279 long length;
1280 ulong length1;
1281 MYSQL_RES *result;
1282 MYSQL_BIND my_bind[2];
1283 my_bool is_null[2];
1284 char query[MAX_TEST_QUERY_LENGTH];
1285
1286 rc= mysql_autocommit(mysql, TRUE);
1287 check_mysql_rc(rc, mysql);
1288
1289 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_long_data_str");
1290 check_mysql_rc(rc, mysql);
1291
1292 rc= mysql_query(mysql, "CREATE TABLE test_long_data_str(id int, longstr long varchar)");
1293 check_mysql_rc(rc, mysql);
1294
1295 strcpy(query, "INSERT INTO test_long_data_str VALUES(?, ?)");
1296 stmt= mysql_stmt_init(mysql);
1297 FAIL_IF(!stmt, mysql_error(mysql));
1298 rc= mysql_stmt_prepare(stmt, SL(query));
1299 check_stmt_rc(rc, stmt)
1300
1301 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1302
1303 memset(my_bind, '\0', sizeof(my_bind));
1304
1305 my_bind[0].buffer= (void *)&length;
1306 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
1307 my_bind[0].is_null= &is_null[0];
1308 is_null[0]= 0;
1309 length= 0;
1310
1311 my_bind[1].buffer= data; /* string data */
1312 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
1313 my_bind[1].length= &length1;
1314 my_bind[1].is_null= &is_null[1];
1315 is_null[1]= 0;
1316 rc= mysql_stmt_bind_param(stmt, my_bind);
1317 check_stmt_rc(rc, stmt);
1318
1319 length= 40;
1320 strcpy(data, "MySQL AB");
1321
1322 /* supply data in pieces */
1323 for(i= 0; i < 4; i++)
1324 {
1325 rc= mysql_stmt_send_long_data(stmt, 1, (char *)data, 5);
1326 check_stmt_rc(rc, stmt);
1327 }
1328 /* execute */
1329 rc= mysql_stmt_execute(stmt);
1330 check_stmt_rc(rc, stmt);
1331
1332 mysql_stmt_close(stmt);
1333
1334 rc= mysql_commit(mysql);
1335 check_mysql_rc(rc, mysql);
1336
1337 /* now fetch the results ..*/
1338 rc= mysql_query(mysql, "SELECT LENGTH(longstr), longstr FROM test_long_data_str");
1339 check_mysql_rc(rc, mysql);
1340
1341 /* get the result */
1342 result= mysql_store_result(mysql);
1343 FAIL_IF(!result, "Invalid result set");
1344
1345 while (mysql_fetch_row(result))
1346 rowcount++;
1347 FAIL_IF(rowcount != 1, "rowcount != 1");
1348
1349 mysql_free_result(result);
1350
1351 sprintf(data, "%d", i*5);
1352 if (verify_col_data(mysql, "test_long_data_str", "LENGTH(longstr)", data))
1353 goto error;
1354 strcpy(data, "MySQLMySQLMySQLMySQL");
1355 if (verify_col_data(mysql, "test_long_data_str", "longstr", data))
1356 goto error;
1357
1358 rc= mysql_query(mysql, "DROP TABLE test_long_data_str");
1359 check_mysql_rc(rc, mysql);
1360
1361 return OK;
1362
1363error:
1364 rc= mysql_query(mysql, "DROP TABLE test_long_data_str");
1365 check_mysql_rc(rc, mysql);
1366 return FAIL;
1367}
1368
1369
1370/* Test long data (string) handling */
1371
1372static int test_long_data_str1(MYSQL *mysql)
1373{
1374 MYSQL_STMT *stmt;
1375 int rc, i, rowcount= 0;
1376 char data[255];
1377 long length;
1378 unsigned long max_blob_length, blob_length, length1;
1379 my_bool true_value;
1380 MYSQL_RES *result;
1381 MYSQL_BIND my_bind[2];
1382 MYSQL_FIELD *field;
1383 char query[MAX_TEST_QUERY_LENGTH];
1384
1385 rc= mysql_autocommit(mysql, TRUE);
1386 check_mysql_rc(rc, mysql);
1387
1388 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_long_data_str");
1389 check_mysql_rc(rc, mysql);
1390
1391 rc= mysql_query(mysql, "CREATE TABLE test_long_data_str(longstr long varchar, blb long varbinary)");
1392 check_mysql_rc(rc, mysql);
1393
1394 strcpy(query, "INSERT INTO test_long_data_str VALUES(?, ?)");
1395 stmt= mysql_stmt_init(mysql);
1396 FAIL_IF(!stmt, mysql_error(mysql));
1397 rc= mysql_stmt_prepare(stmt, SL(query));
1398 check_stmt_rc(rc, stmt)
1399
1400 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1401
1402 memset(my_bind, '\0', sizeof(my_bind));
1403
1404 my_bind[0].buffer= data; /* string data */
1405 my_bind[0].buffer_length= sizeof(data);
1406 my_bind[0].length= (unsigned long *)&length1;
1407 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
1408 length1= 0;
1409
1410 my_bind[1]= my_bind[0];
1411 my_bind[1].buffer_type= MYSQL_TYPE_BLOB;
1412
1413 rc= mysql_stmt_bind_param(stmt, my_bind);
1414 check_stmt_rc(rc, stmt);
1415 length= sprintf(data, "MySQL AB");
1416
1417 /* supply data in pieces */
1418 for (i= 0; i < 3; i++)
1419 {
1420 rc= mysql_stmt_send_long_data(stmt, 0, data, length);
1421 check_stmt_rc(rc, stmt);
1422
1423 rc= mysql_stmt_send_long_data(stmt, 1, data, 2);
1424 check_stmt_rc(rc, stmt);
1425 }
1426
1427 /* execute */
1428 rc= mysql_stmt_execute(stmt);
1429 check_stmt_rc(rc, stmt);
1430
1431 mysql_stmt_close(stmt);
1432
1433 rc= mysql_commit(mysql);
1434 check_mysql_rc(rc, mysql);
1435
1436 /* now fetch the results ..*/
1437 rc= mysql_query(mysql, "SELECT LENGTH(longstr), longstr, LENGTH(blb), blb FROM test_long_data_str");
1438 check_mysql_rc(rc, mysql);
1439
1440 /* get the result */
1441 result= mysql_store_result(mysql);
1442
1443 mysql_field_seek(result, 1);
1444 field= mysql_fetch_field(result);
1445 max_blob_length= field->max_length;
1446
1447 FAIL_IF(!result, "Invalid result set");
1448
1449 while (mysql_fetch_row(result))
1450 rowcount++;
1451
1452 FAIL_IF(rowcount != 1, "rowcount != 1");
1453 mysql_free_result(result);
1454
1455 sprintf(data, "%ld", (long)i*length);
1456 if (verify_col_data(mysql, "test_long_data_str", "length(longstr)", data))
1457 return FAIL;
1458
1459 sprintf(data, "%d", i*2);
1460 if (verify_col_data(mysql, "test_long_data_str", "length(blb)", data))
1461 return FAIL;
1462
1463 /* Test length of field->max_length */
1464 strcpy(query, "SELECT * from test_long_data_str");
1465 stmt= mysql_stmt_init(mysql);
1466 FAIL_IF(!stmt, mysql_error(mysql));
1467 rc= mysql_stmt_prepare(stmt, SL(query));
1468 check_stmt_rc(rc, stmt)
1469
1470 FAIL_IF(mysql_stmt_param_count(stmt) != 0, "Paramcount != 0");
1471
1472 rc= mysql_stmt_execute(stmt);
1473 check_stmt_rc(rc, stmt);
1474
1475 rc= mysql_stmt_store_result(stmt);
1476 check_stmt_rc(rc, stmt);
1477
1478 result= mysql_stmt_result_metadata(stmt);
1479 field= mysql_fetch_fields(result);
1480
1481 /* First test what happens if STMT_ATTR_UPDATE_MAX_LENGTH is not used */
1482 FAIL_IF(field->max_length != 0, "field->max_length != 0");
1483 mysql_free_result(result);
1484
1485 /* Enable updating of field->max_length */
1486 true_value= 1;
1487 mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &true_value);
1488 rc= mysql_stmt_execute(stmt);
1489 check_stmt_rc(rc, stmt);
1490
1491 rc= mysql_stmt_store_result(stmt);
1492 check_stmt_rc(rc, stmt);
1493
1494 result= mysql_stmt_result_metadata(stmt);
1495 field= mysql_fetch_fields(result);
1496
1497 diag("max_length: %lu max_blob_length: %lu", (unsigned long)field->max_length, (unsigned long)max_blob_length);
1498 FAIL_UNLESS(field->max_length == max_blob_length, "field->max_length != max_blob_length");
1499
1500 /* Fetch results into a data buffer that is smaller than data */
1501 memset(my_bind, '\0', sizeof(*my_bind));
1502 my_bind[0].buffer_type= MYSQL_TYPE_BLOB;
1503 my_bind[0].buffer= (void *) &data; /* this buffer won't be altered */
1504 my_bind[0].buffer_length= 16;
1505 my_bind[0].length= (unsigned long *)&blob_length;
1506 my_bind[0].error= &my_bind[0].error_value;
1507 rc= mysql_stmt_bind_result(stmt, my_bind);
1508 data[16]= 0;
1509
1510 rc= mysql_stmt_fetch(stmt);
1511 FAIL_UNLESS(rc == MYSQL_DATA_TRUNCATED, "truncation expected");
1512 FAIL_UNLESS(my_bind[0].error_value, "No error value");
1513 FAIL_UNLESS(strlen(data) == 16, "Invalid string length");
1514 FAIL_UNLESS(blob_length == max_blob_length, "blob_length != max_blob_length");
1515
1516 /* Fetch all data */
1517 memset((my_bind+1), '\0', sizeof(*my_bind));
1518 my_bind[1].buffer_type= MYSQL_TYPE_BLOB;
1519 my_bind[1].buffer= (void *) &data; /* this buffer won't be altered */
1520 my_bind[1].buffer_length= sizeof(data);
1521 my_bind[1].length= (unsigned long *)&blob_length;
1522 memset(data, '\0', sizeof(data));
1523 mysql_stmt_fetch_column(stmt, my_bind+1, 0, 0);
1524 FAIL_UNLESS(strlen(data) == max_blob_length, "strlen(data) != max_blob_length");
1525
1526 mysql_free_result(result);
1527 mysql_stmt_close(stmt);
1528
1529 /* Drop created table */
1530 rc= mysql_query(mysql, "DROP TABLE test_long_data_str");
1531 check_mysql_rc(rc, mysql);
1532
1533 return OK;
1534}
1535
1536
1537/* Test long data (binary) handling */
1538
1539static int test_long_data_bin(MYSQL *mysql)
1540{
1541 MYSQL_STMT *stmt;
1542 int rc, rowcount= 0;
1543 char data[255];
1544 long length;
1545 MYSQL_RES *result;
1546 MYSQL_BIND my_bind[2];
1547 char query[MAX_TEST_QUERY_LENGTH];
1548
1549
1550 rc= mysql_autocommit(mysql, TRUE);
1551 check_mysql_rc(rc, mysql);
1552
1553 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_long_data_bin");
1554 check_mysql_rc(rc, mysql);
1555
1556 rc= mysql_query(mysql, "CREATE TABLE test_long_data_bin(id int, longbin long varbinary)");
1557 check_mysql_rc(rc, mysql);
1558
1559 strcpy(query, "INSERT INTO test_long_data_bin VALUES(?, ?)");
1560 stmt= mysql_stmt_init(mysql);
1561 FAIL_IF(!stmt, mysql_error(mysql));
1562 rc= mysql_stmt_prepare(stmt, SL(query));
1563 check_stmt_rc(rc, stmt)
1564
1565 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1566
1567 memset(my_bind, '\0', sizeof(my_bind));
1568
1569 my_bind[0].buffer= (void *)&length;
1570 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
1571 length= 0;
1572
1573 my_bind[1].buffer= data; /* string data */
1574 my_bind[1].buffer_type= MYSQL_TYPE_LONG_BLOB;
1575 rc= mysql_stmt_bind_param(stmt, my_bind);
1576 check_stmt_rc(rc, stmt);
1577
1578 length= 10;
1579 strcpy(data, "MySQL AB");
1580
1581 /* supply data in pieces */
1582 {
1583 int i;
1584 for (i= 0; i < 100; i++)
1585 {
1586 rc= mysql_stmt_send_long_data(stmt, 1, (char *)data, 4);
1587 check_stmt_rc(rc, stmt);
1588 }
1589 }
1590 /* execute */
1591 rc= mysql_stmt_execute(stmt);
1592 check_stmt_rc(rc, stmt);
1593
1594 mysql_stmt_close(stmt);
1595
1596 rc= mysql_commit(mysql);
1597 check_mysql_rc(rc, mysql);
1598
1599 /* now fetch the results ..*/
1600 rc= mysql_query(mysql, "SELECT LENGTH(longbin), longbin FROM test_long_data_bin");
1601 check_mysql_rc(rc, mysql);
1602
1603 /* get the result */
1604 result= mysql_store_result(mysql);
1605 FAIL_IF(!result, "Invalid result set");
1606
1607 while (mysql_fetch_row(result))
1608 rowcount++;
1609
1610 FAIL_IF(rowcount != 1, "rowcount != 1");
1611 mysql_free_result(result);
1612
1613 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_long_data_bin");
1614 check_mysql_rc(rc, mysql);
1615 return OK;
1616}
1617
1618
1619/* Test simple delete */
1620
1621static int test_simple_delete(MYSQL *mysql)
1622{
1623 MYSQL_STMT *stmt;
1624 int rc, rowcount= 0;
1625 char szData[30]= {0};
1626 int nData= 1;
1627 MYSQL_RES *result;
1628 MYSQL_BIND my_bind[2];
1629 ulong length[2];
1630 char query[MAX_TEST_QUERY_LENGTH];
1631
1632 rc= mysql_autocommit(mysql, TRUE);
1633 check_mysql_rc(rc, mysql);
1634
1635 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_simple_delete");
1636 check_mysql_rc(rc, mysql);
1637
1638 rc= mysql_query(mysql, "CREATE TABLE test_simple_delete(col1 int, \
1639 col2 varchar(50), col3 int )");
1640 check_mysql_rc(rc, mysql);
1641
1642 rc= mysql_query(mysql, "INSERT INTO test_simple_delete VALUES(1, 'MySQL', 100)");
1643 check_mysql_rc(rc, mysql);
1644
1645 FAIL_IF(mysql_affected_rows(mysql) != 1, "Affected rows != 1");
1646
1647 rc= mysql_commit(mysql);
1648 check_mysql_rc(rc, mysql);
1649
1650 /* insert by prepare */
1651 strcpy(query, "DELETE FROM test_simple_delete WHERE col1= ? AND "
1652 "CONVERT(col2 USING utf8)= ? AND col3= 100");
1653 stmt= mysql_stmt_init(mysql);
1654 FAIL_IF(!stmt, mysql_error(mysql));
1655 rc= mysql_stmt_prepare(stmt, SL(query));
1656 check_stmt_rc(rc, stmt)
1657
1658 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1659
1660 memset(my_bind, '\0', sizeof(my_bind));
1661
1662 nData= 1;
1663 strcpy(szData, "MySQL");
1664 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
1665 my_bind[1].buffer= szData; /* string data */
1666 my_bind[1].buffer_length= sizeof(szData);
1667 my_bind[1].length= &length[1];
1668 length[1]= 5;
1669
1670 my_bind[0].buffer= (void *)&nData;
1671 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
1672
1673 rc= mysql_stmt_bind_param(stmt, my_bind);
1674 check_stmt_rc(rc, stmt);
1675
1676 rc= mysql_stmt_execute(stmt);
1677 check_stmt_rc(rc, stmt);
1678
1679 FAIL_IF(mysql_stmt_affected_rows(stmt) != 1, "Affected rows != 1");
1680
1681 mysql_stmt_close(stmt);
1682
1683 /* now fetch the results ..*/
1684 rc= mysql_commit(mysql);
1685 check_mysql_rc(rc, mysql);
1686
1687 /* test the results now, only one row should exist */
1688 rc= mysql_query(mysql, "SELECT * FROM test_simple_delete");
1689 check_mysql_rc(rc, mysql);
1690
1691 /* get the result */
1692 result= mysql_store_result(mysql);
1693 FAIL_IF(!result, "Invalid result set");
1694
1695 while (mysql_fetch_row(result))
1696 rowcount++;
1697
1698 FAIL_IF(rowcount, "rowcount > 0");
1699 mysql_free_result(result);
1700 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_simple_delete");
1701 check_mysql_rc(rc, mysql);
1702
1703 return OK;
1704}
1705
1706static int test_update(MYSQL *mysql)
1707{
1708 MYSQL_STMT *stmt;
1709 int rc;
1710 char szData[25];
1711 int nData= 1, rowcount= 0;
1712 MYSQL_RES *result;
1713 MYSQL_BIND my_bind[2];
1714 ulong length[2];
1715 char query[MAX_TEST_QUERY_LENGTH];
1716
1717 rc= mysql_autocommit(mysql, TRUE);
1718 check_mysql_rc(rc, mysql);
1719
1720 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_update");
1721 check_mysql_rc(rc, mysql);
1722
1723 rc= mysql_query(mysql, "CREATE TABLE test_update("
1724 "col1 int primary key auto_increment, "
1725 "col2 varchar(50), col3 int )");
1726 check_mysql_rc(rc, mysql);
1727
1728 strcpy(query, "INSERT INTO test_update(col2, col3) VALUES(?, ?)");
1729 stmt= mysql_stmt_init(mysql);
1730 FAIL_IF(!stmt, mysql_error(mysql));
1731 rc= mysql_stmt_prepare(stmt, SL(query));
1732 check_stmt_rc(rc, stmt);
1733
1734 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1735
1736 memset(my_bind, '\0', sizeof(my_bind));
1737
1738 /* string data */
1739 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
1740 my_bind[0].buffer= szData;
1741 my_bind[0].buffer_length= sizeof(szData);
1742 my_bind[0].length= &length[0];
1743 length[0]= sprintf(szData, "inserted-data");
1744
1745 my_bind[1].buffer= (void *)&nData;
1746 my_bind[1].buffer_type= MYSQL_TYPE_LONG;
1747
1748 rc= mysql_stmt_bind_param(stmt, my_bind);
1749 check_stmt_rc(rc, stmt);
1750
1751 nData= 100;
1752 rc= mysql_stmt_execute(stmt);
1753 check_stmt_rc(rc, stmt);
1754
1755 FAIL_IF(mysql_stmt_affected_rows(stmt) != 1, "Affected rows != 1");
1756 mysql_stmt_close(stmt);
1757
1758 strcpy(query, "UPDATE test_update SET col2= ? WHERE col3= ?");
1759 stmt= mysql_stmt_init(mysql);
1760 FAIL_IF(!stmt, mysql_error(mysql));
1761 rc= mysql_stmt_prepare(stmt, SL(query));
1762 check_stmt_rc(rc, stmt);
1763
1764 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Paramcount != 2");
1765 nData= 100;
1766
1767 memset(my_bind, '\0', sizeof(my_bind));
1768
1769 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
1770 my_bind[0].buffer= szData;
1771 my_bind[0].buffer_length= sizeof(szData);
1772 my_bind[0].length= &length[0];
1773 length[0]= sprintf(szData, "updated-data");
1774
1775 my_bind[1].buffer= (void *)&nData;
1776 my_bind[1].buffer_type= MYSQL_TYPE_LONG;
1777
1778 rc= mysql_stmt_bind_param(stmt, my_bind);
1779 check_stmt_rc(rc, stmt);
1780
1781 rc= mysql_stmt_execute(stmt);
1782 check_stmt_rc(rc, stmt);
1783 FAIL_IF(mysql_stmt_affected_rows(stmt) != 1, "Affected rows != 1");
1784
1785
1786 mysql_stmt_close(stmt);
1787
1788 /* now fetch the results ..*/
1789 rc= mysql_commit(mysql);
1790 check_mysql_rc(rc, mysql);
1791
1792 /* test the results now, only one row should exist */
1793 rc= mysql_query(mysql, "SELECT * FROM test_update");
1794 check_mysql_rc(rc, mysql);
1795
1796 /* get the result */
1797 result= mysql_store_result(mysql);
1798 FAIL_IF(!result, "Invalid result set");
1799
1800 while (mysql_fetch_row(result))
1801 rowcount++;
1802 FAIL_IF(rowcount != 1, "rowcount != 1");
1803 mysql_free_result(result);
1804 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_update");
1805 check_mysql_rc(rc, mysql);
1806
1807 return OK;
1808}
1809
1810
1811/* Test prepare without parameters */
1812
1813static int test_prepare_noparam(MYSQL *mysql)
1814{
1815 MYSQL_STMT *stmt;
1816 int rc, rowcount= 0;
1817 MYSQL_RES *result;
1818 char query[MAX_TEST_QUERY_LENGTH];
1819
1820 rc= mysql_query(mysql, "DROP TABLE IF EXISTS my_prepare");
1821 check_mysql_rc(rc, mysql);
1822
1823
1824 rc= mysql_query(mysql, "CREATE TABLE my_prepare(col1 int, col2 varchar(50))");
1825 check_mysql_rc(rc, mysql);
1826
1827 /* insert by prepare */
1828 strcpy(query, "INSERT INTO my_prepare VALUES(10, 'venu')");
1829 stmt= mysql_stmt_init(mysql);
1830 FAIL_IF(!stmt, mysql_error(mysql));
1831 rc= mysql_stmt_prepare(stmt, SL(query));
1832 check_stmt_rc(rc, stmt);
1833
1834 FAIL_IF(mysql_stmt_param_count(stmt) != 0, "Paramcount != 0");
1835
1836 rc= mysql_stmt_execute(stmt);
1837 check_stmt_rc(rc, stmt);
1838
1839 mysql_stmt_close(stmt);
1840
1841 /* now fetch the results ..*/
1842 rc= mysql_commit(mysql);
1843 check_mysql_rc(rc, mysql);
1844
1845 /* test the results now, only one row should exist */
1846 rc= mysql_query(mysql, "SELECT * FROM my_prepare");
1847 check_mysql_rc(rc, mysql);
1848
1849 /* get the result */
1850 result= mysql_store_result(mysql);
1851 FAIL_IF(!result, "Invalid result set");
1852
1853 while (mysql_fetch_row(result))
1854 rowcount++;
1855
1856 FAIL_IF(rowcount != 1, "rowcount != 1");
1857 mysql_free_result(result);
1858 rc= mysql_query(mysql, "DROP TABLE IF EXISTS my_prepare");
1859 check_mysql_rc(rc, mysql);
1860
1861 return OK;
1862}
1863
1864
1865/* Test simple bind result */
1866
1867static int test_bind_result(MYSQL *mysql)
1868{
1869 MYSQL_STMT *stmt;
1870 int rc;
1871 int nData;
1872 ulong length1;
1873 char szData[100];
1874 MYSQL_BIND my_bind[2];
1875 my_bool is_null[2];
1876 char query[MAX_TEST_QUERY_LENGTH];
1877
1878 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_bind_result");
1879 check_mysql_rc(rc, mysql);
1880
1881 rc= mysql_query(mysql, "CREATE TABLE test_bind_result(col1 int , col2 varchar(50))");
1882 check_mysql_rc(rc, mysql);
1883
1884 rc= mysql_query(mysql, "INSERT INTO test_bind_result VALUES(10, 'venu')");
1885 check_mysql_rc(rc, mysql);
1886
1887 rc= mysql_query(mysql, "INSERT INTO test_bind_result VALUES(20, 'MySQL')");
1888 check_mysql_rc(rc, mysql);
1889
1890 rc= mysql_query(mysql, "INSERT INTO test_bind_result(col2) VALUES('monty')");
1891 check_mysql_rc(rc, mysql);
1892
1893 rc= mysql_commit(mysql);
1894 check_mysql_rc(rc, mysql);
1895
1896 /* fetch */
1897
1898 memset(my_bind, '\0', sizeof(my_bind));
1899 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
1900 my_bind[0].buffer= (void *) &nData; /* integer data */
1901 my_bind[0].is_null= &is_null[0];
1902
1903 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
1904 my_bind[1].buffer= szData; /* string data */
1905 my_bind[1].buffer_length= sizeof(szData);
1906 my_bind[1].length= &length1;
1907 my_bind[1].is_null= &is_null[1];
1908
1909 strcpy(query, "SELECT * FROM test_bind_result");
1910 stmt= mysql_stmt_init(mysql);
1911 FAIL_IF(!stmt, mysql_error(mysql));
1912 rc= mysql_stmt_prepare(stmt, SL(query));
1913 check_stmt_rc(rc, stmt);
1914
1915 rc= mysql_stmt_bind_result(stmt, my_bind);
1916 check_stmt_rc(rc, stmt);
1917
1918 rc= mysql_stmt_execute(stmt);
1919 check_stmt_rc(rc, stmt);
1920
1921 rc= mysql_stmt_fetch(stmt);
1922 check_stmt_rc(rc, stmt);
1923
1924 FAIL_UNLESS(nData == 10, "nData != 10");
1925 FAIL_UNLESS(strcmp(szData, "venu") == 0, "szData != 'Venu'");
1926 FAIL_UNLESS(length1 == 4, "length1 != 4");
1927
1928 rc= mysql_stmt_fetch(stmt);
1929 check_stmt_rc(rc, stmt);
1930
1931 FAIL_UNLESS(nData == 20, "nData != 20");
1932 FAIL_UNLESS(strcmp(szData, "MySQL") == 0, "szData != 'MySQL'");
1933 FAIL_UNLESS(length1 == 5, "length1 != 5");
1934
1935 rc= mysql_stmt_fetch(stmt);
1936 check_stmt_rc(rc, stmt);
1937
1938 FAIL_UNLESS(is_null[0], "null flag not set");
1939 FAIL_UNLESS(strcmp(szData, "monty") == 0, "szData != 'Monty'");
1940 FAIL_UNLESS(length1 == 5, "length1 != 5");
1941
1942 rc= mysql_stmt_fetch(stmt);
1943 FAIL_UNLESS(rc == MYSQL_NO_DATA, "MYSQL_NO_DATA expected");
1944
1945 mysql_stmt_close(stmt);
1946 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_bind_result");
1947 check_mysql_rc(rc, mysql);
1948
1949 return OK;
1950}
1951
1952static int test_bind_result_ext(MYSQL *mysql)
1953{
1954 MYSQL_STMT *stmt;
1955 int rc, i;
1956 uchar t_data;
1957 short s_data;
1958 int i_data;
1959 longlong b_data;
1960 float f_data;
1961 double d_data;
1962 char szData[20], bData[20];
1963 ulong szLength, bLength;
1964 MYSQL_BIND my_bind[8];
1965 ulong length[8];
1966 my_bool is_null[8];
1967 char query[MAX_TEST_QUERY_LENGTH];
1968
1969 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_bind_result");
1970 check_mysql_rc(rc, mysql);
1971
1972 rc= mysql_query(mysql, "CREATE TABLE test_bind_result(c1 tinyint, "
1973 " c2 smallint, "
1974 " c3 int, c4 bigint, "
1975 " c5 float, c6 double, "
1976 " c7 varbinary(10), "
1977 " c8 varchar(50))");
1978 check_mysql_rc(rc, mysql);
1979
1980 rc= mysql_query(mysql, "INSERT INTO test_bind_result "
1981 "VALUES (19, 2999, 3999, 4999999, "
1982 " 2345.6, 5678.89563, 'venu', 'mysql')");
1983 check_mysql_rc(rc, mysql);
1984
1985 rc= mysql_commit(mysql);
1986 check_mysql_rc(rc, mysql);
1987
1988 memset(my_bind, '\0', sizeof(my_bind));
1989 for (i= 0; i < (int) array_elements(my_bind); i++)
1990 {
1991 my_bind[i].length= &length[i];
1992 my_bind[i].is_null= &is_null[i];
1993 }
1994
1995 my_bind[0].buffer_type= MYSQL_TYPE_TINY;
1996 my_bind[0].buffer= (void *)&t_data;
1997
1998 my_bind[1].buffer_type= MYSQL_TYPE_SHORT;
1999 my_bind[2].buffer_type= MYSQL_TYPE_LONG;
2000
2001 my_bind[3].buffer_type= MYSQL_TYPE_LONGLONG;
2002 my_bind[1].buffer= (void *)&s_data;
2003
2004 my_bind[2].buffer= (void *)&i_data;
2005 my_bind[3].buffer= (void *)&b_data;
2006
2007 my_bind[4].buffer_type= MYSQL_TYPE_FLOAT;
2008 my_bind[4].buffer= (void *)&f_data;
2009
2010 my_bind[5].buffer_type= MYSQL_TYPE_DOUBLE;
2011 my_bind[5].buffer= (void *)&d_data;
2012
2013 my_bind[6].buffer_type= MYSQL_TYPE_STRING;
2014 my_bind[6].buffer= (void *)szData;
2015 my_bind[6].buffer_length= sizeof(szData);
2016 my_bind[6].length= &szLength;
2017
2018 my_bind[7].buffer_type= MYSQL_TYPE_TINY_BLOB;
2019 my_bind[7].buffer= (void *)&bData;
2020 my_bind[7].length= &bLength;
2021 my_bind[7].buffer_length= sizeof(bData);
2022
2023 strcpy(query, "select * from test_bind_result");
2024 stmt= mysql_stmt_init(mysql);
2025 FAIL_IF(!stmt, mysql_error(mysql));
2026 rc= mysql_stmt_prepare(stmt, SL(query));
2027 check_stmt_rc(rc, stmt);
2028
2029 rc= mysql_stmt_bind_result(stmt, my_bind);
2030 check_stmt_rc(rc, stmt);
2031
2032 rc= mysql_stmt_execute(stmt);
2033 check_stmt_rc(rc, stmt);
2034
2035 rc= mysql_stmt_fetch(stmt);
2036 check_stmt_rc(rc, stmt);
2037
2038 FAIL_UNLESS(t_data == 19, "tdata != 19");
2039 FAIL_UNLESS(s_data == 2999, "s_data != 2999");
2040 FAIL_UNLESS(i_data == 3999, "i_data != 3999");
2041 FAIL_UNLESS(b_data == 4999999, "b_data != 4999999");
2042 FAIL_UNLESS(strcmp(szData, "venu") == 0, "szData != 'Venu'");
2043 FAIL_UNLESS(strncmp(bData, "mysql", 5) == 0, "nData != 'mysql'");
2044 FAIL_UNLESS(szLength == 4, "szLength != 4");
2045 FAIL_UNLESS(bLength == 5, "bLength != 5");
2046
2047 rc= mysql_stmt_fetch(stmt);
2048 FAIL_UNLESS(rc == MYSQL_NO_DATA, "MYSQL_NO_DATA expected");
2049
2050 mysql_stmt_close(stmt);
2051 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_bind_result");
2052 check_mysql_rc(rc, mysql);
2053 return OK;
2054}
2055
2056
2057/* Test ext bind result */
2058
2059static int test_bind_result_ext1(MYSQL *mysql)
2060{
2061 MYSQL_STMT *stmt;
2062 uint i;
2063 int rc;
2064 char t_data[20];
2065 float s_data;
2066 short i_data;
2067 uchar b_data;
2068 int f_data;
2069 long bData;
2070 char d_data[20];
2071 double szData;
2072 MYSQL_BIND my_bind[8];
2073 ulong length[8];
2074 my_bool is_null[8];
2075 char query[MAX_TEST_QUERY_LENGTH];
2076
2077 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_bind_result");
2078 check_mysql_rc(rc, mysql);
2079
2080 rc= mysql_query(mysql, "CREATE TABLE test_bind_result(c1 tinyint, c2 smallint, \
2081 c3 int, c4 bigint, \
2082 c5 float, c6 double, \
2083 c7 varbinary(10), \
2084 c8 varchar(10))");
2085 check_mysql_rc(rc, mysql);
2086
2087 rc= mysql_query(mysql, "INSERT INTO test_bind_result VALUES(120, 2999, 3999, 54, \
2088 2.6, 58.89, \
2089 '206', '6.7')");
2090 check_mysql_rc(rc, mysql);
2091
2092 rc= mysql_commit(mysql);
2093 check_mysql_rc(rc, mysql);
2094
2095 memset(my_bind, '\0', sizeof(my_bind));
2096 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
2097 my_bind[0].buffer= (void *) t_data;
2098 my_bind[0].buffer_length= sizeof(t_data);
2099 my_bind[0].error= &my_bind[0].error_value;
2100
2101 my_bind[1].buffer_type= MYSQL_TYPE_FLOAT;
2102 my_bind[1].buffer= (void *)&s_data;
2103 my_bind[1].buffer_length= 0;
2104 my_bind[1].error= &my_bind[1].error_value;
2105
2106 my_bind[2].buffer_type= MYSQL_TYPE_SHORT;
2107 my_bind[2].buffer= (void *)&i_data;
2108 my_bind[2].buffer_length= 0;
2109 my_bind[2].error= &my_bind[2].error_value;
2110
2111 my_bind[3].buffer_type= MYSQL_TYPE_TINY;
2112 my_bind[3].buffer= (void *)&b_data;
2113 my_bind[3].buffer_length= 0;
2114 my_bind[3].error= &my_bind[3].error_value;
2115
2116 my_bind[4].buffer_type= MYSQL_TYPE_LONG;
2117 my_bind[4].buffer= (void *)&f_data;
2118 my_bind[4].buffer_length= 0;
2119 my_bind[4].error= &my_bind[4].error_value;
2120
2121 my_bind[5].buffer_type= MYSQL_TYPE_STRING;
2122 my_bind[5].buffer= (void *)d_data;
2123 my_bind[5].buffer_length= sizeof(d_data);
2124 my_bind[5].error= &my_bind[5].error_value;
2125
2126 my_bind[6].buffer_type= MYSQL_TYPE_LONG;
2127 my_bind[6].buffer= (void *)&bData;
2128 my_bind[6].buffer_length= 0;
2129 my_bind[6].error= &my_bind[6].error_value;
2130
2131 my_bind[7].buffer_type= MYSQL_TYPE_DOUBLE;
2132 my_bind[7].buffer= (void *)&szData;
2133 my_bind[7].buffer_length= 0;
2134 my_bind[7].error= &my_bind[7].error_value;
2135
2136 for (i= 0; i < array_elements(my_bind); i++)
2137 {
2138 my_bind[i].is_null= &is_null[i];
2139 my_bind[i].length= &length[i];
2140 }
2141
2142 strcpy(query, "select * from test_bind_result");
2143 stmt= mysql_stmt_init(mysql);
2144 FAIL_IF(!stmt, mysql_error(mysql));
2145 rc= mysql_stmt_prepare(stmt, SL(query));
2146 check_stmt_rc(rc, stmt);
2147
2148 rc= mysql_stmt_bind_result(stmt, my_bind);
2149 check_stmt_rc(rc, stmt);
2150
2151 rc= mysql_stmt_execute(stmt);
2152 check_stmt_rc(rc, stmt);
2153
2154 rc= mysql_stmt_fetch(stmt);
2155 check_stmt_rc(rc, stmt);
2156
2157 FAIL_UNLESS(strcmp(t_data, "120") == 0, "t_data != 120");
2158 FAIL_UNLESS(i_data == 3999, "i_data != 3999");
2159 FAIL_UNLESS(f_data == 2, "f_data != 2");
2160 FAIL_UNLESS(strcmp(d_data, "58.89") == 0, "d_data != 58.89");
2161 FAIL_UNLESS(b_data == 54, "b_data != 54");
2162
2163 FAIL_UNLESS(length[0] == 3, "Wrong length");
2164 FAIL_UNLESS(length[1] == 4, "Wrong length");
2165 FAIL_UNLESS(length[2] == 2, "Wrong length");
2166 FAIL_UNLESS(length[3] == 1, "Wrong length");
2167 FAIL_UNLESS(length[4] == 4, "Wrong length");
2168 FAIL_UNLESS(length[5] == 5, "Wrong length");
2169 FAIL_UNLESS(length[6] == 4, "Wrong length");
2170 FAIL_UNLESS(length[7] == 8, "Wrong length");
2171
2172 rc= mysql_stmt_fetch(stmt);
2173 FAIL_UNLESS(rc == MYSQL_NO_DATA, "MYSQL_NO_DATA expected");
2174
2175 mysql_stmt_close(stmt);
2176 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_bind_result");
2177 check_mysql_rc(rc, mysql);
2178 return OK;
2179}
2180
2181static int test_bind_negative(MYSQL *mysql)
2182{
2183 MYSQL_STMT *stmt;
2184 char *query;
2185 int rc;
2186 MYSQL_BIND my_bind[1];
2187 int32 my_val= 0;
2188 ulong my_length= 0L;
2189 my_bool my_null= FALSE;
2190
2191 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
2192 check_mysql_rc(rc, mysql);
2193
2194 rc= mysql_query(mysql, "create temporary table t1 (c1 int unsigned)");
2195 check_mysql_rc(rc, mysql);
2196
2197 rc= mysql_query(mysql, "INSERT INTO t1 VALUES (1), (-1)");
2198 check_mysql_rc(rc, mysql);
2199
2200 query= (char*)"INSERT INTO t1 VALUES (?)";
2201 stmt= mysql_stmt_init(mysql);
2202 FAIL_IF(!stmt, mysql_error(mysql));
2203 rc= mysql_stmt_prepare(stmt, SL(query));
2204 check_stmt_rc(rc, stmt);
2205
2206 /* bind parameters */
2207 memset(my_bind, '\0', sizeof(my_bind));
2208
2209 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
2210 my_bind[0].buffer= (void *)&my_val;
2211 my_bind[0].length= &my_length;
2212 my_bind[0].is_null= (char*)&my_null;
2213
2214 rc= mysql_stmt_bind_param(stmt, my_bind);
2215 check_stmt_rc(rc, stmt);
2216
2217 my_val= -1;
2218 rc= mysql_stmt_execute(stmt);
2219 check_stmt_rc(rc, stmt);
2220
2221 mysql_stmt_close(stmt);
2222 rc= mysql_query(mysql, "drop table t1");
2223 check_mysql_rc(rc, mysql);
2224
2225 return OK;
2226}
2227
2228static int test_buffers(MYSQL *mysql)
2229{
2230 MYSQL_STMT *stmt;
2231 MYSQL_BIND my_bind[1];
2232 int rc;
2233 ulong length;
2234 my_bool is_null;
2235 char buffer[20];
2236 char query[MAX_TEST_QUERY_LENGTH];
2237
2238 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_buffer");
2239 check_mysql_rc(rc, mysql);
2240
2241 rc= mysql_query(mysql, "CREATE TABLE test_buffer(str varchar(20))");
2242 check_mysql_rc(rc, mysql);
2243
2244 rc= mysql_query(mysql, "insert into test_buffer values('MySQL')\
2245 , ('Database'), ('Open-Source'), ('Popular')");
2246 check_mysql_rc(rc, mysql);
2247
2248 strcpy(query, "select str from test_buffer");
2249 stmt= mysql_stmt_init(mysql);
2250 FAIL_IF(!stmt, mysql_error(mysql));
2251 rc= mysql_stmt_prepare(stmt, SL(query));
2252 check_stmt_rc(rc, stmt);
2253
2254 rc= mysql_stmt_execute(stmt);
2255 check_stmt_rc(rc, stmt);
2256
2257 memset(buffer, '\0', sizeof(buffer)); /* Avoid overruns in printf() */
2258
2259 memset(my_bind, '\0', sizeof(my_bind));
2260 my_bind[0].length= &length;
2261 my_bind[0].is_null= &is_null;
2262 my_bind[0].buffer_length= 1;
2263 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
2264 my_bind[0].buffer= (void *)buffer;
2265 my_bind[0].error= &my_bind[0].error_value;
2266
2267 rc= mysql_stmt_bind_result(stmt, my_bind);
2268 check_stmt_rc(rc, stmt);
2269
2270 rc= mysql_stmt_store_result(stmt);
2271 check_stmt_rc(rc, stmt);
2272
2273 buffer[1]= 'X';
2274 rc= mysql_stmt_fetch(stmt);
2275
2276 FAIL_UNLESS(rc == MYSQL_DATA_TRUNCATED, "rc != MYSQL_DATA_TRUNCATED");
2277 FAIL_UNLESS(my_bind[0].error_value, "Errorflag not set");
2278 FAIL_UNLESS(buffer[0] == 'M', "buffer[0] != M");
2279 FAIL_UNLESS(buffer[1] == 'X', "buffer[1] != X");
2280 FAIL_UNLESS(length == 5, "length != 5");
2281
2282 my_bind[0].buffer_length= 8;
2283 rc= mysql_stmt_bind_result(stmt, my_bind);/* re-bind */
2284 check_stmt_rc(rc, stmt);
2285
2286 rc= mysql_stmt_fetch(stmt);
2287 check_stmt_rc(rc, stmt);
2288 FAIL_UNLESS(strncmp(buffer, "Database", 8) == 0, "buffer != 'Database'");
2289 FAIL_UNLESS(length == 8, "length != 8");
2290
2291 my_bind[0].buffer_length= 12;
2292 rc= mysql_stmt_bind_result(stmt, my_bind);/* re-bind */
2293 check_stmt_rc(rc, stmt);
2294
2295 rc= mysql_stmt_fetch(stmt);
2296 check_stmt_rc(rc, stmt);
2297 FAIL_UNLESS(strcmp(buffer, "Open-Source") == 0, "buffer != 'Open-Source'");
2298 FAIL_UNLESS(length == 11, "Length != 11");
2299
2300 my_bind[0].buffer_length= 6;
2301 rc= mysql_stmt_bind_result(stmt, my_bind);/* re-bind */
2302 check_stmt_rc(rc, stmt);
2303
2304 rc= mysql_stmt_fetch(stmt);
2305 FAIL_UNLESS(rc == MYSQL_DATA_TRUNCATED, "rc != MYSQL_DATA_TRUNCATED");
2306 FAIL_UNLESS(my_bind[0].error_value, "Errorflag not set");
2307 FAIL_UNLESS(strncmp(buffer, "Popula", 6) == 0, "buffer != 'Popula'");
2308 FAIL_UNLESS(length == 7, "length != 7");
2309
2310 mysql_stmt_close(stmt);
2311 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_buffer");
2312 check_mysql_rc(rc, mysql);
2313
2314 return OK;
2315}
2316
2317static int test_xjoin(MYSQL *mysql)
2318{
2319 MYSQL_STMT *stmt;
2320 int rc, i;
2321 const char *query=
2322 "select t.id, p1.value, n1.value, p2.value, n2.value from t3 t LEFT JOIN t1 p1 ON (p1.id=t.param1_id) LEFT JOIN t2 p2 ON (p2.id=t.param2_id) LEFT JOIN t4 n1 ON (n1.id=p1.name_id) LEFT JOIN t4 n2 ON (n2.id=p2.name_id) where t.id=1";
2323
2324
2325 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2, t3, t4");
2326 check_mysql_rc(rc, mysql);
2327
2328 rc= mysql_query(mysql, "create table t3 (id int(8), param1_id int(8), param2_id int(8)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2329 check_mysql_rc(rc, mysql);
2330
2331 rc= mysql_query(mysql, "create table t1 ( id int(8), name_id int(8), value varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2332 check_mysql_rc(rc, mysql);
2333
2334 rc= mysql_query(mysql, "create table t2 (id int(8), name_id int(8), value varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2335 check_mysql_rc(rc, mysql);
2336
2337 rc= mysql_query(mysql, "create table t4(id int(8), value varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2338 check_mysql_rc(rc, mysql);
2339
2340 rc= mysql_query(mysql, "insert into t3 values (1, 1, 1), (2, 2, null)");
2341 check_mysql_rc(rc, mysql);
2342
2343 rc= mysql_query(mysql, "insert into t1 values (1, 1, 'aaa'), (2, null, 'bbb')");
2344 check_mysql_rc(rc, mysql);
2345
2346 rc= mysql_query(mysql, "insert into t2 values (1, 2, 'ccc')");
2347 check_mysql_rc(rc, mysql);
2348
2349 rc= mysql_query(mysql, "insert into t4 values (1, 'Name1'), (2, null)");
2350 check_mysql_rc(rc, mysql);
2351
2352 stmt= mysql_stmt_init(mysql);
2353 FAIL_IF(!stmt, mysql_error(mysql));
2354 rc= mysql_stmt_prepare(stmt, SL(query));
2355 check_stmt_rc(rc, stmt);
2356
2357 for (i= 0; i < 3; i++)
2358 {
2359 rc= mysql_stmt_execute(stmt);
2360 check_stmt_rc(rc, stmt);
2361 rc= 0;
2362 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
2363 rc++;
2364 FAIL_UNLESS(rc == 1, "rowcount != 1");
2365 }
2366 mysql_stmt_close(stmt);
2367
2368 rc= mysql_query(mysql, "DROP TABLE t1, t2, t3, t4");
2369 check_mysql_rc(rc, mysql);
2370
2371 return OK;
2372}
2373
2374static int test_union_param(MYSQL *mysql)
2375{
2376 MYSQL_STMT *stmt;
2377 char *query;
2378 int rc, i;
2379 MYSQL_BIND my_bind[2];
2380 char my_val[4];
2381 ulong my_length= 3L;
2382 my_bool my_null= FALSE;
2383
2384 strcpy(my_val, "abc");
2385
2386 query= (char*)"select ? as my_col union distinct select ?";
2387 stmt= mysql_stmt_init(mysql);
2388 FAIL_IF(!stmt, mysql_error(mysql));
2389 rc= mysql_stmt_prepare(stmt, SL(query));
2390 check_stmt_rc(rc, stmt);
2391
2392 /*
2393 We need to bzero bind structure because mysql_stmt_bind_param checks all
2394 its members.
2395 */
2396 memset(my_bind, '\0', sizeof(my_bind));
2397
2398 /* bind parameters */
2399 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
2400 my_bind[0].buffer= (char*) &my_val;
2401 my_bind[0].buffer_length= 4;
2402 my_bind[0].length= &my_length;
2403 my_bind[0].is_null= (char*)&my_null;
2404 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
2405 my_bind[1].buffer= (char*) &my_val;
2406 my_bind[1].buffer_length= 4;
2407 my_bind[1].length= &my_length;
2408 my_bind[1].is_null= (char*)&my_null;
2409
2410 rc= mysql_stmt_bind_param(stmt, my_bind);
2411 check_stmt_rc(rc, stmt);
2412
2413 for (i= 0; i < 3; i++)
2414 {
2415 rc= mysql_stmt_execute(stmt);
2416 check_stmt_rc(rc, stmt);
2417 rc= 0;
2418 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
2419 rc++;
2420 FAIL_UNLESS(rc == 1, "rowcount != 1");
2421 }
2422
2423 mysql_stmt_close(stmt);
2424
2425 return OK;
2426}
2427
2428static int test_union(MYSQL *mysql)
2429{
2430 MYSQL_STMT *stmt;
2431 int rc;
2432 const char *query= "SELECT t1.name FROM t1 UNION "
2433 "SELECT t2.name FROM t2";
2434
2435 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2");
2436 check_mysql_rc(rc, mysql);
2437
2438 rc= mysql_query(mysql,
2439 "CREATE TABLE t1 "
2440 "(id INTEGER NOT NULL PRIMARY KEY, "
2441 " name VARCHAR(20) NOT NULL)");
2442 check_mysql_rc(rc, mysql);
2443 rc= mysql_query(mysql,
2444 "INSERT INTO t1 (id, name) VALUES "
2445 "(2, 'Ja'), (3, 'Ede'), "
2446 "(4, 'Haag'), (5, 'Kabul'), "
2447 "(6, 'Almere'), (7, 'Utrecht'), "
2448 "(8, 'Qandahar'), (9, 'Amsterdam'), "
2449 "(10, 'Amersfoort'), (11, 'Constantine')");
2450 check_mysql_rc(rc, mysql);
2451 rc= mysql_query(mysql,
2452 "CREATE TABLE t2 "
2453 "(id INTEGER NOT NULL PRIMARY KEY, "
2454 " name VARCHAR(20) NOT NULL)");
2455 check_mysql_rc(rc, mysql);
2456 rc= mysql_query(mysql,
2457 "INSERT INTO t2 (id, name) VALUES "
2458 "(4, 'Guam'), (5, 'Aruba'), "
2459 "(6, 'Angola'), (7, 'Albania'), "
2460 "(8, 'Anguilla'), (9, 'Argentina'), "
2461 "(10, 'Azerbaijan'), (11, 'Afghanistan'), "
2462 "(12, 'Burkina Faso'), (13, 'Faroe Islands')");
2463 check_mysql_rc(rc, mysql);
2464
2465 stmt= mysql_stmt_init(mysql);
2466 FAIL_IF(!stmt, mysql_error(mysql));
2467 rc= mysql_stmt_prepare(stmt, SL(query));
2468 check_stmt_rc(rc, stmt);
2469
2470 rc= mysql_stmt_execute(stmt);
2471 check_stmt_rc(rc, stmt);
2472 rc= 0;
2473 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
2474 rc++;
2475 FAIL_UNLESS(rc == 20, "rc != 20");
2476 mysql_stmt_close(stmt);
2477
2478 rc= mysql_query(mysql, "DROP TABLE t1, t2");
2479 check_mysql_rc(rc, mysql);
2480
2481 return OK;
2482}
2483
2484static int test_union2(MYSQL *mysql)
2485{
2486 MYSQL_STMT *stmt;
2487 int rc, i;
2488 const char *query= "select col1 FROM t1 where col1=1 union distinct "
2489 "select col1 FROM t1 where col1=2";
2490
2491
2492 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
2493 check_mysql_rc(rc, mysql);
2494
2495 rc= mysql_query(mysql, "CREATE TABLE t1(col1 INT, \
2496 col2 VARCHAR(40), \
2497 col3 SMALLINT, \
2498 col4 TIMESTAMP)");
2499 check_mysql_rc(rc, mysql);
2500
2501 stmt= mysql_stmt_init(mysql);
2502 FAIL_IF(!stmt, mysql_error(mysql));
2503 rc= mysql_stmt_prepare(stmt, SL(query));
2504 check_stmt_rc(rc, stmt);
2505
2506 for (i= 0; i < 3; i++)
2507 {
2508 rc= mysql_stmt_execute(stmt);
2509 check_stmt_rc(rc, stmt);
2510 rc= 0;
2511 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
2512 rc++;
2513 FAIL_UNLESS(rc == 0, "rowcount != 0");
2514 }
2515
2516 mysql_stmt_close(stmt);
2517
2518 rc= mysql_query(mysql, "DROP TABLE t1");
2519 check_mysql_rc(rc, mysql);
2520
2521 return OK;
2522}
2523
2524/* Misc tests to keep pure coverage happy */
2525
2526static int test_pure_coverage(MYSQL *mysql)
2527{
2528 MYSQL_STMT *stmt;
2529 MYSQL_BIND my_bind[1];
2530 int rc;
2531 ulong length;
2532
2533
2534 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_pure");
2535 check_mysql_rc(rc, mysql);
2536
2537 rc= mysql_query(mysql, "CREATE TABLE test_pure(c1 int, c2 varchar(20))");
2538 check_mysql_rc(rc, mysql);
2539
2540 stmt= mysql_stmt_init(mysql);
2541 FAIL_IF(!stmt, mysql_error(mysql));
2542 rc= mysql_stmt_prepare(stmt, SL("insert into test_pure(c67788) values(10)"));
2543 FAIL_IF(!rc, "Error expected");
2544 mysql_stmt_close(stmt);
2545
2546 /* Query without params and result should allow to bind 0 arrays */
2547 stmt= mysql_stmt_init(mysql);
2548 FAIL_IF(!stmt, mysql_error(mysql));
2549 rc= mysql_stmt_prepare(stmt, SL("insert into test_pure(c2) values(10)"));
2550 check_stmt_rc(rc, stmt);
2551
2552 rc= mysql_stmt_bind_param(stmt, (MYSQL_BIND*)0);
2553 check_stmt_rc(rc, stmt);
2554 rc= mysql_stmt_execute(stmt);
2555 check_stmt_rc(rc, stmt);
2556 rc= mysql_stmt_bind_result(stmt, (MYSQL_BIND*)0);
2557 FAIL_UNLESS(rc == 1, "");
2558
2559 mysql_stmt_close(stmt);
2560
2561 stmt= mysql_stmt_init(mysql);
2562 FAIL_IF(!stmt, mysql_error(mysql));
2563 rc= mysql_stmt_prepare(stmt, SL("insert into test_pure(c2) values(?)"));
2564 check_stmt_rc(rc, stmt);
2565
2566 /*
2567 We need to bzero bind structure because mysql_stmt_bind_param checks all
2568 its members.
2569 */
2570 memset(my_bind, '\0', sizeof(my_bind));
2571 my_bind[0].length= &length;
2572 my_bind[0].is_null= 0;
2573 my_bind[0].buffer_length= 0;
2574
2575 my_bind[0].buffer_type= MYSQL_TYPE_GEOMETRY;
2576 rc= mysql_stmt_bind_param(stmt, my_bind);
2577 FAIL_IF(!rc, "Error expected");
2578
2579 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
2580 rc= mysql_stmt_bind_param(stmt, my_bind);
2581 check_stmt_rc(rc, stmt);
2582 rc= mysql_stmt_store_result(stmt);
2583 check_stmt_rc(rc, stmt);
2584 mysql_stmt_close(stmt);
2585
2586 stmt= mysql_stmt_init(mysql);
2587 FAIL_IF(!stmt, mysql_error(mysql));
2588 rc= mysql_stmt_prepare(stmt, SL("select * from test_pure"));
2589 check_stmt_rc(rc, stmt);
2590 rc= mysql_stmt_execute(stmt);
2591 check_stmt_rc(rc, stmt);
2592 mysql_stmt_close(stmt);
2593
2594 mysql_query(mysql, "DROP TABLE test_pure");
2595 return OK;
2596}
2597
2598static int test_insert_select(MYSQL *mysql)
2599{
2600 MYSQL_STMT *stmt_insert, *stmt_select;
2601 char *query;
2602 int rc;
2603 uint i;
2604
2605 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2");
2606 check_mysql_rc(rc, mysql);
2607
2608 rc= mysql_query(mysql, "create table t1 (a int)");
2609 check_mysql_rc(rc, mysql);
2610
2611 rc= mysql_query(mysql, "create table t2 (a int)");
2612 check_mysql_rc(rc, mysql);
2613
2614 rc= mysql_query(mysql, "insert into t2 values (1)");
2615 check_mysql_rc(rc, mysql);
2616
2617 query= (char*)"insert into t1 select a from t2";
2618 stmt_insert= mysql_stmt_init(mysql);
2619 FAIL_IF(!stmt_insert, mysql_error(mysql));
2620 rc= mysql_stmt_prepare(stmt_insert, SL(query));
2621 check_stmt_rc(rc, stmt_insert);
2622
2623 query= (char*)"select * from t1";
2624 stmt_select= mysql_stmt_init(mysql);
2625 FAIL_IF(!stmt_select, mysql_error(mysql));
2626 rc= mysql_stmt_prepare(stmt_select, SL(query));
2627 check_stmt_rc(rc, stmt_select);
2628
2629 for(i= 0; i < 3; i++)
2630 {
2631 rc= mysql_stmt_execute(stmt_insert);
2632 check_stmt_rc(rc, stmt_insert);
2633
2634 rc= mysql_stmt_execute(stmt_select);
2635 check_stmt_rc(rc, stmt_select);
2636 rc= 0;
2637 while (mysql_stmt_fetch(stmt_select) != MYSQL_NO_DATA)
2638 rc++;
2639 FAIL_UNLESS(rc == (int)(i+1), "rc != i+1");
2640 }
2641
2642 mysql_stmt_close(stmt_insert);
2643 mysql_stmt_close(stmt_select);
2644 rc= mysql_query(mysql, "drop table t1, t2");
2645 check_mysql_rc(rc, mysql);
2646 return OK;
2647}
2648
2649/* Test simple prepare-insert */
2650
2651static int test_insert(MYSQL *mysql)
2652{
2653 MYSQL_STMT *stmt;
2654 int rc;
2655 char str_data[50];
2656 char tiny_data;
2657 MYSQL_RES *result;
2658 MYSQL_BIND my_bind[2];
2659 ulong length;
2660
2661
2662 rc= mysql_autocommit(mysql, TRUE);
2663 check_mysql_rc(rc, mysql);
2664
2665 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prep_insert");
2666 check_mysql_rc(rc, mysql);
2667
2668 rc= mysql_query(mysql, "CREATE TABLE test_prep_insert(col1 tinyint, \
2669 col2 varchar(50))");
2670 check_mysql_rc(rc, mysql);
2671
2672 /* insert by prepare */
2673 stmt= mysql_stmt_init(mysql);
2674 FAIL_IF(!stmt, mysql_error(mysql));
2675 rc= mysql_stmt_prepare(stmt, SL("INSERT INTO test_prep_insert VALUES(?, ?)"));
2676 check_stmt_rc(rc, stmt);
2677
2678 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "Param_count != 2");
2679
2680 /*
2681 We need to bzero bind structure because mysql_stmt_bind_param checks all
2682 its members.
2683 */
2684 memset(my_bind, '\0', sizeof(my_bind));
2685
2686 /* tinyint */
2687 my_bind[0].buffer_type= MYSQL_TYPE_TINY;
2688 my_bind[0].buffer= (void *)&tiny_data;
2689
2690 /* string */
2691 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
2692 my_bind[1].buffer= str_data;
2693 my_bind[1].buffer_length= sizeof(str_data);;
2694 my_bind[1].length= &length;
2695
2696 rc= mysql_stmt_bind_param(stmt, my_bind);
2697 check_stmt_rc(rc, stmt);
2698
2699 /* now, execute the prepared statement to insert 10 records.. */
2700 for (tiny_data= 0; tiny_data < 3; tiny_data++)
2701 {
2702 length= sprintf(str_data, "MySQL%d", tiny_data);
2703 rc= mysql_stmt_execute(stmt);
2704 check_stmt_rc(rc, stmt);
2705 }
2706
2707 mysql_stmt_close(stmt);
2708
2709 /* now fetch the results ..*/
2710 rc= mysql_commit(mysql);
2711 check_mysql_rc(rc, mysql);
2712
2713 /* test the results now, only one row should exist */
2714 rc= mysql_query(mysql, "SELECT * FROM test_prep_insert");
2715 check_mysql_rc(rc, mysql);
2716
2717 /* get the result */
2718 result= mysql_store_result(mysql);
2719 FAIL_IF(!result, "Invalid result set");
2720
2721 rc= 0;
2722 while (mysql_fetch_row(result))
2723 rc++;
2724 FAIL_UNLESS((int) tiny_data == rc, "rowcount != tinydata");
2725 mysql_free_result(result);
2726
2727 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_prep_insert");
2728 check_mysql_rc(rc, mysql);
2729
2730 return OK;
2731}
2732
2733static int test_join(MYSQL *mysql)
2734{
2735 MYSQL_STMT *stmt;
2736 int rc, i, j;
2737 const char *query[]= {"SELECT * FROM t2 join t1 on (t1.a=t2.a)",
2738 "SELECT * FROM t2 natural join t1",
2739 "SELECT * FROM t2 join t1 using(a)",
2740 "SELECT * FROM t2 left join t1 on(t1.a=t2.a)",
2741 "SELECT * FROM t2 natural left join t1",
2742 "SELECT * FROM t2 left join t1 using(a)",
2743 "SELECT * FROM t2 right join t1 on(t1.a=t2.a)",
2744 "SELECT * FROM t2 natural right join t1",
2745 "SELECT * FROM t2 right join t1 using(a)"};
2746
2747
2748 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2");
2749 check_mysql_rc(rc, mysql);
2750
2751 rc= mysql_query(mysql, "CREATE TABLE t1 (a int , b int);");
2752 check_mysql_rc(rc, mysql);
2753
2754 rc= mysql_query(mysql,
2755 "insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);");
2756 check_mysql_rc(rc, mysql);
2757
2758 rc= mysql_query(mysql, "CREATE TABLE t2 (a int , c int);");
2759 check_mysql_rc(rc, mysql);
2760
2761 rc= mysql_query(mysql,
2762 "insert into t2 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);");
2763 check_mysql_rc(rc, mysql);
2764
2765 for (j= 0; j < 9; j++)
2766 {
2767 stmt= mysql_stmt_init(mysql);
2768 FAIL_IF(!stmt, mysql_error(mysql));
2769 rc= mysql_stmt_prepare(stmt, SL(query[j]));
2770 check_stmt_rc(rc, stmt);
2771 for (i= 0; i < 3; i++)
2772 {
2773 rc= mysql_stmt_execute(stmt);
2774 check_stmt_rc(rc, stmt);
2775 rc= 0;
2776 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
2777 rc++;
2778 FAIL_UNLESS(rc == 5, "rowcount != 5");
2779 }
2780 mysql_stmt_close(stmt);
2781 }
2782
2783 rc= mysql_query(mysql, "DROP TABLE t1, t2");
2784 check_mysql_rc(rc, mysql);
2785 return OK;
2786}
2787
2788static int test_left_join_view(MYSQL *mysql)
2789{
2790 MYSQL_STMT *stmt;
2791 int rc, i;
2792 const char *query=
2793 "select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x);";
2794
2795
2796 rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1,v1");
2797 check_mysql_rc(rc, mysql);
2798
2799 rc = mysql_query(mysql, "DROP VIEW IF EXISTS v1,t1");
2800 check_mysql_rc(rc, mysql);
2801 rc= mysql_query(mysql,"CREATE TABLE t1 (a int)");
2802 check_mysql_rc(rc, mysql);
2803 rc= mysql_query(mysql,"insert into t1 values (1), (2), (3)");
2804 check_mysql_rc(rc, mysql);
2805 rc= mysql_query(mysql,"create view v1 (x) as select a from t1 where a > 1");
2806 check_mysql_rc(rc, mysql);
2807 stmt= mysql_stmt_init(mysql);
2808 rc= mysql_stmt_prepare(stmt, SL(query));
2809 check_stmt_rc(rc, stmt);
2810
2811 for (i= 0; i < 3; i++)
2812 {
2813 rc= mysql_stmt_execute(stmt);
2814 check_stmt_rc(rc, stmt);
2815 rc= 0;
2816 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
2817 rc++;
2818 FAIL_UNLESS(rc == 3, "rowcount != 3");
2819 }
2820 mysql_stmt_close(stmt);
2821
2822 rc= mysql_query(mysql, "DROP VIEW v1");
2823 check_mysql_rc(rc, mysql);
2824 rc= mysql_query(mysql, "DROP TABLE t1");
2825 check_mysql_rc(rc, mysql);
2826 return OK;
2827}
2828
2829/* Test simple sample - manual */
2830
2831static int test_manual_sample(MYSQL *mysql)
2832{
2833 unsigned int param_count;
2834 MYSQL_STMT *stmt;
2835 short small_data;
2836 int int_data;
2837 int rc;
2838 char str_data[50];
2839 ulonglong affected_rows;
2840 MYSQL_BIND my_bind[3];
2841 my_bool is_null;
2842 char query[MAX_TEST_QUERY_LENGTH];
2843
2844
2845 /*
2846 Sample which is incorporated directly in the manual under Prepared
2847 statements section (Example from mysql_stmt_execute()
2848 */
2849
2850 memset(str_data, 0, sizeof(str_data));
2851 mysql_autocommit(mysql, 1);
2852 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_table");
2853 check_mysql_rc(rc, mysql);
2854 rc= mysql_query(mysql, "CREATE TABLE test_table(col1 int, col2 varchar(50), \
2855 col3 smallint, \
2856 col4 timestamp)");
2857 check_mysql_rc(rc, mysql);
2858
2859 /* Prepare a insert query with 3 parameters */
2860 strcpy(query, "INSERT INTO test_table(col1, col2, col3) values(?, ?, ?)");
2861 stmt= mysql_stmt_init(mysql);
2862 FAIL_IF(!stmt, mysql_error(mysql));
2863 rc= mysql_stmt_prepare(stmt, SL(query));
2864 check_stmt_rc(rc, stmt);
2865
2866 /* Get the parameter count from the statement */
2867 param_count= mysql_stmt_param_count(stmt);
2868 FAIL_IF(param_count != 3, "param_count != 3");
2869
2870 memset(my_bind, '\0', sizeof(my_bind));
2871
2872 /* INTEGER PART */
2873 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
2874 my_bind[0].buffer= (void *)&int_data;
2875
2876 /* STRING PART */
2877 my_bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
2878 my_bind[1].buffer= (void *)str_data;
2879 my_bind[1].buffer_length= sizeof(str_data);
2880
2881 /* SMALLINT PART */
2882 my_bind[2].buffer_type= MYSQL_TYPE_SHORT;
2883 my_bind[2].buffer= (void *)&small_data;
2884 my_bind[2].is_null= &is_null;
2885 is_null= 0;
2886
2887 /* Bind the buffers */
2888 rc= mysql_stmt_bind_param(stmt, my_bind);
2889 check_stmt_rc(rc, stmt);
2890
2891 /* Specify the data */
2892 int_data= 10; /* integer */
2893 strcpy(str_data, "MySQL"); /* string */
2894
2895 /* INSERT SMALLINT data as NULL */
2896 is_null= 1;
2897
2898 /* Execute the insert statement - 1*/
2899 rc= mysql_stmt_execute(stmt);
2900 check_stmt_rc(rc, stmt);
2901
2902 /* Get the total rows affected */
2903 affected_rows= mysql_stmt_affected_rows(stmt);
2904 FAIL_IF(affected_rows != 1, "affected-rows != 1");
2905
2906 /* Re-execute the insert, by changing the values */
2907 int_data= 1000;
2908 strcpy(str_data, "The most popular open source database");
2909 small_data= 1000; /* smallint */
2910 is_null= 0; /* reset */
2911
2912 /* Execute the insert statement - 2*/
2913 rc= mysql_stmt_execute(stmt);
2914 check_stmt_rc(rc, stmt);
2915
2916 /* Get the total rows affected */
2917 affected_rows= mysql_stmt_affected_rows(stmt);
2918
2919 FAIL_IF(affected_rows != 1, "affected_rows != 1");
2920
2921 /* Close the statement */
2922 rc= mysql_stmt_close(stmt);
2923 check_stmt_rc(rc, stmt);
2924
2925 /* DROP THE TABLE */
2926 rc= mysql_query(mysql, "DROP TABLE test_table");
2927 check_mysql_rc(rc, mysql);
2928 return OK;
2929}
2930
2931static int test_create_drop(MYSQL *mysql)
2932{
2933 MYSQL_STMT *stmt_create, *stmt_drop, *stmt_select, *stmt_create_select;
2934 char *query;
2935 int rc, i;
2936
2937 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2");
2938 check_mysql_rc(rc, mysql);
2939
2940 rc= mysql_query(mysql, "create table t2 (a int);");
2941 check_mysql_rc(rc, mysql);
2942
2943 rc= mysql_query(mysql, "create table t1 (a int);");
2944 check_mysql_rc(rc, mysql);
2945
2946 rc= mysql_query(mysql, "insert into t2 values (3), (2), (1);");
2947 check_mysql_rc(rc, mysql);
2948
2949 query= (char*)"create table t1 (a int)";
2950 stmt_create= mysql_stmt_init(mysql);
2951 FAIL_IF(!stmt_create, mysql_error(mysql));
2952 rc= mysql_stmt_prepare(stmt_create, SL(query));
2953 check_stmt_rc(rc, stmt_create);
2954
2955 query= (char*)"drop table t1";
2956 stmt_drop= mysql_stmt_init(mysql);
2957 FAIL_IF(!stmt_drop, mysql_error(mysql));
2958 rc= mysql_stmt_prepare(stmt_drop, SL(query));
2959 check_stmt_rc(rc, stmt_drop);
2960
2961 query= (char*)"select a in (select a from t2) from t1";
2962 stmt_select= mysql_stmt_init(mysql);
2963 FAIL_IF(!stmt_select, mysql_error(mysql));
2964 rc= mysql_stmt_prepare(stmt_select, SL(query));
2965 check_stmt_rc(rc, stmt_select);
2966
2967 rc= mysql_query(mysql, "DROP TABLE t1");
2968 check_mysql_rc(rc, mysql);
2969
2970 query= (char*)"create table t1 select a from t2";
2971 stmt_create_select= mysql_stmt_init(mysql);
2972 FAIL_IF(!stmt_create_select, mysql_error(mysql));
2973 rc= mysql_stmt_prepare(stmt_create_select, SL(query));
2974 check_stmt_rc(rc, stmt_create_select);
2975
2976 for (i= 0; i < 3; i++)
2977 {
2978 rc= mysql_stmt_execute(stmt_create);
2979 check_stmt_rc(rc, stmt_create);
2980
2981 rc= mysql_stmt_execute(stmt_select);
2982 check_stmt_rc(rc, stmt_select);
2983
2984 rc= 0;
2985 while (mysql_stmt_fetch(stmt_select) != MYSQL_NO_DATA)
2986 rc++;
2987 FAIL_UNLESS(rc == 0, "rowcount != 0");
2988
2989 rc= mysql_stmt_execute(stmt_drop);
2990 check_stmt_rc(rc, stmt_drop);
2991
2992 rc= mysql_stmt_execute(stmt_create_select);
2993 check_stmt_rc(rc, stmt_create);
2994
2995 rc= mysql_stmt_execute(stmt_select);
2996 check_stmt_rc(rc, stmt_select);
2997 rc= 0;
2998 while (mysql_stmt_fetch(stmt_select) != MYSQL_NO_DATA)
2999 rc++;
3000 FAIL_UNLESS(rc == 3, "rowcount != 3");
3001
3002 rc= mysql_stmt_execute(stmt_drop);
3003 check_stmt_rc(rc, stmt_drop);
3004 }
3005
3006 mysql_stmt_close(stmt_create);
3007 mysql_stmt_close(stmt_drop);
3008 mysql_stmt_close(stmt_select);
3009 mysql_stmt_close(stmt_create_select);
3010
3011 rc= mysql_query(mysql, "DROP TABLE t2");
3012 check_mysql_rc(rc, mysql);
3013 return OK;
3014}
3015
3016/* Test DATE, TIME, DATETIME and TS with MYSQL_TIME conversion */
3017
3018static int test_date(MYSQL *mysql)
3019{
3020 int rc;
3021
3022 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3023 check_mysql_rc(rc, mysql);
3024
3025 rc= mysql_query(mysql, "CREATE TABLE test_date(c1 TIMESTAMP, \
3026 c2 TIME, \
3027 c3 DATETIME, \
3028 c4 DATE)");
3029
3030 check_mysql_rc(rc, mysql);
3031
3032 rc= test_bind_date_conv(mysql, 5);
3033 mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3034 return rc;
3035}
3036
3037
3038/* Test all time types to DATE and DATE to all types */
3039
3040static int test_date_date(MYSQL *mysql)
3041{
3042 int rc;
3043
3044
3045 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3046 check_mysql_rc(rc, mysql);
3047
3048 rc= mysql_query(mysql, "CREATE TABLE test_date(c1 DATE, \
3049 c2 DATE, \
3050 c3 DATE, \
3051 c4 DATE)");
3052
3053 check_mysql_rc(rc, mysql);
3054
3055 rc= test_bind_date_conv(mysql, 3);
3056 mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3057 return rc;
3058}
3059
3060/* Test all time types to TIMESTAMP and TIMESTAMP to all types */
3061
3062static int test_date_ts(MYSQL *mysql)
3063{
3064 int rc;
3065
3066
3067 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3068 check_mysql_rc(rc, mysql);
3069
3070 rc= mysql_query(mysql, "CREATE TABLE test_date(c1 TIMESTAMP, \
3071 c2 TIMESTAMP, \
3072 c3 TIMESTAMP, \
3073 c4 TIMESTAMP)");
3074
3075 check_mysql_rc(rc, mysql);
3076
3077 rc= test_bind_date_conv(mysql, 2);
3078 mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3079 return rc;
3080}
3081
3082
3083/* Test all time types to DATETIME and DATETIME to all types */
3084
3085static int test_date_dt(MYSQL *mysql)
3086{
3087 int rc;
3088
3089
3090 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3091 check_mysql_rc(rc, mysql);
3092
3093 rc= mysql_query(mysql, "CREATE TABLE test_date(c1 datetime, "
3094 " c2 datetime, c3 datetime, c4 date)");
3095 check_mysql_rc(rc, mysql);
3096
3097 rc= test_bind_date_conv(mysql, 2);
3098 mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3099 return rc;
3100}
3101
3102/* Test all time types to TIME and TIME to all types */
3103
3104static int test_date_time(MYSQL *mysql)
3105{
3106 int rc;
3107
3108
3109 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3110 check_mysql_rc(rc, mysql);
3111
3112 rc= mysql_query(mysql, "CREATE TABLE test_date(c1 TIME, \
3113 c2 TIME, \
3114 c3 TIME, \
3115 c4 TIME)");
3116
3117 check_mysql_rc(rc, mysql);
3118
3119 rc= test_bind_date_conv(mysql, 3);
3120 mysql_query(mysql, "DROP TABLE IF EXISTS test_date");
3121 return rc;
3122}
3123
3124/*
3125 Test of basic checks that are performed in server for components
3126 of MYSQL_TIME parameters.
3127*/
3128
3129static int test_datetime_ranges(MYSQL *mysql)
3130{
3131 const char *stmt_text;
3132 int rc, i;
3133 MYSQL_STMT *stmt;
3134 MYSQL_BIND my_bind[6];
3135 MYSQL_TIME tm[6];
3136
3137 if (!is_mariadb)
3138 return SKIP;
3139
3140 stmt_text= "drop table if exists t1";
3141 rc= mysql_real_query(mysql, SL(stmt_text));
3142 check_mysql_rc(rc, mysql);
3143
3144 stmt_text= "create table t1 (year datetime, month datetime, day datetime, "
3145 "hour datetime, min datetime, sec datetime)";
3146 rc= mysql_real_query(mysql, SL(stmt_text));
3147 check_mysql_rc(rc, mysql);
3148
3149 stmt= mysql_stmt_init(mysql);
3150 FAIL_IF(!stmt, mysql_error(mysql));
3151 stmt_text= "INSERT INTO t1 VALUES (?, ?, ?, ?, ?, ?)";
3152 rc= mysql_stmt_prepare(stmt, SL(stmt_text));
3153 check_stmt_rc(rc, stmt);
3154 FAIL_IF(mysql_stmt_param_count(stmt) != 6, "param_count != 6");
3155
3156 memset(my_bind, '\0', sizeof(my_bind));
3157 for (i= 0; i < 6; i++)
3158 {
3159 my_bind[i].buffer_type= MYSQL_TYPE_DATETIME;
3160 my_bind[i].buffer= &tm[i];
3161 }
3162 rc= mysql_stmt_bind_param(stmt, my_bind);
3163 check_stmt_rc(rc, stmt);
3164
3165 tm[0].year= 2004; tm[0].month= 11; tm[0].day= 10;
3166 tm[0].hour= 12; tm[0].minute= 30; tm[0].second= 30;
3167 tm[0].second_part= 0; tm[0].neg= 0;
3168
3169 tm[5]= tm[4]= tm[3]= tm[2]= tm[1]= tm[0];
3170 tm[0].year= 10000; tm[1].month= 13; tm[2].day= 32;
3171 tm[3].hour= 24; tm[4].minute= 60; tm[5].second= 60;
3172
3173 rc= mysql_stmt_execute(stmt);
3174 check_stmt_rc(rc, stmt);
3175
3176 FAIL_IF(!mysql_warning_count(mysql), "warnings expected");
3177
3178 if (verify_col_data(mysql, "t1", "year", "0000-00-00 00:00:00"))
3179 goto error;
3180 if (verify_col_data(mysql, "t1", "month", "0000-00-00 00:00:00"))
3181 goto error;
3182 if (verify_col_data(mysql, "t1", "day", "0000-00-00 00:00:00"))
3183 goto error;
3184 if (verify_col_data(mysql, "t1", "hour", "0000-00-00 00:00:00"))
3185 goto error;
3186 if (verify_col_data(mysql, "t1", "min", "0000-00-00 00:00:00"))
3187 goto error;
3188 if (verify_col_data(mysql, "t1", "sec", "0000-00-00 00:00:00"))
3189 goto error;
3190
3191 mysql_stmt_close(stmt);
3192
3193 stmt_text= "delete from t1";
3194 rc= mysql_real_query(mysql, SL(stmt_text));
3195 check_mysql_rc(rc, mysql);
3196
3197 stmt_text= "INSERT INTO t1 (year, month, day) VALUES (?, ?, ?)";
3198 stmt= mysql_stmt_init(mysql);
3199 FAIL_IF(!stmt, mysql_error(mysql));
3200 rc= mysql_stmt_prepare(stmt, SL(stmt_text));
3201 check_stmt_rc(rc, stmt);
3202
3203 /*
3204 We reuse contents of bind and tm arrays left from previous part of test.
3205 */
3206 for (i= 0; i < 3; i++)
3207 my_bind[i].buffer_type= MYSQL_TYPE_DATE;
3208
3209 rc= mysql_stmt_bind_param(stmt, my_bind);
3210 check_stmt_rc(rc, stmt);
3211
3212 rc= mysql_stmt_execute(stmt);
3213 check_stmt_rc(rc, stmt);
3214 FAIL_IF(!mysql_warning_count(mysql), "warnings expected");
3215
3216 if (verify_col_data(mysql, "t1", "year", "0000-00-00 00:00:00"))
3217 goto error;
3218 if (verify_col_data(mysql, "t1", "month", "0000-00-00 00:00:00"))
3219 goto error;
3220 if (verify_col_data(mysql, "t1", "day", "0000-00-00 00:00:00"))
3221 goto error;
3222
3223 mysql_stmt_close(stmt);
3224
3225 stmt_text= "drop table t1";
3226 rc= mysql_real_query(mysql, SL(stmt_text));
3227 check_mysql_rc(rc, mysql);
3228
3229 stmt_text= "create table t1 (day_ovfl time, day time, hour time, min time, sec time)";
3230 rc= mysql_real_query(mysql, SL(stmt_text));
3231 check_mysql_rc(rc, mysql);
3232
3233 stmt= mysql_stmt_init(mysql);
3234 FAIL_IF(!stmt, mysql_error(mysql));
3235 stmt_text= "INSERT INTO t1 VALUES (?,?,?,?,?)";
3236 rc= mysql_stmt_prepare(stmt, SL(stmt_text));
3237 check_stmt_rc(rc, stmt);
3238 FAIL_IF(mysql_stmt_param_count(stmt) != 5, "param_count != 5");
3239
3240 /*
3241 Again we reuse what we can from previous part of test.
3242 */
3243 for (i= 0; i < 5; i++)
3244 my_bind[i].buffer_type= MYSQL_TYPE_TIME;
3245
3246 rc= mysql_stmt_bind_param(stmt, my_bind);
3247 check_stmt_rc(rc, stmt);
3248
3249 tm[0].year= 0; tm[0].month= 0; tm[0].day= 10;
3250 tm[0].hour= 12; tm[0].minute= 30; tm[0].second= 30;
3251 tm[0].second_part= 0; tm[0].neg= 0;
3252
3253 tm[4]= tm[3]= tm[2]= tm[1]= tm[0];
3254 tm[0].day= 35; tm[1].day= 34; tm[2].hour= 30; tm[3].minute= 60; tm[4].second= 60;
3255
3256 rc= mysql_stmt_execute(stmt);
3257 check_stmt_rc(rc, stmt);
3258 FAIL_IF(mysql_warning_count(mysql) != 2, "warning_count != 2");
3259
3260 if (verify_col_data(mysql, "t1", "day_ovfl", "838:59:59"))
3261 goto error;
3262 if (verify_col_data(mysql, "t1", "day", "828:30:30"))
3263 goto error;
3264 if (verify_col_data(mysql, "t1", "hour", "270:30:30"))
3265 goto error;
3266 if (verify_col_data(mysql, "t1", "min", "00:00:00"))
3267 goto error;
3268 if (verify_col_data(mysql, "t1", "sec", "00:00:00"))
3269 goto error;
3270
3271 mysql_stmt_close(stmt);
3272 stmt_text= "drop table t1";
3273 rc= mysql_real_query(mysql, SL(stmt_text));
3274 check_mysql_rc(rc, mysql);
3275 return OK;
3276error:
3277 mysql_stmt_close(stmt);
3278 stmt_text= "drop table t1";
3279 rc= mysql_real_query(mysql, SL(stmt_text));
3280 check_mysql_rc(rc, mysql);
3281 return OK;
3282}
3283
3284static int test_derived(MYSQL *mysql)
3285{
3286 MYSQL_STMT *stmt;
3287 int rc, i;
3288 MYSQL_BIND my_bind[1];
3289 int32 my_val= 0;
3290 ulong my_length= 0L;
3291 my_bool my_null= FALSE;
3292 const char *query=
3293 "select count(1) from (select f.id from t1 f where f.id=?) as x";
3294
3295
3296 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
3297 check_mysql_rc(rc, mysql);
3298
3299 rc= mysql_query(mysql, "create table t1 (id int(8), primary key (id)) \
3300ENGINE=InnoDB DEFAULT CHARSET=utf8");
3301 check_mysql_rc(rc, mysql);
3302
3303 rc= mysql_query(mysql, "insert into t1 values (1)");
3304 check_mysql_rc(rc, mysql);
3305
3306 stmt= mysql_stmt_init(mysql);
3307 FAIL_IF(!stmt, mysql_error(mysql));
3308 rc= mysql_stmt_prepare(stmt, SL(query));
3309 check_stmt_rc(rc, stmt);
3310
3311 memset(my_bind, '\0', sizeof(my_bind));
3312
3313 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
3314 my_bind[0].buffer= (void *)&my_val;
3315 my_bind[0].length= &my_length;
3316 my_bind[0].is_null= (char*)&my_null;
3317 my_val= 1;
3318 rc= mysql_stmt_bind_param(stmt, my_bind);
3319 check_stmt_rc(rc, stmt);
3320
3321 for (i= 0; i < 3; i++)
3322 {
3323 rc= mysql_stmt_execute(stmt);
3324 check_stmt_rc(rc, stmt);
3325 rc= 0;
3326 while (!mysql_stmt_fetch(stmt))
3327 rc++;
3328 FAIL_UNLESS(rc == 1, "rowcount != 1");
3329 }
3330 mysql_stmt_close(stmt);
3331
3332 rc= mysql_query(mysql, "DROP TABLE t1");
3333 check_mysql_rc(rc, mysql);
3334 return OK;
3335}
3336
3337static int test_distinct(MYSQL *mysql)
3338{
3339 MYSQL_STMT *stmt;
3340 int rc, i;
3341 const char *query=
3342 "SELECT 2+count(distinct b), group_concat(a) FROM t1 group by a";
3343
3344
3345 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
3346 check_mysql_rc(rc, mysql);
3347
3348 rc= mysql_query(mysql, "CREATE TABLE t1 (a int , b int);");
3349 check_mysql_rc(rc, mysql);
3350
3351 rc= mysql_query(mysql,
3352 "insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), \
3353(1, 10), (2, 20), (3, 30), (4, 40), (5, 50);");
3354 check_mysql_rc(rc, mysql);
3355
3356 for (i= 0; i < 3; i++)
3357 {
3358 stmt= mysql_stmt_init(mysql);
3359 FAIL_IF(!stmt, mysql_error(mysql));
3360 rc= mysql_stmt_prepare(stmt, SL(query));
3361 check_stmt_rc(rc, stmt);
3362 rc= mysql_stmt_execute(stmt);
3363 check_stmt_rc(rc, stmt);
3364
3365 rc= 0;
3366 while (!mysql_stmt_fetch(stmt))
3367 rc++;
3368 FAIL_UNLESS(rc == 5, "rowcount != 5");
3369 mysql_stmt_close(stmt);
3370 }
3371
3372 rc= mysql_query(mysql, "DROP TABLE t1");
3373 check_mysql_rc(rc, mysql);
3374 return OK;
3375}
3376
3377static int test_do_set(MYSQL *mysql)
3378{
3379 MYSQL_STMT *stmt_do, *stmt_set;
3380 char *query;
3381 int rc, i;
3382
3383 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
3384 check_mysql_rc(rc, mysql);
3385
3386 rc= mysql_query(mysql, "create table t1 (a int)");
3387 check_mysql_rc(rc, mysql);
3388
3389 query= (char*)"do @var:=(1 in (select * from t1))";
3390 stmt_do= mysql_stmt_init(mysql);
3391 FAIL_IF(!stmt_do, mysql_error(mysql));
3392 rc= mysql_stmt_prepare(stmt_do, SL(query));
3393 check_stmt_rc(rc, stmt_do);
3394
3395 query= (char*)"set @var=(1 in (select * from t1))";
3396 stmt_set= mysql_stmt_init(mysql);
3397 FAIL_IF(!stmt_set, mysql_error(mysql));
3398 rc= mysql_stmt_prepare(stmt_set, SL(query));
3399 check_stmt_rc(rc, stmt_set);
3400
3401 for (i= 0; i < 3; i++)
3402 {
3403 rc= mysql_stmt_execute(stmt_do);
3404 check_stmt_rc(rc, stmt_do);
3405 rc= mysql_stmt_execute(stmt_set);
3406 check_stmt_rc(rc, stmt_set);
3407 }
3408
3409 mysql_stmt_close(stmt_do);
3410 mysql_stmt_close(stmt_set);
3411 return OK;
3412}
3413
3414static int test_double_compare(MYSQL *mysql)
3415{
3416 MYSQL_STMT *stmt;
3417 int rc;
3418 char real_data[10], tiny_data;
3419 double double_data;
3420 MYSQL_RES *result;
3421 MYSQL_BIND my_bind[3];
3422 ulong length[3];
3423 char query[MAX_TEST_QUERY_LENGTH];
3424
3425
3426 rc= mysql_autocommit(mysql, TRUE);
3427 check_mysql_rc(rc, mysql);
3428
3429 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_double_compare");
3430 check_mysql_rc(rc, mysql);
3431
3432 rc= mysql_query(mysql, "CREATE TABLE test_double_compare(col1 tinyint, "
3433 " col2 float, col3 double )");
3434 check_mysql_rc(rc, mysql);
3435
3436 rc= mysql_query(mysql, "INSERT INTO test_double_compare "
3437 "VALUES (1, 10.2, 34.5)");
3438 check_mysql_rc(rc, mysql);
3439
3440 strcpy(query, "UPDATE test_double_compare SET col1=100 "
3441 "WHERE col1 = ? AND col2 = ? AND COL3 = ?");
3442 stmt= mysql_stmt_init(mysql);
3443 FAIL_IF(!stmt, mysql_error(mysql));
3444 rc= mysql_stmt_prepare(stmt, SL(query));
3445 check_stmt_rc(rc, stmt);
3446
3447 FAIL_IF(mysql_stmt_param_count(stmt) != 3, "param_count != 3");
3448
3449 memset(my_bind, '\0', sizeof(my_bind));
3450
3451 /* tinyint */
3452 my_bind[0].buffer_type= MYSQL_TYPE_TINY;
3453 my_bind[0].buffer= (void *)&tiny_data;
3454
3455 /* string->float */
3456 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
3457 my_bind[1].buffer= (void *)&real_data;
3458 my_bind[1].buffer_length= sizeof(real_data);
3459 my_bind[1].length= &length[1];
3460
3461 /* double */
3462 my_bind[2].buffer_type= MYSQL_TYPE_DOUBLE;
3463 my_bind[2].buffer= (void *)&double_data;
3464
3465 tiny_data= 1;
3466 strcpy(real_data, "10.2");
3467 length[1]= (ulong)strlen(real_data);
3468 double_data= 34.5;
3469 rc= mysql_stmt_bind_param(stmt, my_bind);
3470 check_stmt_rc(rc, stmt);
3471
3472 rc= mysql_stmt_execute(stmt);
3473 check_stmt_rc(rc, stmt);
3474
3475 FAIL_IF(mysql_stmt_affected_rows(stmt), "affected_rows != 0");
3476
3477 mysql_stmt_close(stmt);
3478
3479 /* now fetch the results ..*/
3480 rc= mysql_commit(mysql);
3481 check_mysql_rc(rc, mysql);
3482
3483 /* test the results now, only one row should exist */
3484 rc= mysql_query(mysql, "SELECT * FROM test_double_compare");
3485 check_mysql_rc(rc, mysql);
3486
3487 /* get the result */
3488 result= mysql_store_result(mysql);
3489 FAIL_IF(!result, "Invalid result set");
3490
3491 rc= 0;
3492 while (mysql_fetch_row(result))
3493 rc++;
3494 FAIL_UNLESS((int)tiny_data == rc, "rowcount != tinydata");
3495 mysql_free_result(result);
3496 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_double_compare");
3497 check_mysql_rc(rc, mysql);
3498 return OK;
3499}
3500
3501static int test_multi(MYSQL *mysql)
3502{
3503 MYSQL_STMT *stmt_delete, *stmt_update, *stmt_select1, *stmt_select2;
3504 char *query;
3505 MYSQL_BIND my_bind[1];
3506 int rc, i;
3507 int32 param= 1;
3508 ulong length= 1;
3509
3510 /*
3511 We need to bzero bind structure because mysql_stmt_bind_param checks all
3512 its members.
3513 */
3514 memset(my_bind, '\0', sizeof(my_bind));
3515
3516 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
3517 my_bind[0].buffer= (void *)&param;
3518 my_bind[0].length= &length;
3519
3520 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2");
3521 check_mysql_rc(rc, mysql);
3522
3523 rc= mysql_query(mysql, "create table t1 (a int, b int)");
3524 check_mysql_rc(rc, mysql);
3525
3526 rc= mysql_query(mysql, "create table t2 (a int, b int)");
3527 check_mysql_rc(rc, mysql);
3528
3529 rc= mysql_query(mysql, "insert into t1 values (3, 3), (2, 2), (1, 1)");
3530 check_mysql_rc(rc, mysql);
3531
3532 rc= mysql_query(mysql, "insert into t2 values (3, 3), (2, 2), (1, 1)");
3533 check_mysql_rc(rc, mysql);
3534
3535 query= (char*)"delete t1, t2 from t1, t2 where t1.a=t2.a and t1.b=10";
3536 stmt_delete= mysql_stmt_init(mysql);
3537 FAIL_IF(!stmt_delete, mysql_error(mysql));
3538 rc= mysql_stmt_prepare(stmt_delete, SL(query));
3539 check_stmt_rc(rc, stmt_delete);
3540
3541 query= (char*)"update t1, t2 set t1.b=10, t2.b=10 where t1.a=t2.a and t1.b=?";
3542 stmt_update= mysql_stmt_init(mysql);
3543 FAIL_IF(!stmt_update, mysql_error(mysql));
3544 rc= mysql_stmt_prepare(stmt_update, SL(query));
3545 check_stmt_rc(rc, stmt_update);
3546
3547 query= (char*)"select * from t1";
3548 stmt_select1= mysql_stmt_init(mysql);
3549 FAIL_IF(!stmt_select1, mysql_error(mysql));
3550 rc= mysql_stmt_prepare(stmt_select1, SL(query));
3551 check_stmt_rc(rc, stmt_select1);
3552
3553 query= (char*)"select * from t2";
3554 stmt_select2= mysql_stmt_init(mysql);
3555 FAIL_IF(!stmt_select2, mysql_error(mysql));
3556 rc= mysql_stmt_prepare(stmt_select2, SL(query));
3557 check_stmt_rc(rc, stmt_select2);
3558
3559 for(i= 0; i < 3; i++)
3560 {
3561 rc= mysql_stmt_bind_param(stmt_update, my_bind);
3562 check_stmt_rc(rc, stmt_update);
3563
3564 rc= mysql_stmt_execute(stmt_update);
3565 check_stmt_rc(rc, stmt_update);
3566
3567 rc= mysql_stmt_execute(stmt_delete);
3568 check_stmt_rc(rc, stmt_delete);
3569
3570 rc= mysql_stmt_execute(stmt_select1);
3571 check_stmt_rc(rc, stmt_select1);
3572 rc= 0;
3573 while (!mysql_stmt_fetch(stmt_select1))
3574 rc++;
3575 FAIL_UNLESS(rc == 3-param, "rc != 3 - param");
3576
3577 rc= mysql_stmt_execute(stmt_select2);
3578 check_stmt_rc(rc, stmt_select2);
3579 rc= 0;
3580 while (!mysql_stmt_fetch(stmt_select2))
3581 rc++;
3582 FAIL_UNLESS(rc == 3-param, "rc != 3 - param");
3583
3584 param++;
3585 }
3586
3587 mysql_stmt_close(stmt_delete);
3588 mysql_stmt_close(stmt_update);
3589 mysql_stmt_close(stmt_select1);
3590 mysql_stmt_close(stmt_select2);
3591 rc= mysql_query(mysql, "drop table t1, t2");
3592 check_mysql_rc(rc, mysql);
3593
3594 return OK;
3595}
3596
3597/* Multiple stmts .. */
3598
3599static int test_multi_stmt(MYSQL *mysql)
3600{
3601
3602 MYSQL_STMT *stmt, *stmt1, *stmt2;
3603 int rc;
3604 uint32 id;
3605 char name[50];
3606 MYSQL_BIND my_bind[2];
3607 ulong length[2];
3608 my_bool is_null[2];
3609 const char *query;
3610
3611 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_multi_table");
3612 check_mysql_rc(rc, mysql);
3613
3614 rc= mysql_query(mysql, "CREATE TABLE test_multi_table(id int, name char(20))");
3615 check_mysql_rc(rc, mysql);
3616
3617 rc= mysql_query(mysql, "INSERT INTO test_multi_table values(10, 'mysql')");
3618 check_mysql_rc(rc, mysql);
3619
3620 stmt= mysql_stmt_init(mysql);
3621 FAIL_IF(!stmt, mysql_error(mysql));
3622 query= "SELECT * FROM test_multi_table WHERE id=?";
3623 rc= mysql_stmt_prepare(stmt, SL(query));
3624 check_stmt_rc(rc, stmt);
3625
3626 stmt2= mysql_stmt_init(mysql);
3627 FAIL_IF(!stmt2, mysql_error(mysql));
3628 query= "UPDATE test_multi_table SET name='updated' WHERE id=10";
3629 rc= mysql_stmt_prepare(stmt2, SL(query));
3630 check_stmt_rc(rc, stmt2);
3631
3632 FAIL_IF(mysql_stmt_param_count(stmt) != 1, "param_count != 1");
3633
3634 memset(my_bind, '\0', sizeof(my_bind));
3635
3636 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
3637 my_bind[0].buffer= (void *)&id;
3638 my_bind[0].is_null= &is_null[0];
3639 my_bind[0].length= &length[0];
3640 is_null[0]= 0;
3641 length[0]= 0;
3642
3643 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
3644 my_bind[1].buffer= (void *)name;
3645 my_bind[1].buffer_length= sizeof(name);
3646 my_bind[1].length= &length[1];
3647 my_bind[1].is_null= &is_null[1];
3648
3649 rc= mysql_stmt_bind_param(stmt, my_bind);
3650 check_stmt_rc(rc, stmt);
3651
3652 rc= mysql_stmt_bind_result(stmt, my_bind);
3653 check_stmt_rc(rc, stmt);
3654
3655 id= 10;
3656 rc= mysql_stmt_execute(stmt);
3657 check_stmt_rc(rc, stmt);
3658
3659 id= 999;
3660 rc= mysql_stmt_fetch(stmt);
3661 check_stmt_rc(rc, stmt);
3662
3663 FAIL_UNLESS(id == 10, "id != 10");
3664 FAIL_UNLESS(strcmp(name, "mysql") == 0, "name != 'mysql'");
3665
3666 rc= mysql_stmt_fetch(stmt);
3667 FAIL_UNLESS(rc == MYSQL_NO_DATA, "");
3668
3669 /* alter the table schema now */
3670 stmt1= mysql_stmt_init(mysql);
3671 FAIL_IF(!stmt1, mysql_error(mysql));
3672 query= "DELETE FROM test_multi_table WHERE id=? AND CONVERT(name USING utf8)=?";
3673 rc= mysql_stmt_prepare(stmt1, SL(query));
3674 check_stmt_rc(rc, stmt1);
3675
3676 FAIL_IF(mysql_stmt_param_count(stmt1) != 2, "param_count != 2");
3677
3678 rc= mysql_stmt_bind_param(stmt1, my_bind);
3679 check_stmt_rc(rc, stmt1);
3680
3681 rc= mysql_stmt_execute(stmt2);
3682 check_stmt_rc(rc, stmt2);
3683
3684 FAIL_IF(mysql_stmt_affected_rows(stmt2) != 1, "affected_rows != 1");
3685
3686 rc= mysql_stmt_execute(stmt);
3687 check_stmt_rc(rc, stmt);
3688
3689 rc= mysql_stmt_fetch(stmt);
3690 check_stmt_rc(rc, stmt);
3691
3692 FAIL_UNLESS(id == 10, "id != 10");
3693 FAIL_UNLESS(strcmp(name, "updated") == 0, "name != 'updated'");
3694
3695 rc= mysql_stmt_fetch(stmt);
3696 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
3697
3698 rc= mysql_stmt_execute(stmt1);
3699 check_stmt_rc(rc, stmt1);
3700
3701 FAIL_IF(mysql_stmt_affected_rows(stmt1) != 1, "affected_rows != 1");
3702
3703 mysql_stmt_close(stmt1);
3704
3705 rc= mysql_stmt_execute(stmt);
3706 check_stmt_rc(rc, stmt);
3707
3708 rc= mysql_stmt_fetch(stmt);
3709 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
3710
3711 rc= my_stmt_result(mysql, "SELECT * FROM test_multi_table");
3712 FAIL_UNLESS(rc == 0, "rc != 0");
3713
3714 mysql_stmt_close(stmt);
3715 mysql_stmt_close(stmt2);
3716 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_multi_table");
3717 check_mysql_rc(rc, mysql);
3718
3719 return OK;
3720}
3721
3722/* Test 'n' statements create and close */
3723
3724static int test_nstmts(MYSQL *mysql)
3725{
3726 MYSQL_STMT *stmt;
3727 char query[255];
3728 int rc;
3729 static uint i, total_stmts= 2000;
3730 MYSQL_BIND my_bind[1];
3731
3732 mysql_autocommit(mysql, TRUE);
3733
3734 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_nstmts");
3735 check_mysql_rc(rc, mysql);
3736
3737 rc= mysql_query(mysql, "CREATE TABLE test_nstmts(id int)");
3738 check_mysql_rc(rc, mysql);
3739
3740 memset(my_bind, '\0', sizeof(my_bind));
3741
3742 my_bind[0].buffer= (void *)&i;
3743 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
3744
3745 for (i= 0; i < total_stmts; i++)
3746 {
3747 strcpy(query, "insert into test_nstmts values(?)");
3748 stmt= mysql_stmt_init(mysql);
3749 FAIL_IF(!stmt, mysql_error(mysql));
3750 rc= mysql_stmt_prepare(stmt, SL(query));
3751 check_stmt_rc(rc, stmt);
3752
3753 rc= mysql_stmt_bind_param(stmt, my_bind);
3754 check_stmt_rc(rc, stmt);
3755
3756 rc= mysql_stmt_execute(stmt);
3757 check_stmt_rc(rc, stmt);
3758
3759 mysql_stmt_close(stmt);
3760 }
3761
3762 stmt= mysql_stmt_init(mysql);
3763 FAIL_IF(!stmt, mysql_error(mysql));
3764 rc= mysql_stmt_prepare(stmt, SL(" select count(*) from test_nstmts"));
3765 check_stmt_rc(rc, stmt);
3766
3767 rc= mysql_stmt_execute(stmt);
3768 check_stmt_rc(rc, stmt);
3769
3770 i= 0;
3771 rc= mysql_stmt_bind_result(stmt, my_bind);
3772 check_stmt_rc(rc, stmt);
3773
3774 rc= mysql_stmt_fetch(stmt);
3775 check_stmt_rc(rc, stmt);
3776 FAIL_UNLESS( i == total_stmts, "total_stmts != i");
3777
3778 rc= mysql_stmt_fetch(stmt);
3779 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
3780
3781 mysql_stmt_close(stmt);
3782
3783 rc= mysql_query(mysql, "DROP TABLE test_nstmts");
3784 check_mysql_rc(rc, mysql);
3785 return OK;
3786}
3787
3788/* Test simple null */
3789
3790static int test_null(MYSQL *mysql)
3791{
3792 MYSQL_STMT *stmt;
3793 int rc;
3794 uint nData;
3795 MYSQL_BIND my_bind[2];
3796 my_bool is_null[2];
3797 char query[MAX_TEST_QUERY_LENGTH];
3798
3799
3800 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_null");
3801 check_mysql_rc(rc, mysql);
3802
3803 rc= mysql_query(mysql, "CREATE TABLE test_null(col1 int, col2 varchar(50))");
3804 check_mysql_rc(rc, mysql);
3805
3806 /* insert by prepare, wrong column name */
3807 strcpy(query, "INSERT INTO test_null(col3, col2) VALUES(?, ?)");
3808 stmt= mysql_stmt_init(mysql);
3809 FAIL_IF(!stmt, mysql_error(mysql));
3810 rc= mysql_stmt_prepare(stmt, SL(query));
3811 FAIL_IF(!rc, "Error expected");
3812 mysql_stmt_close(stmt);
3813
3814 strcpy(query, "INSERT INTO test_null(col1, col2) VALUES(?, ?)");
3815 stmt= mysql_stmt_init(mysql);
3816 FAIL_IF(!stmt, mysql_error(mysql));
3817 rc= mysql_stmt_prepare(stmt, SL(query));
3818 check_stmt_rc(rc, stmt);
3819
3820 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "param_count != 2");
3821
3822 memset(my_bind, '\0', sizeof(my_bind));
3823
3824 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
3825 my_bind[0].is_null= &is_null[0];
3826 is_null[0]= 1;
3827 my_bind[1]= my_bind[0];
3828
3829 rc= mysql_stmt_bind_param(stmt, my_bind);
3830 check_stmt_rc(rc, stmt);
3831
3832 /* now, execute the prepared statement to insert 10 records.. */
3833 for (nData= 0; nData<10; nData++)
3834 {
3835 rc= mysql_stmt_execute(stmt);
3836 check_stmt_rc(rc, stmt);
3837 }
3838
3839 /* Re-bind with MYSQL_TYPE_NULL */
3840 my_bind[0].buffer_type= MYSQL_TYPE_NULL;
3841 is_null[0]= 0; /* reset */
3842 my_bind[1]= my_bind[0];
3843
3844 rc= mysql_stmt_bind_param(stmt, my_bind);
3845 check_stmt_rc(rc, stmt);
3846
3847 for (nData= 0; nData<10; nData++)
3848 {
3849 rc= mysql_stmt_execute(stmt);
3850 check_stmt_rc(rc, stmt);
3851 }
3852
3853 mysql_stmt_close(stmt);
3854
3855 /* now fetch the results ..*/
3856 rc= mysql_commit(mysql);
3857 check_mysql_rc(rc, mysql);
3858
3859 nData*= 2;
3860 rc= my_stmt_result(mysql, "SELECT * FROM test_null");;
3861 FAIL_UNLESS((int) nData == rc, "rc != ndata");
3862
3863 /* Fetch results */
3864 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
3865 my_bind[0].buffer= (void *)&nData; /* this buffer won't be altered */
3866 my_bind[0].length= 0;
3867 my_bind[1]= my_bind[0];
3868 my_bind[0].is_null= &is_null[0];
3869 my_bind[1].is_null= &is_null[1];
3870
3871 stmt= mysql_stmt_init(mysql);
3872 FAIL_IF(!stmt, mysql_error(mysql));
3873 rc= mysql_stmt_prepare(stmt, SL("SELECT * FROM test_null"));
3874 check_stmt_rc(rc, stmt);
3875
3876 rc= mysql_stmt_execute(stmt);
3877 check_stmt_rc(rc, stmt);
3878
3879 rc= mysql_stmt_bind_result(stmt, my_bind);
3880 check_stmt_rc(rc, stmt);
3881
3882 rc= 0;
3883 is_null[0]= is_null[1]= 0;
3884 while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
3885 {
3886 FAIL_UNLESS(is_null[0], "!is_null");
3887 FAIL_UNLESS(is_null[1], "!is_null");
3888 rc++;
3889 is_null[0]= is_null[1]= 0;
3890 }
3891 FAIL_UNLESS(rc == (int) nData, "rc != nData");
3892 mysql_stmt_close(stmt);
3893 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_null");
3894 check_mysql_rc(rc, mysql);
3895 return OK;
3896}
3897
3898static int test_order_param(MYSQL *mysql)
3899{
3900 MYSQL_STMT *stmt;
3901 int rc;
3902 const char *query;
3903
3904 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
3905 check_mysql_rc(rc, mysql);
3906
3907 rc= mysql_query(mysql, "CREATE TABLE t1(a INT, b char(10))");
3908 check_mysql_rc(rc, mysql);
3909
3910 stmt= mysql_stmt_init(mysql);
3911 FAIL_IF(!stmt, mysql_error(mysql));
3912 query= "select sum(a) + 200, 1 from t1 "
3913 " union distinct "
3914 "select sum(a) + 200, 1 from t1 group by b ";
3915 rc= mysql_stmt_prepare(stmt, SL(query));
3916 check_stmt_rc(rc, stmt);
3917 mysql_stmt_close(stmt);
3918
3919 stmt= mysql_stmt_init(mysql);
3920 FAIL_IF(!stmt, mysql_error(mysql));
3921 query= "select sum(a) + 200, ? from t1 group by b "
3922 " union distinct "
3923 "select sum(a) + 200, 1 from t1 group by b ";
3924 rc= mysql_stmt_prepare(stmt, SL(query));
3925 check_stmt_rc(rc, stmt);
3926 mysql_stmt_close(stmt);
3927
3928 stmt= mysql_stmt_init(mysql);
3929 FAIL_IF(!stmt, mysql_error(mysql));
3930 query= "select sum(a) + 200, ? from t1 "
3931 " union distinct "
3932 "select sum(a) + 200, 1 from t1 group by b ";
3933 rc= mysql_stmt_prepare(stmt, SL(query));
3934 check_stmt_rc(rc, stmt);
3935 mysql_stmt_close(stmt);
3936
3937 rc= mysql_query(mysql, "DROP TABLE t1");
3938 check_mysql_rc(rc, mysql);
3939 return OK;
3940}
3941
3942static int test_rename(MYSQL *mysql)
3943{
3944 MYSQL_STMT *stmt;
3945 const char *query= "rename table t1 to t2, t3 to t4";
3946 int rc;
3947
3948 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2, t3, t4");
3949 check_mysql_rc(rc, mysql);
3950
3951 stmt= mysql_stmt_init(mysql);
3952 FAIL_IF(!stmt, mysql_error(mysql));
3953 rc= mysql_stmt_prepare(stmt, SL(query));
3954 check_stmt_rc(rc, stmt);
3955
3956 rc= mysql_query(mysql, "create table t1 (a int)");
3957 check_mysql_rc(rc, mysql);
3958
3959 rc= mysql_stmt_execute(stmt);
3960 FAIL_IF(!rc, "Error expected");
3961
3962 rc= mysql_query(mysql, "create table t3 (a int)");
3963 check_mysql_rc(rc, mysql);
3964
3965 rc= mysql_stmt_execute(stmt);
3966 check_stmt_rc(rc, stmt);
3967
3968 rc= mysql_stmt_execute(stmt);
3969 FAIL_IF(!rc, "Errr expected");
3970
3971 rc= mysql_query(mysql, "rename table t2 to t1, t4 to t3");
3972 check_mysql_rc(rc, mysql);
3973
3974 rc= mysql_stmt_execute(stmt);
3975 check_stmt_rc(rc, stmt);
3976
3977 mysql_stmt_close(stmt);
3978
3979 rc= mysql_query(mysql, "DROP TABLE t2, t4");
3980 check_mysql_rc(rc, mysql);
3981 return OK;
3982}
3983
3984static int test_rewind(MYSQL *mysql)
3985{
3986 MYSQL_STMT *stmt;
3987 MYSQL_BIND my_bind;
3988 int rc = 0;
3989 const char *stmt_text;
3990 long unsigned int length=4, Data=0;
3991 my_bool isnull=0;
3992
3993
3994 stmt_text= "CREATE TABLE t1 (a int)";
3995 rc= mysql_real_query(mysql, SL(stmt_text));
3996 check_mysql_rc(rc, mysql);
3997 stmt_text= "INSERT INTO t1 VALUES(2),(3),(4)";
3998 rc= mysql_real_query(mysql, SL(stmt_text));
3999 check_mysql_rc(rc, mysql);
4000
4001 stmt= mysql_stmt_init(mysql);
4002 stmt_text= "SELECT * FROM t1";
4003 rc= mysql_stmt_prepare(stmt, SL(stmt_text));
4004 check_stmt_rc(rc, stmt);
4005
4006 memset(&my_bind, '\0', sizeof(MYSQL_BIND));
4007 my_bind.buffer_type= MYSQL_TYPE_LONG;
4008 my_bind.buffer= (void *)&Data; /* this buffer won't be altered */
4009 my_bind.length= &length;
4010 my_bind.is_null= &isnull;
4011
4012 rc= mysql_stmt_execute(stmt);
4013 check_stmt_rc(rc, stmt);
4014
4015 rc= mysql_stmt_store_result(stmt);
4016 check_stmt_rc(rc, stmt);
4017
4018 rc= mysql_stmt_bind_result(stmt, &my_bind);
4019 check_stmt_rc(rc, stmt);
4020
4021 /* retrieve all result sets till we are at the end */
4022 while(!(rc=mysql_stmt_fetch(stmt)));
4023 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4024
4025 /* seek to the first row */
4026 mysql_stmt_data_seek(stmt, 0);
4027
4028 /* now we should be able to fetch the results again */
4029 /* but mysql_stmt_fetch returns MYSQL_NO_DATA */
4030 while(!(rc= mysql_stmt_fetch(stmt)));
4031
4032 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4033
4034 stmt_text= "DROP TABLE t1";
4035 rc= mysql_real_query(mysql, SL(stmt_text));
4036 check_mysql_rc(rc, mysql);
4037 rc= mysql_stmt_free_result(stmt);
4038 rc= mysql_stmt_close(stmt);
4039 return OK;
4040}
4041
4042/* Test simple select */
4043
4044static int test_select(MYSQL *mysql)
4045{
4046 MYSQL_STMT *stmt;
4047 int rc;
4048 char szData[25];
4049 int nData= 1;
4050 MYSQL_BIND my_bind[2];
4051 ulong length[2];
4052 char query[MAX_TEST_QUERY_LENGTH];
4053
4054
4055 rc= mysql_autocommit(mysql, TRUE);
4056 check_mysql_rc(rc, mysql);
4057
4058 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_select");
4059 check_mysql_rc(rc, mysql);
4060
4061 rc= mysql_query(mysql, "CREATE TABLE test_select(id int, name varchar(50))");
4062 check_mysql_rc(rc, mysql);
4063
4064 /* insert a row and commit the transaction */
4065 rc= mysql_query(mysql, "INSERT INTO test_select VALUES(10, 'venu')");
4066 check_mysql_rc(rc, mysql);
4067
4068 /* now insert the second row, and roll back the transaction */
4069 rc= mysql_query(mysql, "INSERT INTO test_select VALUES(20, 'mysql')");
4070 check_mysql_rc(rc, mysql);
4071
4072 rc= mysql_commit(mysql);
4073 check_mysql_rc(rc, mysql);
4074
4075 strcpy(query, "SELECT * FROM test_select WHERE id= ? "
4076 "AND CONVERT(name USING utf8) =?");
4077 stmt= mysql_stmt_init(mysql);
4078 FAIL_IF(!stmt, mysql_error(mysql));
4079 rc= mysql_stmt_prepare(stmt, SL(query));
4080 check_stmt_rc(rc, stmt);
4081
4082 FAIL_IF(mysql_stmt_param_count(stmt) != 2, "param_count != 2");
4083
4084 memset(my_bind, '\0', sizeof(my_bind));
4085
4086 /* string data */
4087 nData= 10;
4088 strcpy(szData, (char *)"venu");
4089 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
4090 my_bind[1].buffer= (void *)szData;
4091 my_bind[1].buffer_length= 4;
4092 my_bind[1].length= &length[1];
4093 length[1]= 4;
4094
4095 my_bind[0].buffer= (void *)&nData;
4096 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
4097
4098 rc= mysql_stmt_bind_param(stmt, my_bind);
4099 check_stmt_rc(rc, stmt);
4100
4101 rc= mysql_stmt_execute(stmt);
4102 check_stmt_rc(rc, stmt);
4103
4104 rc= 0;
4105 while (!mysql_stmt_fetch(stmt))
4106 rc++;
4107 FAIL_UNLESS(rc == 1, "rc != 1");
4108
4109 mysql_stmt_close(stmt);
4110 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_select");
4111 check_mysql_rc(rc, mysql);
4112 return OK;
4113}
4114
4115/* Test simple select with prepare */
4116
4117static int test_select_prepare(MYSQL *mysql)
4118{
4119 int rc;
4120 MYSQL_STMT *stmt;
4121
4122
4123 rc= mysql_autocommit(mysql, TRUE);
4124 check_mysql_rc(rc, mysql);
4125
4126 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_select");
4127 check_mysql_rc(rc, mysql);
4128
4129 rc= mysql_query(mysql, "CREATE TABLE test_select(id int, name varchar(50))");
4130 check_mysql_rc(rc, mysql);
4131
4132 /* insert a row and commit the transaction */
4133 rc= mysql_query(mysql, "INSERT INTO test_select VALUES(10, 'venu')");
4134 check_mysql_rc(rc, mysql);
4135
4136 rc= mysql_commit(mysql);
4137 check_mysql_rc(rc, mysql);
4138
4139 stmt= mysql_stmt_init(mysql);
4140 FAIL_IF(!stmt, mysql_error(mysql));
4141 rc= mysql_stmt_prepare(stmt, SL("SELECT * FROM test_select"));
4142 check_stmt_rc(rc, stmt);
4143
4144 rc= mysql_stmt_execute(stmt);
4145 check_stmt_rc(rc, stmt);
4146
4147 rc= 0;
4148 while (!mysql_stmt_fetch(stmt))
4149 rc++;
4150 FAIL_UNLESS(rc == 1, "rowcount != 1");
4151 mysql_stmt_close(stmt);
4152
4153 rc= mysql_query(mysql, "DROP TABLE test_select");
4154 check_mysql_rc(rc, mysql);
4155
4156 rc= mysql_query(mysql, "CREATE TABLE test_select(id tinyint, id1 int, "
4157 " id2 float, id3 float, "
4158 " name varchar(50))");
4159 check_mysql_rc(rc, mysql);
4160
4161 /* insert a row and commit the transaction */
4162 rc= mysql_query(mysql, "INSERT INTO test_select(id, id1, id2, name) VALUES(10, 5, 2.3, 'venu')");
4163 check_mysql_rc(rc, mysql);
4164
4165 rc= mysql_commit(mysql);
4166 check_mysql_rc(rc, mysql);
4167
4168 stmt= mysql_stmt_init(mysql);
4169 FAIL_IF(!stmt, mysql_error(mysql));
4170 rc= mysql_stmt_prepare(stmt, SL("SELECT * FROM test_select"));
4171 check_stmt_rc(rc, stmt);
4172
4173 rc= mysql_stmt_execute(stmt);
4174 check_stmt_rc(rc, stmt);
4175
4176 rc= 0;
4177 while (!mysql_stmt_fetch(stmt))
4178 rc++;
4179 FAIL_UNLESS(rc == 1, "rowcount != 1");
4180 mysql_stmt_close(stmt);
4181 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_select");
4182 check_mysql_rc(rc, mysql);
4183 return OK;
4184}
4185
4186/* Test simple show */
4187
4188static int test_select_show_table(MYSQL *mysql)
4189{
4190 MYSQL_STMT *stmt;
4191 int rc, i;
4192
4193 stmt= mysql_stmt_init(mysql);
4194 FAIL_IF(!stmt, mysql_error(mysql));
4195 rc= mysql_stmt_prepare(stmt, SL("SHOW TABLES FROM mysql"));
4196 check_stmt_rc(rc, stmt);
4197
4198 FAIL_IF(mysql_stmt_param_count(stmt), "param_count != 0");
4199
4200 for (i= 1; i < 3; i++)
4201 {
4202 rc= mysql_stmt_execute(stmt);
4203 check_stmt_rc(rc, stmt);
4204 }
4205
4206 while (!mysql_stmt_fetch(stmt));
4207 mysql_stmt_close(stmt);
4208 return OK;
4209}
4210
4211/* Test simple select */
4212
4213static int test_select_version(MYSQL *mysql)
4214{
4215 MYSQL_STMT *stmt;
4216 int rc;
4217
4218
4219 stmt= mysql_stmt_init(mysql);
4220 FAIL_IF(!stmt, mysql_error(mysql));
4221 rc= mysql_stmt_prepare(stmt, SL("SELECT @@version"));
4222 check_stmt_rc(rc, stmt);
4223
4224 FAIL_IF(mysql_stmt_param_count(stmt), "param_count != 0");
4225
4226 rc= mysql_stmt_execute(stmt);
4227 check_stmt_rc(rc, stmt);
4228
4229 while (!mysql_stmt_fetch(stmt));
4230 mysql_stmt_close(stmt);
4231 return OK;
4232}
4233
4234static int test_selecttmp(MYSQL *mysql)
4235{
4236 MYSQL_STMT *stmt;
4237 int rc, i;
4238 const char *query= "select a, (select count(distinct t1.b) as sum from t1, t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3";
4239
4240
4241 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1, t2, t3");
4242 check_mysql_rc(rc, mysql);
4243
4244 rc= mysql_query(mysql, "CREATE TABLE t1 (a int , b int);");
4245 check_mysql_rc(rc, mysql);
4246
4247 rc= mysql_query(mysql, "create table t2 (a int, b int);");
4248 check_mysql_rc(rc, mysql);
4249
4250 rc= mysql_query(mysql, "create table t3 (a int, b int);");
4251 check_mysql_rc(rc, mysql);
4252
4253 rc= mysql_query(mysql,
4254 "insert into t1 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7), \
4255(2, -1), (3, 10);");
4256 check_mysql_rc(rc, mysql);
4257 rc= mysql_query(mysql,
4258 "insert into t2 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1);");
4259 check_mysql_rc(rc, mysql);
4260 rc= mysql_query(mysql,
4261 "insert into t3 values (3, 3), (2, 2), (1, 1);");
4262 check_mysql_rc(rc, mysql);
4263
4264 stmt= mysql_stmt_init(mysql);
4265 FAIL_IF(!stmt, mysql_error(mysql));
4266 rc= mysql_stmt_prepare(stmt, SL(query));
4267 check_stmt_rc(rc, stmt);
4268 for (i= 0; i < 3; i++)
4269 {
4270 rc= mysql_stmt_execute(stmt);
4271 check_stmt_rc(rc, stmt);
4272 rc= 0;
4273 while (!mysql_stmt_fetch(stmt))
4274 rc++;
4275 FAIL_UNLESS(rc == 3, "rowcount != 3");
4276 }
4277 mysql_stmt_close(stmt);
4278
4279 rc= mysql_query(mysql, "DROP TABLE t1, t2, t3");
4280 check_mysql_rc(rc, mysql);
4281 return OK;
4282}
4283
4284static int test_set_option(MYSQL *mysql)
4285{
4286 MYSQL_STMT *stmt;
4287 MYSQL_RES *result;
4288 int rc;
4289
4290
4291 mysql_autocommit(mysql, TRUE);
4292
4293 /* LIMIT the rows count to 2 */
4294 rc= mysql_query(mysql, "SET SQL_SELECT_LIMIT= 2");
4295 check_mysql_rc(rc, mysql);
4296
4297 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_limit");
4298 check_mysql_rc(rc, mysql);
4299
4300 rc= mysql_query(mysql, "CREATE TABLE test_limit(a tinyint)");
4301 check_mysql_rc(rc, mysql);
4302
4303 rc= mysql_query(mysql, "INSERT INTO test_limit VALUES(10), (20), (30), (40)");
4304 check_mysql_rc(rc, mysql);
4305
4306 rc= mysql_query(mysql, "SELECT * FROM test_limit");
4307 check_mysql_rc(rc, mysql);
4308
4309 result= mysql_store_result(mysql);
4310 FAIL_IF(!result, "Invalid result set");
4311
4312 rc= 0;
4313 while (mysql_fetch_row(result))
4314 rc++;
4315 FAIL_UNLESS(rc == 2, "rowcunt != 2");
4316 mysql_free_result(result);
4317
4318 stmt= mysql_stmt_init(mysql);
4319 FAIL_IF(!stmt, mysql_error(mysql));
4320 rc= mysql_stmt_prepare(stmt, SL("SELECT * FROM test_limit"));
4321 check_stmt_rc(rc, stmt);
4322
4323 rc= mysql_stmt_execute(stmt);
4324 check_stmt_rc(rc, stmt);
4325
4326 rc= 0;
4327 while (!mysql_stmt_fetch(stmt))
4328 rc++;
4329 FAIL_UNLESS(rc == 2, "");
4330
4331 mysql_stmt_close(stmt);
4332
4333 /* RESET the LIMIT the rows count to 0 */
4334 rc= mysql_query(mysql, "SET SQL_SELECT_LIMIT=DEFAULT");
4335 check_mysql_rc(rc, mysql);
4336
4337 stmt= mysql_stmt_init(mysql);
4338 FAIL_IF(!stmt, mysql_error(mysql));
4339 rc= mysql_stmt_prepare(stmt, SL("SELECT * FROM test_limit"));
4340 check_stmt_rc(rc, stmt);
4341
4342 rc= mysql_stmt_execute(stmt);
4343 check_stmt_rc(rc, stmt);
4344
4345 rc= 0;
4346 while (!mysql_stmt_fetch(stmt))
4347 rc++;
4348 FAIL_UNLESS(rc == 4, "rowcount != 4");
4349
4350 mysql_stmt_close(stmt);
4351 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_limit");
4352 check_mysql_rc(rc, mysql);
4353 return OK;
4354}
4355
4356/* Test simple set-variable prepare */
4357
4358static int test_set_variable(MYSQL *mysql)
4359{
4360 MYSQL_STMT *stmt, *stmt1;
4361 int rc;
4362 int set_count, def_count, get_count;
4363 ulong length;
4364 char var[NAME_LEN+1];
4365 MYSQL_BIND set_bind[1], get_bind[2];
4366
4367
4368 mysql_autocommit(mysql, TRUE);
4369
4370 stmt1= mysql_stmt_init(mysql);
4371 FAIL_IF(!stmt1, mysql_error(mysql));
4372 rc= mysql_stmt_prepare(stmt1, SL("show variables like 'max_error_count'"));
4373 check_stmt_rc(rc, stmt1);
4374
4375 memset(get_bind, '\0', sizeof(get_bind));
4376
4377 get_bind[0].buffer_type= MYSQL_TYPE_STRING;
4378 get_bind[0].buffer= (void *)var;
4379 get_bind[0].length= &length;
4380 get_bind[0].buffer_length= (int)NAME_LEN;
4381 length= NAME_LEN;
4382
4383 get_bind[1].buffer_type= MYSQL_TYPE_LONG;
4384 get_bind[1].buffer= (void *)&get_count;
4385
4386 rc= mysql_stmt_execute(stmt1);
4387 check_stmt_rc(rc, stmt1);
4388
4389 rc= mysql_stmt_bind_result(stmt1, get_bind);
4390 check_stmt_rc(rc, stmt1);
4391
4392 rc= mysql_stmt_fetch(stmt1);
4393 check_stmt_rc(rc, stmt1);
4394
4395 def_count= get_count;
4396
4397 FAIL_UNLESS(strcmp(var, "max_error_count") == 0, "var != max_error_count");
4398 rc= mysql_stmt_fetch(stmt1);
4399 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4400
4401 stmt= mysql_stmt_init(mysql);
4402 FAIL_IF(!stmt, mysql_error(mysql));
4403 rc= mysql_stmt_prepare(stmt, SL("set max_error_count=?"));
4404 check_stmt_rc(rc, stmt);
4405
4406 memset(set_bind, '\0', sizeof(set_bind));
4407
4408 set_bind[0].buffer_type= MYSQL_TYPE_LONG;
4409 set_bind[0].buffer= (void *)&set_count;
4410
4411 rc= mysql_stmt_bind_param(stmt, set_bind);
4412 check_stmt_rc(rc, stmt);
4413
4414 set_count= 31;
4415 rc= mysql_stmt_execute(stmt);
4416 check_stmt_rc(rc, stmt);
4417
4418 mysql_commit(mysql);
4419
4420 rc= mysql_stmt_execute(stmt1);
4421 check_stmt_rc(rc, stmt1);
4422
4423 rc= mysql_stmt_fetch(stmt1);
4424 check_stmt_rc(rc, stmt1);
4425
4426 FAIL_UNLESS(get_count == set_count, "get_count != set_count");
4427
4428 rc= mysql_stmt_fetch(stmt1);
4429 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4430
4431 /* restore back to default */
4432 set_count= def_count;
4433 rc= mysql_stmt_execute(stmt);
4434 check_stmt_rc(rc, stmt);
4435
4436 rc= mysql_stmt_execute(stmt1);
4437 check_stmt_rc(rc, stmt1);
4438
4439 rc= mysql_stmt_fetch(stmt1);
4440 check_stmt_rc(rc, stmt1);
4441
4442 FAIL_UNLESS(get_count == set_count, "get_count != set_count");
4443
4444 rc= mysql_stmt_fetch(stmt1);
4445 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4446
4447 mysql_stmt_close(stmt);
4448 mysql_stmt_close(stmt1);
4449 return OK;
4450}
4451
4452/* Test SQLmode */
4453
4454static int test_sqlmode(MYSQL *mysql)
4455{
4456 MYSQL_STMT *stmt;
4457 MYSQL_BIND my_bind[2];
4458 char c1[5], c2[5];
4459 int rc;
4460 int ignore_space= 0;
4461 char query[MAX_TEST_QUERY_LENGTH];
4462
4463
4464 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_piping");
4465 check_mysql_rc(rc, mysql);
4466
4467 rc= mysql_query(mysql, "CREATE TABLE test_piping(name varchar(10))");
4468 check_mysql_rc(rc, mysql);
4469
4470 /* PIPES_AS_CONCAT */
4471 strcpy(query, "SET SQL_MODE= \"PIPES_AS_CONCAT\"");
4472 rc= mysql_query(mysql, query);
4473 check_mysql_rc(rc, mysql);
4474
4475 strcpy(query, "INSERT INTO test_piping VALUES(?||?)");
4476 stmt= mysql_stmt_init(mysql);
4477 FAIL_IF(!stmt, mysql_error(mysql));
4478 rc= mysql_stmt_prepare(stmt, SL(query));
4479 check_stmt_rc(rc, stmt);
4480
4481 memset(my_bind, '\0', sizeof(my_bind));
4482
4483 my_bind[0].buffer_type= MYSQL_TYPE_STRING;
4484 my_bind[0].buffer= (void *)c1;
4485 my_bind[0].buffer_length= 2;
4486
4487 my_bind[1].buffer_type= MYSQL_TYPE_STRING;
4488 my_bind[1].buffer= (void *)c2;
4489 my_bind[1].buffer_length= 3;
4490
4491 rc= mysql_stmt_bind_param(stmt, my_bind);
4492 check_stmt_rc(rc, stmt);
4493
4494 strcpy(c1, "My"); strcpy(c2, "SQL");
4495 rc= mysql_stmt_execute(stmt);
4496 check_stmt_rc(rc, stmt);
4497 mysql_stmt_close(stmt);
4498
4499 if (verify_col_data(mysql, "test_piping", "name", "MySQL"))
4500 return FAIL;
4501
4502 rc= mysql_query(mysql, "DELETE FROM test_piping");
4503 check_mysql_rc(rc, mysql);
4504
4505 strcpy(query, "SELECT connection_id ()");
4506 stmt= mysql_stmt_init(mysql);
4507 FAIL_IF(!stmt, mysql_error(mysql));
4508 rc= mysql_stmt_prepare(stmt, SL(query));
4509 check_stmt_rc(rc, stmt);
4510 mysql_stmt_close(stmt);
4511
4512 /* ANSI */
4513 strcpy(query, "SET SQL_MODE= \"ANSI\"");
4514 rc= mysql_query(mysql, query);
4515 check_mysql_rc(rc, mysql);
4516
4517 strcpy(query, "INSERT INTO test_piping VALUES(?||?)");
4518 stmt= mysql_stmt_init(mysql);
4519 FAIL_IF(!stmt, mysql_error(mysql));
4520 rc= mysql_stmt_prepare(stmt, SL(query));
4521 check_stmt_rc(rc, stmt);
4522
4523 rc= mysql_stmt_bind_param(stmt, my_bind);
4524 check_stmt_rc(rc, stmt);
4525
4526 strcpy(c1, "My"); strcpy(c2, "SQL");
4527 rc= mysql_stmt_execute(stmt);
4528 check_stmt_rc(rc, stmt);
4529
4530 mysql_stmt_close(stmt);
4531 if (verify_col_data(mysql, "test_piping", "name", "MySQL"))
4532 return FAIL;
4533
4534 /* ANSI mode spaces ...
4535 skip, if ignore_space was set
4536 */
4537 query_int_variable(mysql, "@@sql_mode LIKE '%IGNORE_SPACE%'", &ignore_space);
4538
4539 if (!ignore_space)
4540 {
4541 strcpy(query, "SELECT connection_id ()");
4542 stmt= mysql_stmt_init(mysql);
4543 FAIL_IF(!stmt, mysql_error(mysql));
4544 rc= mysql_stmt_prepare(stmt, SL(query));
4545 check_stmt_rc(rc, stmt);
4546
4547 rc= mysql_stmt_execute(stmt);
4548 check_stmt_rc(rc, stmt);
4549
4550 rc= mysql_stmt_fetch(stmt);
4551 check_stmt_rc(rc, stmt);
4552
4553 rc= mysql_stmt_fetch(stmt);
4554 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4555
4556 mysql_stmt_close(stmt);
4557 }
4558 /* IGNORE SPACE MODE */
4559 strcpy(query, "SET SQL_MODE= \"IGNORE_SPACE\"");
4560 rc= mysql_query(mysql, query);
4561 check_mysql_rc(rc, mysql);
4562
4563 strcpy(query, "SELECT connection_id ()");
4564 stmt= mysql_stmt_init(mysql);
4565 FAIL_IF(!stmt, mysql_error(mysql));
4566 rc= mysql_stmt_prepare(stmt, SL(query));
4567 check_stmt_rc(rc, stmt);
4568
4569 rc= mysql_stmt_execute(stmt);
4570 check_stmt_rc(rc, stmt);
4571
4572 rc= mysql_stmt_fetch(stmt);
4573 check_stmt_rc(rc, stmt);
4574
4575 rc= mysql_stmt_fetch(stmt);
4576 FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
4577
4578 mysql_stmt_close(stmt);
4579 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_piping");
4580 check_mysql_rc(rc, mysql);
4581 return OK;
4582}
4583
4584/* Test mysql_stmt_close for open stmts */
4585
4586static int test_stmt_close(MYSQL *mysql)
4587{
4588 MYSQL_STMT *stmt1, *stmt2, *stmt3, *stmt_x;
4589 MYSQL_BIND my_bind[1];
4590 MYSQL_RES *result;
4591 unsigned int count;
4592 int rc;
4593 char query[MAX_TEST_QUERY_LENGTH];
4594 my_bool reconnect= 1;
4595
4596 mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
4597
4598 /* set AUTOCOMMIT to ON*/
4599 mysql_autocommit(mysql, TRUE);
4600
4601 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_stmt_close");
4602 check_mysql_rc(rc, mysql);
4603
4604 rc= mysql_query(mysql, "CREATE TABLE test_stmt_close(id int)");
4605 check_mysql_rc(rc, mysql);
4606
4607 strcpy(query, "DO \"nothing\"");
4608 stmt1= mysql_stmt_init(mysql);
4609 FAIL_IF(!stmt1, mysql_error(mysql));
4610 rc= mysql_stmt_prepare(stmt1, SL(query));
4611 check_stmt_rc(rc, stmt1);
4612
4613 FAIL_IF(mysql_stmt_param_count(stmt1), "param_count != 0");
4614
4615 strcpy(query, "INSERT INTO test_stmt_close(id) VALUES(?)");
4616 stmt_x= mysql_stmt_init(mysql);
4617 FAIL_IF(!stmt_x, mysql_error(mysql));
4618 rc= mysql_stmt_prepare(stmt_x, SL(query));
4619 check_stmt_rc(rc, stmt_x);
4620
4621 FAIL_IF(mysql_stmt_param_count(stmt_x) != 1, "param_count != 1");
4622
4623 strcpy(query, "UPDATE test_stmt_close SET id= ? WHERE id= ?");
4624 stmt3= mysql_stmt_init(mysql);
4625 FAIL_IF(!stmt3, mysql_error(mysql));
4626 rc= mysql_stmt_prepare(stmt3, SL(query));
4627 check_stmt_rc(rc, stmt3);
4628
4629 FAIL_IF(mysql_stmt_param_count(stmt3) != 2, "param_count != 2");
4630
4631 strcpy(query, "SELECT * FROM test_stmt_close WHERE id= ?");
4632 stmt2= mysql_stmt_init(mysql);
4633 FAIL_IF(!stmt2, mysql_error(mysql));
4634 rc= mysql_stmt_prepare(stmt2, SL(query));
4635 check_stmt_rc(rc, stmt2);
4636
4637 FAIL_IF(mysql_stmt_param_count(stmt2) != 1, "param_count != 1");
4638
4639 rc= mysql_stmt_close(stmt1);
4640 check_stmt_rc(rc, stmt1);
4641
4642 /*
4643 Originally we were going to close all statements automatically in
4644 mysql_close(). This proved to not work well - users weren't able to
4645 close statements by hand once mysql_close() had been called.
4646 Now mysql_close() doesn't free any statements, so this test doesn't
4647 serve its original designation any more.
4648 Here we free stmt2 and stmt3 by hand to avoid memory leaks.
4649 */
4650 mysql_stmt_close(stmt2);
4651 mysql_stmt_close(stmt3);
4652
4653 /*
4654 We need to bzero bind structure because mysql_stmt_bind_param checks all
4655 its members.
4656 */
4657 memset(my_bind, '\0', sizeof(my_bind));
4658
4659 my_bind[0].buffer= (void *)&count;
4660 my_bind[0].buffer_type= MYSQL_TYPE_LONG;
4661 count= 100;
4662
4663 rc= mysql_stmt_bind_param(stmt_x, my_bind);
4664 check_stmt_rc(rc, stmt_x);
4665
4666 rc= mysql_stmt_execute(stmt_x);
4667 check_stmt_rc(rc, stmt_x);
4668
4669 FAIL_IF(mysql_stmt_affected_rows(stmt_x) != 1, "affected_rows != 1");
4670
4671 rc= mysql_stmt_close(stmt_x);
4672 check_stmt_rc(rc, stmt_x);
4673
4674 rc= mysql_query(mysql, "SELECT id FROM test_stmt_close");
4675 check_mysql_rc(rc, mysql);
4676
4677 result= mysql_store_result(mysql);
4678 FAIL_IF(!result, "Invalid result set");
4679
4680 rc= 0;
4681 while (mysql_fetch_row(result))
4682 rc++;
4683 FAIL_UNLESS(rc == 1, "rwcount != 1");
4684 mysql_free_result(result);
4685 rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_stmt_close");
4686 check_mysql_rc(rc, mysql);
4687 return OK;
4688}
4689
4690static int test_new_date(MYSQL *mysql)
4691{
4692 MYSQL_STMT *stmt;
4693 MYSQL_BIND bind[1];
4694 int rc;
4695 char buffer[50];
4696 my_bool reconnect= 1;
4697 mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect);
4698
4699 /* set AUTOCOMMIT to ON*/
4700 mysql_autocommit(mysql, TRUE);
4701
4702 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
4703 check_mysql_rc(rc, mysql);
4704
4705 rc= mysql_query(mysql, "CREATE TABLE t1 (a date, b date)");
4706 check_mysql_rc(rc, mysql);
4707
4708 rc= mysql_query(mysql, "INSERT INTO t1 VALUES (now(), now() + INTERVAL 1 day)");
4709 check_mysql_rc(rc, mysql);
4710
4711 stmt= mysql_stmt_init(mysql);
4712 rc= mysql_stmt_prepare(stmt, "SELECT if(1, a, b) FROM t1", 26);
4713 check_stmt_rc(rc, stmt);
4714
4715 memset(bind, 0, sizeof(MYSQL_BIND));
4716 bind[0].buffer_length= 50;
4717 bind[0].buffer= (void *)buffer;
4718 bind[0].buffer_type= MYSQL_TYPE_STRING;
4719
4720 rc= mysql_stmt_execute(stmt);
4721 check_stmt_rc(rc, stmt);
4722
4723 rc= mysql_stmt_bind_result(stmt, bind);
4724 check_stmt_rc(rc, stmt);
4725
4726 rc= mysql_stmt_fetch(stmt);
4727 check_stmt_rc(rc, stmt);
4728
4729 rc= mysql_stmt_fetch(stmt);
4730 FAIL_IF(rc != MYSQL_NO_DATA, "NO DATA expected");
4731
4732 mysql_stmt_close(stmt);
4733 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
4734 check_mysql_rc(rc, mysql);
4735 return OK;
4736}
4737
4738static int test_long_data1(MYSQL *mysql)
4739{
4740 MYSQL_STMT *stmt;
4741 int rc;
4742 MYSQL_BIND bind[1];
4743 char query[MAX_TEST_QUERY_LENGTH];
4744 const char *data= "12345";
4745
4746 rc= mysql_autocommit(mysql, TRUE);
4747 check_mysql_rc(rc, mysql);
4748
4749 rc= mysql_query(mysql, "DROP TABLE IF EXISTS tld");
4750 check_mysql_rc(rc, mysql);
4751
4752 rc= mysql_query(mysql, "CREATE TABLE tld (col1 int, "
4753 "col2 long varbinary)");
4754 check_mysql_rc(rc, mysql);
4755 rc= mysql_query(mysql, "INSERT INTO tld VALUES (1,'test')");
4756 check_mysql_rc(rc, mysql);
4757
4758 strcpy(query, "UPDATE tld SET col2=? WHERE col1=1");
4759 stmt= mysql_stmt_init(mysql);
4760 FAIL_IF(!stmt, mysql_error(mysql));
4761 rc= mysql_stmt_prepare(stmt, SL(query));
4762 check_stmt_rc(rc, stmt);
4763 memset(bind, 0, sizeof(MYSQL_BIND));
4764 bind[0].buffer_type= MYSQL_TYPE_STRING;
4765 rc= mysql_stmt_bind_param(stmt, bind);
4766 check_stmt_rc(rc, stmt);
4767 rc= mysql_stmt_send_long_data(stmt, 0, data, 6);
4768 check_stmt_rc(rc, stmt);
4769 rc= mysql_stmt_execute(stmt);
4770 check_stmt_rc(rc, stmt);
4771 rc= mysql_stmt_close(stmt);
4772 check_stmt_rc(rc, stmt);
4773 rc= mysql_query(mysql, "DROP TABLE IF EXISTS tld");
4774 check_mysql_rc(rc, mysql);
4775 return OK;
4776}
4777
4778int test_blob_9000(MYSQL *mysql)
4779{
4780 MYSQL_BIND bind[1];
4781 MYSQL_STMT *stmt;
4782 int rc;
4783 char buffer[9200];
4784 const char *query= "INSERT INTO tb9000 VALUES (?)";
4785
4786 rc= mysql_query(mysql, "DROP TABLE IF EXISTS tb9000");
4787 check_mysql_rc(rc, mysql);
4788 rc= mysql_query(mysql, "CREATE TABLE tb9000 (a blob)");
4789 check_mysql_rc(rc, mysql);
4790
4791 stmt= mysql_stmt_init(mysql);
4792 rc= mysql_stmt_prepare(stmt, SL(query));
4793
4794 memset(bind, 0, sizeof(MYSQL_BIND));
4795 memset(buffer, 'C', 9200);
4796 bind[0].buffer= buffer;
4797 bind[0].buffer_length= 9200;
4798 bind[0].buffer_type= MYSQL_TYPE_STRING;
4799 rc= mysql_stmt_bind_param(stmt, bind);
4800 check_stmt_rc(rc, stmt);
4801 rc= mysql_stmt_execute(stmt);
4802 check_stmt_rc(rc, stmt);
4803
4804 mysql_stmt_close(stmt);
4805 rc= mysql_query(mysql, "DROP TABLE IF EXISTS tb9000");
4806 check_mysql_rc(rc, mysql);
4807 return OK;
4808}
4809
4810int test_fracseconds(MYSQL *mysql)
4811{
4812 MYSQL_STMT *stmt;
4813 int rc;
4814 const char *str= "SELECT NOW(6)";
4815 char buffer[60], buffer1[60];
4816 MYSQL_BIND bind[2];
4817
4818 stmt= mysql_stmt_init(mysql);
4819 rc= mysql_stmt_prepare(stmt, SL(str));
4820 check_stmt_rc(rc, stmt);
4821
4822 rc= mysql_stmt_execute(stmt);
4823 check_stmt_rc(rc, stmt);
4824
4825 memset(&bind, 0, sizeof(MYSQL_BIND));
4826 bind[0].buffer= buffer;
4827 bind[0].buffer_length=60;
4828 bind[0].buffer_type= MYSQL_TYPE_STRING;
4829
4830 rc= mysql_stmt_bind_result(stmt, bind);
4831 check_stmt_rc(rc, stmt);
4832
4833 rc= mysql_stmt_fetch(stmt);
4834 check_stmt_rc(rc, stmt);
4835
4836 FAIL_IF(strlen(buffer) != 26, "Expected timestamp with length of 26");
4837
4838 rc= mysql_stmt_close(stmt);
4839 check_stmt_rc(rc, stmt);
4840
4841 rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
4842 check_mysql_rc(rc, mysql);
4843
4844 rc= mysql_query(mysql, "CREATE TABLE t1 (a timestamp(6), b time(6))");
4845 check_mysql_rc(rc, mysql);
4846
4847 rc= mysql_query(mysql, "INSERT INTO t1 VALUES ('2012-04-25 10:20:49.0194','10:20:49.0194' )");
4848 check_mysql_rc(rc, mysql);
4849
4850 stmt= mysql_stmt_init(mysql);
4851 rc= mysql_stmt_prepare(stmt, "SELECT a,b FROM t1", 18);
4852 check_stmt_rc(rc, stmt);
4853
4854 rc= mysql_stmt_execute(stmt);
4855 check_stmt_rc(rc, stmt);
4856
4857 memset(bind, 0, 2 * sizeof(MYSQL_BIND));
4858 bind[0].buffer= buffer;
4859 bind[1].buffer= buffer1;
4860 bind[0].buffer_length= bind[1].buffer_length= 60;
4861 bind[0].buffer_type= bind[1].buffer_type= MYSQL_TYPE_STRING;
4862
4863 rc= mysql_stmt_bind_result(stmt, bind);
4864 check_stmt_rc(rc, stmt);
4865
4866 rc= mysql_stmt_fetch(stmt);
4867 check_stmt_rc(rc, stmt);
4868 FAIL_IF(strcmp(buffer, "2012-04-25 10:20:49.019400") != 0, "Wrong result");
4869 FAIL_IF(strcmp(buffer1, "10:20:49.019400") != 0, "Wrong result");
4870
4871 rc= mysql_stmt_close(stmt);
4872 check_stmt_rc(rc, stmt);
4873
4874 rc= mysql_query(mysql, "DROP TABLE t1");
4875
4876 return OK;
4877}
4878
4879int test_notrunc(MYSQL *mysql)
4880{
4881 MYSQL_STMT *stmt;
4882 my_bool trunc= 1;
4883 MYSQL_BIND bind[2];
4884 char buffer[5], buffer2[5];
4885 int rc;
4886 my_bool error= 0;
4887 unsigned long len= 1;
4888
4889 const char *query= "SELECT '1234567890', 'foo' FROM DUAL";
4890
4891 mysql_options(mysql, MYSQL_REPORT_DATA_TRUNCATION, &trunc);
4892
4893 stmt= mysql_stmt_init(mysql);
4894
4895 rc= mysql_stmt_prepare(stmt, SL(query));
4896 check_stmt_rc(rc, stmt);
4897
4898 rc= mysql_stmt_execute(stmt);
4899 check_stmt_rc(rc, stmt);
4900
4901 strcpy(buffer, "bar");
4902
4903 memset(bind, 0, sizeof(MYSQL_BIND) * 2);
4904 bind[0].buffer_type= MYSQL_TYPE_NULL;
4905 bind[0].buffer= buffer;
4906 bind[0].buffer_length= 1;
4907 bind[0].length= &len;
4908 bind[0].flags|= MADB_BIND_DUMMY;
4909 bind[0].error= &error;
4910 bind[1].buffer_type= MYSQL_TYPE_STRING;
4911 bind[1].buffer= buffer2;
4912 bind[1].buffer_length= 5;
4913
4914 rc= mysql_stmt_bind_result(stmt, bind);
4915 check_stmt_rc(rc, stmt);
4916 mysql_stmt_store_result(stmt);
4917
4918 rc= mysql_stmt_fetch(stmt);
4919 mysql_stmt_close(stmt);
4920
4921 FAIL_IF(rc!= 0, "expected rc= 0");
4922 FAIL_IF(strcmp(buffer, "bar"), "Bind dummy failed");
4923 FAIL_IF(strcmp(buffer2, "foo"), "Invalid second buffer");
4924
4925 return OK;
4926}
4927
4928static int test_bit2tiny(MYSQL *mysql)
4929{
4930 MYSQL_BIND bind[2];
4931 char data[11];
4932 unsigned long length[2];
4933 my_bool is_null[2], error[2];
4934 const char *query = "SELECT val FROM justbit";
4935 MYSQL_STMT *stmt;
4936 int rc;
4937
4938 mysql_query(mysql, "DROP TABLE IF EXISTS justbit");
4939 mysql_query(mysql, "CREATE TABLE justbit(val bit(1) not null)");
4940 mysql_query(mysql, "INSERT INTO justbit values (1)");
4941
4942 stmt= mysql_stmt_init(mysql);
4943 rc= mysql_stmt_prepare(stmt, SL(query));
4944 check_stmt_rc(rc, stmt);
4945
4946 memset(bind, '\0', sizeof(bind));
4947
4948 bind[0].buffer_type= MYSQL_TYPE_TINY;
4949 bind[0].buffer= &data[0];
4950 bind[0].buffer_length= 1;
4951 bind[0].is_null= &is_null[0];
4952 bind[0].length= &length[0];
4953 bind[0].error= &error[0];
4954
4955 rc= mysql_stmt_execute(stmt);
4956 check_stmt_rc(rc, stmt);
4957
4958 rc= mysql_stmt_bind_result(stmt, bind);
4959 check_stmt_rc(rc, stmt);
4960
4961 rc= mysql_stmt_store_result(stmt);
4962 check_stmt_rc(rc, stmt);
4963
4964 mysql_stmt_fetch(stmt);
4965
4966 FAIL_IF(data[0] != 1, "Value should be 1");
4967
4968 mysql_stmt_free_result(stmt);
4969 mysql_stmt_close(stmt);
4970 rc= mysql_query(mysql, "DROP TABLE IF EXISTS justbit");
4971 check_mysql_rc(rc, mysql);
4972 return OK;
4973}
4974
4975static int test_reexecute(MYSQL *mysql)
4976{
4977 MYSQL_STMT *stmt;
4978 MYSQL_BIND ps_params[3]; /* input parameter buffers */
4979 int int_data[3]; /* input/output values */
4980 int rc;
4981
4982 if (!mariadb_connection(mysql))
4983 return SKIP;
4984
4985 /* set up stored procedure */
4986 rc = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
4987 check_mysql_rc(rc, mysql);
4988
4989 rc = mysql_query(mysql,
4990 "CREATE PROCEDURE p1("
4991 " IN p_in INT, "
4992 " OUT p_out INT, "
4993 " INOUT p_inout INT) "
4994 "BEGIN "
4995 " SELECT p_in, p_out, p_inout; "
4996 " SET p_in = 100, p_out = 200, p_inout = 300; "
4997 " SELECT p_in, p_out, p_inout; "
4998 "END");
4999 check_mysql_rc(rc, mysql);
5000
5001 /* initialize and prepare CALL statement with parameter placeholders */
5002 stmt = mysql_stmt_init(mysql);
5003 if (!stmt)
5004 {
5005 diag("Could not initialize statement");
5006 exit(1);
5007 }
5008 rc = mysql_stmt_prepare(stmt, "CALL p1(?, ?, ?)", 16);
5009 check_stmt_rc(rc, stmt);
5010
5011 /* initialize parameters: p_in, p_out, p_inout (all INT) */
5012 memset(ps_params, 0, sizeof (ps_params));
5013
5014 ps_params[0].buffer_type = MYSQL_TYPE_LONG;
5015 ps_params[0].buffer = (char *) &int_data[0];
5016 ps_params[0].length = 0;
5017 ps_params[0].is_null = 0;
5018
5019 ps_params[1].buffer_type = MYSQL_TYPE_LONG;
5020 ps_params[1].buffer = (char *) &int_data[1];
5021 ps_params[1].length = 0;
5022 ps_params[1].is_null = 0;
5023
5024 ps_params[2].buffer_type = MYSQL_TYPE_LONG;
5025 ps_params[2].buffer = (char *) &int_data[2];
5026 ps_params[2].length = 0;
5027 ps_params[2].is_null = 0;
5028
5029 /* bind parameters */
5030 rc = mysql_stmt_bind_param(stmt, ps_params);
5031 check_stmt_rc(rc, stmt);
5032
5033 /* assign values to parameters and execute statement */
5034 int_data[0]= 10; /* p_in */
5035 int_data[1]= 20; /* p_out */
5036 int_data[2]= 30; /* p_inout */
5037
5038 rc = mysql_stmt_execute(stmt);
5039 check_stmt_rc(rc, stmt);
5040
5041 rc= mysql_stmt_execute(stmt);
5042 check_stmt_rc(rc, stmt);
5043
5044 mysql_stmt_close(stmt);
5045
5046 rc = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
5047 check_mysql_rc(rc, mysql);
5048 return OK;
5049}
5050
5051static int test_prepare_error(MYSQL *mysql)
5052{
5053 MYSQL_STMT *stmt= mysql_stmt_init(mysql);
5054 int rc;
5055
5056 rc= mysql_stmt_prepare(stmt, SL("SELECT 1 FROM tbl_not_exists"));
5057 FAIL_IF(!rc, "Expected error");
5058
5059 rc= mysql_stmt_reset(stmt);
5060 check_stmt_rc(rc, stmt);
5061
5062 rc= mysql_stmt_prepare(stmt, SL("SELECT 1 FROM tbl_not_exists"));
5063 FAIL_IF(!rc, "Expected error");
5064
5065 rc= mysql_stmt_reset(stmt);
5066 check_stmt_rc(rc, stmt);
5067
5068 rc= mysql_stmt_prepare(stmt, SL("SET @a:=1"));
5069 check_stmt_rc(rc, stmt);
5070
5071 mysql_stmt_close(stmt);
5072 return OK;
5073}
5074
5075struct my_tests_st my_tests[] = {
5076 {"test_prepare_error", test_prepare_error, TEST_CONNECTION_NEW, 0, NULL, NULL},
5077 {"test_reexecute", test_reexecute, TEST_CONNECTION_NEW, 0, NULL, NULL},
5078 {"test_bit2tiny", test_bit2tiny, TEST_CONNECTION_NEW, 0, NULL, NULL},
5079 {"test_conc97", test_conc97, TEST_CONNECTION_NEW, 0, NULL, NULL},
5080 {"test_conc83", test_conc83, TEST_CONNECTION_NONE, 0, NULL, NULL},
5081 {"test_conc60", test_conc60, TEST_CONNECTION_DEFAULT, 0, NULL, NULL},
5082 {"test_notrunc", test_notrunc, TEST_CONNECTION_DEFAULT, 0, NULL, NULL},
5083 {"test_fracseconds", test_fracseconds, TEST_CONNECTION_DEFAULT, 0, NULL, NULL},
5084 {"test_blob_9000", test_blob_9000, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5085 {"test_long_data1", test_long_data1, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5086 {"test_prepare_insert_update", test_prepare_insert_update, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5087 {"test_prepare_simple", test_prepare_simple, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5088 {"test_prepare_syntax", test_prepare_syntax, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5089 {"test_prepare_field_result", test_prepare_field_result, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5090 {"test_prepare", test_prepare, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5091 {"test_prepare_ext", test_prepare_ext, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5092 {"test_prepare_multi_statements", test_prepare_multi_statements, TEST_CONNECTION_NEW, 0, NULL , NULL},
5093 {"test_prepare_alter", test_prepare_alter, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5094 {"test_prepare_resultset", test_prepare_resultset, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5095 {"test_open_direct", test_open_direct, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5096 {"test_select_show", test_select_show, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5097 {"test_select", test_select, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5098 {"test_long_data", test_long_data, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5099 {"test_long_data_str", test_long_data_str, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5100 {"test_long_data_str1", test_long_data_str1, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5101 {"test_long_data_bin", test_long_data_bin, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5102 {"test_simple_update", test_simple_update, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5103 {"test_simple_delete", test_simple_delete, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5104 {"test_update", test_update, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5105 {"test_prepare_noparam", test_prepare_noparam, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5106 {"test_bind_result", test_bind_result, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5107 {"test_bind_result_ext", test_bind_result_ext, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5108 {"test_bind_result_ext1", test_bind_result_ext1, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5109 {"test_bind_negative", test_bind_negative, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5110 {"test_buffers", test_buffers, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5111 {"test_xjoin", test_xjoin, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5112 {"test_union", test_union, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5113 {"test_union2", test_union2, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5114 {"test_union_param", test_union_param, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5115 {"test_pure_coverage", test_pure_coverage, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5116 {"test_insert_select", test_insert_select, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5117 {"test_insert", test_insert, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5118 {"test_join", test_join, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5119 {"test_left_join_view", test_left_join_view, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5120 {"test_manual_sample", test_manual_sample, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5121 {"test_create_drop", test_create_drop, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5122 {"test_date", test_date, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5123 {"test_date_ts", test_date_ts, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5124 {"test_date_dt", test_date_dt, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5125 {"test_date_date", test_date_date, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5126 {"test_date_time", test_date_time, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5127 {"test_datetime_ranges", test_datetime_ranges, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5128 {"test_derived", test_derived, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5129 {"test_distinct", test_distinct, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5130 {"test_do_set", test_do_set, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5131 {"test_double_compare", test_double_compare, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5132 {"test_multi", test_multi, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5133 {"test_multi_stmt", test_multi_stmt, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5134 {"test_nstmts", test_nstmts, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5135 {"test_null", test_null, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5136 {"test_order_param", test_order_param, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5137 {"test_rename", test_rename, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5138 {"test_rewind", test_rewind, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5139 {"test_select_prepare", test_select_prepare, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5140 {"test_select_show_table", test_select_show_table, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5141 {"test_select_version", test_select_version, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5142 {"test_selecttmp", test_selecttmp, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5143 {"test_set_option", test_set_option, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5144 {"test_set_variable", test_set_variable, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5145 {"test_sqlmode", test_sqlmode, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5146 {"test_stmt_close", test_stmt_close, TEST_CONNECTION_DEFAULT, 0, NULL , NULL},
5147 {"test_new_date", test_new_date, TEST_CONNECTION_DEFAULT, 0, NULL, NULL},
5148 {NULL, NULL, 0, 0, NULL, NULL}
5149};
5150
5151int main(int argc, char **argv)
5152{
5153 if (argc > 1)
5154 get_options(argc, argv);
5155
5156 get_envvars();
5157
5158 run_tests(my_tests);
5159
5160 return(exit_status());
5161}
5162