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