1 //
2 // Copyright (C) 2004-2006 Maciej Sobczak, Stephen Hutton, Rafal Bobrowski
3 // Distributed under the Boost Software License, Version 1.0.
4 // (See accompanying file LICENSE_1_0.txt or copy at
5 // http://www.boost.org/LICENSE_1_0.txt)
6 //
7 //
8
9 #include "soci/soci.h"
10 #include "soci/firebird/soci-firebird.h"
11 #include "soci-compiler.h"
12 #include "firebird/error-firebird.h" // soci::details::Firebird::throw_iscerror()
13 #include "firebird/common.h"
14 #include "common-tests.h"
15 #include <iostream>
16 #include <string>
17 #include <ctime>
18 #include <cstring>
19 #include <cmath>
20
21 using namespace soci;
22
23 std::string connectString;
24 soci::backend_factory const &backEnd = *factory_firebird();
25
26 // fundamental tests - transactions in Firebird
27 TEST_CASE("Firebird transactions", "[firebird][transaction]")
28 {
29 soci::session sql(backEnd, connectString);
30
31 // In Firebird transaction is always required and is started
32 // automatically when session is opened. There is no need to
33 // call session::begin(); it will do nothing if there is active
34 // transaction.
35
36 // sql.begin();
37
38 try
39 {
40 sql << "drop table test1";
41 }
42 catch (soci_error const &)
43 {} // ignore if error
44
45 sql << "create table test1 (id integer)";
46
47 // After DDL statement transaction must be commited or changes
48 // won't be visible to active transaction.
49 sql.commit();
50
51 // After commit or rollback, transaction must be started manually.
52 sql.begin();
53
54 sql << "insert into test1(id) values(5)";
55 sql << "drop table test1";
56
57 // Transaction is automatically commited in session's destructor
58 }
59
60 // character types
61 TEST_CASE("Firebird char types", "[firebird][string]")
62 {
63 soci::session sql(backEnd, connectString);
64
65 try
66 {
67 sql << "drop table test2";
68 }
69 catch (soci_error const &)
70 {} // ignore if error
71
72 sql << "create table test2 (p1 char(10) character set none, p2 varchar(10) character set none)";
73 sql.commit();
74
75 sql.begin();
76
77 {
78 char a('a'), b('b'), c1, c2;
79
80 sql << "insert into test2(p1,p2) values(?,?)", use(a), use(b);
81
82 sql << "select p1,p2 from test2", into(c1), into(c2);
83 CHECK(c1 == 'a');
84 CHECK(c2 == 'b');
85
86 sql << "delete from test2";
87 }
88
89 #if 0 // SOCI doesn't support binding into(char *, ...) anymore, use std::string
90 {
91 char msg[] = "Hello, Firebird!";
92 char buf1[100], buf2[100], buf3[100];
93 char *b1 = buf1, *b2 = buf2, *b3 = buf3;
94
95 strcpy(b1, msg);
96
97 sql << "insert into test2(p1, p2) values (?,?)", use(b1, 100), use(b1, 100);
98 sql << "select p1, p2 from test2", into(b2, 100), into(b3, 100);
99
100 CHECK(!std::strcmp(buf2, buf3));
101 CHECK(!std::strcmp(buf2, "Hello, Fir"));
102
103 sql << "delete from test2";
104 }
105
106 {
107 char msg[] = "Hello, Firebird!";
108 char buf1[100], buf2[100], buf3[100];
109 strcpy(buf1, msg);
110
111 sql << "insert into test2(p1, p2) values (?,?)",
112 use(buf1), use(buf1);
113 sql << "select p1, p2 from test2", into(buf2), into(buf3);
114
115 CHECK(!std::strcmp(buf2, buf3));
116 CHECK(!std::strcmp(buf2, "Hello, Fir"));
117
118 sql << "delete from test2";
119 }
120 #endif
121
122 {
123 // The test string is exactly 10 bytes long, i.e. same as column length.
124 std::string b1("Hello, FB!"), b2, b3;
125
126 sql << "insert into test2(p1, p2) values (?,?)", use(b1), use(b1);
127 sql << "select p1, p2 from test2", into(b2), into(b3);
128
129 CHECK(b2 == b3);
130 CHECK(b2 == "Hello, FB!");
131
132 sql << "delete from test2";
133 }
134
135 {
136 // verify blank padding in CHAR fields
137 // In Firebird, CHAR fields are always padded with whitespaces.
138 char msg[] = "Hello";
139 sql << "insert into test2(p1) values(\'" << msg << "\')";
140
141 char buf[20];
142 std::string buf_str;
143 sql << "select p1 from test2", into(buf_str);
144 std::strcpy(buf, buf_str.c_str());
145
146 CHECK(std::strncmp(buf, msg, 5) == 0);
147 // This test works only for charset none
148 CHECK(std::strncmp(buf+5, " ", 5) == 0);
149
150 sql << "delete from test2";
151 }
152
153 sql << "drop table test2";
154 }
155
156 // date and time
157 TEST_CASE("Firebird date and time", "[firebird][datetime]")
158 {
159 soci::session sql(backEnd, connectString);
160
161 try
162 {
163 sql << "drop table test3";
164 }
165 catch (soci_error const &)
166 {} // ignore if error
167
168 sql << "create table test3 (p1 timestamp, p2 date, p3 time)";
169 sql.commit();
170
171 sql.begin();
172
173 std::tm t1 = std::tm();
174 std::tm t2 = std::tm();
175 std::tm t3 = std::tm();
176 std::time_t now = std::time(NULL);
177 std::tm t = *std::localtime(&now);
178 sql << "insert into test3(p1, p2, p3) "
179 << "values (?,?,?)", use(t), use(t), use(t);
180
181 sql << "select p1, p2, p3 from test3", into(t1), into(t2), into(t3);
182
183 // timestamp
184 CHECK(t1.tm_year == t.tm_year);
185 CHECK(t1.tm_mon == t.tm_mon);
186 CHECK(t1.tm_mday == t.tm_mday);
187 CHECK(t1.tm_hour == t.tm_hour);
188 CHECK(t1.tm_min == t.tm_min);
189 CHECK(t1.tm_sec == t.tm_sec);
190
191 // date
192 CHECK(t2.tm_year == t.tm_year);
193 CHECK(t2.tm_mon == t.tm_mon);
194 CHECK(t2.tm_mday == t.tm_mday);
195 CHECK(t2.tm_hour == 0);
196 CHECK(t2.tm_min == 0);
197 CHECK(t2.tm_sec == 0);
198
199 // time
200 CHECK(t3.tm_year == 0);
201 CHECK(t3.tm_mon == 0);
202 CHECK(t3.tm_mday == 0);
203 CHECK(t3.tm_hour == t.tm_hour);
204 CHECK(t3.tm_min == t.tm_min);
205 CHECK(t3.tm_sec == t.tm_sec);
206
207 sql << "drop table test3";
208 }
209
210 // floating points
211 TEST_CASE("Firebird floating point", "[firebird][float]")
212 {
213 soci::session sql(backEnd, connectString);
214
215 try
216 {
217 sql << "drop table test4";
218 }
219 catch (soci_error const &)
220 {} // ignore if error
221
222 sql << "create table test4 (p1 numeric(8,2), "
223 << "p2 decimal(14,8), p3 double precision, p4 integer)";
224 sql.commit();
225
226 sql.begin();
227
228 double d1 = 1234.23, d2 = 1e8, d3 = 1.0/1440.0,
229 d4, d5, d6;
230
231 sql << "insert into test4(p1, p2, p3) values (?,?,?)",
232 use(d1), use(d2), use(d3);
233
234 sql << "select p1, p2, p3 from test4",
235 into(d4), into(d5), into(d6);
236
237 // The doubles should make the round trip unchanged, so use the exact
238 // comparisons here.
239 CHECK(tests::are_doubles_exactly_equal(d1, d4));
240 CHECK(tests::are_doubles_exactly_equal(d2, d5));
241 CHECK(tests::are_doubles_exactly_equal(d3, d6));
242
243 // test negative doubles too
244 sql << "delete from test4";
245 d1 = -d1;
246 d2 = -d2;
247 d3 = -d3;
248
249 sql << "insert into test4(p1, p2, p3) values (?,?,?)",
250 use(d1), use(d2), use(d3);
251
252 sql << "select p1, p2, p3 from test4",
253 into(d4), into(d5), into(d6);
254
255 CHECK(tests::are_doubles_exactly_equal(d1, d4));
256 CHECK(tests::are_doubles_exactly_equal(d2, d5));
257 CHECK(tests::are_doubles_exactly_equal(d3, d6));
258
259 // verify an exception is thrown when fetching non-integral value
260 // to integral variable
261 try
262 {
263 int i;
264 sql << "select p1 from test4", into(i);
265
266 // expecting error
267 CHECK(false);
268 }
269 catch (soci_error const &e)
270 {
271 CHECK(e.get_error_message() ==
272 "Can't convert value with scale 2 to integral type");
273 }
274
275 // verify an exception is thrown when inserting non-integral value
276 // to integral column
277 try
278 {
279 sql << "insert into test4(p4) values(?)", use(d1);
280
281 // expecting error
282 CHECK(false);
283 }
284 catch (soci_error const &e)
285 {
286 CHECK(e.get_error_message() ==
287 "Can't convert non-integral value to integral column type");
288 }
289
290 sql << "drop table test4";
291 }
292
293 // integer types and indicators
294 TEST_CASE("Firebird integers", "[firebird][int]")
295 {
296 soci::session sql(backEnd, connectString);
297
298 {
299 short sh(0);
300 sql << "select 3 from rdb$database", into(sh);
301 CHECK(sh == 3);
302 }
303
304 {
305 int i(0);
306 sql << "select 5 from rdb$database", into(i);
307 CHECK(i == 5);
308 }
309
310 {
311 unsigned long ul(0);
312 sql << "select 7 from rdb$database", into(ul);
313 CHECK(ul == 7);
314 }
315
316 {
317 // test indicators
318 indicator ind;
319 int i;
320
321 sql << "select 2 from rdb$database", into(i, ind);
322 CHECK(ind == i_ok);
323
324 sql << "select NULL from rdb$database", into(i, ind);
325 CHECK(ind == i_null);
326
327 #if 0 // SOCI doesn't support binding into(char *, ...) anymore, use std::string
328 char buf[4];
329 sql << "select \'Hello\' from rdb$database", into(buf, ind);
330 CHECK(ind == i_truncated);
331 #endif
332
333 sql << "select 5 from rdb$database where 0 = 1", into(i, ind);
334 CHECK(sql.got_data() == false);
335
336 try
337 {
338 // expect error
339 sql << "select NULL from rdb$database", into(i);
340 CHECK(false);
341 }
342 catch (soci_error const &e)
343 {
344 CHECK(e.get_error_message() ==
345 "Null value fetched and no indicator defined.");
346 }
347
348 // expect no data
349 sql << "select 5 from rdb$database where 0 = 1", into(i);
350 CHECK(!sql.got_data());
351 }
352 }
353
354 // repeated fetch and bulk operations for character types
355 TEST_CASE("Firebird bulk operations", "[firebird][bulk]")
356 {
357 soci::session sql(backEnd, connectString);
358
359 try
360 {
361 sql << "drop table test6";
362 }
363 catch (soci_error const &)
364 {} // ignore if error
365
366 sql << "create table test6 (p1 char(10) character set none, p2 varchar(10) character set none)";
367 sql.commit();
368
369 sql.begin();
370
371 for (char c = 'a'; c <= 'z'; ++c)
372 {
373 sql << "insert into test6(p1, p2) values(?,?)", use(c), use(c);
374 }
375
376 {
377 char c, c1, c2;
378
379 statement st = (sql.prepare <<
380 "select p1,p2 from test6 order by p1", into(c1), into(c2));
381
382 // Verify that fetch after re-executing the same statement works.
383 for (int n = 0; n < 2; ++n)
384 {
385 st.execute();
386
387 c='a';
388 while (st.fetch())
389 {
390 CHECK(c == c1);
391 CHECK(c == c2);
392 ++c;
393 }
394 CHECK(c == 'z'+1);
395 }
396 }
397
398 {
399 char c='a';
400
401 std::vector<char> c1(10), c2(10);
402
403 statement st = (sql.prepare <<
404 "select p1,p2 from test6 order by p1", into(c1), into(c2));
405
406 st.execute();
407 while (st.fetch())
408 {
409 for (std::size_t i = 0; i != c1.size(); ++i)
410 {
411 CHECK(c == c1[i]);
412 CHECK(c == c2[i]);
413 ++c;
414 }
415 }
416 CHECK(c == 'z' + 1);
417 }
418
419 {
420 // verify an exception is thrown when empty vector is used
421 std::vector<char> vec;
422 try
423 {
424 sql << "select p1 from test6", into(vec);
425 CHECK(false);
426 }
427 catch (soci_error const &e)
428 {
429 CHECK(e.get_error_message() ==
430 "Vectors of size 0 are not allowed.");
431 }
432 }
433
434 sql << "delete from test6";
435
436 // verifying std::string
437 int const rowsToTest = 10;
438 for (int i = 0; i != rowsToTest; ++i)
439 {
440 std::ostringstream ss;
441 ss << "Hello_" << i;
442
443 std::string const &x = ss.str();
444
445 sql << "insert into test6(p1, p2) values(\'"
446 << x << "\', \'" << x << "\')";
447 }
448
449 int count;
450 sql << "select count(*) from test6", into(count);
451 CHECK(count == rowsToTest);
452
453 {
454 int i = 0;
455 std::string s1, s2;
456 statement st = (sql.prepare <<
457 "select p1, p2 from test6 order by p1", into(s1), into(s2));
458
459 st.execute();
460 while (st.fetch())
461 {
462 std::ostringstream ss;
463 ss << "Hello_" << i;
464 std::string const &x = ss.str();
465
466 // Note: CHAR fields are always padded with whitespaces
467 ss << " ";
468 CHECK(s1 == ss.str());
469 CHECK(s2 == x);
470 ++i;
471 }
472 CHECK(i == rowsToTest);
473 }
474
475 {
476 int i = 0;
477
478 std::vector<std::string> s1(4), s2(4);
479 statement st = (sql.prepare <<
480 "select p1, p2 from test6 order by p1", into(s1), into(s2));
481 st.execute();
482 while (st.fetch())
483 {
484 for (std::size_t j = 0; j != s1.size(); ++j)
485 {
486 std::ostringstream ss;
487 ss << "Hello_" << i;
488 std::string const &x = ss.str();
489
490 // Note: CHAR fields are always padded with whitespaces
491 ss << " ";
492 CHECK(ss.str() == s1[j]);
493 CHECK(x == s2[j]);
494 ++i;
495 }
496 }
497 CHECK(i == rowsToTest);
498 }
499
500 sql << "drop table test6";
501 }
502
503 // blob test
504 TEST_CASE("Firebird blobs", "[firebird][blob]")
505 {
506 soci::session sql(backEnd, connectString);
507
508 try
509 {
510 sql << "drop table test7";
511 }
512 catch (std::runtime_error &)
513 {} // ignore if error
514
515 sql << "create table test7(id integer, img blob)";
516 sql.commit();
517
518 sql.begin();
519 {
520 // verify empty blob
521 blob b(sql);
522 indicator ind;
523
524 sql << "insert into test7(id, img) values(1,?)", use(b);
525 sql << "select img from test7 where id = 1", into(b, ind);
526
527 CHECK(ind == i_ok);
528 CHECK(b.get_len() == 0);
529
530 sql << "delete from test7";
531 }
532
533 {
534 // create a new blob
535 blob b(sql);
536
537 char str1[] = "Hello";
538 b.write(0, str1, strlen(str1));
539
540 char str2[20];
541 std::size_t i = b.read(3, str2, 2);
542 str2[i] = '\0';
543 CHECK(str2[0] == 'l');
544 CHECK(str2[1] == 'o');
545 CHECK(str2[2] == '\0');
546
547 char str3[] = ", Firebird!";
548 b.append(str3, strlen(str3));
549
550 sql << "insert into test7(id, img) values(1,?)", use(b);
551 }
552
553 {
554 // read & update blob
555 blob b(sql);
556
557 sql << "select img from test7 where id = 1", into(b);
558
559 std::vector<char> text(b.get_len());
560 b.read(0, &text[0], b.get_len());
561 CHECK(strncmp(&text[0], "Hello, Firebird!", b.get_len()) == 0);
562
563 char str1[] = "FIREBIRD";
564 b.write(7, str1, strlen(str1));
565
566 // after modification blob must be written to database
567 sql << "update test7 set img=? where id=1", use(b);
568 }
569
570 {
571 // read blob from database, modify and write to another record
572 blob b(sql);
573
574 sql << "select img from test7 where id = 1", into(b);
575
576 std::vector<char> text(b.get_len());
577 b.read(0, &text[0], b.get_len());
578
579 char str1[] = "HELLO";
580 b.write(0, str1, strlen(str1));
581
582 b.read(0, &text[0], b.get_len());
583 CHECK(strncmp(&text[0], "HELLO, FIREBIRD!", b.get_len()) == 0);
584
585 b.trim(5);
586 sql << "insert into test7(id, img) values(2,?)", use(b);
587 }
588
589 {
590 blob b(sql);
591 statement st = (sql.prepare << "select img from test7", into(b));
592
593 st.execute();
594
595 st.fetch();
596 std::vector<char> text(b.get_len());
597 b.read(0, &text[0], b.get_len());
598 CHECK(strncmp(&text[0], "Hello, FIREBIRD!", b.get_len()) == 0);
599
600 st.fetch();
601 text.resize(b.get_len());
602 b.read(0, &text[0], b.get_len());
603 CHECK(strncmp(&text[0], "HELLO", b.get_len()) == 0);
604 }
605
606 {
607 // delete blob
608 blob b(sql);
609 indicator ind=i_null;
610 sql << "update test7 set img=? where id = 1", use(b, ind);
611
612 sql << "select img from test7 where id = 2", into(b, ind);
613 CHECK(ind==i_ok);
614
615 sql << "select img from test7 where id = 1", into(b, ind);
616 CHECK(ind==i_null);
617 }
618
619 {
620 //create large blob
621 const int blobSize = 65536; //max segment size is 65535(unsigned short)
622 std::vector<char> data(blobSize);
623 blob b(sql);
624 b.write(0, data.data(), blobSize);
625 sql << "insert into test7(id, img) values(3,?)", use(b);
626
627 //now read blob back from database and make sure it has correct content and size
628 blob br(sql);
629 sql << "select img from test7 where id = 3", into(br);
630 std::vector<char> data2(br.get_len());
631 if(br.get_len()>0)
632 br.read(0, data2.data(), br.get_len());
633 CHECK(data == data2);
634 }
635
636 sql << "drop table test7";
637 }
638
639 // named parameters
640 TEST_CASE("Firebird named parameters", "[firebird][named-params]")
641 {
642 soci::session sql(backEnd, connectString);
643
644 try
645 {
646 sql << "drop table test8";
647 }
648 catch (std::runtime_error &)
649 {} // ignore if error
650
651 sql << "create table test8(id1 integer, id2 integer)";
652 sql.commit();
653
654 sql.begin();
655
656 int j = 13, k = 4, i, m;
657 sql << "insert into test8(id1, id2) values(:id1, :id2)",
658 use(k, "id2"), use(j, "id1");
659 sql << "select id1, id2 from test8", into(i), into(m);
660 CHECK(i == j);
661 CHECK(m == k);
662
663 sql << "delete from test8";
664
665 std::vector<int> in1(3), in2(3);
666 in1[0] = 3;
667 in1[1] = 2;
668 in1[2] = 1;
669 in2[0] = 4;
670 in2[1] = 5;
671 in2[2] = 6;
672
673 {
674 statement st = (sql.prepare <<
675 "insert into test8(id1, id2) values(:id1, :id2)",
676 use(k, "id2"), use(j, "id1"));
677
678 std::size_t s = in1.size();
679 for (std::size_t x = 0; x < s; ++x)
680 {
681 j = in1[x];
682 k = in2[x];
683 st.execute();
684 }
685 }
686
687 {
688 statement st = (
689 sql.prepare << "select id1, id2 from test8", into(i), into(m));
690 st.execute();
691
692 std::size_t x(0);
693 while (st.fetch())
694 {
695 CHECK(i == in1[x]);
696 CHECK(m == in2[x]);
697 ++x;
698 }
699 }
700
701 sql << "delete from test8";
702
703 // test vectors
704 sql << "insert into test8(id1, id2) values(:id1, :id2)",
705 use(in1, "id1"), use(in2, "id2");
706
707 std::vector<int> out1(3), out2(3);
708
709 sql << "select id1, id2 from test8", into(out1), into(out2);
710 std::size_t s = out1.size();
711 CHECK(s == 3);
712
713 for (std::size_t x = 0; x<s; ++x)
714 {
715 CHECK(out1[x] == in1[x]);
716 CHECK(out2[x] == in2[x]);
717 }
718
719 sql << "drop table test8";
720 }
721
722 // Dynamic binding to row objects
723 TEST_CASE("Firebird dynamic binding", "[firebird][dynamic]")
724 {
725 soci::session sql(backEnd, connectString);
726
727 try
728 {
729 sql << "drop table test9";
730 }
731 catch (std::runtime_error &)
732 {} // ignore if error
733
734 sql << "create table test9(id integer, msg varchar(20), ntest numeric(10,2))";
735 sql.commit();
736
737 sql.begin();
738
739 {
740 row r;
741 sql << "select * from test9", into(r);
742 CHECK(sql.got_data() == false);
743 }
744
745 std::string msg("Hello");
746 int i(1);
747 double d(3.14);
748 indicator ind(i_ok);
749
750 {
751 statement st((sql.prepare << "insert into test9(id, msg, ntest) "
752 << "values(:id,:msg,:ntest)",
753 use(i, "id"), use(msg, "msg"), use(d, ind, "ntest")));
754
755 st.execute(1);
756
757 i = 2;
758 msg = "Firebird";
759 ind = i_null;
760 st.execute(1);
761 }
762
763 row r;
764 statement st = (sql.prepare <<
765 "select * from test9", into(r));
766 st.execute(1);
767
768 CHECK(r.size() == 3);
769
770 // get properties by position
771 CHECK(r.get_properties(0).get_name() == "ID");
772 CHECK(r.get_properties(1).get_name() == "MSG");
773 CHECK(r.get_properties(2).get_name() == "NTEST");
774
775 CHECK(r.get_properties(0).get_data_type() == dt_integer);
776 CHECK(r.get_properties(1).get_data_type() == dt_string);
777 CHECK(r.get_properties(2).get_data_type() == dt_double);
778
779 // get properties by name
780 CHECK(r.get_properties("ID").get_name() == "ID");
781 CHECK(r.get_properties("MSG").get_name() == "MSG");
782 CHECK(r.get_properties("NTEST").get_name() == "NTEST");
783
784 CHECK(r.get_properties("ID").get_data_type() == dt_integer);
785 CHECK(r.get_properties("MSG").get_data_type() == dt_string);
786 CHECK(r.get_properties("NTEST").get_data_type() == dt_double);
787
788 // get values by position
789 CHECK(r.get<int>(0) == 1);
790 CHECK(r.get<std::string>(1) == "Hello");
791 CHECK(tests::are_doubles_exactly_equal(r.get<double>(2), d));
792
793 // get values by name
794 CHECK(r.get<int>("ID") == 1);
795 CHECK(r.get<std::string>("MSG") == "Hello");
796 CHECK(tests::are_doubles_exactly_equal(r.get<double>("NTEST"), d));
797
798 st.fetch();
799 CHECK(r.get<int>(0) == 2);
800 CHECK(r.get<std::string>("MSG") == "Firebird");
801 CHECK(r.get_indicator(2) == i_null);
802
803 // verify default values
804 CHECK(tests::are_doubles_exactly_equal(r.get<double>("NTEST", 2), 2));
805
806 CHECK_THROWS_AS(r.get<double>("NTEST"), soci_error&);
807
808 // verify exception thrown on invalid get<>
809 CHECK_THROWS_AS(r.get<std::string>(0), std::bad_cast&);
810
811 sql << "drop table test9";
812 }
813
814 // stored procedures
815 TEST_CASE("Firebird stored procedures", "[firebird][procedure]")
816 {
817 soci::session sql(backEnd, connectString);
818
819 try
820 {
821 sql << "drop procedure sp_test10";
822 }
823 catch (std::runtime_error &)
824 {} // ignore if error
825
826 try
827 {
828 sql << "drop procedure sp_test10a";
829 }
830 catch (std::runtime_error &)
831 {} // ignore if error
832
833 try
834 {
835 sql << "drop table test10";
836 }
837 catch (std::runtime_error &)
838 {} // ignore if error
839
840 sql << "create table test10(id integer, id2 integer)";
841
842 sql << "create procedure sp_test10\n"
843 << "returns (rid integer, rid2 integer)\n"
844 << "as begin\n"
845 << "for select id, id2 from test10 into rid, rid2 do begin\n"
846 << "suspend;\n"
847 << "end\n"
848 << "end;\n";
849
850 sql << "create procedure sp_test10a (pid integer, pid2 integer)\n"
851 << "as begin\n"
852 << "insert into test10(id, id2) values (:pid, :pid2);\n"
853 << "end;\n";
854
855 sql.commit();
856
857 sql.begin();
858
859 row r;
860 int p1 = 3, p2 = 4;
861
862 // calling procedures that do not return values requires
863 // 'execute procedure ...' statement
864 sql << "execute procedure sp_test10a ?, ?", use(p1), use(p2);
865
866 // calling procedures that return values requires
867 // 'select ... from ...' statement
868 sql << "select * from sp_test10", into(r);
869
870 CHECK(r.get<int>(0) == p1);
871 CHECK(r.get<int>(1) == p2);
872
873 sql << "delete from test10";
874
875 p1 = 5;
876 p2 = 6;
877 {
878 procedure proc = (
879 sql.prepare << "sp_test10a :p1, :p2",
880 use(p2, "p2"), use(p1, "p1"));
881 proc.execute(1);
882 }
883
884 {
885 row rw;
886 procedure proc = (sql.prepare << "sp_test10", into(rw));
887 proc.execute(1);
888
889 CHECK(rw.get<int>(0) == p1);
890 CHECK(rw.get<int>(1) == p2);
891 }
892
893 sql << "delete from test10";
894
895 // test vectors
896 std::vector<int> in1(3), in2(3);
897 in1[0] = 3;
898 in1[1] = 2;
899 in1[2] = 1;
900 in2[0] = 4;
901 in2[1] = 5;
902 in2[2] = 6;
903
904 {
905 procedure proc = (
906 sql.prepare << "sp_test10a :p1, :p2",
907 use(in2, "p2"), use(in1, "p1"));
908 proc.execute(1);
909 }
910
911 {
912 row rw;
913 procedure proc = (sql.prepare << "sp_test10", into(rw));
914
915 proc.execute(1);
916 CHECK(rw.get<int>(0) == in1[0]);
917 CHECK(rw.get<int>(1) == in2[0]);
918 proc.fetch();
919 CHECK(rw.get<int>(0) == in1[1]);
920 CHECK(rw.get<int>(1) == in2[1]);
921 proc.fetch();
922 CHECK(rw.get<int>(0) == in1[2]);
923 CHECK(rw.get<int>(1) == in2[2]);
924 CHECK(proc.fetch() == false);
925 }
926
927 {
928 std::vector<int> out1(3), out2(3);
929 procedure proc = (sql.prepare << "sp_test10", into(out1), into(out2));
930 proc.execute(1);
931
932 std::size_t s = out1.size();
933 CHECK(s == 3);
934
935 for (std::size_t x = 0; x < s; ++x)
936 {
937 CHECK(out1[x] == in1[x]);
938 CHECK(out2[x] == in2[x]);
939 }
940 }
941
942 sql.rollback();
943
944 sql.begin();
945 sql << "drop procedure sp_test10";
946 sql << "drop procedure sp_test10a";
947 sql << "drop table test10";
948 }
949
950 // direct access to Firebird using handles exposed by
951 // soci::FirebirdStatmentBackend
952 namespace soci
953 {
954 enum eRowCountType
955 {
956 eRowsSelected = isc_info_req_select_count,
957 eRowsInserted = isc_info_req_insert_count,
958 eRowsUpdated = isc_info_req_update_count,
959 eRowsDeleted = isc_info_req_delete_count
960 };
961
962 // Returns number of rows afected by last statement
963 // or -1 if there is no such counter available.
getRowCount(soci::statement & statement,eRowCountType type)964 long getRowCount(soci::statement & statement, eRowCountType type)
965 {
966 ISC_STATUS stat[20];
967 char cnt_req[2], cnt_info[128];
968
969 cnt_req[0]=isc_info_sql_records;
970 cnt_req[1]=isc_info_end;
971
972 firebird_statement_backend* statementBackEnd
973 = static_cast<firebird_statement_backend*>(statement.get_backend());
974
975 // Note: This is very poorly documented function.
976 // It can extract number of rows returned by select statement,
977 // but it appears that this is only number of rows prefetched by
978 // client library, not total number of selected rows.
979 if (isc_dsql_sql_info(stat, &statementBackEnd->stmtp_, sizeof(cnt_req),
980 cnt_req, sizeof(cnt_info), cnt_info))
981 {
982 soci::details::firebird::throw_iscerror(stat);
983 }
984
985 long count = -1;
986 char type_ = static_cast<char>(type);
987 for (char *ptr = cnt_info + 3; *ptr != isc_info_end;)
988 {
989 char count_type = *ptr++;
990 int m = isc_vax_integer(ptr, 2);
991 ptr += 2;
992 count = isc_vax_integer(ptr, static_cast<short>(m));
993
994 if (count_type == type_)
995 {
996 // this is requested number
997 break;
998 }
999 ptr += m;
1000 }
1001
1002 return count;
1003 }
1004
1005 } // namespace soci
1006
1007 TEST_CASE("Firebird direct API use", "[firebird][native]")
1008 {
1009 soci::session sql(backEnd, connectString);
1010
1011 try
1012 {
1013 sql << "drop table test11";
1014 }
1015 catch (std::runtime_error &)
1016 {} // ignore if error
1017
1018 sql << "create table test11(id integer)";
1019 sql.commit();
1020
1021 sql.begin();
1022
1023 {
1024 std::vector<int> in(3);
1025 in[0] = 3;
1026 in[1] = 2;
1027 in[2] = 1;
1028
1029 statement st = (sql.prepare << "insert into test11(id) values(?)",
1030 use(in));
1031 st.execute(1);
1032
1033 // Note: Firebird backend inserts every row with separate insert
1034 // statement to achieve the effect of inserting vectors of values.
1035 // Since getRowCount() returns number of rows affected by the *last*
1036 // statement, it will return 1 here.
1037 CHECK(getRowCount(st, eRowsInserted) == 1);
1038 }
1039
1040 {
1041 int i = 5;
1042 statement st = (sql.prepare << "update test11 set id = ? where id<3",
1043 use(i));
1044 st.execute(1);
1045 CHECK(getRowCount(st, eRowsUpdated) == 2);
1046
1047 // verify that no rows were deleted
1048 CHECK(getRowCount(st, eRowsDeleted) == 0);
1049 }
1050
1051 {
1052 std::vector<int> out(3);
1053 statement st = (sql.prepare << "select id from test11", into(out));
1054 st.execute(1);
1055
1056 CHECK(getRowCount(st, eRowsSelected) == 3);
1057 }
1058
1059 {
1060 statement st = (sql.prepare << "delete from test11 where id=10");
1061 st.execute(1);
1062 CHECK(getRowCount(st, eRowsDeleted) == 0);
1063 }
1064
1065 {
1066 statement st = (sql.prepare << "delete from test11");
1067 st.execute(1);
1068 CHECK(getRowCount(st, eRowsDeleted) == 3);
1069 }
1070
1071 sql << "drop table test11";
1072 }
1073
1074 TEST_CASE("Firebird string coercions", "[firebird][string]")
1075 {
1076 soci::session sql(backEnd, connectString);
1077
1078 try
1079 {
1080 sql << "drop table test12";
1081 }
1082 catch (std::runtime_error &)
1083 {} // ignore if error
1084
1085 sql << "create table test12(a decimal(10,3), b timestamp, c date, d time)";
1086 sql.commit();
1087 sql.begin();
1088
1089 // Check if passing input parameters as strings works
1090 // for different column types.
1091 {
1092 std::string a = "-3.14150", b = "2013-02-28 23:36:01",
1093 c = "2013-02-28", d = "23:36:01";
1094 statement st = (sql.prepare <<
1095 "insert into test12(a, b, c, d) values (?, ?, ?, ?)",
1096 use(a), use(b), use(c), use(d));
1097 st.execute(1);
1098 CHECK(getRowCount(st, eRowsInserted) == 1);
1099 }
1100
1101 {
1102 double a;
1103 std::tm b = std::tm(), c = std::tm(), d = std::tm();
1104 sql << "select a, b, c, d from test12",
1105 into(a), into(b), into(c), into(d);
1106 CHECK(std::fabs(a - (-3.141)) < 0.000001);
1107 CHECK(b.tm_year == 2013 - 1900);
1108 CHECK(b.tm_mon == 2 - 1);
1109 CHECK(b.tm_mday == 28);
1110 CHECK(b.tm_hour == 23);
1111 CHECK(b.tm_min == 36);
1112 CHECK(b.tm_sec == 1);
1113 CHECK(c.tm_year == 2013 - 1900);
1114 CHECK(c.tm_mon == 2 - 1);
1115 CHECK(c.tm_mday == 28);
1116 CHECK(c.tm_hour == 0);
1117 CHECK(c.tm_min == 0);
1118 CHECK(c.tm_sec == 0);
1119 CHECK(d.tm_hour == 23);
1120 CHECK(d.tm_min == 36);
1121 CHECK(d.tm_sec == 1);
1122 }
1123
1124 sql << "drop table test12";
1125 }
1126
1127 // Dynamic binding to row objects: decimals_as_strings
1128 TEST_CASE("Firebird decimals as strings", "[firebird][decimal][string]")
1129 {
1130 using namespace soci::details::firebird;
1131
1132 int a = -12345678;
1133 CHECK(format_decimal<int>(&a, 1) == "-123456780");
1134 CHECK(format_decimal<int>(&a, 0) == "-12345678");
1135 CHECK(format_decimal<int>(&a, -3) == "-12345.678");
1136 CHECK(format_decimal<int>(&a, -8) == "-0.12345678");
1137 CHECK(format_decimal<int>(&a, -9) == "-0.012345678");
1138
1139 a = 12345678;
1140 CHECK(format_decimal<int>(&a, 1) == "123456780");
1141 CHECK(format_decimal<int>(&a, 0) == "12345678");
1142 CHECK(format_decimal<int>(&a, -3) == "12345.678");
1143 CHECK(format_decimal<int>(&a, -8) == "0.12345678");
1144 CHECK(format_decimal<int>(&a, -9) == "0.012345678");
1145
1146 soci::session sql(backEnd, connectString + " decimals_as_strings=1");
1147
1148 try
1149 {
1150 sql << "drop table test13";
1151 }
1152 catch (std::runtime_error &)
1153 {} // ignore if error
1154
1155 sql << "create table test13(ntest1 decimal(10,2), "
1156 << "ntest2 decimal(4,4), ntest3 decimal(3,1))";
1157 sql.commit();
1158
1159 sql.begin();
1160
1161 {
1162 row r;
1163 sql << "select * from test13", into(r);
1164 CHECK(sql.got_data() == false);
1165 }
1166
1167 std::string d_str0("+03.140"), d_str1("3.14"),
1168 d_str2("3.1400"), d_str3("3.1");
1169 indicator ind(i_ok);
1170
1171 {
1172 statement st((sql.prepare <<
1173 "insert into test13(ntest1, ntest2, ntest3) "
1174 "values(:ntest1, :ntest2, :ntest3)",
1175 use(d_str0, ind, "ntest1"), use(d_str0, "ntest2"),
1176 use(d_str0, "ntest3")));
1177
1178 st.execute(1);
1179
1180 ind = i_null;
1181 st.execute(1);
1182 }
1183
1184 row r;
1185 statement st = (sql.prepare << "select * from test13", into(r));
1186 st.execute(1);
1187
1188 CHECK(r.size() == 3);
1189
1190 // get properties by position
1191 CHECK(r.get_properties(0).get_name() == "NTEST1");
1192 CHECK(r.get_properties(0).get_data_type() == dt_string);
1193 CHECK(r.get_properties(1).get_name() == "NTEST2");
1194 CHECK(r.get_properties(1).get_data_type() == dt_string);
1195 CHECK(r.get_properties(2).get_name() == "NTEST3");
1196 CHECK(r.get_properties(2).get_data_type() == dt_string);
1197
1198 // get properties by name
1199 CHECK(r.get_properties("NTEST1").get_name() == "NTEST1");
1200 CHECK(r.get_properties("NTEST1").get_data_type() == dt_string);
1201 CHECK(r.get_properties("NTEST2").get_name() == "NTEST2");
1202 CHECK(r.get_properties("NTEST2").get_data_type() == dt_string);
1203 CHECK(r.get_properties("NTEST3").get_name() == "NTEST3");
1204 CHECK(r.get_properties("NTEST3").get_data_type() == dt_string);
1205
1206 // get values by position
1207 CHECK(r.get<std::string>(0) == d_str1);
1208 CHECK(r.get<std::string>(1) == d_str2);
1209 CHECK(r.get<std::string>(2) == d_str3);
1210
1211 // get values by name
1212 CHECK(r.get<std::string>("NTEST1") == d_str1);
1213 CHECK(r.get<std::string>("NTEST2") == d_str2);
1214 CHECK(r.get<std::string>("NTEST3") == d_str3);
1215
1216 st.fetch();
1217 CHECK(r.get_indicator(0) == i_null);
1218 CHECK(r.get_indicator(1) == i_ok);
1219 CHECK(r.get_indicator(2) == i_ok);
1220
1221 sql << "drop table test13";
1222 }
1223
1224 //
1225 // Support for soci Common Tests
1226 //
1227
1228 struct TableCreator1 : public tests::table_creator_base
1229 {
TableCreator1TableCreator11230 TableCreator1(soci::session & sql)
1231 : tests::table_creator_base(sql)
1232 {
1233 sql << "create table soci_test(id integer, val integer, c char, "
1234 "str varchar(20), sh smallint, ul bigint, d double precision, "
1235 "num76 numeric(7,6), "
1236 "tm timestamp, i1 integer, i2 integer, i3 integer, name varchar(20))";
1237 sql.commit();
1238 sql.begin();
1239 }
1240 };
1241
1242 struct TableCreator2 : public tests::table_creator_base
1243 {
TableCreator2TableCreator21244 TableCreator2(soci::session & sql)
1245 : tests::table_creator_base(sql)
1246 {
1247 sql << "create table soci_test(num_float float, num_int integer, "
1248 "name varchar(20), sometime timestamp, chr char)";
1249 sql.commit();
1250 sql.begin();
1251 }
1252 };
1253
1254 struct TableCreator3 : public tests::table_creator_base
1255 {
TableCreator3TableCreator31256 TableCreator3(soci::session & sql)
1257 : tests::table_creator_base(sql)
1258 {
1259 sql << "create table soci_test(name varchar(100) not null, "
1260 "phone varchar(15))";
1261 sql.commit();
1262 sql.begin();
1263 }
1264 };
1265
1266 struct TableCreator4 : public tests::table_creator_base
1267 {
TableCreator4TableCreator41268 TableCreator4(soci::session & sql)
1269 : tests::table_creator_base(sql)
1270 {
1271 sql << "create table soci_test(val integer)";
1272 sql.commit();
1273 sql.begin();
1274 }
1275 };
1276
1277 struct TableCreatorCLOB : public tests::table_creator_base
1278 {
TableCreatorCLOBTableCreatorCLOB1279 TableCreatorCLOB(soci::session & sql)
1280 : tests::table_creator_base(sql)
1281 {
1282 sql << "create table soci_test(id integer, s blob sub_type text)";
1283 sql.commit();
1284 sql.begin();
1285 }
1286 };
1287
1288 struct TableCreatorXML : public tests::table_creator_base
1289 {
TableCreatorXMLTableCreatorXML1290 TableCreatorXML(soci::session & sql)
1291 : tests::table_creator_base(sql)
1292 {
1293 sql << "create table soci_test(id integer, x blob sub_type text)";
1294 sql.commit();
1295 sql.begin();
1296 }
1297 };
1298
1299 class test_context : public tests::test_context_base
1300 {
1301 public:
test_context(backend_factory const & backEnd,std::string const & connectString)1302 test_context(backend_factory const &backEnd,
1303 std::string const &connectString)
1304 : test_context_base(backEnd, connectString)
1305 {}
1306
table_creator_1(soci::session & s) const1307 tests::table_creator_base* table_creator_1(soci::session& s) const SOCI_OVERRIDE
1308 {
1309 return new TableCreator1(s);
1310 }
1311
table_creator_2(soci::session & s) const1312 tests::table_creator_base* table_creator_2(soci::session& s) const SOCI_OVERRIDE
1313 {
1314 return new TableCreator2(s);
1315 }
1316
table_creator_3(soci::session & s) const1317 tests::table_creator_base* table_creator_3(soci::session& s) const SOCI_OVERRIDE
1318 {
1319 return new TableCreator3(s);
1320 }
1321
table_creator_4(soci::session & s) const1322 tests::table_creator_base* table_creator_4(soci::session& s) const SOCI_OVERRIDE
1323 {
1324 return new TableCreator4(s);
1325 }
1326
table_creator_clob(soci::session & s) const1327 tests::table_creator_base* table_creator_clob(soci::session& s) const SOCI_OVERRIDE
1328 {
1329 return new TableCreatorCLOB(s);
1330 }
1331
table_creator_xml(soci::session & s) const1332 tests::table_creator_base* table_creator_xml(soci::session& s) const SOCI_OVERRIDE
1333 {
1334 return new TableCreatorXML(s);
1335 }
1336
to_date_time(std::string const & datdt_string) const1337 std::string to_date_time(std::string const &datdt_string) const SOCI_OVERRIDE
1338 {
1339 return "'" + datdt_string + "'";
1340 }
1341
on_after_ddl(soci::session & sql) const1342 void on_after_ddl(soci::session& sql) const SOCI_OVERRIDE
1343 {
1344 sql.commit();
1345 }
1346
sql_length(std::string const & s) const1347 std::string sql_length(std::string const& s) const SOCI_OVERRIDE
1348 {
1349 return "char_length(" + s + ")";
1350 }
1351 };
1352
1353
main(int argc,char ** argv)1354 int main(int argc, char** argv)
1355 {
1356
1357 #ifdef _MSC_VER
1358 // Redirect errors, unrecoverable problems, and assert() failures to STDERR,
1359 // instead of debug message window.
1360 // This hack is required to run assert()-driven tests by Buildbot.
1361 // NOTE: Comment this 2 lines for debugging with Visual C++ debugger to catch assertions inside.
1362 _CrtSetReportMode(_CRT_ERROR, _CRTDBG_MODE_FILE);
1363 _CrtSetReportFile(_CRT_ERROR, _CRTDBG_FILE_STDERR);
1364 #endif //_MSC_VER
1365
1366 if (argc >= 2)
1367 {
1368 connectString = argv[1];
1369
1370 // Replace the connect string with the process name to ensure that
1371 // CATCH uses the correct name in its messages.
1372 argv[1] = argv[0];
1373
1374 argc--;
1375 argv++;
1376 }
1377 else
1378 {
1379 std::cout << "usage: " << argv[0]
1380 << " connectstring [test-arguments...]\n"
1381 << "example: " << argv[0]
1382 << " \"service=/usr/local/firebird/db/test.fdb user=SYSDBA password=masterkey\"\n";
1383 return EXIT_FAILURE;
1384 }
1385
1386 test_context tc(backEnd, connectString);
1387
1388 return Catch::Session().run(argc, argv);
1389 }
1390