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