1 //
2 // SQLExecutor.h
3 //
4 // Definition of the SQLExecutor class.
5 //
6 // Copyright (c) 2006, Applied Informatics Software Engineering GmbH.
7 // and Contributors.
8 //
9 // SPDX-License-Identifier: BSL-1.0
10 //
11
12
13 #ifndef SQLExecutor_INCLUDED
14 #define SQLExecutor_INCLUDED
15
16
17 #include "Poco/Data/ODBC/ODBC.h"
18 #include "Poco/Data/ODBC/Utility.h"
19 #include "Poco/Data/ODBC/ODBCException.h"
20 #include "Poco/Data/Session.h"
21 #include "Poco/Data/BulkExtraction.h"
22 #include "Poco/Data/BulkBinding.h"
23 #include "Poco/NumberFormatter.h"
24 #include "Poco/String.h"
25 #include "Poco/Exception.h"
26 #include <iostream>
27
28
29 #define poco_odbc_check_env(r, h) \
30 if (!SQL_SUCCEEDED(r)) \
31 { \
32 Poco::Data::ODBC::EnvironmentException ee(h); \
33 std::cout << ee.toString() << std::endl; \
34 } \
35 assert (SQL_SUCCEEDED(r))
36
37
38 #define poco_odbc_check_dbc(r, h) \
39 if (!SQL_SUCCEEDED(r)) \
40 { \
41 Poco::Data::ODBC::ConnectionException ce(h); \
42 std::cout << ce.toString() << std::endl; \
43 } \
44 assert (SQL_SUCCEEDED(r))
45
46
47 #define poco_odbc_check_stmt(r, h) \
48 if (!SQL_SUCCEEDED(r)) \
49 { \
50 Poco::Data::ODBC::StatementException se(h); \
51 std::cout << se.toString() << std::endl; \
52 } \
53 assert (SQL_SUCCEEDED(r))
54
55
56 #define poco_odbc_check_desc(r, h) \
57 if (!SQL_SUCCEEDED(r)) \
58 { \
59 Poco::Data::ODBC::DescriptorException de(h); \
60 std::cout << de.toString() << std::endl; \
61 } \
62 assert (SQL_SUCCEEDED(r))
63
64
65 #define poco_data_using_statements using Poco::Data::Keywords::now; \
66 using Poco::Data::Keywords::into; \
67 using Poco::Data::Keywords::use; \
68 using Poco::Data::Keywords::bulk; \
69 using Poco::Data::Keywords::limit; \
70 using Poco::Data::CLOB; \
71 using Poco::Data::ODBC::ConnectionException; \
72 using Poco::Data::ODBC::StatementException
73
74
75 class SQLExecutor: public CppUnit::TestCase
76 {
77 public:
78 enum DataBinding
79 {
80 PB_IMMEDIATE,
81 PB_AT_EXEC
82 };
83
84 enum DataExtraction
85 {
86 DE_MANUAL,
87 DE_BOUND
88 };
89
90 SQLExecutor(const std::string& name, Poco::Data::Session* _pSession);
91 ~SQLExecutor();
92
93 void execute(const std::string& sql);
94 /// Execute a query.
95
96 void bareboneODBCTest(const std::string& dbConnString,
97 const std::string& tableCreateString,
98 DataBinding bindMode,
99 DataExtraction extractMode,
100 bool doTime=true,
101 const std::string& blobPlaceholder="?");
102
103 void bareboneODBCMultiResultTest(const std::string& dbConnString,
104 const std::string& tableCreateString,
105 SQLExecutor::DataBinding bindMode,
106 SQLExecutor::DataExtraction extractMode,
107 const std::string& insert = MULTI_INSERT,
108 const std::string& select = MULTI_SELECT);
109 /// The above two functions use "bare bone" ODBC API calls
110 /// (i.e. calls are not "wrapped" in PocoData framework structures).
111 /// The purpose of the functions is to verify that a driver behaves
112 /// correctly as well as to determine its capabilities
113 /// (e.g. SQLGetData() restrictions relaxation policy, if any).
114 /// If these test pass, subsequent tests failures are likely ours.
115
116 void zeroRows();
117 void simpleAccess();
118 void complexType();
119 void complexTypeTuple();
120
121 void simpleAccessVector();
122 void complexTypeVector();
123 void sharedPtrComplexTypeVector();
124 void autoPtrComplexTypeVector();
125 void insertVector();
126 void insertEmptyVector();
127
128 void simpleAccessList();
129 void complexTypeList();
130 void insertList();
131 void insertEmptyList();
132
133 void simpleAccessDeque();
134 void complexTypeDeque();
135 void insertDeque();
136 void insertEmptyDeque();
137
138 void affectedRows(const std::string& whereClause = "");
139
140 void insertSingleBulk();
141 void insertSingleBulkVec();
142
143 void limits();
144 void limitOnce();
145 void limitPrepare();
146 void limitZero();
147 void prepare();
148
149 template <typename C1, typename C2, typename C3, typename C4, typename C5, typename C6>
150 void doBulkWithBool(Poco::UInt32 size, const std::string& blobPlaceholder="?")
151 {
152 poco_data_using_statements;
153
154 std::string funct = "doBulkWithBool()";
155 C1 ints;
156 C2 strings;
157 C3 blobs;
158 C4 floats;
159 C5 dateTimes(size);
160 C6 bools;
161
162 for (int i = 0; i < size; ++i)
163 {
164 ints.push_back(i);
165 strings.push_back(std::string("xyz" + Poco::NumberFormatter::format(i)));
166 blobs.push_back(std::string("abc") + Poco::NumberFormatter::format(i));
167 floats.push_back(i + .5);
168 bools.push_back(0 == i % 2);
169 }
170
171 try
172 {
173 session() <<
174 Poco::format("INSERT INTO MiscTest VALUES (?,%s,?,?,?,?)", blobPlaceholder),
175 use(strings),
176 use(blobs),
177 use(ints),
178 use(floats),
179 use(dateTimes),
180 use(bools), now;
catch(ConnectionException & ce)181 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
catch(StatementException & se)182 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
183
184 try { session() << "DELETE FROM MiscTest", now; }
catch(ConnectionException & ce)185 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
catch(StatementException & se)186 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
187
188 try
189 {
190 session() <<
191 Poco::format("INSERT INTO MiscTest VALUES (?,%s,?,?,?,?)", blobPlaceholder),
192 use(strings, bulk),
193 use(blobs, bulk),
194 use(ints, bulk),
195 use(floats, bulk),
196 use(dateTimes, bulk),
197 use(bools, bulk), now;
catch(ConnectionException & ce)198 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
catch(StatementException & se)199 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
200
201 ints.clear();
202 strings.clear();
203 blobs.clear();
204 floats.clear();
205 dateTimes.clear();
206 bools.clear();
207
208 try
209 {
210 session() << "SELECT * FROM MiscTest ORDER BY Third",
211 into(strings),
212 into(blobs),
213 into(ints),
214 into(floats),
215 into(dateTimes),
216 into(bools),
217 now;
catch(ConnectionException & ce)218 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
catch(StatementException & se)219 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
220
221 std::string number = Poco::NumberFormatter::format(size - 1);
222 assert (size == ints.size());
223 assert (0 == ints.front());
224 assert (size - 1 == ints.back());
225 assert (std::string("xyz0") == strings.front());
226 assert (std::string("xyz") + number == strings.back());
227 assert (CLOB("abc0") == blobs.front());
228 CLOB blob("abc");
229 blob.appendRaw(number.c_str(), number.size());
230 assert (blob == blobs.back());
231 assert (.5 == floats.front());
232 assert (floats.size() - 1 + .5 == floats.back());
233 assert (bools.front());
234 assert (((0 == ((bools.size() - 1) % 2)) == bools.back()));
235
236 ints.clear();
237
238 try
239 {
240 session() << "SELECT First FROM MiscTest", into(ints, bulk(size)), limit(size+1), now;
241 fail ("must fail");
242 }
catch(Poco::InvalidArgumentException &)243 catch(Poco::InvalidArgumentException&){ }
244
245 try
246 {
247 session() << "SELECT First FROM MiscTest", into(ints), bulk(size), now;
248 fail ("must fail");
249 }
catch(Poco::InvalidAccessException &)250 catch(Poco::InvalidAccessException&){ }
251
252 ints.clear();
253 strings.clear();
254 strings.resize(size);
255 blobs.clear();
256 floats.clear();
257 floats.resize(size);
258 dateTimes.clear();
259 bools.clear();
260 bools.resize(size);
261
262 try
263 {
264 session() << "SELECT First, Second, Third, Fourth, Fifth, Sixth FROM MiscTest ORDER BY Third",
265 into(strings, bulk),
266 into(blobs, bulk(size)),
267 into(ints, bulk(size)),
268 into(floats, bulk),
269 into(dateTimes, bulk(size)),
270 into(bools, bulk),
271 now;
catch(ConnectionException & ce)272 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
catch(StatementException & se)273 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
274
275 assert (size == ints.size());
276 assert (0 == ints.front());
277 assert (size - 1 == ints.back());
278 assert (std::string("xyz0") == strings.front());
279 assert (std::string("xyz") + number == strings.back());
280 assert (CLOB("abc0") == blobs.front());
281 blob.assignRaw("abc", 3);
282 blob.appendRaw(number.c_str(), number.size());
283 assert (blob == blobs.back());
284 assert (.5 == floats.front());
285 assert (floats.size() - 1 + .5 == floats.back());
286 assert (bools.front());
287 assert (((0 == ((bools.size() - 1) % 2)) == bools.back()));
288 }
289
290 void doBulkPerformance(Poco::UInt32 size);
291
292 template <typename C1, typename C2, typename C3, typename C4, typename C5>
doBulk(Poco::UInt32 size)293 void doBulk(Poco::UInt32 size)
294 {
295 poco_data_using_statements;
296
297 std::string funct = "doBulk()";
298 C1 ints;
299 C2 strings;
300 C3 blobs;
301 C4 floats;
302 C5 dateTimes(size);
303
304 for (int i = 0; i < size; ++i)
305 {
306 ints.push_back(i);
307 strings.push_back(std::string("xyz" + Poco::NumberFormatter::format(i)));
308 blobs.push_back(std::string("abc") + Poco::NumberFormatter::format(i));
309 floats.push_back(i + .5);
310 }
311
312 try
313 {
314 session() << "INSERT INTO MiscTest VALUES (?,?,?,?,?)",
315 use(strings),
316 use(blobs),
317 use(ints),
318 use(floats),
319 use(dateTimes), now;
320 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
321 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
322
323 try { session() << "DELETE FROM MiscTest", now; }
324 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
325 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
326
327 try
328 {
329 session() << "INSERT INTO MiscTest VALUES (?,?,?,?,?)",
330 use(strings, bulk),
331 use(blobs, bulk),
332 use(ints, bulk),
333 use(floats, bulk),
334 use(dateTimes, bulk), now;
335 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
336 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
337
338 ints.clear();
339 strings.clear();
340 blobs.clear();
341 floats.clear();
342 dateTimes.clear();
343
344 try
345 {
346 session() << "SELECT * FROM MiscTest ORDER BY First",
347 into(strings),
348 into(blobs),
349 into(ints),
350 into(floats),
351 into(dateTimes),
352 now;
353 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
354 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
355
356 std::string number = Poco::NumberFormatter::format(size - 1);
357 assert (size == ints.size());
358 assert (0 == ints.front());
359 assert (size - 1 == ints.back());
360 assert (std::string("xyz0") == strings.front());
361 assert (std::string("xyz") + number == strings.back());
362 assert (CLOB("abc0") == blobs.front());
363 CLOB blob("abc");
364 blob.appendRaw(number.c_str(), number.size());
365 assert (blob == blobs.back());
366 assert (.5 == floats.front());
367 assert (floats.size() - 1 + .5 == floats.back());
368
369 ints.clear();
370
371 try
372 {
373 session() << "SELECT First FROM MiscTest", into(ints, bulk(size)), limit(size+1), now;
374 fail ("must fail");
375 }
376 catch(Poco::InvalidArgumentException&){ }
377
378 try
379 {
380 session() << "SELECT First FROM MiscTest", into(ints), bulk(size), now;
381 fail ("must fail");
382 }
383 catch(Poco::InvalidAccessException&){ }
384
385 ints.clear();
386 strings.clear();
387 blobs.clear();
388 floats.clear();
389 dateTimes.clear();
390
391 try
392 {
393 session() << "SELECT * FROM MiscTest ORDER BY First",
394 into(strings, bulk(size)),
395 into(blobs, bulk(size)),
396 into(ints, bulk(size)),
397 into(floats, bulk(size)),
398 into(dateTimes, bulk(size)),
399 now;
400 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
401 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
402
403 assert (size == ints.size());
404 assert (0 == ints.front());
405 assert (size - 1 == ints.back());
406 assert (std::string("xyz0") == strings.front());
407 assert (std::string("xyz") + number == strings.back());
408 assert (CLOB("abc0") == blobs.front());
409 blob.assignRaw("abc", 3);
410 blob.appendRaw(number.c_str(), number.size());
411 assert (blob == blobs.back());
412 assert (.5 == floats.front());
413 assert (floats.size() - 1 + .5 == floats.back());
414 }
415
416 void setSimple();
417 void setComplex();
418 void setComplexUnique();
419 void multiSetSimple();
420 void multiSetComplex();
421 void mapComplex();
422 void mapComplexUnique();
423 void multiMapComplex();
424 void selectIntoSingle();
425 void selectIntoSingleStep();
426 void selectIntoSingleFail();
427 void lowerLimitOk();
428 void lowerLimitFail();
429 void combinedLimits();
430 void combinedIllegalLimits();
431 void ranges();
432 void illegalRange();
433 void singleSelect();
434 void emptyDB();
435
436 void blob(int bigSize = 1024, const std::string& blobPlaceholder = "?");
437
438 template <typename C1, typename C2>
blobContainer(int size)439 void blobContainer(int size)
440 {
441 poco_data_using_statements;
442
443 std::string funct = "blobContainer()";
444 C1 lastName(size, "lastname");
445 C1 firstName(size, "firstname");
446 C1 address(size, "Address");
447 C2 img(size, CLOB("0123456789", 10));
448 int count = 0;
449 try { session() << "INSERT INTO Person VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(img), now; }
450 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
451 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
452 try { session() << "SELECT COUNT(*) FROM Person", into(count), now; }
453 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
454 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
455 assert (count == size);
456
457 C2 res;
458 try { session() << "SELECT Image FROM Person", into(res), now; }
459 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
460 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
461 assert (res.size() == img.size());
462 assert (res == img);
463 }
464
465 void blobStmt();
466
467 void dateTime();
468 void date();
469 void time();
470 void floats();
471 void doubles();
472 void tuples();
473 void tupleVector();
474
475 void internalExtraction();
476 void filter(const std::string& query =
477 "SELECT * FROM Vectors ORDER BY int0 ASC",
478 const std::string& intFldName = "int0");
479
480 void internalBulkExtraction();
481 void internalBulkExtractionUTF16();
482 void internalStorageType();
483 void nulls();
484 void notNulls(const std::string& sqlState = "23502");
485 void rowIterator();
486 void stdVectorBool();
487
488 void asynchronous(int rowCount = 500);
489
490 void any();
491 void dynamicAny();
492
493 void multipleResults(const std::string& sql =
494 "SELECT * FROM Person WHERE Age = ?; "
495 "SELECT Age FROM Person WHERE FirstName = 'Bart'; "
496 "SELECT * FROM Person WHERE Age = ? OR Age = ? ORDER BY Age;");
497
498 void sqlChannel(const std::string& connect);
499 void sqlLogger(const std::string& connect);
500
501 void sessionTransaction(const std::string& connect);
502 void transaction(const std::string& connect);
503 void transactor();
504 void nullable();
505
506 void unicode(const std::string& dbConnString);
507
508 void reconnect();
509
510 private:
511 static const std::string MULTI_INSERT;
512 static const std::string MULTI_SELECT;
513
514 void setTransactionIsolation(Poco::Data::Session& session, Poco::UInt32 ti);
515
516 Poco::Data::Session& session();
517 Poco::Data::Session* _pSession;
518 };
519
520
session()521 inline Poco::Data::Session& SQLExecutor::session()
522 {
523 poco_check_ptr (_pSession);
524 return *_pSession;
525 }
526
527
528 #endif // SQLExecutor_INCLUDED
529