1 //
2 // SQLExecutor.cpp
3 //
4 // Copyright (c) 2006, 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/Format.h"
15 #include "Poco/Tuple.h"
16 #include "Poco/Nullable.h"
17 #include "Poco/Any.h"
18 #include "Poco/Dynamic/Var.h"
19 #include "Poco/DateTime.h"
20 #include "Poco/Stopwatch.h"
21 #include "Poco/NumberFormatter.h"
22 #include "Poco/Thread.h"
23 #include "Poco/Logger.h"
24 #include "Poco/Message.h"
25 #include "Poco/RefCountedObject.h"
26 #include "Poco/AutoPtr.h"
27 #include "Poco/SharedPtr.h"
28 #include "Poco/Exception.h"
29 #include "Poco/Data/Date.h"
30 #include "Poco/Data/Time.h"
31 #include "Poco/Data/LOB.h"
32 #include "Poco/Data/StatementImpl.h"
33 #include "Poco/Data/RecordSet.h"
34 #include "Poco/Data/RowIterator.h"
35 #include "Poco/Data/RowFilter.h"
36 #include "Poco/Data/BulkExtraction.h"
37 #include "Poco/Data/BulkBinding.h"
38 #include "Poco/Data/SQLChannel.h"
39 #include "Poco/Data/Transaction.h"
40 #include "Poco/Data/ODBC/Connector.h"
41 #include "Poco/Data/ODBC/Utility.h"
42 #include "Poco/Data/ODBC/Diagnostics.h"
43 #include "Poco/Data/ODBC/Error.h"
44 #include "Poco/Data/ODBC/Preparator.h"
45 #include "Poco/Data/ODBC/ODBCException.h"
46 #include "Poco/Data/ODBC/ODBCStatementImpl.h"
47 #include "Poco/UnicodeConverter.h"
48 #include "Poco/UTFString.h"
49 #include <sqltypes.h>
50 #include <iostream>
51 #include <sstream>
52 #include <iterator>
53
54
55 using namespace Poco::Data::Keywords;
56 using Poco::Data::Session;
57 using Poco::Data::Statement;
58 using Poco::Data::RecordSet;
59 using Poco::Data::Column;
60 using Poco::Data::Row;
61 using Poco::Data::RowFilter;
62 using Poco::Data::RowIterator;
63 using Poco::Data::SQLChannel;
64 using Poco::Data::LimitException;
65 using Poco::Data::BindingException;
66 using Poco::Data::CLOB;
67 using Poco::Data::Date;
68 using Poco::Data::Time;
69 using Poco::Data::Transaction;
70 using Poco::Data::NotConnectedException;
71 using Poco::Data::ODBC::Utility;
72 using Poco::Data::ODBC::Preparator;
73 using Poco::Data::ODBC::ConnectionException;
74 using Poco::Data::ODBC::StatementException;
75 using Poco::Data::ODBC::DataTruncatedException;
76 using Poco::Data::ODBC::StatementError;
77 using Poco::format;
78 using Poco::Tuple;
79 using Poco::Nullable;
80 using Poco::Any;
81 using Poco::AnyCast;
82 using Poco::Dynamic::Var;
83 using Poco::DateTime;
84 using Poco::Stopwatch;
85 using Poco::NumberFormatter;
86 using Poco::AutoPtr;
87 using Poco::Thread;
88 using Poco::Logger;
89 using Poco::Message;
90 using Poco::NotFoundException;
91 using Poco::InvalidAccessException;
92 using Poco::InvalidArgumentException;
93 using Poco::NotImplementedException;
94 using Poco::BadCastException;
95 using Poco::RangeException;
96 using Poco::TimeoutException;
97 using Poco::UnicodeConverter;
98 using Poco::UTF16String;
99 using Poco::UTF32String;
100
101 struct Person
102 {
103 std::string lastName;
104 std::string firstName;
105 std::string address;
106 int age;
PersonPerson107 Person(){age = 0;}
PersonPerson108 Person(const std::string& ln, const std::string& fn, const std::string& adr, int a):lastName(ln), firstName(fn), address(adr), age(a)
109 {
110 }
operator ==Person111 bool operator==(const Person& other) const
112 {
113 return lastName == other.lastName && firstName == other.firstName && address == other.address && age == other.age;
114 }
115
operator <Person116 bool operator < (const Person& p) const
117 {
118 if (age < p.age)
119 return true;
120 if (lastName < p.lastName)
121 return true;
122 if (firstName < p.firstName)
123 return true;
124 return (address < p.address);
125 }
126
operator ()Person127 const std::string& operator () () const
128 /// This method is required so we can extract data to a map!
129 {
130 // we choose the lastName as the key
131 return lastName;
132 }
133 };
134
135
136 struct RefCountedPerson : public Poco::RefCountedObject
137 {
138 std::string lastName;
139 std::string firstName;
140 std::string address;
141 int age;
RefCountedPersonRefCountedPerson142 RefCountedPerson(){age = 0;}
RefCountedPersonRefCountedPerson143 RefCountedPerson(const std::string& ln, const std::string& fn, const std::string& adr, int a):lastName(ln), firstName(fn), address(adr), age(a)
144 {
145 }
operator ==RefCountedPerson146 bool operator==(const Person& other) const
147 {
148 return lastName == other.lastName && firstName == other.firstName && address == other.address && age == other.age;
149 }
150
operator <RefCountedPerson151 bool operator < (const RefCountedPerson& p) const
152 {
153 if (age < p.age)
154 return true;
155 if (lastName < p.lastName)
156 return true;
157 if (firstName < p.firstName)
158 return true;
159 return (address < p.address);
160 }
161
operator ()RefCountedPerson162 const std::string& operator () () const
163 /// This method is required so we can extract data to a map!
164 {
165 // we choose the lastName as the key
166 return lastName;
167 }
168
169 private:
170 RefCountedPerson(const RefCountedPerson &);
171 RefCountedPerson& operator = (const RefCountedPerson&);
172 };
173
174
175 namespace Poco {
176 namespace Data {
177
178
179 template <>
180 class TypeHandler<Person>
181 {
182 public:
bind(std::size_t pos,const Person & obj,AbstractBinder::Ptr pBinder,AbstractBinder::Direction dir=AbstractBinder::PD_IN)183 static void bind(std::size_t pos,
184 const Person& obj,
185 AbstractBinder::Ptr pBinder,
186 AbstractBinder::Direction dir = AbstractBinder::PD_IN)
187 {
188 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
189 poco_assert_dbg (!pBinder.isNull());
190 pBinder->bind(pos++, obj.lastName, dir);
191 pBinder->bind(pos++, obj.firstName, dir);
192 pBinder->bind(pos++, obj.address, dir);
193 pBinder->bind(pos++, obj.age, dir);
194 }
195
prepare(std::size_t pos,const Person & obj,AbstractPreparator::Ptr pPrepare)196 static void prepare(std::size_t pos, const Person& obj, AbstractPreparator::Ptr pPrepare)
197 {
198 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
199 poco_assert_dbg (!pPrepare.isNull());
200 pPrepare->prepare(pos++, obj.lastName);
201 pPrepare->prepare(pos++, obj.firstName);
202 pPrepare->prepare(pos++, obj.address);
203 pPrepare->prepare(pos++, obj.age);
204 }
205
size()206 static std::size_t size()
207 {
208 return 4;
209 }
210
extract(std::size_t pos,Person & obj,const Person & defVal,AbstractExtractor::Ptr pExt)211 static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor::Ptr pExt)
212 {
213 poco_assert_dbg (!pExt.isNull());
214 if (!pExt->extract(pos++, obj.lastName))
215 obj.lastName = defVal.lastName;
216 if (!pExt->extract(pos++, obj.firstName))
217 obj.firstName = defVal.firstName;
218 if (!pExt->extract(pos++, obj.address))
219 obj.address = defVal.address;
220 if (!pExt->extract(pos++, obj.age))
221 obj.age = defVal.age;
222 }
223
224 private:
225 TypeHandler(const TypeHandler&);
226 TypeHandler& operator=(const TypeHandler&);
227 };
228
229
230 template <>
231 class TypeHandler<RefCountedPerson>
232 {
233 public:
bind(std::size_t pos,const RefCountedPerson & obj,AbstractBinder::Ptr pBinder,AbstractBinder::Direction dir=AbstractBinder::PD_IN)234 static void bind(std::size_t pos,
235 const RefCountedPerson& obj,
236 AbstractBinder::Ptr pBinder,
237 AbstractBinder::Direction dir = AbstractBinder::PD_IN)
238 {
239 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
240 poco_assert_dbg (!pBinder.isNull());
241 pBinder->bind(pos++, obj.lastName, dir);
242 pBinder->bind(pos++, obj.firstName, dir);
243 pBinder->bind(pos++, obj.address, dir);
244 pBinder->bind(pos++, obj.age, dir);
245 }
246
prepare(std::size_t pos,RefCountedPerson & obj,AbstractPreparator::Ptr pPrepare)247 static void prepare(std::size_t pos, RefCountedPerson& obj, AbstractPreparator::Ptr pPrepare)
248 {
249 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
250 poco_assert_dbg (!pPrepare.isNull());
251 pPrepare->prepare(pos++, obj.lastName);
252 pPrepare->prepare(pos++, obj.firstName);
253 pPrepare->prepare(pos++, obj.address);
254 pPrepare->prepare(pos++, obj.age);
255 }
256
size()257 static std::size_t size()
258 {
259 return 4;
260 }
261
extract(std::size_t pos,RefCountedPerson & obj,const RefCountedPerson & defVal,AbstractExtractor::Ptr pExt)262 static void extract(std::size_t pos, RefCountedPerson& obj, const RefCountedPerson& defVal, AbstractExtractor::Ptr pExt)
263 {
264 poco_assert_dbg (!pExt.isNull());
265 if (!pExt->extract(pos++, obj.lastName))
266 obj.lastName = defVal.lastName;
267 if (!pExt->extract(pos++, obj.firstName))
268 obj.firstName = defVal.firstName;
269 if (!pExt->extract(pos++, obj.address))
270 obj.address = defVal.address;
271 if (!pExt->extract(pos++, obj.age))
272 obj.age = defVal.age;
273 }
274
275 private:
276 TypeHandler(const TypeHandler&);
277 TypeHandler& operator=(const TypeHandler&);
278 };
279
280
281 } } // namespace Poco::Data
282
283
284 const std::string SQLExecutor::MULTI_INSERT =
285 "INSERT INTO Test VALUES ('1', 2, 3.5);"
286 "INSERT INTO Test VALUES ('2', 3, 4.5);"
287 "INSERT INTO Test VALUES ('3', 4, 5.5);"
288 "INSERT INTO Test VALUES ('4', 5, 6.5);"
289 "INSERT INTO Test VALUES ('5', 6, 7.5);";
290
291 const std::string SQLExecutor::MULTI_SELECT =
292 "SELECT * FROM Test WHERE First = '1';"
293 "SELECT * FROM Test WHERE First = '2';"
294 "SELECT * FROM Test WHERE First = '3';"
295 "SELECT * FROM Test WHERE First = '4';"
296 "SELECT * FROM Test WHERE First = '5';";
297
298
SQLExecutor(const std::string & name,Poco::Data::Session * pSession)299 SQLExecutor::SQLExecutor(const std::string& name, Poco::Data::Session* pSession):
300 CppUnit::TestCase(name),
301 _pSession(pSession)
302 {
303 }
304
305
~SQLExecutor()306 SQLExecutor::~SQLExecutor()
307 {
308 }
309
310
bareboneODBCTest(const std::string & dbConnString,const std::string & tableCreateString,SQLExecutor::DataBinding bindMode,SQLExecutor::DataExtraction extractMode,bool doTime,const std::string & blobPlaceholder)311 void SQLExecutor::bareboneODBCTest(const std::string& dbConnString,
312 const std::string& tableCreateString,
313 SQLExecutor::DataBinding bindMode,
314 SQLExecutor::DataExtraction extractMode,
315 bool doTime,
316 const std::string& blobPlaceholder)
317 {
318 SQLRETURN rc;
319 SQLHENV henv = SQL_NULL_HENV;
320 SQLHDBC hdbc = SQL_NULL_HDBC;
321 SQLHSTMT hstmt = SQL_NULL_HSTMT;
322
323 // Environment begin
324 rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
325 poco_odbc_check_env (rc, henv);
326 rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
327 poco_odbc_check_env (rc, henv);
328
329 // Connection begin
330 rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
331 poco_odbc_check_dbc (rc, hdbc);
332
333 SQLCHAR connectOutput[1024] = {0};
334 SQLSMALLINT result;
335 rc = SQLDriverConnect(hdbc
336 , NULL
337 ,(SQLCHAR*) dbConnString.c_str()
338 ,(SQLSMALLINT) SQL_NTS
339 , connectOutput
340 , sizeof(connectOutput)
341 , &result
342 , SQL_DRIVER_NOPROMPT);
343 poco_odbc_check_dbc (rc, hdbc);
344
345 // retrieve datetime type information for this DBMS
346 rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
347 poco_odbc_check_stmt (rc, hstmt);
348
349 rc = SQLGetTypeInfo(hstmt, SQL_TYPE_TIMESTAMP);
350 poco_odbc_check_stmt (rc, hstmt);
351
352 rc = SQLFetch(hstmt);
353 assertTrue (SQL_SUCCEEDED(rc) || SQL_NO_DATA == rc);
354
355 SQLULEN dateTimeColSize = 0;
356 SQLSMALLINT dateTimeDecDigits = 0;
357 if (SQL_SUCCEEDED(rc))
358 {
359 SQLLEN ind = 0;
360 rc = SQLGetData(hstmt, 3, SQL_C_SLONG, &dateTimeColSize, sizeof(SQLINTEGER), &ind);
361 poco_odbc_check_stmt (rc, hstmt);
362 rc = SQLGetData(hstmt, 14, SQL_C_SSHORT, &dateTimeDecDigits, sizeof(SQLSMALLINT), &ind);
363 poco_odbc_check_stmt (rc, hstmt);
364
365 assertTrue (sizeof(SQL_TIMESTAMP_STRUCT) <= dateTimeColSize);
366 }
367 else if (SQL_NO_DATA == rc)
368 std::cerr << '[' << name() << ']' << " Warning: no SQL_TYPE_TIMESTAMP data type info returned by driver." << std::endl;
369
370 rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
371 poco_odbc_check_stmt (rc, hstmt);
372
373 // Statement begin
374 rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
375 poco_odbc_check_stmt (rc, hstmt);
376
377 std::string sql = "DROP TABLE Test";
378 SQLCHAR* pStr = (SQLCHAR*) sql.c_str();
379 SQLExecDirect(hstmt, pStr, (SQLINTEGER) sql.length());
380 //no return code check - ignore drop errors
381
382 // create table and go
383 sql = tableCreateString;
384 pStr = (SQLCHAR*) sql.c_str();
385 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
386 poco_odbc_check_stmt (rc, hstmt);
387
388 rc = SQLExecute(hstmt);
389 poco_odbc_check_stmt (rc, hstmt);
390
391 sql = format("INSERT INTO Test VALUES (?,?,%s,?,?,?)", blobPlaceholder);
392 pStr = (SQLCHAR*) sql.c_str();
393 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
394 poco_odbc_check_stmt (rc, hstmt);
395
396 std::string str[3] = { "11111111111", "222222222222222222222222", "333333333333333333333333333" };
397 int fourth = 4;
398 float fifth = 1.5;
399 SQL_TIMESTAMP_STRUCT sixth;
400 sixth.year = 1965;
401 sixth.month = 6;
402 sixth.day = 18;
403 sixth.hour = 5;
404 sixth.minute = 34;
405 sixth.second = 58;
406 // Fraction support is limited to milliseconds due to MS SQL Server limitation
407 // see http://support.microsoft.com/kb/263872
408 sixth.fraction = 997000000;
409
410 SQLLEN li[3] = { SQL_NTS, SQL_NTS, 0 };
411 SQLINTEGER size = (SQLINTEGER) str[0].size();
412
413 if (SQLExecutor::PB_AT_EXEC == bindMode)
414 li[0] = SQL_LEN_DATA_AT_EXEC(size);
415
416 rc = SQLBindParameter(hstmt,
417 (SQLUSMALLINT) 1,
418 SQL_PARAM_INPUT,
419 SQL_C_CHAR,
420 SQL_LONGVARCHAR,
421 (SQLUINTEGER) size,
422 0,
423 (SQLPOINTER) str[0].c_str(),
424 size,
425 &li[0]);
426 poco_odbc_check_stmt (rc, hstmt);
427
428 size = (SQLINTEGER) str[1].size();
429 if (SQLExecutor::PB_AT_EXEC == bindMode)
430 li[1] = SQL_LEN_DATA_AT_EXEC(size);
431 else li[1] = SQL_NTS;
432
433 rc = SQLBindParameter(hstmt,
434 (SQLUSMALLINT) 2,
435 SQL_PARAM_INPUT,
436 SQL_C_CHAR,
437 SQL_LONGVARCHAR,
438 (SQLUINTEGER) size,
439 0,
440 (SQLPOINTER) str[1].c_str(),
441 size,
442 &li[1]);
443 poco_odbc_check_stmt (rc, hstmt);
444
445 size = (SQLINTEGER) str[2].size();
446 if (SQLExecutor::PB_AT_EXEC == bindMode)
447 li[2] = SQL_LEN_DATA_AT_EXEC(size);
448 else li[2] = size;
449
450 rc = SQLBindParameter(hstmt,
451 (SQLUSMALLINT) 3,
452 SQL_PARAM_INPUT,
453 SQL_C_BINARY,
454 SQL_LONGVARBINARY,
455 (SQLUINTEGER) size,
456 0,
457 (SQLPOINTER) str[2].data(),
458 size,
459 &li[2]);
460 poco_odbc_check_stmt (rc, hstmt);
461
462 rc = SQLBindParameter(hstmt,
463 (SQLUSMALLINT) 4,
464 SQL_PARAM_INPUT,
465 SQL_C_SLONG,
466 SQL_INTEGER,
467 0,
468 0,
469 (SQLPOINTER) &fourth,
470 0,
471 0);
472 poco_odbc_check_stmt (rc, hstmt);
473
474 rc = SQLBindParameter(hstmt,
475 (SQLUSMALLINT) 5,
476 SQL_PARAM_INPUT,
477 SQL_C_FLOAT,
478 SQL_REAL,
479 0,
480 1,
481 (SQLPOINTER) &fifth,
482 0,
483 0);
484 poco_odbc_check_stmt (rc, hstmt);
485
486 SQLSMALLINT dataType = 0;
487 SQLULEN parameterSize = 0;
488 SQLSMALLINT decimalDigits = 0;
489 SQLSMALLINT nullable = 0;
490 rc = SQLDescribeParam(hstmt, 6, &dataType, ¶meterSize, &decimalDigits, &nullable);
491 if (SQL_SUCCEEDED(rc))
492 {
493 if (parameterSize)
494 dateTimeColSize = parameterSize;
495 if (decimalDigits)
496 dateTimeDecDigits = decimalDigits;
497 }
498 else
499 std::cerr << '[' << name() << ']' << " Warning: could not get SQL_TYPE_TIMESTAMP parameter description." << std::endl;
500
501 rc = SQLBindParameter(hstmt,
502 (SQLUSMALLINT) 6,
503 SQL_PARAM_INPUT,
504 SQL_C_TYPE_TIMESTAMP,
505 SQL_TYPE_TIMESTAMP,
506 dateTimeColSize,
507 dateTimeDecDigits,
508 (SQLPOINTER) &sixth,
509 0,
510 0);
511 poco_odbc_check_stmt (rc, hstmt);
512
513 rc = SQLExecute(hstmt);
514 assertTrue (SQL_NEED_DATA == rc || SQL_SUCCEEDED(rc));
515
516 if (SQL_NEED_DATA == rc)
517 {
518 SQLPOINTER pParam = 0;
519 while (SQL_NEED_DATA == (rc = SQLParamData(hstmt, &pParam)))
520 {
521 SQLINTEGER dataSize = 0;
522 // Data size should be ignored for non-null,
523 // non-variable length fields, but SQLite ODBC
524 // driver insists on it always being the actual
525 // data length
526
527 if (pParam == (SQLPOINTER) str[0].c_str())
528 dataSize = (SQLINTEGER) str[0].size();
529 else if (pParam == (SQLPOINTER) str[1].c_str())
530 dataSize = (SQLINTEGER) str[1].size();
531 else if (pParam == (SQLPOINTER) str[2].c_str())
532 dataSize = (SQLINTEGER) str[2].size();
533 else if (pParam == (SQLPOINTER) &fourth)
534 dataSize = (SQLINTEGER) sizeof(fourth);
535 else if (pParam == (SQLPOINTER) &fifth)
536 dataSize = (SQLINTEGER) sizeof(fifth);
537 else if (pParam == (SQLPOINTER) &sixth)
538 dataSize = (SQLINTEGER) sizeof(sixth);
539
540 assertTrue (0 != dataSize);
541 rc = SQLPutData(hstmt, pParam, dataSize);
542 poco_odbc_check_stmt (rc, hstmt);
543 }
544 }
545 poco_odbc_check_stmt (rc, hstmt);
546
547 sql = "SELECT * FROM Test";
548 pStr = (SQLCHAR*) sql.c_str();
549 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
550 poco_odbc_check_stmt (rc, hstmt);
551
552 char chr[3][50] = {{ 0 }};
553 SQLLEN lengths[6] = { 0 };
554 fourth = 0;
555 fifth = 0.0f;
556 std::memset(&sixth, 0, sizeof(sixth));
557
558 if (SQLExecutor::DE_BOUND == extractMode)
559 {
560 rc = SQLBindCol(hstmt,
561 (SQLUSMALLINT) 1,
562 SQL_C_CHAR,
563 (SQLPOINTER) chr[0],
564 (SQLINTEGER) sizeof(chr[0]),
565 &lengths[0]);
566 poco_odbc_check_stmt (rc, hstmt);
567
568 rc = SQLBindCol(hstmt,
569 (SQLUSMALLINT) 2,
570 SQL_C_CHAR,
571 (SQLPOINTER) chr[1],
572 (SQLINTEGER) sizeof(chr[1]),
573 &lengths[1]);
574 poco_odbc_check_stmt (rc, hstmt);
575
576 rc = SQLBindCol(hstmt,
577 (SQLUSMALLINT) 3,
578 SQL_C_BINARY,
579 (SQLPOINTER) chr[2],
580 (SQLINTEGER) sizeof(chr[2]),
581 &lengths[2]);
582 poco_odbc_check_stmt (rc, hstmt);
583
584 rc = SQLBindCol(hstmt,
585 (SQLUSMALLINT) 4,
586 SQL_C_SLONG,
587 (SQLPOINTER) &fourth,
588 (SQLINTEGER) 0,
589 &lengths[3]);
590 poco_odbc_check_stmt (rc, hstmt);
591
592 rc = SQLBindCol(hstmt,
593 (SQLUSMALLINT) 5,
594 SQL_C_FLOAT,
595 (SQLPOINTER) &fifth,
596 (SQLINTEGER) 0,
597 &lengths[4]);
598 poco_odbc_check_stmt (rc, hstmt);
599
600 rc = SQLBindCol(hstmt,
601 (SQLUSMALLINT) 6,
602 SQL_C_TYPE_TIMESTAMP,
603 (SQLPOINTER) &sixth,
604 (SQLINTEGER) 0,
605 &lengths[5]);
606 poco_odbc_check_stmt (rc, hstmt);
607 }
608
609 rc = SQLExecute(hstmt);
610 poco_odbc_check_stmt (rc, hstmt);
611 rc = SQLFetch(hstmt);
612 poco_odbc_check_stmt (rc, hstmt);
613
614 if (SQLExecutor::DE_MANUAL == extractMode)
615 {
616 SQLLEN len = lengths[0] = 0;
617 while (SQL_SUCCESS_WITH_INFO == (rc = SQLGetData(hstmt,
618 (SQLUSMALLINT) 1,
619 SQL_C_CHAR,
620 chr[0] + len,
621 sizeof(chr[0]) - len,
622 &lengths[0])))
623 {
624 len += lengths[0];
625 if (!lengths[0] || len >= sizeof(chr[1]))
626 break;
627 }
628 poco_odbc_check_stmt (rc, hstmt);
629
630 len = lengths[1] = 0;
631 while (SQL_SUCCESS_WITH_INFO == (rc = SQLGetData(hstmt,
632 (SQLUSMALLINT) 2,
633 SQL_C_CHAR,
634 chr[1] + len,
635 sizeof(chr[1]) - len,
636 &lengths[1])))
637 {
638 len += lengths[1];
639 if (!lengths[1] || len >= sizeof(chr[1]))
640 break;
641 }
642 poco_odbc_check_stmt (rc, hstmt);
643
644 len = lengths[2] = 0;
645 while (SQL_SUCCESS_WITH_INFO == (rc = SQLGetData(hstmt,
646 (SQLUSMALLINT) 3,
647 SQL_C_BINARY,
648 chr[2] + len,
649 sizeof(chr[2]) - len,
650 &lengths[2])))
651 {
652 len += lengths[1];
653 if (!lengths[2] || len >= sizeof(chr[2]))
654 break;
655 }
656 poco_odbc_check_stmt (rc, hstmt);
657
658 rc = SQLGetData(hstmt,
659 (SQLUSMALLINT) 4,
660 SQL_C_SLONG,
661 &fourth,
662 0,
663 &lengths[3]);
664 poco_odbc_check_stmt (rc, hstmt);
665
666 rc = SQLGetData(hstmt,
667 (SQLUSMALLINT) 5,
668 SQL_C_FLOAT,
669 &fifth,
670 0,
671 &lengths[4]);
672 poco_odbc_check_stmt (rc, hstmt);
673
674 rc = SQLGetData(hstmt,
675 (SQLUSMALLINT) 6,
676 SQL_C_TYPE_TIMESTAMP,
677 &sixth,
678 0,
679 &lengths[5]);
680 poco_odbc_check_stmt (rc, hstmt);
681 }
682
683 assertTrue (0 == std::strncmp(str[0].c_str(), chr[0], str[0].size()));
684 assertTrue (0 == std::strncmp(str[1].c_str(), chr[1], str[1].size()));
685 assertTrue (0 == std::strncmp(str[2].c_str(), chr[2], str[2].size()));
686 assertTrue (4 == fourth);
687 assertTrue (1.5 == fifth);
688
689 assertTrue (1965 == sixth.year);
690 assertTrue (6 == sixth.month);
691 assertTrue (18 == sixth.day);
692 if (doTime)
693 {
694 assertTrue (5 == sixth.hour);
695 assertTrue (34 == sixth.minute);
696 if (sixth.fraction) // MySQL rounds fraction
697 {
698 assertTrue (58 == sixth.second);
699 assertTrue (997000000 == sixth.fraction);
700 }
701 else
702 {
703 assertTrue (59 == sixth.second);
704 }
705 }
706
707 rc = SQLCloseCursor(hstmt);
708 poco_odbc_check_stmt (rc, hstmt);
709
710 sql = "DROP TABLE Test";
711 pStr = (SQLCHAR*) sql.c_str();
712 rc = SQLExecDirect(hstmt, pStr, (SQLINTEGER) sql.length());
713 poco_odbc_check_stmt (rc, hstmt);
714
715 rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
716 poco_odbc_check_stmt (rc, hstmt);
717
718 // Connection end
719 rc = SQLDisconnect(hdbc);
720 poco_odbc_check_dbc (rc, hdbc);
721 rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
722 poco_odbc_check_dbc (rc, hdbc);
723
724 // Environment end
725 rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
726 poco_odbc_check_env (rc, henv);
727 }
728
729
bareboneODBCMultiResultTest(const std::string & dbConnString,const std::string & tableCreateString,SQLExecutor::DataBinding bindMode,SQLExecutor::DataExtraction extractMode,const std::string & insert,const std::string & select)730 void SQLExecutor::bareboneODBCMultiResultTest(const std::string& dbConnString,
731 const std::string& tableCreateString,
732 SQLExecutor::DataBinding bindMode,
733 SQLExecutor::DataExtraction extractMode,
734 const std::string& insert,
735 const std::string& select)
736 {
737 SQLRETURN rc;
738 SQLHENV henv = SQL_NULL_HENV;
739 SQLHDBC hdbc = SQL_NULL_HDBC;
740 SQLHSTMT hstmt = SQL_NULL_HSTMT;
741
742 // Environment begin
743 rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
744 poco_odbc_check_stmt (rc, hstmt);
745 rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
746 poco_odbc_check_stmt (rc, hstmt);
747
748 // Connection begin
749 rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
750 poco_odbc_check_stmt (rc, hstmt);
751
752 SQLCHAR connectOutput[512] = {0};
753 SQLSMALLINT result;
754 rc = SQLDriverConnect(hdbc
755 , NULL
756 ,(SQLCHAR*) dbConnString.c_str()
757 ,(SQLSMALLINT) SQL_NTS
758 , connectOutput
759 , sizeof(connectOutput)
760 , &result
761 , SQL_DRIVER_NOPROMPT);
762 poco_odbc_check_stmt (rc, hstmt);
763
764 // Statement begin
765 rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
766 poco_odbc_check_stmt (rc, hstmt);
767
768 std::string sql = "DROP TABLE Test";
769 SQLCHAR* pStr = (SQLCHAR*) sql.c_str();
770 SQLExecDirect(hstmt, pStr, (SQLINTEGER) sql.length());
771 //no return code check - ignore drop errors
772
773 // create table and go
774 sql = tableCreateString;
775 pStr = (SQLCHAR*) sql.c_str();
776 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
777 poco_odbc_check_stmt (rc, hstmt);
778
779 rc = SQLExecute(hstmt);
780 poco_odbc_check_stmt (rc, hstmt);
781
782 // insert multiple rows
783 pStr = (SQLCHAR*) insert.c_str();
784 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) insert.length());
785 poco_odbc_check_stmt (rc, hstmt);
786 rc = SQLExecute(hstmt);
787 poco_odbc_check_stmt (rc, hstmt);
788 do
789 {
790 SQLLEN rowCount = 0;
791 SQLRowCount(hstmt, &rowCount);
792 assertTrue (1 == rowCount);
793 } while (SQL_NO_DATA != SQLMoreResults(hstmt));
794
795 // make sure all five rows made it in
796 sql = "select count(*) from Test";
797 int count = 0;
798 SQLLEN length = 0;
799 pStr = (SQLCHAR*) sql.c_str();
800 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
801 poco_odbc_check_stmt (rc, hstmt);
802 if (SQLExecutor::DE_BOUND == extractMode)
803 {
804 rc = SQLBindCol(hstmt,
805 (SQLUSMALLINT) 1,
806 SQL_C_SLONG,
807 (SQLPOINTER) &count,
808 (SQLINTEGER) 0,
809 &length);
810 poco_odbc_check_stmt (rc, hstmt);
811 }
812
813 rc = SQLExecute(hstmt);
814 poco_odbc_check_stmt (rc, hstmt);
815
816 rc = SQLFetch(hstmt);
817 poco_odbc_check_stmt (rc, hstmt);
818
819 if (SQLExecutor::DE_MANUAL == extractMode)
820 {
821 rc = SQLGetData(hstmt,
822 (SQLUSMALLINT) 1,
823 SQL_C_SLONG,
824 &count,
825 0,
826 &length);
827 poco_odbc_check_stmt (rc, hstmt);
828 }
829 assertTrue (5 == count);
830
831 rc = SQLCloseCursor(hstmt);
832 poco_odbc_check_stmt (rc, hstmt);
833
834 // select multiple rows
835 pStr = (SQLCHAR*) select.c_str();
836 rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) select.length());
837 poco_odbc_check_stmt (rc, hstmt);
838
839 char chr[5] = { 0 };
840 SQLLEN lengths[3] = { 0 };
841 int second = 0;
842 float third = 0.0f;
843
844 if (SQLExecutor::DE_BOUND == extractMode)
845 {
846 rc = SQLBindCol(hstmt,
847 (SQLUSMALLINT) 1,
848 SQL_C_CHAR,
849 (SQLPOINTER) chr,
850 (SQLINTEGER) 4,
851 &lengths[0]);
852 poco_odbc_check_stmt (rc, hstmt);
853
854 rc = SQLBindCol(hstmt,
855 (SQLUSMALLINT) 2,
856 SQL_C_SLONG,
857 (SQLPOINTER) &second,
858 (SQLINTEGER) 0,
859 &lengths[1]);
860 poco_odbc_check_stmt (rc, hstmt);
861
862 rc = SQLBindCol(hstmt,
863 (SQLUSMALLINT) 3,
864 SQL_C_FLOAT,
865 (SQLPOINTER) &third,
866 (SQLINTEGER) 0,
867 &lengths[2]);
868 poco_odbc_check_stmt (rc, hstmt);
869 }
870
871 rc = SQLExecute(hstmt);
872 poco_odbc_check_stmt (rc, hstmt);
873
874 char one = 0x31;
875 int two = 2;
876 float three = 3.5;
877 count = 0;
878
879 do
880 {
881 rc = SQLFetch(hstmt);
882 poco_odbc_check_stmt (rc, hstmt);
883
884 if (SQLExecutor::DE_MANUAL == extractMode)
885 {
886 rc = SQLGetData(hstmt,
887 (SQLUSMALLINT) 1,
888 SQL_C_CHAR,
889 chr,
890 4,
891 &lengths[0]);
892 poco_odbc_check_stmt (rc, hstmt);
893
894 rc = SQLGetData(hstmt,
895 (SQLUSMALLINT) 2,
896 SQL_C_SLONG,
897 &second,
898 0,
899 &lengths[1]);
900 poco_odbc_check_stmt (rc, hstmt);
901
902 rc = SQLGetData(hstmt,
903 (SQLUSMALLINT) 3,
904 SQL_C_FLOAT,
905 &third,
906 0,
907 &lengths[2]);
908 poco_odbc_check_stmt (rc, hstmt);
909 }
910
911 assertTrue (one++ == chr[0]);
912 assertTrue (two++ == second);
913 assertTrue (three == third);
914 three += 1.0;
915
916 ++count;
917 } while (SQL_NO_DATA != SQLMoreResults(hstmt));
918
919 assertTrue (5 == count);
920
921 sql = "DROP TABLE Test";
922 pStr = (SQLCHAR*) sql.c_str();
923 rc = SQLExecDirect(hstmt, pStr, (SQLINTEGER) sql.length());
924 poco_odbc_check_stmt (rc, hstmt);
925
926 rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
927 poco_odbc_check_stmt (rc, hstmt);
928
929 // Connection end
930 rc = SQLDisconnect(hdbc);
931 poco_odbc_check_stmt (rc, hstmt);
932 rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
933 poco_odbc_check_stmt (rc, hstmt);
934
935 // Environment end
936 rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
937 poco_odbc_check_stmt (rc, hstmt);
938 }
939
940
execute(const std::string & sql)941 void SQLExecutor::execute(const std::string& sql)
942 {
943 try { session() << sql, now; }
944 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (sql); }
945 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (sql); }
946 }
947
948
zeroRows()949 void SQLExecutor::zeroRows()
950 {
951 Statement stmt = (session() << "SELECT * FROM Person WHERE 0 = 1");
952 assertTrue (0 == stmt.execute());
953 }
954
955
simpleAccess()956 void SQLExecutor::simpleAccess()
957 {
958 std::string funct = "simpleAccess()";
959 std::string lastName = "lastName";
960 std::string firstName("firstName");
961 std::string address("Address");
962 int age = 133132;
963 int count = 0;
964 std::string result;
965
966 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(age), now; }
967 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
968 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
969
970 count = 0;
971 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
972 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
973 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
974 assertTrue (count == 1);
975
976 try { session() << "SELECT LastName FROM Person", into(result), now; }
977 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
978 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
979 assertTrue (lastName == result);
980
981 try { session() << "SELECT Age FROM Person", into(count), now; }
982 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
983 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
984 assertTrue (count == age);
985 }
986
987
complexType()988 void SQLExecutor::complexType()
989 {
990 std::string funct = "complexType()";
991 Person p1("LN1", "FN1", "ADDR1", 1);
992 Person p2("LN2", "FN2", "ADDR2", 2);
993
994 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(p1), now; }
995 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
996 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
997
998 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(p2), now; }
999 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1000 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1001
1002 int count = 0;
1003 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1004 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1005 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1006 assertTrue (count == 2);
1007
1008 Person c1;
1009 try { session() << "SELECT * FROM Person WHERE LastName = 'LN1'", into(c1), now; }
1010 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1011 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1012 assertTrue (c1 == p1);
1013 }
1014
1015
complexTypeTuple()1016 void SQLExecutor::complexTypeTuple()
1017 {
1018 std::string funct = "complexTypeTuple()";
1019 Person p1("LN1", "FN1", "ADDR1", 1);
1020 Person p2("LN2", "FN2", "ADDR2", 2);
1021
1022 Tuple<Person,Person> t(p1,p2);
1023 try { *_pSession << "INSERT INTO Person VALUES(?,?,?,?,?,?,?,?)", use(t), now; }
1024 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1025 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1026
1027 Tuple<Person,Person> ret;
1028 assertTrue (ret != t);
1029 try { *_pSession << "SELECT * FROM Person", into(ret), now; }
1030 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1031 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1032 assertTrue (ret == t);
1033 }
1034
1035
simpleAccessVector()1036 void SQLExecutor::simpleAccessVector()
1037 {
1038 std::string funct = "simpleAccessVector()";
1039 std::vector<std::string> lastNames;
1040 std::vector<std::string> firstNames;
1041 std::vector<std::string> addresses;
1042 std::vector<int> ages;
1043 std::string tableName("Person");
1044 lastNames.push_back("LN1");
1045 lastNames.push_back("LN2");
1046 firstNames.push_back("FN1");
1047 firstNames.push_back("FN2");
1048 addresses.push_back("ADDR1");
1049 addresses.push_back("ADDR2");
1050 ages.push_back(1);
1051 ages.push_back(2);
1052 int count = 0;
1053 std::string result;
1054
1055 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1056 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1057 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1058
1059 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1060 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1061 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1062 assertTrue (count == 2);
1063
1064 std::vector<std::string> lastNamesR;
1065 std::vector<std::string> firstNamesR;
1066 std::vector<std::string> addressesR;
1067 std::vector<int> agesR;
1068 try { session() << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
1069 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1070 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1071 assertTrue (ages == agesR);
1072 assertTrue (lastNames == lastNamesR);
1073 assertTrue (firstNames == firstNamesR);
1074 assertTrue (addresses == addressesR);
1075 }
1076
1077
complexTypeVector()1078 void SQLExecutor::complexTypeVector()
1079 {
1080 std::string funct = "complexTypeVector()";
1081 std::vector<Person> people;
1082 people.push_back(Person("LN1", "FN1", "ADDR1", 1));
1083 people.push_back(Person("LN2", "FN2", "ADDR2", 2));
1084
1085 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1086 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1087 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1088
1089 int count = 0;
1090 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1091 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1092 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1093 assertTrue (count == 2);
1094
1095 std::vector<Person> result;
1096 try { session() << "SELECT * FROM Person", into(result), now; }
1097 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1098 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1099 assertTrue (result == people);
1100 }
1101
1102
sharedPtrComplexTypeVector()1103 void SQLExecutor::sharedPtrComplexTypeVector()
1104 {
1105 std::string funct = "sharedPtrComplexTypeVector()";
1106 std::vector<Poco::SharedPtr<Person> > people;
1107 people.push_back(new Person("LN1", "FN1", "ADDR1", 1));
1108 people.push_back(new Person("LN2", "FN2", "ADDR2", 2));
1109
1110 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1111 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1112 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1113
1114 int count = 0;
1115 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1116 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1117 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1118 assertTrue (count == 2);
1119
1120 std::vector<Person> result;
1121 try { session() << "SELECT * FROM Person", into(result), now; }
1122 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1123 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1124 assertTrue (2 == result.size());
1125 assertTrue (result[0] == *people[0]);
1126 assertTrue (result[1] == *people[1]);
1127 }
1128
1129
autoPtrComplexTypeVector()1130 void SQLExecutor::autoPtrComplexTypeVector()
1131 {
1132 std::string funct = "sharedPtrComplexTypeVector()";
1133 std::vector<Poco::AutoPtr<RefCountedPerson> > people;
1134 people.push_back(new RefCountedPerson("LN1", "FN1", "ADDR1", 1));
1135 people.push_back(new RefCountedPerson("LN2", "FN2", "ADDR2", 2));
1136
1137 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1138 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1139 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1140
1141 int count = 0;
1142 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1143 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1144 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1145 assertTrue (count == 2);
1146
1147 std::vector<Person> result;
1148 try { session() << "SELECT * FROM Person", into(result), now; }
1149 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1150 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1151 assertTrue (2 == result.size());
1152 assertTrue (result[0].address == people[0]->address);
1153 assertTrue (result[0].age == people[0]->age);
1154 assertTrue (result[0].firstName == people[0]->firstName);
1155 assertTrue (result[0].lastName == people[0]->lastName);
1156 assertTrue (result[1].address == people[1]->address);
1157 assertTrue (result[1].age == people[1]->age);
1158 assertTrue (result[1].firstName == people[1]->firstName);
1159 assertTrue (result[1].lastName == people[1]->lastName);
1160 }
1161
1162
insertVector()1163 void SQLExecutor::insertVector()
1164 {
1165 std::string funct = "insertVector()";
1166 std::vector<std::string> str;
1167 str.push_back("s1");
1168 str.push_back("s2");
1169 str.push_back("s3");
1170 str.push_back("s3");
1171 int count = 100;
1172
1173 {
1174 Statement stmt((session() << "INSERT INTO Strings VALUES (?)", use(str)));
1175 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1176 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1177 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1178 assertTrue (count == 0);
1179
1180 try { stmt.execute(); }
1181 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1182
1183 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1184 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1185 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1186 assertTrue (count == 4);
1187 }
1188 count = 0;
1189 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1190 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1191 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1192 assertTrue (count == 4);
1193 }
1194
1195
insertEmptyVector()1196 void SQLExecutor::insertEmptyVector()
1197 {
1198 std::string funct = "insertEmptyVector()";
1199 std::vector<std::string> str;
1200
1201 try
1202 {
1203 session() << "INSERT INTO Strings VALUES (?)", use(str), now;
1204 fail("empty collections should not work");
1205 }
1206 catch (Poco::Exception&)
1207 {
1208 }
1209 }
1210
1211
simpleAccessList()1212 void SQLExecutor::simpleAccessList()
1213 {
1214 std::string funct = "simpleAccessList()";
1215 std::list<std::string> lastNames;
1216 std::list<std::string> firstNames;
1217 std::list<std::string> addresses;
1218 std::list<int> ages;
1219 std::string tableName("Person");
1220 lastNames.push_back("LN1");
1221 lastNames.push_back("LN2");
1222 firstNames.push_back("FN1");
1223 firstNames.push_back("FN2");
1224 addresses.push_back("ADDR1");
1225 addresses.push_back("ADDR2");
1226 ages.push_back(1);
1227 ages.push_back(2);
1228 int count = 0;
1229 std::string result;
1230
1231 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1232 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1233 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1234
1235 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1236 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1237 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1238 assertTrue (count == 2);
1239
1240 std::list<std::string> lastNamesR;
1241 std::list<std::string> firstNamesR;
1242 std::list<std::string> addressesR;
1243 std::list<int> agesR;
1244 try { session() << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
1245 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1246 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1247 assertTrue (ages == agesR);
1248 assertTrue (lastNames == lastNamesR);
1249 assertTrue (firstNames == firstNamesR);
1250 assertTrue (addresses == addressesR);
1251 }
1252
1253
complexTypeList()1254 void SQLExecutor::complexTypeList()
1255 {
1256 std::string funct = "complexTypeList()";
1257 std::list<Person> people;
1258 people.push_back(Person("LN1", "FN1", "ADDR1", 1));
1259 people.push_back(Person("LN2", "FN2", "ADDR2", 2));
1260
1261 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1262 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1263 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1264
1265 int count = 0;
1266 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1267 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1268 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1269 assertTrue (count == 2);
1270
1271 std::list<Person> result;
1272 try { session() << "SELECT * FROM Person", into(result), now; }
1273 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1274 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1275 assertTrue (result == people);
1276 }
1277
1278
insertList()1279 void SQLExecutor::insertList()
1280 {
1281 std::string funct = "insertList()";
1282 std::list<std::string> str;
1283 str.push_back("s1");
1284 str.push_back("s2");
1285 str.push_back("s3");
1286 str.push_back("s3");
1287 int count = 100;
1288
1289 {
1290 Statement stmt((session() << "INSERT INTO Strings VALUES (?)", use(str)));
1291 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1292 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1293 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1294 assertTrue (count == 0);
1295
1296 try { stmt.execute(); }
1297 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1298
1299 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1300 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1301 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1302 assertTrue (count == 4);
1303 }
1304 count = 0;
1305 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1306 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1307 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1308 assertTrue (count == 4);
1309 }
1310
1311
insertEmptyList()1312 void SQLExecutor::insertEmptyList()
1313 {
1314 std::string funct = "insertEmptyList()";
1315 std::list<std::string> str;
1316
1317 try
1318 {
1319 session() << "INSERT INTO Strings VALUES (?)", use(str), now;
1320 fail("empty collections should not work");
1321 }
1322 catch (Poco::Exception&)
1323 {
1324 }
1325 }
1326
1327
simpleAccessDeque()1328 void SQLExecutor::simpleAccessDeque()
1329 {
1330 std::string funct = "simpleAccessDeque()";
1331 std::deque<std::string> lastNames;
1332 std::deque<std::string> firstNames;
1333 std::deque<std::string> addresses;
1334 std::deque<int> ages;
1335 std::string tableName("Person");
1336 lastNames.push_back("LN1");
1337 lastNames.push_back("LN2");
1338 firstNames.push_back("FN1");
1339 firstNames.push_back("FN2");
1340 addresses.push_back("ADDR1");
1341 addresses.push_back("ADDR2");
1342 ages.push_back(1);
1343 ages.push_back(2);
1344 int count = 0;
1345 std::string result;
1346
1347 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1348 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1349 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1350
1351 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1352 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1353 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1354 assertTrue (count == 2);
1355
1356 std::deque<std::string> lastNamesR;
1357 std::deque<std::string> firstNamesR;
1358 std::deque<std::string> addressesR;
1359 std::deque<int> agesR;
1360 try { session() << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
1361 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1362 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1363 assertTrue (ages == agesR);
1364 assertTrue (lastNames == lastNamesR);
1365 assertTrue (firstNames == firstNamesR);
1366 assertTrue (addresses == addressesR);
1367 }
1368
1369
complexTypeDeque()1370 void SQLExecutor::complexTypeDeque()
1371 {
1372 std::string funct = "complexTypeDeque()";
1373 std::deque<Person> people;
1374 people.push_back(Person("LN1", "FN1", "ADDR1", 1));
1375 people.push_back(Person("LN2", "FN2", "ADDR2", 2));
1376
1377 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1378 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1379 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1380
1381 int count = 0;
1382 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1383 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1384 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1385 assertTrue (count == 2);
1386
1387 std::deque<Person> result;
1388 try { session() << "SELECT * FROM Person", into(result), now; }
1389 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1390 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1391 assertTrue (result == people);
1392 }
1393
1394
insertDeque()1395 void SQLExecutor::insertDeque()
1396 {
1397 std::string funct = "insertDeque()";
1398 std::deque<std::string> str;
1399 str.push_back("s1");
1400 str.push_back("s2");
1401 str.push_back("s3");
1402 str.push_back("s3");
1403 int count = 100;
1404
1405 {
1406 Statement stmt((session() << "INSERT INTO Strings VALUES (?)", use(str)));
1407 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1408 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1409 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1410 assertTrue (count == 0);
1411
1412 try { stmt.execute(); }
1413 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1414
1415 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1416 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1417 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1418 assertTrue (count == 4);
1419 }
1420 count = 0;
1421 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1422 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1423 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1424 assertTrue (count == 4);
1425 }
1426
1427
insertEmptyDeque()1428 void SQLExecutor::insertEmptyDeque()
1429 {
1430 std::string funct = "insertEmptyDeque()";
1431 std::deque<std::string> str;
1432
1433 try
1434 {
1435 session() << "INSERT INTO Strings VALUES (?)", use(str), now;
1436 fail("empty collections should not work");
1437 }
1438 catch (Poco::Exception&)
1439 {
1440 }
1441 }
1442
1443
affectedRows(const std::string & whereClause)1444 void SQLExecutor::affectedRows(const std::string& whereClause)
1445 {
1446 std::vector<std::string> str;
1447 str.push_back("s1");
1448 str.push_back("s2");
1449 str.push_back("s3");
1450 str.push_back("s3");
1451 int count = 100;
1452
1453 Statement stmt1((session() << "INSERT INTO Strings VALUES(?)", use(str)));
1454 session() << "SELECT COUNT(*) FROM Strings", into(count), now;
1455 assertTrue (count == 0);
1456 assertTrue (4 == stmt1.execute());
1457 session() << "SELECT COUNT(*) FROM Strings", into(count), now;
1458 assertTrue (count == 4);
1459
1460 Statement stmt2(session() << "UPDATE Strings SET str = 's4' WHERE str = 's3'");
1461 assertTrue (2 == stmt2.execute());
1462
1463 Statement stmt3(session() << "DELETE FROM Strings WHERE str = 's1'");
1464 assertTrue (1 == stmt3.execute());
1465
1466 std::string sql;
1467 format(sql, "DELETE FROM Strings %s", whereClause);
1468 Statement stmt4(session() << sql);
1469 assertTrue (3 == stmt4.execute());
1470 }
1471
1472
insertSingleBulk()1473 void SQLExecutor::insertSingleBulk()
1474 {
1475 std::string funct = "insertSingleBulk()";
1476 int x = 0;
1477 Statement stmt((session() << "INSERT INTO Strings VALUES (?)", use(x)));
1478
1479 for (x = 0; x < 100; ++x)
1480 {
1481 std::size_t i = stmt.execute();
1482 assertTrue (1 == i);
1483 }
1484 int count = 0;
1485 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1486 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1487 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1488 assertTrue (count == 100);
1489
1490 try { session() << "SELECT SUM(str) FROM Strings", into(count), now; }
1491 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1492 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1493 assertTrue (count == ((0+99)*100/2));
1494 }
1495
1496
floats()1497 void SQLExecutor::floats()
1498 {
1499 std::string funct = "floats()";
1500 float data = 1.5f;
1501 float ret = 0.0f;
1502
1503 try { session() << "INSERT INTO Strings VALUES (?)", use(data), now; }
1504 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1505 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1506
1507 int count = 0;
1508 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1509 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1510 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1511 assertTrue (count == 1);
1512
1513 try { session() << "SELECT str FROM Strings", into(ret), now; }
1514 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1515 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1516 assertTrue (ret == data);
1517 }
1518
1519
doubles()1520 void SQLExecutor::doubles()
1521 {
1522 std::string funct = "floats()";
1523 double data = 1.5;
1524 double ret = 0.0;
1525
1526 try { session() << "INSERT INTO Strings VALUES (?)", use(data), now; }
1527 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1528 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1529
1530 int count = 0;
1531 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1532 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1533 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1534 assertTrue (count == 1);
1535
1536 try { session() << "SELECT str FROM Strings", into(ret), now; }
1537 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1538 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1539 assertTrue (ret == data);
1540 }
1541
1542
insertSingleBulkVec()1543 void SQLExecutor::insertSingleBulkVec()
1544 {
1545 std::string funct = "insertSingleBulkVec()";
1546 std::vector<int> data;
1547
1548 for (int x = 0; x < 100; ++x)
1549 data.push_back(x);
1550
1551 Statement stmt((session() << "INSERT INTO Strings VALUES (?)", use(data)));
1552 stmt.execute();
1553
1554 int count = 0;
1555 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1556 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1557 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1558
1559 assertTrue (count == 100);
1560 try { session() << "SELECT SUM(str) FROM Strings", into(count), now; }
1561 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1562 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1563 assertTrue (count == ((0+99)*100/2));
1564 }
1565
1566
limits()1567 void SQLExecutor::limits()
1568 {
1569 std::string funct = "limit()";
1570 std::vector<int> data;
1571 for (int x = 0; x < 100; ++x)
1572 {
1573 data.push_back(x);
1574 }
1575
1576 try { session() << "INSERT INTO Strings VALUES (?)", use(data), now; }
1577 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1578 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1579
1580 std::vector<int> retData;
1581 try { session() << "SELECT * FROM Strings", into(retData), limit(50), now; }
1582 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1583 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1584 assertTrue (retData.size() == 50);
1585 for (int x = 0; x < 50; ++x)
1586 {
1587 assertTrue (data[x] == retData[x]);
1588 }
1589 }
1590
1591
limitZero()1592 void SQLExecutor::limitZero()
1593 {
1594 std::string funct = "limitZero()";
1595 std::vector<int> data;
1596 for (int x = 0; x < 100; ++x)
1597 {
1598 data.push_back(x);
1599 }
1600
1601 try { session() << "INSERT INTO Strings VALUES (?)", use(data), now; }
1602 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1603 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1604
1605 std::vector<int> retData;
1606 try { session() << "SELECT * FROM Strings", into(retData), limit(0), now; }// stupid test, but at least we shouldn't crash
1607 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1608 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1609 assertTrue (retData.size() == 0);
1610 }
1611
1612
limitOnce()1613 void SQLExecutor::limitOnce()
1614 {
1615 std::string funct = "limitOnce()";
1616 std::vector<int> data;
1617 for (int x = 0; x < 101; ++x)
1618 {
1619 data.push_back(x);
1620 }
1621
1622 try { session() << "INSERT INTO Strings VALUES (?)", use(data), now; }
1623 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1624 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1625
1626 std::vector<int> retData;
1627 Statement stmt = (session() << "SELECT * FROM Strings", into(retData), limit(50), now);
1628 assertTrue (!stmt.done());
1629 assertTrue (retData.size() == 50);
1630 stmt.execute();
1631 assertTrue (!stmt.done());
1632 assertTrue (retData.size() == 100);
1633 stmt.execute();
1634 assertTrue (stmt.done());
1635 assertTrue (retData.size() == 101);
1636
1637 for (int x = 0; x < 101; ++x)
1638 {
1639 assertTrue (data[x] == retData[x]);
1640 }
1641 }
1642
1643
limitPrepare()1644 void SQLExecutor::limitPrepare()
1645 {
1646 std::string funct = "limitPrepare()";
1647 std::vector<int> data;
1648 for (int x = 0; x < 100; ++x)
1649 {
1650 data.push_back(x);
1651 }
1652
1653 try
1654 {
1655 Statement stmt = (session() << "INSERT INTO Strings VALUES (?)", use(data));
1656 assertTrue (100 == stmt.execute());
1657 }
1658 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1659 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1660
1661 std::vector<int> retData;
1662 Statement stmt = (session() << "SELECT * FROM Strings", into(retData), limit(50));
1663 assertTrue (retData.size() == 0);
1664 assertTrue (!stmt.done());
1665
1666 try { stmt.execute(); }
1667 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1668 assertTrue (!stmt.done());
1669 assertTrue (retData.size() == 50);
1670
1671 try { stmt.execute(); }
1672 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1673 assertTrue (stmt.done());
1674 assertTrue (retData.size() == 100);
1675
1676 try { stmt.execute(); }// will restart execution!
1677 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1678
1679 assertTrue (!stmt.done());
1680 assertTrue (retData.size() == 150);
1681 for (int x = 0; x < 150; ++x)
1682 {
1683 assertTrue (data[x%100] == retData[x]);
1684 }
1685 }
1686
1687
prepare()1688 void SQLExecutor::prepare()
1689 {
1690 std::string funct = "prepare()";
1691 std::vector<int> data;
1692 for (int x = 0; x < 100; x += 2)
1693 {
1694 data.push_back(x);
1695 }
1696
1697 {
1698 Statement stmt((session() << "INSERT INTO Strings VALUES (?)", use(data)));
1699 }
1700
1701 // stmt should not have been executed when destroyed
1702 int count = 100;
1703 try { session() << "SELECT COUNT(*) FROM Strings", into(count), now; }
1704 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1705 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1706 assertTrue (count == 0);
1707 }
1708
1709
doBulkPerformance(Poco::UInt32 size)1710 void SQLExecutor::doBulkPerformance(Poco::UInt32 size)
1711 {
1712 std::string funct = "doBulk()";
1713 std::vector<int> ints(size, 1);
1714 std::vector<std::string> strings(size, "abc");
1715 std::vector<double> floats(size, .5);
1716 std::vector<DateTime> dateTimes(size);
1717
1718 Stopwatch sw;
1719 try
1720 {
1721 sw.start();
1722 session() << "INSERT INTO MiscTest (First, Third, Fourth, Fifth) VALUES (?,?,?,?)",
1723 use(strings),
1724 use(ints),
1725 use(floats),
1726 use(dateTimes), now;
1727 sw.stop();
1728 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1729 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1730
1731 double time = sw.elapsed() / 1000.0;
1732
1733 try { session() << "DELETE FROM MiscTest", now; }
1734 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1735 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1736
1737 try
1738 {
1739 sw.restart();
1740 session() << "INSERT INTO MiscTest (First, Third, Fourth, Fifth) VALUES (?,?,?,?)",
1741 use(strings, bulk),
1742 use(ints, bulk),
1743 use(floats, bulk),
1744 use(dateTimes, bulk), now;
1745 sw.stop();
1746 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1747 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1748
1749 double bulkTime = sw.elapsed() / 1000.0;
1750
1751 double speedup;
1752 if (0.0 == bulkTime)
1753 {
1754 if (0.0 == time) speedup = 1.0;
1755 else speedup = time;
1756 }
1757 else
1758 speedup = time / bulkTime;
1759
1760 std::cout << "INSERT => Size:" << size
1761 << ", Time: " << time
1762 << ", Bulk Time: " << bulkTime
1763 << " [ms], Speedup: " << speedup
1764 << 'x' << std::endl;
1765
1766 ints.clear();
1767 strings.clear();
1768 floats.clear();
1769 dateTimes.clear();
1770
1771 try
1772 {
1773 sw.restart();
1774 session() << "SELECT First, Third, Fourth, Fifth FROM MiscTest",
1775 into(strings),
1776 into(ints),
1777 into(floats),
1778 into(dateTimes),
1779 now;
1780 sw.stop();
1781 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1782 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1783
1784 time = sw.elapsed() / 1000.0;
1785
1786 assertTrue (ints.size() == size);
1787
1788 ints.clear();
1789 strings.clear();
1790 floats.clear();
1791 dateTimes.clear();
1792
1793 try
1794 {
1795 sw.restart();
1796 session() << "SELECT First, Third, Fourth, Fifth FROM MiscTest",
1797 into(strings, bulk(size)),
1798 into(ints, bulk(size)),
1799 into(floats, bulk(size)),
1800 into(dateTimes, bulk(size)),
1801 now;
1802 sw.stop();
1803 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1804 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1805
1806 bulkTime = sw.elapsed() / 1000.0;
1807
1808 assertTrue (ints.size() == size);
1809
1810 if (0.0 == bulkTime)
1811 {
1812 if (0.0 == time) speedup = 1.0;
1813 else speedup = time;
1814 }
1815 else
1816 speedup = time / bulkTime;
1817
1818 std::cout << "SELECT => Size:" << size
1819 << ", Time: " << time
1820 << ", Bulk Time: " << bulkTime
1821 << " [ms], Speedup: " << speedup
1822 << 'x' << std::endl;
1823 }
1824
1825
setSimple()1826 void SQLExecutor::setSimple()
1827 {
1828 std::string funct = "setSimple()";
1829 std::set<std::string> lastNames;
1830 std::set<std::string> firstNames;
1831 std::set<std::string> addresses;
1832 std::set<int> ages;
1833 std::string tableName("Person");
1834 lastNames.insert("LN1");
1835 lastNames.insert("LN2");
1836 firstNames.insert("FN1");
1837 firstNames.insert("FN2");
1838 addresses.insert("ADDR1");
1839 addresses.insert("ADDR2");
1840 ages.insert(1);
1841 ages.insert(2);
1842 int count = 0;
1843 std::string result;
1844
1845 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1846 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1847 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1848 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1849 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1850 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1851 assertTrue (count == 2);
1852
1853 std::set<std::string> lastNamesR;
1854 std::set<std::string> firstNamesR;
1855 std::set<std::string> addressesR;
1856 std::set<int> agesR;
1857 try { session() << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
1858 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1859 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1860 assertTrue (ages == agesR);
1861 assertTrue (lastNames == lastNamesR);
1862 assertTrue (firstNames == firstNamesR);
1863 assertTrue (addresses == addressesR);
1864 }
1865
1866
setComplex()1867 void SQLExecutor::setComplex()
1868 {
1869 std::string funct = "setComplex()";
1870 std::set<Person> people;
1871 people.insert(Person("LN1", "FN1", "ADDR1", 1));
1872 people.insert(Person("LN2", "FN2", "ADDR2", 2));
1873
1874 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1875 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1876 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1877 int count = 0;
1878 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1879 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1880 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1881 assertTrue (count == 2);
1882
1883 std::set<Person> result;
1884 try { session() << "SELECT * FROM Person", into(result), now; }
1885 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1886 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1887 assertTrue (result == people);
1888 }
1889
1890
setComplexUnique()1891 void SQLExecutor::setComplexUnique()
1892 {
1893 std::string funct = "setComplexUnique()";
1894 std::vector<Person> people;
1895 Person p1("LN1", "FN1", "ADDR1", 1);
1896 people.push_back(p1);
1897 people.push_back(p1);
1898 people.push_back(p1);
1899 people.push_back(p1);
1900 Person p2("LN2", "FN2", "ADDR2", 2);
1901 people.push_back(p2);
1902
1903 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1904 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1905 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1906 int count = 0;
1907 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1908 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1909 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1910 assertTrue (count == 5);
1911
1912 std::set<Person> result;
1913 try { session() << "SELECT * FROM Person", into(result), now; }
1914 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1915 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1916 assertTrue (result.size() == 2);
1917 assertTrue (*result.begin() == p1);
1918 assertTrue (*++result.begin() == p2);
1919 }
1920
multiSetSimple()1921 void SQLExecutor::multiSetSimple()
1922 {
1923 std::string funct = "multiSetSimple()";
1924 std::multiset<std::string> lastNames;
1925 std::multiset<std::string> firstNames;
1926 std::multiset<std::string> addresses;
1927 std::multiset<int> ages;
1928 std::string tableName("Person");
1929 lastNames.insert("LN1");
1930 lastNames.insert("LN2");
1931 firstNames.insert("FN1");
1932 firstNames.insert("FN2");
1933 addresses.insert("ADDR1");
1934 addresses.insert("ADDR2");
1935 ages.insert(1);
1936 ages.insert(2);
1937 int count = 0;
1938 std::string result;
1939
1940 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
1941 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1942 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1943 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1944 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1945 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1946 assertTrue (count == 2);
1947
1948 std::multiset<std::string> lastNamesR;
1949 std::multiset<std::string> firstNamesR;
1950 std::multiset<std::string> addressesR;
1951 std::multiset<int> agesR;
1952 try { session() << "SELECT * FROM Person", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
1953 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1954 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1955 assertTrue (ages.size() == agesR.size());
1956 assertTrue (lastNames.size() == lastNamesR.size());
1957 assertTrue (firstNames.size() == firstNamesR.size());
1958 assertTrue (addresses.size() == addressesR.size());
1959 }
1960
1961
multiSetComplex()1962 void SQLExecutor::multiSetComplex()
1963 {
1964 std::string funct = "multiSetComplex()";
1965 std::multiset<Person> people;
1966 Person p1("LN1", "FN1", "ADDR1", 1);
1967 people.insert(p1);
1968 people.insert(p1);
1969 people.insert(p1);
1970 people.insert(p1);
1971 Person p2("LN2", "FN2", "ADDR2", 2);
1972 people.insert(p2);
1973
1974 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
1975 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1976 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1977 int count = 0;
1978 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
1979 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1980 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1981 assertTrue (count == 5);
1982
1983 std::multiset<Person> result;
1984 try { session() << "SELECT * FROM Person", into(result), now; }
1985 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
1986 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
1987 assertTrue (result.size() == people.size());
1988 }
1989
1990
mapComplex()1991 void SQLExecutor::mapComplex()
1992 {
1993 std::string funct = "mapComplex()";
1994 std::map<std::string, Person> people;
1995 Person p1("LN1", "FN1", "ADDR1", 1);
1996 Person p2("LN2", "FN2", "ADDR2", 2);
1997 people.insert(std::make_pair("LN1", p1));
1998 people.insert(std::make_pair("LN2", p2));
1999
2000 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2001 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2002 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2003 int count = 0;
2004 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2005 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2006 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2007 assertTrue (count == 2);
2008
2009 std::map<std::string, Person> result;
2010 try { session() << "SELECT * FROM Person", into(result), now; }
2011 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2012 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2013 assertTrue (result == people);
2014 }
2015
2016
mapComplexUnique()2017 void SQLExecutor::mapComplexUnique()
2018 {
2019 std::string funct = "mapComplexUnique()";
2020 std::multimap<std::string, Person> people;
2021 Person p1("LN1", "FN1", "ADDR1", 1);
2022 Person p2("LN2", "FN2", "ADDR2", 2);
2023 people.insert(std::make_pair("LN1", p1));
2024 people.insert(std::make_pair("LN1", p1));
2025 people.insert(std::make_pair("LN1", p1));
2026 people.insert(std::make_pair("LN1", p1));
2027 people.insert(std::make_pair("LN2", p2));
2028
2029 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2030 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2031 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2032 int count = 0;
2033 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2034 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2035 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2036 assertTrue (count == 5);
2037
2038 std::map<std::string, Person> result;
2039 try { session() << "SELECT * FROM Person", into(result), now; }
2040 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2041 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2042 assertTrue (result.size() == 2);
2043 }
2044
2045
multiMapComplex()2046 void SQLExecutor::multiMapComplex()
2047 {
2048 std::string funct = "multiMapComplex()";
2049 std::multimap<std::string, Person> people;
2050 Person p1("LN1", "FN1", "ADDR1", 1);
2051 Person p2("LN2", "FN2", "ADDR2", 2);
2052 people.insert(std::make_pair("LN1", p1));
2053 people.insert(std::make_pair("LN1", p1));
2054 people.insert(std::make_pair("LN1", p1));
2055 people.insert(std::make_pair("LN1", p1));
2056 people.insert(std::make_pair("LN2", p2));
2057
2058 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2059 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2060 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2061 int count = 0;
2062 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2063 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2064 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2065 assertTrue (count == 5);
2066
2067 std::multimap<std::string, Person> result;
2068 try { session() << "SELECT * FROM Person", into(result), now; }
2069 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2070 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2071 assertTrue (result.size() == people.size());
2072 }
2073
2074
selectIntoSingle()2075 void SQLExecutor::selectIntoSingle()
2076 {
2077 std::string funct = "selectIntoSingle()";
2078 std::multimap<std::string, Person> people;
2079 Person p1("LN1", "FN1", "ADDR1", 1);
2080 Person p2("LN2", "FN2", "ADDR2", 2);
2081 people.insert(std::make_pair("LN1", p1));
2082 people.insert(std::make_pair("LN2", p2));
2083
2084 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2085 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2086 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2087 int count = 0;
2088 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2089 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2090 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2091 assertTrue (count == 2);
2092 Person result;
2093 try { session() << "SELECT * FROM Person ORDER BY LastName", into(result), limit(1), now; }// will return 1 object into one single result
2094 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2095 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2096 assertTrue (result == p1);
2097 }
2098
2099
selectIntoSingleStep()2100 void SQLExecutor::selectIntoSingleStep()
2101 {
2102 std::string funct = "selectIntoSingleStep()";
2103 std::multimap<std::string, Person> people;
2104 Person p1("LN1", "FN1", "ADDR1", 1);
2105 Person p2("LN2", "FN2", "ADDR2", 2);
2106 people.insert(std::make_pair("LN1", p1));
2107 people.insert(std::make_pair("LN2", p2));
2108
2109 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2110 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2111 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2112
2113 int count = 0;
2114 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2115 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2116 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2117 assertTrue (count == 2);
2118 Person result;
2119 Statement stmt = (session() << "SELECT * FROM Person", into(result), limit(1));
2120 stmt.execute();
2121 assertTrue (result == p1);
2122 assertTrue (!stmt.done());
2123 stmt.execute();
2124 assertTrue (result == p2);
2125 assertTrue (stmt.done());
2126 }
2127
2128
selectIntoSingleFail()2129 void SQLExecutor::selectIntoSingleFail()
2130 {
2131 std::string funct = "selectIntoSingleFail()";
2132 std::multimap<std::string, Person> people;
2133 Person p1("LN1", "FN1", "ADDR1", 1);
2134 Person p2("LN2", "FN2", "ADDR2", 2);
2135 people.insert(std::make_pair("LN1", p1));
2136 people.insert(std::make_pair("LN2", p2));
2137
2138 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2139 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2140 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2141 int count = 0;
2142 try { session() << "SELECT COUNT(*) FROM Person", into(count), limit(2, true), now; }
2143 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2144 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2145 assertTrue (count == 2);
2146 Person result;
2147 try
2148 {
2149 session() << "SELECT * FROM Person", into(result), limit(1, true), now; // will fail now
2150 fail("hardLimit is set: must fail");
2151 }
2152 catch(Poco::Data::LimitException&)
2153 {
2154 }
2155 }
2156
2157
lowerLimitOk()2158 void SQLExecutor::lowerLimitOk()
2159 {
2160 std::string funct = "lowerLimitOk()";
2161 std::multimap<std::string, Person> people;
2162 Person p1("LN1", "FN1", "ADDR1", 1);
2163 Person p2("LN2", "FN2", "ADDR2", 2);
2164 people.insert(std::make_pair("LN1", p1));
2165 people.insert(std::make_pair("LN1", p2));
2166
2167 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2168 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2169 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2170
2171 int count = 0;
2172 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2173 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2174 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2175 assertTrue (count == 2);
2176 Person result;
2177 try
2178 {
2179 session() << "SELECT * FROM Person", into(result), lowerLimit(2), now; // will return 2 objects into one single result but only room for one!
2180 fail("Not enough space for results");
2181 }
2182 catch(Poco::Exception&)
2183 {
2184 }
2185 }
2186
2187
singleSelect()2188 void SQLExecutor::singleSelect()
2189 {
2190 std::string funct = "singleSelect()";
2191 std::multimap<std::string, Person> people;
2192 Person p1("LN1", "FN1", "ADDR1", 1);
2193 Person p2("LN2", "FN2", "ADDR2", 2);
2194 people.insert(std::make_pair("LN1", p1));
2195 people.insert(std::make_pair("LN1", p2));
2196
2197 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2198 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2199 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2200
2201 int count = 0;
2202 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2203 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2204 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2205 assertTrue (count == 2);
2206 Person result;
2207 Statement stmt = (session() << "SELECT * FROM Person", into(result), limit(1));
2208 stmt.execute();
2209 assertTrue (result == p1);
2210 assertTrue (!stmt.done());
2211 stmt.execute();
2212 assertTrue (result == p2);
2213 assertTrue (stmt.done());
2214 }
2215
2216
lowerLimitFail()2217 void SQLExecutor::lowerLimitFail()
2218 {
2219 std::string funct = "lowerLimitFail()";
2220 std::multimap<std::string, Person> people;
2221 Person p1("LN1", "FN1", "ADDR1", 1);
2222 Person p2("LN2", "FN2", "ADDR2", 2);
2223 people.insert(std::make_pair("LN1", p1));
2224 people.insert(std::make_pair("LN1", p2));
2225
2226 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2227 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2228 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2229 int count = 0;
2230 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2231 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2232 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2233 assertTrue (count == 2);
2234 Person result;
2235 try
2236 {
2237 session() << "SELECT * FROM Person", into(result), lowerLimit(3), now; // will fail
2238 fail("should fail. not enough data");
2239 }
2240 catch(Poco::Exception&)
2241 {
2242 }
2243 }
2244
2245
combinedLimits()2246 void SQLExecutor::combinedLimits()
2247 {
2248 std::string funct = "combinedLimits()";
2249 std::multimap<std::string, Person> people;
2250 Person p1("LN1", "FN1", "ADDR1", 1);
2251 Person p2("LN2", "FN2", "ADDR2", 2);
2252 people.insert(std::make_pair("LN1", p1));
2253 people.insert(std::make_pair("LN1", p2));
2254
2255 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2256 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2257 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2258 int count = 0;
2259 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2260 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2261 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2262 assertTrue (count == 2);
2263 std::vector <Person> result;
2264 try { session() << "SELECT * FROM Person", into(result), lowerLimit(2), upperLimit(2), now; }// will return 2 objects
2265 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2266 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2267 assertTrue (result.size() == 2);
2268 assertTrue (result[0] == p1);
2269 assertTrue (result[1] == p2);
2270 }
2271
2272
2273
ranges()2274 void SQLExecutor::ranges()
2275 {
2276 std::string funct = "range()";
2277 std::multimap<std::string, Person> people;
2278 Person p1("LN1", "FN1", "ADDR1", 1);
2279 Person p2("LN2", "FN2", "ADDR2", 2);
2280 people.insert(std::make_pair("LN1", p1));
2281 people.insert(std::make_pair("LN1", p2));
2282
2283 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2284 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2285 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2286 int count = 0;
2287 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2288 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2289 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2290 assertTrue (count == 2);
2291 std::vector <Person> result;
2292 try { session() << "SELECT * FROM Person", into(result), range(2, 2), now; }// will return 2 objects
2293 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2294 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2295 assertTrue (result.size() == 2);
2296 assertTrue (result[0] == p1);
2297 assertTrue (result[1] == p2);
2298 }
2299
2300
combinedIllegalLimits()2301 void SQLExecutor::combinedIllegalLimits()
2302 {
2303 std::string funct = "combinedIllegalLimits()";
2304 std::multimap<std::string, Person> people;
2305 Person p1("LN1", "FN1", "ADDR1", 1);
2306 Person p2("LN2", "FN2", "ADDR2", 2);
2307 people.insert(std::make_pair("LN1", p1));
2308 people.insert(std::make_pair("LN1", p2));
2309
2310 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2311 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2312 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2313 int count = 0;
2314 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2315 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2316 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2317 assertTrue (count == 2);
2318 Person result;
2319 try
2320 {
2321 session() << "SELECT * FROM Person", into(result), lowerLimit(3), upperLimit(2), now;
2322 fail("lower > upper is not allowed");
2323 }
2324 catch(LimitException&)
2325 {
2326 }
2327 }
2328
2329
illegalRange()2330 void SQLExecutor::illegalRange()
2331 {
2332 std::string funct = "illegalRange()";
2333 std::multimap<std::string, Person> people;
2334 Person p1("LN1", "FN1", "ADDR1", 1);
2335 Person p2("LN2", "FN2", "ADDR2", 2);
2336 people.insert(std::make_pair("LN1", p1));
2337 people.insert(std::make_pair("LN1", p2));
2338
2339 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(people), now; }
2340 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2341 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2342 int count = 0;
2343 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2344 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2345 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2346 assertTrue (count == 2);
2347 Person result;
2348 try
2349 {
2350 session() << "SELECT * FROM Person", into(result), range(3, 2), now;
2351 fail("lower > upper is not allowed");
2352 }
2353 catch(LimitException&)
2354 {
2355 }
2356 }
2357
2358
emptyDB()2359 void SQLExecutor::emptyDB()
2360 {
2361 std::string funct = "emptyDB()";
2362 int count = 0;
2363 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2364 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2365 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2366 assertTrue (count == 0);
2367
2368 Person result;
2369 Statement stmt = (session() << "SELECT * FROM Person", into(result), limit(1));
2370 stmt.execute();
2371 assertTrue (result.firstName.empty());
2372 assertTrue (stmt.done());
2373 }
2374
2375
blob(int bigSize,const std::string & blobPlaceholder)2376 void SQLExecutor::blob(int bigSize, const std::string& blobPlaceholder)
2377 {
2378 std::string funct = "blob()";
2379 std::string lastName("lastname");
2380 std::string firstName("firstname");
2381 std::string address("Address");
2382
2383 CLOB img("0123456789", 10);
2384 int count = 0;
2385 try { session() << format("INSERT INTO Person VALUES (?,?,?,%s)", blobPlaceholder),
2386 use(lastName), use(firstName), use(address), use(img), now; }
2387 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2388 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2389 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2390 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2391 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2392 assertTrue (count == 1);
2393
2394 CLOB res;
2395 assertTrue (res.size() == 0);
2396 try { session() << "SELECT Image FROM Person", into(res), now; }
2397 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2398 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2399 assertTrue (res == img);
2400
2401 CLOB big;
2402 std::vector<char> v(bigSize, 'x');
2403 big.assignRaw(&v[0], v.size());
2404
2405 assertTrue (big.size() == bigSize);
2406
2407 try { session() << "DELETE FROM Person", now; }
2408 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2409 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2410
2411 try { session() << format("INSERT INTO Person VALUES (?,?,?,%s)", blobPlaceholder),
2412 use(lastName), use(firstName), use(address), use(big), now; }
2413 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2414 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2415
2416 try { session() << "SELECT Image FROM Person", into(res), now; }
2417 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2418 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2419 assertTrue (res == big);
2420 }
2421
2422
blobStmt()2423 void SQLExecutor::blobStmt()
2424 {
2425 std::string funct = "blobStmt()";
2426 std::string lastName("lastname");
2427 std::string firstName("firstname");
2428 std::string address("Address");
2429 CLOB blob("0123456789", 10);
2430
2431 int count = 0;
2432 Statement ins = (session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(blob));
2433 ins.execute();
2434 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2435 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2436 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2437 assertTrue (count == 1);
2438
2439 CLOB res;
2440 poco_assert (res.size() == 0);
2441 Statement stmt = (session() << "SELECT Image FROM Person", into(res));
2442 try { stmt.execute(); }
2443 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2444 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2445 poco_assert (res == blob);
2446 }
2447
2448
dateTime()2449 void SQLExecutor::dateTime()
2450 {
2451 std::string funct = "dateTime()";
2452 std::string lastName("lastname");
2453 std::string firstName("firstname");
2454 std::string address("Address");
2455
2456 DateTime born(1965, 6, 18, 5, 35, 1);
2457 int count = 0;
2458 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(born), now; }
2459 catch (ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail(funct); }
2460 catch (StatementException& se){ std::cout << se.toString() << std::endl; fail(funct); }
2461 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2462 catch (ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail(funct); }
2463 catch (StatementException& se){ std::cout << se.toString() << std::endl; fail(funct); }
2464 assertTrue (count == 1);
2465
2466 DateTime res;
2467 try { session() << "SELECT Born FROM Person", into(res), now; }
2468 catch (ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail(funct); }
2469 catch (StatementException& se){ std::cout << se.toString() << std::endl; fail(funct); }
2470 assertTrue (res == born);
2471
2472 Statement stmt = (session() << "SELECT Born FROM Person", now);
2473 RecordSet rset(stmt);
2474
2475 res = rset["Born"].convert<DateTime>();
2476 assertTrue (res == born);
2477 }
2478
2479
date()2480 void SQLExecutor::date()
2481 {
2482 std::string funct = "date()";
2483 std::string lastName("lastname");
2484 std::string firstName("firstname");
2485 std::string address("Address");
2486
2487 Date bornDate(1965, 6, 18);
2488 int count = 0;
2489 try { session() << "INSERT INTO Person VALUES (?,?,?,?)",
2490 use(lastName),
2491 use(firstName),
2492 use(address),
2493 use(bornDate),
2494 now; }
2495 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2496 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2497 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2498 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2499 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2500 assertTrue (count == 1);
2501
2502 Date d;
2503 Time t;
2504 try { session() << "SELECT BornDate FROM Person", into(d), now; }
2505 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2506 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2507 assertTrue (d == bornDate);
2508
2509 Statement stmt = (session() << "SELECT BornDate FROM Person", now);
2510 RecordSet rset(stmt);
2511
2512 DateTime dt1 = rset["BornDate"].convert<DateTime>();
2513
2514 Date d2(dt1);
2515 assertTrue (d2 == bornDate);
2516 }
2517
2518
time()2519 void SQLExecutor::time()
2520 {
2521 std::string funct = "time()";
2522 std::string lastName("lastname");
2523 std::string firstName("firstname");
2524 std::string address("Address");
2525
2526 Time bornTime (5, 35, 1);
2527 int count = 0;
2528 try { session() << "INSERT INTO Person VALUES (?,?,?,?)",
2529 use(lastName),
2530 use(firstName),
2531 use(address),
2532 use(bornTime),
2533 now; }
2534 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2535 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2536 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
2537 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2538 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2539 assertTrue (count == 1);
2540
2541 Date d;
2542 Time t;
2543 try { session() << "SELECT BornTime FROM Person", into(t), now; }
2544 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2545 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2546 assertTrue (t == bornTime);
2547
2548 Statement stmt = (session() << "SELECT BornTime FROM Person", now);
2549 RecordSet rset(stmt);
2550
2551 DateTime dt2 = rset["BornTime"].convert<DateTime>();
2552 Time t2(dt2);
2553 assertTrue (t2 == bornTime);
2554 }
2555
2556
tuples()2557 void SQLExecutor::tuples()
2558 {
2559 typedef Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> TupleType;
2560 std::string funct = "tuples()";
2561 TupleType t(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
2562
2563 try { session() << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", use(t), now; }
2564 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2565 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2566
2567 TupleType ret(-10,-11,-12,-13,-14,-15,-16,-17,-18,-19);
2568 assertTrue (ret != t);
2569 try { session() << "SELECT * FROM Tuples", into(ret), now; }
2570 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2571 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2572 assertTrue (ret == t);
2573 }
2574
tupleVector()2575 void SQLExecutor::tupleVector()
2576 {
2577 typedef Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> TupleType;
2578 std::string funct = "tupleVector()";
2579 TupleType t(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
2580 Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int>
2581 t10(10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29);
2582 TupleType t100(100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119);
2583 std::vector<TupleType> v;
2584 v.push_back(t);
2585 v.push_back(t10);
2586 v.push_back(t100);
2587
2588 try { session() << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", use(v), now; }
2589 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2590 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2591
2592 int count = 0;
2593 try { session() << "SELECT COUNT(*) FROM Tuples", into(count), now; }
2594 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2595 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2596 assertTrue (v.size() == count);
2597
2598 std::vector<Tuple<int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int,int> > ret;
2599 try { session() << "SELECT * FROM Tuples", into(ret), now; }
2600 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2601 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2602 assertTrue (ret == v);
2603 }
2604
2605
internalExtraction()2606 void SQLExecutor::internalExtraction()
2607 {
2608 std::string funct = "internalExtraction()";
2609 std::vector<Tuple<int, double, std::string> > v;
2610 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
2611 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
2612 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
2613 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
2614
2615 try { session() << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now; }
2616 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2617 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2618
2619 try
2620 {
2621 Statement stmt = (session() << "SELECT * FROM Vectors", now);
2622 RecordSet rset(stmt);
2623
2624 assertTrue (3 == rset.columnCount());
2625 assertTrue (4 == rset.rowCount());
2626
2627 int curVal = 3;
2628 do
2629 {
2630 assertTrue (rset["str0"] == curVal);
2631 ++curVal;
2632 } while (rset.moveNext());
2633
2634 rset.moveFirst();
2635 assertTrue (rset["str0"] == "3");
2636 rset.moveLast();
2637 assertTrue (rset["str0"] == "6");
2638
2639 RecordSet rset2(rset);
2640 assertTrue (3 == rset2.columnCount());
2641 assertTrue (4 == rset2.rowCount());
2642
2643 int i = rset.value<int>(0,0);
2644 assertTrue (1 == i);
2645
2646 std::string s = rset.value(0,0).convert<std::string>();
2647 assertTrue ("1" == s);
2648
2649 int a = rset.value<int>(0,2);
2650 assertTrue (3 == a);
2651
2652 try
2653 {
2654 double d = rset.value<double>(1,1);
2655 assertTrue (2.5 == d);
2656 }
2657 catch (BadCastException&)
2658 {
2659 float f = rset.value<float>(1,1);
2660 assertTrue (2.5 == f);
2661 }
2662
2663 try
2664 {
2665 s = rset.value<std::string>(2, 2);
2666 }
2667 catch (BadCastException&)
2668 {
2669 UTF16String us = rset.value<Poco::UTF16String>(2, 2);
2670 Poco::UnicodeConverter::convert(us, s);
2671 }
2672 assertTrue ("5" == s);
2673
2674 i = rset.value("str0", 2);
2675 assertTrue (5 == i);
2676
2677 const Column<std::deque<int> >& col = rset.column<std::deque<int> >(0);
2678 Column<std::deque<int> >::Iterator it = col.begin();
2679 Column<std::deque<int> >::Iterator end = col.end();
2680 for (int i = 1; it != end; ++it, ++i)
2681 assertTrue (*it == i);
2682
2683 rset = (session() << "SELECT COUNT(*) AS cnt FROM Vectors", now);
2684
2685 //various results for COUNT(*) are received from different drivers
2686 try
2687 {
2688 //this is what most drivers will return
2689 int i = rset.value<int>(0,0);
2690 assertTrue (4 == i);
2691 }
2692 catch(BadCastException&)
2693 {
2694 try
2695 {
2696 //this is for Oracle
2697 double i = rset.value<double>(0,0);
2698 assertTrue (4 == int(i));
2699 }
2700 catch(BadCastException&)
2701 {
2702 //this is for PostgreSQL
2703 Poco::Int64 big = rset.value<Poco::Int64>(0,0);
2704 assertTrue (4 == big);
2705 }
2706 }
2707
2708 s = rset.value("cnt", 0).convert<std::string>();
2709 assertTrue ("4" == s);
2710
2711 try { rset.column<std::deque<int> >(100); fail ("must fail"); }
2712 catch (RangeException&) { }
2713
2714 try { rset.value<std::string>(0,0); fail ("must fail"); }
2715 catch (BadCastException&) { }
2716
2717 stmt = (session() << "DELETE FROM Vectors", now);
2718 rset = stmt;
2719
2720 try { rset.column<std::deque<int> >(0); fail ("must fail"); }
2721 catch (RangeException&) { }
2722 }
2723 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2724 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2725 }
2726
2727
filter(const std::string & query,const std::string & intFldName)2728 void SQLExecutor::filter(const std::string& query, const std::string& intFldName)
2729 {
2730 std::string funct = "filter()";
2731 std::vector<Tuple<int, double, std::string> > v;
2732 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
2733 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
2734 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
2735 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
2736
2737 try { session() << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now; }
2738 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2739 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2740
2741 try
2742 {
2743 Statement stmt = (session() << query, now);
2744 RecordSet rset(stmt);
2745 assertTrue (rset.totalRowCount() == 4);
2746 RowFilter::Ptr pRF = new RowFilter(&rset);
2747 assertTrue (pRF->isEmpty());
2748 pRF->add(intFldName, RowFilter::VALUE_EQUAL, 1);
2749 assertTrue (!pRF->isEmpty());
2750
2751 Var da;
2752 try
2753 {
2754 da = rset.value(0, 1);
2755 fail ("must fail");
2756 } catch (InvalidAccessException&)
2757 {
2758 da = rset.value(0, 1, false);
2759 assertTrue (2 == da);
2760 da = rset.value(0, 0);
2761 assertTrue (1 == da);
2762 }
2763
2764 assertTrue (rset.rowCount() == 1);
2765 assertTrue (rset.moveFirst());
2766 assertTrue (1 == rset[intFldName]);
2767 assertTrue (!rset.moveNext());
2768 pRF->add("flt0", RowFilter::VALUE_LESS_THAN_OR_EQUAL, 3.5f);
2769 assertTrue (rset.rowCount() == 3);
2770 assertTrue (rset.moveNext());
2771 assertTrue (2.5 == rset["flt0"]);
2772 assertTrue (rset.moveNext());
2773 assertTrue (3.5 == rset["flt0"]);
2774 assertTrue (!rset.moveNext());
2775 pRF->add("str0", RowFilter::VALUE_EQUAL, 6);
2776 assertTrue (rset.rowCount() == 4);
2777 assertTrue (rset.moveLast());
2778 assertTrue ("6" == rset["str0"]);
2779 pRF->remove("flt0");
2780 assertTrue (rset.rowCount() == 2);
2781 assertTrue (rset.moveFirst());
2782 assertTrue ("3" == rset["str0"]);
2783 assertTrue (rset.moveNext());
2784 assertTrue ("6" == rset["str0"]);
2785 pRF->remove(intFldName);
2786 pRF->remove("str0");
2787 assertTrue (pRF->isEmpty());
2788 pRF->add("str0", "!=", 3);
2789 assertTrue (rset.rowCount() == 3);
2790
2791 RowFilter::Ptr pRF1 = new RowFilter(pRF, RowFilter::OP_AND);
2792 pRF1->add(intFldName, "==", 2);
2793 assertTrue (rset.rowCount() == 1);
2794 pRF1->add(intFldName, "<", 2);
2795 assertTrue (rset.rowCount() == 1);
2796 pRF1->add(intFldName, ">", 3);
2797 assertTrue (rset.rowCount() == 2);
2798 pRF->removeFilter(pRF1);
2799 pRF->remove("str0");
2800 assertTrue (pRF->isEmpty());
2801 assertTrue (rset.rowCount() == 4);
2802 }
2803 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2804 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2805 }
2806
2807
internalBulkExtraction()2808 void SQLExecutor::internalBulkExtraction()
2809 {
2810 std::string funct = "internalBulkExtraction()";
2811 int size = 100;
2812 std::vector<std::string> lastName(size);
2813 std::vector<std::string> firstName(size);
2814 std::vector<std::string> address(size);
2815 std::vector<int> age(size);
2816
2817 for (int i = 0; i < size; ++i)
2818 {
2819 lastName[i] = "LN" + NumberFormatter::format(i);
2820 firstName[i] = "FN" + NumberFormatter::format(i);
2821 address[i] = "Addr" + NumberFormatter::format(i);
2822 age[i] = i;
2823 }
2824
2825 try
2826 {
2827 session() << "INSERT INTO Person VALUES (?,?,?,?)",
2828 use(lastName, bulk),
2829 use(firstName, bulk),
2830 use(address, bulk),
2831 use(age, bulk),
2832 now;
2833 }
2834 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2835 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2836
2837 try
2838 {
2839 Statement stmt = (session() << "SELECT * FROM Person", bulk(size), now);
2840 RecordSet rset(stmt);
2841 assertTrue (size == rset.rowCount());
2842 assertTrue ("LN0" == rset["LastName"]);
2843 assertTrue (0 == rset["Age"]);
2844 rset.moveNext();
2845 assertTrue ("LN1" == rset["LastName"]);
2846 assertTrue (1 == rset["Age"]);
2847 rset.moveLast();
2848 assertTrue (std::string("LN") + NumberFormatter::format(size - 1) == rset["LastName"]);
2849 assertTrue (size - 1 == rset["Age"]);
2850 }
2851 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2852 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2853
2854 try
2855 {
2856 Statement stmt = (session() << "SELECT * FROM Person", limit(size), bulk, now);
2857 RecordSet rset(stmt);
2858 assertTrue (size == rset.rowCount());
2859 assertTrue ("LN0" == rset["LastName"]);
2860 assertTrue (0 == rset["Age"]);
2861 rset.moveLast();
2862 assertTrue (std::string("LN") + NumberFormatter::format(size - 1) == rset["LastName"]);
2863 assertTrue (size - 1 == rset["Age"]);
2864 }
2865 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2866 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2867 }
2868
2869
internalBulkExtractionUTF16()2870 void SQLExecutor::internalBulkExtractionUTF16()
2871 {
2872 std::string funct = "internalBulkExtraction()";
2873 int size = 100;
2874 std::vector<UTF16String> lastName(size);
2875 std::vector<UTF16String> firstName(size);
2876 std::vector<UTF16String> address(size);
2877 std::vector<int> age(size);
2878
2879 for (int i = 0; i < size; ++i)
2880 {
2881 lastName[i] = Poco::UnicodeConverter::to<UTF16String>("LN" + NumberFormatter::format(i));
2882 firstName[i] = Poco::UnicodeConverter::to<UTF16String>("FN" + NumberFormatter::format(i));
2883 address[i] = Poco::UnicodeConverter::to<UTF16String>("Addr" + NumberFormatter::format(i));
2884 age[i] = i;
2885 }
2886
2887 try
2888 {
2889 session() << "INSERT INTO Person VALUES (?,?,?,?)",
2890 use(lastName, bulk),
2891 use(firstName, bulk),
2892 use(address, bulk),
2893 use(age, bulk),
2894 now;
2895 }
2896 catch (ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail(funct); }
2897 catch (StatementException& se){ std::cout << se.toString() << std::endl; fail(funct); }
2898
2899 try
2900 {
2901 Statement stmt = (session() << "SELECT * FROM Person", bulk(size), now);
2902 RecordSet rset(stmt);
2903 assertTrue (size == rset.rowCount());
2904 assertTrue (Poco::UnicodeConverter::to<UTF16String>("LN0") == rset["LastName"]);
2905 assertTrue (0 == rset["Age"]);
2906 rset.moveNext();
2907 assertTrue (Poco::UnicodeConverter::to<UTF16String>("LN1") == rset["LastName"]);
2908 assertTrue (1 == rset["Age"]);
2909 rset.moveLast();
2910 assertTrue (std::string("LN") + NumberFormatter::format(size - 1) == rset["LastName"]);
2911 assertTrue (size - 1 == rset["Age"]);
2912 }
2913 catch (ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail(funct); }
2914 catch (StatementException& se){ std::cout << se.toString() << std::endl; fail(funct); }
2915
2916 try
2917 {
2918 Statement stmt = (session() << "SELECT * FROM Person", limit(size), bulk, now);
2919 RecordSet rset(stmt);
2920 assertTrue (size == rset.rowCount());
2921 assertTrue ("LN0" == rset["LastName"]);
2922 assertTrue (0 == rset["Age"]);
2923 rset.moveLast();
2924 assertTrue (std::string("LN") + NumberFormatter::format(size - 1) == rset["LastName"]);
2925 assertTrue (size - 1 == rset["Age"]);
2926 }
2927 catch (ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail(funct); }
2928 catch (StatementException& se){ std::cout << se.toString() << std::endl; fail(funct); }
2929 }
2930
2931
internalStorageType()2932 void SQLExecutor::internalStorageType()
2933 {
2934 std::string funct = "internalStorageType()";
2935 std::vector<Statement::Manipulator> manips;
2936 manips.push_back(list);
2937 manips.push_back(deque);
2938 manips.push_back(vector);
2939
2940 std::vector<Tuple<int, double, std::string> > v;
2941 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
2942 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
2943 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
2944 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
2945
2946 try { session() << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now; }
2947 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2948 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2949
2950 try
2951 {
2952 std::vector<Statement::Manipulator>::iterator it = manips.begin();
2953 std::vector<Statement::Manipulator>::iterator end = manips.end();
2954
2955 for (; it != end; ++it)
2956 {
2957 Statement stmt = (session() << "SELECT * FROM Vectors", *it, now);
2958 RecordSet rset(stmt);
2959
2960 assertTrue (3 == rset.columnCount());
2961 assertTrue (4 == rset.rowCount());
2962
2963 int curVal = 3;
2964 do
2965 {
2966 assertTrue (rset["str0"] == curVal);
2967 ++curVal;
2968 } while (rset.moveNext());
2969
2970 rset.moveFirst();
2971 assertTrue (rset["str0"] == "3");
2972 rset.moveLast();
2973 assertTrue (rset["str0"] == "6");
2974
2975 try
2976 {
2977 stmt = (session() << "SELECT * FROM Vectors", now, *it);
2978 fail ("must fail");
2979 }
2980 catch(InvalidAccessException&){}
2981
2982 try
2983 {
2984 stmt = (session() << "SELECT * FROM Vectors", into(v), now, *it);
2985 fail ("must fail");
2986 }
2987 catch(InvalidAccessException&){}
2988
2989 try
2990 {
2991 stmt = (session() << "SELECT * FROM Vectors", into(v), *it, now);
2992 fail ("must fail");
2993 }
2994 catch(InvalidAccessException&){}
2995 }
2996 }
2997 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
2998 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
2999 }
3000
3001
notNulls(const std::string & sqlState)3002 void SQLExecutor::notNulls(const std::string& sqlState)
3003 {
3004 try
3005 {
3006 session() << "INSERT INTO NullTest (i,r,v) VALUES (?,?,?)", use(null), use(null), use(null), now;
3007 fail ("must fail");
3008 }catch (StatementException& se)
3009 {
3010 //double check if we're failing for the right reason
3011 //default sqlState value is "23502"; some drivers report "HY???" codes
3012 if (se.diagnostics().fields().size())
3013 {
3014 std::string st = se.diagnostics().sqlState(0);
3015 if (sqlState != st)
3016 std::cerr << '[' << name() << ']' << " Warning: expected SQL state [" << sqlState <<
3017 "], received [" << se.diagnostics().sqlState(0) << "] instead." << std::endl;
3018 }
3019 }
3020 }
3021
3022
nulls()3023 void SQLExecutor::nulls()
3024 {
3025 std::string funct = "nulls()";
3026
3027 try { session() << "INSERT INTO NullTest (i,r,v) VALUES (?,?,?)", use(null), use(null), use(null), now; }
3028 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3029 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3030
3031 RecordSet rs(session(), "SELECT * FROM NullTest");
3032 assertTrue (1 == rs.rowCount());
3033 rs.moveFirst();
3034 assertTrue (rs.isNull("i"));
3035 assertTrue (rs["i"] != 0);
3036 assertTrue (rs.isNull("r"));
3037 assertTrue (rs.isNull("v"));
3038 assertTrue (rs["v"] != "");
3039 assertTrue (rs.nvl<int>("i") == 0);
3040 assertTrue (rs.nvl("i", -1) == -1);
3041 assertTrue (rs.nvl<double>("r") == double());
3042 assertTrue (rs.nvl("r", -1.5) == -1.5);
3043 assertTrue (rs.nvl<std::string>("v") == "");
3044 assertTrue (rs.nvl("v", "123") == "123");
3045 try { session() << "DELETE FROM NullTest", now; }
3046 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3047 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3048
3049 int i = 1;
3050 double f = 1.5;
3051 std::string s = "123";
3052
3053 try { session() << "INSERT INTO NullTest (i, r, v) VALUES (?,?,?)", use(i), use(f), use(s), now; }
3054 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3055 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3056 rs = (session() << "SELECT * FROM NullTest", now);
3057 assertTrue (1 == rs.rowCount());
3058 rs.moveFirst();
3059 assertTrue (!rs.isNull("i"));
3060 assertTrue (rs["i"] == 1);
3061 assertTrue (!rs.isNull("v"));
3062 assertTrue (!rs.isNull("r"));
3063 assertTrue (rs["v"] == "123");
3064 assertTrue (rs.nvl<int>("i") == 1);
3065 assertTrue (rs.nvl("i", -1) == 1);
3066 assertTrue (rs.nvl<double>("r") == 1.5);
3067 assertTrue (rs.nvl("r", -1.5) == 1.5);
3068 assertTrue (rs.nvl<std::string>("v") == "123");
3069 assertTrue (rs.nvl("v", "456") == "123");
3070 try { session() << "UPDATE NullTest SET v = ? WHERE i = ?", use(null), use(i), now; }
3071 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3072 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3073 i = 2;
3074 f = 3.4;
3075 try { session() << "INSERT INTO NullTest (i, r, v) VALUES (?,?,?)", use(i), use(null), use(null), now; }
3076 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3077 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3078 rs = (session() << "SELECT i, r, v FROM NullTest ORDER BY i ASC", now);
3079 assertTrue (2 == rs.rowCount());
3080 rs.moveFirst();
3081 assertTrue (!rs.isNull("i"));
3082 assertTrue (rs["i"] == 1);
3083 assertTrue (!rs.isNull("r"));
3084 assertTrue (rs.isNull("v"));
3085 assertTrue (rs["v"] != "");
3086
3087 assertTrue (rs.moveNext());
3088 assertTrue (!rs.isNull("i"));
3089 assertTrue (rs["i"] == 2);
3090 assertTrue (rs.isNull("r"));
3091 assertTrue (rs.isNull("v"));
3092 assertTrue (rs["v"] != "");
3093
3094 try { session() << "DELETE FROM NullTest", now; }
3095 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3096 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3097
3098 try { session() << "INSERT INTO NullTest (v) VALUES (?)", bind(""), now; }
3099 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3100 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3101
3102 bool esin = session().getFeature("emptyStringIsNull");
3103 session().setFeature("emptyStringIsNull", true);
3104
3105 try
3106 {
3107 session().setFeature("forceEmptyString", true);
3108 fail ("must fail");
3109 } catch (InvalidAccessException&) { }
3110
3111 bool fes = session().getFeature("forceEmptyString");
3112 session().setFeature("forceEmptyString", false);
3113
3114 RecordSet rs1(session(), "SELECT v FROM NullTest");
3115 assertTrue (1 == rs1.rowCount());
3116 rs1.moveFirst();
3117 assertTrue (rs1.isNull("v"));
3118 assertTrue (!(rs["v"] == ""));
3119
3120 session().setFeature("emptyStringIsNull", false);
3121 session().setFeature("forceEmptyString", true);
3122 RecordSet rs2(session(), "SELECT v FROM NullTest");
3123 assertTrue (1 == rs2.rowCount());
3124 rs2.moveFirst();
3125 assertTrue (!rs2.isNull("v"));
3126 assertTrue ((rs2["v"] == ""));
3127
3128 try
3129 {
3130 session().setFeature("emptyStringIsNull", true);
3131 fail ("must fail");
3132 } catch (InvalidAccessException&) { }
3133
3134 session().setFeature("emptyStringIsNull", esin);
3135 session().setFeature("forceEmptyString", fes);
3136 }
3137
3138
rowIterator()3139 void SQLExecutor::rowIterator()
3140 {
3141 std::string funct = "rowIterator()";
3142 std::vector<Tuple<int, double, std::string> > v;
3143 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
3144 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
3145 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
3146 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
3147
3148 try { session() << "DELETE FROM Vectors", now; }
3149 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3150 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3151
3152 RecordSet rset0(session(), "SELECT * FROM Vectors");
3153 assertTrue (rset0.begin() == rset0.end());
3154
3155 try { session() << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now; }
3156 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3157 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3158
3159 RecordSet rset(session(), "SELECT * FROM Vectors");
3160
3161 std::ostringstream osLoop;
3162 RecordSet::Iterator it = rset.begin();
3163 RecordSet::Iterator end = rset.end();
3164 for (int i = 1; it != end; ++it, ++i)
3165 {
3166 assertTrue (it->get(0) == i);
3167 osLoop << *it;
3168 }
3169 assertTrue (!osLoop.str().empty());
3170
3171 std::ostringstream osCopy;
3172 std::copy(rset.begin(), rset.end(), std::ostream_iterator<Row>(osCopy));
3173 assertTrue (osLoop.str() == osCopy.str());
3174
3175 RowFilter::Ptr pRF = new RowFilter(&rset);
3176 assertTrue (pRF->isEmpty());
3177 pRF->add("str0", RowFilter::VALUE_EQUAL, "3");
3178 assertTrue (!pRF->isEmpty());
3179 it = rset.begin();
3180 end = rset.end();
3181 for (int i = 1; it != end; ++it, ++i)
3182 {
3183 assertTrue (it->get(0) == i);
3184 assertTrue (1 == i);
3185 }
3186 }
3187
3188
stdVectorBool()3189 void SQLExecutor::stdVectorBool()
3190 {
3191 std::string funct = "stdVectorBool()";
3192
3193 bool b = false;
3194 try { session() << "INSERT INTO BoolTest VALUES (?)", use(b), now; }
3195 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3196 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3197
3198 b = true;
3199 session() << "SELECT * FROM BoolTest", into(b), now;
3200 assertTrue (false == b);
3201 session() << "DELETE FROM BoolTest", now;
3202
3203 b = true;
3204 try { session() << "INSERT INTO BoolTest VALUES (?)", use(b), now; }
3205 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3206 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3207
3208 b = false;
3209 session() << "SELECT * FROM BoolTest", into(b), now;
3210 assertTrue (true == b);
3211 session() << "DELETE FROM BoolTest", now;
3212
3213 std::vector<bool> v;
3214 v.push_back(true);
3215 v.push_back(false);
3216 v.push_back(false);
3217 v.push_back(true);
3218
3219 try { session() << "INSERT INTO BoolTest VALUES (?)", use(v), now; }
3220 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3221 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3222
3223 v.clear();
3224 session() << "SELECT * FROM BoolTest", into(v), now;
3225
3226 assertTrue (4 == v.size());
3227 std::vector<bool>::iterator it = v.begin();
3228 std::vector<bool>::iterator end = v.end();
3229 int t = 0;
3230 for (; it != end; ++it)
3231 t += *it ? 1 : 0;
3232 assertTrue (2 == t);
3233
3234 try { session() << "SELECT * FROM BoolTest WHERE b = ?", out(v), now; fail("must fail"); }
3235 catch (BindingException&) { }
3236
3237 try { session() << "SELECT * FROM BoolTest WHERE b = ?", io(v), now; fail("must fail"); }
3238 catch (BindingException&) { }
3239
3240 RecordSet rset(session(), "SELECT * FROM BoolTest");
3241
3242 t = 0;
3243 for (int i = 0; i < 4; ++i)
3244 t += rset.value<bool>(0, i) ? 1 : 0;
3245 assertTrue (2 == t);
3246 }
3247
3248
asynchronous(int rowCount)3249 void SQLExecutor::asynchronous(int rowCount)
3250 {
3251 Session tmp = session();
3252
3253 std::vector<int> data(rowCount);
3254 Statement stmt = (tmp << "INSERT INTO Strings VALUES(?)", use(data));
3255 Statement::Result result = stmt.executeAsync();
3256 assertTrue (!stmt.isAsync());
3257 result.wait();
3258
3259 Statement stmt1 = (tmp << "SELECT * FROM Strings", into(data), async, now);
3260 assertTrue (stmt1.isAsync());
3261 assertTrue (stmt1.wait() == rowCount);
3262
3263 // +++ if this part of the test case fails, increase the rowCount until achieved
3264 // that first execute is still executing when the second one is called
3265 stmt1.execute();
3266 try {
3267 stmt1.execute();
3268 fail ("execute() must fail");
3269 } catch (InvalidAccessException&)
3270 {
3271 stmt1.wait();
3272 stmt1.execute();
3273 stmt1.wait();
3274 }
3275 // ---
3276
3277 stmt = tmp << "SELECT * FROM Strings", into(data), async, now;
3278 assertTrue (stmt.isAsync());
3279 stmt.wait();
3280 assertTrue (stmt.execute() == 0);
3281
3282 // +++ if this part of the test case fails, increase the rowCount until achieved
3283 // that first execute is still executing when the second one is called
3284 try {
3285 result = stmt.executeAsync();
3286 fail ("executeAsync() must fail");
3287 } catch (InvalidAccessException&)
3288 {
3289 assertTrue (stmt.isAsync());
3290 stmt.wait();
3291 result = stmt.executeAsync();
3292 }
3293 // ---
3294
3295 assertTrue (stmt.wait() == rowCount);
3296 assertTrue (result.data() == rowCount);
3297 stmt.setAsync(false);
3298 assertTrue (!stmt.isAsync());
3299 assertTrue (stmt.execute() == rowCount);
3300
3301 stmt = tmp << "SELECT * FROM Strings", into(data), sync, now;
3302 assertTrue (!stmt.isAsync());
3303 assertTrue (stmt.wait() == 0);
3304 assertTrue (stmt.execute() == rowCount);
3305 result = stmt.executeAsync();
3306 assertTrue (!stmt.isAsync());
3307 result.wait();
3308 assertTrue (result.data() == rowCount);
3309
3310 assertTrue (0 == rowCount % 10);
3311 int step = (int) (rowCount/10);
3312 data.clear();
3313 Statement stmt2 = (tmp << "SELECT * FROM Strings", into(data), async, limit(step));
3314 assertTrue (data.size() == 0);
3315 assertTrue (!stmt2.done());
3316 std::size_t rows = 0;
3317
3318 for (int i = 0; !stmt2.done(); i += step)
3319 {
3320 stmt2.execute();
3321 rows = stmt2.wait();
3322 assertTrue (step == rows);
3323 assertTrue (step + i == data.size());
3324 }
3325 assertTrue (stmt2.done());
3326 assertTrue (rowCount == data.size());
3327
3328 stmt2 = tmp << "SELECT * FROM Strings", reset;
3329 assertTrue (!stmt2.isAsync());
3330 assertTrue ("deque" == stmt2.getStorage());
3331 assertTrue (stmt2.execute() == rowCount);
3332 }
3333
3334
any()3335 void SQLExecutor::any()
3336 {
3337 Any i = 42;
3338 Any f = 42.5;
3339 std::string ss("42");
3340 Any s = ss;
3341 #ifdef POCO_ODBC_UNICODE
3342 UTF16String us;
3343 Poco::UnicodeConverter::convert(ss, us);
3344 s = us;
3345 #endif
3346 Session tmp = session();
3347
3348 tmp << "INSERT INTO Anys VALUES (?, ?, ?)", use(i), use(f), use(s), now;
3349
3350 int count = 0;
3351 tmp << "SELECT COUNT(*) FROM Anys", into(count), now;
3352 assertTrue (1 == count);
3353
3354 i = 0;
3355 f = 0.0;
3356 s = std::string("");
3357 tmp << "SELECT * FROM Anys", into(i), into(f), into(s), now;
3358 assertTrue (AnyCast<int>(i) == 42);
3359 assertTrue (AnyCast<double>(f) == 42.5);
3360 #ifdef POCO_ODBC_UNICODE
3361 // drivers may behave differently here
3362 try
3363 {
3364 assertTrue (AnyCast<UTF16String>(s) == us);
3365 }
3366 catch (BadCastException&)
3367 {
3368 assertTrue (AnyCast<std::string>(s) == "42");
3369 }
3370 #else
3371 assertTrue (AnyCast<std::string>(s) == "42");
3372 #endif
3373 }
3374
3375
dynamicAny()3376 void SQLExecutor::dynamicAny()
3377 {
3378 Var i = 42;
3379 Var f = 42.5;
3380 Var s = "42";
3381
3382 Session tmp = session();
3383 tmp << "INSERT INTO Anys VALUES (?, ?, ?)", use(i), use(f), use(s), now;
3384
3385 int count = 0;
3386 tmp << "SELECT COUNT(*) FROM Anys", into(count), now;
3387 assertTrue (1 == count);
3388
3389 i = 0;
3390 f = 0.0;
3391 s = std::string("");
3392 tmp << "SELECT * FROM Anys", into(i), into(f), into(s), now;
3393 assertTrue (42 == i);
3394 assertTrue (42.5 == f);
3395 assertTrue ("42" == s);
3396 }
3397
3398
multipleResults(const std::string & sql)3399 void SQLExecutor::multipleResults(const std::string& sql)
3400 {
3401 typedef Tuple<std::string, std::string, std::string, Poco::UInt32> Person;
3402 std::vector<Person> people;
3403 people.push_back(Person("Simpson", "Homer", "Springfield", 42));
3404 people.push_back(Person("Simpson", "Marge", "Springfield", 38));
3405 people.push_back(Person("Simpson", "Bart", "Springfield", 10));
3406 people.push_back(Person("Simpson", "Lisa", "Springfield", 8));
3407 people.push_back(Person("Simpson", "Maggie", "Springfield", 3));
3408 session() << "INSERT INTO Person VALUES (?, ?, ?, ?)", use(people), now;
3409
3410 Person pHomer;
3411 int aHomer = 42, aLisa = 8;
3412 Poco::UInt32 aBart = 0;
3413
3414 Poco::UInt32 pos1 = 1;
3415 int pos2 = 2;
3416 std::vector<Person> people2;
3417 Statement stmt(session());
3418 stmt << sql, into(pHomer, from(0)), use(aHomer)
3419 , into(aBart, pos1)
3420 , into(people2, from(pos2)), use(aLisa), use(aHomer);
3421
3422 assertTrue (4 == stmt.execute());
3423 assertTrue (Person("Simpson", "Homer", "Springfield", 42) == pHomer);
3424 assertTrue (10 == aBart);
3425 assertTrue (2 == people2.size());
3426 assertTrue (Person("Simpson", "Lisa", "Springfield", 8) == people2[0]);
3427 assertTrue (Person("Simpson", "Homer", "Springfield", 42) == people2[1]);
3428 }
3429
3430
sqlChannel(const std::string & connect)3431 void SQLExecutor::sqlChannel(const std::string& connect)
3432 {
3433 try
3434 {
3435 AutoPtr<SQLChannel> pChannel = new SQLChannel(Poco::Data::ODBC::Connector::KEY, connect, "TestSQLChannel");
3436 pChannel->setProperty("keep", "2 seconds");
3437
3438 Message msgInf("InformationSource", "a Informational async message", Message::PRIO_INFORMATION);
3439 pChannel->log(msgInf);
3440 Message msgWarn("WarningSource", "b Warning async message", Message::PRIO_WARNING);
3441 pChannel->log(msgWarn);
3442 pChannel->wait();
3443
3444 pChannel->setProperty("async", "false");
3445 Message msgInfS("InformationSource", "c Informational sync message", Message::PRIO_INFORMATION);
3446 pChannel->log(msgInfS);
3447 Message msgWarnS("WarningSource", "d Warning sync message", Message::PRIO_WARNING);
3448 pChannel->log(msgWarnS);
3449
3450 RecordSet rs(session(), "SELECT * FROM T_POCO_LOG ORDER by Text");
3451 assertTrue (4 == rs.rowCount());
3452 assertTrue ("InformationSource" == rs["Source"]);
3453 assertTrue ("a Informational async message" == rs["Text"]);
3454 rs.moveNext();
3455 assertTrue ("WarningSource" == rs["Source"]);
3456 assertTrue ("b Warning async message" == rs["Text"]);
3457 rs.moveNext();
3458 assertTrue ("InformationSource" == rs["Source"]);
3459 assertTrue ("c Informational sync message" == rs["Text"]);
3460 rs.moveNext();
3461 assertTrue ("WarningSource" == rs["Source"]);
3462 assertTrue ("d Warning sync message" == rs["Text"]);
3463
3464 Thread::sleep(3000);
3465
3466 Message msgInfA("InformationSource", "e Informational sync message", Message::PRIO_INFORMATION);
3467 pChannel->log(msgInfA);
3468 Message msgWarnA("WarningSource", "f Warning sync message", Message::PRIO_WARNING);
3469 pChannel->log(msgWarnA);
3470
3471 RecordSet rs1(session(), "SELECT * FROM T_POCO_LOG_ARCHIVE");
3472 assertTrue (4 == rs1.rowCount());
3473
3474 pChannel->setProperty("keep", "");
3475 assertTrue ("forever" == pChannel->getProperty("keep"));
3476 RecordSet rs2(session(), "SELECT * FROM T_POCO_LOG ORDER by Text");
3477 assertTrue (2 == rs2.rowCount());
3478 assertTrue ("InformationSource" == rs2["Source"]);
3479 assertTrue ("e Informational sync message" == rs2["Text"]);
3480 rs2.moveNext();
3481 assertTrue ("WarningSource" == rs2["Source"]);
3482 assertTrue ("f Warning sync message" == rs2["Text"]);
3483
3484 }
3485 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("sqlChannel()"); }
3486 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("sqlChannel()"); }
3487 }
3488
3489
sqlLogger(const std::string & connect)3490 void SQLExecutor::sqlLogger(const std::string& connect)
3491 {
3492 try
3493 {
3494 Logger& root = Logger::root();
3495 root.setChannel(new SQLChannel(Poco::Data::ODBC::Connector::KEY, connect, "TestSQLChannel"));
3496 root.setLevel(Message::PRIO_INFORMATION);
3497
3498 root.information("a Informational message");
3499 root.warning("b Warning message");
3500 root.debug("Debug message");
3501
3502 Thread::sleep(100);
3503 RecordSet rs(session(), "SELECT * FROM T_POCO_LOG ORDER by Text");
3504 assertTrue (2 == rs.rowCount());
3505 assertTrue ("TestSQLChannel" == rs["Source"]);
3506 assertTrue ("a Informational message" == rs["Text"]);
3507 rs.moveNext();
3508 assertTrue ("TestSQLChannel" == rs["Source"]);
3509 assertTrue ("b Warning message" == rs["Text"]);
3510 }
3511 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("sqlLogger()"); }
3512 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("sqlLogger()"); }
3513 }
3514
3515
setTransactionIsolation(Session & session,Poco::UInt32 ti)3516 void SQLExecutor::setTransactionIsolation(Session& session, Poco::UInt32 ti)
3517 {
3518 if (session.hasTransactionIsolation(ti))
3519 {
3520 std::string funct = "setTransactionIsolation()";
3521
3522 try
3523 {
3524 Transaction t(session, false);
3525 t.setIsolation(ti);
3526
3527 assertTrue (ti == t.getIsolation());
3528 assertTrue (t.isIsolation(ti));
3529
3530 assertTrue (ti == session.getTransactionIsolation());
3531 assertTrue (session.isTransactionIsolation(ti));
3532 }
3533 catch(Poco::Exception& e){ std::cout << funct << ':' << e.displayText() << std::endl;}
3534 }
3535 else
3536 {
3537 std::cerr << '[' << name() << ']' << " Warning, transaction isolation not supported: ";
3538 switch (ti)
3539 {
3540 case Session::TRANSACTION_READ_COMMITTED:
3541 std::cerr << "READ COMMITTED"; break;
3542 case Session::TRANSACTION_READ_UNCOMMITTED:
3543 std::cerr << "READ UNCOMMITTED"; break;
3544 case Session::TRANSACTION_REPEATABLE_READ:
3545 std::cerr << "REPEATABLE READ"; break;
3546 case Session::TRANSACTION_SERIALIZABLE:
3547 std::cerr << "SERIALIZABLE"; break;
3548 default:
3549 std::cerr << "UNKNOWN"; break;
3550 }
3551 std::cerr << std::endl;
3552 }
3553 }
3554
3555
sessionTransaction(const std::string & connect)3556 void SQLExecutor::sessionTransaction(const std::string& connect)
3557 {
3558 if (!session().canTransact())
3559 {
3560 std::cout << "Session not capable of transactions." << std::endl;
3561 return;
3562 }
3563
3564 Session local("odbc", connect);
3565 local.setFeature("autoCommit", true);
3566
3567 std::string funct = "transaction()";
3568 std::vector<std::string> lastNames;
3569 std::vector<std::string> firstNames;
3570 std::vector<std::string> addresses;
3571 std::vector<int> ages;
3572 std::string tableName("Person");
3573 lastNames.push_back("LN1");
3574 lastNames.push_back("LN2");
3575 firstNames.push_back("FN1");
3576 firstNames.push_back("FN2");
3577 addresses.push_back("ADDR1");
3578 addresses.push_back("ADDR2");
3579 ages.push_back(1);
3580 ages.push_back(2);
3581 int count = 0, locCount = 0;
3582 std::string result;
3583
3584 bool autoCommit = session().getFeature("autoCommit");
3585
3586 session().setFeature("autoCommit", true);
3587 assertTrue (!session().isTransaction());
3588 session().setFeature("autoCommit", false);
3589 assertTrue (!session().isTransaction());
3590
3591 setTransactionIsolation(session(), Session::TRANSACTION_READ_UNCOMMITTED);
3592 setTransactionIsolation(session(), Session::TRANSACTION_REPEATABLE_READ);
3593 setTransactionIsolation(session(), Session::TRANSACTION_SERIALIZABLE);
3594
3595 setTransactionIsolation(session(), Session::TRANSACTION_READ_COMMITTED);
3596
3597 session().begin();
3598 assertTrue (session().isTransaction());
3599 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
3600 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3601 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3602 assertTrue (session().isTransaction());
3603
3604 Statement stmt = (local << "SELECT COUNT(*) FROM Person", into(locCount), async, now);
3605
3606 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
3607 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3608 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3609 assertTrue (2 == count);
3610 assertTrue (session().isTransaction());
3611 session().rollback();
3612 assertTrue (!session().isTransaction());
3613
3614 stmt.wait();
3615 assertTrue (0 == locCount);
3616
3617 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3618 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3619 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3620 assertTrue (0 == count);
3621 assertTrue (!session().isTransaction());
3622
3623 session().begin();
3624 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
3625 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3626 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3627 assertTrue (session().isTransaction());
3628
3629 Statement stmt1 = (local << "SELECT COUNT(*) FROM Person", into(locCount), async, now);
3630
3631 session().commit();
3632 assertTrue (!session().isTransaction());
3633
3634 stmt1.wait();
3635 assertTrue (2 == locCount);
3636
3637 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3638 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3639 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3640 assertTrue (2 == count);
3641
3642 session().setFeature("autoCommit", autoCommit);
3643 }
3644
3645
transaction(const std::string & connect)3646 void SQLExecutor::transaction(const std::string& connect)
3647 {
3648 if (!session().canTransact())
3649 {
3650 std::cout << "Session not transaction-capable." << std::endl;
3651 return;
3652 }
3653
3654 Session local("odbc", connect);
3655 local.setFeature("autoCommit", true);
3656
3657 setTransactionIsolation(session(), Session::TRANSACTION_READ_COMMITTED);
3658 if (local.hasTransactionIsolation(Session::TRANSACTION_READ_UNCOMMITTED))
3659 setTransactionIsolation(local, Session::TRANSACTION_READ_UNCOMMITTED);
3660 else if (local.hasTransactionIsolation(Session::TRANSACTION_READ_COMMITTED))
3661 setTransactionIsolation(local, Session::TRANSACTION_READ_COMMITTED);
3662
3663 std::string funct = "transaction()";
3664 std::vector<std::string> lastNames;
3665 std::vector<std::string> firstNames;
3666 std::vector<std::string> addresses;
3667 std::vector<int> ages;
3668 std::string tableName("Person");
3669 lastNames.push_back("LN1");
3670 lastNames.push_back("LN2");
3671 firstNames.push_back("FN1");
3672 firstNames.push_back("FN2");
3673 addresses.push_back("ADDR1");
3674 addresses.push_back("ADDR2");
3675 ages.push_back(1);
3676 ages.push_back(2);
3677 int count = 0, locCount = 0;
3678 std::string result;
3679
3680 bool autoCommit = session().getFeature("autoCommit");
3681
3682 session().setFeature("autoCommit", true);
3683 assertTrue (!session().isTransaction());
3684 session().setFeature("autoCommit", false);
3685 assertTrue (!session().isTransaction());
3686 session().setTransactionIsolation(Session::TRANSACTION_READ_COMMITTED);
3687
3688 {
3689 Transaction trans(session());
3690 assertTrue (trans.isActive());
3691 assertTrue (session().isTransaction());
3692
3693 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
3694 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3695 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3696
3697 assertTrue (session().isTransaction());
3698 assertTrue (trans.isActive());
3699
3700 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
3701 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3702 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3703 assertTrue (2 == count);
3704 assertTrue (session().isTransaction());
3705 assertTrue (trans.isActive());
3706 }
3707 assertTrue (!session().isTransaction());
3708
3709 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3710 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3711 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3712 assertTrue (0 == count);
3713 assertTrue (!session().isTransaction());
3714
3715 {
3716 Transaction trans(session());
3717 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
3718 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3719 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3720
3721 Statement stmt1 = (local << "SELECT COUNT(*) FROM Person", into(locCount), async, now);
3722
3723 assertTrue (session().isTransaction());
3724 assertTrue (trans.isActive());
3725 trans.commit();
3726 assertTrue (!session().isTransaction());
3727 assertTrue (!trans.isActive());
3728
3729 stmt1.wait();
3730 assertTrue (2 == locCount);
3731 }
3732
3733 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3734 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3735 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3736 assertTrue (2 == count);
3737
3738 try { session() << "DELETE FROM Person", now; }
3739 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3740 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3741
3742 Statement stmt1 = (local << "SELECT count(*) FROM Person", into(locCount), async, now);
3743
3744 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3745 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3746 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3747 assertTrue (0 == count);
3748 try
3749 {
3750 stmt1.wait(5000);
3751 if (local.getTransactionIsolation() == Session::TRANSACTION_READ_UNCOMMITTED)
3752 assertTrue (0 == locCount);
3753 } catch (TimeoutException&)
3754 { std::cerr << '[' << name() << ']' << " Warning: async query timed out." << std::endl; }
3755 session().commit();
3756 // repeat for those that don't support uncommitted read isolation
3757 if (local.getTransactionIsolation() == Session::TRANSACTION_READ_COMMITTED)
3758 {
3759 stmt1.wait();
3760 local << "SELECT count(*) FROM Person", into(locCount), now;
3761 assertTrue (0 == locCount);
3762 }
3763
3764 std::string sql1 = format("INSERT INTO Person VALUES ('%s','%s','%s',%d)", lastNames[0], firstNames[0], addresses[0], ages[0]);
3765 std::string sql2 = format("INSERT INTO Person VALUES ('%s','%s','%s',%d)", lastNames[1], firstNames[1], addresses[1], ages[1]);
3766 std::vector<std::string> sql;
3767 sql.push_back(sql1);
3768 sql.push_back(sql2);
3769
3770 Transaction trans(session());
3771
3772 trans.execute(sql1, false);
3773 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3774 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3775 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3776 assertTrue (1 == count);
3777 trans.execute(sql2, false);
3778 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3779 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3780 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3781 assertTrue (2 == count);
3782
3783 Statement stmt2 = (local << "SELECT COUNT(*) FROM Person", into(locCount), async, now);
3784
3785 trans.rollback();
3786
3787 stmt2.wait();
3788 assertTrue (0 == locCount);
3789
3790 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3791 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3792 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3793 assertTrue (0 == count);
3794
3795 trans.execute(sql);
3796
3797 Statement stmt3 = (local << "SELECT COUNT(*) FROM Person", into(locCount), now);
3798 assertTrue (2 == locCount);
3799
3800 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3801 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3802 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3803 assertTrue (2 == count);
3804
3805 session().setFeature("autoCommit", autoCommit);
3806 }
3807
3808
3809 struct TestCommitTransactor
3810 {
operator ()TestCommitTransactor3811 void operator () (Session& session) const
3812 {
3813 session << "INSERT INTO Person VALUES ('lastName','firstName','address',10)", now;
3814 }
3815 };
3816
3817
3818 struct TestRollbackTransactor
3819 {
operator ()TestRollbackTransactor3820 void operator () (Session& session) const
3821 {
3822 session << "INSERT INTO Person VALUES ('lastName','firstName','address',10)", now;
3823 throw Poco::Exception("test");
3824 }
3825 };
3826
3827
transactor()3828 void SQLExecutor::transactor()
3829 {
3830 std::string funct = "transaction()";
3831 int count = 0;
3832
3833 bool autoCommit = session().getFeature("autoCommit");
3834 session().setFeature("autoCommit", false);
3835 session().setTransactionIsolation(Session::TRANSACTION_READ_COMMITTED);
3836
3837 TestCommitTransactor ct;
3838 Transaction t1(session(), ct);
3839
3840 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3841 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3842 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3843 assertTrue (1 == count);
3844
3845 try { session() << "DELETE FROM Person", now; session().commit();}
3846 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3847 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3848
3849 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3850 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3851 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3852 assertTrue (0 == count);
3853
3854 try
3855 {
3856 TestRollbackTransactor rt;
3857 Transaction t(session(), rt);
3858 fail ("must fail");
3859 } catch (Poco::Exception&) { }
3860
3861 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3862 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3863 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3864 assertTrue (0 == count);
3865
3866 try
3867 {
3868 TestRollbackTransactor rt;
3869 Transaction t(session());
3870 t.transact(rt);
3871 fail ("must fail");
3872 } catch (Poco::Exception&) { }
3873
3874 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3875 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3876 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3877 assertTrue (0 == count);
3878
3879 try
3880 {
3881 TestRollbackTransactor rt;
3882 Transaction t(session(), false);
3883 t.transact(rt);
3884 fail ("must fail");
3885 } catch (Poco::Exception&) { }
3886
3887 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3888 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3889 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3890 assertTrue (0 == count);
3891
3892 try
3893 {
3894 TestRollbackTransactor rt;
3895 Transaction t(session(), true);
3896 t.transact(rt);
3897 fail ("must fail");
3898 } catch (Poco::Exception&) { }
3899
3900 try { session() << "SELECT count(*) FROM Person", into(count), now; }
3901 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3902 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3903 assertTrue (0 == count);
3904
3905 session().setFeature("autoCommit", autoCommit);
3906 }
3907
3908
nullable()3909 void SQLExecutor::nullable()
3910 {
3911 try { session() << "INSERT INTO NullableTest VALUES(NULL, NULL, NULL, NULL)", now; }
3912 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("nullable()"); }
3913 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("nullable()"); }
3914
3915 Nullable<int> i = 1;
3916 Nullable<double> f = 1.5;
3917 Nullable<std::string> s = std::string("abc");
3918 Nullable<DateTime> d = DateTime();
3919
3920 assertTrue (!i.isNull());
3921 assertTrue (!f.isNull());
3922 assertTrue (!s.isNull());
3923 assertTrue (!d.isNull());
3924
3925 session() << "SELECT EmptyString, EmptyInteger, EmptyFloat, EmptyDateTime FROM NullableTest", into(s), into(i), into(f), into(d), now;
3926
3927 assertTrue (i.isNull());
3928 assertTrue (f.isNull());
3929 assertTrue (s.isNull());
3930 assertTrue (d.isNull());
3931
3932 RecordSet rs(session(), "SELECT * FROM NullableTest");
3933
3934 rs.moveFirst();
3935 assertTrue (rs.isNull("EmptyString"));
3936 assertTrue (rs.isNull("EmptyInteger"));
3937 assertTrue (rs.isNull("EmptyFloat"));
3938 assertTrue (rs.isNull("EmptyDateTime"));
3939
3940 Var di = 1;
3941 Var df = 1.5;
3942 Var ds = "abc";
3943 Var dd = DateTime();
3944
3945 assertTrue (!di.isEmpty());
3946 assertTrue (!df.isEmpty());
3947 assertTrue (!ds.isEmpty());
3948 assertTrue (!dd.isEmpty());
3949
3950 Statement stmt = (session() << "SELECT EmptyString, EmptyInteger, EmptyFloat, EmptyDateTime FROM NullableTest", into(ds), into(di), into(df), into(dd), now);
3951
3952 assertTrue (di.isEmpty());
3953 assertTrue (df.isEmpty());
3954 assertTrue (ds.isEmpty());
3955 assertTrue (dd.isEmpty());
3956 }
3957
3958
reconnect()3959 void SQLExecutor::reconnect()
3960 {
3961 std::string funct = "reconnect()";
3962 std::string lastName = "lastName";
3963 std::string firstName("firstName");
3964 std::string address("Address");
3965 int age = 133132;
3966 int count = 0;
3967 std::string result;
3968
3969 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(age), now; }
3970 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3971 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3972
3973 count = 0;
3974 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
3975 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3976 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3977 assertTrue (count == 1);
3978
3979 assertTrue (session().isConnected());
3980 session().close();
3981 assertTrue (!session().isConnected());
3982 try
3983 {
3984 session() << "SELECT LastName FROM Person", into(result), now;
3985 fail ("must fail");
3986 }
3987 catch(NotConnectedException&){ }
3988 assertTrue (!session().isConnected());
3989
3990 session().open();
3991 assertTrue (session().isConnected());
3992 try { session() << "SELECT Age FROM Person", into(count), now; }
3993 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
3994 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
3995 assertTrue (count == age);
3996 assertTrue (session().isConnected());
3997 }
3998
3999
unicode(const std::string & dbConnString)4000 void SQLExecutor::unicode(const std::string& dbConnString)
4001 {
4002 const unsigned char supp[] = { 0x41, 0x42, 0xf0, 0x90, 0x82, 0xa4, 0xf0, 0xaf, 0xa6, 0xa0, 0xf0, 0xaf, 0xa8, 0x9d, 0x00 };
4003 std::string text((const char*) supp);
4004
4005 UTF16String wtext;
4006 Poco::UnicodeConverter::convert(text, wtext);
4007 session() << "INSERT INTO UnicodeTable VALUES (?)", use(wtext), now;
4008 wtext.clear();
4009 text.clear();
4010 session() << "SELECT str FROM UnicodeTable", into(wtext), now;
4011 Poco::UnicodeConverter::convert(wtext, text);
4012 assertTrue (text == std::string((const char*)supp));
4013 }