2
* Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
4
* This program is free software; you can redistribute it and/or
5
* modify it under the terms of the GNU General Public License as
6
* published by the Free Software Foundation; version 2 of the
9
* This program is distributed in the hope that it will be useful,
10
* but WITHOUT ANY WARRANTY; without even the implied warranty of
11
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12
* GNU General Public License for more details.
14
* You should have received a copy of the GNU General Public License
15
* along with this program; if not, write to the Free Software
16
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
20
#include "tut_stdafx.h"
22
#include "connection_helpers.h"
23
#include "grtsqlparser/sql_facade.h"
25
#define DATABASE_TO_USE "USE test"
27
static bool populate_test_table(std::auto_ptr<sql::Statement> &stmt)
29
stmt->execute(DATABASE_TO_USE);
30
stmt->execute("DROP TABLE IF EXISTS test_function");
31
if (true == stmt->execute("CREATE TABLE test_function (a integer, b integer, c integer default null)"))
34
if (true == stmt->execute("INSERT INTO test_function (a,b,c) VALUES(1, 111, NULL)"))
36
stmt->execute("DROP TABLE test_function");
42
static bool populate_tx_test_table(std::auto_ptr<sql::Statement> &stmt)
44
stmt->execute(DATABASE_TO_USE);
45
stmt->execute("DROP TABLE IF EXISTS test_function_tx");
46
if (true == stmt->execute("CREATE TABLE test_function_tx (a integer, b integer, c integer default null) engine = innodb"))
49
if (true == stmt->execute("INSERT INTO test_function_tx (a,b,c) VALUES(1, 111, NULL)"))
51
stmt->execute("DROP TABLE test_function_tx");
54
stmt->getConnection()->commit();
58
BEGIN_TEST_DATA_CLASS(module_dbc_statement_test)
61
SqlFacade::Ref sql_splitter;
65
TEST_MODULE(module_dbc_statement_test, "DBC: statement tests");
70
std::string path = "../../Bin/Debug";
71
grtm.set_search_paths(path, path, path);
72
grtm.initialize(path);
77
grt->scan_metaclasses_in("../../res/grt/");
78
grt->end_loading_metaclasses();
80
ensure_equals("load structs", grt->get_metaclasses().size(), INT_METACLASS_COUNT);
82
sql_splitter= SqlFacade::instance_for_rdbms_name(grt, "Mysql");
83
ensure("failed to get sqlparser module", (NULL != sql_splitter));
86
// We don't test Statement::getWarnings() as such doesn't exist.
87
// We don't test Statement::clearWarnings() as such doesn't exist.
89
// Test construction of a statement object.
92
db_mgmt_ConnectionRef connectionProperties(grt);
94
setup_env(grt, connectionProperties);
97
sql::DriverManager *dm= sql::DriverManager::getDriverManager();
98
ensure("dm is NULL", dm != NULL);
100
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
101
ensure("conn is NULL", wrapper.get() != NULL);
103
sql::Connection* connection= wrapper.get();
105
/* Going out scope will free the object. We test this as there is no close() method */
106
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
108
} catch (sql::SQLException &) {
109
printf("ERR: Caught sql::SQLException\n");
114
// Test simple update statement against statement object.
117
db_mgmt_ConnectionRef connectionProperties(grt);
119
setup_env(grt, connectionProperties);
122
sql::DriverManager *dm= sql::DriverManager::getDriverManager();
123
ensure("dm is NULL", dm != NULL);
125
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
126
ensure("conn is NULL", wrapper.get() != NULL);
128
sql::Connection* connection= wrapper.get();
130
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
131
ensure("stmt is NULL", stmt.get() != NULL);
133
ensure("Data not populated", true == populate_test_table(stmt));
134
if (true == stmt->execute("UPDATE test_function SET a = 2, b = 222 where b = 111"))
135
ensure("True returned for UPDATE", false);
137
stmt->execute("DROP TABLE test_function");
138
} catch (sql::SQLException &) {
139
printf("ERR: Caught sql::SQLException\n");
142
printf("ERR: Caught unknown exception\n");
147
// Test simple query against statement object.
150
db_mgmt_ConnectionRef connectionProperties(grt);
152
setup_env(grt, connectionProperties);
155
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
156
ensure("dm is NULL", dm != NULL);
158
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
159
ensure("conn is NULL", wrapper.get() != NULL);
161
sql::Connection* connection= wrapper.get();
163
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
164
ensure("stmt is NULL", stmt.get() != NULL);
166
ensure("Data not populated", true == populate_test_table(stmt));
167
if (false == stmt->execute("SELECT * FROM test_function"))
168
ensure("False returned for SELECT", false);
171
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
172
ensure("conn2 is NULL", wrapper2.get() != NULL);
173
sql::Connection* connection2= wrapper2.get();
174
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
175
ensure("stmt is NULL", stmt2.get() != NULL);
176
stmt2->execute(DATABASE_TO_USE);
177
stmt2->execute("DROP TABLE test_function");
178
} catch (sql::SQLException &) {
179
printf("ERR: Caught sql::SQLException\n");
182
printf("ERR: Caught unknown exception\n");
187
// Test executeQuery() - returning a result set.
190
db_mgmt_ConnectionRef connectionProperties(grt);
192
setup_env(grt, connectionProperties);
195
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
196
ensure("dm is NULL", dm != NULL);
198
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
199
ensure("conn is NULL", wrapper.get() != NULL);
201
sql::Connection* connection= wrapper.get();
203
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
204
ensure("stmt is NULL", stmt.get() != NULL);
206
ensure("Data not populated", true == populate_test_table(stmt));
207
/* Get a result set */
209
std::auto_ptr<sql::ResultSet> rset(stmt->executeQuery("SELECT * FROM test_function"));
210
ensure("NULL returned for result set", rset.get() != NULL);
211
} catch (sql::SQLException &) {
212
printf("ERR: sql::SQLException caught\n");
217
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
218
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
219
sql::Connection* connection2= wrapper2.get();
220
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
221
ensure("stmt is NULL", stmt2.get() != NULL);
222
stmt2->execute(DATABASE_TO_USE);
223
stmt2->execute("DROP TABLE test_function");
224
} catch (sql::SQLException &) {
225
printf("ERR: Caught sql::SQLException\n");
228
printf("ERR: Caught unknown exception\n");
233
// Test executeQuery() - returning empty result set.
236
db_mgmt_ConnectionRef connectionProperties(grt);
238
setup_env(grt, connectionProperties);
241
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
242
ensure("dm is NULL", dm != NULL);
244
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
245
ensure("conn is NULL", wrapper.get() != NULL);
247
sql::Connection* connection= wrapper.get();
249
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
250
ensure("stmt is NULL", stmt.get() != NULL);
252
ensure("Data not populated", true == populate_test_table(stmt));
253
/* Get a result set */
255
std::auto_ptr<sql::ResultSet> rset(stmt->executeQuery("SELECT * FROM test_function WHERE 1=2"));
256
ensure("NULL returned for result set", rset.get() != NULL);
257
ensure("Non-empty result set", false == rset->next());
259
} catch (sql::SQLException &) {
260
printf("ERR: Caught sql::SQLException\n");
265
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
266
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
267
sql::Connection* connection2= wrapper2.get();
268
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
269
ensure("stmt is NULL", stmt2.get() != NULL);
270
stmt2->execute(DATABASE_TO_USE);
271
stmt2->execute("DROP TABLE test_function");
272
} catch (sql::SQLException &) {
273
printf("ERR: Caught sql::SQLException\n");
276
printf("ERR: Caught unknown exception\n");
281
// Test executeQuery() - use it for inserting, should generate an exception.
284
db_mgmt_ConnectionRef connectionProperties(grt);
286
setup_env(grt, connectionProperties);
289
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
290
ensure("dm is NULL", dm != NULL);
292
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
293
ensure("conn is NULL", wrapper.get() != NULL);
295
sql::Connection* connection= wrapper.get();
297
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
298
ensure("stmt is NULL", stmt.get() != NULL);
300
ensure("Data not populated", true == populate_test_table(stmt));
301
/* Get a result set */
303
std::auto_ptr<sql::ResultSet> rset(stmt->executeQuery("INSERT INTO test_function VALUES(2,200)"));
304
ensure("NULL returned for result set", rset.get() == NULL);
305
ensure("Non-empty result set", false == rset->next());
306
} catch (sql::SQLException &) {
308
printf("ERR: Incorrectly sql::SQLException ist not thrown\n");
312
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
313
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
314
sql::Connection* connection2= wrapper2.get();
315
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
316
ensure("stmt is NULL", stmt2.get() != NULL);
317
stmt2->execute(DATABASE_TO_USE);
318
stmt2->execute("DROP TABLE test_function");
319
} catch (sql::SQLException &) {
320
printf("ERR: Caught sql::SQLException\n");
323
printf("ERR: Caught unknown exception\n");
328
// Test executeUpdate() - check the returned value.
331
db_mgmt_ConnectionRef connectionProperties(grt);
333
setup_env(grt, connectionProperties);
336
sql::DriverManager *dm= sql::DriverManager::getDriverManager();
337
ensure("dm is NULL", dm != NULL);
339
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
340
ensure("conn is NULL", wrapper.get() != NULL);
342
sql::Connection* connection= wrapper.get();
344
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
345
ensure("stmt is NULL", stmt.get() != NULL);
347
ensure("Data not populated", true == populate_test_table(stmt));
348
/* Get a result set */
350
ensure_equals("Number of updated rows",
351
stmt->executeUpdate("UPDATE test_function SET a = 123"),
353
} catch (sql::SQLException &) {
354
printf("ERR: Caught sql::SQLException\n");
359
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
360
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
361
sql::Connection* connection2= wrapper2.get();
362
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
363
ensure("stmt is NULL", stmt2.get() != NULL);
364
stmt2->execute(DATABASE_TO_USE);
365
stmt2->execute("DROP TABLE test_function");
366
} catch (sql::SQLException &) {
367
printf("ERR: Caught sql::SQLException\n");
370
printf("ERR: Caught unknown exception\n");
375
// Test executeUpdate() - execute a SELECT, should get an exception
378
db_mgmt_ConnectionRef connectionProperties(grt);
380
setup_env(grt, connectionProperties);
383
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
384
ensure("dm is NULL", dm != NULL);
386
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
387
ensure("conn is NULL", wrapper.get() != NULL);
389
sql::Connection* connection= wrapper.get();
391
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
392
ensure("stmt is NULL", stmt.get() != NULL);
394
ensure("Data not populated", populate_test_table(stmt));
395
/* Get a result set */
397
stmt->executeUpdate("SELECT * FROM test_function");
398
// TODO: executing a query which returns a result set should throw an exception.
399
// fail("No exception thrown");
400
} catch (sql::SQLException &) {}
403
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
404
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
405
sql::Connection* connection2= wrapper2.get();
406
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
407
ensure("stmt is NULL", stmt2.get() != NULL);
408
stmt2->execute(DATABASE_TO_USE);
409
stmt2->execute("DROP TABLE test_function");
410
} catch (sql::SQLException &) {
411
printf("ERR: Caught sql::SQLException\n");
416
// Test getFetchSize() - should return int value.
419
db_mgmt_ConnectionRef connectionProperties(grt);
421
setup_env(grt, connectionProperties);
424
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
425
ensure("dm is NULL", dm != NULL);
427
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
428
ensure("conn is NULL", wrapper.get() != NULL);
430
sql::Connection* connection= wrapper.get();
432
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
433
ensure("stmt is NULL", stmt.get() != NULL);
435
// TODO: implement and test getFetchSize() and getFechtDirection()
436
// ensure("fetchSize not > 0", stmt->getFetchSize() > 0);
438
} catch (sql::SQLException &) {
439
printf("ERR: Caught sql::SQLException\n");
444
// We don't test Statement::getMaxRows() as such doesn't exist.
445
// We don't test Statement::getMoreResults() as such doesn't exist.
446
// We don't test Statement::getQueryTimeout() as such doesn't exist.
448
// Test getResultSet() - execute() a query and get the result set.
451
db_mgmt_ConnectionRef connectionProperties(grt);
453
setup_env(grt, connectionProperties);
456
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
457
ensure("dm is NULL", dm != NULL);
459
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
460
ensure("conn is NULL", wrapper.get() != NULL);
462
sql::Connection* connection= wrapper.get();
464
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
465
ensure("stmt is NULL", stmt.get() != NULL);
467
ensure("Data not populated", true == populate_test_table(stmt));
468
ensure("Statement::execute returned false", true == stmt->execute("SELECT * FROM test_function"));
470
std::auto_ptr<sql::ResultSet> rset(stmt->getResultSet());
471
ensure("rset is NULL", rset.get() != NULL);
474
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
475
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
476
sql::Connection* connection2= wrapper2.get();
477
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
478
ensure("stmt is NULL", stmt2.get() != NULL);
479
stmt2->execute(DATABASE_TO_USE);
480
stmt2->execute("DROP TABLE test_function");
481
} catch (sql::SQLException &) {
482
printf("ERR: Caught sql::SQLException\n");
487
// Test getResultSet() - execute() an update query and get the result set - should be empty.
488
// TODO: Doesn't test much as stmt::getResultSet() is not implemented.
491
db_mgmt_ConnectionRef connectionProperties(grt);
493
setup_env(grt, connectionProperties);
496
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
497
ensure("dm is NULL", dm != NULL);
499
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
500
ensure("conn is NULL", wrapper.get() != NULL);
502
sql::Connection* connection= wrapper.get();
504
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
505
ensure("stmt is NULL", stmt.get() != NULL);
507
ensure("Data not populated", true == populate_test_table(stmt));
509
ensure("Statement::execute returned true", false == stmt->execute("UPDATE test_function SET a = 222"));
513
std::auto_ptr<sql::ResultSet> rset(stmt->getResultSet());
514
if (NULL == rset.get())
515
throw sql::SQLException();
516
fail("Got result set for an update operation.");
518
catch (sql::SQLException &) {}
521
sql::ConnectionWrapper wrapper2= dm->getConnection(connectionProperties);
522
ensure("wrapper2 is NULL", wrapper2.get() != NULL);
523
sql::Connection* connection2= wrapper2.get();
524
std::auto_ptr<sql::Statement> stmt2(connection2->createStatement());
525
ensure("stmt is NULL", stmt2.get() != NULL);
526
stmt2->execute(DATABASE_TO_USE);
527
stmt2->execute("DROP TABLE test_function");
528
} catch (sql::SQLException &) {
529
printf("ERR: Caught sql::SQLException\n");
534
// We don't test Statement::getResultSetConcurrency() as such doesn't exist.
535
// We don't test Statement::getResultSetType() as such doesn't exist.
536
// We don't test Statement::getUpdateCount() as such doesn't exist.
537
// We don't test Statement::getWarnings() as such doesn't exist.
539
// Test setFetchSize() - set and get the value.
540
// TODO: Doesn't pass because setFetchSize() is unimplemented.
543
db_mgmt_ConnectionRef connectionProperties(grt);
545
setup_env(grt, connectionProperties);
548
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
549
ensure("dm is NULL", dm != NULL);
551
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
552
ensure("conn is NULL", wrapper.get() != NULL);
554
sql::Connection* connection= wrapper.get();
556
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
557
ensure("stmt is NULL", stmt.get() != NULL);
559
int setFetchSize = 50;
561
stmt->setFetchSize(setFetchSize);
563
ensure_equals("Non-equal", setFetchSize, stmt->getFetchSize());
565
} catch (sql::SQLException &) {
566
printf("ERR: Caught sql::SQLException\n");
571
// Test setFetchSize() - set negative value and expect an exception.
572
// TODO: Doesn't pass because setFetchSize() is unimplemented.
575
db_mgmt_ConnectionRef connectionProperties(grt);
577
setup_env(grt, connectionProperties);
580
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
581
ensure("dm is NULL", dm != NULL);
583
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
584
ensure("conn is NULL", wrapper.get() != NULL);
586
sql::Connection* connection= wrapper.get();
588
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
589
ensure("stmt is NULL", stmt.get() != NULL);
592
stmt->setFetchSize(-1);
593
ensure("No exception", false);
594
} catch (sql::InvalidArgumentException) {
595
printf("INFO: Caught sql::InvalidArgumentException\n");
598
} catch (sql::SQLException &) {
599
printf("ERR: Caught sql::SQLException\n");
604
// Test setQueryTimeout() - set negative value and expect an exception.
605
// TODO: Doesn't pass because setQueryTimeout() is unimplemented.
608
db_mgmt_ConnectionRef connectionProperties(grt);
610
setup_env(grt, connectionProperties);
613
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
614
ensure("dm is NULL", dm != NULL);
616
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
617
ensure("conn is NULL", wrapper.get() != NULL);
619
sql::Connection* connection= wrapper.get();
621
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
622
ensure("stmt is NULL", stmt.get() != NULL);
625
stmt->setQueryTimeout(-1);
626
printf("ERR: No exception\n");
627
} catch (sql::InvalidArgumentException &e) {
631
} catch (sql::SQLException &) {
632
printf("ERR: Caught sql::SQLException\n");
637
// Test addBatch()/executeBatch() (includes a test against the 'out of sync' error).
640
db_mgmt_ConnectionRef connectionProperties(grt);
642
setup_env(grt, connectionProperties);
645
sql::DriverManager *dm = sql::DriverManager::getDriverManager();
646
ensure("dm is NULL", dm != NULL);
648
sql::ConnectionWrapper wrapper= dm->getConnection(connectionProperties);
649
ensure("conn is NULL", wrapper.get() != NULL);
651
sql::Connection* connection= wrapper.get();
653
std::auto_ptr<sql::Statement> stmt(connection->createStatement());
654
ensure("stmt is NULL", stmt.get() != NULL);
656
std::string sql_script=
657
"DROP DATABASE IF EXISTS dbc_statement_test_15;"
658
"CREATE DATABASE dbc_statement_test_15;"
659
"CREATE TABLE dbc_statement_test_15.table1 (id int);"
661
"CREATE TABLE dbc_statement_test_15.table2 (id int);"
663
"CREATE TABLE dbc_statement_test_15.table3 (id int);"
665
std::list<std::string> statements;
666
ensure("failed to get sqlparser module", (NULL != sql_splitter));
667
sql_splitter->splitSqlScript(sql_script, statements);
668
sql::SqlBatchExec()(stmt.get(), statements);
670
} catch (sql::SQLException &) {
671
printf("ERR: Caught sql::SQLException\n");