1//
2// SQLExecutor.cpp
3//
4// Copyright (c) 2008, Applied Informatics Software Engineering GmbH.
5// and Contributors.
6//
7// SPDX-License-Identifier: BSL-1.0
8//
9
10#include <iostream>
11#include <limits>
12
13
14#include "Poco/CppUnit/TestCase.h"
15#include "SQLExecutor.h"
16#include "Poco/NumberParser.h"
17#include "Poco/String.h"
18#include "Poco/Format.h"
19#include "Poco/Tuple.h"
20#include "Poco/DateTime.h"
21#include "Poco/Any.h"
22#include "Poco/Exception.h"
23#include "Poco/SQL/LOB.h"
24#include "Poco/SQL/Date.h"
25#include "Poco/SQL/Time.h"
26#include "Poco/SQL/StatementImpl.h"
27#include "Poco/SQL/RecordSet.h"
28#include "Poco/SQL/Transaction.h"
29#include "Poco/SQL/MySQL/Connector.h"
30#include "Poco/SQL/MySQL/MySQLException.h"
31
32#include <tuple>
33
34#if POCO_MSVS_VERSION == 2015
35#define HAVE_STRUCT_TIMESPEC
36#endif
37
38#if (POCO_OS == POCO_OS_CYGWIN)
39typedef unsigned short ushort; /* System V compatibility */
40typedef unsigned int uint; /* System V compatibility */
41typedef unsigned long ulong; /* System V compatibility */
42#endif
43
44#include <mysql.h>
45
46using namespace Poco::SQL;
47using namespace Poco::SQL::Keywords;
48using Poco::SQL::MySQL::ConnectionException;
49using Poco::SQL::MySQL::StatementException;
50using Poco::format;
51using Poco::Tuple;
52using Poco::DateTime;
53using Poco::NumberParser;
54using Poco::Any;
55using Poco::AnyCast;
56using Poco::DynamicAny;
57using Poco::NotFoundException;
58using Poco::InvalidAccessException;
59using Poco::BadCastException;
60using Poco::RangeException;
61
62
63struct Person
64{
65 std::string lastName;
66 std::string firstName;
67 std::string address;
68 int age;
69 Person(){age = 0;}
70 Person(const std::string& ln, const std::string& fn, const std::string& adr, int a):lastName(ln), firstName(fn), address(adr), age(a)
71 {
72 }
73 bool operator==(const Person& other) const
74 {
75 return lastName == other.lastName && firstName == other.firstName && address == other.address && age == other.age;
76 }
77
78 bool operator < (const Person& p) const
79 {
80 if (age < p.age)
81 return true;
82 if (lastName < p.lastName)
83 return true;
84 if (firstName < p.firstName)
85 return true;
86 return (address < p.address);
87 }
88
89 const std::string& operator () () const
90 /// This method is required so we can extract data to a map!
91 {
92 // we choose the lastName as examplary key
93 return lastName;
94 }
95};
96
97
98namespace Poco {
99namespace SQL {
100
101
102template <>
103class TypeHandler<Person>
104{
105public:
106 static void bind(std::size_t pos, const Person& obj, AbstractBinder::Ptr pBinder, AbstractBinder::Direction dir)
107 {
108 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
109 poco_assert_dbg (!pBinder.isNull());
110 pBinder->bind(pos++, obj.lastName, dir);
111 pBinder->bind(pos++, obj.firstName, dir);
112 pBinder->bind(pos++, obj.address, dir);
113 pBinder->bind(pos++, obj.age, dir);
114 }
115
116 static void prepare(std::size_t pos, const Person& obj, AbstractPreparator::Ptr pPrepare)
117 {
118 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
119 poco_assert_dbg (!pPrepare.isNull());
120 pPrepare->prepare(pos++, obj.lastName);
121 pPrepare->prepare(pos++, obj.firstName);
122 pPrepare->prepare(pos++, obj.address);
123 pPrepare->prepare(pos++, obj.age);
124 }
125
126 static std::size_t size()
127 {
128 return 4;
129 }
130
131 static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor::Ptr pExt)
132 {
133 poco_assert_dbg (!pExt.isNull());
134 if (!pExt->extract(pos++, obj.lastName))
135 obj.lastName = defVal.lastName;
136 if (!pExt->extract(pos++, obj.firstName))
137 obj.firstName = defVal.firstName;
138 if (!pExt->extract(pos++, obj.address))
139 obj.address = defVal.address;
140 if (!pExt->extract(pos++, obj.age))
141 obj.age = defVal.age;
142 }
143
144private:
145 TypeHandler();
146 ~TypeHandler();
147 TypeHandler(const TypeHandler&);
148 TypeHandler& operator=(const TypeHandler&);
149};
150
151
152} } // namespace Poco::SQL
153
154
155SQLExecutor::SQLExecutor(const std::string& name, Poco::SQL::Session* pSession):
156 CppUnit::TestCase(name),
157 _pSession(pSession)
158{
159}
160
161
162SQLExecutor::~SQLExecutor()
163{
164}
165
166
167void SQLExecutor::bareboneMySQLTest(const std::string& host, const std::string& user, const std::string& pwd, const std::string& db, const std::string& port, const char* tableCreateString)
168{
169 MYSQL* hsession = mysql_init(0);
170 poco_assert (hsession != 0);
171
172 MYSQL* tmp = mysql_real_connect(hsession, host.c_str(), user.c_str(), pwd.c_str(), db.c_str(), NumberParser::parse(port), 0, 0);
173 poco_assert (tmp == hsession);
174
175 MYSQL_STMT* hstmt = mysql_stmt_init(hsession);
176 poco_assert (hstmt != 0);
177
178 std::string sql = "DROP TABLE Test";
179 mysql_real_query(hsession, sql.c_str(), static_cast<unsigned long>(sql.length()));
180
181 sql = tableCreateString;
182 int rc = mysql_stmt_prepare(hstmt, sql.c_str(), static_cast<unsigned long>(sql.length()));
183 poco_assert (rc == 0);
184
185 rc = mysql_stmt_execute(hstmt);
186 poco_assert (rc == 0);
187
188 sql = "INSERT INTO Test VALUES (?,?,?,?,?)";
189 rc = mysql_stmt_prepare(hstmt, sql.c_str(), static_cast<unsigned long>(sql.length()));
190 poco_assert (rc == 0);
191
192 std::string str[3] = { "111", "222", "333" };
193 int fourth = 4;
194 float fifth = 1.5;
195
196 MYSQL_BIND bind_param[5] = {{0}};
197
198 bind_param[0].buffer = const_cast<char*>(str[0].c_str());
199 bind_param[0].buffer_length = static_cast<unsigned long>(str[0].length());
200 bind_param[0].buffer_type = MYSQL_TYPE_STRING;
201
202 bind_param[1].buffer = const_cast<char*>(str[1].c_str());
203 bind_param[1].buffer_length = static_cast<unsigned long>(str[1].length());
204 bind_param[1].buffer_type = MYSQL_TYPE_STRING;
205
206 bind_param[2].buffer = const_cast<char*>(str[2].c_str());
207 bind_param[2].buffer_length = static_cast<unsigned long>(str[2].length());
208 bind_param[2].buffer_type = MYSQL_TYPE_STRING;
209
210 bind_param[3].buffer = &fourth;
211 bind_param[3].buffer_type = MYSQL_TYPE_LONG;
212
213 bind_param[4].buffer = &fifth;
214 bind_param[4].buffer_type = MYSQL_TYPE_FLOAT;
215
216 rc = mysql_stmt_bind_param(hstmt, bind_param);
217 poco_assert (rc == 0);
218
219 rc = mysql_stmt_execute(hstmt);
220 poco_assert (rc == 0);
221
222 sql = "SELECT * FROM Test";
223 rc = mysql_stmt_prepare(hstmt, sql.c_str(), static_cast<unsigned long>(sql.length()));
224 poco_assert (rc == 0);
225
226 char chr[3][5] = {{ 0 }};
227 unsigned long lengths[5] = { 0 };
228 fourth = 0;
229 fifth = 0.0f;
230
231 MYSQL_BIND bind_result[5] = {{0}};
232
233 bind_result[0].buffer = chr[0];
234 bind_result[0].buffer_length = sizeof(chr[0]);
235 bind_result[0].buffer_type = MYSQL_TYPE_STRING;
236 bind_result[0].length = &lengths[0];
237
238 bind_result[1].buffer = chr[1];
239 bind_result[1].buffer_length = sizeof(chr[1]);
240 bind_result[1].buffer_type = MYSQL_TYPE_STRING;
241 bind_result[1].length = &lengths[1];
242
243 bind_result[2].buffer = chr[2];
244 bind_result[2].buffer_length = sizeof(chr[2]);
245 bind_result[2].buffer_type = MYSQL_TYPE_STRING;
246 bind_result[2].length = &lengths[2];
247
248 bind_result[3].buffer = &fourth;
249 bind_result[3].buffer_type = MYSQL_TYPE_LONG;
250 bind_result[3].length = &lengths[3];
251
252 bind_result[4].buffer = &fifth;
253 bind_result[4].buffer_type = MYSQL_TYPE_FLOAT;
254 bind_result[4].length = &lengths[4];
255
256 rc = mysql_stmt_bind_result(hstmt, bind_result);
257 poco_assert (rc == 0);
258
259 rc = mysql_stmt_execute(hstmt);
260 poco_assert (rc == 0);
261 rc = mysql_stmt_fetch(hstmt);
262 poco_assert (rc == 0);
263
264 poco_assert (0 == std::strncmp("111", chr[0], 3));
265 poco_assert (0 == std::strncmp("222", chr[1], 3));
266 poco_assert (0 == std::strncmp("333", chr[2], 3));
267 poco_assert (4 == fourth);
268 poco_assert (1.5 == fifth);
269
270 rc = mysql_stmt_close(hstmt);
271 poco_assert (rc == 0);
272
273 sql = "DROP TABLE Test";
274 rc = mysql_real_query(hsession, sql.c_str(), static_cast<unsigned long>(sql.length()));
275 poco_assert (rc == 0);
276
277 mysql_close(hsession);
278}
279
280
281void SQLExecutor::simpleAccess()
282{
283 std::string funct = "simpleAccess()";
284 std::string lastName = "lastName";
285 std::string firstName("firstName");
286 std::string address("Address");
287 int age = 133132;
288 int count = 0;
289 std::string result;
290
291 count = 0;
292 try
293 {
294 Statement stmt(*_pSession);
295 stmt << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(age);//, now;
296 stmt.execute();
297 }
298 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
299 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
300
301 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
302 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
303 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
304
305 poco_assert (count == 1);
306
307 try { *_pSession << "SELECT LastName FROM Person", into(result), now; }
308 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
309 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
310 poco_assert (lastName == result);
311
312 try { *_pSession << "SELECT Age FROM Person", into(count), now; }
313 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
314 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
315 poco_assert (count == age);
316}
317
318
319void SQLExecutor::complexType()
320{
321 std::string funct = "complexType()";
322 Person p1("LN1", "FN1", "ADDR1", 1);
323 Person p2("LN2", "FN2", "ADDR2", 2);
324
325 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(p1), now; }
326 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
327 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
328
329 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(p2), now; }
330 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
331 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
332
333 int count = 0;
334 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
335 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
336 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
337 poco_assert (count == 2);
338
339 Person c1;
340 Person c2;
341 try { *_pSession << "SELECT * FROM Person WHERE LastName = 'LN1'", into(c1), now; }
342 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
343 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
344 poco_assert (c1 == p1);
345}
346
347
348void SQLExecutor::simpleAccessVector()
349{
350 std::string funct = "simpleAccessVector()";
351 std::vector<std::string> lastNames;
352 std::vector<std::string> firstNames;
353 std::vector<std::string> addresses;
354 std::vector<int> ages;
355 std::string tableName("Person");
356 lastNames.push_back("LN1");
357 lastNames.push_back("LN2");
358 firstNames.push_back("FN1");
359 firstNames.push_back("FN2");
360 addresses.push_back("ADDR1");
361 addresses.push_back("ADDR2");
362 ages.push_back(1);
363 ages.push_back(2);
364 int count = 0;
365 std::string result;
366
367 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
368 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
369 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
370
371 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
372 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
373 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
374 poco_assert (count == 2);
375
376 std::vector<std::string> lastNamesR;
377 std::vector<std::string> firstNamesR;
378 std::vector<std::string> addressesR;
379 std::vector<int> agesR;
380 try { *_pSession << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
381 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
382 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
383 poco_assert (ages == agesR);
384 poco_assert (lastNames == lastNamesR);
385 poco_assert (firstNames == firstNamesR);
386 poco_assert (addresses == addressesR);
387}
388
389
390void SQLExecutor::complexTypeVector()
391{
392 std::string funct = "complexTypeVector()";
393 std::vector<Person> people;
394 people.push_back(Person("LN1", "FN1", "ADDR1", 1));
395 people.push_back(Person("LN2", "FN2", "ADDR2", 2));
396
397 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
398 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
399 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
400
401 int count = 0;
402 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
403 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
404 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
405 poco_assert (count == 2);
406
407 std::vector<Person> result;
408 try { *_pSession << "SELECT * FROM Person", into(result), now; }
409 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
410 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
411 poco_assert (result == people);
412}
413
414
415void SQLExecutor::insertVector()
416{
417 std::string funct = "insertVector()";
418 std::vector<std::string> str;
419 str.push_back("s1");
420 str.push_back("s2");
421 str.push_back("s3");
422 str.push_back("s3");
423 int count = 100;
424
425 {
426 Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(str)));
427 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
428 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
429 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
430 poco_assert (count == 0);
431
432 try { stmt.execute(); }
433 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
434
435 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
436 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
437 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
438 poco_assert (count == 4);
439 }
440 count = 0;
441 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
442 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
443 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
444 poco_assert (count == 4);
445}
446
447
448void SQLExecutor::insertEmptyVector()
449{
450 std::string funct = "insertEmptyVector()";
451 std::vector<std::string> str;
452
453 try
454 {
455 *_pSession << "INSERT INTO Strings VALUES (?)", use(str), now;
456 fail("empty collections should not work");
457 }
458 catch (Poco::Exception&)
459 {
460 }
461}
462
463
464void SQLExecutor::insertSingleBulk()
465{
466 std::string funct = "insertSingleBulk()";
467 int x = 0;
468 Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(x)));
469
470 for (x = 0; x < 100; ++x)
471 {
472 std::size_t i = stmt.execute();
473 poco_assert (i == 1);
474 }
475
476 int count = 0;
477 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
478 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
479 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
480 poco_assert (count == 100);
481
482 try { *_pSession << "SELECT SUM(str) FROM Strings", into(count), now; }
483 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
484 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
485 poco_assert (count == ((0+99)*100/2));
486}
487
488
489void SQLExecutor::unsignedInts()
490{
491 std::string funct = "unsignedInts()";
492 Poco::UInt32 data = std::numeric_limits<Poco::UInt32>::max();
493 Poco::UInt32 ret = 0;
494
495 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
496 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
497 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
498
499 int count = 0;
500 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
501 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
502 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
503 poco_assert (count == 1);
504
505 try { *_pSession << "SELECT str FROM Strings", into(ret), now; }
506 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
507 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
508 poco_assert (ret == data);
509}
510
511
512void SQLExecutor::floats()
513{
514 std::string funct = "floats()";
515 float data = 1.5f;
516 float ret = 0.0f;
517
518 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
519 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
520 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
521
522 int count = 0;
523 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
524 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
525 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
526 poco_assert (count == 1);
527
528 try { *_pSession << "SELECT str FROM Strings", into(ret), now; }
529 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
530 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
531 poco_assert (ret == data);
532}
533
534
535void SQLExecutor::doubles()
536{
537 std::string funct = "floats()";
538 double data = 1.5;
539 double ret = 0.0;
540
541 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
542 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
543 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
544
545 int count = 0;
546 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
547 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
548 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
549 poco_assert (count == 1);
550
551 try { *_pSession << "SELECT str FROM Strings", into(ret), now; }
552 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
553 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
554 poco_assert (ret == data);
555}
556
557void SQLExecutor::any()
558{
559 std::string funct = "any()";
560 Any i8 = Poco::Int8(42);
561 Any i16 = Poco::Int16(420);
562 Any i32 = Poco::UInt32(42058);
563 Any i64 = Poco::Int64(2205861);
564 Any f = float(42.5);
565 Any d = double(4278.5);
566 Any s = std::string("42");
567 Any date = Date(DateTime());
568 Any t = Time(DateTime());
569 Any dateTime = DateTime(2017, 9, 2, 18, 49, 15, 227, 987);
570 Any e;
571 assertTrue (e.empty());
572
573 try { *_pSession << "INSERT INTO Anys VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, null)",
574 use(i8), use(i16), use(i32), use(i64), use(f), use(d), use(s), use(s), use(date),
575 use(t), use(dateTime), now; }
576 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
577 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
578
579 int count = 0;
580 try { *_pSession << "SELECT COUNT(*) FROM Anys", into(count), now; }
581 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
582 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
583 poco_assert (count == 1);
584
585 i8 = Poco::Int8(0);
586 i16 = Poco::Int16(0);
587 i32 = Poco::Int32(0);
588 i64 = Poco::Int64(0);
589 f = 0.0f;
590 d = 0.0;
591 s = std::string("");
592 Any s2 = std::string("");
593 Any dateR = Date();
594 Any tR = Time();
595 Any dateTimeR = DateTime();
596 e = 1;
597 assertTrue (!e.empty());
598
599 try { *_pSession << "SELECT * FROM Anys", into(i8), into(i16), into(i32), into(i64),
600 into(f), into(d), into(s), into(s2), into(dateR), into(tR), into(dateTimeR), into(e), now; }
601 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
602 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
603
604 assertTrue (42 == AnyCast<Poco::Int8>(i8));
605 assertTrue (420 == AnyCast<Poco::Int16>(i16));
606 assertTrue (42058 == AnyCast<Poco::Int32>(i32));
607 assertTrue (2205861 == AnyCast<Poco::Int64>(i64));
608 assertTrue (42.5f == AnyCast<float>(f));
609 assertTrue (4278.5 == AnyCast<double>(d));
610 assertTrue ("42" == AnyCast<std::string>(s));
611 assertTrue ("42" == AnyCast<std::string>(s2));
612 assertTrue (AnyCast<Date>(date) == AnyCast<Date>(dateR));
613 assertTrue (AnyCast<Time>(t) == AnyCast<Time>(tR));
614 assertTrue (AnyCast<DateTime>(dateTimeR) == AnyCast<DateTime>(dateTime));
615 assertTrue (e.empty());
616}
617
618void SQLExecutor::dynamicAny()
619{
620 std::string funct = "dynamicAny()";
621 DynamicAny i8 = Poco::Int8(42);
622 DynamicAny i16 = Poco::Int16(420);
623 DynamicAny i32 = Poco::UInt32(42058);
624 DynamicAny i64 = Poco::Int64(2205861);
625 DynamicAny f = float(42.5);
626 DynamicAny d = double(4278.5);
627 DynamicAny s = std::string("42");
628 DynamicAny date = Date(DateTime());
629 DynamicAny t = Time(DateTime());
630 DynamicAny dateTime = DateTime();
631 DynamicAny e;
632 assertTrue (e.isEmpty());
633
634 try { *_pSession << "INSERT INTO Anys VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, null)",
635 use(i8), use(i16), use(i32), use(i64), use(f), use(d), use(s), use(s), use(date),
636 use(t), use(dateTime), now; }
637 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
638 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
639
640 int count = 0;
641 try { *_pSession << "SELECT COUNT(*) FROM Anys", into(count), now; }
642 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
643 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
644 poco_assert (count == 1);
645
646 i8 = Poco::Int8(0);
647 i16 = Poco::Int16(0);
648 i32 = Poco::Int32(0);
649 i64 = Poco::Int64(0);
650 f = 0.0f;
651 d = 0.0;
652 s = std::string("");
653 DynamicAny s2 = std::string("");
654 DynamicAny dateR = Date();
655 DynamicAny tR = Time();
656 DynamicAny dateTimeR = DateTime();
657 e = 1;
658 assertTrue (!e.isEmpty());
659
660 try { *_pSession << "SELECT * FROM Anys", into(i8), into(i16), into(i32), into(i64),
661 into(f), into(d), into(s), into(s2), into(dateR), into(tR), into(dateTimeR), into(e), now; }
662 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
663 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
664
665 assertTrue (42 == i8);
666 assertTrue (420 == i16);
667 assertTrue (42058 == i32);
668 assertTrue (2205861 == i64);
669 assertTrue (42.5f == f);
670 assertTrue (4278.5 == d);
671 assertTrue ("42" == s);
672 assertTrue ("42" == s2);
673 assertTrue (date == dateR);
674 assertTrue (t == tR);
675 assertTrue (dateTimeR.convert<DateTime>() == dateTime.convert<DateTime>());
676 assertTrue (e.isEmpty());
677}
678
679
680void SQLExecutor::insertSingleBulkVec()
681{
682 std::string funct = "insertSingleBulkVec()";
683 std::vector<int> data;
684
685 for (int x = 0; x < 100; ++x)
686 data.push_back(x);
687
688 Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(data)));
689 stmt.execute();
690
691 int count = 0;
692 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
693 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
694 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
695
696 poco_assert (count == 100);
697 try { *_pSession << "SELECT SUM(str) FROM Strings", into(count), now; }
698 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
699 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
700 poco_assert (count == ((0+99)*100/2));
701}
702
703
704void SQLExecutor::limits()
705{
706 std::string funct = "limit()";
707 std::vector<int> data;
708 for (int x = 0; x < 100; ++x)
709 {
710 data.push_back(x);
711 }
712
713 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
714 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
715 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
716
717 std::vector<int> retData;
718 try { *_pSession << "SELECT * FROM Strings", into(retData), limit(50), now; }
719 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
720 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
721 poco_assert (retData.size() == 50);
722 for (int x = 0; x < 50; ++x)
723 {
724 poco_assert (data[x] == retData[x]);
725 }
726}
727
728
729void SQLExecutor::limitZero()
730{
731 std::string funct = "limitZero()";
732 std::vector<int> data;
733 for (int x = 0; x < 100; ++x)
734 {
735 data.push_back(x);
736 }
737
738 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
739 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
740 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
741
742 std::vector<int> retData;
743 try { *_pSession << "SELECT * FROM Strings", into(retData), limit(0), now; }// stupid test, but at least we shouldn't crash
744 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
745 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
746 poco_assert (retData.size() == 0);
747}
748
749
750void SQLExecutor::limitOnce()
751{
752 std::string funct = "limitOnce()";
753 std::vector<int> data;
754 for (int x = 0; x < 101; ++x)
755 {
756 data.push_back(x);
757 }
758
759 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
760 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
761 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
762
763 std::vector<int> retData;
764 Statement stmt = (*_pSession << "SELECT * FROM Strings", into(retData), limit(50), now);
765 poco_assert (!stmt.done());
766 poco_assert (retData.size() == 50);
767 stmt.execute();
768 poco_assert (!stmt.done());
769 poco_assert (retData.size() == 100);
770 stmt.execute();
771 poco_assert (stmt.done());
772 poco_assert (retData.size() == 101);
773
774 for (int x = 0; x < 101; ++x)
775 {
776 poco_assert (data[x] == retData[x]);
777 }
778}
779
780
781void SQLExecutor::limitPrepare()
782{
783 std::string funct = "limitPrepare()";
784 std::vector<int> data;
785 for (int x = 0; x < 100; ++x)
786 {
787 data.push_back(x);
788 }
789
790 try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
791 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
792 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
793
794 std::vector<int> retData;
795 Statement stmt = (*_pSession << "SELECT * FROM Strings", into(retData), limit(50));
796 poco_assert (retData.size() == 0);
797 poco_assert (!stmt.done());
798
799 try { stmt.execute(); }
800 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
801 poco_assert (!stmt.done());
802 poco_assert (retData.size() == 50);
803
804 try { stmt.execute(); }
805 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
806 poco_assert (stmt.done());
807 poco_assert (retData.size() == 100);
808
809 try { stmt.execute(); }// will restart execution!
810 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
811 poco_assert (!stmt.done());
812 poco_assert (retData.size() == 150);
813 for (int x = 0; x < 150; ++x)
814 {
815 poco_assert (data[x%100] == retData[x]);
816 }
817}
818
819
820
821void SQLExecutor::prepare()
822{
823 std::string funct = "prepare()";
824 std::vector<int> data;
825 for (int x = 0; x < 100; x += 2)
826 {
827 data.push_back(x);
828 }
829
830 {
831 Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(data)));
832 }
833 // stmt should not have been executed when destroyed
834 int count = 100;
835 try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
836 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
837 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
838 poco_assert (count == 0);
839}
840
841
842void SQLExecutor::setSimple()
843{
844 std::string funct = "setSimple()";
845 std::set<std::string> lastNames;
846 std::set<std::string> firstNames;
847 std::set<std::string> addresses;
848 std::set<int> ages;
849 std::string tableName("Person");
850 lastNames.insert("LN1");
851 lastNames.insert("LN2");
852 firstNames.insert("FN1");
853 firstNames.insert("FN2");
854 addresses.insert("ADDR1");
855 addresses.insert("ADDR2");
856 ages.insert(1);
857 ages.insert(2);
858 int count = 0;
859 std::string result;
860
861 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
862 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
863 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
864 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
865 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
866 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
867 poco_assert (count == 2);
868
869 std::set<std::string> lastNamesR;
870 std::set<std::string> firstNamesR;
871 std::set<std::string> addressesR;
872 std::set<int> agesR;
873 try { *_pSession << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
874 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
875 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
876 poco_assert (ages == agesR);
877 poco_assert (lastNames == lastNamesR);
878 poco_assert (firstNames == firstNamesR);
879 poco_assert (addresses == addressesR);
880}
881
882
883void SQLExecutor::setComplex()
884{
885 std::string funct = "setComplex()";
886 std::set<Person> people;
887 people.insert(Person("LN1", "FN1", "ADDR1", 1));
888 people.insert(Person("LN2", "FN2", "ADDR2", 2));
889
890 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
891 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
892 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
893 int count = 0;
894 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
895 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
896 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
897 poco_assert (count == 2);
898
899 std::set<Person> result;
900 try { *_pSession << "SELECT * FROM Person", into(result), now; }
901 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
902 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
903 poco_assert (result == people);
904}
905
906
907void SQLExecutor::setComplexUnique()
908{
909 std::string funct = "setComplexUnique()";
910 std::vector<Person> people;
911 Person p1("LN1", "FN1", "ADDR1", 1);
912 people.push_back(p1);
913 people.push_back(p1);
914 people.push_back(p1);
915 people.push_back(p1);
916 Person p2("LN2", "FN2", "ADDR2", 2);
917 people.push_back(p2);
918
919 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
920 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
921 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
922 int count = 0;
923 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
924 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
925 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
926 poco_assert (count == 5);
927
928 std::set<Person> result;
929 try { *_pSession << "SELECT * FROM Person", into(result), now; }
930 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
931 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
932 poco_assert (result.size() == 2);
933 poco_assert (*result.begin() == p1);
934 poco_assert (*++result.begin() == p2);
935}
936
937void SQLExecutor::multiSetSimple()
938{
939 std::string funct = "multiSetSimple()";
940 std::multiset<std::string> lastNames;
941 std::multiset<std::string> firstNames;
942 std::multiset<std::string> addresses;
943 std::multiset<int> ages;
944 std::string tableName("Person");
945 lastNames.insert("LN1");
946 lastNames.insert("LN2");
947 firstNames.insert("FN1");
948 firstNames.insert("FN2");
949 addresses.insert("ADDR1");
950 addresses.insert("ADDR2");
951 ages.insert(1);
952 ages.insert(2);
953 int count = 0;
954 std::string result;
955
956 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
957 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
958 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
959 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
960 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
961 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
962 poco_assert (count == 2);
963
964 std::multiset<std::string> lastNamesR;
965 std::multiset<std::string> firstNamesR;
966 std::multiset<std::string> addressesR;
967 std::multiset<int> agesR;
968 try { *_pSession << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
969 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
970 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
971 poco_assert (ages.size() == agesR.size());
972 poco_assert (lastNames.size() == lastNamesR.size());
973 poco_assert (firstNames.size() == firstNamesR.size());
974 poco_assert (addresses.size() == addressesR.size());
975}
976
977
978void SQLExecutor::multiSetComplex()
979{
980 std::string funct = "multiSetComplex()";
981 std::multiset<Person> people;
982 Person p1("LN1", "FN1", "ADDR1", 1);
983 people.insert(p1);
984 people.insert(p1);
985 people.insert(p1);
986 people.insert(p1);
987 Person p2("LN2", "FN2", "ADDR2", 2);
988 people.insert(p2);
989
990 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
991 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
992 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
993 int count = 0;
994 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
995 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
996 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
997 poco_assert (count == 5);
998
999 std::multiset<Person> result;
1000 try { *_pSession << "SELECT * FROM Person", into(result), now; }
1001 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1002 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1003 poco_assert (result.size() == people.size());
1004}
1005
1006
1007void SQLExecutor::mapComplex()
1008{
1009 std::string funct = "mapComplex()";
1010 std::map<std::string, Person> people;
1011 Person p1("LN1", "FN1", "ADDR1", 1);
1012 Person p2("LN2", "FN2", "ADDR2", 2);
1013 people.insert(std::make_pair("LN1", p1));
1014 people.insert(std::make_pair("LN2", p2));
1015
1016 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1017 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1018 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1019 int count = 0;
1020 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1021 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1022 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1023 poco_assert (count == 2);
1024
1025 std::map<std::string, Person> result;
1026 try { *_pSession << "SELECT * FROM Person", into(result), now; }
1027 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1028 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1029 poco_assert (result == people);
1030}
1031
1032
1033void SQLExecutor::mapComplexUnique()
1034{
1035 std::string funct = "mapComplexUnique()";
1036 std::multimap<std::string, Person> people;
1037 Person p1("LN1", "FN1", "ADDR1", 1);
1038 Person p2("LN2", "FN2", "ADDR2", 2);
1039 people.insert(std::make_pair("LN1", p1));
1040 people.insert(std::make_pair("LN1", p1));
1041 people.insert(std::make_pair("LN1", p1));
1042 people.insert(std::make_pair("LN1", p1));
1043 people.insert(std::make_pair("LN2", p2));
1044
1045 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1046 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1047 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1048 int count = 0;
1049 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1050 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1051 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1052 poco_assert (count == 5);
1053
1054 std::map<std::string, Person> result;
1055 try { *_pSession << "SELECT * FROM Person", into(result), now; }
1056 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1057 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1058 poco_assert (result.size() == 2);
1059}
1060
1061
1062void SQLExecutor::multiMapComplex()
1063{
1064 std::string funct = "multiMapComplex()";
1065 std::multimap<std::string, Person> people;
1066 Person p1("LN1", "FN1", "ADDR1", 1);
1067 Person p2("LN2", "FN2", "ADDR2", 2);
1068 people.insert(std::make_pair("LN1", p1));
1069 people.insert(std::make_pair("LN1", p1));
1070 people.insert(std::make_pair("LN1", p1));
1071 people.insert(std::make_pair("LN1", p1));
1072 people.insert(std::make_pair("LN2", p2));
1073
1074 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1075 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1076 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1077 int count = 0;
1078 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1079 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1080 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1081 poco_assert (count == 5);
1082
1083 std::multimap<std::string, Person> result;
1084 try { *_pSession << "SELECT * FROM Person", into(result), now; }
1085 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1086 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1087 poco_assert (result.size() == people.size());
1088}
1089
1090
1091void SQLExecutor::selectIntoSingle()
1092{
1093 std::string funct = "selectIntoSingle()";
1094 std::multimap<std::string, Person> people;
1095 Person p1("LN1", "FN1", "ADDR1", 1);
1096 Person p2("LN2", "FN2", "ADDR2", 2);
1097 people.insert(std::make_pair("LN1", p1));
1098 people.insert(std::make_pair("LN1", p2));
1099
1100 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1101 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1102 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1103 int count = 0;
1104 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1105 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1106 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1107 poco_assert (count == 2);
1108 Person result;
1109 try { *_pSession << "SELECT * FROM Person", into(result), limit(1), now; }// will return 1 object into one single result
1110 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1111 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1112 poco_assert (result == p1);
1113}
1114
1115
1116void SQLExecutor::selectIntoSingleStep()
1117{
1118 std::string funct = "selectIntoSingleStep()";
1119 std::multimap<std::string, Person> people;
1120 Person p1("LN1", "FN1", "ADDR1", 1);
1121 Person p2("LN2", "FN2", "ADDR2", 2);
1122 people.insert(std::make_pair("LN1", p1));
1123 people.insert(std::make_pair("LN1", p2));
1124
1125 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1126 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1127 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1128
1129 int count = 0;
1130 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1131 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1132 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1133 poco_assert (count == 2);
1134 Person result;
1135 Statement stmt = (*_pSession << "SELECT * FROM Person", into(result), limit(1));
1136 stmt.execute();
1137 poco_assert (result == p1);
1138 poco_assert (!stmt.done());
1139 stmt.execute();
1140 poco_assert (result == p2);
1141 poco_assert (stmt.done());
1142}
1143
1144
1145void SQLExecutor::selectIntoSingleFail()
1146{
1147 std::string funct = "selectIntoSingleFail()";
1148 std::multimap<std::string, Person> people;
1149 Person p1("LN1", "FN1", "ADDR1", 1);
1150 Person p2("LN2", "FN2", "ADDR2", 2);
1151 people.insert(std::make_pair("LN1", p1));
1152 people.insert(std::make_pair("LN1", p2));
1153
1154 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1155 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1156 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1157 int count = 0;
1158 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), limit(2, true), now; }
1159 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1160 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1161 poco_assert (count == 2);
1162 Person result;
1163 try
1164 {
1165 *_pSession << "SELECT * FROM Person", into(result), limit(1, true), now; // will fail now
1166 fail("hardLimit is set: must fail");
1167 }
1168 catch(Poco::SQL::LimitException&)
1169 {
1170 }
1171}
1172
1173
1174void SQLExecutor::lowerLimitOk()
1175{
1176 std::string funct = "lowerLimitOk()";
1177 std::multimap<std::string, Person> people;
1178 Person p1("LN1", "FN1", "ADDR1", 1);
1179 Person p2("LN2", "FN2", "ADDR2", 2);
1180 people.insert(std::make_pair("LN1", p1));
1181 people.insert(std::make_pair("LN1", p2));
1182
1183 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1184 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1185 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1186
1187 int count = 0;
1188 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1189 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1190 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1191 poco_assert (count == 2);
1192 Person result;
1193 try
1194 {
1195 *_pSession << "SELECT * FROM Person", into(result), lowerLimit(2), now; // will return 2 objects into one single result but only room for one!
1196 fail("Not enough space for results");
1197 }
1198 catch(Poco::Exception&)
1199 {
1200 }
1201}
1202
1203
1204void SQLExecutor::singleSelect()
1205{
1206 std::string funct = "singleSelect()";
1207 std::multimap<std::string, Person> people;
1208 Person p1("LN1", "FN1", "ADDR1", 1);
1209 Person p2("LN2", "FN2", "ADDR2", 2);
1210 people.insert(std::make_pair("LN1", p1));
1211 people.insert(std::make_pair("LN1", p2));
1212
1213 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1214 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1215 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1216
1217 int count = 0;
1218 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1219 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1220 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1221 poco_assert (count == 2);
1222 Person result;
1223 Statement stmt = (*_pSession << "SELECT * FROM Person", into(result), limit(1));
1224 stmt.execute();
1225 poco_assert (result == p1);
1226 poco_assert (!stmt.done());
1227 stmt.execute();
1228 poco_assert (result == p2);
1229 poco_assert (stmt.done());
1230}
1231
1232
1233void SQLExecutor::lowerLimitFail()
1234{
1235 std::string funct = "lowerLimitFail()";
1236 std::multimap<std::string, Person> people;
1237 Person p1("LN1", "FN1", "ADDR1", 1);
1238 Person p2("LN2", "FN2", "ADDR2", 2);
1239 people.insert(std::make_pair("LN1", p1));
1240 people.insert(std::make_pair("LN1", p2));
1241
1242 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1243 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1244 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1245 int count = 0;
1246 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1247 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1248 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1249 poco_assert (count == 2);
1250 Person result;
1251 try
1252 {
1253 *_pSession << "SELECT * FROM Person", into(result), lowerLimit(3), now; // will fail
1254 fail("should fail. not enough data");
1255 }
1256 catch(Poco::Exception&)
1257 {
1258 }
1259}
1260
1261
1262void SQLExecutor::combinedLimits()
1263{
1264 std::string funct = "combinedLimits()";
1265 std::multimap<std::string, Person> people;
1266 Person p1("LN1", "FN1", "ADDR1", 1);
1267 Person p2("LN2", "FN2", "ADDR2", 2);
1268 people.insert(std::make_pair("LN1", p1));
1269 people.insert(std::make_pair("LN1", p2));
1270
1271 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1272 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1273 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1274 int count = 0;
1275 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1276 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1277 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1278 poco_assert (count == 2);
1279 std::vector <Person> result;
1280 try { *_pSession << "SELECT * FROM Person", into(result), lowerLimit(2), upperLimit(2), now; }// will return 2 objects
1281 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1282 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1283 poco_assert (result.size() == 2);
1284 poco_assert (result[0] == p1);
1285 poco_assert (result[1] == p2);
1286}
1287
1288
1289
1290void SQLExecutor::ranges()
1291{
1292 std::string funct = "range()";
1293 std::multimap<std::string, Person> people;
1294 Person p1("LN1", "FN1", "ADDR1", 1);
1295 Person p2("LN2", "FN2", "ADDR2", 2);
1296 people.insert(std::make_pair("LN1", p1));
1297 people.insert(std::make_pair("LN1", p2));
1298
1299 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1300 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1301 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1302 int count = 0;
1303 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1304 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1305 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1306 poco_assert (count == 2);
1307 std::vector <Person> result;
1308 try { *_pSession << "SELECT * FROM Person", into(result), range(2, 2), now; }// will return 2 objects
1309 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1310 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1311 poco_assert (result.size() == 2);
1312 poco_assert (result[0] == p1);
1313 poco_assert (result[1] == p2);
1314}
1315
1316
1317void SQLExecutor::combinedIllegalLimits()
1318{
1319 std::string funct = "combinedIllegalLimits()";
1320 std::multimap<std::string, Person> people;
1321 Person p1("LN1", "FN1", "ADDR1", 1);
1322 Person p2("LN2", "FN2", "ADDR2", 2);
1323 people.insert(std::make_pair("LN1", p1));
1324 people.insert(std::make_pair("LN1", p2));
1325
1326 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1327 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1328 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1329 int count = 0;
1330 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1331 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1332 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1333 poco_assert (count == 2);
1334 Person result;
1335 try
1336 {
1337 *_pSession << "SELECT * FROM Person", into(result), lowerLimit(3), upperLimit(2), now;
1338 fail("lower > upper is not allowed");
1339 }
1340 catch(LimitException&)
1341 {
1342 }
1343}
1344
1345
1346void SQLExecutor::illegalRange()
1347{
1348 std::string funct = "illegalRange()";
1349 std::multimap<std::string, Person> people;
1350 Person p1("LN1", "FN1", "ADDR1", 1);
1351 Person p2("LN2", "FN2", "ADDR2", 2);
1352 people.insert(std::make_pair("LN1", p1));
1353 people.insert(std::make_pair("LN1", p2));
1354
1355 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1356 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1357 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1358 int count = 0;
1359 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1360 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1361 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1362 poco_assert (count == 2);
1363 Person result;
1364 try
1365 {
1366 *_pSession << "SELECT * FROM Person", into(result), range(3, 2), now;
1367 fail("lower > upper is not allowed");
1368 }
1369 catch(LimitException&)
1370 {
1371 }
1372}
1373
1374
1375void SQLExecutor::emptyDB()
1376{
1377 std::string funct = "emptyDB()";
1378 int count = 0;
1379 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1380 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1381 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1382 poco_assert (count == 0);
1383
1384 Person result;
1385 Statement stmt = (*_pSession << "SELECT * FROM Person", into(result), limit(1));
1386 stmt.execute();
1387 poco_assert (result.firstName.empty());
1388 poco_assert (stmt.done());
1389}
1390
1391
1392void SQLExecutor::dateTime()
1393{
1394 std::string funct = "dateTime()";
1395 std::string lastName("Bart");
1396 std::string firstName("Simpson");
1397 std::string address("Springfield");
1398 DateTime birthday(1980, 4, 1, 5, 45, 12, 354, 879);
1399
1400 int count = 0;
1401 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(birthday), now; }
1402 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1403 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1404 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1405 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1406 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1407 poco_assert (count == 1);
1408
1409 DateTime bd;
1410 poco_assert (bd != birthday);
1411 try { *_pSession << "SELECT Birthday FROM Person", into(bd), now; }
1412 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1413 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1414 poco_assert (bd == birthday);
1415
1416 std::cout << std::endl << RecordSet(*_pSession, "SELECT * FROM Person");
1417}
1418
1419
1420void SQLExecutor::date()
1421{
1422 std::string funct = "date()";
1423 std::string lastName("Bart");
1424 std::string firstName("Simpson");
1425 std::string address("Springfield");
1426 Date birthday(1980, 4, 1);
1427
1428 int count = 0;
1429 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(birthday), now; }
1430 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1431 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1432 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1433 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1434 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1435 poco_assert (count == 1);
1436
1437 Date bd;
1438 poco_assert (bd != birthday);
1439 try { *_pSession << "SELECT Birthday FROM Person", into(bd), now; }
1440 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1441 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1442 poco_assert (bd == birthday);
1443
1444 std::cout << std::endl << RecordSet(*_pSession, "SELECT * FROM Person");
1445}
1446
1447
1448void SQLExecutor::time()
1449{
1450 std::string funct = "date()";
1451 std::string lastName("Bart");
1452 std::string firstName("Simpson");
1453 std::string address("Springfield");
1454 Time birthday(1, 2, 3);
1455
1456 int count = 0;
1457 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(birthday), now; }
1458 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1459 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1460 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1461 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1462 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1463 poco_assert (count == 1);
1464
1465 Time bd;
1466 poco_assert (bd != birthday);
1467 try { *_pSession << "SELECT Birthday FROM Person", into(bd), now; }
1468 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1469 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1470 poco_assert (bd == birthday);
1471
1472 std::cout << std::endl << RecordSet(*_pSession, "SELECT * FROM Person");
1473}
1474
1475
1476void SQLExecutor::blob(unsigned int bigSize)
1477{
1478 std::string funct = "blob()";
1479 std::string lastName("lastname");
1480 std::string firstName("firstname");
1481 std::string address("Address");
1482
1483 Poco::SQL::CLOB img("0123456789", 10);
1484 int count = 0;
1485 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(img), now; }
1486 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1487 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1488 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1489 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1490 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1491 poco_assert (count == 1);
1492
1493 Poco::SQL::CLOB res;
1494 poco_assert (res.size() == 0);
1495 try { *_pSession << "SELECT Image FROM Person", into(res), now; }
1496 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1497 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1498 poco_assert (res == img);
1499
1500 Poco::SQL::CLOB big;
1501 std::vector<char> v(bigSize, 'x');
1502 big.assignRaw(&v[0], (std::size_t) v.size());
1503
1504 poco_assert (big.size() == (std::size_t) bigSize);
1505
1506 try { *_pSession << "DELETE FROM Person", now; }
1507 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1508 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1509
1510 try { *_pSession << "INSERT INTO Person VALUES(?,?,?,?)", use(lastName), use(firstName), use(address), use(big), now; }
1511 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1512 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1513
1514 try { *_pSession << "SELECT Image FROM Person", into(res), now; }
1515 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1516 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1517 poco_assert (res == big);
1518}
1519
1520
1521void SQLExecutor::blobStmt()
1522{
1523 std::string funct = "blobStmt()";
1524 std::string lastName("lastname");
1525 std::string firstName("firstname");
1526 std::string address("Address");
1527 Poco::SQL::CLOB blob("0123456789", 10);
1528
1529 int count = 0;
1530 Statement ins = (*_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(blob));
1531 ins.execute();
1532 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1533 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1534 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1535 poco_assert (count == 1);
1536
1537 Poco::SQL::CLOB res;
1538 poco_assert (res.size() == 0);
1539 Statement stmt = (*_pSession << "SELECT Image FROM Person", into(res));
1540 try { stmt.execute(); }
1541 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1542 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1543 poco_assert (res == blob);
1544}
1545
1546void SQLExecutor::longText()
1547{
1548 std::string funct = "longText()";
1549 std::string lastName("lastname");
1550 std::string firstName("firstname");
1551 std::string address("Address");
1552 std::string info("0123456789");
1553
1554 Poco::SQL::CLOB img("0123456789", 10);
1555 int count = 0;
1556 try { *_pSession << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(info), now; }
1557 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1558 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1559
1560 try { *_pSession << "SELECT COUNT(*) FROM Person", into(count), now; }
1561 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1562 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1563 assertTrue (count == 1);
1564
1565 std::string res;
1566 poco_assert (res.size() == 0);
1567 Statement stmt = (*_pSession << "SELECT Info FROM Person", into(res));
1568 try { stmt.execute(); }
1569 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1570 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1571 poco_assert (res == info);
1572}
1573
1574void SQLExecutor::tuples()
1575{
1576 typedef Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> TupleType;
1577 std::string funct = "tuples()";
1578 TupleType t(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
1579
1580 try { *_pSession << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", use(t), now; }
1581 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1582 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1583
1584 TupleType ret(-10,-11,-12,-13,-14,-15,-16,-17,-18,-19);
1585 poco_assert (ret != t);
1586 try { *_pSession << "SELECT * FROM Tuples", into(ret), now; }
1587 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1588 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1589 poco_assert (ret == t);
1590}
1591
1592
1593void SQLExecutor::tupleVector()
1594{
1595 typedef Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> TupleType;
1596 std::string funct = "tupleVector()";
1597 TupleType t(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
1598 Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int>
1599 t10(10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29);
1600 TupleType t100(100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119);
1601 std::vector<TupleType> v;
1602 v.push_back(t);
1603 v.push_back(t10);
1604 v.push_back(t100);
1605
1606 try { *_pSession << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", use(v), now; }
1607 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1608 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1609
1610 int count = 0;
1611 try { *_pSession << "SELECT COUNT(*) FROM Tuples", into(count), now; }
1612 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1613 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1614 poco_assert (v.size() == (std::size_t) count);
1615
1616 std::vector<Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> > ret;
1617 try { *_pSession << "SELECT * FROM Tuples", into(ret), now; }
1618 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1619 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1620 poco_assert (ret == v);
1621}
1622
1623
1624void SQLExecutor::stdTuples()
1625{
1626 typedef std::tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> TupleType;
1627 std::string funct = "stdTuples()";
1628 TupleType t = std::make_tuple(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
1629
1630 try { *_pSession << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", use(t), now; }
1631 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1632 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1633
1634 TupleType ret = std::make_tuple(-10,-11,-12,-13,-14,-15,-16,-17,-18,-19,-20,-21,-22,-23,-24,-25,-26,-27,-28,-29);
1635 poco_assert (ret != t);
1636 try { *_pSession << "SELECT * FROM Tuples", into(ret), now; }
1637 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1638 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1639 poco_assert (ret == t);
1640}
1641
1642
1643void SQLExecutor::stdTupleVector()
1644{
1645 typedef std::tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> TupleType;
1646 std::string funct = "stdTupleVector()";
1647 TupleType t = std::make_tuple(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
1648 auto t10 = std::make_tuple(10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29);
1649 TupleType t100 = std::make_tuple(100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119);
1650 std::vector<TupleType> v;
1651 v.push_back(t);
1652 v.push_back(t10);
1653 v.push_back(t100);
1654
1655 try { *_pSession << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", use(v), now; }
1656 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1657 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1658
1659 int count = 0;
1660 try { *_pSession << "SELECT COUNT(*) FROM Tuples", into(count), now; }
1661 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1662 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1663 poco_assert (v.size() == (std::size_t) count);
1664
1665 std::vector<TupleType> ret;
1666 try { *_pSession << "SELECT * FROM Tuples", into(ret), now; }
1667 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1668 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1669 poco_assert (ret == v);
1670}
1671
1672
1673void SQLExecutor::internalExtraction()
1674{
1675 /*std::string funct = "internalExtraction()";
1676 std::vector<Tuple<int, double, std::string> > v;
1677 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
1678 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
1679 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
1680 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
1681
1682 try { *_pSession << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now; }
1683 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1684 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1685
1686 try
1687 {
1688 Statement stmt = (*_pSession << "SELECT * FROM Vectors", now);
1689 RecordSet rset(stmt);
1690
1691 poco_assert (3 == rset.columnCount());
1692 poco_assert (4 == rset.rowCount());
1693
1694 int curVal = 3;
1695 do
1696 {
1697 poco_assert (rset["str0"] == curVal);
1698 ++curVal;
1699 } while (rset.moveNext());
1700
1701 rset.moveFirst();
1702 poco_assert (rset["str0"] == "3");
1703 rset.moveLast();
1704 poco_assert (rset["str0"] == "6");
1705
1706 RecordSet rset2(rset);
1707 poco_assert (3 == rset2.columnCount());
1708 poco_assert (4 == rset2.rowCount());
1709
1710 int i = rset.value<int>(0,0);
1711 poco_assert (1 == i);
1712
1713 std::string s = rset.value(0,0);
1714 poco_assert ("1" == s);
1715
1716 int a = rset.value<int>(0,2);
1717 poco_assert (3 == a);
1718
1719 try
1720 {
1721 double d = rset.value<double>(1,1);
1722 poco_assert (2.5 == d);
1723 }
1724 catch (BadCastException&)
1725 {
1726 float f = rset.value<float>(1,1);
1727 poco_assert (2.5 == f);
1728 }
1729
1730 s = rset.value<std::string>(2,2);
1731 poco_assert ("5" == s);
1732 i = rset.value("str0", 2);
1733 poco_assert (5 == i);
1734
1735 const Column<int>& col = rset.column<int>(0);
1736 Column<int>::Iterator it = col.begin();
1737 Column<int>::Iterator end = col.end();
1738 for (int i = 1; it != end; ++it, ++i)
1739 poco_assert (*it == i);
1740
1741 rset = (*_pSession << "SELECT COUNT(*) AS cnt FROM Vectors", now);
1742
1743 //various results for COUNT(*) are received from different drivers
1744 try
1745 {
1746 //this is what most drivers will return
1747 int i = rset.value<int>(0,0);
1748 poco_assert (4 == i);
1749 }
1750 catch(BadCastException&)
1751 {
1752 try
1753 {
1754 //this is for Oracle
1755 double i = rset.value<double>(0,0);
1756 poco_assert (4 == int(i));
1757 }
1758 catch(BadCastException&)
1759 {
1760 //this is for PostgreSQL
1761 Poco::Int64 big = rset.value<Poco::Int64>(0,0);
1762 poco_assert (4 == big);
1763 }
1764 }
1765
1766 s = rset.value("cnt", 0).convert<std::string>();
1767 poco_assert ("4" == s);
1768
1769 try { const Column<int>& col1 = rset.column<int>(100); fail ("must fail"); }
1770 catch (RangeException&) { }
1771
1772 try { rset.value<std::string>(0,0); fail ("must fail"); }
1773 catch (BadCastException&) { }
1774
1775 stmt = (*_pSession << "DELETE FROM Vectors", now);
1776 rset = stmt;
1777
1778 try { const Column<int>& col1 = rset.column<int>(0); fail ("must fail"); }
1779 catch (RangeException&) { }
1780 }
1781 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1782 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1783*/
1784}
1785
1786
1787void SQLExecutor::doNull()
1788{
1789 std::string funct = "null()";
1790
1791 *_pSession << "INSERT INTO Vectors VALUES (?, ?, ?)",
1792 use(Poco::SQL::Keywords::null),
1793 use(Poco::SQL::Keywords::null),
1794 use(Poco::SQL::Keywords::null), now;
1795
1796 int count = 0;
1797 try { *_pSession << "SELECT COUNT(*) FROM Vectors", into(count), now; }
1798 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1799 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1800 poco_assert (count == 1);
1801
1802 int i0 = 0;
1803 Statement stmt1 = (*_pSession << "SELECT i0 FROM Vectors", into(i0, Poco::SQL::Position(0), -1));
1804 try { stmt1.execute(); }
1805 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1806 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1807 poco_assert (i0 == -1);
1808
1809 float flt0 = 0;
1810 Statement stmt2 = (*_pSession << "SELECT flt0 FROM Vectors", into(flt0, Poco::SQL::Position(0), 3.25f));
1811 try { stmt2.execute(); }
1812 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1813 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1814 poco_assert (flt0 == 3.25);
1815
1816 std::string str0("string");
1817 Statement stmt3 = (*_pSession << "SELECT str0 FROM Vectors", into(str0, Poco::SQL::Position(0), std::string("DEFAULT")));
1818 try { stmt3.execute(); }
1819 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1820 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1821 poco_assert (str0 == "DEFAULT");
1822}
1823
1824
1825void SQLExecutor::setTransactionIsolation(Session& session, Poco::UInt32 ti)
1826{
1827 if (session.hasTransactionIsolation(ti))
1828 {
1829 std::string funct = "setTransactionIsolation()";
1830
1831 try
1832 {
1833 Transaction t(session, false);
1834 t.setIsolation(ti);
1835
1836 poco_assert (ti == t.getIsolation());
1837 poco_assert (t.isIsolation(ti));
1838
1839 poco_assert (ti == session.getTransactionIsolation());
1840 poco_assert (session.isTransactionIsolation(ti));
1841 }
1842 catch(Poco::Exception& e){ std::cout << funct << ':' << e.displayText() << std::endl;}
1843 }
1844 else
1845 {
1846 std::cout << "Transaction isolation not supported: ";
1847 switch (ti)
1848 {
1849 case Session::TRANSACTION_READ_COMMITTED:
1850 std::cout << "READ COMMITTED"; break;
1851 case Session::TRANSACTION_READ_UNCOMMITTED:
1852 std::cout << "READ UNCOMMITTED"; break;
1853 case Session::TRANSACTION_REPEATABLE_READ:
1854 std::cout << "REPEATABLE READ"; break;
1855 case Session::TRANSACTION_SERIALIZABLE:
1856 std::cout << "SERIALIZABLE"; break;
1857 default:
1858 std::cout << "UNKNOWN"; break;
1859 }
1860 std::cout << std::endl;
1861 }
1862}
1863
1864
1865void SQLExecutor::sessionTransaction(const std::string& connect)
1866{
1867 if (!_pSession->canTransact())
1868 {
1869 std::cout << "Session not capable of transactions." << std::endl;
1870 return;
1871 }
1872
1873 Session local("mysql", connect);
1874 local.setFeature("autoCommit", true);
1875
1876 std::string funct = "transaction()";
1877 std::vector<std::string> lastNames;
1878 std::vector<std::string> firstNames;
1879 std::vector<std::string> addresses;
1880 std::vector<int> ages;
1881 std::string tableName("Person");
1882 lastNames.push_back("LN1");
1883 lastNames.push_back("LN2");
1884 firstNames.push_back("FN1");
1885 firstNames.push_back("FN2");
1886 addresses.push_back("ADDR1");
1887 addresses.push_back("ADDR2");
1888 ages.push_back(1);
1889 ages.push_back(2);
1890 int count = 0, locCount = 0;
1891 std::string result;
1892
1893 bool autoCommit = _pSession->getFeature("autoCommit");
1894
1895 _pSession->setFeature("autoCommit", true);
1896 poco_assert (!_pSession->isTransaction());
1897 _pSession->setFeature("autoCommit", false);
1898 poco_assert (!_pSession->isTransaction());
1899
1900 setTransactionIsolation((*_pSession), Session::TRANSACTION_READ_UNCOMMITTED);
1901 setTransactionIsolation((*_pSession), Session::TRANSACTION_REPEATABLE_READ);
1902 setTransactionIsolation((*_pSession), Session::TRANSACTION_SERIALIZABLE);
1903
1904 setTransactionIsolation((*_pSession), Session::TRANSACTION_READ_COMMITTED);
1905
1906 _pSession->begin();
1907 poco_assert (_pSession->isTransaction());
1908 try { (*_pSession) << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1909 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1910 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1911 poco_assert (_pSession->isTransaction());
1912
1913 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
1914 poco_assert (0 == locCount);
1915
1916 try { (*_pSession) << "SELECT COUNT(*) FROM Person", into(count), now; }
1917 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1918 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1919 poco_assert (2 == count);
1920 poco_assert (_pSession->isTransaction());
1921 _pSession->rollback();
1922 poco_assert (!_pSession->isTransaction());
1923
1924 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
1925 poco_assert (0 == locCount);
1926
1927 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
1928 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1929 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1930 poco_assert (0 == count);
1931 poco_assert (!_pSession->isTransaction());
1932
1933 _pSession->begin();
1934 try { (*_pSession) << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1935 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1936 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1937 poco_assert (_pSession->isTransaction());
1938
1939 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
1940 poco_assert (0 == locCount);
1941
1942 _pSession->commit();
1943 poco_assert (!_pSession->isTransaction());
1944
1945 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
1946 poco_assert (2 == locCount);
1947
1948 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
1949 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
1950 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
1951 poco_assert (2 == count);
1952
1953 _pSession->setFeature("autoCommit", autoCommit);
1954}
1955
1956
1957void SQLExecutor::transaction(const std::string& connect)
1958{
1959 if (!_pSession->canTransact())
1960 {
1961 std::cout << "Session not transaction-capable." << std::endl;
1962 return;
1963 }
1964
1965 Session local("mysql", connect);
1966 local.setFeature("autoCommit", true);
1967
1968 setTransactionIsolation((*_pSession), Session::TRANSACTION_READ_COMMITTED);
1969 setTransactionIsolation(local, Session::TRANSACTION_READ_COMMITTED);
1970
1971 std::string funct = "transaction()";
1972 std::vector<std::string> lastNames;
1973 std::vector<std::string> firstNames;
1974 std::vector<std::string> addresses;
1975 std::vector<int> ages;
1976 std::string tableName("Person");
1977 lastNames.push_back("LN1");
1978 lastNames.push_back("LN2");
1979 firstNames.push_back("FN1");
1980 firstNames.push_back("FN2");
1981 addresses.push_back("ADDR1");
1982 addresses.push_back("ADDR2");
1983 ages.push_back(1);
1984 ages.push_back(2);
1985 int count = 0, locCount = 0;
1986 std::string result;
1987
1988 bool autoCommit = _pSession->getFeature("autoCommit");
1989
1990 _pSession->setFeature("autoCommit", true);
1991 poco_assert (!_pSession->isTransaction());
1992 _pSession->setFeature("autoCommit", false);
1993 poco_assert (!_pSession->isTransaction());
1994 _pSession->setTransactionIsolation(Session::TRANSACTION_READ_COMMITTED);
1995
1996 {
1997 Transaction trans((*_pSession));
1998 poco_assert (trans.isActive());
1999 poco_assert (_pSession->isTransaction());
2000
2001 try { (*_pSession) << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
2002 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2003 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2004
2005 poco_assert (_pSession->isTransaction());
2006 poco_assert (trans.isActive());
2007
2008 try { (*_pSession) << "SELECT COUNT(*) FROM Person", into(count), now; }
2009 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2010 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2011 poco_assert (2 == count);
2012 poco_assert (_pSession->isTransaction());
2013 poco_assert (trans.isActive());
2014 }
2015 poco_assert (!_pSession->isTransaction());
2016
2017 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2018 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2019 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2020 poco_assert (0 == count);
2021 poco_assert (!_pSession->isTransaction());
2022
2023 {
2024 Transaction trans((*_pSession));
2025 try { (*_pSession) << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
2026 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2027 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2028
2029 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2030 poco_assert (0 == locCount);
2031
2032 poco_assert (_pSession->isTransaction());
2033 poco_assert (trans.isActive());
2034 trans.commit();
2035 poco_assert (!_pSession->isTransaction());
2036 poco_assert (!trans.isActive());
2037 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2038 poco_assert (2 == locCount);
2039 }
2040
2041 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2042 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2043 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2044 poco_assert (2 == count);
2045
2046 _pSession->begin();
2047 try { (*_pSession) << "DELETE FROM Person", now; }
2048 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2049 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2050
2051 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2052 poco_assert (2 == locCount);
2053
2054 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2055 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2056 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2057 poco_assert (0 == count);
2058 _pSession->commit();
2059
2060 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2061 poco_assert (0 == locCount);
2062
2063 std::string sql1 = format("INSERT INTO Person VALUES ('%s','%s','%s',%d)", lastNames[0], firstNames[0], addresses[0], ages[0]);
2064 std::string sql2 = format("INSERT INTO Person VALUES ('%s','%s','%s',%d)", lastNames[1], firstNames[1], addresses[1], ages[1]);
2065 std::vector<std::string> sql;
2066 sql.push_back(sql1);
2067 sql.push_back(sql2);
2068
2069 Transaction trans((*_pSession));
2070
2071 trans.execute(sql1, false);
2072 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2073 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2074 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2075 poco_assert (1 == count);
2076 trans.execute(sql2, false);
2077 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2078 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2079 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2080 poco_assert (2 == count);
2081
2082 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2083 poco_assert (0 == locCount);
2084
2085 trans.rollback();
2086
2087 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2088 poco_assert (0 == locCount);
2089
2090 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2091 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2092 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2093 poco_assert (0 == count);
2094
2095 trans.execute(sql);
2096
2097 local << "SELECT COUNT(*) FROM Person", into(locCount), now;
2098 poco_assert (2 == locCount);
2099
2100 try { (*_pSession) << "SELECT count(*) FROM Person", into(count), now; }
2101 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2102 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2103 poco_assert (2 == count);
2104
2105 _pSession->setFeature("autoCommit", autoCommit);
2106}
2107
2108
2109void SQLExecutor::reconnect()
2110{
2111 std::string funct = "reconnect()";
2112 std::string lastName = "lastName";
2113 std::string firstName("firstName");
2114 std::string address("Address");
2115 int age = 133132;
2116 int count = 0;
2117 std::string result;
2118
2119 try { (*_pSession) << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(age), now; }
2120 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2121 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2122
2123 count = 0;
2124 try { (*_pSession) << "SELECT COUNT(*) FROM Person", into(count), now; }
2125 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2126 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2127 poco_assert (count == 1);
2128
2129 poco_assert (_pSession->isConnected());
2130 _pSession->close();
2131 poco_assert (!_pSession->isConnected());
2132 try
2133 {
2134 (*_pSession) << "SELECT LastName FROM Person", into(result), now;
2135 fail ("must fail");
2136 }
2137 catch(NotConnectedException&){ }
2138 poco_assert (!_pSession->isConnected());
2139
2140 _pSession->open();
2141 poco_assert (_pSession->isConnected());
2142 try { (*_pSession) << "SELECT Age FROM Person", into(count), now; }
2143 catch(ConnectionException& ce){ std::cout << ce.displayText() << std::endl; fail (funct); }
2144 catch(StatementException& se){ std::cout << se.displayText() << std::endl; fail (funct); }
2145 poco_assert (count == age);
2146 poco_assert (_pSession->isConnected());
2147}
2148