1 /****************************************************************************
2 **
3 ** Copyright (C) 2015 The Qt Company Ltd.
4 ** Contact: http://www.qt.io/licensing/
5 **
6 ** This file is part of the test suite of the Qt Toolkit.
7 **
8 ** $QT_BEGIN_LICENSE:LGPL$
9 ** Commercial License Usage
10 ** Licensees holding valid commercial Qt licenses may use this file in
11 ** accordance with the commercial license agreement provided with the
12 ** Software or, alternatively, in accordance with the terms contained in
13 ** a written agreement between you and The Qt Company. For licensing terms
14 ** and conditions see http://www.qt.io/terms-conditions. For further
15 ** information use the contact form at http://www.qt.io/contact-us.
16 **
17 ** GNU Lesser General Public License Usage
18 ** Alternatively, this file may be used under the terms of the GNU Lesser
19 ** General Public License version 2.1 or version 3 as published by the Free
20 ** Software Foundation and appearing in the file LICENSE.LGPLv21 and
21 ** LICENSE.LGPLv3 included in the packaging of this file. Please review the
22 ** following information to ensure the GNU Lesser General Public License
23 ** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
24 ** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
25 **
26 ** As a special exception, The Qt Company gives you certain additional
27 ** rights. These rights are described in The Qt Company LGPL Exception
28 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
29 **
30 ** GNU General Public License Usage
31 ** Alternatively, this file may be used under the terms of the GNU
32 ** General Public License version 3.0 as published by the Free Software
33 ** Foundation and appearing in the file LICENSE.GPL included in the
34 ** packaging of this file. Please review the following information to
35 ** ensure the GNU General Public License version 3.0 requirements will be
36 ** met: http://www.gnu.org/copyleft/gpl.html.
37 **
38 ** $QT_END_LICENSE$
39 **
40 ****************************************************************************/
41
42 #include <QtTest/QtTest>
43 #include <QtSql/QtSql>
44
45 #include "../qsqldatabase/tst_databases.h"
46
47 const QString qtest(qTableName( "qtest", __FILE__ ));
48
49 //TESTED_FILES=
50
51 class tst_QSqlQuery : public QObject
52 {
53 Q_OBJECT
54
55 public:
56 tst_QSqlQuery();
57 virtual ~tst_QSqlQuery();
58
59 public slots:
60 void initTestCase();
61 void cleanupTestCase();
62 void init();
63 void cleanup();
64
65 private slots:
value_data()66 void value_data() { generic_data(); }
67 void value();
isValid_data()68 void isValid_data() { generic_data(); }
69 void isValid();
isActive_data()70 void isActive_data() { generic_data(); }
71 void isActive();
isSelect_data()72 void isSelect_data() { generic_data(); }
73 void isSelect();
numRowsAffected_data()74 void numRowsAffected_data() { generic_data(); }
75 void numRowsAffected();
size_data()76 void size_data() { generic_data(); }
77 void size();
isNull_data()78 void isNull_data() { generic_data(); }
79 void isNull();
query_exec_data()80 void query_exec_data() { generic_data(); }
81 void query_exec();
execErrorRecovery_data()82 void execErrorRecovery_data() { generic_data(); }
83 void execErrorRecovery();
first_data()84 void first_data() { generic_data(); }
85 void first();
next_data()86 void next_data() { generic_data(); }
87 void next();
prev_data()88 void prev_data() { generic_data(); }
89 void prev();
last_data()90 void last_data() { generic_data(); }
91 void last();
seek_data()92 void seek_data() { generic_data(); }
93 void seek();
transaction_data()94 void transaction_data() { generic_data(); }
95 void transaction();
record_data()96 void record_data() { generic_data(); }
97 void record();
record_sqlite_data()98 void record_sqlite_data() { generic_data("QSQLITE"); }
99 void record_sqlite();
finish_data()100 void finish_data() { generic_data(); }
101 void finish();
sqlite_finish_data()102 void sqlite_finish_data() { generic_data(); }
103 void sqlite_finish();
nextResult_data()104 void nextResult_data() { generic_data(); }
105 void nextResult();
106
107 // forwardOnly mode need special treatment
forwardOnly_data()108 void forwardOnly_data() { generic_data(); }
109 void forwardOnly();
110
111 // bug specific tests
bitField_data()112 void bitField_data() {generic_data("QTDS"); }
113 void bitField();
nullBlob_data()114 void nullBlob_data() { generic_data("QOCI"); }
115 void nullBlob();
blob_data()116 void blob_data() { generic_data(); }
117 void blob();
rawField_data()118 void rawField_data() { generic_data("QOCI"); }
119 void rawField();
precision_data()120 void precision_data() { generic_data(); }
121 void precision();
nullResult_data()122 void nullResult_data() { generic_data(); }
123 void nullResult();
joins_data()124 void joins_data() { generic_data(); }
125 void joins();
outValues_data()126 void outValues_data() { generic_data(); }
127 void outValues();
char1Select_data()128 void char1Select_data() { generic_data(); }
129 void char1Select();
char1SelectUnicode_data()130 void char1SelectUnicode_data() { generic_data(); }
131 void char1SelectUnicode();
synonyms_data()132 void synonyms_data() { generic_data(); }
133 void synonyms();
oraOutValues_data()134 void oraOutValues_data() { generic_data("QOCI"); }
135 void oraOutValues();
mysqlOutValues_data()136 void mysqlOutValues_data() { generic_data("QMYSQL"); }
137 void mysqlOutValues();
oraClob_data()138 void oraClob_data() { generic_data("QOCI"); }
139 void oraClob();
oraLong_data()140 void oraLong_data() { generic_data("QOCI"); }
141 void oraLong();
oraOCINumber_data()142 void oraOCINumber_data() { generic_data("QOCI"); }
143 void oraOCINumber();
outValuesDB2_data()144 void outValuesDB2_data() { generic_data("QDB2"); }
145 void outValuesDB2();
storedProceduresIBase_data()146 void storedProceduresIBase_data() {generic_data("QIBASE"); }
147 void storedProceduresIBase();
oraRowId_data()148 void oraRowId_data() { generic_data("QOCI"); }
149 void oraRowId();
prepare_bind_exec_data()150 void prepare_bind_exec_data() { generic_data(); }
151 void prepare_bind_exec();
prepared_select_data()152 void prepared_select_data() { generic_data(); }
153 void prepared_select();
sqlServerLongStrings_data()154 void sqlServerLongStrings_data() { generic_data(); }
155 void sqlServerLongStrings();
invalidQuery_data()156 void invalidQuery_data() { generic_data(); }
157 void invalidQuery();
batchExec_data()158 void batchExec_data() { generic_data(); }
159 void batchExec();
oraArrayBind_data()160 void oraArrayBind_data() { generic_data(); }
161 void oraArrayBind();
lastInsertId_data()162 void lastInsertId_data() { generic_data(); }
163 void lastInsertId();
lastQuery_data()164 void lastQuery_data() { generic_data(); }
165 void lastQuery();
bindBool_data()166 void bindBool_data() { generic_data(); }
167 void bindBool();
bindWithDoubleColonCastOperator_data()168 void bindWithDoubleColonCastOperator_data() { generic_data(); }
169 void bindWithDoubleColonCastOperator();
queryOnInvalidDatabase_data()170 void queryOnInvalidDatabase_data() { generic_data(); }
171 void queryOnInvalidDatabase();
createQueryOnClosedDatabase_data()172 void createQueryOnClosedDatabase_data() { generic_data(); }
173 void createQueryOnClosedDatabase();
seekForwardOnlyQuery_data()174 void seekForwardOnlyQuery_data() { generic_data(); }
175 void seekForwardOnlyQuery();
reExecutePreparedForwardOnlyQuery_data()176 void reExecutePreparedForwardOnlyQuery_data() { generic_data(); }
177 void reExecutePreparedForwardOnlyQuery();
blobsPreparedQuery_data()178 void blobsPreparedQuery_data() { generic_data(); }
179 void blobsPreparedQuery();
emptyTableNavigate_data()180 void emptyTableNavigate_data() { generic_data(); }
181 void emptyTableNavigate();
182
183 #ifdef NOT_READY_YET
184 void task_229811();
task_229811_data()185 void task_229811_data() { generic_data(); }
task_234422_data()186 void task_234422_data() { generic_data(); }
187 void task_234422();
188 #endif
task_217003_data()189 void task_217003_data() { generic_data(); }
190 void task_217003();
191
task_250026_data()192 void task_250026_data() { generic_data("QODBC"); }
193 void task_250026();
task_205701_data()194 void task_205701_data() { generic_data("QMYSQL"); }
195 void task_205701();
196
task_233829_data()197 void task_233829_data() { generic_data("QPSQL"); }
198 void task_233829();
199
sqlServerReturn0_data()200 void sqlServerReturn0_data() { generic_data(); }
201 void sqlServerReturn0();
202
QTBUG_551_data()203 void QTBUG_551_data() { generic_data("QOCI"); }
204 void QTBUG_551();
205
QTBUG_5251_data()206 void QTBUG_5251_data() { generic_data("QPSQL"); }
207 void QTBUG_5251();
QTBUG_6421_data()208 void QTBUG_6421_data() { generic_data("QOCI"); }
209 void QTBUG_6421();
QTBUG_6618_data()210 void QTBUG_6618_data() { generic_data("QODBC"); }
211 void QTBUG_6618();
QTBUG_6852_data()212 void QTBUG_6852_data() { generic_data("QMYSQL"); }
213 void QTBUG_6852();
QTBUG_5765_data()214 void QTBUG_5765_data() { generic_data("QMYSQL"); }
215 void QTBUG_5765();
QTBUG_14132_data()216 void QTBUG_14132_data() { generic_data("QOCI"); }
217 void QTBUG_14132();
QTBUG_21884_data()218 void QTBUG_21884_data() { generic_data("QSQLITE"); }
219 void QTBUG_21884();
QTBUG_16967_data()220 void QTBUG_16967_data() { generic_data("QSQLITE"); }
221 void QTBUG_16967(); //clean close
QTBUG_14904_data()222 void QTBUG_14904_data() { generic_data("QSQLITE"); }
223 void QTBUG_14904();
224
QTBUG_2192_data()225 void QTBUG_2192_data() { generic_data(); }
226 void QTBUG_2192();
227
sqlite_constraint_data()228 void sqlite_constraint_data() { generic_data("QSQLITE"); }
229 void sqlite_constraint();
230
sqlite_real_data()231 void sqlite_real_data() { generic_data("QSQLITE"); }
232 void sqlite_real();
233
aggregateFunctionTypes_data()234 void aggregateFunctionTypes_data() { generic_data(); }
235 void aggregateFunctionTypes();
236 private:
237 // returns all database connections
238 void generic_data(const QString &engine=QString());
239 void dropTestTables( QSqlDatabase db );
240 void createTestTables( QSqlDatabase db );
241 void populateTestTables( QSqlDatabase db );
242
243 tst_Databases dbs;
244 };
245
tst_QSqlQuery()246 tst_QSqlQuery::tst_QSqlQuery()
247 {
248 }
249
~tst_QSqlQuery()250 tst_QSqlQuery::~tst_QSqlQuery()
251 {
252 }
253
initTestCase()254 void tst_QSqlQuery::initTestCase()
255 {
256 dbs.open();
257
258 for ( QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it ) {
259 QSqlDatabase db = QSqlDatabase::database(( *it ) );
260 CHECK_DATABASE( db );
261 dropTestTables( db ); //in case of leftovers
262 createTestTables( db );
263 populateTestTables( db );
264 }
265 }
266
cleanupTestCase()267 void tst_QSqlQuery::cleanupTestCase()
268 {
269 for ( QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it ) {
270 QSqlDatabase db = QSqlDatabase::database(( *it ) );
271 CHECK_DATABASE( db );
272 dropTestTables( db );
273 }
274
275 dbs.close();
276 }
277
init()278 void tst_QSqlQuery::init()
279 {
280 }
281
cleanup()282 void tst_QSqlQuery::cleanup()
283 {
284 QFETCH( QString, dbName );
285 QSqlDatabase db = QSqlDatabase::database( dbName );
286 CHECK_DATABASE( db );
287
288 if ( QTest::currentTestFunction() == QLatin1String( "numRowsAffected" )
289 || QTest::currentTestFunction() == QLatin1String( "transactions" )
290 || QTest::currentTestFunction() == QLatin1String( "size" )
291 || QTest::currentTestFunction() == QLatin1String( "isActive" )
292 || QTest::currentTestFunction() == QLatin1String( "lastInsertId" ) ) {
293 populateTestTables( db );
294 }
295
296 if ( QTest::currentTestFailed() && ( db.driverName().startsWith( "QOCI" )
297 || db.driverName().startsWith( "QODBC" ) ) ) {
298 //since Oracle ODBC totally craps out on error, we init again
299 db.close();
300 db.open();
301 }
302 }
303
generic_data(const QString & engine)304 void tst_QSqlQuery::generic_data(const QString& engine)
305 {
306 if ( dbs.fillTestTable(engine) == 0 ) {
307 if(engine.isEmpty())
308 QSKIP( "No database drivers are available in this Qt configuration", SkipAll );
309 else
310 QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit(), SkipAll );
311 }
312 }
313
dropTestTables(QSqlDatabase db)314 void tst_QSqlQuery::dropTestTables( QSqlDatabase db )
315 {
316 QStringList tablenames;
317 // drop all the table in case a testcase failed
318 tablenames << qtest
319 << qTableName( "qtest_null", __FILE__ )
320 << qTableName( "qtest_blob", __FILE__ )
321 << qTableName( "qtest_bittest", __FILE__ )
322 << qTableName( "qtest_nullblob", __FILE__ )
323 << qTableName( "qtest_rawtest", __FILE__ )
324 << qTableName( "qtest_precision", __FILE__ )
325 << qTableName( "qtest_prepare", __FILE__ )
326 << qTableName( "qtestj1", __FILE__ )
327 << qTableName( "qtestj2", __FILE__ )
328 << qTableName( "char1Select", __FILE__ )
329 << qTableName( "char1SU", __FILE__ )
330 << qTableName( "qxmltest", __FILE__ )
331 << qTableName( "qtest_exerr", __FILE__ )
332 << qTableName( "qtest_empty", __FILE__ )
333 << qTableName( "clobby", __FILE__ )
334 << qTableName( "bindtest", __FILE__ )
335 << qTableName( "more_results", __FILE__ )
336 << qTableName( "blobstest", __FILE__ )
337 << qTableName( "oraRowId", __FILE__ )
338 << qTableName( "qtest_batch", __FILE__ )
339 << qTableName("bug6421", __FILE__).toUpper()
340 << qTableName("bug5765", __FILE__)
341 << qTableName("bug6852", __FILE__)
342 << qTableName("bug21884", __FILE__)
343 << qTableName( "qtest_lockedtable", __FILE__ )
344 << qTableName( "Planet", __FILE__ )
345 << qTableName( "task_250026", __FILE__ )
346 << qTableName( "task_234422", __FILE__ )
347 << qTableName("test141895", __FILE__)
348 << qTableName("qtest_oraOCINumber", __FILE__)
349 << qTableName( "bug2192", __FILE__);
350
351 if ( db.driverName().startsWith("QPSQL") )
352 tablenames << qTableName("task_233829", __FILE__);
353
354 if ( db.driverName().startsWith("QSQLITE") )
355 tablenames << qTableName( "record_sqlite", __FILE__ );
356
357 if ( tst_Databases::isSqlServer( db ) || db.driverName().startsWith( "QOCI" ) )
358 tablenames << qTableName( "qtest_longstr", __FILE__ );
359
360 if (tst_Databases::isSqlServer( db ))
361 db.exec("DROP PROCEDURE " + qTableName("test141895_proc", __FILE__));
362
363 if (tst_Databases::isMySQL( db ))
364 db.exec("DROP PROCEDURE IF EXISTS "+qTableName("bug6852_proc", __FILE__));
365
366 tst_Databases::safeDropTables( db, tablenames );
367
368 if ( db.driverName().startsWith( "QOCI" ) ) {
369 QSqlQuery q( db );
370 q.exec( "DROP PACKAGE " + qTableName("pkg", __FILE__) );
371 }
372 }
373
createTestTables(QSqlDatabase db)374 void tst_QSqlQuery::createTestTables( QSqlDatabase db )
375 {
376 QSqlQuery q( db );
377
378 if ( db.driverName().startsWith( "QMYSQL" ) )
379 // ### stupid workaround until we find a way to hardcode this
380 // in the MySQL server startup script
381 q.exec( "set table_type=innodb" );
382 else if(tst_Databases::isPostgreSQL(db))
383 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
384
385 if(tst_Databases::isPostgreSQL(db))
386 QVERIFY_SQL( q, exec( "create table " + qtest + " (id serial NOT NULL, t_varchar varchar(20), t_char char(20), primary key(id)) WITH OIDS" ) );
387 else
388 QVERIFY_SQL( q, exec( "create table " + qtest + " (id int "+tst_Databases::autoFieldName(db) +" NOT NULL, t_varchar varchar(20), t_char char(20), primary key(id))" ) );
389
390 if ( tst_Databases::isSqlServer( db ) || db.driverName().startsWith( "QTDS" ) )
391 QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_null", __FILE__ ) + " (id int null, t_varchar varchar(20) null)" ) );
392 else
393 QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_null", __FILE__ ) + " (id int, t_varchar varchar(20))" ) );
394 }
395
populateTestTables(QSqlDatabase db)396 void tst_QSqlQuery::populateTestTables( QSqlDatabase db )
397 {
398 QSqlQuery q( db );
399 const QString qtest_null(qTableName( "qtest_null", __FILE__ ));
400 q.exec( "delete from " + qtest );
401 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (1, 'VarChar1', 'Char1')" ) );
402 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (2, 'VarChar2', 'Char2')" ) );
403 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (3, 'VarChar3', 'Char3')" ) );
404 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (4, 'VarChar4', 'Char4')" ) );
405 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (5, 'VarChar5', 'Char5')" ) );
406
407 q.exec( "delete from " + qtest_null );
408 QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (0, NULL)" ) );
409 QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (1, 'n')" ) );
410 QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (2, 'i')" ) );
411 QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (3, NULL)" ) );
412 }
413
414 // There were problems with char fields of size 1
char1Select()415 void tst_QSqlQuery::char1Select()
416 {
417 QFETCH( QString, dbName );
418 QSqlDatabase db = QSqlDatabase::database( dbName );
419 CHECK_DATABASE( db );
420
421 {
422 QSqlQuery q( db );
423 QVERIFY_SQL( q, exec( "create table " + qTableName( "char1Select", __FILE__ ) + " (id char(1))" ) );
424 QVERIFY_SQL( q, exec( "insert into " + qTableName( "char1Select", __FILE__ ) + " values ('a')" ) );
425 QVERIFY_SQL( q, exec( "select * from " + qTableName( "char1Select", __FILE__ ) ) );
426 QVERIFY( q.next() );
427
428 if ( db.driverName().startsWith( "QIBASE" ) )
429 QCOMPARE( q.value( 0 ).toString().left( 1 ), QString( "a" ) );
430 else
431 QCOMPARE( q.value( 0 ).toString(), QString( "a" ) );
432
433 QVERIFY( !q.next() );
434 }
435 }
436
char1SelectUnicode()437 void tst_QSqlQuery::char1SelectUnicode()
438 {
439 QFETCH( QString, dbName );
440 QSqlDatabase db = QSqlDatabase::database( dbName );
441 CHECK_DATABASE( db );
442
443 if(db.driverName().startsWith("QDB2"))
444 QSKIP("Needs someone with more Unicode knowledge than I have to fix", SkipSingle);
445
446 if ( db.driver()->hasFeature( QSqlDriver::Unicode ) ) {
447 QString uniStr( QChar(0x0915) ); // DEVANAGARI LETTER KA
448 QSqlQuery q( db );
449
450 if ( db.driverName().startsWith( "QMYSQL" ) && tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 )
451 QSKIP( "Test requires MySQL >= 5.0", SkipSingle );
452
453 QString createQuery;
454 const QString char1SelectUnicode(qTableName( "char1SU", __FILE__ ));
455
456 if ( tst_Databases::isSqlServer( db ) )
457 createQuery = "create table " + char1SelectUnicode + "(id nchar(1))";
458 else if ( db.driverName().startsWith( "QDB2" )
459 || db.driverName().startsWith( "QOCI" )
460 || db.driverName().startsWith( "QPSQL" ) )
461 createQuery = "create table " + char1SelectUnicode + " (id char(3))";
462 else if ( db.driverName().startsWith( "QIBASE" ) )
463 createQuery = "create table " + char1SelectUnicode +
464 " (id char(1) character set unicode_fss)";
465 else if ( db.driverName().startsWith( "QMYSQL" ) )
466 createQuery = "create table " + char1SelectUnicode + " (id char(1)) "
467 "default character set 'utf8'";
468 else
469 createQuery = "create table " + char1SelectUnicode + " (id char(1))";
470
471 QVERIFY_SQL( q, exec( createQuery ) );
472
473 QVERIFY_SQL( q, prepare( "insert into " + char1SelectUnicode + " values(?)" ) );
474
475 q.bindValue( 0, uniStr );
476
477 QVERIFY_SQL( q, exec() );
478
479 QVERIFY_SQL( q, exec( "select * from " + char1SelectUnicode ) );
480
481 QVERIFY( q.next() );
482
483 if ( !q.value( 0 ).toString().isEmpty() )
484 QCOMPARE( q.value( 0 ).toString()[ 0 ].unicode(), uniStr[0].unicode() );
485
486 QCOMPARE( q.value( 0 ).toString().trimmed(), uniStr );
487
488 QVERIFY( !q.next() );
489 }
490 else
491 QSKIP( "Database not unicode capable", SkipSingle );
492 }
493
oraRowId()494 void tst_QSqlQuery::oraRowId()
495 {
496 QFETCH( QString, dbName );
497 QSqlDatabase db = QSqlDatabase::database( dbName );
498 CHECK_DATABASE( db );
499 const QString oraRowId(qTableName("oraRowId", __FILE__));
500
501 QSqlQuery q( db );
502 QVERIFY_SQL( q, exec( "select rowid from " + qtest ) );
503 QVERIFY( q.next() );
504 QCOMPARE( q.value( 0 ).type(), QVariant::String );
505 QVERIFY( !q.value( 0 ).toString().isEmpty() );
506
507 QVERIFY_SQL( q, exec( "create table " + oraRowId + " (id char(1))" ) );
508
509 QVERIFY_SQL( q, exec( "insert into " + oraRowId + " values('a')" ) );
510 QVariant v1 = q.lastInsertId();
511 QVERIFY( v1.isValid() );
512
513 QVERIFY_SQL( q, exec( "insert into " + oraRowId + " values('b')" ) );
514 QVariant v2 = q.lastInsertId();
515 QVERIFY( v2.isValid() );
516
517 QVERIFY_SQL( q, prepare( "select * from " + oraRowId + " where rowid = ?" ) );
518 q.addBindValue( v1 );
519 QVERIFY_SQL( q, exec() );
520 QVERIFY( q.next() );
521 QCOMPARE( q.value( 0 ).toString(), QString( "a" ) );
522
523 q.addBindValue( v2 );
524 QVERIFY_SQL( q, exec() );
525 QVERIFY( q.next() );
526 QCOMPARE( q.value( 0 ).toString(), QString( "b" ) );
527 }
528
mysqlOutValues()529 void tst_QSqlQuery::mysqlOutValues()
530 {
531 QFETCH( QString, dbName );
532 QSqlDatabase db = QSqlDatabase::database( dbName );
533 CHECK_DATABASE( db );
534 const QString hello(qTableName( "hello", __FILE__ )), qtestproc(qTableName( "qtestproc", __FILE__ ));
535
536 QSqlQuery q( db );
537
538 if ( db.driverName().startsWith( "QMYSQL" ) && tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 )
539 QSKIP( "Test requires MySQL >= 5.0", SkipSingle );
540
541 q.exec( "drop function " + hello );
542
543 QVERIFY_SQL( q, exec( "create function " + hello + " (s char(20)) returns varchar(50) return concat('Hello ', s)" ) );
544
545 QVERIFY_SQL( q, exec( "select " + hello + "('world')" ) );
546 QVERIFY_SQL( q, next() );
547
548 QCOMPARE( q.value( 0 ).toString(), QString( "Hello world" ) );
549
550 QVERIFY_SQL( q, prepare( "select " + hello + "('harald')" ) );
551 QVERIFY_SQL( q, exec() );
552 QVERIFY_SQL( q, next() );
553
554 QCOMPARE( q.value( 0 ).toString(), QString( "Hello harald" ) );
555
556 QVERIFY_SQL( q, exec( "drop function " + hello ) );
557
558 q.exec( "drop procedure " + qtestproc );
559
560 QVERIFY_SQL( q, exec( "create procedure " + qtestproc + " () "
561 "BEGIN select * from " + qtest + " order by id; END" ) );
562 QVERIFY_SQL( q, exec( "call " + qtestproc + "()" ) );
563 QVERIFY_SQL( q, next() );
564 QCOMPARE( q.value( 1 ).toString(), QString( "VarChar1" ) );
565
566 QVERIFY_SQL( q, exec( "drop procedure " + qtestproc ) );
567
568 QVERIFY_SQL( q, exec( "create procedure " + qtestproc + " (OUT param1 INT) "
569 "BEGIN set param1 = 42; END" ) );
570
571 QVERIFY_SQL( q, exec( "call " + qtestproc + " (@out)" ) );
572 QVERIFY_SQL( q, exec( "select @out" ) );
573 QCOMPARE( q.record().fieldName( 0 ), QString( "@out" ) );
574 QVERIFY_SQL( q, next() );
575 QCOMPARE( q.value( 0 ).toInt(), 42 );
576
577 QVERIFY_SQL( q, exec( "drop procedure " + qtestproc ) );
578 }
579
bindBool()580 void tst_QSqlQuery::bindBool()
581 {
582 // QTBUG-27763: bool value got converted to int 127 by mysql driver becuase sizeof(bool) < sizeof(int).
583 // The problem was the way the bool value from the application was handled. It doesn't matter
584 // whether the table column type is BOOL or INT. Use INT here because all DBMSs have it and all
585 // should pass this test.
586 QFETCH( QString, dbName );
587 QSqlDatabase db = QSqlDatabase::database( dbName );
588 CHECK_DATABASE( db );
589 QSqlQuery q(db);
590
591 const QString tableName(qTableName( "bindBool", __FILE__ ));
592 q.exec("DROP TABLE " + tableName);
593 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INT, flag INT NOT NULL, PRIMARY KEY(id))"));
594
595 for (int i = 0; i < 2; ++i) {
596 bool flag = i;
597 q.prepare("INSERT INTO " + tableName + " (id, flag) VALUES(:id, :flag)");
598 q.bindValue(":id", i);
599 q.bindValue(":flag", flag);
600 QVERIFY_SQL(q, exec());
601 }
602
603 QVERIFY_SQL(q, exec("SELECT id, flag FROM " + tableName));
604 for (int i = 0; i < 2; ++i) {
605 bool flag = i;
606 QVERIFY_SQL(q, next());
607 QCOMPARE(q.value(0).toInt(), i);
608 QCOMPARE(q.value(1).toBool(), flag);
609 }
610 QVERIFY_SQL(q, exec("DROP TABLE " + tableName));
611 }
612
oraOutValues()613 void tst_QSqlQuery::oraOutValues()
614 {
615 QFETCH( QString, dbName );
616 QSqlDatabase db = QSqlDatabase::database( dbName );
617 CHECK_DATABASE( db );
618 const QString tst_outValues(qTableName("tst_outValues", __FILE__));
619
620 if ( !db.driver()->hasFeature( QSqlDriver::PreparedQueries ) ) {
621 QSKIP( "Test requires prepared query support", SkipSingle );
622 return;
623 }
624
625 QSqlQuery q( db );
626
627 q.setForwardOnly( true );
628
629 /*** outvalue int ***/
630 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x out int) is\n"
631 "begin\n"
632 " x := 42;\n"
633 "end;\n" ) );
634 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
635 q.addBindValue( 0, QSql::Out );
636 QVERIFY_SQL( q, exec() );
637 QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
638
639 // bind a null value, make sure the OCI driver resets the null flag
640 q.addBindValue( QVariant( QVariant::Int ), QSql::Out );
641 QVERIFY_SQL( q, exec() );
642 QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
643 QVERIFY( !q.boundValue( 0 ).isNull() );
644
645 /*** outvalue varchar ***/
646 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x out varchar) is\n"
647 "begin\n"
648 " x := 'blah';\n"
649 "end;\n" ) );
650 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
651 QString s1( "12345" );
652 s1.reserve( 512 );
653 q.addBindValue( s1, QSql::Out );
654 QVERIFY_SQL( q, exec() );
655 QCOMPARE( q.boundValue( 0 ).toString(), QString( "blah" ) );
656
657 /*** in/outvalue numeric ***/
658 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out numeric) is\n"
659 "begin\n"
660 " x := x + 10;\n"
661 "end;\n" ) );
662 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
663 q.addBindValue( 10, QSql::Out );
664 QVERIFY_SQL( q, exec() );
665 QCOMPARE( q.boundValue( 0 ).toInt(), 20 );
666
667 /*** in/outvalue varchar ***/
668 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out varchar) is\n"
669 "begin\n"
670 " x := 'homer';\n"
671 "end;\n" ) );
672 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
673 q.addBindValue( QString( "maggy" ), QSql::Out );
674 QVERIFY_SQL( q, exec() );
675 QCOMPARE( q.boundValue( 0 ).toString(), QString( "homer" ) );
676
677 /*** in/outvalue varchar ***/
678 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out varchar) is\n"
679 "begin\n"
680 " x := NULL;\n"
681 "end;\n" ) );
682 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
683 q.addBindValue( QString( "maggy" ), QSql::Out );
684 QVERIFY_SQL( q, exec() );
685 QVERIFY( q.boundValue( 0 ).isNull() );
686
687 /*** in/outvalue int ***/
688 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out int) is\n"
689 "begin\n"
690 " x := NULL;\n"
691 "end;\n" ) );
692 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
693 q.addBindValue( 42, QSql::Out );
694 QVERIFY_SQL( q, exec() );
695 QVERIFY( q.boundValue( 0 ).isNull() );
696
697 /*** in/outvalue varchar ***/
698 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in varchar, y out varchar) is\n"
699 "begin\n"
700 " y := x||'bubulalakikikokololo';\n"
701 "end;\n" ) );
702 QVERIFY( q.prepare( "call " + tst_outValues + "(?, ?)" ) );
703 q.addBindValue( QString( "fifi" ), QSql::In );
704 QString out;
705 out.reserve( 50 );
706 q.addBindValue( out, QSql::Out );
707 QVERIFY_SQL( q, exec() );
708 QCOMPARE( q.boundValue( 1 ).toString(), QString( "fifibubulalakikikokololo" ) );
709 }
710
oraClob()711 void tst_QSqlQuery::oraClob()
712 {
713 QFETCH( QString, dbName );
714 QSqlDatabase db = QSqlDatabase::database( dbName );
715 CHECK_DATABASE( db );
716 const QString clobby(qTableName("clobby", __FILE__));
717
718 QSqlQuery q( db );
719
720 // simple short string
721 QVERIFY_SQL( q, exec( "create table " + clobby + "(id int primary key, cl clob, bl blob)" ) );
722 QVERIFY_SQL( q, prepare( "insert into " + clobby + " (id, cl, bl) values(?, ?, ?)" ) );
723 q.addBindValue( 1 );
724 q.addBindValue( "bubu" );
725 q.addBindValue( QByteArray("bubu") );
726 QVERIFY_SQL( q, exec() );
727
728 QVERIFY_SQL( q, exec( "select bl, cl from " + clobby + " where id = 1" ) );
729 QVERIFY( q.next() );
730 QCOMPARE( q.value( 0 ).toString(), QString( "bubu" ) );
731 QCOMPARE( q.value( 1 ).toString(), QString( "bubu" ) );
732
733 // simple short string with binding
734 QVERIFY_SQL( q, prepare( "insert into " + clobby + " (id, cl, bl) values(?, ?, ?)" ) );
735 q.addBindValue( 2 );
736 q.addBindValue( "lala", QSql::Binary );
737 q.addBindValue( QByteArray("lala"), QSql::Binary );
738 QVERIFY_SQL( q, exec() );
739
740 QVERIFY_SQL( q, exec( "select bl, cl from " + clobby + " where id = 2" ) );
741 QVERIFY( q.next() );
742 QCOMPARE( q.value( 0 ).toString(), QString( "lala" ) );
743 QCOMPARE( q.value( 1 ).toString(), QString( "lala" ) );
744
745 // loooong string
746 QString loong;
747 loong.fill( QLatin1Char( 'A' ), 25000 );
748 QVERIFY_SQL( q, prepare( "insert into " + clobby + " (id, cl, bl) values(?, ?, ?)" ) );
749 q.addBindValue( 3 );
750 q.addBindValue( loong, QSql::Binary );
751 q.addBindValue( loong.toLatin1(), QSql::Binary );
752 QVERIFY_SQL( q, exec() );
753
754 QVERIFY_SQL( q, exec( "select bl, cl from " + clobby + " where id = 3" ) );
755 QVERIFY( q.next() );
756 QCOMPARE( q.value( 0 ).toString().count(), loong.count() );
757 QVERIFY( q.value( 0 ).toString() == loong );
758 QCOMPARE( q.value( 1 ).toByteArray().count(), loong.toLatin1().count() );
759 QVERIFY( q.value( 1 ).toByteArray() == loong.toLatin1() );
760 }
761
storedProceduresIBase()762 void tst_QSqlQuery::storedProceduresIBase()
763 {
764 QFETCH( QString, dbName );
765 QSqlDatabase db = QSqlDatabase::database( dbName );
766 CHECK_DATABASE( db );
767
768 QSqlQuery q( db );
769 q.exec( "drop procedure " + qTableName( "TESTPROC", __FILE__ ) );
770
771 QVERIFY_SQL( q, exec( "create procedure " + qTableName( "TESTPROC", __FILE__ ) +
772 " RETURNS (x integer, y varchar(20)) "
773 "AS BEGIN "
774 " x = 42; "
775 " y = 'Hello Anders'; "
776 "END" ) );
777
778 QVERIFY_SQL( q, prepare( "execute procedure " + qTableName( "TestProc", __FILE__ ) ) );
779 QVERIFY_SQL( q, exec() );
780
781 // check for a valid result set
782 QSqlRecord rec = q.record();
783 QCOMPARE( rec.count(), 2 );
784 QCOMPARE( rec.fieldName( 0 ).toUpper(), QString( "X" ) );
785 QCOMPARE( rec.fieldName( 1 ).toUpper(), QString( "Y" ) );
786
787 // the first next shall suceed
788 QVERIFY_SQL( q, next() );
789 QCOMPARE( q.value( 0 ).toInt(), 42 );
790 QCOMPARE( q.value( 1 ).toString(), QString( "Hello Anders" ) );
791
792 // the second next shall fail
793 QVERIFY( !q.next() );
794
795 q.exec( "drop procedure " + qTableName( "TestProc", __FILE__ ) );
796 }
797
outValuesDB2()798 void tst_QSqlQuery::outValuesDB2()
799 {
800 QFETCH( QString, dbName );
801 QSqlDatabase db = QSqlDatabase::database( dbName );
802 CHECK_DATABASE( db );
803
804 if ( !db.driver()->hasFeature( QSqlDriver::PreparedQueries ) ) {
805 QSKIP( "Test requires prepared query support", SkipSingle );
806 return;
807 }
808
809 QSqlQuery q( db );
810
811 q.setForwardOnly( true );
812
813 q.exec( "drop procedure " + qTableName( "tst_outValues", __FILE__ ) ); //non-fatal
814 QVERIFY_SQL( q, exec( "CREATE PROCEDURE " + qTableName( "tst_outValues", __FILE__ ) +
815 " (OUT x int, OUT x2 double, OUT x3 char(20))\n"
816 "LANGUAGE SQL\n"
817 "P1: BEGIN\n"
818 " SET x = 42;\n"
819 " SET x2 = 4.2;\n"
820 " SET x3 = 'Homer';\n"
821 "END P1" ) );
822
823 QVERIFY_SQL( q, prepare( "call " + qTableName( "tst_outValues", __FILE__ ) + "(?, ?, ?)" ) );
824
825 q.addBindValue( 0, QSql::Out );
826 q.addBindValue( 0.0, QSql::Out );
827 q.addBindValue( "Simpson", QSql::Out );
828
829 QVERIFY_SQL( q, exec() );
830
831 QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
832 QCOMPARE( q.boundValue( 1 ).toDouble(), 4.2 );
833 QCOMPARE( q.boundValue( 2 ).toString().trimmed(), QString( "Homer" ) );
834 }
835
outValues()836 void tst_QSqlQuery::outValues()
837 {
838 QFETCH( QString, dbName );
839 QSqlDatabase db = QSqlDatabase::database( dbName );
840 CHECK_DATABASE( db );
841 const QString tst_outValues(qTableName("tst_outValues", __FILE__));
842
843 if ( !db.driver()->hasFeature( QSqlDriver::PreparedQueries ) ) {
844 QSKIP( "Test requires prepared query support", SkipSingle );
845 return;
846 }
847
848 QSqlQuery q( db );
849
850 q.setForwardOnly( true );
851
852 if ( db.driverName().startsWith( "QOCI" ) ) {
853 QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x out int) is\n"
854 "begin\n"
855 " x := 42;\n"
856 "end;\n" ) );
857 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
858 } else if ( db.driverName().startsWith( "QDB2" ) ) {
859 q.exec( "drop procedure " + tst_outValues ); //non-fatal
860 QVERIFY_SQL( q, exec( "CREATE PROCEDURE " + tst_outValues + " (OUT x int)\n"
861 "LANGUAGE SQL\n"
862 "P1: BEGIN\n"
863 " SET x = 42;\n"
864 "END P1" ) );
865 QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
866 } else if ( tst_Databases::isSqlServer( db ) ) {
867 q.exec( "drop procedure " + tst_outValues ); //non-fatal
868 QVERIFY_SQL( q, exec( "create procedure " + tst_outValues + " (@x int out) as\n"
869 "begin\n"
870 " set @x = 42\n"
871 "end\n" ) );
872 QVERIFY( q.prepare( "{call " + tst_outValues + "(?)}" ) );
873 } else {
874 QSKIP( "Don't know how to create a stored procedure for this database server, please fix this test", SkipSingle );
875 return;
876 }
877
878 q.addBindValue( 0, QSql::Out );
879
880 QVERIFY_SQL( q, exec() );
881
882 QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
883 }
884
blob()885 void tst_QSqlQuery::blob()
886 {
887 static const int BLOBSIZE = 1024 * 10;
888 static const int BLOBCOUNT = 2;
889
890 QFETCH( QString, dbName );
891 QSqlDatabase db = QSqlDatabase::database( dbName );
892 CHECK_DATABASE( db );
893
894 if ( !db.driver()->hasFeature( QSqlDriver::BLOB ) )
895 QSKIP( "DBMS not BLOB capable", SkipSingle );
896
897 //don' make it too big otherwise sybase and mysql will complain
898 QByteArray ba( BLOBSIZE, 0 );
899
900 int i;
901
902 for ( i = 0; i < ( int )ba.size(); ++i )
903 ba[i] = i % 256;
904
905 QSqlQuery q( db );
906
907 q.setForwardOnly( true );
908
909 QString queryString = QString( "create table " + qTableName( "qtest_blob", __FILE__ ) +
910 " (id int not null primary key, t_blob %1)" ).arg( tst_Databases::blobTypeName( db, BLOBSIZE ) );
911 QVERIFY_SQL( q, exec( queryString ) );
912
913 QVERIFY_SQL( q, prepare( "insert into " + qTableName( "qtest_blob", __FILE__ ) + " (id, t_blob) values (?, ?)" ) );
914
915 for ( i = 0; i < BLOBCOUNT; ++i ) {
916 q.addBindValue( i );
917 q.addBindValue( ba );
918 QVERIFY_SQL( q, exec() );
919 }
920
921 QVERIFY_SQL( q, exec( "select * from " + qTableName( "qtest_blob", __FILE__ ) ) );
922
923 for ( i = 0; i < BLOBCOUNT; ++i ) {
924 QVERIFY( q.next() );
925 QByteArray res = q.value( 1 ).toByteArray();
926 QVERIFY2( res.size() >= ba.size(),
927 QString( "array sizes differ, expected %1, got %2" ).arg( ba.size() ).arg( res.size() ).toLatin1() );
928
929 for ( int i2 = 0; i2 < ( int )ba.size(); ++i2 ) {
930 if ( res[i2] != ba[i2] )
931 QFAIL( QString( "ByteArrays differ at position %1, expected %2, got %3" ).arg(
932 i2 ).arg(( int )( unsigned char )ba[i2] ).arg(( int )( unsigned char )res[i2] ).toLatin1() );
933 }
934 }
935 }
936
value()937 void tst_QSqlQuery::value()
938 {
939 QFETCH( QString, dbName );
940 QSqlDatabase db = QSqlDatabase::database( dbName );
941 CHECK_DATABASE( db );
942
943 QSqlQuery q( db );
944 QVERIFY_SQL( q, exec( "select id, t_varchar, t_char from " + qtest + " order by id" ) );
945 int i = 1;
946
947 while ( q.next() ) {
948 QCOMPARE( q.value( 0 ).toInt(), i );
949
950 if ( db.driverName().startsWith( "QIBASE" ) )
951 QVERIFY( q.value( 1 ).toString().startsWith( "VarChar" + QString::number( i ) ) );
952 else if ( q.value( 1 ).toString().right( 1 ) == " " )
953 QCOMPARE( q.value( 1 ).toString(), ( "VarChar" + QString::number( i ) + " " ) );
954 else
955 QCOMPARE( q.value( 1 ).toString(), ( "VarChar" + QString::number( i ) ) );
956
957 if ( db.driverName().startsWith( "QIBASE" ) )
958 QVERIFY( q.value( 2 ).toString().startsWith( "Char" + QString::number( i ) ) );
959 else if ( q.value( 2 ).toString().right( 1 ) != " " )
960 QCOMPARE( q.value( 2 ).toString(), ( "Char" + QString::number( i ) ) );
961 else
962 QCOMPARE( q.value( 2 ).toString(), ( "Char" + QString::number( i ) + " " ) );
963
964 i++;
965 }
966 }
967
record()968 void tst_QSqlQuery::record()
969 {
970 QFETCH( QString, dbName );
971 QSqlDatabase db = QSqlDatabase::database( dbName );
972 CHECK_DATABASE( db );
973
974 QSqlQuery q( db );
975 QVERIFY( q.record().isEmpty() );
976 QVERIFY_SQL( q, exec( "select id, t_varchar, t_char from " + qtest + " order by id" ) );
977 QCOMPARE( q.record().fieldName( 0 ).toLower(), QString( "id" ) );
978 QCOMPARE( q.record().fieldName( 1 ).toLower(), QString( "t_varchar" ) );
979 QCOMPARE( q.record().fieldName( 2 ).toLower(), QString( "t_char" ) );
980 QCOMPARE(q.record().value(0), QVariant(q.record().field(0).type()));
981 QCOMPARE(q.record().value(1), QVariant(q.record().field(1).type()));
982 QCOMPARE(q.record().value(2), QVariant(q.record().field(2).type()));
983
984 QVERIFY( q.next() );
985 QVERIFY( q.next() );
986
987 QCOMPARE( q.record().fieldName( 0 ).toLower(), QString( "id" ) );
988 QCOMPARE( q.value( 0 ).toInt(), 2 );
989 }
990
isValid()991 void tst_QSqlQuery::isValid()
992 {
993 QFETCH( QString, dbName );
994 QSqlDatabase db = QSqlDatabase::database( dbName );
995 CHECK_DATABASE( db );
996
997 QSqlQuery q( db );
998 QVERIFY( !q.isValid() );
999 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1000 QVERIFY( q.first() );
1001 QVERIFY( q.isValid() );
1002 }
1003
isActive()1004 void tst_QSqlQuery::isActive()
1005 {
1006 QFETCH( QString, dbName );
1007 QSqlDatabase db = QSqlDatabase::database( dbName );
1008 CHECK_DATABASE( db );
1009
1010 QSqlQuery q( db );
1011 QVERIFY( !q.isActive() );
1012 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1013 QVERIFY( q.isActive() );
1014 QVERIFY( q.last() );
1015
1016 if ( !tst_Databases::isMSAccess( db ) )
1017 // Access is stupid enough to let you scroll over boundaries
1018 QVERIFY( !q.next() );
1019
1020 QVERIFY( q.isActive() );
1021
1022 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (41, 'VarChar41', 'Char41')" ) );
1023
1024 QVERIFY( q.isActive() );
1025
1026 QVERIFY_SQL( q, exec( "update " + qtest + " set id = 42 where id = 41" ) );
1027
1028 QVERIFY( q.isActive() );
1029
1030 QVERIFY_SQL( q, exec( "delete from " + qtest + " where id = 42" ) );
1031
1032 QVERIFY( q.isActive() );
1033
1034 QVERIFY_SQL( q, exec( "delete from " + qtest + " where id = 42" ) );
1035
1036 QVERIFY( q.isActive() );
1037 }
1038
numRowsAffected()1039 void tst_QSqlQuery::numRowsAffected()
1040 {
1041 QFETCH( QString, dbName );
1042 QSqlDatabase db = QSqlDatabase::database( dbName );
1043 CHECK_DATABASE( db );
1044
1045 QSqlQuery q( db );
1046 QCOMPARE( q.numRowsAffected(), -1 );
1047
1048 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1049 int i = 0;
1050
1051 while ( q.next() )
1052 ++i;
1053
1054 if ( q.numRowsAffected() == -1 || q.numRowsAffected() == 0 )
1055 QSKIP("Database doesn't support numRowsAffected", SkipSingle);
1056
1057 if ( q.numRowsAffected() != -1 && q.numRowsAffected() != 0 && q.numRowsAffected() != i ) {
1058 // the value is undefined for SELECT, this check is just here for curiosity
1059 qDebug( "Expected numRowsAffected to be -1, 0 or %d, got %d", i, q.numRowsAffected() );
1060 }
1061
1062 QVERIFY_SQL( q, exec( "update " + qtest + " set id = 100 where id = 1" ) );
1063
1064 QCOMPARE( q.numRowsAffected(), 1 );
1065 QCOMPARE( q.numRowsAffected(), 1 ); // yes, we check twice
1066
1067 QVERIFY_SQL( q, exec( "update " + qtest + " set id = id + 100" ) );
1068 QCOMPARE( q.numRowsAffected(), i );
1069 QCOMPARE( q.numRowsAffected(), i ); // yes, we check twice
1070
1071 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (42000, 'homer', 'marge')" ) );
1072 QCOMPARE( q.numRowsAffected(), 1 );
1073 QCOMPARE( q.numRowsAffected(), 1 ); // yes, we check twice
1074
1075 QSqlQuery q2( db );
1076 QVERIFY_SQL( q2, exec( "insert into " + qtest + " values (42001, 'homer', 'marge')" ) );
1077
1078 if ( !db.driverName().startsWith( "QSQLITE2" ) ) {
1079 // SQLite 2.x accumulates changed rows in nested queries. See task 33794
1080 QCOMPARE( q2.numRowsAffected(), 1 );
1081 QCOMPARE( q2.numRowsAffected(), 1 ); // yes, we check twice
1082 }
1083 }
1084
size()1085 void tst_QSqlQuery::size()
1086 {
1087 QFETCH( QString, dbName );
1088 QSqlDatabase db = QSqlDatabase::database( dbName );
1089 CHECK_DATABASE( db );
1090
1091 QSqlQuery q( db );
1092 QCOMPARE( q.size(), -1 );
1093
1094 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1095 int i = 0;
1096
1097 while ( q.next() )
1098 ++i;
1099
1100 if ( db.driver()->hasFeature( QSqlDriver::QuerySize ) ) {
1101 QCOMPARE( q.size(), i );
1102 QCOMPARE( q.size(), i ); // yes, twice
1103 } else {
1104 QCOMPARE( q.size(), -1 );
1105 QCOMPARE( q.size(), -1 ); // yes, twice
1106 }
1107
1108 QSqlQuery q2( "select * from " + qtest, db );
1109
1110 if ( db.driver()->hasFeature( QSqlDriver::QuerySize ) )
1111 QCOMPARE( q.size(), i );
1112 else
1113 QCOMPARE( q.size(), -1 );
1114
1115 q2.clear();
1116
1117 QVERIFY_SQL( q, exec( "update " + qtest + " set id = 100 where id = 1" ) );
1118 QCOMPARE( q.size(), -1 );
1119 QCOMPARE( q.size(), -1 ); // yes, twice
1120 }
1121
isSelect()1122 void tst_QSqlQuery::isSelect()
1123 {
1124 QFETCH( QString, dbName );
1125 QSqlDatabase db = QSqlDatabase::database( dbName );
1126 CHECK_DATABASE( db );
1127
1128 QSqlQuery q( db );
1129 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1130 QVERIFY( q.isSelect() );
1131
1132 QVERIFY_SQL( q, exec( "update " + qtest + " set id = 1 where id = 1" ) );
1133 QVERIFY( q.isSelect() == false );
1134 }
1135
first()1136 void tst_QSqlQuery::first()
1137 {
1138 QFETCH( QString, dbName );
1139 QSqlDatabase db = QSqlDatabase::database( dbName );
1140 CHECK_DATABASE( db );
1141
1142 QSqlQuery q( db );
1143 QVERIFY( q.at() == QSql::BeforeFirstRow );
1144 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1145 QVERIFY( q.last() );
1146 QVERIFY_SQL( q, first() );
1147 QVERIFY( q.at() == 0 );
1148 }
1149
next()1150 void tst_QSqlQuery::next()
1151 {
1152 QFETCH( QString, dbName );
1153 QSqlDatabase db = QSqlDatabase::database( dbName );
1154 CHECK_DATABASE( db );
1155
1156 QSqlQuery q( db );
1157 QVERIFY( q.at() == QSql::BeforeFirstRow );
1158 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1159 QVERIFY( q.first() );
1160 QVERIFY( q.next() );
1161 QVERIFY( q.at() == 1 );
1162 }
1163
prev()1164 void tst_QSqlQuery::prev()
1165 {
1166 QFETCH( QString, dbName );
1167 QSqlDatabase db = QSqlDatabase::database( dbName );
1168 CHECK_DATABASE( db );
1169
1170 QSqlQuery q( db );
1171 QVERIFY( q.at() == QSql::BeforeFirstRow );
1172 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1173 QVERIFY( q.first() );
1174 QVERIFY( q.next() );
1175 QVERIFY( q.previous() );
1176 QVERIFY( q.at() == 0 );
1177 }
1178
last()1179 void tst_QSqlQuery::last()
1180 {
1181 QFETCH( QString, dbName );
1182 QSqlDatabase db = QSqlDatabase::database( dbName );
1183 CHECK_DATABASE( db );
1184
1185 QSqlQuery q( db );
1186 QCOMPARE( q.at(), int( QSql::BeforeFirstRow ) );
1187 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1188 int i = 0;
1189
1190 while ( q.next() )
1191 i++;
1192
1193 QCOMPARE( q.at(), int( QSql::AfterLastRow ) );
1194
1195 QVERIFY( q.last() );
1196
1197 QSet<int> validReturns(QSet<int>() << -1 << i-1);
1198 QVERIFY( validReturns.contains(q.at()) );
1199
1200 QSqlQuery q2( "select * from " + qtest, db );
1201
1202 QVERIFY( q2.last() );
1203
1204 QVERIFY( validReturns.contains(q.at()) );
1205 }
1206
seek()1207 void tst_QSqlQuery::seek()
1208 {
1209 QFETCH( QString, dbName );
1210 QSqlDatabase db = QSqlDatabase::database( dbName );
1211 CHECK_DATABASE( db );
1212 QSqlQuery q( db );
1213 QVERIFY( q.at() == QSql::BeforeFirstRow );
1214 QVERIFY_SQL( q, exec( QString( "select id from %1 order by id" ).arg( qtest ) ) );
1215
1216 // NB! The order of the calls below are important!
1217 QVERIFY( q.last() );
1218 QVERIFY( !q.seek( QSql::BeforeFirstRow ) );
1219 QCOMPARE( q.at(), int( QSql::BeforeFirstRow ) );
1220 QVERIFY( q.seek( 0 ) );
1221 QCOMPARE( q.at(), 0 );
1222 QCOMPARE( q.value( 0 ).toInt(), 1 );
1223
1224 QVERIFY( q.seek( 1 ) );
1225 QCOMPARE( q.at(), 1 );
1226 QCOMPARE( q.value( 0 ).toInt(), 2 );
1227
1228 QVERIFY( q.seek( 3 ) );
1229 QCOMPARE( q.at(), 3 );
1230 QCOMPARE( q.value( 0 ).toInt(), 4 );
1231
1232 QVERIFY( q.seek( -2, true ) );
1233 QCOMPARE( q.at(), 1 );
1234 QVERIFY( q.seek( 0 ) );
1235 QCOMPARE( q.at(), 0 );
1236 QCOMPARE( q.value( 0 ).toInt(), 1 );
1237 }
1238
seekForwardOnlyQuery()1239 void tst_QSqlQuery::seekForwardOnlyQuery()
1240 {
1241 QFETCH( QString, dbName );
1242 QSqlDatabase db = QSqlDatabase::database( dbName );
1243 CHECK_DATABASE( db );
1244
1245 QSqlQuery q( db );
1246 q.setForwardOnly( false );
1247 QVERIFY( !q.isForwardOnly() );
1248
1249 QVERIFY( q.at() == QSql::BeforeFirstRow );
1250 QVERIFY_SQL( q, exec( QString( "select id from %1 order by id" ).arg( qtest ) ) );
1251
1252 QSqlRecord rec;
1253
1254 // NB! The order of the calls below are important!
1255 QVERIFY( q.seek( 0 ) );
1256 QCOMPARE( q.at(), 0 );
1257 rec = q.record();
1258 QCOMPARE( rec.value( 0 ).toInt(), 1 );
1259
1260 QVERIFY( q.seek( 1 ) );
1261 QCOMPARE( q.at(), 1 );
1262 rec = q.record();
1263 QCOMPARE( rec.value( 0 ).toInt(), 2 );
1264
1265 // Make a jump!
1266 QVERIFY( q.seek( 3 ) );
1267 QCOMPARE( q.at(), 3 );
1268 rec = q.record();
1269 QCOMPARE( rec.value( 0 ).toInt(), 4 );
1270
1271 // Last record in result set
1272 QVERIFY( q.seek( 4 ) );
1273 QCOMPARE( q.at(), 4 );
1274 rec = q.record();
1275 QCOMPARE( rec.value( 0 ).toInt(), 5 );
1276 }
1277
1278 // tests the forward only mode;
forwardOnly()1279 void tst_QSqlQuery::forwardOnly()
1280 {
1281 QFETCH( QString, dbName );
1282 QSqlDatabase db = QSqlDatabase::database( dbName );
1283 CHECK_DATABASE( db );
1284
1285 QSqlQuery q( db );
1286 q.setForwardOnly( true );
1287 QVERIFY( q.isForwardOnly() );
1288 QVERIFY( q.at() == QSql::BeforeFirstRow );
1289 QVERIFY_SQL( q, exec( "select * from " + qtest + " order by id" ) );
1290 QVERIFY( q.at() == QSql::BeforeFirstRow );
1291 QVERIFY( q.first() );
1292 QCOMPARE( q.at(), 0 );
1293 QCOMPARE( q.value( 0 ).toInt(), 1 );
1294 QVERIFY( q.next() );
1295 QCOMPARE( q.at(), 1 );
1296 QCOMPARE( q.value( 0 ).toInt(), 2 );
1297 QVERIFY( q.next() );
1298 QCOMPARE( q.at(), 2 );
1299 QCOMPARE( q.value( 0 ).toInt(), 3 );
1300
1301 // lets make some mistakes to see how robust it is
1302 QTest::ignoreMessage( QtWarningMsg, "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1303 QVERIFY( q.first() == false );
1304 QCOMPARE( q.at(), 2 );
1305 QCOMPARE( q.value( 0 ).toInt(), 3 );
1306 QTest::ignoreMessage( QtWarningMsg, "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1307 QVERIFY( q.previous() == false );
1308 QCOMPARE( q.at(), 2 );
1309 QCOMPARE( q.value( 0 ).toInt(), 3 );
1310 QVERIFY( q.next() );
1311 QCOMPARE( q.at(), 3 );
1312 QCOMPARE( q.value( 0 ).toInt(), 4 );
1313
1314 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1315 int i = 0;
1316
1317 while ( q.next() )
1318 i++;
1319
1320 QVERIFY( q.at() == QSql::AfterLastRow );
1321
1322 QSqlQuery q2 = q;
1323
1324 QVERIFY( q2.isForwardOnly() );
1325
1326 QVERIFY_SQL( q, exec( "select * from " + qtest + " order by id" ) );
1327
1328 QVERIFY( q.isForwardOnly() );
1329
1330 QVERIFY( q2.isForwardOnly() );
1331
1332 QCOMPARE( q.at(), int( QSql::BeforeFirstRow ) );
1333
1334 QVERIFY_SQL( q, seek( 3 ) );
1335
1336 QCOMPARE( q.at(), 3 );
1337
1338 QCOMPARE( q.value( 0 ).toInt(), 4 );
1339
1340 QTest::ignoreMessage( QtWarningMsg, "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1341
1342 QVERIFY( q.seek( 0 ) == false );
1343
1344 QCOMPARE( q.value( 0 ).toInt(), 4 );
1345
1346 QCOMPARE( q.at(), 3 );
1347
1348 QVERIFY( q.last() );
1349
1350 QCOMPARE( q.at(), i-1 );
1351
1352 QTest::ignoreMessage( QtWarningMsg, "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1353
1354 QVERIFY( q.first() == false );
1355
1356 QCOMPARE( q.at(), i-1 );
1357
1358 QVERIFY( q.next() == false );
1359
1360 QCOMPARE( q.at(), int( QSql::AfterLastRow ) );
1361 }
1362
query_exec()1363 void tst_QSqlQuery::query_exec()
1364 {
1365 QFETCH( QString, dbName );
1366 QSqlDatabase db = QSqlDatabase::database( dbName );
1367 CHECK_DATABASE( db );
1368
1369 QSqlQuery q( db );
1370 QVERIFY( !q.isValid() );
1371 QVERIFY( !q.isActive() );
1372 QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1373 QVERIFY( q.isActive() );
1374 QVERIFY( q.next() );
1375 QVERIFY( q.isValid() );
1376 }
1377
isNull()1378 void tst_QSqlQuery::isNull()
1379 {
1380 QFETCH( QString, dbName );
1381 QSqlDatabase db = QSqlDatabase::database( dbName );
1382 CHECK_DATABASE( db );
1383
1384 QSqlQuery q( db );
1385 QVERIFY_SQL( q, exec( "select id, t_varchar from " + qTableName( "qtest_null", __FILE__ ) + " order by id" ) );
1386 QVERIFY( q.next() );
1387 QVERIFY( !q.isNull( 0 ) );
1388 QVERIFY( q.isNull( 1 ) );
1389 QCOMPARE( q.value( 0 ).toInt(), 0 );
1390 QCOMPARE( q.value( 1 ).toString(), QString() );
1391 QVERIFY( !q.value( 0 ).isNull() );
1392 QVERIFY( q.value( 1 ).isNull() );
1393
1394 QVERIFY( q.next() );
1395 QVERIFY( !q.isNull( 0 ) );
1396 QVERIFY( !q.isNull( 1 ) );
1397 }
1398
1399 /*! TDS specific BIT field test */
bitField()1400 void tst_QSqlQuery::bitField()
1401 {
1402 QFETCH( QString, dbName );
1403 QSqlDatabase db = QSqlDatabase::database( dbName );
1404 CHECK_DATABASE( db );
1405
1406 if ( !db.driverName().startsWith( "QTDS" ) )
1407 QSKIP( "TDS specific test", SkipSingle );
1408
1409 QSqlQuery q( db );
1410
1411 QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_bittest", __FILE__ ) + " (bitty bit)" ) );
1412
1413 QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest_bittest", __FILE__ ) + " values (0)" ) );
1414
1415 QVERIFY_SQL( q, exec( "insert into " + qTableName( "qtest_bittest", __FILE__ ) + " values (1)" ) );
1416
1417 QVERIFY_SQL( q, exec( "select bitty from " + qTableName( "qtest_bittest", __FILE__ ) ) );
1418
1419 QVERIFY( q.next() );
1420
1421 QVERIFY( q.value( 0 ).toInt() == 0 );
1422
1423 QVERIFY( q.next() );
1424
1425 QVERIFY( q.value( 0 ).toInt() == 1 );
1426 }
1427
1428
1429 /*! Oracle specific NULL BLOB test */
nullBlob()1430 void tst_QSqlQuery::nullBlob()
1431 {
1432 QFETCH( QString, dbName );
1433 QSqlDatabase db = QSqlDatabase::database( dbName );
1434 CHECK_DATABASE( db );
1435 const QString qtest_nullblob(qTableName("qtest_nullblob", __FILE__));
1436
1437 QSqlQuery q( db );
1438 QVERIFY_SQL( q, exec( "create table " + qtest_nullblob + " (id int primary key, bb blob)" ) );
1439 QVERIFY_SQL( q, exec( "insert into " + qtest_nullblob + " values (0, EMPTY_BLOB())" ) );
1440 QVERIFY_SQL( q, exec( "insert into " + qtest_nullblob + " values (1, NULL)" ) );
1441 QVERIFY_SQL( q, exec( "insert into " + qtest_nullblob + " values (2, 'aabbcc00112233445566')" ) );
1442 // necessary otherwise oracle will bombard you with internal errors
1443 q.setForwardOnly( true );
1444 QVERIFY_SQL( q, exec( "select * from " + qtest_nullblob + " order by id" ) );
1445
1446 QVERIFY( q.next() );
1447 QCOMPARE(( int )q.value( 1 ).toByteArray().size(), 0 );
1448 QVERIFY( !q.isNull( 1 ) );
1449
1450 QVERIFY( q.next() );
1451 QCOMPARE(( int )q.value( 1 ).toByteArray().size(), 0 );
1452 QVERIFY( q.isNull( 1 ) );
1453
1454 QVERIFY( q.next() );
1455 QCOMPARE(( int )q.value( 1 ).toByteArray().size(), 10 );
1456 QVERIFY( !q.isNull( 1 ) );
1457 }
1458
1459 /* Oracle specific RAW field test */
rawField()1460 void tst_QSqlQuery::rawField()
1461 {
1462 QFETCH( QString, dbName );
1463 QSqlDatabase db = QSqlDatabase::database( dbName );
1464 CHECK_DATABASE( db );
1465 const QString qtest_rawtest(qTableName("qtest_rawtest", __FILE__));
1466
1467 QSqlQuery q( db );
1468 q.setForwardOnly( true );
1469 QVERIFY_SQL( q, exec( "create table " + qtest_rawtest +
1470 " (id int, col raw(20))" ) );
1471 QVERIFY_SQL( q, exec( "insert into " + qtest_rawtest + " values (0, NULL)" ) );
1472 QVERIFY_SQL( q, exec( "insert into " + qtest_rawtest + " values (1, '00aa1100ddeeff')" ) );
1473 QVERIFY_SQL( q, exec( "select col from " + qtest_rawtest + " order by id" ) );
1474 QVERIFY( q.next() );
1475 QVERIFY( q.isNull( 0 ) );
1476 QCOMPARE(( int )q.value( 0 ).toByteArray().size(), 0 );
1477 QVERIFY( q.next() );
1478 QVERIFY( !q.isNull( 0 ) );
1479 QCOMPARE(( int )q.value( 0 ).toByteArray().size(), 7 );
1480 }
1481
1482 // test whether we can fetch values with more than DOUBLE precision
1483 // note that MySQL's 3.x highest precision is that of a double, although
1484 // you can define field with higher precision
precision()1485 void tst_QSqlQuery::precision()
1486 {
1487 QFETCH( QString, dbName );
1488 QSqlDatabase db = QSqlDatabase::database( dbName );
1489 CHECK_DATABASE( db );
1490 const QString qtest_precision(qTableName( "qtest_precision", __FILE__ ));
1491
1492 static const char* precStr = "1.2345678901234567891";
1493
1494 if ( db.driverName().startsWith( "QIBASE" ) )
1495 QSKIP( "DB unable to store high precision", SkipSingle );
1496
1497 {
1498 // need a new scope for SQLITE
1499 QSqlQuery q( db );
1500
1501 if ( tst_Databases::isMSAccess( db ) )
1502 QVERIFY_SQL( q, exec( "create table " + qtest_precision + " (col1 number)" ) );
1503 else
1504 QVERIFY_SQL( q, exec( "create table " + qtest_precision + " (col1 numeric(21, 20))" ) );
1505
1506 QVERIFY_SQL( q, exec( "insert into " + qtest_precision + " (col1) values (1.2345678901234567891)" ) );
1507
1508 QVERIFY_SQL( q, exec( "select * from " + qtest_precision ) );
1509 QVERIFY( q.next() );
1510
1511 QString val = q.value( 0 ).toString();
1512
1513 if ( !val.startsWith( "1.2345678901234567891" ) ) {
1514 int i = 0;
1515
1516 while ( precStr[i] != 0 && *( precStr + i ) == val[i].toLatin1() )
1517 i++;
1518
1519 // MySQL and TDS have crappy precisions by default
1520 if ( db.driverName().startsWith( "QMYSQL" ) ) {
1521 if ( i < 17 )
1522 QWARN( "MySQL didn't return the right precision" );
1523 } else if ( db.driverName().startsWith( "QTDS" ) ) {
1524 if ( i < 18 )
1525 QWARN( "TDS didn't return the right precision" );
1526 } else {
1527 QWARN( QString( tst_Databases::dbToString( db ) + " didn't return the right precision (" +
1528 QString::number( i ) + " out of 21), " + val ).toLatin1() );
1529 }
1530 }
1531 } // SQLITE scope
1532 }
1533
nullResult()1534 void tst_QSqlQuery::nullResult()
1535 {
1536 QFETCH( QString, dbName );
1537 QSqlDatabase db = QSqlDatabase::database( dbName );
1538 CHECK_DATABASE( db );
1539
1540 QSqlQuery q( db );
1541 QVERIFY_SQL( q, exec( "select * from " + qtest + " where id > 50000" ) );
1542
1543 if ( q.driver()->hasFeature( QSqlDriver::QuerySize ) )
1544 QCOMPARE( q.size(), 0 );
1545
1546 QVERIFY( q.next() == false );
1547
1548 QVERIFY( q.first() == false );
1549 QVERIFY( q.last() == false );
1550 QVERIFY( q.previous() == false );
1551 QVERIFY( q.seek( 10 ) == false );
1552 QVERIFY( q.seek( 0 ) == false );
1553 }
1554
1555 // this test is just an experiment to see whether we can do query-based transactions
1556 // the real transaction test is in tst_QSqlDatabase
transaction()1557 void tst_QSqlQuery::transaction()
1558 {
1559 // query based transaction is not really possible with Qt
1560 QSKIP( "only tested manually by trained staff", SkipAll );
1561
1562 QFETCH( QString, dbName );
1563 QSqlDatabase db = QSqlDatabase::database( dbName );
1564 CHECK_DATABASE( db );
1565
1566 if ( !db.driver()->hasFeature( QSqlDriver::Transactions ) )
1567 QSKIP( "DBMS not transaction capable", SkipSingle );
1568
1569 // this is the standard SQL
1570 QString startTransactionStr( "start transaction" );
1571
1572 if ( db.driverName().startsWith( "QMYSQL" ) )
1573 startTransactionStr = "begin work";
1574
1575 QSqlQuery q( db );
1576
1577 QSqlQuery q2( db );
1578
1579 // test a working transaction
1580 q.exec( startTransactionStr );
1581
1582 QVERIFY_SQL( q, exec( "insert into" + qtest + " values (40, 'VarChar40', 'Char40')" ) );
1583
1584 QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 40" ) );
1585
1586 QVERIFY( q.next() );
1587
1588 QCOMPARE( q.value( 0 ).toInt(), 40 );
1589
1590 QVERIFY_SQL( q, exec( "commit" ) );
1591
1592 QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 40" ) );
1593
1594 QVERIFY( q.next() );
1595
1596 QCOMPARE( q.value( 0 ).toInt(), 40 );
1597
1598 // test a rollback
1599 q.exec( startTransactionStr );
1600
1601 QVERIFY_SQL( q, exec( "insert into" + qtest + " values (41, 'VarChar41', 'Char41')" ) );
1602
1603 QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 41" ) );
1604
1605 QVERIFY( q.next() );
1606
1607 QCOMPARE( q.value( 0 ).toInt(), 41 );
1608
1609 if ( !q.exec( "rollback" ) ) {
1610 if ( db.driverName().startsWith( "QMYSQL" ) ) {
1611 qDebug( "MySQL: " + tst_Databases::printError( q.lastError() ) );
1612 QSKIP( "MySQL transaction failed ", SkipSingle ); //non-fatal
1613 } else
1614 QFAIL( "Could not rollback transaction: " + tst_Databases::printError( q.lastError() ) );
1615 }
1616
1617 QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 41" ) );
1618
1619 QVERIFY( q.next() == false );
1620
1621 // test concurrent access
1622 q.exec( startTransactionStr );
1623 QVERIFY_SQL( q, exec( "insert into" + qtest + " values (42, 'VarChar42', 'Char42')" ) );
1624 QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 42" ) );
1625 QVERIFY( q.next() );
1626 QCOMPARE( q.value( 0 ).toInt(), 42 );
1627
1628 QVERIFY_SQL( q2, exec( "select * from" + qtest + " where id = 42" ) );
1629
1630 if ( q2.next() )
1631 qDebug( QString( "DBMS '%1' doesn't support query based transactions with concurrent access" ).arg(
1632 tst_Databases::dbToString( db ) ).toLatin1() );
1633
1634 QVERIFY_SQL( q, exec( "commit" ) );
1635
1636 QVERIFY_SQL( q2, exec( "select * from" + qtest + " where id = 42" ) );
1637
1638 QVERIFY( q2.next() );
1639
1640 QCOMPARE( q2.value( 0 ).toInt(), 42 );
1641 }
1642
joins()1643 void tst_QSqlQuery::joins()
1644 {
1645 QFETCH( QString, dbName );
1646 QSqlDatabase db = QSqlDatabase::database( dbName );
1647 CHECK_DATABASE( db );
1648 const QString qtestj1(qTableName("qtestj1", __FILE__)), qtestj2(qTableName("qtestj2", __FILE__));
1649
1650 if ( db.driverName().startsWith( "QOCI" )
1651 || db.driverName().startsWith( "QTDS" )
1652 || db.driverName().startsWith( "QODBC" )
1653 || db.driverName().startsWith( "QIBASE" ) ) {
1654 // Oracle broken beyond recognition - cannot outer join on more than
1655 // one table.
1656 QSKIP( "DBMS cannot understand standard SQL", SkipSingle );
1657 return;
1658 }
1659
1660 QSqlQuery q( db );
1661
1662 QVERIFY_SQL( q, exec( "create table " + qtestj1 + " (id1 int, id2 int)" ) );
1663 QVERIFY_SQL( q, exec( "create table " + qtestj2 + " (id int, name varchar(20))" ) );
1664 QVERIFY_SQL( q, exec( "insert into " + qtestj1 + " values (1, 1)" ) );
1665 QVERIFY_SQL( q, exec( "insert into " + qtestj1 + " values (1, 2)" ) );
1666 QVERIFY_SQL( q, exec( "insert into " + qtestj2 + " values(1, 'trenton')" ) );
1667 QVERIFY_SQL( q, exec( "insert into " + qtestj2 + " values(2, 'marius')" ) );
1668
1669 QVERIFY_SQL( q, exec( "select qtestj1.id1, qtestj1.id2, qtestj2.id, qtestj2.name, qtestj3.id, qtestj3.name "
1670 "from " + qtestj1 + " qtestj1 left outer join " + qtestj2 +
1671 " qtestj2 on (qtestj1.id1 = qtestj2.id) "
1672 "left outer join " + qtestj2 + " as qtestj3 on (qtestj1.id2 = qtestj3.id)" ) );
1673
1674 QVERIFY( q.next() );
1675 QCOMPARE( q.value( 0 ).toInt(), 1 );
1676 QCOMPARE( q.value( 1 ).toInt(), 1 );
1677 QCOMPARE( q.value( 2 ).toInt(), 1 );
1678 QCOMPARE( q.value( 3 ).toString(), QString( "trenton" ) );
1679 QCOMPARE( q.value( 4 ).toInt(), 1 );
1680 QCOMPARE( q.value( 5 ).toString(), QString( "trenton" ) );
1681
1682 QVERIFY( q.next() );
1683 QCOMPARE( q.value( 0 ).toInt(), 1 );
1684 QCOMPARE( q.value( 1 ).toInt(), 2 );
1685 QCOMPARE( q.value( 2 ).toInt(), 1 );
1686 QCOMPARE( q.value( 3 ).toString(), QString( "trenton" ) );
1687 QCOMPARE( q.value( 4 ).toInt(), 2 );
1688 QCOMPARE( q.value( 5 ).toString(), QString( "marius" ) );
1689 }
1690
synonyms()1691 void tst_QSqlQuery::synonyms()
1692 {
1693 QFETCH( QString, dbName );
1694 QSqlDatabase db = QSqlDatabase::database( dbName );
1695 CHECK_DATABASE( db );
1696
1697 QSqlQuery q(db);
1698 QVERIFY_SQL( q, exec("select a.id, a.t_char, a.t_varchar from " + qtest + " a where a.id = 1") );
1699 QVERIFY( q.next() );
1700 QCOMPARE( q.value( 0 ).toInt(), 1 );
1701 QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "Char1" ) );
1702 QCOMPARE( q.value( 2 ).toString().trimmed(), QString( "VarChar1" ) );
1703
1704 QSqlRecord rec = q.record();
1705 QCOMPARE(( int )rec.count(), 3 );
1706 QCOMPARE( rec.field( 0 ).name().toLower(), QString( "id" ) );
1707 QCOMPARE( rec.field( 1 ).name().toLower(), QString( "t_char" ) );
1708 QCOMPARE( rec.field( 2 ).name().toLower(), QString( "t_varchar" ) );
1709 }
1710
1711 // It doesn't make sense to split this into several tests
prepare_bind_exec()1712 void tst_QSqlQuery::prepare_bind_exec()
1713 {
1714 QFETCH( QString, dbName );
1715 QSqlDatabase db = QSqlDatabase::database( dbName );
1716 CHECK_DATABASE( db );
1717 const QString qtest_prepare(qTableName("qtest_prepare", __FILE__));
1718
1719 if(db.driverName().startsWith("QIBASE") && (db.databaseName() == "silence.nokia.troll.no:c:\\ibase\\testdb_ascii" || db.databaseName() == "/opt/interbase/qttest.gdb"))
1720 QSKIP("Can't transliterate extended unicode to ascii", SkipSingle);
1721 if(db.driverName().startsWith("QDB2"))
1722 QSKIP("Needs someone with more Unicode knowledge than I have to fix", SkipSingle);
1723
1724 {
1725 // new scope for SQLITE
1726 static const QString utf8str = QString::fromUtf8( "काचं शक्नोम्यत्तुम् । नोपहिनस्ति माम् ॥" );
1727
1728 static const QString values[6] = { "Harry", "Trond", "Mark", "Ma?rk", "?", ":id" };
1729
1730 bool useUnicode = db.driver()->hasFeature( QSqlDriver::Unicode );
1731
1732 QSqlQuery q( db );
1733
1734 if ( db.driverName().startsWith( "QMYSQL" ) && tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 )
1735 useUnicode = false;
1736
1737 QString createQuery;
1738
1739 if(tst_Databases::isPostgreSQL(db))
1740 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
1741
1742 if ( tst_Databases::isSqlServer( db ) || db.driverName().startsWith( "QTDS" ) )
1743 createQuery = "create table " + qtest_prepare + " (id int primary key, name nvarchar(200) null)";
1744 else if ( tst_Databases::isMySQL(db) && useUnicode )
1745 createQuery = "create table " + qtest_prepare + " (id int not null primary key, name varchar(200) character set utf8)";
1746 else
1747 createQuery = "create table " + qtest_prepare + " (id int not null primary key, name varchar(200))";
1748
1749 QVERIFY_SQL( q, exec( createQuery ) );
1750
1751 QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (:id, :name)" ) );
1752 int i;
1753
1754 for ( i = 0; i < 6; ++i ) {
1755 q.bindValue( ":name", values[i] );
1756 q.bindValue( ":id", i );
1757 QVERIFY_SQL( q, exec() );
1758 QMap<QString, QVariant> m = q.boundValues();
1759 QCOMPARE(( int ) m.count(), 2 );
1760 QCOMPARE( m[":name"].toString(), values[i] );
1761 QCOMPARE( m[":id"].toInt(), i );
1762 }
1763
1764 q.bindValue( ":id", 8 );
1765
1766 QVERIFY_SQL( q, exec() );
1767
1768 if ( useUnicode ) {
1769 q.bindValue( ":id", 7 );
1770 q.bindValue( ":name", utf8str );
1771 QVERIFY_SQL( q, exec() );
1772 }
1773
1774 QVERIFY_SQL( q, exec( "SELECT * FROM " + qtest_prepare + " order by id" ) );
1775
1776 for ( i = 0; i < 6; ++i ) {
1777 QVERIFY( q.next() );
1778 QCOMPARE( q.value( 0 ).toInt(), i );
1779 QCOMPARE( q.value( 1 ).toString().trimmed(), values[ i ] );
1780 }
1781
1782 if ( useUnicode ) {
1783 QVERIFY_SQL( q, next() );
1784 QCOMPARE( q.value( 0 ).toInt(), 7 );
1785 QCOMPARE( q.value( 1 ).toString(), utf8str );
1786 }
1787
1788 QVERIFY_SQL( q, next() );
1789
1790 QCOMPARE( q.value( 0 ).toInt(), 8 );
1791 QCOMPARE( q.value( 1 ).toString(), values[5] );
1792
1793 QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (:id, 'Bart')" ) );
1794 q.bindValue( ":id", 99 );
1795 QVERIFY_SQL( q, exec() );
1796 q.bindValue( ":id", 100 );
1797 QVERIFY_SQL( q, exec() );
1798 QVERIFY( q.exec( "select * from " + qtest_prepare + " where id > 98 order by id" ) );
1799
1800 for ( i = 99; i <= 100; ++i ) {
1801 QVERIFY( q.next() );
1802 QCOMPARE( q.value( 0 ).toInt(), i );
1803 QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "Bart" ) );
1804 }
1805
1806 /*** SELECT stuff ***/
1807 QVERIFY( q.prepare( "select * from " + qtest_prepare + " where id = :id" ) );
1808
1809 for ( i = 0; i < 6; ++i ) {
1810 q.bindValue( ":id", i );
1811 QVERIFY_SQL( q, exec() );
1812 QVERIFY_SQL( q, next() );
1813 QCOMPARE( q.value( 0 ).toInt(), i );
1814 QCOMPARE( q.value( 1 ).toString().trimmed(), values[ i ] );
1815 QSqlRecord rInf = q.record();
1816 QCOMPARE(( int )rInf.count(), 2 );
1817 QCOMPARE( rInf.field( 0 ).name().toUpper(), QString( "ID" ) );
1818 QCOMPARE( rInf.field( 1 ).name().toUpper(), QString( "NAME" ) );
1819 QVERIFY( !q.next() );
1820 }
1821
1822 QVERIFY_SQL( q, exec( "DELETE FROM " + qtest_prepare ) );
1823
1824 QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (?, ?)" ) );
1825 q.bindValue( 0, 0 );
1826 q.bindValue( 1, values[ 0 ] );
1827 QVERIFY_SQL( q, exec() );
1828 q.addBindValue( 1 );
1829 q.addBindValue( values[ 1 ] );
1830 QVERIFY_SQL( q, exec() );
1831 q.addBindValue( 2 );
1832 q.addBindValue( values[ 2 ] );
1833 QVERIFY_SQL( q, exec() );
1834 q.addBindValue( 3 );
1835 q.addBindValue( values[ 3 ] );
1836 QVERIFY_SQL( q, exec() );
1837 q.addBindValue( 4 );
1838 q.addBindValue( values[ 4 ] );
1839 QVERIFY_SQL( q, exec() );
1840 q.bindValue( 1, values[ 5 ] );
1841 q.bindValue( 0, 5 );
1842 QVERIFY_SQL( q, exec() );
1843 q.bindValue( 0, 6 );
1844 q.bindValue( 1, QString() );
1845 QVERIFY_SQL( q, exec() );
1846
1847 if ( db.driver()->hasFeature( QSqlDriver::Unicode ) ) {
1848 q.bindValue( 0, 7 );
1849 q.bindValue( 1, utf8str );
1850 QVERIFY_SQL( q, exec() );
1851 }
1852
1853 QVERIFY_SQL( q, exec( "SELECT * FROM " + qtest_prepare + " order by id" ) );
1854
1855 for ( i = 0; i < 6; ++i ) {
1856 QVERIFY( q.next() );
1857 QCOMPARE( q.value( 0 ).toInt(), i );
1858 QCOMPARE( q.value( 1 ).toString().trimmed(), values[ i ] );
1859 }
1860
1861 QVERIFY( q.next() );
1862
1863 QCOMPARE( q.value( 0 ).toInt(), 6 );
1864 QVERIFY( q.isNull( 1 ) );
1865
1866 if ( useUnicode ) {
1867 QVERIFY( q.next() );
1868 QCOMPARE( q.value( 0 ).toInt(), 7 );
1869 QCOMPARE( q.value( 1 ).toString(), utf8str );
1870 }
1871
1872 QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (?, 'Bart')" ) );
1873
1874 q.bindValue( 0, 99 );
1875 QVERIFY_SQL( q, exec() );
1876 q.addBindValue( 100 );
1877 QVERIFY_SQL( q, exec() );
1878 QVERIFY( q.exec( "select * from " + qtest_prepare + " where id > 98 order by id" ) );
1879
1880 for ( i = 99; i <= 100; ++i ) {
1881 QVERIFY( q.next() );
1882 QCOMPARE( q.value( 0 ).toInt(), i );
1883 QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "Bart" ) );
1884 }
1885
1886 /* insert a duplicate id and make sure the db bails out */
1887 QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (?, ?)" ) );
1888
1889 q.addBindValue( 99 );
1890
1891 q.addBindValue( "something silly" );
1892
1893 QVERIFY( !q.exec() );
1894
1895 QVERIFY( q.lastError().isValid() );
1896
1897 QVERIFY( !q.isActive() );
1898
1899 } // end of SQLite scope
1900 }
1901
prepared_select()1902 void tst_QSqlQuery::prepared_select()
1903 {
1904 QFETCH( QString, dbName );
1905 QSqlDatabase db = QSqlDatabase::database( dbName );
1906 CHECK_DATABASE( db );
1907
1908 QSqlQuery q( db );
1909 QVERIFY_SQL( q, prepare( "select a.id, a.t_char, a.t_varchar from " + qtest + " a where a.id = ?" ) );
1910
1911 q.bindValue( 0, 1 );
1912 QVERIFY_SQL( q, exec() );
1913 QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
1914 QVERIFY( q.next() );
1915 QCOMPARE( q.value( 0 ).toInt(), 1 );
1916
1917 q.bindValue( 0, 2 );
1918 QVERIFY_SQL( q, exec() );
1919 QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
1920 QVERIFY( q.next() );
1921 QCOMPARE( q.value( 0 ).toInt(), 2 );
1922
1923 q.bindValue( 0, 3 );
1924 QVERIFY_SQL( q, exec() );
1925 QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
1926 QVERIFY( q.next() );
1927 QCOMPARE( q.value( 0 ).toInt(), 3 );
1928
1929 QVERIFY_SQL( q, prepare( "select a.id, a.t_char, a.t_varchar from " + qtest + " a where a.id = ?" ) );
1930 QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
1931 QVERIFY( !q.first() );
1932 }
1933
sqlServerLongStrings()1934 void tst_QSqlQuery::sqlServerLongStrings()
1935 {
1936 QFETCH( QString, dbName );
1937 QSqlDatabase db = QSqlDatabase::database( dbName );
1938 CHECK_DATABASE( db );
1939
1940 if ( !tst_Databases::isSqlServer( db ) )
1941 QSKIP( "SQL Server specific test", SkipSingle );
1942
1943 QSqlQuery q( db );
1944
1945 QVERIFY_SQL( q, exec( "CREATE TABLE " + qTableName( "qtest_longstr", __FILE__ ) + " (id int primary key, longstring ntext)" ) );
1946
1947 QVERIFY_SQL( q, prepare( "INSERT INTO " + qTableName( "qtest_longstr", __FILE__ ) + " VALUES (?, ?)" ) );
1948
1949 q.addBindValue( 0 );
1950
1951 q.addBindValue( QString::fromLatin1( "bubu" ) );
1952
1953 QVERIFY_SQL( q, exec() );
1954
1955 QString testStr;
1956
1957 testStr.fill( QLatin1Char( 'a' ), 85000 );
1958
1959 q.addBindValue( 1 );
1960
1961 q.addBindValue( testStr );
1962
1963 QVERIFY_SQL( q, exec() );
1964
1965 QVERIFY_SQL( q, exec( "select * from " + qTableName( "qtest_longstr", __FILE__ ) ) );
1966
1967 QVERIFY_SQL( q, next() );
1968
1969 QCOMPARE( q.value( 0 ).toInt(), 0 );
1970
1971 QCOMPARE( q.value( 1 ).toString(), QString::fromLatin1( "bubu" ) );
1972
1973 QVERIFY_SQL( q, next() );
1974
1975 QCOMPARE( q.value( 0 ).toInt(), 1 );
1976
1977 QCOMPARE( q.value( 1 ).toString(), testStr );
1978 }
1979
invalidQuery()1980 void tst_QSqlQuery::invalidQuery()
1981 {
1982 QFETCH( QString, dbName );
1983 QSqlDatabase db = QSqlDatabase::database( dbName );
1984 CHECK_DATABASE( db );
1985
1986 QSqlQuery q( db );
1987
1988 QVERIFY( !q.exec() );
1989
1990 QVERIFY( !q.exec( "blahfasel" ) );
1991 QVERIFY( q.lastError().type() != QSqlError::NoError );
1992 QVERIFY( !q.next() );
1993 QVERIFY( !q.isActive() );
1994
1995 if ( !db.driverName().startsWith( "QOCI" ) && !db.driverName().startsWith( "QDB2" ) && !db.driverName().startsWith( "QODBC" ) ) {
1996 // oracle and db2 just prepares everything without complaining
1997 if ( db.driver()->hasFeature( QSqlDriver::PreparedQueries ) )
1998 QVERIFY( !q.prepare( "blahfasel" ) );
1999 }
2000
2001 QVERIFY( !q.exec() );
2002
2003 QVERIFY( !q.isActive() );
2004 QVERIFY( !q.next() );
2005 }
2006
2007 class ResultHelper: public QSqlResult
2008 {
2009
2010 public:
ResultHelper()2011 ResultHelper(): QSqlResult( 0 ) {} // don't call, it's only for stupid compilers
2012
execBatch(bool bindArray=false)2013 bool execBatch( bool bindArray = false )
2014 {
2015 return QSqlResult::execBatch( bindArray );
2016 }
2017 };
2018
batchExec()2019 void tst_QSqlQuery::batchExec()
2020 {
2021 QFETCH( QString, dbName );
2022 QSqlDatabase db = QSqlDatabase::database( dbName );
2023 CHECK_DATABASE( db );
2024
2025 if ( !db.driver()->hasFeature( QSqlDriver::BatchOperations ) )
2026 QSKIP( "Database can't do BatchOperations", SkipSingle );
2027
2028 QSqlQuery q( db );
2029 const QString tableName = qTableName( "qtest_batch", __FILE__ );
2030
2031 QVERIFY_SQL( q, exec( "create table " + tableName + " (id int, name varchar(20), dt date, num numeric(8, 4))" ) );
2032 QVERIFY_SQL( q, prepare( "insert into " + tableName + " (id, name, dt, num) values (?, ?, ?, ?)" ) );
2033
2034 QVariantList intCol;
2035 intCol << 1 << 2 << QVariant( QVariant::Int );
2036
2037 QVariantList charCol;
2038 charCol << QLatin1String( "harald" ) << QLatin1String( "boris" ) << QVariant( QVariant::String );
2039
2040 QVariantList dateCol;
2041 QDateTime dt = QDateTime( QDate::currentDate(), QTime( 1, 2, 3 ) );
2042 dateCol << dt << dt.addDays( -1 ) << QVariant( QVariant::DateTime );
2043
2044 QVariantList numCol;
2045 numCol << 2.3 << 3.4 << QVariant( QVariant::Double );
2046
2047 q.addBindValue( intCol );
2048 q.addBindValue( charCol );
2049 q.addBindValue( dateCol );
2050 q.addBindValue( numCol );
2051
2052 QVERIFY_SQL( q, execBatch() );
2053 QVERIFY_SQL( q, exec( "select id, name, dt, num from " + tableName + " order by id" ) );
2054
2055 QVERIFY( q.next() );
2056 QCOMPARE( q.value( 0 ).toInt(), 1 );
2057 QCOMPARE( q.value( 1 ).toString(), QString( "harald" ) );
2058 QCOMPARE( q.value( 2 ).toDateTime(), dt );
2059 QCOMPARE( q.value( 3 ).toDouble(), 2.3 );
2060
2061 QVERIFY( q.next() );
2062 QCOMPARE( q.value( 0 ).toInt(), 2 );
2063 QCOMPARE( q.value( 1 ).toString(), QString( "boris" ) );
2064 QCOMPARE( q.value( 2 ).toDateTime(), dt.addDays( -1 ) );
2065 QCOMPARE( q.value( 3 ).toDouble(), 3.4 );
2066
2067 QVERIFY( q.next() );
2068 QVERIFY( q.value( 0 ).isNull() );
2069 QVERIFY( q.value( 1 ).isNull() );
2070 QVERIFY( q.value( 2 ).isNull() );
2071 QVERIFY( q.value( 3 ).isNull() );
2072 }
2073
oraArrayBind()2074 void tst_QSqlQuery::oraArrayBind()
2075 {
2076 QFETCH( QString, dbName );
2077 QSqlDatabase db = QSqlDatabase::database( dbName );
2078 CHECK_DATABASE( db );
2079
2080 if ( !db.driver()->hasFeature( QSqlDriver::BatchOperations ) )
2081 QSKIP( "Database can't do BatchOperations", SkipSingle );
2082
2083 QSqlQuery q( db );
2084
2085 QVERIFY_SQL( q, exec( "CREATE OR REPLACE PACKAGE ora_array_test "
2086 "IS "
2087 "TYPE names_type IS TABLE OF VARCHAR(64) NOT NULL INDEX BY BINARY_INTEGER; "
2088 "names_tab names_type; "
2089 "PROCEDURE set_name(name_in IN VARCHAR2, row_in in INTEGER); "
2090 "PROCEDURE get_name(row_in IN INTEGER, str_out OUT VARCHAR2); "
2091 "PROCEDURE get_table(tbl OUT names_type); "
2092 "PROCEDURE set_table(tbl IN names_type); "
2093 "END ora_array_test; " ) );
2094
2095 QVERIFY_SQL( q, exec( "CREATE OR REPLACE PACKAGE BODY ora_array_test "
2096 "IS "
2097 "PROCEDURE set_name(name_in IN VARCHAR2, row_in in INTEGER) "
2098 "IS "
2099 "BEGIN "
2100 "names_tab(row_in) := name_in; "
2101 "END set_name; "
2102
2103 "PROCEDURE get_name(row_in IN INTEGER, str_out OUT VARCHAR2) "
2104 "IS "
2105 "BEGIN "
2106 "str_out := names_tab(row_in); "
2107 "END get_name; "
2108
2109 "PROCEDURE get_table(tbl OUT names_type) "
2110 "IS "
2111 "BEGIN "
2112 "tbl:=names_tab; "
2113 "END get_table; "
2114
2115 "PROCEDURE set_table(tbl IN names_type) "
2116 "IS "
2117 "BEGIN "
2118 "names_tab := tbl; "
2119 "END set_table; "
2120 "END ora_array_test; " ) );
2121
2122 QVariantList list;
2123
2124 list << QString( "lorem" ) << QString( "ipsum" ) << QString( "dolor" ) << QString( "sit" ) << QString( "amet" );
2125
2126 QVERIFY_SQL( q, prepare( "BEGIN "
2127 "ora_array_test.set_table(?); "
2128 "END;" ) );
2129
2130 q.bindValue( 0, list, QSql::In );
2131
2132 QVERIFY_SQL( q, execBatch( QSqlQuery::ValuesAsColumns ) );
2133
2134 QVERIFY_SQL( q, prepare( "BEGIN "
2135 "ora_array_test.get_table(?); "
2136 "END;" ) );
2137
2138 list.clear();
2139
2140 list << QString( 64,' ' ) << QString( 64,' ' ) << QString( 64,' ' ) << QString( 64,' ' ) << QString( 64,' ' );
2141
2142 q.bindValue( 0, list, QSql::Out );
2143
2144 QVERIFY_SQL( q, execBatch( QSqlQuery::ValuesAsColumns ) );
2145
2146 QVariantList out_list = q.boundValue( 0 ).toList();
2147
2148 QCOMPARE( out_list.at( 0 ).toString(), QString( "lorem" ) );
2149
2150 QCOMPARE( out_list.at( 1 ).toString(), QString( "ipsum" ) );
2151
2152 QCOMPARE( out_list.at( 2 ).toString(), QString( "dolor" ) );
2153
2154 QCOMPARE( out_list.at( 3 ).toString(), QString( "sit" ) );
2155
2156 QCOMPARE( out_list.at( 4 ).toString(), QString( "amet" ) );
2157
2158 QVERIFY_SQL( q, exec( "DROP PACKAGE ora_array_test" ) );
2159 }
2160
2161 /*
2162 Tests that QSqlDatabase::record and QSqlQuery::record returns the same thing
2163 otherwise our models get confused.
2164 */
record_sqlite()2165 void tst_QSqlQuery::record_sqlite()
2166 {
2167 QFETCH( QString, dbName );
2168 QSqlDatabase db = QSqlDatabase::database( dbName );
2169 CHECK_DATABASE( db );
2170
2171 QSqlQuery q( db );
2172
2173 QVERIFY_SQL( q, exec( "create table "+qTableName( "record_sqlite", __FILE__ )+"(id integer primary key, name varchar, title int)" ) );
2174
2175 QSqlRecord rec = db.record( qTableName( "record_sqlite", __FILE__ ) );
2176
2177 QCOMPARE( rec.count(), 3 );
2178 QCOMPARE( rec.field( 0 ).type(), QVariant::Int );
2179 QCOMPARE( rec.field( 1 ).type(), QVariant::String );
2180 QCOMPARE( rec.field( 2 ).type(), QVariant::Int );
2181
2182 /* important - select from an empty table */
2183 QVERIFY_SQL( q, exec( "select id, name, title from "+qTableName( "record_sqlite", __FILE__ ) ) );
2184
2185 rec = q.record();
2186 QCOMPARE( rec.count(), 3 );
2187 QCOMPARE( rec.field( 0 ).type(), QVariant::Int );
2188 QCOMPARE( rec.field( 1 ).type(), QVariant::String );
2189 QCOMPARE( rec.field( 2 ).type(), QVariant::Int );
2190 }
2191
oraLong()2192 void tst_QSqlQuery::oraLong()
2193 {
2194 QFETCH( QString, dbName );
2195 QSqlDatabase db = QSqlDatabase::database( dbName );
2196 CHECK_DATABASE( db );
2197
2198 QSqlQuery q( db );
2199
2200 QString aLotOfText( 127000, QLatin1Char( 'H' ) );
2201
2202 QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_longstr", __FILE__ ) + " (id int primary key, astr long)" ) );
2203 QVERIFY_SQL( q, prepare( "insert into " + qTableName( "qtest_longstr", __FILE__ ) + " (id, astr) values (?, ?)" ) );
2204 q.addBindValue( 1 );
2205 q.addBindValue( aLotOfText );
2206 QVERIFY_SQL( q, exec() );
2207
2208 QVERIFY_SQL( q, exec( "select id,astr from " + qTableName( "qtest_longstr", __FILE__ ) ) );
2209
2210 QVERIFY( q.next() );
2211 QCOMPARE( q.value( 0 ).toInt(), 1 );
2212 QCOMPARE( q.value( 1 ).toString(), aLotOfText );
2213 }
2214
execErrorRecovery()2215 void tst_QSqlQuery::execErrorRecovery()
2216 {
2217 QFETCH( QString, dbName );
2218 QSqlDatabase db = QSqlDatabase::database( dbName );
2219 CHECK_DATABASE( db );
2220
2221 QSqlQuery q( db );
2222
2223 QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_exerr", __FILE__ ) + " (id int not null primary key)" ) );
2224 QVERIFY_SQL( q, prepare( "insert into " + qTableName( "qtest_exerr", __FILE__ ) + " values (?)" ) );
2225
2226 q.addBindValue( 1 );
2227 QVERIFY_SQL( q, exec() );
2228
2229 q.addBindValue( 1 ); // binding the same pkey - should fail
2230 QVERIFY( !q.exec() );
2231
2232 q.addBindValue( 2 ); // this should work again
2233 QVERIFY_SQL( q, exec() );
2234 }
2235
lastInsertId()2236 void tst_QSqlQuery::lastInsertId()
2237 {
2238 QFETCH( QString, dbName );
2239 QSqlDatabase db = QSqlDatabase::database( dbName );
2240 CHECK_DATABASE( db );
2241
2242 if ( !db.driver()->hasFeature( QSqlDriver::LastInsertId ) )
2243 QSKIP( "Database doesn't support lastInsertId", SkipSingle );
2244
2245 QSqlQuery q( db );
2246
2247 QVERIFY_SQL( q, exec( "insert into " + qtest + " values (41, 'VarChar41', 'Char41')" ) );
2248
2249 QVariant v = q.lastInsertId();
2250
2251 QVERIFY( v.isValid() );
2252 }
2253
lastQuery()2254 void tst_QSqlQuery::lastQuery()
2255 {
2256 QFETCH( QString, dbName );
2257 QSqlDatabase db = QSqlDatabase::database( dbName );
2258 CHECK_DATABASE( db );
2259
2260 QSqlQuery q( db );
2261 QString sql = "select * from " + qtest;
2262 QVERIFY_SQL( q, exec( sql ) );
2263 QCOMPARE( q.lastQuery(), sql );
2264 QCOMPARE( q.executedQuery(), sql );
2265 }
2266
bindWithDoubleColonCastOperator()2267 void tst_QSqlQuery::bindWithDoubleColonCastOperator()
2268 {
2269 QFETCH( QString, dbName );
2270 QSqlDatabase db = QSqlDatabase::database( dbName );
2271 CHECK_DATABASE( db );
2272
2273 // Only PostgreSQL support the double-colon cast operator
2274
2275 if ( !db.driverName().startsWith( "QPSQL" ) ) {
2276 QSKIP( "Test requires PostgreSQL", SkipSingle );
2277 return;
2278 }
2279
2280 const QString tablename(qTableName( "bindtest", __FILE__ ));
2281
2282 QSqlQuery q( db );
2283
2284 QVERIFY_SQL( q, exec( "create table " + tablename + " (id1 int, id2 int, id3 int, fld1 int, fld2 int)" ) );
2285 QVERIFY_SQL( q, exec( "insert into " + tablename + " values (1, 2, 3, 10, 5)" ) );
2286
2287 QVERIFY_SQL( q, prepare( "select sum((fld1 - fld2)::int) from " + tablename + " where id1 = :myid1 and id2 =:myid2 and id3=:myid3" ) );
2288 q.bindValue( ":myid1", 1 );
2289 q.bindValue( ":myid2", 2 );
2290 q.bindValue( ":myid3", 3 );
2291
2292 QVERIFY_SQL( q, exec() );
2293 QVERIFY_SQL( q, next() );
2294
2295 if ( db.driver()->hasFeature( QSqlDriver::PreparedQueries ) )
2296 QCOMPARE( q.executedQuery(), QString( "select sum((fld1 - fld2)::int) from " + tablename + " where id1 = ? and id2 =? and id3=?" ) );
2297 else
2298 QCOMPARE( q.executedQuery(), QString( "select sum((fld1 - fld2)::int) from " + tablename + " where id1 = 1 and id2 =2 and id3=3" ) );
2299 }
2300
2301 /* For task 157397: Using QSqlQuery with an invalid QSqlDatabase
2302 does not set the last error of the query.
2303 This test function will output some warnings, that's ok.
2304 */
queryOnInvalidDatabase()2305 void tst_QSqlQuery::queryOnInvalidDatabase()
2306 {
2307 {
2308 QTest::ignoreMessage( QtWarningMsg, "QSqlDatabase: INVALID driver not loaded" );
2309 QSqlDatabase db = QSqlDatabase::addDatabase( "INVALID", "invalidConnection" );
2310 QVERIFY2( db.lastError().isValid(),
2311 qPrintable( QString( "db.lastError().isValid() should be true!" ) ) );
2312
2313 QTest::ignoreMessage( QtWarningMsg, "QSqlQuery::exec: database not open" );
2314 QSqlQuery query( "SELECT 1 AS ID", db );
2315 QVERIFY2( query.lastError().isValid(),
2316 qPrintable( QString( "query.lastError().isValid() should be true!" ) ) );
2317 }
2318
2319 QSqlDatabase::removeDatabase( "invalidConnection" );
2320
2321 {
2322 QSqlDatabase db = QSqlDatabase::database( "this connection does not exist" );
2323 QTest::ignoreMessage( QtWarningMsg, "QSqlQuery::exec: database not open" );
2324 QSqlQuery query( "SELECT 1 AS ID", db );
2325 QVERIFY2( query.lastError().isValid(),
2326 qPrintable( QString( "query.lastError().isValid() should be true!" ) ) );
2327 }
2328 }
2329
2330 /* For task 159138: Error on instantiating a sql-query before explicitly
2331 opening the database. This is something we don't support, so this isn't
2332 really a bug. However some of the drivers are nice enough to support it.
2333 */
createQueryOnClosedDatabase()2334 void tst_QSqlQuery::createQueryOnClosedDatabase()
2335 {
2336 QFETCH( QString, dbName );
2337 QSqlDatabase db = QSqlDatabase::database( dbName );
2338 CHECK_DATABASE( db );
2339
2340 // Only supported by these drivers
2341
2342 if ( !db.driverName().startsWith( "QPSQL" )
2343 && !db.driverName().startsWith( "QOCI" )
2344 && !db.driverName().startsWith( "QMYSQL" )
2345 && !db.driverName().startsWith( "QDB2" ) ) {
2346 QSKIP( "Test is specific for PostgreSQL, Oracle, MySql and DB2", SkipSingle );
2347 return;
2348 }
2349
2350 db.close();
2351
2352 QSqlQuery q( db );
2353 db.open();
2354 QVERIFY_SQL( q, exec( QString( "select * from %1 where id = 1" ).arg( qtest ) ) );
2355
2356 QVERIFY_SQL( q, next() );
2357 QCOMPARE( q.value( 0 ).toInt(), 1 );
2358 QCOMPARE( q.value( 1 ).toString().trimmed(), QLatin1String( "VarChar1" ) );
2359 QCOMPARE( q.value( 2 ).toString().trimmed(), QLatin1String( "Char1" ) );
2360
2361 db.close();
2362 QVERIFY2( !q.exec( QString( "select * from %1 where id = 1" ).arg( qtest ) ),
2363 qPrintable( QString( "This can't happen! The query should not have been executed!" ) ) );
2364 }
2365
reExecutePreparedForwardOnlyQuery()2366 void tst_QSqlQuery::reExecutePreparedForwardOnlyQuery()
2367 {
2368 QFETCH( QString, dbName );
2369 QSqlDatabase db = QSqlDatabase::database( dbName );
2370 CHECK_DATABASE( db );
2371
2372 QSqlQuery q( db );
2373 q.setForwardOnly( true );
2374
2375 QVERIFY_SQL( q, prepare( QString( "SELECT id, t_varchar, t_char FROM %1 WHERE id = :id" ).arg( qtest ) ) );
2376 q.bindValue( ":id", 1 );
2377 QVERIFY_SQL( q, exec() );
2378
2379 // Do something, like iterate over the result, or skip to the end
2380 QVERIFY_SQL( q, last() );
2381
2382 QVERIFY_SQL( q, exec() );
2383 /* This was broken with SQLite because the cache size was set to 0 in the 2nd execute.
2384 When forwardOnly is set we don't cahce the entire result, but we do cache the current row
2385 but this requires the cache size to be equal to the column count.
2386 */
2387 QVERIFY_SQL( q, next() );
2388 QCOMPARE( q.value( 0 ).toInt(), 1 );
2389 QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "VarChar1" ) );
2390 QCOMPARE( q.value( 2 ).toString().trimmed(), QString( "Char1" ) );
2391 }
2392
finish()2393 void tst_QSqlQuery::finish()
2394 {
2395 QFETCH( QString, dbName );
2396 QSqlDatabase db = QSqlDatabase::database( dbName );
2397 CHECK_DATABASE( db );
2398
2399 QSqlQuery q( db );
2400 QVERIFY_SQL( q, prepare( "SELECT id FROM " + qtest + " WHERE id = ?" ) );
2401
2402 int id = 4;
2403 q.bindValue( 0, id );
2404 QVERIFY_SQL( q, exec() );
2405 QVERIFY( q.isActive() );
2406 QVERIFY_SQL( q, next() );
2407 QCOMPARE( q.value( 0 ).toInt(), id );
2408
2409 q.finish();
2410 QVERIFY( !q.isActive() ); // query is now inactive
2411 QCOMPARE( q.boundValue( 0 ).toInt(), id ); // bound values are retained
2412
2413 QVERIFY_SQL( q, exec() ); // no prepare necessary
2414 QVERIFY( q.isActive() );
2415 QVERIFY_SQL( q, next() );
2416 QCOMPARE( q.value( 0 ).toInt(), id );
2417
2418 q.finish();
2419 QVERIFY( !q.isActive() );
2420
2421 QVERIFY_SQL( q, exec( "SELECT id FROM " + qtest + " WHERE id = 1" ) );
2422 QVERIFY( q.isActive() );
2423 QVERIFY_SQL( q, next() );
2424 QCOMPARE( q.value( 0 ).toInt(), 1 );
2425 QCOMPARE( q.record().count(), 1 );
2426 }
2427
sqlite_finish()2428 void tst_QSqlQuery::sqlite_finish()
2429 {
2430 QFETCH( QString, dbName );
2431 QSqlDatabase db = QSqlDatabase::database( dbName );
2432 CHECK_DATABASE( db );
2433 if (db.driverName() != QLatin1String("QSQLITE")) {
2434 QSKIP("Sqlite3 specific test", SkipSingle);
2435 return;
2436 }
2437
2438 if ( db.databaseName().startsWith( ':' ) )
2439 QSKIP( "This test requires a database on the filesystem, not in-memory", SkipAll );
2440
2441 {
2442 QSqlDatabase db2 = QSqlDatabase::addDatabase( "QSQLITE", "sqlite_finish_sqlite" );
2443 db2.setDatabaseName( db.databaseName() );
2444 QVERIFY_SQL( db2, open() );
2445
2446 const QString tableName(qTableName( "qtest_lockedtable", __FILE__ ));
2447 QSqlQuery q( db );
2448
2449 tst_Databases::safeDropTable( db, tableName );
2450 q.exec( "CREATE TABLE " + tableName + " (pk_id INTEGER PRIMARY KEY, whatever TEXT)" );
2451 q.exec( "INSERT INTO " + tableName + " values(1, 'whatever')" );
2452 q.exec( "INSERT INTO " + tableName + " values(2, 'whatever more')" );
2453
2454 // This creates a read-lock in the database
2455 QVERIFY_SQL( q, exec( "SELECT * FROM " + tableName + " WHERE pk_id = 1 or pk_id = 2" ) );
2456 QVERIFY_SQL( q, next() );
2457
2458 // The DELETE will fail because of the read-lock
2459 QSqlQuery q2( db2 );
2460 QVERIFY( !q2.exec( "DELETE FROM " + tableName + " WHERE pk_id=2" ) );
2461 QCOMPARE( q2.numRowsAffected(), -1 );
2462
2463 // The DELETE will succeed now because finish() removes the lock
2464 q.finish();
2465 QVERIFY_SQL( q2, exec( "DELETE FROM " + tableName + " WHERE pk_id=2" ) );
2466 QCOMPARE( q2.numRowsAffected(), 1 );
2467
2468 tst_Databases::safeDropTable( db, tableName );
2469 }
2470
2471 QSqlDatabase::removeDatabase( "sqlite_finish_sqlite" );
2472 }
2473
nextResult()2474 void tst_QSqlQuery::nextResult()
2475 {
2476 QFETCH( QString, dbName );
2477 QSqlDatabase db = QSqlDatabase::database( dbName );
2478 CHECK_DATABASE( db );
2479
2480 if ( !db.driver()->hasFeature( QSqlDriver::MultipleResultSets ) || !db.driver()->hasFeature( QSqlDriver::BatchOperations ) )
2481 QSKIP( "DBMS does not support multiple result sets or batch operations", SkipSingle );
2482
2483 QSqlQuery q( db );
2484
2485 if ( db.driverName().startsWith( "QMYSQL" ) && tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 )
2486 QSKIP( "Test requires MySQL >= 5.0", SkipSingle );
2487
2488 enum DriverType { ODBC, MYSQL, DB2 };
2489 DriverType driverType = ODBC;
2490
2491 if ( db.driverName().startsWith( "QMYSQL" ) )
2492 driverType = MYSQL;
2493 else if ( db.driverName().startsWith( "QDB2" ) )
2494 driverType = DB2;
2495
2496 const QString tableName(qTableName( "more_results", __FILE__ ));
2497
2498 QVERIFY_SQL( q, exec( "CREATE TABLE " + tableName + " (id integer, text varchar(20), num numeric(6, 3), empty varchar(10));" ) );
2499
2500 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(1, 'one', 1.1, '');" ) );
2501
2502 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(2, 'two', 2.2, '');" ) );
2503
2504 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(3, 'three', 3.3, '');" ) );
2505
2506 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(4, 'four', 4.4, '');" ) );
2507
2508 QStringList tstStrings;
2509
2510 tstStrings << "one" << "two" << "three" << "four";
2511
2512 // Query that returns only one result set, nothing special about this
2513 QVERIFY_SQL( q, exec( QString( "SELECT * FROM %1;" ).arg( tableName ) ) );
2514
2515 QVERIFY( q.next() ); // Move to first row of the result set
2516
2517 QVERIFY( !q.nextResult() ); // No more result sets are available
2518
2519 QVERIFY( !q.isActive() ); // So the query is no longer active
2520
2521 QVERIFY( !q.next() ); // ... and no data is available as the call
2522
2523 // to nextResult() discarded the result set
2524
2525 // Query that returns two result sets (batch sql)
2526 // When working with multiple result sets SQL Server insists on non-scrollable cursors
2527 if ( driverType == ODBC )
2528 q.setForwardOnly( true );
2529
2530 QVERIFY_SQL( q, exec( "SELECT id FROM " + tableName + "; SELECT text, num FROM " + tableName + ';' ) );
2531
2532 QCOMPARE( q.record().count(), 1 ); // Check that the meta data is as expected
2533
2534 QCOMPARE( q.record().field( 0 ).name().toUpper(), QString( "ID" ) );
2535
2536 QCOMPARE( q.record().field( 0 ).type(), QVariant::Int );
2537
2538 QVERIFY( q.nextResult() ); // Discards first result set and move to the next
2539
2540 QCOMPARE( q.record().count(), 2 ); // New meta data should be available
2541
2542 QCOMPARE( q.record().field( 0 ).name().toUpper(), QString( "TEXT" ) );
2543
2544 QCOMPARE( q.record().field( 0 ).type(), QVariant::String );
2545
2546 QCOMPARE( q.record().field( 1 ).name().toUpper(), QString( "NUM" ) );
2547
2548 if ( driverType == MYSQL )
2549 QCOMPARE( q.record().field( 1 ).type(), QVariant::String );
2550 else
2551 QCOMPARE( q.record().field( 1 ).type(), QVariant::Double );
2552
2553 QVERIFY( q.next() ); // Move to first row of the second result set
2554
2555 QFAIL_SQL(q, nextResult()); // No more result sets after this
2556
2557 QVERIFY( !q.isActive() ); // So the query is no longer active
2558
2559 QVERIFY( !q.next() ); // ... and no data is available as the call to
2560
2561 // nextResult() discarded the result set
2562
2563 // Query that returns one result set, a count of affected rows and then another result set
2564 QString query1 = QString( "SELECT id, text, num, empty FROM %1 WHERE id <= 3" ).arg( tableName );
2565
2566 QString query2 = QString( "UPDATE %1 SET empty = 'Yatta!'" ).arg( tableName );
2567
2568 QString query3 = QString( "SELECT id, empty FROM %1 WHERE id <=2" ).arg( tableName );
2569
2570 QVERIFY_SQL( q, exec( QString( "%1; %2; %3;" ).arg( query1 ).arg( query2 ).arg( query3 ) ) );
2571
2572 // Check result set returned by first statement
2573 QVERIFY( q.isSelect() ); // The first statement is a select
2574
2575 for ( int i = 0; i < 3; i++ ) {
2576 QVERIFY_SQL( q, next() );
2577 QCOMPARE( q.value( 0 ).toInt(), 1+i );
2578 QCOMPARE( q.value( 1 ).toString(), tstStrings.at( i ) );
2579 QCOMPARE( q.value( 2 ).toDouble(), 1.1*( i+1 ) );
2580 QVERIFY( q.value( 3 ).toString().isEmpty() );
2581 }
2582
2583 QVERIFY_SQL( q, nextResult() );
2584
2585 QVERIFY( !q.isSelect() ); // The second statement isn't a SELECT
2586 QVERIFY( !q.next() ); // ... so no result set is available
2587 QCOMPARE( q.numRowsAffected(), 4 ); // 4 rows was affected by the UPDATE
2588
2589 // Check result set returned by third statement
2590 QVERIFY_SQL( q, nextResult() );
2591 QVERIFY( q.isSelect() ); // The third statement is a SELECT
2592
2593 for ( int i = 0; i < 2; i++ ) {
2594 QVERIFY_SQL( q, next() );
2595 QCOMPARE( q.value( 0 ).toInt(), 1+i );
2596 QCOMPARE( q.value( 1 ).toString(), QString( "Yatta!" ) );
2597 }
2598
2599 // Stored procedure with multiple result sets
2600 const QString procName(qTableName( "proc_more_res", __FILE__ ));
2601
2602 q.exec( QString( "DROP PROCEDURE %1;" ).arg( procName ) );
2603
2604 if ( driverType == MYSQL )
2605 QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1()"
2606 "\nBEGIN"
2607 "\nSELECT id, text FROM %2;"
2608 "\nSELECT empty, num, text, id FROM %3;"
2609 "\nEND" ).arg( procName ).arg( tableName ).arg( tableName ) ) );
2610 else if ( driverType == DB2 )
2611 QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1()"
2612 "\nRESULT SETS 2"
2613 "\nLANGUAGE SQL"
2614 "\np1:BEGIN"
2615 "\nDECLARE cursor1 CURSOR WITH RETURN FOR SELECT id, text FROM %2;"
2616 "\nDECLARE cursor2 CURSOR WITH RETURN FOR SELECT empty, num, text, id FROM %3;"
2617 "\nOPEN cursor1;"
2618 "\nOPEN cursor2;"
2619 "\nEND p1" ).arg( procName ).arg( tableName ).arg( tableName ) ) );
2620 else
2621 QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1"
2622 "\nAS"
2623 "\nSELECT id, text FROM %2"
2624 "\nSELECT empty, num, text, id FROM %3" ).arg( procName ).arg( tableName ).arg( tableName ) ) );
2625
2626 if ( driverType == MYSQL || driverType == DB2 ) {
2627 q.setForwardOnly( true );
2628 QVERIFY_SQL( q, exec( QString( "CALL %1()" ).arg( procName ) ) );
2629 } else {
2630 QVERIFY_SQL( q, exec( QString( "EXEC %1" ).arg( procName ) ) );
2631 }
2632
2633 for ( int i = 0; i < 4; i++ ) {
2634 QVERIFY_SQL( q, next() );
2635 QCOMPARE( q.value( 0 ).toInt(), i+1 );
2636 QCOMPARE( q.value( 1 ).toString(), tstStrings.at( i ) );
2637 }
2638
2639 QVERIFY_SQL( q, nextResult() );
2640
2641 QVERIFY_SQL( q, isActive() );
2642
2643 for ( int i = 0; i < 4; i++ ) {
2644 QVERIFY_SQL( q, next() );
2645 QCOMPARE( q.value( 0 ).toString(), QString( "Yatta!" ) );
2646 QCOMPARE( q.value( 1 ).toDouble(), 1.1*( 1+i ) );
2647 QCOMPARE( q.value( 2 ).toString(), tstStrings.at( i ) );
2648 QCOMPARE( q.value( 3 ).toInt(), 1+i );
2649 }
2650
2651 // MySQL also counts the CALL itself as a result
2652 if ( driverType == MYSQL ) {
2653 QVERIFY( q.nextResult() );
2654 QVERIFY( !q.isSelect() ); // ... but it's not a select
2655 QCOMPARE( q.numRowsAffected(), 0 ); // ... and no rows are affected (at least not with this procedure)
2656 }
2657
2658 QVERIFY( !q.nextResult() );
2659
2660 QVERIFY( !q.isActive() );
2661
2662 q.exec( QString( "DROP PROCEDURE %1;" ).arg( procName ) );
2663 }
2664
2665
2666 // For task 190311. Problem: Truncation happens on the 2nd execution if that BLOB is larger
2667 // than the BLOB on the 1st execution. This was only for MySQL, but the test is general
2668 // enough to be run with all backends.
blobsPreparedQuery()2669 void tst_QSqlQuery::blobsPreparedQuery()
2670 {
2671 QFETCH( QString, dbName );
2672 QSqlDatabase db = QSqlDatabase::database( dbName );
2673 CHECK_DATABASE( db );
2674
2675 if ( !db.driver()->hasFeature( QSqlDriver::BLOB ) || !db.driver()->hasFeature( QSqlDriver::PreparedQueries ) )
2676 QSKIP( "DBMS does not support BLOBs or prepared queries", SkipSingle );
2677
2678 const QString tableName(qTableName( "blobstest", __FILE__ ));
2679
2680 QSqlQuery q( db );
2681 q.setForwardOnly( true ); // This is needed to make the test work with DB2.
2682 QString shortBLOB( "abc" );
2683 QString longerBLOB( "abcdefghijklmnopqrstuvxyz¿äëïöü¡ " );
2684
2685 // In PostgreSQL a BLOB is not called a BLOB, but a BYTEA! :-)
2686 // ... and in SQL Server it can be called a lot, but IMAGE will do.
2687 QString typeName( "BLOB" );
2688 if ( db.driverName().startsWith( "QPSQL" ) )
2689 typeName = "BYTEA";
2690 else if ( db.driverName().startsWith( "QODBC" ) && tst_Databases::isSqlServer( db ))
2691 typeName = "IMAGE";
2692
2693 QVERIFY_SQL( q, exec( QString( "CREATE TABLE %1(id INTEGER, data %2)" ).arg( tableName ).arg( typeName ) ) );
2694 q.prepare( QString( "INSERT INTO %1(id, data) VALUES(:id, :data)" ).arg( tableName ) );
2695 q.bindValue( ":id", 1 );
2696 q.bindValue( ":data", shortBLOB.toAscii() );
2697 QVERIFY_SQL( q, exec() );
2698
2699 q.bindValue( ":id", 2 );
2700 q.bindValue( ":data", longerBLOB.toAscii() );
2701 QVERIFY_SQL( q, exec() );
2702
2703 // Two executions and result sets
2704 q.prepare( QString( "SELECT data FROM %1 WHERE id = ?" ).arg( tableName ) );
2705 q.bindValue( 0, QVariant( 1 ) );
2706 QVERIFY_SQL( q, exec() );
2707 QVERIFY_SQL( q, next() );
2708 QCOMPARE( q.value( 0 ).toString(), shortBLOB );
2709
2710 q.bindValue( 0, QVariant( 2 ) );
2711 QVERIFY_SQL( q, exec() );
2712 QVERIFY_SQL( q, next() );
2713 QCOMPARE( q.value( 0 ).toString(), longerBLOB );
2714
2715 // Only one execution and result set
2716 q.prepare( QString( "SELECT id, data FROM %1 ORDER BY id" ).arg( tableName ) );
2717 QVERIFY_SQL( q, exec() );
2718 QVERIFY_SQL( q, next() );
2719 QCOMPARE( q.value( 1 ).toString(), shortBLOB );
2720 QVERIFY_SQL( q, next() );
2721 QCOMPARE( q.value( 1 ).toString(), longerBLOB );
2722 }
2723
2724 // There were problems with navigating past the end of a table returning an error on mysql
emptyTableNavigate()2725 void tst_QSqlQuery::emptyTableNavigate()
2726 {
2727 QFETCH( QString, dbName );
2728 QSqlDatabase db = QSqlDatabase::database( dbName );
2729 CHECK_DATABASE( db );
2730
2731 {
2732 QSqlQuery q( db );
2733 QVERIFY_SQL( q, exec( "create table " + qTableName( "qtest_empty", __FILE__ ) + " (id char(10))" ) );
2734 QVERIFY_SQL( q, prepare( "select * from " + qTableName( "qtest_empty", __FILE__ ) ) );
2735 QVERIFY_SQL( q, exec() );
2736 QVERIFY( !q.next() );
2737 QCOMPARE( q.lastError().isValid(), false );
2738 }
2739 }
2740
task_217003()2741 void tst_QSqlQuery::task_217003()
2742 {
2743 QFETCH( QString, dbName );
2744 QSqlDatabase db = QSqlDatabase::database( dbName );
2745 CHECK_DATABASE( db );
2746 QSqlQuery q( db );
2747 const QString Planet(qTableName( "Planet", __FILE__));
2748
2749 QVERIFY_SQL( q, exec( "create table " + Planet + " (Name varchar(20))" ) );
2750 QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Mercury')" ) );
2751 QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Venus')" ) );
2752 QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Earth')" ) );
2753 QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Mars')" ) );
2754
2755 QVERIFY_SQL( q, exec( "SELECT Name FROM " + Planet ) );
2756 QVERIFY_SQL( q, seek( 3 ) );
2757 QCOMPARE( q.value( 0 ).toString(), QString( "Mars" ) );
2758 QVERIFY_SQL( q, seek( 1 ) );
2759 QCOMPARE( q.value( 0 ).toString(), QString( "Venus" ) );
2760 QVERIFY_SQL( q, exec( "SELECT Name FROM " + Planet ) );
2761 QVERIFY_SQL( q, seek( 3 ) );
2762 QCOMPARE( q.value( 0 ).toString(), QString( "Mars" ) );
2763 QVERIFY_SQL( q, seek( 0 ) );
2764 QCOMPARE( q.value( 0 ).toString(), QString( "Mercury" ) );
2765 QVERIFY_SQL( q, seek( 1 ) );
2766 QCOMPARE( q.value( 0 ).toString(), QString( "Venus" ) );
2767 }
2768
task_250026()2769 void tst_QSqlQuery::task_250026()
2770 {
2771 QString data258, data1026;
2772 QFETCH( QString, dbName );
2773 QSqlDatabase db = QSqlDatabase::database( dbName );
2774 CHECK_DATABASE( db );
2775 QSqlQuery q( db );
2776
2777 const QString tableName(qTableName( "task_250026", __FILE__ ));
2778
2779 if ( !q.exec( "create table " + tableName + " (longfield varchar(1100))" ) ) {
2780 qDebug() << "Error" << q.lastError();
2781 QSKIP( "Db doesn't support \"1100\" as a size for fields", SkipSingle );
2782 }
2783
2784 data258.fill( 'A', 258 );
2785 data1026.fill( 'A', 1026 );
2786 QVERIFY_SQL( q, prepare( "insert into " + tableName + "(longfield) VALUES (:longfield)" ) );
2787 q.bindValue( "longfield", data258 );
2788 QVERIFY_SQL( q, exec() );
2789 q.bindValue( "longfield", data1026 );
2790 QVERIFY_SQL( q, exec() );
2791 QVERIFY_SQL( q, exec( "select * from " + tableName ) );
2792 QVERIFY_SQL( q, next() );
2793 QCOMPARE( q.value( 0 ).toString().length(), data258.length() );
2794 QVERIFY_SQL( q, next() );
2795 QCOMPARE( q.value( 0 ).toString().length(), data1026.length() );
2796 }
2797
task_205701()2798 void tst_QSqlQuery::task_205701()
2799 {
2800 QSqlDatabase qsdb = QSqlDatabase::addDatabase("QMYSQL", "atest");
2801 qsdb.setHostName("test");
2802 qsdb.setDatabaseName("test");
2803 qsdb.setUserName("test");
2804 qsdb.setPassword("test");
2805 qsdb.open();
2806
2807 // {
2808 QSqlQuery query(qsdb);
2809 // }
2810 QSqlDatabase::removeDatabase("atest");
2811 }
2812
2813 #ifdef NOT_READY_YET
2814 // For task: 229811
task_229811()2815 void tst_QSqlQuery::task_229811()
2816 {
2817 QFETCH( QString, dbName );
2818 QSqlDatabase db = QSqlDatabase::database( dbName );
2819 CHECK_DATABASE( db );
2820
2821 if (!db.driverName().startsWith( "QODBC" )) return;
2822
2823 QSqlQuery q( db );
2824
2825 const QString tableName(qTableName( "task_229811", __FILE__ ));
2826
2827 if ( !q.exec( "CREATE TABLE " + tableName + " (Word varchar(20))" ) ) {
2828 qDebug() << "Warning" << q.lastError();
2829 }
2830
2831 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " values ('Albert')" ) );
2832 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " values ('Beehive')" ) );
2833 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " values ('Alimony')" ) );
2834 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " values ('Bohemian')" ) );
2835 QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " values ('AllStars')" ) );
2836
2837
2838 QString stmt = "SELECT * FROM " + tableName + " WHERE Word LIKE :name";
2839 QVERIFY_SQL(q,prepare(stmt));
2840 q.bindValue(":name", "A%");
2841 QVERIFY_SQL(q,exec());
2842
2843 QVERIFY(q.isActive());
2844 QVERIFY(q.isSelect());
2845 QVERIFY(q.first());
2846
2847 QSqlRecord rec = q.record();
2848 QCOMPARE(rec.field(0).value().toString(), QString("Albert"));
2849 QVERIFY(q.next());
2850 rec = q.record();
2851 QCOMPARE(rec.field(0).value().toString(), QString("Alimony"));
2852 QVERIFY(q.next());
2853 rec = q.record();
2854 QCOMPARE(rec.field(0).value().toString(),QString("AllStars"));
2855
2856 q.exec("DROP TABLE " + tableName );
2857 }
2858
task_234422()2859 void tst_QSqlQuery::task_234422()
2860 {
2861 QFETCH( QString, dbName );
2862 QSqlDatabase db = QSqlDatabase::database( dbName );
2863 CHECK_DATABASE( db );
2864
2865 QSqlQuery query(db);
2866 QStringList m_airlines;
2867 QStringList m_countries;
2868
2869 m_airlines << "Lufthansa" << "SAS" << "United" << "KLM" << "Aeroflot";
2870 m_countries << "DE" << "SE" << "US" << "NL" << "RU";
2871
2872 const QString tableName(qTableName( "task_234422", __FILE__ ));
2873
2874 QVERIFY_SQL(query,exec("CREATE TABLE " + tableName + " (id int primary key, "
2875 "name varchar(20), homecountry varchar(2))"));
2876 for (int i = 0; i < m_airlines.count(); ++i) {
2877 QVERIFY(query.exec(QString("INSERT INTO " + tableName + " values(%1, '%2', '%3')")
2878 .arg(i).arg(m_airlines[i], m_countries[i])));
2879 }
2880
2881 QVERIFY_SQL(query, exec("SELECT name FROM " + tableName));
2882 QVERIFY(query.isSelect());
2883 QVERIFY(query.first());
2884 QVERIFY(query.next());
2885 QCOMPARE(query.at(), 1);
2886
2887 QSqlQuery query2(query);
2888
2889 QVERIFY_SQL(query2,exec());
2890 QVERIFY(query2.first());
2891 QCOMPARE(query2.at(), 0);
2892 QCOMPARE(query.at(), 1);
2893 }
2894
2895 #endif
2896
task_233829()2897 void tst_QSqlQuery::task_233829()
2898 {
2899 QFETCH( QString, dbName );
2900 QSqlDatabase db = QSqlDatabase::database( dbName );
2901 CHECK_DATABASE( db );
2902
2903 QSqlQuery q( db );
2904 const QString tableName(qTableName("task_233829", __FILE__));
2905 QVERIFY_SQL(q,exec("CREATE TABLE " + tableName + "(dbl1 double precision,dbl2 double precision) without oids;"));
2906
2907 QString queryString("INSERT INTO " + tableName +"(dbl1, dbl2) VALUES(?,?)");
2908
2909 double k = 0.0;
2910 QVERIFY_SQL(q,prepare(queryString));
2911 q.bindValue(0,0.0 / k); // nan
2912 q.bindValue(1,0.0 / k); // nan
2913 QVERIFY_SQL(q,exec());
2914 }
2915
sqlServerReturn0()2916 void tst_QSqlQuery::sqlServerReturn0()
2917 {
2918 QFETCH( QString, dbName );
2919 QSqlDatabase db = QSqlDatabase::database( dbName );
2920 CHECK_DATABASE( db );
2921 if (!tst_Databases::isSqlServer( db ))
2922 QSKIP("SQL Server specific test", SkipSingle);
2923
2924 const QString tableName(qTableName("test141895", __FILE__)), procName(qTableName("test141895_proc", __FILE__));
2925 QSqlQuery q( db );
2926 q.exec("DROP TABLE " + tableName);
2927 q.exec("DROP PROCEDURE " + procName);
2928 QVERIFY_SQL(q, exec("CREATE TABLE "+tableName+" (id integer)"));
2929 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (1)"));
2930 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (2)"));
2931 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (2)"));
2932 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (3)"));
2933 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (1)"));
2934 QVERIFY_SQL(q, exec("CREATE PROCEDURE "+procName+
2935 " AS "
2936 "SELECT * FROM "+tableName+" WHERE ID = 2 "
2937 "RETURN 0"));
2938
2939 QVERIFY_SQL(q, exec("{CALL "+procName+"}"));
2940
2941 QVERIFY_SQL(q, next());
2942 }
2943
QTBUG_551()2944 void tst_QSqlQuery::QTBUG_551()
2945 {
2946 QFETCH( QString, dbName );
2947 QSqlDatabase db = QSqlDatabase::database( dbName );
2948 CHECK_DATABASE( db );
2949 QSqlQuery q(db);
2950 const QString pkgname(qTableName("pkg", __FILE__));
2951 QVERIFY_SQL(q, exec("CREATE OR REPLACE PACKAGE "+pkgname+" IS \n\
2952 \n\
2953 TYPE IntType IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;\n\
2954 TYPE VCType IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;\n\
2955 PROCEDURE P (Inp IN IntType, Outp OUT VCType);\n\
2956 END "+pkgname+";"));
2957
2958 QVERIFY_SQL(q, exec("CREATE OR REPLACE PACKAGE BODY "+pkgname+" IS\n\
2959 PROCEDURE P (Inp IN IntType, Outp OUT VCType)\n\
2960 IS\n\
2961 BEGIN\n\
2962 Outp(1) := '1. Value is ' ||TO_CHAR(Inp(1));\n\
2963 Outp(2) := '2. Value is ' ||TO_CHAR(Inp(2));\n\
2964 Outp(3) := '3. Value is ' ||TO_CHAR(Inp(3));\n\
2965 END p;\n\
2966 END "+pkgname+";"));
2967
2968 QVariantList inLst, outLst, res_outLst;
2969
2970 q.prepare("begin "+pkgname+".p(:inp, :outp); end;");
2971
2972 QString StVal;
2973 StVal.reserve(60);
2974
2975 // loading arrays
2976 for (int Cnt=0; Cnt < 3; Cnt++) {
2977 inLst << Cnt;
2978 outLst << StVal;
2979 }
2980
2981 q.bindValue(":inp", inLst);
2982 q.bindValue(":outp", outLst, QSql::Out);
2983
2984 QVERIFY_SQL(q, execBatch(QSqlQuery::ValuesAsColumns) );
2985 res_outLst = qVariantValue<QVariantList>(q.boundValues()[":outp"]);
2986 QCOMPARE(res_outLst[0].toString(), QLatin1String("1. Value is 0"));
2987 QCOMPARE(res_outLst[1].toString(), QLatin1String("2. Value is 1"));
2988 QCOMPARE(res_outLst[2].toString(), QLatin1String("3. Value is 2"));
2989 }
2990
QTBUG_14132()2991 void tst_QSqlQuery::QTBUG_14132()
2992 {
2993 QFETCH( QString, dbName );
2994 QSqlDatabase db = QSqlDatabase::database( dbName );
2995 CHECK_DATABASE( db );
2996 QSqlQuery q(db);
2997 const QString procedureName(qTableName("procedure", __FILE__));
2998 QVERIFY_SQL(q, exec("CREATE OR REPLACE PROCEDURE "+ procedureName + " (outStr OUT varchar2) \n\
2999 is \n\
3000 begin \n\
3001 outStr := 'OUTSTRING'; \n\
3002 end;"));
3003 QString placeholder = "XXXXXXXXX";
3004 QVERIFY(q.prepare("CALL "+procedureName+"(?)"));
3005 q.addBindValue(placeholder, QSql::Out);
3006 QVERIFY_SQL(q, exec());
3007 QCOMPARE(q.boundValue(0).toString(), QLatin1String("OUTSTRING"));
3008 }
3009
QTBUG_5251()3010 void tst_QSqlQuery::QTBUG_5251()
3011 {
3012 QFETCH( QString, dbName );
3013 QSqlDatabase db = QSqlDatabase::database( dbName );
3014 CHECK_DATABASE( db );
3015 const QString timetest(qTableName("timetest", __FILE__));
3016
3017 if (!db.driverName().startsWith( "QPSQL" )) return;
3018
3019 QSqlQuery q(db);
3020 q.exec("DROP TABLE " + timetest);
3021 QVERIFY_SQL(q, exec("CREATE TABLE " + timetest + " (t TIME)"));
3022 QVERIFY_SQL(q, exec("INSERT INTO " + timetest + " VALUES ('1:2:3.666')"));
3023
3024 QSqlTableModel timetestModel(0,db);
3025 timetestModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
3026 timetestModel.setTable(timetest);
3027 QVERIFY_SQL(timetestModel, select());
3028
3029 QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("01:02:03.666"));
3030 QVERIFY_SQL(timetestModel,setData(timetestModel.index(0, 0), QTime(0,12,34,500)));
3031 QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:12:34.500"));
3032 QVERIFY_SQL(timetestModel, submitAll());
3033 QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:12:34.500"));
3034
3035 QVERIFY_SQL(q, exec("UPDATE " + timetest + " SET t = '0:11:22.33'"));
3036 QVERIFY_SQL(timetestModel, select());
3037 QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:11:22.330"));
3038
3039 }
3040
QTBUG_6421()3041 void tst_QSqlQuery::QTBUG_6421()
3042 {
3043 QFETCH( QString, dbName );
3044 QSqlDatabase db = QSqlDatabase::database( dbName );
3045 CHECK_DATABASE( db );
3046
3047 QSqlQuery q(db);
3048 const QString tableName(qTableName("bug6421", __FILE__).toUpper());
3049
3050 QVERIFY_SQL(q, exec("create table "+tableName+"(COL1 char(10), COL2 char(10), COL3 char(10))"));
3051 QVERIFY_SQL(q, exec("create index INDEX1 on "+tableName+" (COL1 desc)"));
3052 QVERIFY_SQL(q, exec("create index INDEX2 on "+tableName+" (COL2 desc)"));
3053 QVERIFY_SQL(q, exec("create index INDEX3 on "+tableName+" (COL3 desc)"));
3054 q.setForwardOnly(true);
3055 QVERIFY_SQL(q, exec("select COLUMN_EXPRESSION from ALL_IND_EXPRESSIONS where TABLE_NAME='"+tableName+"'"));
3056 QVERIFY_SQL(q, next());
3057 QCOMPARE(q.value(0).toString(), QLatin1String("\"COL1\""));
3058 QVERIFY_SQL(q, next());
3059 QCOMPARE(q.value(0).toString(), QLatin1String("\"COL2\""));
3060 QVERIFY_SQL(q, next());
3061 QCOMPARE(q.value(0).toString(), QLatin1String("\"COL3\""));
3062 }
3063
QTBUG_6618()3064 void tst_QSqlQuery::QTBUG_6618()
3065 {
3066 QFETCH( QString, dbName );
3067 QSqlDatabase db = QSqlDatabase::database( dbName );
3068 CHECK_DATABASE( db );
3069 if (!tst_Databases::isSqlServer( db ))
3070 QSKIP("SQL Server specific test", SkipSingle);
3071
3072 QSqlQuery q(db);
3073 q.exec( "drop procedure " + qTableName( "tst_raiseError", __FILE__ ) ); //non-fatal
3074 QString errorString;
3075 for (int i=0;i<110;i++)
3076 errorString+="reallylong";
3077 errorString+=" error";
3078 QVERIFY_SQL( q, exec("create procedure " + qTableName( "tst_raiseError", __FILE__ ) + " as\n"
3079 "begin\n"
3080 " raiserror('" + errorString + "', 16, 1)\n"
3081 "end\n" ));
3082 q.exec( "{call " + qTableName( "tst_raiseError", __FILE__ ) + "}" );
3083 QVERIFY(q.lastError().text().contains(errorString));
3084 }
3085
QTBUG_6852()3086 void tst_QSqlQuery::QTBUG_6852()
3087 {
3088 QFETCH( QString, dbName );
3089 QSqlDatabase db = QSqlDatabase::database( dbName );
3090 CHECK_DATABASE( db );
3091 if ( tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 )
3092 QSKIP( "Test requires MySQL >= 5.0", SkipSingle );
3093
3094 QSqlQuery q(db);
3095 const QString tableName(qTableName("bug6852", __FILE__)), procName(qTableName("bug6852_proc", __FILE__));
3096
3097 QVERIFY_SQL(q, exec("DROP PROCEDURE IF EXISTS "+procName));
3098 QVERIFY_SQL(q, exec("CREATE TABLE "+tableName+"(\n"
3099 "MainKey INT NOT NULL,\n"
3100 "OtherTextCol VARCHAR(45) NOT NULL,\n"
3101 "PRIMARY KEY(`MainKey`))"));
3102 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(0, \"Disabled\")"));
3103 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(5, \"Error Only\")"));
3104 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(10, \"Enabled\")"));
3105 QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(15, \"Always\")"));
3106 QVERIFY_SQL(q, exec("CREATE PROCEDURE "+procName+"()\n"
3107 "READS SQL DATA\n"
3108 "BEGIN\n"
3109 " SET @st = 'SELECT MainKey, OtherTextCol from "+tableName+"';\n"
3110 " PREPARE stmt from @st;\n"
3111 " EXECUTE stmt;\n"
3112 "END;"));
3113
3114 QVERIFY_SQL(q, exec("CALL "+procName+"()"));
3115 QVERIFY_SQL(q, next());
3116 QCOMPARE(q.value(0).toInt(), 0);
3117 QCOMPARE(q.value(1).toString(), QLatin1String("Disabled"));
3118 }
3119
QTBUG_5765()3120 void tst_QSqlQuery::QTBUG_5765()
3121 {
3122 QFETCH( QString, dbName );
3123 QSqlDatabase db = QSqlDatabase::database( dbName );
3124 CHECK_DATABASE( db );
3125 if ( tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 1 ).toFloat()<4.1 )
3126 QSKIP( "Test requires MySQL >= 4.1", SkipSingle );
3127
3128 QSqlQuery q(db);
3129 const QString tableName(qTableName("bug5765", __FILE__));
3130
3131 QVERIFY_SQL(q, exec("CREATE TABLE "+tableName+"(testval TINYINT(1) DEFAULT 0)"));
3132 q.prepare("INSERT INTO "+tableName+" SET testval = :VALUE");
3133 q.bindValue(":VALUE", 1);
3134 QVERIFY_SQL(q, exec());
3135 q.bindValue(":VALUE", 12);
3136 QVERIFY_SQL(q, exec());
3137 q.bindValue(":VALUE", 123);
3138 QVERIFY_SQL(q, exec());
3139 QString sql="select testval from "+tableName;
3140 QVERIFY_SQL(q, exec(sql));
3141 QVERIFY_SQL(q, next());
3142 QCOMPARE(q.value(0).toInt(), 1);
3143 QVERIFY_SQL(q, next());
3144 QCOMPARE(q.value(0).toInt(), 12);
3145 QVERIFY_SQL(q, next());
3146 QCOMPARE(q.value(0).toInt(), 123);
3147 QVERIFY_SQL(q, prepare(sql));
3148 QVERIFY_SQL(q, exec());
3149 QVERIFY_SQL(q, next());
3150 QCOMPARE(q.value(0).toInt(), 1);
3151 QVERIFY_SQL(q, next());
3152 QCOMPARE(q.value(0).toInt(), 12);
3153 QVERIFY_SQL(q, next());
3154 QCOMPARE(q.value(0).toInt(), 123);
3155 }
3156
3157 /**
3158 * This test case tests multiple statements in one execution.
3159 * Sqlite driver doesn't support multiple statement at one time.
3160 * If more than one statement is given, the exec or prepare function
3161 * return failure to the client.
3162 */
QTBUG_21884()3163 void tst_QSqlQuery::QTBUG_21884()
3164 {
3165 QFETCH(QString, dbName);
3166 QSqlDatabase db = QSqlDatabase::database(dbName);
3167 CHECK_DATABASE(db);
3168
3169 QSqlQuery q(db);
3170
3171 QStringList stList;
3172 QString tableName(qTableName("bug21884", __FILE__ ));
3173 stList << "create table " + tableName + "(id integer primary key, note string)";
3174 stList << "select * from " + tableName + ";";
3175 stList << "select * from " + tableName + "; \t\n\r";
3176 stList << "drop table " + tableName;
3177
3178
3179 foreach (const QString& st, stList) {
3180 QVERIFY_SQL(q, exec(st));
3181 }
3182
3183 foreach (const QString& st, stList) {
3184 QVERIFY_SQL(q, prepare(st));
3185 QVERIFY_SQL(q, exec());
3186 }
3187
3188 stList.clear();
3189 stList << "create table " + tableName + "(id integer primary key); select * from " + tableName;
3190 stList << "create table " + tableName + "(id integer primary key); syntax error!;";
3191 stList << "create table " + tableName + "(id integer primary key);;";
3192 stList << "create table " + tableName + "(id integer primary key);\'\"\a\b\b\v";
3193
3194 foreach (const QString&st , stList) {
3195 QVERIFY2(!q.prepare(st), qPrintable(QString("the statement is expected to fail! ") + st));
3196 QVERIFY2(!q.exec(st), qPrintable(QString("the statement is expected to fail! ") + st));
3197 }
3198 }
3199
3200 /**
3201 * This test case test sqlite driver close function. Sqlite driver should close cleanly
3202 * even if there is still outstanding prepared statement.
3203 */
QTBUG_16967()3204 void tst_QSqlQuery::QTBUG_16967()
3205 {
3206 QSqlQuery q2;
3207 QFETCH(QString, dbName);
3208 {
3209 QSqlDatabase db = QSqlDatabase::database(dbName);
3210 CHECK_DATABASE(db);
3211 db.close();
3212 QCOMPARE(db.lastError().type(), QSqlError::NoError);
3213 }
3214 {
3215 QSqlDatabase db = QSqlDatabase::database(dbName);
3216 CHECK_DATABASE(db);
3217 QSqlQuery q(db);
3218 q2 = q;
3219 q.prepare("CREATE TABLE t1 (id INTEGER PRIMARY KEY, str TEXT);");
3220 db.close();
3221 QCOMPARE(db.lastError().type(), QSqlError::NoError);
3222 }
3223 {
3224 QSqlDatabase db = QSqlDatabase::database(dbName);
3225 CHECK_DATABASE(db);
3226 QSqlQuery q(db);
3227 q2 = q;
3228 q2.prepare("CREATE TABLE t1 (id INTEGER PRIMARY KEY, str TEXT);");
3229 q2.exec();
3230 db.close();
3231 QCOMPARE(db.lastError().type(), QSqlError::NoError);
3232 }
3233 {
3234 QSqlDatabase db = QSqlDatabase::database(dbName);
3235 CHECK_DATABASE(db);
3236 QSqlQuery q(db);
3237 q2 = q;
3238 q.exec("INSERT INTO t1 (id, str) VALUES(1, \"test1\");");
3239 db.close();
3240 QCOMPARE(db.lastError().type(), QSqlError::NoError);
3241 }
3242 {
3243 QSqlDatabase db = QSqlDatabase::database(dbName);
3244 CHECK_DATABASE(db);
3245 QSqlQuery q(db);
3246 q2 = q;
3247 q2.exec("SELECT * FROM t1;");
3248 db.close();
3249 QCOMPARE(db.lastError().type(), QSqlError::NoError);
3250 }
3251 }
3252
3253 /**
3254 * Test for aliases with dots
3255 */
QTBUG_14904()3256 void tst_QSqlQuery::QTBUG_14904()
3257 {
3258 QFETCH(QString, dbName);
3259 QSqlDatabase db = QSqlDatabase::database(dbName);
3260 CHECK_DATABASE(db);
3261
3262 QSqlQuery q(db);
3263
3264 QString tableName(qTableName("bug14904", __FILE__ ));
3265 tst_Databases::safeDropTable( db, tableName );
3266
3267 q.prepare("create table " + tableName + "(val1 int)");
3268 QVERIFY_SQL(q, exec());
3269 q.prepare("insert into " + tableName + "(val1) values(?);");
3270 q.addBindValue(1);
3271 QVERIFY_SQL(q, exec());
3272
3273 QString sql="select val1 AS value1 from " + tableName;
3274 QVERIFY_SQL(q, exec(sql));
3275 QVERIFY_SQL(q, next());
3276
3277 QCOMPARE(q.record().indexOf("value1"), 0);
3278 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3279 QCOMPARE(q.value(0).toInt(), 1);
3280
3281 sql="select val1 AS 'value.one' from " + tableName;
3282 QVERIFY_SQL(q, exec(sql));
3283 QVERIFY_SQL(q, next());
3284 QCOMPARE(q.record().indexOf("value.one"), 0); // was -1 before bug fix
3285 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3286 QCOMPARE(q.value(0).toInt(), 1);
3287 }
3288
QTBUG_2192()3289 void tst_QSqlQuery::QTBUG_2192()
3290 {
3291 QFETCH( QString, dbName );
3292 QSqlDatabase db = QSqlDatabase::database( dbName );
3293 CHECK_DATABASE( db );
3294 {
3295 const QString tableName(qTableName("bug2192", __FILE__));
3296 tst_Databases::safeDropTable( db, tableName );
3297
3298 QSqlQuery q(db);
3299 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (dt DATETIME)"));
3300
3301 QDateTime dt = QDateTime(QDate(2012, 7, 4), QTime(23, 59, 59, 999));
3302 QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (dt) VALUES (?)"));
3303 q.bindValue(0, dt);
3304 QVERIFY_SQL(q, exec());
3305
3306 QVERIFY_SQL(q, exec("SELECT dt FROM " + tableName));
3307 QVERIFY_SQL(q, next());
3308
3309 // Check if retrieved value preserves reported precision
3310 int precision = qMax(0, q.record().field("dt").precision());
3311 int diff = qAbs(q.value(0).toDateTime().msecsTo(dt));
3312 int keep = qMin(1000, (int)qPow(10.0, precision));
3313 QVERIFY(diff <= 1000 - keep);
3314 }
3315 }
3316
oraOCINumber()3317 void tst_QSqlQuery::oraOCINumber()
3318 {
3319 QFETCH( QString, dbName );
3320 QSqlDatabase db = QSqlDatabase::database( dbName );
3321 CHECK_DATABASE( db );
3322 const QString qtest_oraOCINumber(qTableName("qtest_oraOCINumber", __FILE__));
3323
3324 QSqlQuery q( db );
3325 q.setForwardOnly( true );
3326 QVERIFY_SQL( q, exec( "create table " + qtest_oraOCINumber +
3327 " (col1 number(20), col2 number(20))" ) );
3328 QVERIFY(q.prepare("insert into " + qtest_oraOCINumber + " values (?, ?)"));
3329 QVariantList col1Values;
3330 QVariantList col2Values;
3331 col1Values << (qulonglong)(1)
3332 << (qulonglong)(0)
3333 << (qulonglong)(INT_MAX)
3334 << (qulonglong)(UINT_MAX)
3335 << (qulonglong)(LONG_MAX)
3336 << (qulonglong)(ULONG_MAX)
3337 << (qulonglong)(LLONG_MAX)
3338 << (qulonglong)(ULLONG_MAX);
3339
3340 col2Values << (qlonglong)(1)
3341 << (qlonglong)(0)
3342 << (qlonglong)(-1)
3343 << (qlonglong)(LONG_MAX)
3344 << (qlonglong)(LONG_MIN)
3345 << (qlonglong)(ULONG_MAX)
3346 << (qlonglong)(LLONG_MAX)
3347 << (qlonglong)(LLONG_MIN);
3348
3349 q.addBindValue(col1Values);
3350 q.addBindValue(col2Values);
3351 QVERIFY(q.execBatch());
3352 QString sqlStr = "select * from " + qtest_oraOCINumber + " where col1 = :bindValue0 AND col2 = :bindValue1";
3353 QVERIFY(q.prepare(sqlStr));
3354
3355 q.bindValue(":bindValue0", (qulonglong)(1), QSql::InOut);
3356 q.bindValue(":bindValue1", (qlonglong)(1), QSql::InOut);
3357
3358 QVERIFY_SQL( q, exec() );
3359 QVERIFY( q.next() );
3360 QCOMPARE(q.boundValue( 0 ).toULongLong(), qulonglong(1));
3361 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(1));
3362
3363 q.bindValue(":bindValue0", (qulonglong)(0), QSql::InOut);
3364 q.bindValue(":bindValue1", (qlonglong)(0), QSql::InOut);
3365 QVERIFY_SQL( q, exec() );
3366
3367 QVERIFY( q.next() );
3368 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(0));
3369 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(0));
3370
3371 q.bindValue(":bindValue0", (qulonglong)(INT_MAX), QSql::InOut);
3372 q.bindValue(":bindValue1", (qlonglong)(-1), QSql::InOut);
3373 QVERIFY_SQL( q, exec() );
3374
3375 QVERIFY( q.next() );
3376 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(INT_MAX));
3377 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(-1));
3378
3379 q.bindValue(":bindValue0", (qulonglong)(UINT_MAX), QSql::InOut);
3380 q.bindValue(":bindValue1", (qlonglong)(LONG_MAX), QSql::InOut);
3381 QVERIFY_SQL( q, exec() );
3382
3383 QVERIFY( q.next() );
3384 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(UINT_MAX));
3385 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LONG_MAX));
3386
3387 q.bindValue(":bindValue0", (qulonglong)(LONG_MAX), QSql::InOut);
3388 q.bindValue(":bindValue1", (qlonglong)(LONG_MIN), QSql::InOut);
3389 QVERIFY_SQL( q, exec() );
3390
3391 QVERIFY( q.next() );
3392 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(LONG_MAX));
3393 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LONG_MIN));
3394
3395 q.bindValue(":bindValue0", (qulonglong)(ULONG_MAX), QSql::InOut);
3396 q.bindValue(":bindValue1", (qlonglong)(ULONG_MAX), QSql::InOut);
3397 QVERIFY_SQL( q, exec() );
3398
3399 QVERIFY( q.next() );
3400 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(ULONG_MAX));
3401 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(ULONG_MAX));
3402
3403 q.bindValue(":bindValue0", (qulonglong)(LLONG_MAX), QSql::InOut);
3404 q.bindValue(":bindValue1", (qlonglong)(LLONG_MAX), QSql::InOut);
3405 QVERIFY_SQL( q, exec() );
3406
3407 QVERIFY( q.next() );
3408 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(LLONG_MAX));
3409 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LLONG_MAX));
3410
3411 q.bindValue(":bindValue0", (qulonglong)(ULLONG_MAX), QSql::InOut);
3412 q.bindValue(":bindValue1", (qlonglong)(LLONG_MIN), QSql::InOut);
3413 QVERIFY_SQL( q, exec() );
3414
3415 QVERIFY( q.next() );
3416 QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(ULLONG_MAX));
3417 QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LLONG_MIN));
3418
3419 }
3420
sqlite_constraint()3421 void tst_QSqlQuery::sqlite_constraint()
3422 {
3423 QFETCH( QString, dbName );
3424 QSqlDatabase db = QSqlDatabase::database( dbName );
3425 CHECK_DATABASE( db );
3426
3427 if (db.driverName() != QLatin1String("QSQLITE")) {
3428 QSKIP("Sqlite3 specific test", SkipSingle);
3429 return;
3430 }
3431
3432 QSqlQuery q(db);
3433 const QString trigger(qTableName("test_constraint", __FILE__));
3434
3435 QVERIFY_SQL(q, exec("CREATE TEMP TRIGGER "+trigger+" BEFORE DELETE ON "+qtest+
3436 "\nFOR EACH ROW "
3437 "\nBEGIN"
3438 "\n SELECT RAISE(ABORT, 'Raised Abort successfully');"
3439 "\nEND;"
3440 ));
3441
3442 QVERIFY(!q.exec("DELETE FROM "+qtest));
3443 QCOMPARE(q.lastError().databaseText(), QLatin1String("Raised Abort successfully"));
3444 }
3445
sqlite_real()3446 void tst_QSqlQuery::sqlite_real()
3447 {
3448 QFETCH(QString, dbName);
3449 QSqlDatabase db = QSqlDatabase::database(dbName);
3450 CHECK_DATABASE(db);
3451 const QString tableName(qTableName("sqliterealtype", __FILE__));
3452 tst_Databases::safeDropTable( db, tableName );
3453
3454 QSqlQuery q(db);
3455 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER, realVal REAL)"));
3456 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, realVal) VALUES (1, 2.3)"));
3457 QVERIFY_SQL(q, exec("SELECT realVal FROM " + tableName));
3458 QVERIFY(q.next());
3459 QCOMPARE(q.value(0).toDouble(), 2.3);
3460 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3461
3462 q.prepare("INSERT INTO " + tableName + " (id, realVal) VALUES (?, ?)");
3463 QVariant var((double)5.6);
3464 q.addBindValue(4);
3465 q.addBindValue(var);
3466 QVERIFY_SQL(q, exec());
3467
3468 QVERIFY_SQL(q, exec("SELECT realVal FROM " + tableName + " WHERE ID=4"));
3469 QVERIFY(q.next());
3470 QCOMPARE(q.value(0).toDouble(), 5.6);
3471 }
3472
aggregateFunctionTypes()3473 void tst_QSqlQuery::aggregateFunctionTypes()
3474 {
3475 QFETCH(QString, dbName);
3476 QSqlDatabase db = QSqlDatabase::database(dbName);
3477 CHECK_DATABASE(db);
3478 {
3479 const QString tableName(qTableName("numericFunctionsWithIntValues", __FILE__));
3480 tst_Databases::safeDropTable( db, tableName );
3481
3482 QSqlQuery q(db);
3483 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER)"));
3484
3485 // First test without any entries
3486 QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
3487 QVERIFY(q.next());
3488 QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
3489
3490 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1)"));
3491 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2)"));
3492
3493 QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
3494 QVERIFY(q.next());
3495 QCOMPARE(q.value(0).toInt(), 3);
3496 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3497
3498 QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
3499 QVERIFY(q.next());
3500 QCOMPARE(q.value(0).toDouble(), 1.5);
3501 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3502
3503 QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
3504 QVERIFY(q.next());
3505 QCOMPARE(q.value(0).toInt(), 2);
3506 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3507
3508 QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
3509 QVERIFY(q.next());
3510 QCOMPARE(q.value(0).toInt(), 1);
3511 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3512
3513 QVERIFY_SQL(q, exec("SELECT MAX(id) FROM " + tableName));
3514 QVERIFY(q.next());
3515 QCOMPARE(q.value(0).toInt(), 2);
3516 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3517 }
3518 {
3519 const QString tableName(qTableName("numericFunctionsWithDoubleValues", __FILE__));
3520 tst_Databases::safeDropTable( db, tableName );
3521
3522 QSqlQuery q(db);
3523 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id DOUBLE)"));
3524
3525 // First test without any entries
3526 QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
3527 QVERIFY(q.next());
3528 QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
3529
3530 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1.5)"));
3531 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2.5)"));
3532
3533 QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
3534 QVERIFY(q.next());
3535 QCOMPARE(q.value(0).toDouble(), 4.0);
3536 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3537
3538 QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
3539 QVERIFY(q.next());
3540 QCOMPARE(q.value(0).toDouble(), 2.0);
3541 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3542
3543 QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
3544 QVERIFY(q.next());
3545 QCOMPARE(q.value(0).toInt(), 2);
3546 QCOMPARE(q.record().field(0).type(), QVariant::Int);
3547
3548 QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
3549 QVERIFY(q.next());
3550 QCOMPARE(q.value(0).toDouble(), 1.5);
3551 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3552
3553 QVERIFY_SQL(q, exec("SELECT MAX(id) FROM " + tableName));
3554 QVERIFY(q.next());
3555 QCOMPARE(q.value(0).toDouble(), 2.5);
3556 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3557
3558 QVERIFY_SQL(q, exec("SELECT ROUND(id, 1) FROM " + tableName + " WHERE id=1.5"));
3559 QVERIFY(q.next());
3560 QCOMPARE(q.value(0).toDouble(), 1.5);
3561 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3562
3563 QVERIFY_SQL(q, exec("SELECT ROUND(id, 0) FROM " + tableName + " WHERE id=2.5"));
3564 QVERIFY(q.next());
3565 QCOMPARE(q.value(0).toDouble(), 3.0);
3566 QCOMPARE(q.record().field(0).type(), QVariant::Double);
3567 }
3568 {
3569 const QString tableName(qTableName("stringFunctions", __FILE__));
3570 tst_Databases::safeDropTable( db, tableName );
3571
3572 QSqlQuery q(db);
3573 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER, txt VARCHAR(50))"));
3574
3575 QVERIFY_SQL(q, exec("SELECT MAX(txt) FROM " + tableName));
3576 QVERIFY(q.next());
3577 QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
3578
3579 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, txt) VALUES (1, 'lower')"));
3580 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, txt) VALUES (2, 'upper')"));
3581
3582 QVERIFY_SQL(q, exec("SELECT MAX(txt) FROM " + tableName));
3583 QVERIFY(q.next());
3584 QCOMPARE(q.value(0).toString(), QLatin1String("upper"));
3585 QCOMPARE(q.record().field(0).type(), QVariant::String);
3586 }
3587 }
3588
3589 QTEST_MAIN( tst_QSqlQuery )
3590 #include "tst_qsqlquery.moc"
3591