1	/****************************************************************************
2	**
3	** Copyright (C) 2022 The Qt Company Ltd.
4	** Contact: https://www.qt.io/licensing/
5	**
6	** This file is part of the test suite of the Qt Toolkit.
7	**
8	** $QT_BEGIN_LICENSE:GPL-EXCEPT$
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 https://www.qt.io/terms-conditions. For further
15	** information use the contact form at https://www.qt.io/contact-us.
16	**
17	** GNU General Public License Usage
18	** Alternatively, this file may be used under the terms of the GNU
19	** General Public License version 3 as published by the Free Software
20	** Foundation with exceptions as appearing in the file LICENSE.GPL3-EXCEPT
21	** included in the packaging of this file. Please review the following
22	** information to ensure the GNU General Public License requirements will
23	** be met: https://www.gnu.org/licenses/gpl-3.0.html.
24	**
25	** $QT_END_LICENSE$
26	**
27	****************************************************************************/
28
29	#include <QtTest/QtTest>
30	#include <QtSql/QtSql>
31
32	#include <numeric>
33
34	#include "../qsqldatabase/tst_databases.h"
35
36	const QString qtest(qTableName(prefix: "qtest", __FILE__, db: QSqlDatabase()));
37
38	class tst_QSqlQuery : public QObject
39	{
40	Q_OBJECT
41
42	public:
43	tst_QSqlQuery();
44	virtual ~tst_QSqlQuery();
45
46	public slots:
47	void initTestCase();
48	void cleanupTestCase();
49	void init();
50	void cleanup();
51
52	private slots:
53	void value_data() { generic_data(); }
54	void value();
55	void isValid_data() { generic_data(); }
56	void isValid();
57	void isActive_data() { generic_data(); }
58	void isActive();
59	void isSelect_data() { generic_data(); }
60	void isSelect();
61	void numRowsAffected_data() { generic_data(); }
62	void numRowsAffected();
63	void size_data() { generic_data(); }
64	void size();
65	void isNull_data() { generic_data(); }
66	void isNull();
67	void query_exec_data() { generic_data(); }
68	void query_exec();
69	void execErrorRecovery_data() { generic_data(); }
70	void execErrorRecovery();
71	void prematureExec_data() { generic_data(); }
72	void prematureExec();
73	void first_data() { generic_data(); }
74	void first();
75	void next_data() { generic_data(); }
76	void next();
77	void prev_data() { generic_data(); }
78	void prev();
79	void last_data() { generic_data(); }
80	void last();
81	void seek_data() { generic_data(); }
82	void seek();
83	void transaction_data() { generic_data(); }
84	void transaction();
85	void record_data() { generic_data(); }
86	void record();
87	void record_sqlite_data() { generic_data(engine: "QSQLITE"); }
88	void record_sqlite();
89	void finish_data() { generic_data(); }
90	void finish();
91	void sqlite_finish_data() { generic_data(engine: "QSQLITE"); }
92	void sqlite_finish();
93	void nextResult_data() { generic_data(); }
94	void nextResult();
95
96	// forwardOnly mode need special treatment
97	void forwardOnly_data() { generic_data(); }
98	void forwardOnly();
99	void forwardOnlyMultipleResultSet_data() { generic_data(); }
100	void forwardOnlyMultipleResultSet();
101	void psql_forwardOnlyQueryResultsLost_data() { generic_data(engine: "QPSQL"); }
102	void psql_forwardOnlyQueryResultsLost();
103
104	// bug specific tests
105	void tds_bitField_data() { generic_data(engine: "QTDS"); }
106	void tds_bitField();
107	void oci_nullBlob_data() { generic_data(engine: "QOCI"); }
108	void oci_nullBlob();
109	void blob_data() { generic_data(); }
110	void blob();
111	void oci_rawField_data() { generic_data(engine: "QOCI"); }
112	void oci_rawField();
113	void precision_data() { generic_data(); }
114	void precision();
115	void nullResult_data() { generic_data(); }
116	void nullResult();
117	void joins_data() { generic_data(); }
118	void joins();
119	void outValues_data() { generic_data(); }
120	void outValues();
121	void char1Select_data() { generic_data(); }
122	void char1Select();
123	void char1SelectUnicode_data() { generic_data(); }
124	void char1SelectUnicode();
125	void synonyms_data() { generic_data(); }
126	void synonyms();
127	void oraOutValues_data() { generic_data(engine: "QOCI"); }
128	void oraOutValues();
129	void mysql_outValues_data() { generic_data(engine: "QMYSQL"); }
130	void mysql_outValues();
131	void oraClob_data() { generic_data(engine: "QOCI"); }
132	void oraClob();
133	void oraClobBatch_data() { generic_data(engine: "QOCI"); }
134	void oraClobBatch();
135	void oraLong_data() { generic_data(engine: "QOCI"); }
136	void oraLong();
137	void oraOCINumber_data() { generic_data(engine: "QOCI"); }
138	void oraOCINumber();
139	void outValuesDB2_data() { generic_data(engine: "QDB2"); }
140	void outValuesDB2();
141	void storedProceduresIBase_data() {generic_data(engine: "QIBASE"); }
142	void storedProceduresIBase();
143	void oraRowId_data() { generic_data(engine: "QOCI"); }
144	void oraRowId();
145	void prepare_bind_exec_data() { generic_data(); }
146	void prepare_bind_exec();
147	void prepared_select_data() { generic_data(); }
148	void prepared_select();
149	void sqlServerLongStrings_data() { generic_data(); }
150	void sqlServerLongStrings();
151	void invalidQuery_data() { generic_data(); }
152	void invalidQuery();
153	void batchExec_data() { generic_data(); }
154	void batchExec();
155	void QTBUG_43874_data() { generic_data(); }
156	void QTBUG_43874();
157	void oraArrayBind_data() { generic_data(engine: "QOCI"); }
158	void oraArrayBind();
159	void lastInsertId_data() { generic_data(); }
160	void lastInsertId();
161	void lastQuery_data() { generic_data(); }
162	void lastQuery();
163	void lastQueryTwoQueries_data() { generic_data(); }
164	void lastQueryTwoQueries();
165	void bindBool_data() { generic_data(); }
166	void bindBool();
167	void psql_bindWithDoubleColonCastOperator_data() { generic_data(engine: "QPSQL"); }
168	void psql_bindWithDoubleColonCastOperator();
169	void psql_specialFloatValues_data() { generic_data(engine: "QPSQL"); }
170	void psql_specialFloatValues();
171	void queryOnInvalidDatabase_data() { generic_data(); }
172	void queryOnInvalidDatabase();
173	void createQueryOnClosedDatabase_data() { generic_data(); }
174	void createQueryOnClosedDatabase();
175	void seekForwardOnlyQuery_data() { generic_data(); }
176	void seekForwardOnlyQuery();
177	void reExecutePreparedForwardOnlyQuery_data() { generic_data(); }
178	void reExecutePreparedForwardOnlyQuery();
179	void blobsPreparedQuery_data() { generic_data(); }
180	void blobsPreparedQuery();
181	void emptyTableNavigate_data() { generic_data(); }
182	void emptyTableNavigate();
183	void timeStampParsing_data() { generic_data(); }
184	void timeStampParsing();
185	void sqliteVirtualTable_data() { generic_data(engine: "QSQLITE"); }
186	void sqliteVirtualTable();
187	void mysql_timeType_data() { generic_data(engine: "QMYSQL"); }
188	void mysql_timeType();
189	void ibase_executeBlock_data() { generic_data(engine: "QIBASE"); }
190	void ibase_executeBlock();
191
192	void task_217003_data() { generic_data(); }
193	void task_217003();
194
195	void task_250026_data() { generic_data(engine: "QODBC"); }
196	void task_250026();
197	void crashQueryOnCloseDatabase();
198
199	void task_233829_data() { generic_data(engine: "QPSQL"); }
200	void task_233829();
201
202	void QTBUG_12477_data() { generic_data(engine: "QPSQL"); }
203	void QTBUG_12477();
204
205	void sqlServerReturn0_data() { generic_data(); }
206	void sqlServerReturn0();
207
208	void QTBUG_551_data() { generic_data(engine: "QOCI"); }
209	void QTBUG_551();
210
211	void QTBUG_5251_data() { generic_data(engine: "QPSQL"); }
212	void QTBUG_5251();
213	void QTBUG_6421_data() { generic_data(engine: "QOCI"); }
214	void QTBUG_6421();
215	void QTBUG_6618_data() { generic_data(engine: "QODBC"); }
216	void QTBUG_6618();
217	void QTBUG_6852_data() { generic_data(engine: "QMYSQL"); }
218	void QTBUG_6852();
219	void QTBUG_5765_data() { generic_data(engine: "QMYSQL"); }
220	void QTBUG_5765();
221	void QTBUG_12186_data() { generic_data(engine: "QSQLITE"); }
222	void QTBUG_12186();
223	void QTBUG_14132_data() { generic_data(engine: "QOCI"); }
224	void QTBUG_14132();
225	void QTBUG_18435_data() { generic_data(engine: "QODBC"); }
226	void QTBUG_18435();
227	void QTBUG_21884_data() { generic_data(engine: "QSQLITE"); }
228	void QTBUG_21884();
229	void QTBUG_16967_data() { generic_data(engine: "QSQLITE"); }
230	void QTBUG_16967(); //clean close
231	void QTBUG_23895_data() { generic_data(engine: "QSQLITE"); }
232	void QTBUG_23895(); //sqlite boolean type
233	void QTBUG_14904_data() { generic_data(engine: "QSQLITE"); }
234	void QTBUG_14904();
235
236	void QTBUG_2192_data() { generic_data(); }
237	void QTBUG_2192();
238
239	void QTBUG_36211_data() { generic_data(engine: "QPSQL"); }
240	void QTBUG_36211();
241
242	void QTBUG_53969_data() { generic_data(engine: "QMYSQL"); }
243	void QTBUG_53969();
244
245	void gisPointDatatype_data() { generic_data(engine: "QMYSQL"); }
246	void gisPointDatatype();
247
248	void sqlite_constraint_data() { generic_data(engine: "QSQLITE"); }
249	void sqlite_constraint();
250
251	void sqlite_real_data() { generic_data(engine: "QSQLITE"); }
252	void sqlite_real();
253
254	void aggregateFunctionTypes_data() { generic_data(); }
255	void aggregateFunctionTypes();
256
257	void integralTypesMysql_data() { generic_data(engine: "QMYSQL"); }
258	void integralTypesMysql();
259
260	void QTBUG_57138_data() { generic_data(engine: "QSQLITE"); }
261	void QTBUG_57138();
262
263	void QTBUG_73286_data() { generic_data(engine: "QODBC"); }
264	void QTBUG_73286();
265
266	void dateTime_data();
267	void dateTime();
268
269	void ibaseArray_data() { generic_data(engine: "QIBASE"); }
270	void ibaseArray();
271
272	private:
273	// returns all database connections
274	void generic_data(const QString &engine=QString());
275	void dropTestTables( QSqlDatabase db );
276	void createTestTables( QSqlDatabase db );
277	void populateTestTables( QSqlDatabase db );
278
279	tst_Databases dbs;
280	};
281
282	tst_QSqlQuery::tst_QSqlQuery()
283	{
284	}
285
286	tst_QSqlQuery::~tst_QSqlQuery()
287	{
288	}
289
290	void tst_QSqlQuery::initTestCase()
291	{
292	QVERIFY(dbs.open());
293
294	for ( QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it ) {
295	QSqlDatabase db = QSqlDatabase::database(connectionName: ( *it ) );
296	CHECK_DATABASE( db );
297	dropTestTables( db ); //in case of leftovers
298	createTestTables( db );
299	populateTestTables( db );
300	}
301	}
302
303	void tst_QSqlQuery::cleanupTestCase()
304	{
305	for ( QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it ) {
306	QSqlDatabase db = QSqlDatabase::database(connectionName: ( *it ) );
307	CHECK_DATABASE( db );
308	dropTestTables( db );
309	}
310
311	dbs.close();
312	}
313
314	void tst_QSqlQuery::init()
315	{
316	}
317
318	void tst_QSqlQuery::cleanup()
319	{
320	if (QTest::currentTestFunction() == QLatin1String("crashQueryOnCloseDatabase"))
321	return;
322	QFETCH( QString, dbName );
323	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
324	CHECK_DATABASE( db );
325	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
326	if ( QTest::currentTestFunction() == QLatin1String( "numRowsAffected" )
327	|| QTest::currentTestFunction() == QLatin1String( "transactions" )
328	|| QTest::currentTestFunction() == QLatin1String( "size" )
329	|| QTest::currentTestFunction() == QLatin1String( "isActive" )
330	|| QTest::currentTestFunction() == QLatin1String( "lastInsertId" ) ) {
331	populateTestTables( db );
332	}
333
334	if (QTest::currentTestFailed() && (dbType == QSqlDriver::Oracle || db.driverName().startsWith(s: "QODBC"))) {
335	//since Oracle ODBC totally craps out on error, we init again
336	db.close();
337	db.open();
338	}
339	}
340
341	void tst_QSqlQuery::generic_data(const QString& engine)
342	{
343	if ( dbs.fillTestTable(driverPrefix: engine) == 0 ) {
344	if(engine.isEmpty())
345	QSKIP( "No database drivers are available in this Qt configuration");
346	else
347	QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit());
348	}
349	}
350
351	void tst_QSqlQuery::dropTestTables( QSqlDatabase db )
352	{
353	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
354	QStringList tablenames;
355	// drop all the table in case a testcase failed
356	tablenames << qtest
357	<< qTableName(prefix: "qtest_null", __FILE__, db)
358	<< qTableName(prefix: "qtest_blob", __FILE__, db)
359	<< qTableName(prefix: "qtest_bittest", __FILE__, db)
360	<< qTableName(prefix: "qtest_nullblob", __FILE__, db)
361	<< qTableName(prefix: "qtest_rawtest", __FILE__, db)
362	<< qTableName(prefix: "qtest_precision", __FILE__, db)
363	<< qTableName(prefix: "qtest_prepare", __FILE__, db)
364	<< qTableName(prefix: "qtestj1", __FILE__, db)
365	<< qTableName(prefix: "qtestj2", __FILE__, db)
366	<< qTableName(prefix: "char1Select", __FILE__, db)
367	<< qTableName(prefix: "char1SU", __FILE__, db)
368	<< qTableName(prefix: "qxmltest", __FILE__, db)
369	<< qTableName(prefix: "qtest_exerr", __FILE__, db)
370	<< qTableName(prefix: "qtest_empty", __FILE__, db)
371	<< qTableName(prefix: "clobby", __FILE__, db)
372	<< qTableName(prefix: "bindtest", __FILE__, db)
373	<< qTableName(prefix: "more_results", __FILE__, db)
374	<< qTableName(prefix: "blobstest", __FILE__, db)
375	<< qTableName(prefix: "oraRowId", __FILE__, db)
376	<< qTableName(prefix: "bug43874", __FILE__, db)
377	<< qTableName(prefix: "bug6421", __FILE__, db).toUpper()
378	<< qTableName(prefix: "bug5765", __FILE__, db)
379	<< qTableName(prefix: "bug6852", __FILE__, db)
380	<< qTableName(prefix: "bug21884", __FILE__, db)
381	<< qTableName(prefix: "bug23895", __FILE__, db)
382	<< qTableName(prefix: "qtest_lockedtable", __FILE__, db)
383	<< qTableName(prefix: "Planet", __FILE__, db)
384	<< qTableName(prefix: "task_250026", __FILE__, db)
385	<< qTableName(prefix: "task_234422", __FILE__, db)
386	<< qTableName(prefix: "test141895", __FILE__, db)
387	<< qTableName(prefix: "qtest_oraOCINumber", __FILE__, db)
388	<< qTableName(prefix: "bug2192", __FILE__, db)
389	<< qTableName(prefix: "tst_record", __FILE__, db);
390
391	if (dbType == QSqlDriver::PostgreSQL)
392	tablenames << qTableName(prefix: "task_233829", __FILE__, db);
393
394	if (dbType == QSqlDriver::SQLite)
395	tablenames << qTableName(prefix: "record_sqlite", __FILE__, db);
396
397	if (dbType == QSqlDriver::MSSqlServer || dbType == QSqlDriver::Oracle)
398	tablenames << qTableName(prefix: "qtest_longstr", __FILE__, db);
399
400	if (dbType == QSqlDriver::MSSqlServer)
401	db.exec(query: "DROP PROCEDURE " + qTableName(prefix: "test141895_proc", __FILE__, db));
402
403	if (dbType == QSqlDriver::MySqlServer)
404	db.exec(query: "DROP PROCEDURE IF EXISTS "+ qTableName(prefix: "bug6852_proc", __FILE__, db));
405
406	tst_Databases::safeDropTables( db, tableNames: tablenames );
407
408	if (dbType == QSqlDriver::Oracle) {
409	QSqlQuery q( db );
410	q.exec(query: "DROP PACKAGE " + qTableName(prefix: "pkg", __FILE__, db));
411	}
412	}
413
414	void tst_QSqlQuery::createTestTables( QSqlDatabase db )
415	{
416	QSqlQuery q( db );
417	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
418	if (dbType == QSqlDriver::MySqlServer)
419	// ### stupid workaround until we find a way to hardcode this
420	// in the MySQL server startup script
421	q.exec( query: "set table_type=innodb" );
422	else if (dbType == QSqlDriver::PostgreSQL)
423	QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
424
425	if (dbType == QSqlDriver::PostgreSQL)
426	QVERIFY_SQL( q, exec( "create table " + qtest + " (id serial NOT NULL, t_varchar varchar(20), t_char char(20), primary key(id)) WITH OIDS" ) );
427	else
428	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))" ) );
429
430	if (dbType == QSqlDriver::MSSqlServer || dbType == QSqlDriver::Sybase)
431	QVERIFY_SQL(q, exec("create table " + qTableName("qtest_null", __FILE__, db) + " (id int null, t_varchar varchar(20) null)"));
432	else
433	QVERIFY_SQL(q, exec("create table " + qTableName("qtest_null", __FILE__, db) + " (id int, t_varchar varchar(20))"));
434	}
435
436	void tst_QSqlQuery::populateTestTables( QSqlDatabase db )
437	{
438	QSqlQuery q( db );
439	const QString qtest_null(qTableName( prefix: "qtest_null", __FILE__, db));
440	q.exec( query: "delete from " + qtest );
441	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (1, 'VarChar1', 'Char1')" ) );
442	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (2, 'VarChar2', 'Char2')" ) );
443	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (3, 'VarChar3', 'Char3')" ) );
444	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (4, 'VarChar4', 'Char4')" ) );
445	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (5, 'VarChar5', 'Char5')" ) );
446
447	q.exec( query: "delete from " + qtest_null );
448	QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (0, NULL)" ) );
449	QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (1, 'n')" ) );
450	QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (2, 'i')" ) );
451	QVERIFY_SQL( q, exec( "insert into " + qtest_null + " values (3, NULL)" ) );
452	}
453
454	// There were problems with char fields of size 1
455	void tst_QSqlQuery::char1Select()
456	{
457	QFETCH( QString, dbName );
458	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
459	CHECK_DATABASE( db );
460
461	{
462	QSqlQuery q( db );
463	const QString tbl = qTableName(prefix: "char1Select", __FILE__, db);
464	q.exec( query: "drop table " + tbl);
465	QVERIFY_SQL(q, exec("create table " + tbl + " (id char(1))"));
466	QVERIFY_SQL(q, exec("insert into " + tbl + " values ('a')"));
467	QVERIFY_SQL(q, exec("select * from " + tbl));
468	QVERIFY( q.next() );
469	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
470	if (dbType == QSqlDriver::Interbase)
471	QCOMPARE( q.value( 0 ).toString().left( 1 ), QString( "a" ) );
472	else
473	QCOMPARE( q.value( 0 ).toString(), QString( "a" ) );
474
475	QVERIFY( !q.next() );
476	}
477	}
478
479	void tst_QSqlQuery::char1SelectUnicode()
480	{
481	QFETCH( QString, dbName );
482	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
483	CHECK_DATABASE( db );
484	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
485	if (dbType == QSqlDriver::DB2)
486	QSKIP("Needs someone with more Unicode knowledge than I have to fix");
487
488	if ( db.driver()->hasFeature( f: QSqlDriver::Unicode ) ) {
489	QString uniStr( QChar(0x0915) ); // DEVANAGARI LETTER KA
490	QSqlQuery q( db );
491	QString createQuery;
492	const QString char1SelectUnicode(qTableName(prefix: "char1SU", __FILE__, db));
493
494	if (dbType == QSqlDriver::MSSqlServer)
495	createQuery = "create table " + char1SelectUnicode + "(id nchar(1))";
496	else if (dbType == QSqlDriver::DB2 || dbType == QSqlDriver::Oracle || dbType == QSqlDriver::PostgreSQL)
497	createQuery = "create table " + char1SelectUnicode + " (id char(3))";
498	else if (dbType == QSqlDriver::Interbase)
499	createQuery = "create table " + char1SelectUnicode +
500	" (id char(1) character set unicode_fss)";
501	else if (dbType == QSqlDriver::MySqlServer)
502	createQuery = "create table " + char1SelectUnicode + " (id char(1)) "
503	"default character set 'utf8'";
504	else
505	createQuery = "create table " + char1SelectUnicode + " (id char(1))";
506
507	QVERIFY_SQL( q, exec( createQuery ) );
508
509	QVERIFY_SQL( q, prepare( "insert into " + char1SelectUnicode + " values(?)" ) );
510
511	q.bindValue( pos: 0, val: uniStr );
512
513	QVERIFY_SQL( q, exec() );
514
515	QVERIFY_SQL( q, exec( "select * from " + char1SelectUnicode ) );
516
517	QVERIFY( q.next() );
518
519	if ( !q.value( i: 0 ).toString().isEmpty() )
520	QCOMPARE( q.value( 0 ).toString()[ 0 ].unicode(), uniStr[0].unicode() );
521
522	QCOMPARE( q.value( 0 ).toString().trimmed(), uniStr );
523
524	QVERIFY( !q.next() );
525	}
526	else
527	QSKIP( "Database not unicode capable");
528	}
529
530	void tst_QSqlQuery::oraRowId()
531	{
532	QFETCH( QString, dbName );
533	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
534	CHECK_DATABASE( db );
535	const QString oraRowId(qTableName(prefix: "oraRowId", __FILE__, db));
536
537	QSqlQuery q( db );
538	QVERIFY_SQL( q, exec( "select rowid from " + qtest ) );
539	QVERIFY( q.next() );
540	QCOMPARE( q.value( 0 ).type(), QVariant::String );
541	QVERIFY( !q.value( 0 ).toString().isEmpty() );
542
543	QVERIFY_SQL( q, exec( "create table " + oraRowId + " (id char(1))" ) );
544
545	QVERIFY_SQL( q, exec( "insert into " + oraRowId + " values('a')" ) );
546	QVariant v1 = q.lastInsertId();
547	QVERIFY( v1.isValid() );
548
549	QVERIFY_SQL( q, exec( "insert into " + oraRowId + " values('b')" ) );
550	QVariant v2 = q.lastInsertId();
551	QVERIFY( v2.isValid() );
552
553	QVERIFY_SQL( q, prepare( "select * from " + oraRowId + " where rowid = ?" ) );
554	q.addBindValue( val: v1 );
555	QVERIFY_SQL( q, exec() );
556	QVERIFY( q.next() );
557	QCOMPARE( q.value( 0 ).toString(), QString( "a" ) );
558
559	q.addBindValue( val: v2 );
560	QVERIFY_SQL( q, exec() );
561	QVERIFY( q.next() );
562	QCOMPARE( q.value( 0 ).toString(), QString( "b" ) );
563	}
564
565	void tst_QSqlQuery::mysql_outValues()
566	{
567	QFETCH( QString, dbName );
568	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
569	CHECK_DATABASE( db );
570	const QString hello(qTableName(prefix: "hello", __FILE__, db)), qtestproc(qTableName(prefix: "qtestproc", __FILE__, db));
571
572	QSqlQuery q( db );
573
574	q.exec( query: "drop function " + hello );
575
576	QVERIFY_SQL(q, exec("create function " + hello + " (s char(20)) returns varchar(50) READS SQL DATA return concat('Hello ', s)"));
577
578	QVERIFY_SQL( q, exec( "select " + hello + "('world')" ) );
579	QVERIFY_SQL( q, next() );
580
581	QCOMPARE( q.value( 0 ).toString(), QString( "Hello world" ) );
582
583	QVERIFY_SQL( q, prepare( "select " + hello + "('harald')" ) );
584	QVERIFY_SQL( q, exec() );
585	QVERIFY_SQL( q, next() );
586
587	QCOMPARE( q.value( 0 ).toString(), QString( "Hello harald" ) );
588
589	QVERIFY_SQL( q, exec( "drop function " + hello ) );
590
591	q.exec( query: "drop procedure " + qtestproc );
592
593	QVERIFY_SQL( q, exec( "create procedure " + qtestproc + " () "
594	"BEGIN select * from " + qtest + " order by id; END" ) );
595	QVERIFY_SQL( q, exec( "call " + qtestproc + "()" ) );
596	QVERIFY_SQL( q, next() );
597	QCOMPARE( q.value( 1 ).toString(), QString( "VarChar1" ) );
598
599	QVERIFY_SQL( q, exec( "drop procedure " + qtestproc ) );
600
601	QVERIFY_SQL( q, exec( "create procedure " + qtestproc + " (OUT param1 INT) "
602	"BEGIN set param1 = 42; END" ) );
603
604	QVERIFY_SQL( q, exec( "call " + qtestproc + " (@out)" ) );
605	QVERIFY_SQL( q, exec( "select @out" ) );
606	QCOMPARE( q.record().fieldName( 0 ), QString( "@out" ) );
607	QVERIFY_SQL( q, next() );
608	QCOMPARE( q.value( 0 ).toInt(), 42 );
609
610	QVERIFY_SQL( q, exec( "drop procedure " + qtestproc ) );
611	}
612
613	void tst_QSqlQuery::bindBool()
614	{
615	// QTBUG-27763: bool value got converted to int 127 by mysql driver because sizeof(bool) < sizeof(int).
616	// The problem was the way the bool value from the application was handled. For our purposes here, it
617	// doesn't matter whether the column type is BOOLEAN or INT. All DBMSs have INT, and this usually
618	// works for this test. Postresql is an exception because its INT type does not accept BOOLEAN
619	// values and its BOOLEAN columns do not accept INT values.
620	QFETCH( QString, dbName );
621	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
622	CHECK_DATABASE( db );
623	QSqlQuery q(db);
624	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
625	const QString tableName(qTableName(prefix: "bindBool", __FILE__, db));
626
627	q.exec(query: "DROP TABLE " + tableName);
628	QString colType = dbType == QSqlDriver::PostgreSQL ? QLatin1String("BOOLEAN") : QLatin1String("INT");
629	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INT, flag " + colType + " NOT NULL, PRIMARY KEY(id))"));
630
631	for (int i = 0; i < 2; ++i) {
632	bool flag = i;
633	q.prepare(query: "INSERT INTO " + tableName + " (id, flag) VALUES(:id, :flag)");
634	q.bindValue(placeholder: ":id", val: i);
635	q.bindValue(placeholder: ":flag", val: flag);
636	QVERIFY_SQL(q, exec());
637	}
638
639	QVERIFY_SQL(q, exec("SELECT id, flag FROM " + tableName + " ORDER BY id"));
640	for (int i = 0; i < 2; ++i) {
641	bool flag = i;
642	QVERIFY_SQL(q, next());
643	QCOMPARE(q.value(0).toInt(), i);
644	QCOMPARE(q.value(1).toBool(), flag);
645	}
646	QVERIFY_SQL(q, prepare("SELECT flag FROM " + tableName + " WHERE flag = :filter"));
647	const bool filter = true;
648	q.bindValue(placeholder: ":filter", val: filter);
649	QVERIFY_SQL(q, exec());
650	QVERIFY_SQL(q, next());
651	QCOMPARE(q.value(0).toBool(), filter);
652	QFAIL_SQL(q, next());
653	QVERIFY_SQL(q, exec("DROP TABLE " + tableName));
654	}
655
656	void tst_QSqlQuery::oraOutValues()
657	{
658	QFETCH( QString, dbName );
659	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
660	CHECK_DATABASE( db );
661	const QString tst_outValues(qTableName(prefix: "tst_outValues", __FILE__, db));
662
663	if ( !db.driver()->hasFeature( f: QSqlDriver::PreparedQueries ) )
664	QSKIP( "Test requires prepared query support");
665
666	QSqlQuery q( db );
667
668	q.setForwardOnly( true );
669
670	/*** outvalue int ***/
671	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x out int) is\n"
672	"begin\n"
673	" x := 42;\n"
674	"end;\n" ) );
675	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
676	q.addBindValue( val: 0, type: QSql::Out );
677	QVERIFY_SQL( q, exec() );
678	QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
679
680	// bind a null value, make sure the OCI driver resets the null flag
681	q.addBindValue( val: QVariant( QVariant::Int ), type: QSql::Out );
682	QVERIFY_SQL( q, exec() );
683	QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
684	QVERIFY( !q.boundValue( 0 ).isNull() );
685
686	/*** outvalue varchar ***/
687	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x out varchar) is\n"
688	"begin\n"
689	" x := 'blah';\n"
690	"end;\n" ) );
691	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
692	QString s1( "12345" );
693	s1.reserve( asize: 512 );
694	q.addBindValue( val: s1, type: QSql::Out );
695	QVERIFY_SQL( q, exec() );
696	QCOMPARE( q.boundValue( 0 ).toString(), QString( "blah" ) );
697
698	/*** in/outvalue numeric ***/
699	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out numeric) is\n"
700	"begin\n"
701	" x := x + 10;\n"
702	"end;\n" ) );
703	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
704	q.addBindValue( val: 10, type: QSql::Out );
705	QVERIFY_SQL( q, exec() );
706	QCOMPARE( q.boundValue( 0 ).toInt(), 20 );
707
708	/*** in/outvalue varchar ***/
709	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out varchar) is\n"
710	"begin\n"
711	" x := 'homer';\n"
712	"end;\n" ) );
713	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
714	q.addBindValue( val: QString( "maggy" ), type: QSql::Out );
715	QVERIFY_SQL( q, exec() );
716	QCOMPARE( q.boundValue( 0 ).toString(), QString( "homer" ) );
717
718	/*** in/outvalue varchar ***/
719	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out varchar) is\n"
720	"begin\n"
721	" x := NULL;\n"
722	"end;\n" ) );
723	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
724	q.addBindValue( val: QString( "maggy" ), type: QSql::Out );
725	QVERIFY_SQL( q, exec() );
726	QVERIFY( q.boundValue( 0 ).isNull() );
727
728	/*** in/outvalue int ***/
729	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in out int) is\n"
730	"begin\n"
731	" x := NULL;\n"
732	"end;\n" ) );
733	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
734	q.addBindValue( val: 42, type: QSql::Out );
735	QVERIFY_SQL( q, exec() );
736	QVERIFY( q.boundValue( 0 ).isNull() );
737
738	/*** in/outvalue varchar ***/
739	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x in varchar, y out varchar) is\n"
740	"begin\n"
741	" y := x||'bubulalakikikokololo';\n"
742	"end;\n" ) );
743	QVERIFY( q.prepare( "call " + tst_outValues + "(?, ?)" ) );
744	q.addBindValue( val: QString( "fifi" ), type: QSql::In );
745	QString out;
746	out.reserve( asize: 50 );
747	q.addBindValue( val: out, type: QSql::Out );
748	QVERIFY_SQL( q, exec() );
749	QCOMPARE( q.boundValue( 1 ).toString(), QString( "fifibubulalakikikokololo" ) );
750
751	/*** in/outvalue date ***/
752	QVERIFY_SQL(q, exec("create or replace procedure " + tst_outValues + "(x in date, y out date) is\n"
753	"begin\n"
754	" y := x;\n"
755	"end;\n"));
756	QVERIFY(q.prepare("call " + tst_outValues + "(?, ?)"));
757	const QDate date = QDate::currentDate();
758	q.addBindValue(val: date, type: QSql::In);
759	q.addBindValue(val: QVariant(QDate()), type: QSql::Out);
760	QVERIFY_SQL(q, exec());
761	QCOMPARE(q.boundValue(1).toDate(), date);
762
763	/*** in/outvalue timestamp ***/
764	QVERIFY_SQL(q, exec("create or replace procedure " + tst_outValues + "(x in timestamp, y out timestamp) is\n"
765	"begin\n"
766	" y := x;\n"
767	"end;\n"));
768	QVERIFY(q.prepare("call " + tst_outValues + "(?, ?)"));
769	const QDateTime dt = QDateTime::currentDateTime();
770	q.addBindValue(val: dt, type: QSql::In);
771	q.addBindValue(val: QVariant(QVariant::DateTime), type: QSql::Out);
772	QVERIFY_SQL(q, exec());
773	QCOMPARE(q.boundValue(1).toDateTime(), dt);
774	}
775
776	void tst_QSqlQuery::oraClob()
777	{
778	QFETCH( QString, dbName );
779	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
780	CHECK_DATABASE( db );
781	const QString clobby(qTableName(prefix: "clobby", __FILE__, db));
782
783	QSqlQuery q( db );
784
785	// simple short string
786	QVERIFY_SQL( q, exec( "create table " + clobby + "(id int primary key, cl clob, bl blob)" ) );
787	QVERIFY_SQL( q, prepare( "insert into " + clobby + " (id, cl, bl) values(?, ?, ?)" ) );
788	q.addBindValue( val: 1 );
789	q.addBindValue( val: "bubu" );
790	q.addBindValue( val: QByteArray("bubu") );
791	QVERIFY_SQL( q, exec() );
792
793	QVERIFY_SQL( q, exec( "select bl, cl from " + clobby + " where id = 1" ) );
794	QVERIFY( q.next() );
795	QCOMPARE( q.value( 0 ).toString(), QString( "bubu" ) );
796	QCOMPARE( q.value( 1 ).toString(), QString( "bubu" ) );
797
798	// simple short string with binding
799	QVERIFY_SQL( q, prepare( "insert into " + clobby + " (id, cl, bl) values(?, ?, ?)" ) );
800	q.addBindValue( val: 2 );
801	q.addBindValue( val: "lala", type: QSql::Binary );
802	q.addBindValue( val: QByteArray("lala"), type: QSql::Binary );
803	QVERIFY_SQL( q, exec() );
804
805	QVERIFY_SQL( q, exec( "select bl, cl from " + clobby + " where id = 2" ) );
806	QVERIFY( q.next() );
807	QCOMPARE( q.value( 0 ).toString(), QString( "lala" ) );
808	QCOMPARE( q.value( 1 ).toString(), QString( "lala" ) );
809
810	// loooong string
811	QString loong;
812	loong.fill( c: QLatin1Char( 'A' ), size: 25000 );
813	QVERIFY_SQL( q, prepare( "insert into " + clobby + " (id, cl, bl) values(?, ?, ?)" ) );
814	q.addBindValue( val: 3 );
815	q.addBindValue( val: loong, type: QSql::Binary );
816	q.addBindValue( val: loong.toLatin1(), type: QSql::Binary );
817	QVERIFY_SQL( q, exec() );
818
819	QVERIFY_SQL( q, exec( "select bl, cl from " + clobby + " where id = 3" ) );
820	QVERIFY( q.next() );
821	QCOMPARE( q.value( 0 ).toString().count(), loong.count() );
822	QVERIFY( q.value( 0 ).toString() == loong );
823	QCOMPARE( q.value( 1 ).toByteArray().count(), loong.toLatin1().count() );
824	QVERIFY( q.value( 1 ).toByteArray() == loong.toLatin1() );
825	}
826
827	void tst_QSqlQuery::oraClobBatch()
828	{
829	QFETCH(QString, dbName);
830	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
831	CHECK_DATABASE(db);
832	const QString clobBatch(qTableName(prefix: "clobBatch", __FILE__, db));
833	tst_Databases::safeDropTables(db, tableNames: { clobBatch });
834	QSqlQuery q(db);
835	QVERIFY_SQL(q, exec("create table " + clobBatch + "(cl clob)"));
836
837	const QString longString(USHRT_MAX + 1, QLatin1Char('A'));
838	QVERIFY_SQL(q, prepare("insert into " + clobBatch + " (cl) values(:cl)"));
839	const QVariantList vars = { longString };
840	q.addBindValue(val: vars);
841	QVERIFY_SQL(q, execBatch());
842
843	QVERIFY_SQL(q, exec("select cl from " + clobBatch));
844	QVERIFY(q.next());
845	QCOMPARE(q.value(0).toString().count(), longString.size());
846	QVERIFY(q.value(0).toString() == longString);
847	}
848
849	void tst_QSqlQuery::storedProceduresIBase()
850	{
851	QFETCH( QString, dbName );
852	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
853	CHECK_DATABASE( db );
854
855	QSqlQuery q( db );
856	q.exec(query: "drop procedure " + qTableName(prefix: "TESTPROC", __FILE__, db));
857
858	QVERIFY_SQL(q, exec("create procedure " + qTableName("TESTPROC", __FILE__, db) +
859	" RETURNS (x integer, y varchar(20)) "
860	"AS BEGIN "
861	" x = 42; "
862	" y = 'Hello Anders'; "
863	"END" ) );
864
865	QVERIFY_SQL(q, prepare("execute procedure " + qTableName("TestProc", __FILE__, db)));
866	QVERIFY_SQL( q, exec() );
867
868	// check for a valid result set
869	QSqlRecord rec = q.record();
870	QCOMPARE( rec.count(), 2 );
871	QCOMPARE( rec.fieldName( 0 ).toUpper(), QString( "X" ) );
872	QCOMPARE( rec.fieldName( 1 ).toUpper(), QString( "Y" ) );
873
874	// the first next shall suceed
875	QVERIFY_SQL( q, next() );
876	QCOMPARE( q.value( 0 ).toInt(), 42 );
877	QCOMPARE( q.value( 1 ).toString(), QString( "Hello Anders" ) );
878
879	// the second next shall fail
880	QVERIFY( !q.next() );
881
882	q.exec(query: "drop procedure " + qTableName(prefix: "TestProc", __FILE__, db));
883	}
884
885	void tst_QSqlQuery::outValuesDB2()
886	{
887	QFETCH( QString, dbName );
888	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
889	CHECK_DATABASE( db );
890
891	if ( !db.driver()->hasFeature( f: QSqlDriver::PreparedQueries ) )
892	QSKIP( "Test requires prepared query support");
893
894	QSqlQuery q( db );
895
896	q.setForwardOnly( true );
897
898	q.exec(query: "drop procedure " + qTableName(prefix: "tst_outValues", __FILE__, db)); //non-fatal
899	QVERIFY_SQL( q, exec( "CREATE PROCEDURE " + qTableName("tst_outValues", __FILE__, db) +
900	" (OUT x int, OUT x2 double, OUT x3 char(20))\n"
901	"LANGUAGE SQL\n"
902	"P1: BEGIN\n"
903	" SET x = 42;\n"
904	" SET x2 = 4.2;\n"
905	" SET x3 = 'Homer';\n"
906	"END P1" ) );
907
908	QVERIFY_SQL(q, prepare("call " + qTableName("tst_outValues", __FILE__, db) + "(?, ?, ?)"));
909
910	q.addBindValue( val: 0, type: QSql::Out );
911	q.addBindValue( val: 0.0, type: QSql::Out );
912	q.addBindValue( val: "Simpson", type: QSql::Out );
913
914	QVERIFY_SQL( q, exec() );
915
916	QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
917	QCOMPARE( q.boundValue( 1 ).toDouble(), 4.2 );
918	QCOMPARE( q.boundValue( 2 ).toString().trimmed(), QString( "Homer" ) );
919	}
920
921	void tst_QSqlQuery::outValues()
922	{
923	QFETCH( QString, dbName );
924	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
925	CHECK_DATABASE( db );
926	const QString tst_outValues(qTableName(prefix: "tst_outValues", __FILE__, db));
927
928	if ( !db.driver()->hasFeature( f: QSqlDriver::PreparedQueries ) )
929	QSKIP( "Test requires prepared query support");
930
931	QSqlQuery q( db );
932
933	q.setForwardOnly( true );
934	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
935	if (dbType == QSqlDriver::Oracle) {
936	QVERIFY_SQL( q, exec( "create or replace procedure " + tst_outValues + "(x out int) is\n"
937	"begin\n"
938	" x := 42;\n"
939	"end;\n" ) );
940	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
941	} else if (dbType == QSqlDriver::DB2) {
942	q.exec( query: "drop procedure " + tst_outValues ); //non-fatal
943	QVERIFY_SQL( q, exec( "CREATE PROCEDURE " + tst_outValues + " (OUT x int)\n"
944	"LANGUAGE SQL\n"
945	"P1: BEGIN\n"
946	" SET x = 42;\n"
947	"END P1" ) );
948	QVERIFY( q.prepare( "call " + tst_outValues + "(?)" ) );
949	} else if (dbType == QSqlDriver::MSSqlServer) {
950	q.exec( query: "drop procedure " + tst_outValues ); //non-fatal
951	QVERIFY_SQL( q, exec( "create procedure " + tst_outValues + " (@x int out) as\n"
952	"begin\n"
953	" set @x = 42\n"
954	"end\n" ) );
955	QVERIFY( q.prepare( "{call " + tst_outValues + "(?)}" ) );
956	} else
957	QSKIP( "Don't know how to create a stored procedure for this database server, please fix this test");
958
959	q.addBindValue( val: 0, type: QSql::Out );
960
961	QVERIFY_SQL( q, exec() );
962
963	QCOMPARE( q.boundValue( 0 ).toInt(), 42 );
964	}
965
966	void tst_QSqlQuery::blob()
967	{
968	static const int BLOBSIZE = 1024 * 10;
969	static const int BLOBCOUNT = 2;
970
971	QFETCH( QString, dbName );
972	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
973	CHECK_DATABASE( db );
974
975	if ( !db.driver()->hasFeature( f: QSqlDriver::BLOB ) )
976	QSKIP( "DBMS not BLOB capable");
977
978	//don' make it too big otherwise sybase and mysql will complain
979	QByteArray ba( BLOBSIZE, 0 );
980
981	for (int i = 0; i < ba.size(); ++i)
982	ba[i] = i % 256;
983
984	QSqlQuery q( db );
985
986	q.setForwardOnly( true );
987
988	QString queryString = QString("create table " + qTableName(prefix: "qtest_blob", __FILE__, db) +
989	" (id int not null primary key, t_blob %1)" ).arg( a: tst_Databases::blobTypeName( db, blobSize: BLOBSIZE ) );
990	QVERIFY_SQL( q, exec( queryString ) );
991
992	QVERIFY_SQL(q, prepare("insert into " + qTableName("qtest_blob", __FILE__, db) + " (id, t_blob) values (?, ?)"));
993
994	for (int i = 0; i < BLOBCOUNT; ++i) {
995	q.addBindValue( val: i );
996	q.addBindValue( val: ba );
997	QVERIFY_SQL( q, exec() );
998	}
999
1000	QVERIFY_SQL(q, exec("select * from " + qTableName("qtest_blob", __FILE__, db)));
1001
1002	for (int i = 0; i < BLOBCOUNT; ++i) {
1003	QVERIFY( q.next() );
1004	QByteArray res = q.value( i: 1 ).toByteArray();
1005	QVERIFY2( res.size() >= ba.size(),
1006	QString( "array sizes differ, expected %1, got %2" ).arg( ba.size() ).arg( res.size() ).toLatin1() );
1007
1008	for (int i2 = 0; i2 < ba.size(); ++i2) {
1009	if ( res[i2] != ba[i2] )
1010	QFAIL( QString( "ByteArrays differ at position %1, expected %2, got %3" ).arg(
1011	i2 ).arg(( int )( unsigned char )ba[i2] ).arg(( int )( unsigned char )res[i2] ).toLatin1() );
1012	}
1013	}
1014	}
1015
1016	void tst_QSqlQuery::value()
1017	{
1018	QFETCH( QString, dbName );
1019	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1020	CHECK_DATABASE( db );
1021	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1022	QSqlQuery q( db );
1023	QVERIFY_SQL( q, exec( "select id, t_varchar, t_char from " + qtest + " order by id" ) );
1024	int i = 1;
1025
1026	while ( q.next() ) {
1027	QCOMPARE( q.value( 0 ).toInt(), i );
1028	QCOMPARE( q.value( "id" ).toInt(), i );
1029
1030	if (dbType == QSqlDriver::Interbase)
1031	QVERIFY( q.value( 1 ).toString().startsWith( "VarChar" + QString::number( i ) ) );
1032	else if ( q.value( i: 1 ).toString().endsWith(c: QLatin1Char(' ')))
1033	QCOMPARE( q.value( 1 ).toString(), ( "VarChar" + QString::number( i ) + " " ) );
1034	else
1035	QCOMPARE( q.value( 1 ).toString(), ( "VarChar" + QString::number( i ) ) );
1036
1037	if (dbType == QSqlDriver::Interbase)
1038	QVERIFY( q.value( 2 ).toString().startsWith( "Char" + QString::number( i ) ) );
1039	else if (!q.value( i: 2 ).toString().endsWith(c: QLatin1Char(' ')))
1040	QCOMPARE( q.value( 2 ).toString(), ( "Char" + QString::number( i ) ) );
1041	else
1042	QCOMPARE( q.value( 2 ).toString(), ( "Char" + QString::number( i ) + " " ) );
1043
1044	i++;
1045	}
1046	}
1047
1048	#define SETUP_RECORD_TABLE \
1049	do { \
1050	QVERIFY_SQL(q, exec("CREATE TABLE " + tst_record + " (id integer, extra varchar(50))")); \
1051	for (int i = 0; i < 3; ++i) \
1052	QVERIFY_SQL(q, exec(QString("INSERT INTO " + tst_record + " VALUES(%1, 'extra%1')").arg(i))); \
1053	} while (0)
1054
1055	#define CHECK_RECORD \
1056	do { \
1057	QVERIFY_SQL(q, exec(QString("select %1.id, %1.t_varchar, %1.t_char, %2.id, %2.extra from %1, %2 where " \
1058	"%1.id = %2.id order by %1.id").arg(lowerQTest).arg(tst_record))); \
1059	QCOMPARE(q.record().fieldName(0).toLower(), QString("id")); \
1060	QCOMPARE(q.record().field(0).tableName().toLower(), lowerQTest); \
1061	QCOMPARE(q.record().fieldName(1).toLower(), QString("t_varchar")); \
1062	QCOMPARE(q.record().field(1).tableName().toLower(), lowerQTest); \
1063	QCOMPARE(q.record().fieldName(2).toLower(), QString("t_char")); \
1064	QCOMPARE(q.record().field(2).tableName().toLower(), lowerQTest); \
1065	QCOMPARE(q.record().fieldName(3).toLower(), QString("id")); \
1066	QCOMPARE(q.record().field(3).tableName().toLower(), tst_record); \
1067	QCOMPARE(q.record().fieldName(4).toLower(), QString("extra")); \
1068	QCOMPARE(q.record().field(4).tableName().toLower(), tst_record); \
1069	} while (0)
1070
1071	void tst_QSqlQuery::record()
1072	{
1073	QFETCH( QString, dbName );
1074	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1075	CHECK_DATABASE( db );
1076
1077	QSqlQuery q( db );
1078	QVERIFY( q.record().isEmpty() );
1079	QVERIFY_SQL( q, exec( "select id, t_varchar, t_char from " + qtest + " order by id" ) );
1080	QCOMPARE( q.record().fieldName( 0 ).toLower(), QString( "id" ) );
1081	QCOMPARE( q.record().fieldName( 1 ).toLower(), QString( "t_varchar" ) );
1082	QCOMPARE( q.record().fieldName( 2 ).toLower(), QString( "t_char" ) );
1083	QCOMPARE(q.record().value(0), QVariant(q.record().field(0).type()));
1084	QCOMPARE(q.record().value(1), QVariant(q.record().field(1).type()));
1085	QCOMPARE(q.record().value(2), QVariant(q.record().field(2).type()));
1086
1087	QVERIFY( q.next() );
1088	QVERIFY( q.next() );
1089
1090	QCOMPARE( q.record().fieldName( 0 ).toLower(), QString( "id" ) );
1091	QCOMPARE( q.value( 0 ).toInt(), 2 );
1092
1093	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1094	if (dbType == QSqlDriver::Oracle)
1095	QSKIP("Getting the tablename is not supported in Oracle");
1096	const auto lowerQTest = qtest.toLower();
1097	for (int i = 0; i < 3; ++i)
1098	QCOMPARE(q.record().field(i).tableName().toLower(), lowerQTest);
1099	q.clear();
1100	const auto tst_record = qTableName(prefix: "tst_record", __FILE__, db, escape: false).toLower();
1101	SETUP_RECORD_TABLE;
1102	CHECK_RECORD;
1103	q.clear();
1104
1105	// Recreate the tables, in a different order
1106	const QStringList tables = { qtest, tst_record, qTableName(prefix: "qtest_null", __FILE__, db) };
1107	tst_Databases::safeDropTables(db, tableNames: tables);
1108	SETUP_RECORD_TABLE;
1109	createTestTables(db);
1110	populateTestTables(db);
1111	CHECK_RECORD;
1112	}
1113
1114	void tst_QSqlQuery::isValid()
1115	{
1116	QFETCH( QString, dbName );
1117	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1118	CHECK_DATABASE( db );
1119
1120	QSqlQuery q( db );
1121	QVERIFY( !q.isValid() );
1122	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1123	QVERIFY( q.first() );
1124	QVERIFY( q.isValid() );
1125	}
1126
1127	void tst_QSqlQuery::isActive()
1128	{
1129	QFETCH( QString, dbName );
1130	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1131	CHECK_DATABASE( db );
1132
1133	QSqlQuery q( db );
1134	QVERIFY( !q.isActive() );
1135	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1136	QVERIFY( q.isActive() );
1137	QVERIFY( q.last() );
1138
1139	if ( !tst_Databases::isMSAccess( db ) )
1140	// Access is stupid enough to let you scroll over boundaries
1141	QVERIFY( !q.next() );
1142
1143	QVERIFY( q.isActive() );
1144
1145	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (41, 'VarChar41', 'Char41')" ) );
1146
1147	QVERIFY( q.isActive() );
1148
1149	QVERIFY_SQL( q, exec( "update " + qtest + " set id = 42 where id = 41" ) );
1150
1151	QVERIFY( q.isActive() );
1152
1153	QVERIFY_SQL( q, exec( "delete from " + qtest + " where id = 42" ) );
1154
1155	QVERIFY( q.isActive() );
1156
1157	QVERIFY_SQL( q, exec( "delete from " + qtest + " where id = 42" ) );
1158
1159	QVERIFY( q.isActive() );
1160	}
1161
1162	void tst_QSqlQuery::numRowsAffected()
1163	{
1164	QFETCH( QString, dbName );
1165	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1166	CHECK_DATABASE( db );
1167
1168	QSqlQuery q( db );
1169	QCOMPARE( q.numRowsAffected(), -1 );
1170
1171	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1172	int i = 0;
1173
1174	while ( q.next() )
1175	++i;
1176
1177	if ( q.numRowsAffected() == -1 || q.numRowsAffected() == 0 )
1178	QSKIP("Database doesn't support numRowsAffected");
1179
1180	if ( q.numRowsAffected() != -1 && q.numRowsAffected() != 0 && q.numRowsAffected() != i ) {
1181	// the value is undefined for SELECT, this check is just here for curiosity
1182	qDebug( msg: "Expected numRowsAffected to be -1, 0 or %d, got %d", i, q.numRowsAffected() );
1183	}
1184
1185	QVERIFY_SQL( q, exec( "update " + qtest + " set id = 100 where id = 1" ) );
1186
1187	QCOMPARE( q.numRowsAffected(), 1 );
1188	QCOMPARE( q.numRowsAffected(), 1 ); // yes, we check twice
1189
1190	QVERIFY_SQL( q, exec( "update " + qtest + " set id = id + 100" ) );
1191	QCOMPARE( q.numRowsAffected(), i );
1192	QCOMPARE( q.numRowsAffected(), i ); // yes, we check twice
1193
1194	QVERIFY_SQL( q, prepare( "update " + qtest + " set id = id + :newid" ) );
1195	q.bindValue(placeholder: ":newid", val: 100);
1196	QVERIFY_SQL( q, exec() );
1197	QCOMPARE( q.numRowsAffected(), i );
1198	QCOMPARE( q.numRowsAffected(), i ); // yes, we check twice
1199
1200	QVERIFY_SQL( q, prepare( "update " + qtest + " set id = id + :newid where NOT(1 = 1)" ) );
1201	q.bindValue(placeholder: ":newid", val: 100);
1202	QVERIFY_SQL( q, exec() );
1203	QCOMPARE( q.numRowsAffected(), 0 );
1204	QCOMPARE( q.numRowsAffected(), 0 ); // yes, we check twice
1205
1206	QVERIFY_SQL( q, exec( "insert into " + qtest + " values (42000, 'homer', 'marge')" ) );
1207	QCOMPARE( q.numRowsAffected(), 1 );
1208	QCOMPARE( q.numRowsAffected(), 1 ); // yes, we check twice
1209
1210	QSqlQuery q2( db );
1211	QVERIFY_SQL( q2, exec( "insert into " + qtest + " values (42001, 'homer', 'marge')" ) );
1212
1213	if ( !db.driverName().startsWith( s: "QSQLITE2" ) ) {
1214	// SQLite 2.x accumulates changed rows in nested queries. See task 33794
1215	QCOMPARE( q2.numRowsAffected(), 1 );
1216	QCOMPARE( q2.numRowsAffected(), 1 ); // yes, we check twice
1217	}
1218	}
1219
1220	void tst_QSqlQuery::size()
1221	{
1222	QFETCH( QString, dbName );
1223	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1224	CHECK_DATABASE( db );
1225
1226	QSqlQuery q( db );
1227	QCOMPARE( q.size(), -1 );
1228
1229	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1230	int i = 0;
1231
1232	while ( q.next() )
1233	++i;
1234
1235	if ( db.driver()->hasFeature( f: QSqlDriver::QuerySize ) ) {
1236	QCOMPARE( q.size(), i );
1237	QCOMPARE( q.size(), i ); // yes, twice
1238	} else {
1239	QCOMPARE( q.size(), -1 );
1240	QCOMPARE( q.size(), -1 ); // yes, twice
1241	}
1242
1243	QSqlQuery q2( "select * from " + qtest, db );
1244
1245	if ( db.driver()->hasFeature( f: QSqlDriver::QuerySize ) )
1246	QCOMPARE( q.size(), i );
1247	else
1248	QCOMPARE( q.size(), -1 );
1249
1250	q2.clear();
1251
1252	QVERIFY_SQL( q, exec( "update " + qtest + " set id = 100 where id = 1" ) );
1253	QCOMPARE( q.size(), -1 );
1254	QCOMPARE( q.size(), -1 ); // yes, twice
1255	}
1256
1257	void tst_QSqlQuery::isSelect()
1258	{
1259	QFETCH( QString, dbName );
1260	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1261	CHECK_DATABASE( db );
1262
1263	QSqlQuery q( db );
1264	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1265	QVERIFY( q.isSelect() );
1266
1267	QVERIFY_SQL( q, exec( "update " + qtest + " set id = 1 where id = 1" ) );
1268	QVERIFY( q.isSelect() == false );
1269	}
1270
1271	void tst_QSqlQuery::first()
1272	{
1273	QFETCH( QString, dbName );
1274	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1275	CHECK_DATABASE( db );
1276
1277	QSqlQuery q( db );
1278	QVERIFY( q.at() == QSql::BeforeFirstRow );
1279	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1280	QVERIFY( q.last() );
1281	QVERIFY_SQL( q, first() );
1282	QVERIFY( q.at() == 0 );
1283	}
1284
1285	void tst_QSqlQuery::next()
1286	{
1287	QFETCH( QString, dbName );
1288	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1289	CHECK_DATABASE( db );
1290
1291	QSqlQuery q( db );
1292	QVERIFY( q.at() == QSql::BeforeFirstRow );
1293	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1294	QVERIFY( q.first() );
1295	QVERIFY( q.next() );
1296	QVERIFY( q.at() == 1 );
1297	}
1298
1299	void tst_QSqlQuery::prev()
1300	{
1301	QFETCH( QString, dbName );
1302	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1303	CHECK_DATABASE( db );
1304
1305	QSqlQuery q( db );
1306	QVERIFY( q.at() == QSql::BeforeFirstRow );
1307	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1308	QVERIFY( q.first() );
1309	QVERIFY( q.next() );
1310	QVERIFY( q.previous() );
1311	QVERIFY( q.at() == 0 );
1312	}
1313
1314	void tst_QSqlQuery::last()
1315	{
1316	QFETCH( QString, dbName );
1317	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1318	CHECK_DATABASE( db );
1319
1320	QSqlQuery q( db );
1321	QCOMPARE( q.at(), int( QSql::BeforeFirstRow ) );
1322	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1323	int i = 0;
1324
1325	while ( q.next() )
1326	i++;
1327
1328	QCOMPARE( q.at(), int( QSql::AfterLastRow ) );
1329
1330	QVERIFY( q.last() );
1331
1332	QSet<int> validReturns(QSet<int>() << -1 << i-1);
1333	QVERIFY( validReturns.contains(q.at()) );
1334
1335	QSqlQuery q2( "select * from " + qtest, db );
1336
1337	QVERIFY( q2.last() );
1338
1339	QVERIFY( validReturns.contains(q.at()) );
1340	}
1341
1342	void tst_QSqlQuery::seek()
1343	{
1344	QFETCH( QString, dbName );
1345	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1346	CHECK_DATABASE( db );
1347	QSqlQuery q( db );
1348	QVERIFY( q.at() == QSql::BeforeFirstRow );
1349	QVERIFY_SQL( q, exec( QString( "select id from %1 order by id" ).arg( qtest ) ) );
1350
1351	// NB! The order of the calls below are important!
1352	QVERIFY( q.last() );
1353	QVERIFY( !q.seek( QSql::BeforeFirstRow ) );
1354	QCOMPARE( q.at(), int( QSql::BeforeFirstRow ) );
1355	QVERIFY( q.seek( 0 ) );
1356	QCOMPARE( q.at(), 0 );
1357	QCOMPARE( q.value( 0 ).toInt(), 1 );
1358
1359	QVERIFY( q.seek( 1 ) );
1360	QCOMPARE( q.at(), 1 );
1361	QCOMPARE( q.value( 0 ).toInt(), 2 );
1362
1363	QVERIFY( q.seek( 3 ) );
1364	QCOMPARE( q.at(), 3 );
1365	QCOMPARE( q.value( 0 ).toInt(), 4 );
1366
1367	QVERIFY( q.seek( -2, true ) );
1368	QCOMPARE( q.at(), 1 );
1369	QVERIFY( q.seek( 0 ) );
1370	QCOMPARE( q.at(), 0 );
1371	QCOMPARE( q.value( 0 ).toInt(), 1 );
1372
1373	QVERIFY(!q.seek(QSql::BeforeFirstRow));
1374	QCOMPARE(q.at(), int(QSql::BeforeFirstRow));
1375	QVERIFY(q.seek(1, true));
1376	QCOMPARE(q.at(), 0);
1377	QCOMPARE(q.value(0).toInt(), 1);
1378
1379	qint32 count = 1;
1380	while (q.next()) ++count;
1381
1382	QCOMPARE(q.at(), int(QSql::AfterLastRow));
1383
1384	if (!q.isForwardOnly()) {
1385	QVERIFY(q.seek(-1, true));
1386	QCOMPARE(q.at(), count - 1);
1387	QCOMPARE(q.value(0).toInt(), count);
1388	} else {
1389	QVERIFY(!q.seek(-1, true));
1390	QCOMPARE(q.at(), int(QSql::AfterLastRow));
1391	}
1392	}
1393
1394	void tst_QSqlQuery::seekForwardOnlyQuery()
1395	{
1396	QFETCH( QString, dbName );
1397	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1398	CHECK_DATABASE( db );
1399
1400	QSqlQuery q( db );
1401	q.setForwardOnly( false );
1402	QVERIFY( !q.isForwardOnly() );
1403
1404	QVERIFY( q.at() == QSql::BeforeFirstRow );
1405	QVERIFY_SQL( q, exec( QString( "select id from %1 order by id" ).arg( qtest ) ) );
1406
1407	QSqlRecord rec;
1408
1409	// NB! The order of the calls below are important!
1410	QVERIFY( q.seek( 0 ) );
1411	QCOMPARE( q.at(), 0 );
1412	rec = q.record();
1413	QCOMPARE( rec.value( 0 ).toInt(), 1 );
1414
1415	QVERIFY( q.seek( 1 ) );
1416	QCOMPARE( q.at(), 1 );
1417	rec = q.record();
1418	QCOMPARE( rec.value( 0 ).toInt(), 2 );
1419
1420	// Make a jump!
1421	QVERIFY( q.seek( 3 ) );
1422	QCOMPARE( q.at(), 3 );
1423	rec = q.record();
1424	QCOMPARE( rec.value( 0 ).toInt(), 4 );
1425
1426	// Last record in result set
1427	QVERIFY( q.seek( 4 ) );
1428	QCOMPARE( q.at(), 4 );
1429	rec = q.record();
1430	QCOMPARE( rec.value( 0 ).toInt(), 5 );
1431	}
1432
1433	// tests the forward only mode;
1434	void tst_QSqlQuery::forwardOnly()
1435	{
1436	QFETCH( QString, dbName );
1437	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1438	CHECK_DATABASE( db );
1439
1440	QSqlQuery q( db );
1441	q.setForwardOnly( true );
1442	QVERIFY( q.isForwardOnly() );
1443	QVERIFY( q.at() == QSql::BeforeFirstRow );
1444	QVERIFY_SQL( q, exec( "select * from " + qtest + " order by id" ) );
1445	if (!q.isForwardOnly())
1446	QSKIP("DBMS doesn't support forward-only queries");
1447	QVERIFY( q.at() == QSql::BeforeFirstRow );
1448	QVERIFY( q.first() );
1449	QCOMPARE( q.at(), 0 );
1450	QCOMPARE( q.value( 0 ).toInt(), 1 );
1451	QVERIFY( q.next() );
1452	QCOMPARE( q.at(), 1 );
1453	QCOMPARE( q.value( 0 ).toInt(), 2 );
1454	QVERIFY( q.next() );
1455	QCOMPARE( q.at(), 2 );
1456	QCOMPARE( q.value( 0 ).toInt(), 3 );
1457
1458	// lets make some mistakes to see how robust it is
1459	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1460	QVERIFY( q.first() == false );
1461	QCOMPARE( q.at(), 2 );
1462	QCOMPARE( q.value( 0 ).toInt(), 3 );
1463	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1464	QVERIFY( q.previous() == false );
1465	QCOMPARE( q.at(), 2 );
1466	QCOMPARE( q.value( 0 ).toInt(), 3 );
1467	QVERIFY( q.next() );
1468	QCOMPARE( q.at(), 3 );
1469	QCOMPARE( q.value( 0 ).toInt(), 4 );
1470
1471	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1472	int i = 0;
1473
1474	while ( q.next() )
1475	i++;
1476
1477	QVERIFY( q.at() == QSql::AfterLastRow );
1478
1479	QSqlQuery q2 = q;
1480
1481	QVERIFY( q2.isForwardOnly() );
1482
1483	QVERIFY_SQL( q, exec( "select * from " + qtest + " order by id" ) );
1484
1485	QVERIFY( q.isForwardOnly() );
1486
1487	QVERIFY( q2.isForwardOnly() );
1488
1489	QCOMPARE( q.at(), int( QSql::BeforeFirstRow ) );
1490
1491	QVERIFY_SQL( q, seek( 3 ) );
1492
1493	QCOMPARE( q.at(), 3 );
1494
1495	QCOMPARE( q.value( 0 ).toInt(), 4 );
1496
1497	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1498
1499	QVERIFY( q.seek( 0 ) == false );
1500
1501	QCOMPARE( q.value( 0 ).toInt(), 4 );
1502
1503	QCOMPARE( q.at(), 3 );
1504
1505	QVERIFY( q.last() );
1506
1507	QCOMPARE( q.at(), i-1 );
1508
1509	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query" );
1510
1511	QVERIFY( q.first() == false );
1512
1513	QCOMPARE( q.at(), i-1 );
1514
1515	QVERIFY( q.next() == false );
1516
1517	QCOMPARE( q.at(), int( QSql::AfterLastRow ) );
1518	}
1519
1520	void tst_QSqlQuery::forwardOnlyMultipleResultSet()
1521	{
1522	QFETCH(QString, dbName);
1523	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1524	CHECK_DATABASE(db);
1525
1526	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1527
1528	if (!db.driver()->hasFeature(f: QSqlDriver::MultipleResultSets))
1529	QSKIP("DBMS doesn't support multiple result sets");
1530
1531	QSqlQuery q(db);
1532	q.setForwardOnly(true);
1533	QVERIFY_SQL(q, exec("select id, t_varchar from " + qtest + " order by id;" // 1.
1534	"select id, t_varchar, t_char from " + qtest + " where id<4 order by id;" // 2.
1535	"update " + qtest + " set t_varchar='VarChar555' where id=5;" // 3.
1536	"select * from " + qtest + " order by id;" // 4.
1537	"select * from " + qtest + " where id=5 order by id;" // 5.
1538	"select * from " + qtest + " where id=-1 order by id;" // 6.
1539	"select * from " + qtest + " order by id")); // 7.
1540
1541	if (!q.isForwardOnly())
1542	QSKIP("DBMS doesn't support forward-only queries");
1543
1544	// 1. Result set with 2 columns and 5 rows
1545	QVERIFY(q.at() == QSql::BeforeFirstRow);
1546	QCOMPARE(q.isActive(), true);
1547	QCOMPARE(q.isSelect(), true);
1548
1549	// Test record() of first result set
1550	QSqlRecord record = q.record();
1551	QCOMPARE(record.count(), 2);
1552	QCOMPARE(record.indexOf("id"), 0);
1553	QCOMPARE(record.indexOf("t_varchar"), 1);
1554	if (dbType != QSqlDriver::PostgreSQL) { // tableName() is not available in forward-only mode of QPSQL
1555	QCOMPARE(record.field(0).tableName(), qtest); // BUG: This fails for Microsoft SQL Server 2016 (QODBC), need fix
1556	QCOMPARE(record.field(1).tableName(), qtest);
1557	}
1558
1559	// Test navigation
1560	QVERIFY(q.first());
1561	QCOMPARE(q.at(), 0);
1562	QCOMPARE(q.value(0).toInt(), 1);
1563
1564	QVERIFY(q.next());
1565	QCOMPARE(q.at(), 1);
1566	QCOMPARE(q.value(0).toInt(), 2);
1567
1568	QVERIFY(q.seek(3));
1569	QCOMPARE(q.at(), 3);
1570	QCOMPARE(q.value(0).toInt(), 4);
1571
1572	QTest::ignoreMessage(type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query");
1573	QVERIFY(q.first() == false);
1574	QCOMPARE(q.at(), 3);
1575	QCOMPARE(q.value(0).toInt(), 4);
1576
1577	QTest::ignoreMessage(type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query");
1578	QVERIFY(q.previous() == false);
1579	QCOMPARE(q.at(), 3);
1580	QCOMPARE(q.value(0).toInt(), 4);
1581
1582	QTest::ignoreMessage(type: QtWarningMsg, message: "QSqlQuery::seek: cannot seek backwards in a forward only query");
1583	QVERIFY(q.seek(1) == false);
1584	QCOMPARE(q.at(), 3);
1585	QCOMPARE(q.value(0).toInt(), 4);
1586
1587	QVERIFY(q.last());
1588	QCOMPARE(q.at(), 4);
1589
1590	// Try move after last row
1591	QVERIFY(q.next() == false);
1592	QCOMPARE(q.at(), QSql::AfterLastRow);
1593	QCOMPARE(q.isActive(), true);
1594
1595	// 2. Result set with 3 columns and 3 rows
1596	QVERIFY(q.nextResult());
1597	QVERIFY(q.at() == QSql::BeforeFirstRow);
1598	QCOMPARE(q.isActive(), true);
1599	QCOMPARE(q.isSelect(), true);
1600
1601	// Test record() of second result set
1602	record = q.record();
1603	QCOMPARE(record.count(), 3);
1604	QCOMPARE(record.indexOf("id"), 0);
1605	QCOMPARE(record.indexOf("t_varchar"), 1);
1606	QCOMPARE(record.indexOf("t_char"), 2);
1607
1608	// Test iteration
1609	QVERIFY(q.at() == QSql::BeforeFirstRow);
1610	int index = 0;
1611	while (q.next()) {
1612	QCOMPARE(q.at(), index);
1613	QCOMPARE(q.value(0).toInt(), index+1);
1614	index++;
1615	}
1616	QVERIFY(q.at() == QSql::AfterLastRow);
1617	QCOMPARE(index, 3);
1618
1619	// 3. Update statement
1620	QVERIFY(q.nextResult());
1621	QVERIFY(q.at() == QSql::BeforeFirstRow);
1622	QCOMPARE(q.isActive(), true);
1623	QCOMPARE(q.isSelect(), false);
1624	QCOMPARE(q.numRowsAffected(), 1);
1625
1626	// 4. Result set with 5 rows
1627	QVERIFY(q.nextResult());
1628	QVERIFY(q.at() == QSql::BeforeFirstRow);
1629	QCOMPARE(q.isActive(), true);
1630	QCOMPARE(q.isSelect(), true);
1631
1632	// Test forward seek(n)
1633	QVERIFY(q.seek(2));
1634	QCOMPARE(q.at(), 2);
1635	QCOMPARE(q.value(0).toInt(), 3);
1636
1637	// Test value(string)
1638	QCOMPARE(q.value("id").toInt(), 3);
1639	QCOMPARE(q.value("t_varchar").toString(), "VarChar3");
1640	QCOMPARE(q.value("t_char").toString().trimmed(), "Char3");
1641
1642	// Next 2 rows of current result set will be
1643	// discarded by next call of nextResult()
1644
1645	// 5. Result set with 1 row
1646	QVERIFY(q.nextResult());
1647	QVERIFY(q.at() == QSql::BeforeFirstRow);
1648	QCOMPARE(q.isActive(), true);
1649	QCOMPARE(q.isSelect(), true);
1650	QVERIFY(q.first());
1651	QCOMPARE(q.at(), 0);
1652	QCOMPARE(q.value(0).toInt(), 5);
1653	QVERIFY(q.next() == false);
1654	QVERIFY(q.at() == QSql::AfterLastRow);
1655
1656	// 6. Result set without rows
1657	QVERIFY(q.nextResult());
1658	QVERIFY(q.at() == QSql::BeforeFirstRow);
1659	QCOMPARE(q.isActive(), true);
1660	QCOMPARE(q.isSelect(), true);
1661	QVERIFY(q.next() == false);
1662
1663	// 7. Result set with 5 rows
1664	QVERIFY(q.nextResult());
1665	QVERIFY(q.at() == QSql::BeforeFirstRow);
1666	QCOMPARE(q.isActive(), true);
1667	QCOMPARE(q.isSelect(), true);
1668
1669	// Just skip it, so we move after last result set.
1670	QVERIFY(q.nextResult() == false);
1671	QVERIFY(q.at() == QSql::BeforeFirstRow);
1672	QCOMPARE(q.isActive(), false);
1673
1674	// See if we can execute another query
1675	QVERIFY_SQL(q, exec("select id from " + qtest + " where id=5"));
1676	QVERIFY(q.at() == QSql::BeforeFirstRow);
1677	QCOMPARE(q.isActive(), true);
1678	QCOMPARE(q.isSelect(), true);
1679	QVERIFY(q.first());
1680	QCOMPARE(q.at(), 0);
1681	QCOMPARE(q.value("id").toInt(), 5);
1682	QCOMPARE(q.record().count(), 1);
1683	QCOMPARE(q.record().indexOf("id"), 0);
1684	}
1685
1686	void tst_QSqlQuery::psql_forwardOnlyQueryResultsLost()
1687	{
1688	QFETCH(QString, dbName);
1689	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1690	CHECK_DATABASE(db);
1691
1692	QSqlQuery q1(db);
1693	q1.setForwardOnly(true);
1694	QVERIFY_SQL(q1, exec("select * from " + qtest + " where id<=3 order by id"));
1695	if (!q1.isForwardOnly())
1696	QSKIP("DBMS doesn't support forward-only queries");
1697
1698	// Read first row of q1
1699	QVERIFY(q1.next());
1700	QCOMPARE(q1.at(), 0);
1701	QCOMPARE(q1.value(0).toInt(), 1);
1702
1703	// Executing another query on the same db connection
1704	// will cause the query results of q1 to be lost.
1705	QSqlQuery q2(db);
1706	q2.setForwardOnly(true);
1707	QVERIFY_SQL(q2, exec("select * from " + qtest + " where id>3 order by id"));
1708
1709	QTest::ignoreMessage(type: QtWarningMsg, message: "QPSQLDriver::getResult: Query results lost - "
1710	"probably discarded on executing another SQL query.");
1711
1712	// Reading next row of q1 will not possible.
1713	QVERIFY(!q1.next());
1714	QCOMPARE(q1.at(), QSql::AfterLastRow);
1715	QVERIFY(q1.lastError().type() != QSqlError::NoError);
1716
1717	// See if we can read rows from q2
1718	QVERIFY(q2.seek(1));
1719	QCOMPARE(q2.at(), 1);
1720	QCOMPARE(q2.value(0).toInt(), 5);
1721	}
1722
1723	void tst_QSqlQuery::query_exec()
1724	{
1725	QFETCH( QString, dbName );
1726	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1727	CHECK_DATABASE( db );
1728
1729	QSqlQuery q( db );
1730	QVERIFY( !q.isValid() );
1731	QVERIFY( !q.isActive() );
1732	QVERIFY_SQL( q, exec( "select * from " + qtest ) );
1733	QVERIFY( q.isActive() );
1734	QVERIFY( q.next() );
1735	QVERIFY( q.isValid() );
1736	}
1737
1738	void tst_QSqlQuery::isNull()
1739	{
1740	QFETCH( QString, dbName );
1741	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1742	CHECK_DATABASE( db );
1743
1744	QSqlQuery q( db );
1745	QVERIFY_SQL(q, exec("select id, t_varchar from " + qTableName("qtest_null", __FILE__, db) + " order by id"));
1746	QVERIFY( q.next() );
1747	QVERIFY( !q.isNull( 0 ) );
1748	QVERIFY(!q.isNull("id"));
1749	QVERIFY( q.isNull( 1 ) );
1750	QVERIFY(q.isNull("t_varchar"));
1751	QCOMPARE( q.value( 0 ).toInt(), 0 );
1752	QCOMPARE( q.value( 1 ).toString(), QString() );
1753	QVERIFY( !q.value( 0 ).isNull() );
1754	QVERIFY( q.value( 1 ).isNull() );
1755
1756	QVERIFY( q.next() );
1757	QVERIFY( !q.isNull( 0 ) );
1758	QVERIFY(!q.isNull("id"));
1759	QVERIFY( !q.isNull( 1 ) );
1760	QVERIFY(!q.isNull("t_varchar"));
1761
1762	// For a non existent field, it should be returning true.
1763	QVERIFY(q.isNull(2));
1764	QVERIFY(q.isNull("unknown"));
1765	}
1766
1767	/*! TDS specific BIT field test */
1768	void tst_QSqlQuery::tds_bitField()
1769	{
1770	QFETCH( QString, dbName );
1771	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1772	CHECK_DATABASE( db );
1773	const QString tableName = qTableName(prefix: "qtest_bittest", __FILE__, db);
1774	QSqlQuery q( db );
1775
1776	QVERIFY_SQL(q, exec("create table " + tableName + " (bitty bit)"));
1777
1778	QVERIFY_SQL(q, exec("insert into " + tableName + " values (0)"));
1779
1780	QVERIFY_SQL(q, exec("insert into " + tableName + " values (1)"));
1781
1782	QVERIFY_SQL(q, exec("select bitty from " + tableName));
1783
1784	QVERIFY( q.next() );
1785
1786	QVERIFY( q.value( 0 ).toInt() == 0 );
1787
1788	QVERIFY( q.next() );
1789
1790	QVERIFY( q.value( 0 ).toInt() == 1 );
1791	}
1792
1793
1794	/*! Oracle specific NULL BLOB test */
1795	void tst_QSqlQuery::oci_nullBlob()
1796	{
1797	QFETCH( QString, dbName );
1798	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1799	CHECK_DATABASE( db );
1800	const QString qtest_nullblob(qTableName(prefix: "qtest_nullblob", __FILE__, db));
1801
1802	QSqlQuery q( db );
1803	QVERIFY_SQL( q, exec( "create table " + qtest_nullblob + " (id int primary key, bb blob)" ) );
1804	QVERIFY_SQL( q, exec( "insert into " + qtest_nullblob + " values (0, EMPTY_BLOB())" ) );
1805	QVERIFY_SQL( q, exec( "insert into " + qtest_nullblob + " values (1, NULL)" ) );
1806	QVERIFY_SQL( q, exec( "insert into " + qtest_nullblob + " values (2, 'aabbcc00112233445566')" ) );
1807	// necessary otherwise oracle will bombard you with internal errors
1808	q.setForwardOnly( true );
1809	QVERIFY_SQL( q, exec( "select * from " + qtest_nullblob + " order by id" ) );
1810
1811	QVERIFY( q.next() );
1812	QCOMPARE(( int )q.value( 1 ).toByteArray().size(), 0 );
1813	QVERIFY( !q.isNull( 1 ) );
1814
1815	QVERIFY( q.next() );
1816	QCOMPARE(( int )q.value( 1 ).toByteArray().size(), 0 );
1817	QVERIFY( q.isNull( 1 ) );
1818
1819	QVERIFY( q.next() );
1820	QCOMPARE(( int )q.value( 1 ).toByteArray().size(), 10 );
1821	QVERIFY( !q.isNull( 1 ) );
1822	}
1823
1824	/* Oracle specific RAW field test */
1825	void tst_QSqlQuery::oci_rawField()
1826	{
1827	QFETCH( QString, dbName );
1828	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1829	CHECK_DATABASE( db );
1830	const QString qtest_rawtest(qTableName(prefix: "qtest_rawtest", __FILE__, db));
1831
1832	QSqlQuery q( db );
1833	q.setForwardOnly( true );
1834	QVERIFY_SQL( q, exec( "create table " + qtest_rawtest +
1835	" (id int, col raw(20))" ) );
1836	QVERIFY_SQL( q, exec( "insert into " + qtest_rawtest + " values (0, NULL)" ) );
1837	QVERIFY_SQL( q, exec( "insert into " + qtest_rawtest + " values (1, '00aa1100ddeeff')" ) );
1838	QVERIFY_SQL( q, exec( "select col from " + qtest_rawtest + " order by id" ) );
1839	QVERIFY( q.next() );
1840	QVERIFY( q.isNull( 0 ) );
1841	QCOMPARE(( int )q.value( 0 ).toByteArray().size(), 0 );
1842	QVERIFY( q.next() );
1843	QVERIFY( !q.isNull( 0 ) );
1844	QCOMPARE(( int )q.value( 0 ).toByteArray().size(), 7 );
1845	}
1846
1847	// test whether we can fetch values with more than DOUBLE precision
1848	// note that SQLite highest precision is that of a double, although
1849	// you can define field with higher precision
1850	void tst_QSqlQuery::precision()
1851	{
1852	QFETCH( QString, dbName );
1853	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1854	CHECK_DATABASE( db );
1855	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1856	if (dbType == QSqlDriver::Interbase)
1857	QSKIP("DB unable to store high precision");
1858
1859	const auto oldPrecision = db.driver()->numericalPrecisionPolicy();
1860	db.driver()->setNumericalPrecisionPolicy(QSql::HighPrecision);
1861	const QString qtest_precision(qTableName(prefix: "qtest_precision", __FILE__, db));
1862	static const QLatin1String precStr("1.2345678901234567891");
1863
1864	{
1865	// need a new scope for SQLITE
1866	QSqlQuery q( db );
1867
1868	q.exec(query: "drop table " + qtest_precision);
1869	if (tst_Databases::isMSAccess(db))
1870	QVERIFY_SQL(q, exec("CREATE TABLE " + qtest_precision + " (col1 number)"));
1871	else
1872	QVERIFY_SQL(q, exec("CREATE TABLE " + qtest_precision + " (col1 numeric(21, 20))"));
1873
1874	QVERIFY_SQL(q, exec("INSERT INTO " + qtest_precision + " (col1) VALUES (" + precStr + ")"));
1875	QVERIFY_SQL(q, exec("SELECT * FROM " + qtest_precision));
1876	QVERIFY(q.next());
1877	const QString val = q.value(i: 0).toString();
1878	if (!val.startsWith(s: precStr)) {
1879	int i = 0;
1880	while (i < val.size() && precStr[i] != 0 && precStr[i] == val[i].toLatin1())
1881	i++;
1882
1883	// TDS has crappy precisions by default
1884	if (dbType == QSqlDriver::Sybase) {
1885	if (i < 18)
1886	QWARN("TDS didn't return the right precision");
1887	} else {
1888	QWARN(QString(tst_Databases::dbToString(db) + " didn't return the right precision (" +
1889	QString::number(i) + " out of 21), " + val).toUtf8());
1890	}
1891	}
1892	} // SQLITE scope
1893	db.driver()->setNumericalPrecisionPolicy(oldPrecision);
1894	}
1895
1896	void tst_QSqlQuery::nullResult()
1897	{
1898	QFETCH( QString, dbName );
1899	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1900	CHECK_DATABASE( db );
1901
1902	QSqlQuery q( db );
1903	QVERIFY_SQL( q, exec( "select * from " + qtest + " where id > 50000" ) );
1904
1905	if ( q.driver()->hasFeature( f: QSqlDriver::QuerySize ) )
1906	QCOMPARE( q.size(), 0 );
1907
1908	QVERIFY( q.next() == false );
1909
1910	QVERIFY( q.first() == false );
1911	QVERIFY( q.last() == false );
1912	QVERIFY( q.previous() == false );
1913	QVERIFY( q.seek( 10 ) == false );
1914	QVERIFY( q.seek( 0 ) == false );
1915	}
1916
1917	// this test is just an experiment to see whether we can do query-based transactions
1918	// the real transaction test is in tst_QSqlDatabase
1919	void tst_QSqlQuery::transaction()
1920	{
1921	// query based transaction is not really possible with Qt
1922	QSKIP( "only tested manually by trained staff");
1923
1924	QFETCH( QString, dbName );
1925	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
1926	CHECK_DATABASE( db );
1927	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1928	if ( !db.driver()->hasFeature( f: QSqlDriver::Transactions ) )
1929	QSKIP( "DBMS not transaction capable");
1930
1931	// this is the standard SQL
1932	QString startTransactionStr( "start transaction" );
1933
1934	if (dbType == QSqlDriver::MySqlServer)
1935	startTransactionStr = "begin work";
1936
1937	QSqlQuery q( db );
1938
1939	QSqlQuery q2( db );
1940
1941	// test a working transaction
1942	q.exec( query: startTransactionStr );
1943
1944	QVERIFY_SQL( q, exec( "insert into" + qtest + " values (40, 'VarChar40', 'Char40')" ) );
1945
1946	QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 40" ) );
1947
1948	QVERIFY( q.next() );
1949
1950	QCOMPARE( q.value( 0 ).toInt(), 40 );
1951
1952	QVERIFY_SQL( q, exec( "commit" ) );
1953
1954	QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 40" ) );
1955
1956	QVERIFY( q.next() );
1957
1958	QCOMPARE( q.value( 0 ).toInt(), 40 );
1959
1960	// test a rollback
1961	q.exec( query: startTransactionStr );
1962
1963	QVERIFY_SQL( q, exec( "insert into" + qtest + " values (41, 'VarChar41', 'Char41')" ) );
1964
1965	QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 41" ) );
1966
1967	QVERIFY( q.next() );
1968
1969	QCOMPARE( q.value( 0 ).toInt(), 41 );
1970
1971	if ( !q.exec( query: "rollback" ) ) {
1972	if (dbType == QSqlDriver::MySqlServer) {
1973	qDebug( msg: "MySQL: %s", qPrintable(tst_Databases::printError( q.lastError() ) ));
1974	QSKIP( "MySQL transaction failed "); //non-fatal
1975	} else
1976	QFAIL( "Could not rollback transaction: " + tst_Databases::printError( q.lastError() ) );
1977	}
1978
1979	QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 41" ) );
1980
1981	QVERIFY( q.next() == false );
1982
1983	// test concurrent access
1984	q.exec( query: startTransactionStr );
1985	QVERIFY_SQL( q, exec( "insert into" + qtest + " values (42, 'VarChar42', 'Char42')" ) );
1986	QVERIFY_SQL( q, exec( "select * from" + qtest + " where id = 42" ) );
1987	QVERIFY( q.next() );
1988	QCOMPARE( q.value( 0 ).toInt(), 42 );
1989
1990	QVERIFY_SQL( q2, exec( "select * from" + qtest + " where id = 42" ) );
1991
1992	if ( q2.next() )
1993	qDebug(msg: "DBMS '%s' doesn't support query based transactions with concurrent access",
1994	qPrintable(tst_Databases::dbToString( db )));
1995
1996	QVERIFY_SQL( q, exec( "commit" ) );
1997
1998	QVERIFY_SQL( q2, exec( "select * from" + qtest + " where id = 42" ) );
1999
2000	QVERIFY( q2.next() );
2001
2002	QCOMPARE( q2.value( 0 ).toInt(), 42 );
2003	}
2004
2005	void tst_QSqlQuery::joins()
2006	{
2007	QFETCH( QString, dbName );
2008	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2009	CHECK_DATABASE( db );
2010	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2011	const QString qtestj1(qTableName(prefix: "qtestj1", __FILE__, db)), qtestj2(qTableName(prefix: "qtestj2", __FILE__, db));
2012
2013	if (dbType == QSqlDriver::Oracle || dbType == QSqlDriver::Sybase
2014	|| dbType == QSqlDriver::Interbase || db.driverName().startsWith(s: "QODBC"))
2015	// Oracle broken beyond recognition - cannot outer join on more than
2016	// one table.
2017	QSKIP( "DBMS cannot understand standard SQL");
2018
2019	QSqlQuery q( db );
2020
2021	QVERIFY_SQL( q, exec( "create table " + qtestj1 + " (id1 int, id2 int)" ) );
2022	QVERIFY_SQL( q, exec( "create table " + qtestj2 + " (id int, name varchar(20))" ) );
2023	QVERIFY_SQL( q, exec( "insert into " + qtestj1 + " values (1, 1)" ) );
2024	QVERIFY_SQL( q, exec( "insert into " + qtestj1 + " values (1, 2)" ) );
2025	QVERIFY_SQL( q, exec( "insert into " + qtestj2 + " values(1, 'trenton')" ) );
2026	QVERIFY_SQL( q, exec( "insert into " + qtestj2 + " values(2, 'marius')" ) );
2027
2028	QVERIFY_SQL( q, exec( "select qtestj1.id1, qtestj1.id2, qtestj2.id, qtestj2.name, qtestj3.id, qtestj3.name "
2029	"from " + qtestj1 + " qtestj1 left outer join " + qtestj2 +
2030	" qtestj2 on (qtestj1.id1 = qtestj2.id) "
2031	"left outer join " + qtestj2 + " as qtestj3 on (qtestj1.id2 = qtestj3.id)" ) );
2032
2033	QVERIFY( q.next() );
2034	QCOMPARE( q.value( 0 ).toInt(), 1 );
2035	QCOMPARE( q.value( 1 ).toInt(), 1 );
2036	QCOMPARE( q.value( 2 ).toInt(), 1 );
2037	QCOMPARE( q.value( 3 ).toString(), QString( "trenton" ) );
2038	QCOMPARE( q.value( 4 ).toInt(), 1 );
2039	QCOMPARE( q.value( 5 ).toString(), QString( "trenton" ) );
2040
2041	QVERIFY( q.next() );
2042	QCOMPARE( q.value( 0 ).toInt(), 1 );
2043	QCOMPARE( q.value( 1 ).toInt(), 2 );
2044	QCOMPARE( q.value( 2 ).toInt(), 1 );
2045	QCOMPARE( q.value( 3 ).toString(), QString( "trenton" ) );
2046	QCOMPARE( q.value( 4 ).toInt(), 2 );
2047	QCOMPARE( q.value( 5 ).toString(), QString( "marius" ) );
2048	}
2049
2050	void tst_QSqlQuery::synonyms()
2051	{
2052	QFETCH( QString, dbName );
2053	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2054	CHECK_DATABASE( db );
2055
2056	QSqlQuery q(db);
2057	QVERIFY_SQL( q, exec("select a.id, a.t_char, a.t_varchar from " + qtest + " a where a.id = 1") );
2058	QVERIFY( q.next() );
2059	QCOMPARE( q.value( 0 ).toInt(), 1 );
2060	QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "Char1" ) );
2061	QCOMPARE( q.value( 2 ).toString().trimmed(), QString( "VarChar1" ) );
2062
2063	QSqlRecord rec = q.record();
2064	QCOMPARE(( int )rec.count(), 3 );
2065	QCOMPARE( rec.field( 0 ).name().toLower(), QString( "id" ) );
2066	QCOMPARE( rec.field( 1 ).name().toLower(), QString( "t_char" ) );
2067	QCOMPARE( rec.field( 2 ).name().toLower(), QString( "t_varchar" ) );
2068	}
2069
2070	// It doesn't make sense to split this into several tests
2071	void tst_QSqlQuery::prepare_bind_exec()
2072	{
2073	QFETCH( QString, dbName );
2074	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2075	CHECK_DATABASE( db );
2076	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2077	const QString qtest_prepare(qTableName(prefix: "qtest_prepare", __FILE__, db));
2078
2079	if (dbType == QSqlDriver::DB2)
2080	QSKIP("Needs someone with more Unicode knowledge than I have to fix");
2081
2082	{
2083	// new scope for SQLITE
2084	static const QString utf8str = QString::fromUtf8( str: "काचं शक्नोम्यत्तुम् । नोपहिनस्ति माम् ॥" );
2085
2086	static const QString values[6] = { "Harry", "Trond", "Mark", "Ma?rk", "?", ":id" };
2087
2088	bool useUnicode = db.driver()->hasFeature( f: QSqlDriver::Unicode );
2089
2090	QSqlQuery q( db );
2091	QString createQuery;
2092	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2093	if (dbType == QSqlDriver::PostgreSQL)
2094	QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
2095
2096	if (dbType == QSqlDriver::MSSqlServer || dbType == QSqlDriver::Sybase)
2097	createQuery = "create table " + qtest_prepare + " (id int primary key, name nvarchar(200) null, name2 nvarchar(200) null)";
2098	else if (dbType == QSqlDriver::MySqlServer && useUnicode)
2099	createQuery = "create table " + qtest_prepare + " (id int not null primary key, name varchar(200) character set utf8, name2 varchar(200) character set utf8)";
2100	else
2101	createQuery = "create table " + qtest_prepare + " (id int not null primary key, name varchar(200), name2 varchar(200))";
2102
2103	q.exec(query: "drop table " + qtest_prepare);
2104	QVERIFY_SQL( q, exec( createQuery ) );
2105
2106	QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (:id, :name)" ) );
2107	int i;
2108
2109	for ( i = 0; i < 6; ++i ) {
2110	q.bindValue( placeholder: ":name", val: values[i] );
2111	q.bindValue( placeholder: ":id", val: i );
2112	QVERIFY_SQL( q, exec() );
2113	QMap<QString, QVariant> m = q.boundValues();
2114	QCOMPARE(( int ) m.count(), 2 );
2115	QCOMPARE( m[":name"].toString(), values[i] );
2116	QCOMPARE( m[":id"].toInt(), i );
2117	}
2118
2119	q.bindValue( placeholder: ":id", val: 8 );
2120
2121	QVERIFY_SQL( q, exec() );
2122
2123	if ( useUnicode ) {
2124	q.bindValue( placeholder: ":id", val: 7 );
2125	q.bindValue( placeholder: ":name", val: utf8str );
2126	QVERIFY_SQL( q, exec() );
2127	}
2128
2129	QVERIFY_SQL( q, exec( "SELECT * FROM " + qtest_prepare + " order by id" ) );
2130
2131	for ( i = 0; i < 6; ++i ) {
2132	QVERIFY( q.next() );
2133	QCOMPARE( q.value( 0 ).toInt(), i );
2134	QCOMPARE( q.value( 1 ).toString().trimmed(), values[ i ] );
2135	}
2136
2137	if ( useUnicode ) {
2138	QVERIFY_SQL( q, next() );
2139	QCOMPARE( q.value( 0 ).toInt(), 7 );
2140	QCOMPARE( q.value( 1 ).toString(), utf8str );
2141	}
2142
2143	QVERIFY_SQL( q, next() );
2144
2145	QCOMPARE( q.value( 0 ).toInt(), 8 );
2146	QCOMPARE( q.value( 1 ).toString(), values[5] );
2147
2148	QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (:id, 'Bart')" ) );
2149	q.bindValue( placeholder: ":id", val: 99 );
2150	QVERIFY_SQL( q, exec() );
2151	q.bindValue( placeholder: ":id", val: 100 );
2152	QVERIFY_SQL( q, exec() );
2153	QVERIFY( q.exec( "select * from " + qtest_prepare + " where id > 98 order by id" ) );
2154
2155	for ( i = 99; i <= 100; ++i ) {
2156	QVERIFY( q.next() );
2157	QCOMPARE( q.value( 0 ).toInt(), i );
2158	QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "Bart" ) );
2159	}
2160
2161	/*** SELECT stuff ***/
2162	QVERIFY( q.prepare( "select * from " + qtest_prepare + " where id = :id" ) );
2163
2164	for ( i = 0; i < 6; ++i ) {
2165	q.bindValue( placeholder: ":id", val: i );
2166	QVERIFY_SQL( q, exec() );
2167	QVERIFY_SQL( q, next() );
2168	QCOMPARE( q.value( 0 ).toInt(), i );
2169	QCOMPARE( q.value( 1 ).toString().trimmed(), values[ i ] );
2170	QSqlRecord rInf = q.record();
2171	QCOMPARE(( int )rInf.count(), 3 );
2172	QCOMPARE( rInf.field( 0 ).name().toUpper(), QString( "ID" ) );
2173	QCOMPARE( rInf.field( 1 ).name().toUpper(), QString( "NAME" ) );
2174	QVERIFY( !q.next() );
2175	}
2176
2177	QVERIFY_SQL( q, exec( "DELETE FROM " + qtest_prepare ) );
2178
2179	/*** Below we test QSqlQuery::boundValues() with position arguments.
2180	* Due to the fact that the name of a positional argument is not
2181	* specified by the Qt docs, we only test that the QMap contains
2182	* the correct values and that QSqlResult::boundValueName returns
2183	* the key that corrosponds to the correct value. ***/
2184	QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (?, ?)" ) );
2185	q.bindValue( pos: 0, val: 0 );
2186	q.bindValue( pos: 1, val: values[ 0 ] );
2187	QCOMPARE( q.boundValues().size(), 2 );
2188	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 0);
2189	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[0]);
2190	QVERIFY_SQL( q, exec() );
2191	QCOMPARE( q.boundValues().size(), 2 );
2192	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 0);
2193	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[0]);
2194
2195	q.addBindValue( val: 1 );
2196	q.addBindValue( val: values[ 1 ] );
2197	QCOMPARE( q.boundValues().size(), 2 );
2198	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 1);
2199	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[1]);
2200	QVERIFY_SQL( q, exec() );
2201	QCOMPARE( q.boundValues().size(), 2 );
2202	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 1);
2203	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[1]);
2204
2205	q.addBindValue( val: 2 );
2206	q.addBindValue( val: values[ 2 ] );
2207	QCOMPARE( q.boundValues().size(), 2 );
2208	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 2);
2209	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[2]);
2210	QVERIFY_SQL( q, exec() );
2211	QCOMPARE( q.boundValues().size(), 2 );
2212	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 2);
2213	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[2]);
2214
2215	q.addBindValue( val: 3 );
2216	q.addBindValue( val: values[ 3 ] );
2217	QCOMPARE( q.boundValues().size(), 2 );
2218	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 3);
2219	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[3]);
2220	QVERIFY_SQL( q, exec() );
2221	QCOMPARE( q.boundValues().size(), 2 );
2222	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 3);
2223	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[3]);
2224
2225	q.addBindValue( val: 4 );
2226	q.addBindValue( val: values[ 4 ] );
2227	QCOMPARE( q.boundValues().size(), 2 );
2228	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 4);
2229	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[4]);
2230	QVERIFY_SQL( q, exec() );
2231	QCOMPARE( q.boundValues().size(), 2 );
2232	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 4);
2233	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[4]);
2234
2235	q.bindValue( pos: 1, val: values[ 5 ] );
2236	q.bindValue( pos: 0, val: 5 );
2237	QCOMPARE( q.boundValues().size(), 2 );
2238	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 5);
2239	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[5]);
2240	QVERIFY_SQL( q, exec() );
2241	QCOMPARE( q.boundValues().size(), 2 );
2242	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 5);
2243	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), values[5]);
2244
2245	q.bindValue( pos: 0, val: 6 );
2246	q.bindValue( pos: 1, val: QString() );
2247	QCOMPARE( q.boundValues().size(), 2 );
2248	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 6);
2249	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), QString());
2250	QVERIFY_SQL( q, exec() );
2251	QCOMPARE( q.boundValues().size(), 2 );
2252	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 6);
2253	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), QString());
2254
2255	if ( db.driver()->hasFeature( f: QSqlDriver::Unicode ) ) {
2256	q.bindValue( pos: 0, val: 7 );
2257	q.bindValue( pos: 1, val: utf8str );
2258	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 7);
2259	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), utf8str);
2260	QVERIFY_SQL( q, exec() );
2261	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 7);
2262	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toString(), utf8str);
2263	}
2264
2265	// Test binding more placeholders than the query contains placeholders
2266	q.addBindValue(val: 8);
2267	q.addBindValue(val: 9);
2268	q.addBindValue(val: 10);
2269	QCOMPARE(q.boundValues().size(), 3);
2270	QCOMPARE(q.boundValues()[q.result()->boundValueName(0)].toInt(), 8);
2271	QCOMPARE(q.boundValues()[q.result()->boundValueName(1)].toInt(), 9);
2272	QCOMPARE(q.boundValues()[q.result()->boundValueName(2)].toInt(), 10);
2273	QFAIL_SQL(q, exec());
2274
2275	QVERIFY_SQL( q, exec( "SELECT * FROM " + qtest_prepare + " order by id" ) );
2276
2277	for ( i = 0; i < 6; ++i ) {
2278	QVERIFY( q.next() );
2279	QCOMPARE( q.value( 0 ).toInt(), i );
2280	QCOMPARE( q.value( 1 ).toString().trimmed(), values[ i ] );
2281	}
2282
2283	QVERIFY( q.next() );
2284
2285	QCOMPARE( q.value( 0 ).toInt(), 6 );
2286	QVERIFY( q.isNull( 1 ) );
2287
2288	if ( useUnicode ) {
2289	QVERIFY( q.next() );
2290	QCOMPARE( q.value( 0 ).toInt(), 7 );
2291	QCOMPARE( q.value( 1 ).toString(), utf8str );
2292	}
2293
2294	QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (?, 'Bart')" ) );
2295
2296	q.bindValue( pos: 0, val: 99 );
2297	QVERIFY_SQL( q, exec() );
2298	q.addBindValue( val: 100 );
2299	QVERIFY_SQL( q, exec() );
2300	QVERIFY( q.exec( "select * from " + qtest_prepare + " where id > 98 order by id" ) );
2301
2302	for ( i = 99; i <= 100; ++i ) {
2303	QVERIFY( q.next() );
2304	QCOMPARE( q.value( 0 ).toInt(), i );
2305	QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "Bart" ) );
2306	}
2307
2308	/* insert a duplicate id and make sure the db bails out */
2309	QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name) values (?, ?)" ) );
2310
2311	q.addBindValue( val: 99 );
2312
2313	q.addBindValue( val: "something silly" );
2314
2315	QVERIFY( !q.exec() );
2316
2317	QVERIFY( q.lastError().isValid() );
2318
2319	QVERIFY( !q.isActive() );
2320
2321	QVERIFY( q.prepare( "insert into " + qtest_prepare + " (id, name, name2) values (:id, :name, :name)" ) );
2322	for ( i = 101; i < 103; ++i ) {
2323	q.bindValue( placeholder: ":id", val: i );
2324	q.bindValue( placeholder: ":name", val: "name" );
2325	QVERIFY( q.exec() );
2326	}
2327
2328	// Test for QTBUG-6420
2329	QVERIFY( q.exec( "select * from " + qtest_prepare + " where id > 100 order by id" ) );
2330	QVERIFY( q.next() );
2331	QCOMPARE( q.value(0).toInt(), 101 );
2332	QCOMPARE( q.value(1).toString(), QString("name") );
2333	QCOMPARE( q.value(2).toString(), QString("name") );
2334
2335	// Test that duplicated named placeholders before the next unique one works correctly - QTBUG-65150
2336	QVERIFY(q.prepare("insert into " + qtest_prepare + " (id, name, name2) values (:id, :id, :name)"));
2337	for (i = 104; i < 106; ++i) {
2338	q.bindValue(placeholder: ":id", val: i);
2339	q.bindValue(placeholder: ":name", val: "name");
2340	QVERIFY(q.exec());
2341	}
2342	QVERIFY(q.exec("select * from " + qtest_prepare + " where id > 103 order by id"));
2343	QVERIFY(q.next());
2344	QCOMPARE(q.value(0).toInt(), 104);
2345	QCOMPARE(q.value(1).toString(), QString("104"));
2346	QCOMPARE(q.value(2).toString(), QString("name"));
2347
2348	// Test that duplicated named placeholders in any order
2349	QVERIFY(q.prepare("insert into " + qtest_prepare + " (id, name, name2) values (:id, :name, :id)"));
2350	for (i = 107; i < 109; ++i) {
2351	q.bindValue(placeholder: ":id", val: i);
2352	q.bindValue(placeholder: ":name", val: "name");
2353	QVERIFY(q.exec());
2354	}
2355	QVERIFY(q.exec("select * from " + qtest_prepare + " where id > 106 order by id"));
2356	QVERIFY(q.next());
2357	QCOMPARE(q.value(0).toInt(), 107);
2358	QCOMPARE(q.value(1).toString(), QString("name"));
2359	QCOMPARE(q.value(2).toString(), QString("107"));
2360
2361	// Test just duplicated placeholders
2362	QVERIFY(q.prepare("insert into " + qtest_prepare + " (id, name, name2) values (110, :name, :name)"));
2363	q.bindValue(placeholder: ":name", val: "name");
2364	QVERIFY_SQL(q, exec());
2365	QVERIFY(q.exec("select * from " + qtest_prepare + " where id > 109 order by id"));
2366	QVERIFY(q.next());
2367	QCOMPARE(q.value(0).toInt(), 110);
2368	QCOMPARE(q.value(1).toString(), QString("name"));
2369	QCOMPARE(q.value(2).toString(), QString("name"));
2370	} // end of SQLite scope
2371	}
2372
2373	void tst_QSqlQuery::prepared_select()
2374	{
2375	QFETCH( QString, dbName );
2376	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2377	CHECK_DATABASE( db );
2378
2379	QSqlQuery q( db );
2380	QVERIFY_SQL( q, prepare( "select a.id, a.t_char, a.t_varchar from " + qtest + " a where a.id = ?" ) );
2381
2382	q.bindValue( pos: 0, val: 1 );
2383	QVERIFY_SQL( q, exec() );
2384	QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
2385	QVERIFY( q.next() );
2386	QCOMPARE( q.value( 0 ).toInt(), 1 );
2387
2388	q.bindValue( pos: 0, val: 2 );
2389	QVERIFY_SQL( q, exec() );
2390	QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
2391	QVERIFY( q.next() );
2392	QCOMPARE( q.value( 0 ).toInt(), 2 );
2393
2394	q.bindValue( pos: 0, val: 3 );
2395	QVERIFY_SQL( q, exec() );
2396	QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
2397	QVERIFY( q.next() );
2398	QCOMPARE( q.value( 0 ).toInt(), 3 );
2399
2400	QVERIFY_SQL( q, prepare( "select a.id, a.t_char, a.t_varchar from " + qtest + " a where a.id = ?" ) );
2401	QCOMPARE( q.at(), ( int )QSql::BeforeFirstRow );
2402	QVERIFY( !q.first() );
2403	}
2404
2405	void tst_QSqlQuery::sqlServerLongStrings()
2406	{
2407	QFETCH( QString, dbName );
2408	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2409	CHECK_DATABASE( db );
2410
2411	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2412	if (dbType != QSqlDriver::MSSqlServer)
2413	QSKIP( "SQL Server specific test");
2414
2415	QSqlQuery q( db );
2416
2417	QVERIFY_SQL(q, exec("CREATE TABLE " + qTableName("qtest_longstr", __FILE__, db) + " (id int primary key, longstring ntext)"));
2418
2419	QVERIFY_SQL(q, prepare("INSERT INTO " + qTableName("qtest_longstr", __FILE__, db) + " VALUES (?, ?)"));
2420
2421	q.addBindValue( val: 0 );
2422
2423	q.addBindValue( val: QString::fromLatin1( str: "bubu" ) );
2424
2425	QVERIFY_SQL( q, exec() );
2426
2427	QString testStr;
2428
2429	testStr.fill( c: QLatin1Char( 'a' ), size: 85000 );
2430
2431	q.addBindValue( val: 1 );
2432
2433	q.addBindValue( val: testStr );
2434
2435	QVERIFY_SQL( q, exec() );
2436
2437	QVERIFY_SQL(q, exec("select * from " + qTableName( "qtest_longstr", __FILE__, db)));
2438
2439	QVERIFY_SQL( q, next() );
2440
2441	QCOMPARE( q.value( 0 ).toInt(), 0 );
2442
2443	QCOMPARE( q.value( 1 ).toString(), QString::fromLatin1( "bubu" ) );
2444
2445	QVERIFY_SQL( q, next() );
2446
2447	QCOMPARE( q.value( 0 ).toInt(), 1 );
2448
2449	QCOMPARE( q.value( 1 ).toString(), testStr );
2450	}
2451
2452	void tst_QSqlQuery::invalidQuery()
2453	{
2454	QFETCH( QString, dbName );
2455	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2456	CHECK_DATABASE( db );
2457	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2458	QSqlQuery q( db );
2459
2460	QVERIFY( !q.exec() );
2461
2462	QVERIFY( !q.exec( "blahfasel" ) );
2463	QVERIFY( q.lastError().type() != QSqlError::NoError );
2464	QVERIFY( !q.next() );
2465	QVERIFY( !q.isActive() );
2466
2467	if (dbType != QSqlDriver::Oracle && dbType != QSqlDriver::DB2 && !db.driverName().startsWith(s: "QODBC")) {
2468	// oracle and db2 just prepares everything without complaining
2469	if ( db.driver()->hasFeature( f: QSqlDriver::PreparedQueries ) )
2470	QVERIFY( !q.prepare( "blahfasel" ) );
2471	}
2472
2473	QVERIFY( !q.exec() );
2474
2475	QVERIFY( !q.isActive() );
2476	QVERIFY( !q.next() );
2477	}
2478
2479	void tst_QSqlQuery::batchExec()
2480	{
2481	QFETCH( QString, dbName );
2482	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2483	CHECK_DATABASE( db );
2484
2485	QSqlQuery q( db );
2486	const QString tableName = qTableName(prefix: "qtest_batch", __FILE__, db);
2487	tst_Databases::safeDropTable(db, tableName);
2488	QVERIFY_SQL(q, exec(QStringLiteral("create table ") + tableName +
2489	QStringLiteral(" (id int, name varchar(20), dt date, num numeric(8, 4), "
2490	"dtstamp TIMESTAMP(3), extraId int, extraName varchar(20))")));
2491
2492	const QVariantList intCol = { 1, 2, QVariant(QVariant::Int) };
2493	const QVariantList charCol = { QStringLiteral("harald"), QStringLiteral("boris"),
2494	QVariant(QVariant::String) };
2495	const QDateTime currentDateTime = QDateTime(QDateTime::currentDateTime());
2496	const QVariantList dateCol = { currentDateTime.date(), currentDateTime.date().addDays(days: -1),
2497	QVariant(QVariant::Date) };
2498	const QVariantList numCol = { 2.3, 3.4, QVariant(QVariant::Double) };
2499	const QVariantList timeStampCol = { currentDateTime, currentDateTime.addDays(days: -1),
2500	QVariant(QVariant::DateTime) };
2501
2502	// Test with positional placeholders
2503	QVERIFY_SQL(q, prepare(QStringLiteral("insert into ") + tableName +
2504	QStringLiteral(" (id, name, dt, num, dtstamp, extraId, extraName) values "
2505	"(?, ?, ?, ?, ?, ?, ?)")));
2506	q.addBindValue(val: intCol);
2507	q.addBindValue( val: charCol );
2508	q.addBindValue( val: dateCol );
2509	q.addBindValue( val: numCol );
2510	q.addBindValue(val: timeStampCol);
2511	q.addBindValue(val: intCol);
2512	q.addBindValue(val: charCol);
2513
2514	QVERIFY_SQL( q, execBatch() );
2515	QVERIFY_SQL(q, exec(QStringLiteral("select id, name, dt, num, dtstamp, "
2516	"extraId, extraName from ") + tableName));
2517
2518	for (int i = 0; i < intCol.size(); ++i) {
2519	QVERIFY(q.next());
2520	QCOMPARE(q.value(0).toInt(), intCol.at(i));
2521	QCOMPARE(q.value(1).toString(), charCol.at(i));
2522	QCOMPARE(q.value(2).toDate(), dateCol.at(i));
2523	QCOMPARE(q.value(3).toDouble(), numCol.at(i));
2524	if (tst_Databases::getDatabaseType(db) == QSqlDriver::MySqlServer && timeStampCol.at(i).isNull()) {
2525	QEXPECT_FAIL("", "This appears to be a bug in MySQL as it converts null datetimes to the "
2526	"current datetime for a timestamp field", Continue);
2527	}
2528	QCOMPARE(q.value(4).toDateTime(), timeStampCol.at(i));
2529	QCOMPARE(q.value(5).toInt(), intCol.at(i));
2530	QCOMPARE(q.value(6).toString(), charCol.at(i));
2531	}
2532
2533	// Empty table ready for retesting with duplicated named placeholders
2534	QVERIFY_SQL(q, exec(QStringLiteral("delete from ") + tableName));
2535	QVERIFY_SQL(q, prepare(QStringLiteral("insert into ") + tableName +
2536	QStringLiteral(" (id, name, dt, num, dtstamp, extraId, extraName) "
2537	"values (:id, :name, :dt, :num, :dtstamp, :id, :name)")));
2538	q.bindValue(placeholder: ":id", val: intCol);
2539	q.bindValue(placeholder: ":name", val: charCol);
2540	q.bindValue(placeholder: ":dt", val: dateCol);
2541	q.bindValue(placeholder: ":num", val: numCol);
2542	q.bindValue(placeholder: ":dtstamp", val: timeStampCol);
2543
2544	QVERIFY_SQL(q, execBatch());
2545	QVERIFY_SQL(q, exec(QStringLiteral("select id, name, dt, num, dtstamp, extraId, extraName from ") +
2546	tableName));
2547
2548	for (int i = 0; i < intCol.size(); ++i) {
2549	QVERIFY(q.next());
2550	QCOMPARE(q.value(0).toInt(), intCol.at(i));
2551	QCOMPARE(q.value(1).toString(), charCol.at(i));
2552	QCOMPARE(q.value(2).toDate(), dateCol.at(i));
2553	QCOMPARE(q.value(3).toDouble(), numCol.at(i));
2554	if (tst_Databases::getDatabaseType(db) == QSqlDriver::MySqlServer && timeStampCol.at(i).isNull()) {
2555	QEXPECT_FAIL("", "This appears to be a bug in MySQL as it converts null datetimes to the "
2556	"current datetime for a timestamp field", Continue);
2557	}
2558	QCOMPARE(q.value(4).toDateTime(), timeStampCol.at(i));
2559	QCOMPARE(q.value(5).toInt(), intCol.at(i));
2560	QCOMPARE(q.value(6).toString(), charCol.at(i));
2561	}
2562
2563	// Only test the prepared stored procedure approach where the driver has support
2564	// for batch operations as this will not work without it
2565	if (db.driver()->hasFeature(f: QSqlDriver::BatchOperations)) {
2566	const QString procName = qTableName(prefix: "qtest_batch_proc", __FILE__, db);
2567	QVERIFY_SQL(q, exec("create or replace procedure " + procName +
2568	" (x in timestamp, y out timestamp) is\n"
2569	"begin\n"
2570	" y := x;\n"
2571	"end;\n"));
2572	QVERIFY(q.prepare("call " + procName + "(?, ?)"));
2573	q.addBindValue(val: timeStampCol, type: QSql::In);
2574	QVariantList emptyDateTimes;
2575	emptyDateTimes.reserve(alloc: timeStampCol.size());
2576	for (int i = 0; i < timeStampCol.size(); i++)
2577	emptyDateTimes << QVariant(QDateTime());
2578	q.addBindValue(val: emptyDateTimes, type: QSql::Out);
2579	QVERIFY_SQL(q, execBatch());
2580	QCOMPARE(q.boundValue(1).toList(), timeStampCol);
2581	}
2582	}
2583
2584	void tst_QSqlQuery::QTBUG_43874()
2585	{
2586	QFETCH(QString, dbName);
2587	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2588	CHECK_DATABASE(db);
2589
2590	QSqlQuery q(db);
2591	const QString tableName = qTableName(prefix: "bug43874", __FILE__, db);
2592
2593	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INT)"));
2594	QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (id) VALUES (?)"));
2595
2596	for (int i = 0; i < 2; ++i) {
2597	QVariantList ids;
2598	ids << i;
2599	q.addBindValue(val: ids);
2600	QVERIFY_SQL(q, execBatch());
2601	}
2602	QVERIFY_SQL(q, exec("SELECT id FROM " + tableName + " ORDER BY id"));
2603
2604	QVERIFY(q.next());
2605	QCOMPARE(q.value(0).toInt(), 0);
2606
2607	QVERIFY(q.next());
2608	QCOMPARE(q.value(0).toInt(), 1);
2609	}
2610
2611	void tst_QSqlQuery::oraArrayBind()
2612	{
2613	QFETCH( QString, dbName );
2614	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2615	CHECK_DATABASE( db );
2616
2617	if ( !db.driver()->hasFeature( f: QSqlDriver::BatchOperations ) )
2618	QSKIP( "Database can't do BatchOperations");
2619
2620	QSqlQuery q( db );
2621
2622	QVERIFY_SQL( q, exec( "CREATE OR REPLACE PACKAGE ora_array_test "
2623	"IS "
2624	"TYPE names_type IS TABLE OF VARCHAR(64) NOT NULL INDEX BY BINARY_INTEGER; "
2625	"names_tab names_type; "
2626	"PROCEDURE set_name(name_in IN VARCHAR2, row_in in INTEGER); "
2627	"PROCEDURE get_name(row_in IN INTEGER, str_out OUT VARCHAR2); "
2628	"PROCEDURE get_table(tbl OUT names_type); "
2629	"PROCEDURE set_table(tbl IN names_type); "
2630	"END ora_array_test; " ) );
2631
2632	QVERIFY_SQL( q, exec( "CREATE OR REPLACE PACKAGE BODY ora_array_test "
2633	"IS "
2634	"PROCEDURE set_name(name_in IN VARCHAR2, row_in in INTEGER) "
2635	"IS "
2636	"BEGIN "
2637	"names_tab(row_in) := name_in; "
2638	"END set_name; "
2639
2640	"PROCEDURE get_name(row_in IN INTEGER, str_out OUT VARCHAR2) "
2641	"IS "
2642	"BEGIN "
2643	"str_out := names_tab(row_in); "
2644	"END get_name; "
2645
2646	"PROCEDURE get_table(tbl OUT names_type) "
2647	"IS "
2648	"BEGIN "
2649	"tbl:=names_tab; "
2650	"END get_table; "
2651
2652	"PROCEDURE set_table(tbl IN names_type) "
2653	"IS "
2654	"BEGIN "
2655	"names_tab := tbl; "
2656	"END set_table; "
2657	"END ora_array_test; " ) );
2658
2659	QVariantList list;
2660
2661	list << QString( "lorem" ) << QString( "ipsum" ) << QString( "dolor" ) << QString( "sit" ) << QString( "amet" );
2662
2663	QVERIFY_SQL( q, prepare( "BEGIN "
2664	"ora_array_test.set_table(?); "
2665	"END;" ) );
2666
2667	q.bindValue( pos: 0, val: list, type: QSql::In );
2668
2669	QVERIFY_SQL( q, execBatch( QSqlQuery::ValuesAsColumns ) );
2670
2671	QVERIFY_SQL( q, prepare( "BEGIN "
2672	"ora_array_test.get_table(?); "
2673	"END;" ) );
2674
2675	list.clear();
2676
2677	list << QString( 64,' ' ) << QString( 64,' ' ) << QString( 64,' ' ) << QString( 64,' ' ) << QString( 64,' ' );
2678
2679	q.bindValue( pos: 0, val: list, type: QSql::Out );
2680
2681	QVERIFY_SQL( q, execBatch( QSqlQuery::ValuesAsColumns ) );
2682
2683	QVariantList out_list = q.boundValue( pos: 0 ).toList();
2684
2685	QCOMPARE( out_list.at( 0 ).toString(), QString( "lorem" ) );
2686
2687	QCOMPARE( out_list.at( 1 ).toString(), QString( "ipsum" ) );
2688
2689	QCOMPARE( out_list.at( 2 ).toString(), QString( "dolor" ) );
2690
2691	QCOMPARE( out_list.at( 3 ).toString(), QString( "sit" ) );
2692
2693	QCOMPARE( out_list.at( 4 ).toString(), QString( "amet" ) );
2694
2695	QVERIFY_SQL( q, exec( "DROP PACKAGE ora_array_test" ) );
2696	}
2697
2698	/*
2699	Tests that QSqlDatabase::record and QSqlQuery::record returns the same thing
2700	otherwise our models get confused.
2701	*/
2702	void tst_QSqlQuery::record_sqlite()
2703	{
2704	QFETCH( QString, dbName );
2705	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2706	CHECK_DATABASE( db );
2707
2708	QSqlQuery q( db );
2709
2710	QVERIFY_SQL(q, exec("create table " + qTableName("record_sqlite", __FILE__, db) + "(id integer primary key, name varchar, title int)"));
2711
2712	QSqlRecord rec = db.record(tablename: qTableName(prefix: "record_sqlite", __FILE__, db));
2713
2714	QCOMPARE( rec.count(), 3 );
2715	QCOMPARE( rec.field( 0 ).type(), QVariant::Int );
2716	QCOMPARE( rec.field( 1 ).type(), QVariant::String );
2717	QCOMPARE( rec.field( 2 ).type(), QVariant::Int );
2718
2719	/* important - select from an empty table */
2720	QVERIFY_SQL(q, exec("select id, name, title from " + qTableName("record_sqlite", __FILE__, db)));
2721
2722	rec = q.record();
2723	QCOMPARE( rec.count(), 3 );
2724	QCOMPARE( rec.field( 0 ).type(), QVariant::Int );
2725	QCOMPARE( rec.field( 1 ).type(), QVariant::String );
2726	QCOMPARE( rec.field( 2 ).type(), QVariant::Int );
2727	}
2728
2729	void tst_QSqlQuery::oraLong()
2730	{
2731	QFETCH( QString, dbName );
2732	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2733	CHECK_DATABASE( db );
2734
2735	QSqlQuery q( db );
2736
2737	QString aLotOfText( 127000, QLatin1Char( 'H' ) );
2738
2739	QVERIFY_SQL(q, exec("create table " + qTableName("qtest_longstr", __FILE__, db) + " (id int primary key, astr long)"));
2740	QVERIFY_SQL(q, prepare("insert into " + qTableName("qtest_longstr", __FILE__, db) + " (id, astr) values (?, ?)"));
2741	q.addBindValue( val: 1 );
2742	q.addBindValue( val: aLotOfText );
2743	QVERIFY_SQL( q, exec() );
2744
2745	QVERIFY_SQL(q, exec("select id,astr from " + qTableName("qtest_longstr", __FILE__, db)));
2746
2747	QVERIFY( q.next() );
2748	QCOMPARE( q.value( 0 ).toInt(), 1 );
2749	QCOMPARE( q.value( 1 ).toString(), aLotOfText );
2750	}
2751
2752	void tst_QSqlQuery::execErrorRecovery()
2753	{
2754	QFETCH( QString, dbName );
2755	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2756	CHECK_DATABASE( db );
2757
2758	QSqlQuery q( db );
2759
2760	const QString tbl = qTableName(prefix: "qtest_exerr", __FILE__, db);
2761	q.exec(query: "drop table " + tbl);
2762	QVERIFY_SQL(q, exec("create table " + tbl + " (id int not null primary key)"));
2763	QVERIFY_SQL(q, prepare("insert into " + tbl + " values (?)" ));
2764
2765	q.addBindValue( val: 1 );
2766	QVERIFY_SQL( q, exec() );
2767
2768	q.addBindValue( val: 1 ); // binding the same pkey - should fail
2769	QVERIFY( !q.exec() );
2770
2771	q.addBindValue( val: 2 ); // this should work again
2772	QVERIFY_SQL( q, exec() );
2773	}
2774
2775	void tst_QSqlQuery::prematureExec()
2776	{
2777	QFETCH(QString, dbName);
2778	// We only want the engine name, for addDatabase():
2779	int cut = dbName.indexOf(c: QChar('@'));
2780	if (cut < 0)
2781	QSKIP("Failed to parse database type out of name");
2782	dbName.truncate(pos: cut);
2783	cut = dbName.indexOf(c: QChar('_'));
2784	if (cut >= 0)
2785	dbName = dbName.mid(position: cut + 1);
2786
2787	auto db = QSqlDatabase::addDatabase(type: dbName);
2788	QSqlQuery q(db);
2789
2790	QTest::ignoreMessage(type: QtWarningMsg,
2791	message: "QSqlDatabasePrivate::removeDatabase: connection "
2792	"'qt_sql_default_connection' is still in use, all "
2793	"queries will cease to work.");
2794	QTest::ignoreMessage(type: QtWarningMsg,
2795	message: "QSqlDatabasePrivate::addDatabase: duplicate connection name "
2796	"'qt_sql_default_connection', old connection removed.");
2797	auto otherDb = QSqlDatabase::addDatabase(type: dbName);
2798
2799	QTest::ignoreMessage(type: QtWarningMsg, message: "QSqlQuery::exec: called before driver has been set up");
2800	// QTBUG-100037: shouldn't crash !
2801	QVERIFY(!q.exec("select stuff from TheVoid"));
2802	}
2803
2804	void tst_QSqlQuery::lastInsertId()
2805	{
2806	QFETCH( QString, dbName );
2807	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2808	CHECK_DATABASE( db );
2809
2810	if ( !db.driver()->hasFeature( f: QSqlDriver::LastInsertId ) )
2811	QSKIP( "Database doesn't support lastInsertId");
2812
2813	QSqlQuery q( db );
2814
2815	// PostgreSQL >= 8.1 relies on lastval() which does not work if a value is
2816	// manually inserted to the serial field, so we create a table specifically
2817	if (tst_Databases::getDatabaseType(db) == QSqlDriver::PostgreSQL) {
2818	const auto tst_lastInsertId = qTableName(prefix: "tst_lastInsertId", __FILE__, db);
2819	tst_Databases::safeDropTable(db, tableName: tst_lastInsertId);
2820	QVERIFY_SQL(q, exec(QStringLiteral("create table ") + tst_lastInsertId +
2821	QStringLiteral(" (id serial not null, t_varchar "
2822	"varchar(20), t_char char(20), primary key(id))")));
2823	QVERIFY_SQL(q, exec(QStringLiteral("insert into ") + tst_lastInsertId +
2824	QStringLiteral(" (t_varchar, t_char) values "
2825	"('VarChar41', 'Char41')")));
2826	} else {
2827	QVERIFY_SQL(q, exec(QStringLiteral("insert into ") + qtest +
2828	QStringLiteral(" values (41, 'VarChar41', 'Char41')")));
2829	}
2830	QVariant v = q.lastInsertId();
2831
2832	QVERIFY( v.isValid() );
2833	}
2834
2835	void tst_QSqlQuery::lastQuery()
2836	{
2837	QFETCH( QString, dbName );
2838	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2839	CHECK_DATABASE( db );
2840
2841	QSqlQuery q( db );
2842	QString sql = "select * from " + qtest;
2843	QVERIFY_SQL( q, exec( sql ) );
2844	QCOMPARE( q.lastQuery(), sql );
2845	QCOMPARE( q.executedQuery(), sql );
2846	}
2847
2848	void tst_QSqlQuery::lastQueryTwoQueries()
2849	{
2850	QFETCH(QString, dbName);
2851	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2852	CHECK_DATABASE(db);
2853
2854	QSqlQuery q(db);
2855
2856	QString sql = QLatin1String("select * from ") + qtest;
2857	QVERIFY_SQL(q, exec(sql));
2858	QCOMPARE(q.lastQuery(), sql);
2859	QCOMPARE(q.executedQuery(), sql);
2860
2861	sql = QLatin1String("select id from ") + qtest;
2862	QVERIFY_SQL(q, exec(sql));
2863	QCOMPARE(q.lastQuery(), sql);
2864	QCOMPARE(q.executedQuery(), sql);
2865	}
2866
2867	void tst_QSqlQuery::psql_bindWithDoubleColonCastOperator()
2868	{
2869	QFETCH( QString, dbName );
2870	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2871	CHECK_DATABASE( db );
2872
2873	const QString tablename(qTableName(prefix: "bindtest", __FILE__, db));
2874
2875	QSqlQuery q( db );
2876
2877	QVERIFY_SQL( q, exec( "create table " + tablename + " (id1 int, id2 int, id3 int, fld1 int, fld2 int)" ) );
2878	QVERIFY_SQL( q, exec( "insert into " + tablename + " values (1, 2, 3, 10, 5)" ) );
2879
2880	QVERIFY_SQL( q, prepare( "select sum((fld1 - fld2)::int) from " + tablename + " where id1 = :myid1 and id2 =:myid2 and id3=:myid3" ) );
2881	q.bindValue( placeholder: ":myid1", val: 1 );
2882	q.bindValue( placeholder: ":myid2", val: 2 );
2883	q.bindValue( placeholder: ":myid3", val: 3 );
2884
2885	QVERIFY_SQL( q, exec() );
2886	QVERIFY_SQL( q, next() );
2887
2888	// the positional placeholders are converted to named placeholders in executedQuery()
2889	if (db.driver()->hasFeature(f: QSqlDriver::PreparedQueries))
2890	QCOMPARE(q.executedQuery(), QString("select sum((fld1 - fld2)::int) from " + tablename + " where id1 = :myid1 and id2 =:myid2 and id3=:myid3"));
2891	else
2892	QCOMPARE(q.executedQuery(), QString("select sum((fld1 - fld2)::int) from " + tablename + " where id1 = 1 and id2 =2 and id3=3"));
2893	}
2894
2895	void tst_QSqlQuery::psql_specialFloatValues()
2896	{
2897	if (!std::numeric_limits<float>::has_quiet_NaN)
2898	QSKIP("Platform does not have quiet_NaN");
2899	if (!std::numeric_limits<float>::has_infinity)
2900	QSKIP("Platform does not have infinity");
2901
2902	QFETCH( QString, dbName );
2903	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2904
2905	CHECK_DATABASE( db );
2906	QSqlQuery query(db);
2907	const QString tableName = qTableName(prefix: "floattest", __FILE__, db);
2908	QVERIFY_SQL( query, exec("create table " + tableName + " (value float)" ) );
2909	QVERIFY_SQL(query, prepare("insert into " + tableName + " values(:value)") );
2910
2911	QVariantList data;
2912	data << QVariant(double(42.42))
2913	<< QVariant(std::numeric_limits<double>::quiet_NaN())
2914	<< QVariant(std::numeric_limits<double>::infinity())
2915	<< QVariant(float(42.42))
2916	<< QVariant(std::numeric_limits<float>::quiet_NaN())
2917	<< QVariant(std::numeric_limits<float>::infinity());
2918
2919	foreach (const QVariant &v, data) {
2920	query.bindValue(placeholder: ":value", val: v);
2921	QVERIFY_SQL( query, exec() );
2922	}
2923
2924	QVERIFY_SQL( query, exec("drop table " + tableName) );
2925	}
2926
2927	/* For task 157397: Using QSqlQuery with an invalid QSqlDatabase
2928	does not set the last error of the query.
2929	This test function will output some warnings, that's ok.
2930	*/
2931	void tst_QSqlQuery::queryOnInvalidDatabase()
2932	{
2933	{
2934	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlDatabase: INVALID driver not loaded" );
2935	QSqlDatabase db = QSqlDatabase::addDatabase( type: "INVALID", connectionName: "invalidConnection" );
2936	QVERIFY2( db.lastError().isValid(),
2937	qPrintable( QString( "db.lastError().isValid() should be true!" ) ) );
2938
2939	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlQuery::exec: database not open" );
2940	QSqlQuery query( "SELECT 1 AS ID", db );
2941	QVERIFY2( query.lastError().isValid(),
2942	qPrintable( QString( "query.lastError().isValid() should be true!" ) ) );
2943	}
2944
2945	QSqlDatabase::removeDatabase( connectionName: "invalidConnection" );
2946
2947	{
2948	QSqlDatabase db = QSqlDatabase::database( connectionName: "this connection does not exist" );
2949	QTest::ignoreMessage( type: QtWarningMsg, message: "QSqlQuery::exec: database not open" );
2950	QSqlQuery query( "SELECT 1 AS ID", db );
2951	QVERIFY2( query.lastError().isValid(),
2952	qPrintable( QString( "query.lastError().isValid() should be true!" ) ) );
2953	}
2954	}
2955
2956	/* For task 159138: Error on instantiating a sql-query before explicitly
2957	opening the database. This is something we don't support, so this isn't
2958	really a bug. However some of the drivers are nice enough to support it.
2959	*/
2960	void tst_QSqlQuery::createQueryOnClosedDatabase()
2961	{
2962	QFETCH( QString, dbName );
2963	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2964	CHECK_DATABASE( db );
2965	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2966	// Only supported by these drivers
2967
2968	if (dbType != QSqlDriver::PostgreSQL && dbType != QSqlDriver::Oracle
2969	&& dbType != QSqlDriver::MySqlServer && dbType != QSqlDriver::DB2)
2970	QSKIP( "Test is specific for PostgreSQL, Oracle, MySql and DB2");
2971
2972	db.close();
2973
2974	QSqlQuery q( db );
2975	db.open();
2976	QVERIFY_SQL( q, exec( QString( "select * from %1 where id = 1" ).arg( qtest ) ) );
2977
2978	QVERIFY_SQL( q, next() );
2979	QCOMPARE( q.value( 0 ).toInt(), 1 );
2980	QCOMPARE( q.value( 1 ).toString().trimmed(), QLatin1String( "VarChar1" ) );
2981	QCOMPARE( q.value( 2 ).toString().trimmed(), QLatin1String( "Char1" ) );
2982
2983	db.close();
2984	QVERIFY2( !q.exec( QString( "select * from %1 where id = 1" ).arg( qtest ) ),
2985	qPrintable( QString( "This can't happen! The query should not have been executed!" ) ) );
2986	}
2987
2988	void tst_QSqlQuery::reExecutePreparedForwardOnlyQuery()
2989	{
2990	QFETCH( QString, dbName );
2991	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
2992	CHECK_DATABASE( db );
2993
2994	QSqlQuery q( db );
2995	q.setForwardOnly( true );
2996
2997	QVERIFY_SQL( q, prepare( QString( "SELECT id, t_varchar, t_char FROM %1 WHERE id = :id" ).arg( qtest ) ) );
2998	q.bindValue( placeholder: ":id", val: 1 );
2999	QVERIFY_SQL( q, exec() );
3000
3001	// Do something, like iterate over the result, or skip to the end
3002	QVERIFY_SQL( q, last() );
3003
3004	QVERIFY_SQL( q, exec() );
3005	/* This was broken with SQLite because the cache size was set to 0 in the 2nd execute.
3006	When forwardOnly is set we don't cahce the entire result, but we do cache the current row
3007	but this requires the cache size to be equal to the column count.
3008	*/
3009	QVERIFY_SQL( q, next() );
3010	QCOMPARE( q.value( 0 ).toInt(), 1 );
3011	QCOMPARE( q.value( 1 ).toString().trimmed(), QString( "VarChar1" ) );
3012	QCOMPARE( q.value( 2 ).toString().trimmed(), QString( "Char1" ) );
3013	}
3014
3015	void tst_QSqlQuery::finish()
3016	{
3017	QFETCH( QString, dbName );
3018	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3019	CHECK_DATABASE( db );
3020
3021	QSqlQuery q( db );
3022	QVERIFY_SQL( q, prepare( "SELECT id FROM " + qtest + " WHERE id = ?" ) );
3023
3024	int id = 4;
3025	q.bindValue( pos: 0, val: id );
3026	QVERIFY_SQL( q, exec() );
3027	QVERIFY( q.isActive() );
3028	QVERIFY_SQL( q, next() );
3029	QCOMPARE( q.value( 0 ).toInt(), id );
3030
3031	q.finish();
3032	QVERIFY( !q.isActive() ); // query is now inactive
3033	QCOMPARE( q.boundValue( 0 ).toInt(), id ); // bound values are retained
3034
3035	QVERIFY_SQL( q, exec() ); // no prepare necessary
3036	QVERIFY( q.isActive() );
3037	QVERIFY_SQL( q, next() );
3038	QCOMPARE( q.value( 0 ).toInt(), id );
3039
3040	q.finish();
3041	QVERIFY( !q.isActive() );
3042
3043	QVERIFY_SQL( q, exec( "SELECT id FROM " + qtest + " WHERE id = 1" ) );
3044	QVERIFY( q.isActive() );
3045	QVERIFY_SQL( q, next() );
3046	QCOMPARE( q.value( 0 ).toInt(), 1 );
3047	QCOMPARE( q.record().count(), 1 );
3048	}
3049
3050	void tst_QSqlQuery::sqlite_finish()
3051	{
3052	QFETCH( QString, dbName );
3053	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3054	CHECK_DATABASE( db );
3055
3056	if ( db.databaseName().startsWith( c: ':' ) )
3057	QSKIP( "This test requires a database on the filesystem, not in-memory");
3058
3059	{
3060	QSqlDatabase db2 = QSqlDatabase::addDatabase( type: "QSQLITE", connectionName: "sqlite_finish_sqlite" );
3061	db2.setDatabaseName( db.databaseName() );
3062	QVERIFY_SQL( db2, open() );
3063
3064	const QString tableName(qTableName(prefix: "qtest_lockedtable", __FILE__, db));
3065	QSqlQuery q( db );
3066
3067	tst_Databases::safeDropTable( db, tableName );
3068	q.exec( query: "CREATE TABLE " + tableName + " (pk_id INTEGER PRIMARY KEY, whatever TEXT)" );
3069	q.exec( query: "INSERT INTO " + tableName + " values(1, 'whatever')" );
3070	q.exec( query: "INSERT INTO " + tableName + " values(2, 'whatever more')" );
3071
3072	// This creates a read-lock in the database
3073	QVERIFY_SQL( q, exec( "SELECT * FROM " + tableName + " WHERE pk_id = 1 or pk_id = 2" ) );
3074	QVERIFY_SQL( q, next() );
3075
3076	// The DELETE will fail because of the read-lock
3077	QSqlQuery q2( db2 );
3078	QVERIFY( !q2.exec( "DELETE FROM " + tableName + " WHERE pk_id=2" ) );
3079	QCOMPARE( q2.numRowsAffected(), -1 );
3080
3081	// The DELETE will succeed now because finish() removes the lock
3082	q.finish();
3083	QVERIFY_SQL( q2, exec( "DELETE FROM " + tableName + " WHERE pk_id=2" ) );
3084	QCOMPARE( q2.numRowsAffected(), 1 );
3085
3086	tst_Databases::safeDropTable( db, tableName );
3087	}
3088
3089	QSqlDatabase::removeDatabase( connectionName: "sqlite_finish_sqlite" );
3090	}
3091
3092	void tst_QSqlQuery::nextResult()
3093	{
3094	QFETCH( QString, dbName );
3095	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3096	CHECK_DATABASE( db );
3097	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
3098	if (!db.driver()->hasFeature(f: QSqlDriver::MultipleResultSets))
3099	QSKIP("DBMS does not support multiple result sets");
3100
3101	QSqlQuery q( db );
3102	const QString tableName(qTableName(prefix: "more_results", __FILE__, db));
3103
3104	QVERIFY_SQL( q, exec( "CREATE TABLE " + tableName + " (id integer, text varchar(20), num numeric(6, 3), empty varchar(10));" ) );
3105
3106	QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(1, 'one', 1.1, '');" ) );
3107
3108	QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(2, 'two', 2.2, '');" ) );
3109
3110	QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(3, 'three', 3.3, '');" ) );
3111
3112	QVERIFY_SQL( q, exec( "INSERT INTO " + tableName + " VALUES(4, 'four', 4.4, '');" ) );
3113
3114	QStringList tstStrings;
3115
3116	tstStrings << "one" << "two" << "three" << "four";
3117
3118	// Query that returns only one result set, nothing special about this
3119	QVERIFY_SQL( q, exec( QString( "SELECT * FROM %1;" ).arg( tableName ) ) );
3120
3121	QVERIFY( q.next() ); // Move to first row of the result set
3122
3123	QVERIFY( !q.nextResult() ); // No more result sets are available
3124
3125	QVERIFY( !q.isActive() ); // So the query is no longer active
3126
3127	QVERIFY( !q.next() ); // ... and no data is available as the call
3128
3129	// to nextResult() discarded the result set
3130
3131	// Query that returns two result sets (batch sql)
3132	// When working with multiple result sets SQL Server insists on non-scrollable cursors
3133	if (db.driverName().startsWith(s: "QODBC"))
3134	q.setForwardOnly( true );
3135
3136	QVERIFY_SQL( q, exec( "SELECT id FROM " + tableName + "; SELECT text, num FROM " + tableName + ';' ) );
3137
3138	QCOMPARE( q.record().count(), 1 ); // Check that the meta data is as expected
3139
3140	QCOMPARE( q.record().field( 0 ).name().toUpper(), QString( "ID" ) );
3141
3142	QCOMPARE( q.record().field( 0 ).type(), QVariant::Int );
3143
3144	QVERIFY( q.nextResult() ); // Discards first result set and move to the next
3145
3146	QCOMPARE( q.record().count(), 2 ); // New meta data should be available
3147
3148	QCOMPARE( q.record().field( 0 ).name().toUpper(), QString( "TEXT" ) );
3149
3150	QCOMPARE( q.record().field( 0 ).type(), QVariant::String );
3151
3152	QCOMPARE( q.record().field( 1 ).name().toUpper(), QString( "NUM" ) );
3153	QCOMPARE(q.record().field(1).type(), QVariant::Double);
3154
3155	QVERIFY( q.next() ); // Move to first row of the second result set
3156
3157	QFAIL_SQL(q, nextResult()); // No more result sets after this
3158
3159	QVERIFY( !q.isActive() ); // So the query is no longer active
3160
3161	QVERIFY( !q.next() ); // ... and no data is available as the call to
3162
3163	// nextResult() discarded the result set
3164
3165	// Query that returns one result set, a count of affected rows and then another result set
3166	QString query1 = QString( "SELECT id, text, num, empty FROM %1 WHERE id <= 3" ).arg( a: tableName );
3167
3168	QString query2 = QString( "UPDATE %1 SET empty = 'Yatta!'" ).arg( a: tableName );
3169
3170	QString query3 = QString( "SELECT id, empty FROM %1 WHERE id <=2" ).arg( a: tableName );
3171
3172	QVERIFY_SQL( q, exec( QString( "%1; %2; %3;" ).arg( query1 ).arg( query2 ).arg( query3 ) ) );
3173
3174	// Check result set returned by first statement
3175	QVERIFY( q.isSelect() ); // The first statement is a select
3176
3177	for ( int i = 0; i < 3; i++ ) {
3178	QVERIFY_SQL( q, next() );
3179	QCOMPARE( q.value( 0 ).toInt(), 1+i );
3180	QCOMPARE( q.value( 1 ).toString(), tstStrings.at( i ) );
3181	QCOMPARE( q.value( 2 ).toDouble(), 1.1*( i+1 ) );
3182	QVERIFY( q.value( 3 ).toString().isEmpty() );
3183	}
3184
3185	QVERIFY_SQL( q, nextResult() );
3186
3187	QVERIFY( !q.isSelect() ); // The second statement isn't a SELECT
3188	QVERIFY( !q.next() ); // ... so no result set is available
3189	QCOMPARE( q.numRowsAffected(), 4 ); // 4 rows was affected by the UPDATE
3190
3191	// Check result set returned by third statement
3192	QVERIFY_SQL( q, nextResult() );
3193	QVERIFY( q.isSelect() ); // The third statement is a SELECT
3194
3195	for ( int i = 0; i < 2; i++ ) {
3196	QVERIFY_SQL( q, next() );
3197	QCOMPARE( q.value( 0 ).toInt(), 1+i );
3198	QCOMPARE( q.value( 1 ).toString(), QString( "Yatta!" ) );
3199	}
3200
3201	// Stored procedure with multiple result sets
3202	const QString procName(qTableName(prefix: "proc_more_res", __FILE__, db));
3203
3204	if (dbType == QSqlDriver::PostgreSQL)
3205	q.exec(query: QString("DROP FUNCTION %1(refcursor, refcursor);").arg(a: procName));
3206	else
3207	q.exec(query: QString("DROP PROCEDURE %1;").arg(a: procName));
3208
3209	if (dbType == QSqlDriver::MySqlServer)
3210	QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1()"
3211	"\nBEGIN"
3212	"\nSELECT id, text FROM %2;"
3213	"\nSELECT empty, num, text, id FROM %3;"
3214	"\nEND" ).arg( procName ).arg( tableName ).arg( tableName ) ) );
3215	else if (dbType == QSqlDriver::DB2)
3216	QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1()"
3217	"\nRESULT SETS 2"
3218	"\nLANGUAGE SQL"
3219	"\np1:BEGIN"
3220	"\nDECLARE cursor1 CURSOR WITH RETURN FOR SELECT id, text FROM %2;"
3221	"\nDECLARE cursor2 CURSOR WITH RETURN FOR SELECT empty, num, text, id FROM %3;"
3222	"\nOPEN cursor1;"
3223	"\nOPEN cursor2;"
3224	"\nEND p1" ).arg( procName ).arg( tableName ).arg( tableName ) ) );
3225	else if (dbType == QSqlDriver::PostgreSQL)
3226	QVERIFY_SQL(q, exec(QString("CREATE FUNCTION %1(ref1 refcursor, ref2 refcursor)"
3227	"\nRETURNS SETOF refcursor AS $$"
3228	"\nBEGIN"
3229	"\nOPEN ref1 FOR SELECT id, text FROM %2;"
3230	"\nRETURN NEXT ref1;"
3231	"\nOPEN ref2 FOR SELECT empty, num, text, id FROM %2;"
3232	"\nRETURN NEXT ref2;"
3233	"\nEND;"
3234	"\n$$ LANGUAGE plpgsql").arg(procName).arg(tableName)));
3235	else
3236	QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1"
3237	"\nAS"
3238	"\nSELECT id, text FROM %2"
3239	"\nSELECT empty, num, text, id FROM %3" ).arg( procName ).arg( tableName ).arg( tableName ) ) );
3240
3241	if (dbType == QSqlDriver::MySqlServer || dbType == QSqlDriver::DB2) {
3242	q.setForwardOnly( true );
3243	QVERIFY_SQL( q, exec( QString( "CALL %1()" ).arg( procName ) ) );
3244	} else if (dbType == QSqlDriver::PostgreSQL) {
3245	// Returning multiple result sets from PostgreSQL stored procedure:
3246	// http://sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure
3247	QVERIFY_SQL(q, exec(QString("BEGIN;"
3248	"SELECT %1('cur1', 'cur2');"
3249	"FETCH ALL IN cur1;"
3250	"FETCH ALL IN cur2;"
3251	"COMMIT;").arg(procName)));
3252	} else {
3253	QVERIFY_SQL( q, exec( QString( "EXEC %1" ).arg( procName ) ) );
3254	}
3255
3256	if (dbType == QSqlDriver::PostgreSQL) {
3257	// First result set - start of transaction
3258	QVERIFY(!q.isSelect());
3259	QCOMPARE(q.numRowsAffected(), 0);
3260	QVERIFY(q.nextResult());
3261	// Second result set contains cursor names
3262	QVERIFY(q.isSelect());
3263	QVERIFY(q.next());
3264	QCOMPARE(q.value(0).toString(), "cur1");
3265	QVERIFY(q.next());
3266	QCOMPARE(q.value(0).toString(), "cur2");
3267	QVERIFY(q.nextResult());
3268	}
3269
3270	for ( int i = 0; i < 4; i++ ) {
3271	QVERIFY_SQL( q, next() );
3272	QCOMPARE( q.value( 0 ).toInt(), i+1 );
3273	QCOMPARE( q.value( 1 ).toString(), tstStrings.at( i ) );
3274	}
3275
3276	QVERIFY_SQL( q, nextResult() );
3277
3278	QVERIFY_SQL( q, isActive() );
3279
3280	for ( int i = 0; i < 4; i++ ) {
3281	QVERIFY_SQL( q, next() );
3282	QCOMPARE( q.value( 0 ).toString(), QString( "Yatta!" ) );
3283	QCOMPARE( q.value( 1 ).toDouble(), 1.1*( 1+i ) );
3284	QCOMPARE( q.value( 2 ).toString(), tstStrings.at( i ) );
3285	QCOMPARE( q.value( 3 ).toInt(), 1+i );
3286	}
3287
3288	// MySQL also counts the CALL itself as a result
3289	if (dbType == QSqlDriver::MySqlServer) {
3290	QVERIFY( q.nextResult() );
3291	QVERIFY( !q.isSelect() ); // ... but it's not a select
3292	QCOMPARE( q.numRowsAffected(), 0 ); // ... and no rows are affected (at least not with this procedure)
3293	}
3294	if (dbType == QSqlDriver::PostgreSQL) {
3295	// Last result set - commit transaction
3296	QVERIFY(q.nextResult());
3297	QVERIFY(!q.isSelect());
3298	QCOMPARE(q.numRowsAffected(), 0);
3299	}
3300
3301	QVERIFY( !q.nextResult() );
3302
3303	QVERIFY( !q.isActive() );
3304
3305	if (dbType == QSqlDriver::PostgreSQL)
3306	q.exec(query: QString("DROP FUNCTION %1(refcursor, refcursor);").arg(a: procName));
3307	else
3308	q.exec(query: QString("DROP PROCEDURE %1;").arg(a: procName));
3309	}
3310
3311
3312	// For task 190311. Problem: Truncation happens on the 2nd execution if that BLOB is larger
3313	// than the BLOB on the 1st execution. This was only for MySQL, but the test is general
3314	// enough to be run with all backends.
3315	void tst_QSqlQuery::blobsPreparedQuery()
3316	{
3317	QFETCH( QString, dbName );
3318	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3319	CHECK_DATABASE( db );
3320
3321	if ( !db.driver()->hasFeature( f: QSqlDriver::BLOB ) || !db.driver()->hasFeature( f: QSqlDriver::PreparedQueries ) )
3322	QSKIP( "DBMS does not support BLOBs or prepared queries");
3323
3324	const QString tableName(qTableName(prefix: "blobstest", __FILE__, db));
3325
3326	QSqlQuery q( db );
3327	q.setForwardOnly( true ); // This is needed to make the test work with DB2.
3328	QString shortBLOB( "abc" );
3329	QString longerBLOB( "abcdefghijklmnopqrstuvxyz¿äëïöü¡ " );
3330
3331	// In PostgreSQL a BLOB is not called a BLOB, but a BYTEA! :-)
3332	// ... and in SQL Server it can be called a lot, but IMAGE will do.
3333	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
3334	QString typeName( "BLOB" );
3335	if (dbType == QSqlDriver::PostgreSQL)
3336	typeName = "BYTEA";
3337	else if (dbType == QSqlDriver::MSSqlServer)
3338	typeName = "IMAGE";
3339
3340	QVERIFY_SQL( q, exec( QString( "CREATE TABLE %1(id INTEGER, data %2)" ).arg( tableName ).arg( typeName ) ) );
3341	q.prepare( query: QString( "INSERT INTO %1(id, data) VALUES(:id, :data)" ).arg( a: tableName ) );
3342	q.bindValue( placeholder: ":id", val: 1 );
3343	q.bindValue( placeholder: ":data", val: shortBLOB );
3344	QVERIFY_SQL( q, exec() );
3345
3346	q.bindValue( placeholder: ":id", val: 2 );
3347	q.bindValue( placeholder: ":data", val: longerBLOB );
3348	QVERIFY_SQL( q, exec() );
3349
3350	// Two executions and result sets
3351	q.prepare( query: QString( "SELECT data FROM %1 WHERE id = ?" ).arg( a: tableName ) );
3352	q.bindValue( pos: 0, val: QVariant( 1 ) );
3353	QVERIFY_SQL( q, exec() );
3354	QVERIFY_SQL( q, next() );
3355	QCOMPARE( q.value( 0 ).toString(), shortBLOB );
3356
3357	q.bindValue( pos: 0, val: QVariant( 2 ) );
3358	QVERIFY_SQL( q, exec() );
3359	QVERIFY_SQL( q, next() );
3360	QCOMPARE( q.value( 0 ).toString().toUtf8(), longerBLOB.toUtf8() );
3361
3362	// Only one execution and result set
3363	q.prepare( query: QString( "SELECT id, data FROM %1 ORDER BY id" ).arg( a: tableName ) );
3364	QVERIFY_SQL( q, exec() );
3365	QVERIFY_SQL( q, next() );
3366	QCOMPARE( q.value( 1 ).toString(), shortBLOB );
3367	QVERIFY_SQL( q, next() );
3368	QCOMPARE( q.value( 1 ).toString(), longerBLOB );
3369	}
3370
3371	// There were problems with navigating past the end of a table returning an error on mysql
3372	void tst_QSqlQuery::emptyTableNavigate()
3373	{
3374	QFETCH( QString, dbName );
3375	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3376	CHECK_DATABASE( db );
3377
3378	{
3379	QSqlQuery q( db );
3380	const QString tbl = qTableName(prefix: "qtest_empty", __FILE__, db);
3381	q.exec(query: "drop table " + tbl);
3382	QVERIFY_SQL(q, exec("create table " + tbl + " (id char(10))"));
3383	QVERIFY_SQL(q, prepare("select * from " + tbl));
3384	QVERIFY_SQL( q, exec() );
3385	QVERIFY( !q.next() );
3386	QCOMPARE( q.lastError().isValid(), false );
3387	}
3388	}
3389
3390	void tst_QSqlQuery::timeStampParsing()
3391	{
3392	QFETCH(QString, dbName);
3393	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3394	CHECK_DATABASE(db);
3395	const QString tableName(qTableName(prefix: "timeStampParsing", __FILE__, db));
3396	tst_Databases::safeDropTable(db, tableName);
3397	QSqlQuery q(db);
3398	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
3399	if (dbType == QSqlDriver::PostgreSQL) {
3400	QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("("
3401	"id serial NOT NULL, "
3402	"datefield timestamp, primary key(id));")));
3403	} else if (dbType == QSqlDriver::MySqlServer) {
3404	QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("("
3405	"id integer NOT NULL AUTO_INCREMENT,"
3406	"datefield timestamp, primary key(id));")));
3407	} else {
3408	QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("("
3409	"\"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"
3410	"\"datefield\" timestamp);")));
3411	}
3412	QVERIFY_SQL(q, exec(
3413	QStringLiteral("INSERT INTO ") + tableName + QStringLiteral(" (datefield) VALUES (current_timestamp);"
3414	)));
3415	QVERIFY_SQL(q, exec(QStringLiteral("SELECT * FROM ") + tableName));
3416	while (q.next())
3417	QVERIFY(q.value(1).toDateTime().isValid());
3418	}
3419
3420	void tst_QSqlQuery::task_217003()
3421	{
3422	QFETCH( QString, dbName );
3423	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3424	CHECK_DATABASE( db );
3425	QSqlQuery q( db );
3426	const QString Planet(qTableName( prefix: "Planet", __FILE__, db));
3427
3428	q.exec(query: "drop table " + Planet);
3429	QVERIFY_SQL( q, exec( "create table " + Planet + " (Name varchar(20))" ) );
3430	QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Mercury')" ) );
3431	QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Venus')" ) );
3432	QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Earth')" ) );
3433	QVERIFY_SQL( q, exec( "insert into " + Planet + " VALUES ('Mars')" ) );
3434
3435	QVERIFY_SQL( q, exec( "SELECT Name FROM " + Planet ) );
3436	QVERIFY_SQL( q, seek( 3 ) );
3437	QCOMPARE( q.value( 0 ).toString(), QString( "Mars" ) );
3438	QVERIFY_SQL( q, seek( 1 ) );
3439	QCOMPARE( q.value( 0 ).toString(), QString( "Venus" ) );
3440	QVERIFY_SQL( q, exec( "SELECT Name FROM " + Planet ) );
3441	QVERIFY_SQL( q, seek( 3 ) );
3442	QCOMPARE( q.value( 0 ).toString(), QString( "Mars" ) );
3443	QVERIFY_SQL( q, seek( 0 ) );
3444	QCOMPARE( q.value( 0 ).toString(), QString( "Mercury" ) );
3445	QVERIFY_SQL( q, seek( 1 ) );
3446	QCOMPARE( q.value( 0 ).toString(), QString( "Venus" ) );
3447	}
3448
3449	void tst_QSqlQuery::task_250026()
3450	{
3451	QString data258, data1026;
3452	QFETCH( QString, dbName );
3453	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3454	CHECK_DATABASE( db );
3455	QSqlQuery q( db );
3456
3457	const QString tableName(qTableName(prefix: "task_250026", __FILE__, db));
3458
3459	if ( !q.exec( query: "create table " + tableName + " (longfield varchar(1100))" ) ) {
3460	qDebug() << "Error" << q.lastError();
3461	QSKIP( "Db doesn't support \"1100\" as a size for fields");
3462	}
3463
3464	data258.fill( c: 'A', size: 258 );
3465	data1026.fill( c: 'A', size: 1026 );
3466	QVERIFY_SQL( q, prepare( "insert into " + tableName + "(longfield) VALUES (:longfield)" ) );
3467	q.bindValue( placeholder: ":longfield", val: data258 );
3468	QVERIFY_SQL( q, exec() );
3469	q.bindValue( placeholder: ":longfield", val: data1026 );
3470	QVERIFY_SQL( q, exec() );
3471	QVERIFY_SQL( q, exec( "select * from " + tableName ) );
3472	QVERIFY_SQL( q, next() );
3473	QCOMPARE( q.value( 0 ).toString().length(), data258.length() );
3474	QVERIFY_SQL( q, next() );
3475	QCOMPARE( q.value( 0 ).toString().length(), data1026.length() );
3476	}
3477
3478	void tst_QSqlQuery::crashQueryOnCloseDatabase()
3479	{
3480	for (const auto &dbName : qAsConst(t&: dbs.dbNames)) {
3481	QSqlDatabase clonedDb = QSqlDatabase::cloneDatabase(
3482	other: QSqlDatabase::database(connectionName: dbName), connectionName: "crashTest");
3483	qDebug() << "Testing crash in sqlquery dtor for driver" << clonedDb.driverName();
3484	QVERIFY(clonedDb.open());
3485	QSqlQuery q(clonedDb);
3486	clonedDb.close();
3487	QSqlDatabase::removeDatabase(connectionName: "crashTest");
3488	}
3489	}
3490
3491	void tst_QSqlQuery::task_233829()
3492	{
3493	QFETCH( QString, dbName );
3494	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3495	CHECK_DATABASE( db );
3496
3497	QSqlQuery q( db );
3498	const QString tableName(qTableName(prefix: "task_233829", __FILE__, db));
3499	QVERIFY_SQL(q,exec("CREATE TABLE " + tableName + "(dbl1 double precision,dbl2 double precision) without oids;"));
3500
3501	QString queryString("INSERT INTO " + tableName +"(dbl1, dbl2) VALUES(?,?)");
3502
3503	double k = 0.0;
3504	QVERIFY_SQL(q,prepare(queryString));
3505	q.bindValue(pos: 0,val: 0.0 / k); // nan
3506	q.bindValue(pos: 1,val: 0.0 / k); // nan
3507	QVERIFY_SQL(q,exec());
3508	}
3509
3510	void tst_QSqlQuery::QTBUG_12477()
3511	{
3512	QFETCH(QString, dbName);
3513	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3514	CHECK_DATABASE(db);
3515	if (!db.driverName().startsWith(s: "QPSQL"))
3516	QSKIP("PostgreSQL specific test");
3517
3518	QSqlQuery q(db);
3519	QVERIFY_SQL(q, exec("SELECT 1::bit, '10101010000111101101'::varbit, "
3520	"'10101111011'::varbit(15), '22222.20'::numeric(16,2), "
3521	"'333333'::numeric(18), '444444'::numeric"));
3522	QVERIFY_SQL(q, next());
3523	QSqlRecord r = q.record();
3524	QSqlField f;
3525
3526	f = r.field(i: 0);
3527	QCOMPARE(f.length(), 1);
3528	QCOMPARE(f.precision(), -1);
3529
3530	f = r.field(i: 1);
3531	QCOMPARE(f.length(), -1);
3532	QCOMPARE(f.precision(), -1);
3533
3534	f = r.field(i: 2);
3535	QCOMPARE(f.length(), 15);
3536	QCOMPARE(f.precision(), -1);
3537
3538	f = r.field(i: 3);
3539	QCOMPARE(f.length(), 16);
3540	QCOMPARE(f.precision(), 2);
3541
3542	f = r.field(i: 4);
3543	QCOMPARE(f.length(), 18);
3544	QCOMPARE(f.precision(), 0);
3545
3546	f = r.field(i: 5);
3547	QCOMPARE(f.length(), -1);
3548	QCOMPARE(f.precision(), -1);
3549	}
3550
3551	void tst_QSqlQuery::sqlServerReturn0()
3552	{
3553	QFETCH( QString, dbName );
3554	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3555	CHECK_DATABASE( db );
3556	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
3557	if (dbType != QSqlDriver::MSSqlServer)
3558	QSKIP("SQL Server specific test");
3559
3560	const QString tableName(qTableName(prefix: "test141895", __FILE__, db)), procName(qTableName(prefix: "test141895_proc", __FILE__, db));
3561	QSqlQuery q( db );
3562	q.exec(query: "DROP TABLE " + tableName);
3563	q.exec(query: "DROP PROCEDURE " + procName);
3564	QVERIFY_SQL(q, exec("CREATE TABLE "+tableName+" (id integer)"));
3565	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (1)"));
3566	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (2)"));
3567	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (2)"));
3568	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (3)"));
3569	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" (id) VALUES (1)"));
3570	QVERIFY_SQL(q, exec("CREATE PROCEDURE "+procName+
3571	" AS "
3572	"SELECT * FROM "+tableName+" WHERE ID = 2 "
3573	"RETURN 0"));
3574
3575	QVERIFY_SQL(q, exec("{CALL " + procName + QLatin1Char('}')));
3576
3577	QVERIFY_SQL(q, next());
3578	}
3579
3580	void tst_QSqlQuery::QTBUG_551()
3581	{
3582	QFETCH( QString, dbName );
3583	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3584	CHECK_DATABASE( db );
3585	QSqlQuery q(db);
3586	const QString pkgname(qTableName(prefix: "pkg", __FILE__, db));
3587	QVERIFY_SQL(q, exec("CREATE OR REPLACE PACKAGE "+pkgname+" IS \n\
3588	\n\
3589	TYPE IntType IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;\n\
3590	TYPE VCType IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;\n\
3591	PROCEDURE P (Inp IN IntType, Outp OUT VCType);\n\
3592	END "+ pkgname + QLatin1Char(';')));
3593
3594	QVERIFY_SQL(q, exec("CREATE OR REPLACE PACKAGE BODY "+pkgname+" IS\n\
3595	PROCEDURE P (Inp IN IntType, Outp OUT VCType)\n\
3596	IS\n\
3597	BEGIN\n\
3598	Outp(1) := '1. Value is ' ||TO_CHAR(Inp(1));\n\
3599	Outp(2) := '2. Value is ' ||TO_CHAR(Inp(2));\n\
3600	Outp(3) := '3. Value is ' ||TO_CHAR(Inp(3));\n\
3601	END p;\n\
3602	END " + pkgname + QLatin1Char(';')));
3603
3604	QVariantList inLst, outLst, res_outLst;
3605
3606	q.prepare(query: "begin "+pkgname+".p(:inp, :outp); end;");
3607
3608	QString StVal;
3609	StVal.reserve(asize: 60);
3610
3611	// loading arrays
3612	for (int Cnt=0; Cnt < 3; Cnt++) {
3613	inLst << Cnt;
3614	outLst << StVal;
3615	}
3616
3617	q.bindValue(placeholder: ":inp", val: inLst);
3618	q.bindValue(placeholder: ":outp", val: outLst, type: QSql::Out);
3619
3620	QVERIFY_SQL(q, execBatch(QSqlQuery::ValuesAsColumns) );
3621	res_outLst = qvariant_cast<QVariantList>(v: q.boundValues()[":outp"]);
3622	QCOMPARE(res_outLst[0].toString(), QLatin1String("1. Value is 0"));
3623	QCOMPARE(res_outLst[1].toString(), QLatin1String("2. Value is 1"));
3624	QCOMPARE(res_outLst[2].toString(), QLatin1String("3. Value is 2"));
3625	}
3626
3627	void tst_QSqlQuery::QTBUG_12186()
3628	{
3629	QFETCH( QString, dbName );
3630	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3631
3632	// make sure that query.boundValues() returns the values in the right order even for more than 16 placeholders
3633	QSqlQuery query(db);
3634	query.prepare(query: "INSERT INTO person (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18) "
3635	"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
3636
3637	QList<QVariant> values;
3638
3639	for (int i = 0; i < 18; ++i)
3640	values << i;
3641
3642	foreach (QVariant v, values)
3643	query.bindValue(pos: v.toInt(), val: v);
3644
3645	QCOMPARE(query.boundValues().values(), values);
3646	}
3647
3648	void tst_QSqlQuery::QTBUG_14132()
3649	{
3650	QFETCH( QString, dbName );
3651	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3652	CHECK_DATABASE( db );
3653	QSqlQuery q(db);
3654	const QString procedureName(qTableName(prefix: "procedure", __FILE__, db));
3655	QVERIFY_SQL(q, exec("CREATE OR REPLACE PROCEDURE "+ procedureName + " (outStr OUT varchar2) \n\
3656	is \n\
3657	begin \n\
3658	outStr := 'OUTSTRING'; \n\
3659	end;"));
3660	QString placeholder = "XXXXXXXXX";
3661	QVERIFY(q.prepare("CALL "+procedureName+"(?)"));
3662	q.addBindValue(val: placeholder, type: QSql::Out);
3663	QVERIFY_SQL(q, exec());
3664	QCOMPARE(q.boundValue(0).toString(), QLatin1String("OUTSTRING"));
3665	}
3666
3667	void tst_QSqlQuery::QTBUG_18435()
3668	{
3669	QFETCH(QString, dbName);
3670	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3671	CHECK_DATABASE(db);
3672	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
3673	if (dbType != QSqlDriver::MSSqlServer || !db.driverName().startsWith(s: "QODBC"))
3674	QSKIP("SQL Server specific test");
3675
3676	QSqlQuery q(db);
3677	QString procName(qTableName(prefix: "qtbug_18435_proc", __FILE__, db));
3678
3679	q.exec(query: "DROP PROCEDURE " + procName);
3680	const QString stmt =
3681	"CREATE PROCEDURE " + procName + " @key nvarchar(50) OUTPUT AS\n"
3682	"BEGIN\n"
3683	" SET NOCOUNT ON\n"
3684	" SET @key = 'TEST'\n"
3685	"END\n";
3686
3687	QVERIFY_SQL(q, exec(stmt));
3688	QVERIFY_SQL(q, prepare("{CALL "+ procName +"(?)}"));
3689	const QString testStr = "0123";
3690	q.bindValue(pos: 0, val: testStr, type: QSql::Out);
3691	QVERIFY_SQL(q, exec());
3692	QCOMPARE(q.boundValue(0).toString(), QLatin1String("TEST"));
3693
3694	QVERIFY_SQL(q, exec("DROP PROCEDURE " + procName));
3695	}
3696
3697	void tst_QSqlQuery::QTBUG_5251()
3698	{
3699	// Since QSqlTableModel will escape the identifiers, we need to escape
3700	// them for databases that are case sensitive
3701	QFETCH( QString, dbName );
3702	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3703	CHECK_DATABASE( db );
3704	const QString timetest(qTableName(prefix: "timetest", __FILE__, db));
3705	tst_Databases::safeDropTable(db, tableName: timetest);
3706	QSqlQuery q(db);
3707	QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + timetest + QStringLiteral(" (t TIME)")));
3708	QVERIFY_SQL(q, exec(QStringLiteral("INSERT INTO ") + timetest +
3709	QStringLiteral(" VALUES ('1:2:3.666')")));
3710
3711	QSqlTableModel timetestModel(0,db);
3712	timetestModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
3713	timetestModel.setTable(timetest);
3714	QVERIFY_SQL(timetestModel, select());
3715
3716	QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("01:02:03.666"));
3717	QVERIFY_SQL(timetestModel,setData(timetestModel.index(0, 0), QTime(0,12,34,500)));
3718	QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:12:34.500"));
3719	QVERIFY_SQL(timetestModel, submitAll());
3720	QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:12:34.500"));
3721
3722	QVERIFY_SQL(q, exec(QStringLiteral("UPDATE ") + timetest +
3723	QStringLiteral(" SET t = '0:11:22.33'")));
3724	QVERIFY_SQL(timetestModel, select());
3725	QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:11:22.330"));
3726
3727	}
3728
3729	void tst_QSqlQuery::QTBUG_6421()
3730	{
3731	QFETCH( QString, dbName );
3732	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3733	CHECK_DATABASE( db );
3734
3735	QSqlQuery q(db);
3736	const QString tableName(qTableName(prefix: "bug6421", __FILE__, db).toUpper());
3737
3738	QVERIFY_SQL(q, exec("create table "+tableName+"(COL1 char(10), COL2 char(10), COL3 char(10))"));
3739	QVERIFY_SQL(q, exec("create index INDEX1 on "+tableName+" (COL1 desc)"));
3740	QVERIFY_SQL(q, exec("create index INDEX2 on "+tableName+" (COL2 desc)"));
3741	QVERIFY_SQL(q, exec("create index INDEX3 on "+tableName+" (COL3 desc)"));
3742	q.setForwardOnly(true);
3743	QVERIFY_SQL(q, exec("select COLUMN_EXPRESSION from ALL_IND_EXPRESSIONS where TABLE_NAME='" + tableName + QLatin1Char('\'')));
3744	QVERIFY_SQL(q, next());
3745	QCOMPARE(q.value(0).toString(), QLatin1String("\"COL1\""));
3746	QVERIFY_SQL(q, next());
3747	QCOMPARE(q.value(0).toString(), QLatin1String("\"COL2\""));
3748	QVERIFY_SQL(q, next());
3749	QCOMPARE(q.value(0).toString(), QLatin1String("\"COL3\""));
3750	}
3751
3752	void tst_QSqlQuery::QTBUG_6618()
3753	{
3754	QFETCH( QString, dbName );
3755	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3756	CHECK_DATABASE( db );
3757	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
3758	if (dbType != QSqlDriver::MSSqlServer)
3759	QSKIP("SQL Server specific test");
3760
3761	QSqlQuery q(db);
3762	q.exec(query: "drop procedure " + qTableName(prefix: "tst_raiseError", __FILE__, db)); //non-fatal
3763	QString errorString;
3764	for (int i=0;i<110;i++)
3765	errorString+="reallylong";
3766	errorString+=" error";
3767	QVERIFY_SQL(q, exec("create procedure " + qTableName("tst_raiseError", __FILE__, db) + " as\n"
3768	"begin\n"
3769	" raiserror('" + errorString + "', 16, 1)\n"
3770	"end\n" ));
3771	q.exec(query: "{call " + qTableName(prefix: "tst_raiseError", __FILE__, db) + QLatin1Char('}'));
3772	QVERIFY(q.lastError().text().contains(errorString));
3773	}
3774
3775	void tst_QSqlQuery::QTBUG_6852()
3776	{
3777	QFETCH( QString, dbName );
3778	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3779	CHECK_DATABASE( db );
3780	QSqlQuery q(db);
3781	const QString tableName(qTableName(prefix: "bug6852", __FILE__, db)), procName(qTableName(prefix: "bug6852_proc", __FILE__, db));
3782
3783	QVERIFY_SQL(q, exec("DROP PROCEDURE IF EXISTS "+procName));
3784	QVERIFY_SQL(q, exec("CREATE TABLE "+tableName+"(\n"
3785	"MainKey INT NOT NULL,\n"
3786	"OtherTextCol VARCHAR(45) NOT NULL,\n"
3787	"PRIMARY KEY(`MainKey`))"));
3788	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(0, \"Disabled\")"));
3789	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(5, \"Error Only\")"));
3790	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(10, \"Enabled\")"));
3791	QVERIFY_SQL(q, exec("INSERT INTO "+tableName+" VALUES(15, \"Always\")"));
3792	QVERIFY_SQL(q, exec("CREATE PROCEDURE "+procName+"()\n"
3793	"READS SQL DATA\n"
3794	"BEGIN\n"
3795	" SET @st = 'SELECT MainKey, OtherTextCol from "+tableName+"';\n"
3796	" PREPARE stmt from @st;\n"
3797	" EXECUTE stmt;\n"
3798	"END;"));
3799
3800	QVERIFY_SQL(q, exec("CALL "+procName+"()"));
3801	QVERIFY_SQL(q, next());
3802	QCOMPARE(q.value(0).toInt(), 0);
3803	QCOMPARE(q.value(1).toString(), QLatin1String("Disabled"));
3804	}
3805
3806	void tst_QSqlQuery::QTBUG_5765()
3807	{
3808	QFETCH( QString, dbName );
3809	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
3810	CHECK_DATABASE( db );
3811	QSqlQuery q(db);
3812	const QString tableName(qTableName(prefix: "bug5765", __FILE__, db));
3813
3814	QVERIFY_SQL(q, exec("CREATE TABLE "+tableName+"(testval TINYINT(1) DEFAULT 0)"));
3815	q.prepare(query: "INSERT INTO "+tableName+" SET testval = :VALUE");
3816	q.bindValue(placeholder: ":VALUE", val: 1);
3817	QVERIFY_SQL(q, exec());
3818	q.bindValue(placeholder: ":VALUE", val: 12);
3819	QVERIFY_SQL(q, exec());
3820	q.bindValue(placeholder: ":VALUE", val: 123);
3821	QVERIFY_SQL(q, exec());
3822	QString sql="select testval from "+tableName;
3823	QVERIFY_SQL(q, exec(sql));
3824	QVERIFY_SQL(q, next());
3825	QCOMPARE(q.value(0).toInt(), 1);
3826	QVERIFY_SQL(q, next());
3827	QCOMPARE(q.value(0).toInt(), 12);
3828	QVERIFY_SQL(q, next());
3829	QCOMPARE(q.value(0).toInt(), 123);
3830	QVERIFY_SQL(q, prepare(sql));
3831	QVERIFY_SQL(q, exec());
3832	QVERIFY_SQL(q, next());
3833	QCOMPARE(q.value(0).toInt(), 1);
3834	QVERIFY_SQL(q, next());
3835	QCOMPARE(q.value(0).toInt(), 12);
3836	QVERIFY_SQL(q, next());
3837	QCOMPARE(q.value(0).toInt(), 123);
3838	}
3839
3840	/**
3841	* This test case tests multiple statements in one execution.
3842	* Sqlite driver doesn't support multiple statement at one time.
3843	* If more than one statement is given, the exec or prepare function
3844	* return failure to the client.
3845	*/
3846	void tst_QSqlQuery::QTBUG_21884()
3847	{
3848	QFETCH(QString, dbName);
3849	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3850	CHECK_DATABASE(db);
3851
3852	QSqlQuery q(db);
3853
3854	QStringList stList;
3855	QString tableName(qTableName(prefix: "bug21884", __FILE__, db));
3856	stList << "create table " + tableName + "(id integer primary key, note string)";
3857	stList << "select * from " + tableName + QLatin1Char(';');
3858	stList << "select * from " + tableName + "; \t\n\r";
3859	stList << "drop table " + tableName;
3860
3861
3862	foreach (const QString& st, stList) {
3863	QVERIFY_SQL(q, exec(st));
3864	}
3865
3866	foreach (const QString& st, stList) {
3867	QVERIFY_SQL(q, prepare(st));
3868	QVERIFY_SQL(q, exec());
3869	}
3870
3871	stList.clear();
3872	stList << "create table " + tableName + "(id integer primary key); select * from " + tableName;
3873	stList << "create table " + tableName + "(id integer primary key); syntax error!;";
3874	stList << "create table " + tableName + "(id integer primary key);;";
3875	stList << "create table " + tableName + "(id integer primary key);\'\"\a\b\b\v";
3876
3877	foreach (const QString&st , stList) {
3878	QVERIFY2(!q.prepare(st), qPrintable(QString("the statement is expected to fail! ") + st));
3879	QVERIFY2(!q.exec(st), qPrintable(QString("the statement is expected to fail! ") + st));
3880	}
3881	}
3882
3883	/**
3884	* This test case test sqlite driver close function. Sqlite driver should close cleanly
3885	* even if there is still outstanding prepared statement.
3886	*/
3887	void tst_QSqlQuery::QTBUG_16967()
3888	{
3889	QSqlQuery q2;
3890	QFETCH(QString, dbName);
3891	{
3892	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3893	CHECK_DATABASE(db);
3894	db.close();
3895	QCOMPARE(db.lastError().type(), QSqlError::NoError);
3896	}
3897	{
3898	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3899	CHECK_DATABASE(db);
3900	QSqlQuery q(db);
3901	q2 = q;
3902	q.prepare(query: "CREATE TABLE t1 (id INTEGER PRIMARY KEY, str TEXT);");
3903	db.close();
3904	QCOMPARE(db.lastError().type(), QSqlError::NoError);
3905	}
3906	{
3907	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3908	CHECK_DATABASE(db);
3909	QSqlQuery q(db);
3910	q2 = q;
3911	q2.prepare(query: "CREATE TABLE t1 (id INTEGER PRIMARY KEY, str TEXT);");
3912	q2.exec();
3913	db.close();
3914	QCOMPARE(db.lastError().type(), QSqlError::NoError);
3915	}
3916	{
3917	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3918	CHECK_DATABASE(db);
3919	QSqlQuery q(db);
3920	q2 = q;
3921	q.exec(query: "INSERT INTO t1 (id, str) VALUES(1, \"test1\");");
3922	db.close();
3923	QCOMPARE(db.lastError().type(), QSqlError::NoError);
3924	}
3925	{
3926	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3927	CHECK_DATABASE(db);
3928	QSqlQuery q(db);
3929	q2 = q;
3930	q.exec(query: "SELECT * FROM t1;");
3931	db.close();
3932	QCOMPARE(db.lastError().type(), QSqlError::NoError);
3933	}
3934	}
3935
3936	/**
3937	* In SQLite when a boolean value is bound to a placeholder, it should be converted
3938	* into integer 0/1 rather than text "false"/"true". According to documentation,
3939	* SQLite does not have separate Boolean storage class. Instead, Boolean values are
3940	* stored as integers.
3941	*/
3942	void tst_QSqlQuery::QTBUG_23895()
3943	{
3944	QFETCH(QString, dbName);
3945	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
3946	CHECK_DATABASE(db);
3947
3948	QSqlQuery q(db);
3949
3950	QString tableName(qTableName(prefix: "bug23895", __FILE__, db));
3951	q.prepare(query: "create table " + tableName + "(id integer primary key, val1 bool, val2 boolean)");
3952	QVERIFY_SQL(q, exec());
3953	q.prepare(query: "insert into " + tableName + "(id, val1, val2) values(?, ?, ?);");
3954	q.addBindValue(val: 1);
3955	q.addBindValue(val: true);
3956	q.addBindValue(val: false);
3957	QVERIFY_SQL(q, exec());
3958
3959	QString sql="select * from " + tableName;
3960	QVERIFY_SQL(q, exec(sql));
3961	QVERIFY_SQL(q, next());
3962
3963	QCOMPARE(q.record().field(0).type(), QVariant::Int);
3964	QCOMPARE(q.value(0).type(), QVariant::LongLong);
3965	QCOMPARE(q.value(0).toInt(), 1);
3966	QCOMPARE(q.record().field(1).type(), QVariant::Bool);
3967	QCOMPARE(q.value(1).type(), QVariant::LongLong);
3968	QCOMPARE(q.value(1).toBool(), true);
3969	QCOMPARE(q.record().field(2).type(), QVariant::Bool);
3970	QCOMPARE(q.value(2).type(), QVariant::LongLong);
3971	QCOMPARE(q.value(2).toBool(), false);
3972
3973	q.prepare(query: "insert into " + tableName + "(id, val1, val2) values(?, ?, ?);");
3974	q.addBindValue(val: 2);
3975	q.addBindValue(val: false);
3976	q.addBindValue(val: false);
3977	QVERIFY_SQL(q, exec());
3978
3979	sql="select * from " + tableName + " where val1";
3980	QVERIFY_SQL(q, exec(sql));
3981	QVERIFY_SQL(q, next());
3982	QCOMPARE(q.value(0).toInt(), 1);
3983	QVERIFY(!q.next());
3984
3985	sql="select * from " + tableName + " where not val2";
3986	QVERIFY_SQL(q, exec(sql));
3987	QVERIFY_SQL(q, next());
3988	QCOMPARE(q.value(0).toInt(), 1);
3989	QVERIFY_SQL(q, next());
3990	QCOMPARE(q.value(0).toInt(), 2);
3991	QVERIFY(!q.next());
3992	}
3993
3994	/**
3995	* Test for aliases with dots
3996	*/
3997	void tst_QSqlQuery::QTBUG_14904()
3998	{
3999	QFETCH(QString, dbName);
4000	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4001	CHECK_DATABASE(db);
4002
4003	QSqlQuery q(db);
4004
4005	QString tableName(qTableName(prefix: "bug14904", __FILE__, db));
4006	tst_Databases::safeDropTable( db, tableName );
4007
4008	q.prepare(query: "create table " + tableName + "(val1 bool)");
4009	QVERIFY_SQL(q, exec());
4010	q.prepare(query: "insert into " + tableName + "(val1) values(?);");
4011	q.addBindValue(val: true);
4012	QVERIFY_SQL(q, exec());
4013
4014	QString sql="select val1 AS value1 from " + tableName;
4015	QVERIFY_SQL(q, exec(sql));
4016	QVERIFY_SQL(q, next());
4017
4018	QCOMPARE(q.record().indexOf("value1"), 0);
4019	QCOMPARE(q.record().field(0).type(), QVariant::Bool);
4020	QCOMPARE(q.value(0).toBool(), true);
4021
4022	sql="select val1 AS 'value.one' from " + tableName;
4023	QVERIFY_SQL(q, exec(sql));
4024	QVERIFY_SQL(q, next());
4025	QCOMPARE(q.record().indexOf("value.one"), 0); // was -1 before bug fix
4026	QCOMPARE(q.record().field(0).type(), QVariant::Bool);
4027	QCOMPARE(q.value(0).toBool(), true);
4028	}
4029
4030	void tst_QSqlQuery::QTBUG_2192()
4031	{
4032	QFETCH( QString, dbName );
4033	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
4034	CHECK_DATABASE( db );
4035	{
4036	const QString tableName(qTableName(prefix: "bug2192", __FILE__, db));
4037	tst_Databases::safeDropTable( db, tableName );
4038
4039	QSqlQuery q(db);
4040	QVERIFY_SQL(q, exec(QString("CREATE TABLE " + tableName + " (dt %1)").arg(tst_Databases::dateTimeTypeName(db))));
4041
4042	QDateTime dt = QDateTime(QDate(2012, 7, 4), QTime(23, 59, 59, 999));
4043	QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (dt) VALUES (?)"));
4044	q.bindValue(pos: 0, val: dt);
4045	QVERIFY_SQL(q, exec());
4046
4047	QVERIFY_SQL(q, exec("SELECT dt FROM " + tableName));
4048	QVERIFY_SQL(q, next());
4049
4050	// Check if retrieved value preserves reported precision
4051	int precision = qMax(a: 0, b: q.record().field(name: "dt").precision());
4052	qint64 diff = qAbs(t: q.value(i: 0).toDateTime().msecsTo(dt));
4053	qint64 keep = qMin(a: 1000LL, b: qRound64(d: qPow(x: 10.0, y: precision)));
4054	QVERIFY(diff <= 1000 - keep);
4055	}
4056	}
4057
4058	void tst_QSqlQuery::QTBUG_36211()
4059	{
4060	QFETCH( QString, dbName );
4061	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
4062	CHECK_DATABASE( db );
4063	if (tst_Databases::getDatabaseType(db) == QSqlDriver::PostgreSQL) {
4064	const QString tableName(qTableName(prefix: "bug36211", __FILE__, db));
4065	tst_Databases::safeDropTable( db, tableName );
4066
4067	QSqlQuery q(db);
4068	QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (dtwtz timestamptz, dtwotz timestamp)").arg(tableName)));
4069
4070	#if QT_CONFIG(timezone)
4071	QTimeZone l_tzBrazil("America/Sao_Paulo");
4072	QTimeZone l_tzChina("Asia/Shanghai");
4073	QVERIFY(l_tzBrazil.isValid());
4074	QVERIFY(l_tzChina.isValid());
4075	QDateTime dt = QDateTime(QDate(2014, 10, 30), QTime(14, 12, 02, 357));
4076	QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (dtwtz, dtwotz) VALUES (:dt, :dt)"));
4077	q.bindValue(placeholder: ":dt", val: dt);
4078	QVERIFY_SQL(q, exec());
4079	q.bindValue(placeholder: ":dt", val: dt.toTimeZone(toZone: l_tzBrazil));
4080	QVERIFY_SQL(q, exec());
4081	q.bindValue(placeholder: ":dt", val: dt.toTimeZone(toZone: l_tzChina));
4082	QVERIFY_SQL(q, exec());
4083
4084	QVERIFY_SQL(q, exec("SELECT dtwtz, dtwotz FROM " + tableName));
4085
4086	for (int i = 0; i < 3; ++i) {
4087	QVERIFY_SQL(q, next());
4088
4089	for (int j = 0; j < 2; ++j) {
4090	// Check if retrieved value preserves reported precision
4091	int precision = qMax(a: 0, b: q.record().field(i: j).precision());
4092	qint64 diff = qAbs(t: q.value(i: j).toDateTime().msecsTo(dt));
4093	qint64 keep = qMin(a: 1000LL, b: qRound64(d: qPow(x: 10.0, y: precision)));
4094	QVERIFY(diff <= 1000 - keep);
4095	}
4096	}
4097	#endif
4098	}
4099	}
4100
4101	void tst_QSqlQuery::QTBUG_53969()
4102	{
4103	QFETCH( QString, dbName );
4104	QVector<int> values = QVector<int>() << 10 << 20 << 127 << 128 << 1, tableValues;
4105	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
4106	CHECK_DATABASE( db );
4107	tableValues.reserve(asize: values.size());
4108	if (tst_Databases::getDatabaseType(db) == QSqlDriver::MySqlServer) {
4109	const QString tableName(qTableName(prefix: "bug53969", __FILE__, db));
4110	tst_Databases::safeDropTable( db, tableName );
4111
4112	QSqlQuery q(db);
4113	QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id INT AUTO_INCREMENT PRIMARY KEY, "
4114	"test_number TINYINT(3) UNSIGNED)")
4115	.arg(tableName)));
4116
4117	QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (test_number) VALUES (:value)"));
4118
4119	QVector<int>::iterator begin = values.begin(), end = values.end(), it;
4120	for (it = begin; it != end; ++it) {
4121	q.bindValue(placeholder: ":value", val: *it);
4122	QVERIFY_SQL(q, exec());
4123	}
4124
4125	QVERIFY_SQL(q, prepare("SELECT test_number FROM " + tableName));
4126	QVERIFY_SQL(q, exec());
4127
4128	while (q.next()) {
4129	bool ok;
4130	tableValues.push_back(t: q.value(i: 0).toUInt(ok: &ok));
4131	QVERIFY(ok);
4132	}
4133	QCOMPARE(values, tableValues);
4134	}
4135	}
4136
4137	void tst_QSqlQuery::gisPointDatatype()
4138	{
4139	QFETCH(QString, dbName);
4140	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4141	CHECK_DATABASE(db);
4142
4143	QSqlQuery sqlQuery(db);
4144	const auto tableName = qTableName(prefix: "qtbug72140", __FILE__, db);
4145	tst_Databases::safeDropTable(db, tableName);
4146	QString sqlCommand = QStringLiteral("CREATE TABLE %1 (`lonlat_point` POINT NULL) ENGINE = InnoDB;").arg(a: tableName);
4147	QVERIFY(sqlQuery.exec(sqlCommand));
4148	sqlCommand = QStringLiteral("INSERT INTO %1(lonlat_point) VALUES(ST_GeomFromText('POINT(1 1)'));").arg(a: tableName);
4149	QVERIFY(sqlQuery.exec(sqlCommand));
4150	sqlCommand = QStringLiteral("SELECT * FROM %1;").arg(a: tableName);
4151	QVERIFY(sqlQuery.exec(sqlCommand));
4152	QCOMPARE(sqlQuery.record().field(0).type(), QVariant::Type::ByteArray);
4153	QVERIFY(sqlQuery.next());
4154	}
4155
4156	void tst_QSqlQuery::oraOCINumber()
4157	{
4158	QFETCH( QString, dbName );
4159	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
4160	CHECK_DATABASE( db );
4161	const QString qtest_oraOCINumber(qTableName(prefix: "qtest_oraOCINumber", __FILE__, db));
4162
4163	QSqlQuery q( db );
4164	q.setForwardOnly( true );
4165	QVERIFY_SQL( q, exec( "create table " + qtest_oraOCINumber +
4166	" (col1 number(20), col2 number(20))" ) );
4167	QVERIFY(q.prepare("insert into " + qtest_oraOCINumber + " values (?, ?)"));
4168	QVariantList col1Values;
4169	QVariantList col2Values;
4170	col1Values << (qulonglong)(1)
4171	<< (qulonglong)(0)
4172	<< (qulonglong)(INT_MAX)
4173	<< (qulonglong)(UINT_MAX)
4174	<< (qulonglong)(LONG_MAX)
4175	<< (qulonglong)(ULONG_MAX)
4176	<< (qulonglong)(LLONG_MAX)
4177	<< (qulonglong)(ULLONG_MAX);
4178
4179	col2Values << (qlonglong)(1)
4180	<< (qlonglong)(0)
4181	<< (qlonglong)(-1)
4182	<< (qlonglong)(LONG_MAX)
4183	<< (qlonglong)(LONG_MIN)
4184	<< (qlonglong)(ULONG_MAX)
4185	<< (qlonglong)(LLONG_MAX)
4186	<< (qlonglong)(LLONG_MIN);
4187
4188	q.addBindValue(val: col1Values);
4189	q.addBindValue(val: col2Values);
4190	QVERIFY(q.execBatch());
4191	QString sqlStr = "select * from " + qtest_oraOCINumber + " where col1 = :bindValue0 AND col2 = :bindValue1";
4192	QVERIFY(q.prepare(sqlStr));
4193
4194	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(1), type: QSql::InOut);
4195	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(1), type: QSql::InOut);
4196
4197	QVERIFY_SQL( q, exec() );
4198	QVERIFY( q.next() );
4199	QCOMPARE(q.boundValue( 0 ).toULongLong(), qulonglong(1));
4200	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(1));
4201
4202	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(0), type: QSql::InOut);
4203	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(0), type: QSql::InOut);
4204	QVERIFY_SQL( q, exec() );
4205
4206	QVERIFY( q.next() );
4207	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(0));
4208	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(0));
4209
4210	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(INT_MAX), type: QSql::InOut);
4211	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(-1), type: QSql::InOut);
4212	QVERIFY_SQL( q, exec() );
4213
4214	QVERIFY( q.next() );
4215	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(INT_MAX));
4216	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(-1));
4217
4218	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(UINT_MAX), type: QSql::InOut);
4219	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(LONG_MAX), type: QSql::InOut);
4220	QVERIFY_SQL( q, exec() );
4221
4222	QVERIFY( q.next() );
4223	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(UINT_MAX));
4224	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LONG_MAX));
4225
4226	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(LONG_MAX), type: QSql::InOut);
4227	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(LONG_MIN), type: QSql::InOut);
4228	QVERIFY_SQL( q, exec() );
4229
4230	QVERIFY( q.next() );
4231	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(LONG_MAX));
4232	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LONG_MIN));
4233
4234	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(ULONG_MAX), type: QSql::InOut);
4235	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(ULONG_MAX), type: QSql::InOut);
4236	QVERIFY_SQL( q, exec() );
4237
4238	QVERIFY( q.next() );
4239	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(ULONG_MAX));
4240	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(ULONG_MAX));
4241
4242	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(LLONG_MAX), type: QSql::InOut);
4243	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(LLONG_MAX), type: QSql::InOut);
4244	QVERIFY_SQL( q, exec() );
4245
4246	QVERIFY( q.next() );
4247	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(LLONG_MAX));
4248	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LLONG_MAX));
4249
4250	q.bindValue(placeholder: ":bindValue0", val: (qulonglong)(ULLONG_MAX), type: QSql::InOut);
4251	q.bindValue(placeholder: ":bindValue1", val: (qlonglong)(LLONG_MIN), type: QSql::InOut);
4252	QVERIFY_SQL( q, exec() );
4253
4254	QVERIFY( q.next() );
4255	QCOMPARE(q.boundValue( 0 ).toULongLong(), (qulonglong)(ULLONG_MAX));
4256	QCOMPARE(q.boundValue( 1 ).toLongLong(), (qlonglong)(LLONG_MIN));
4257
4258	}
4259
4260	void tst_QSqlQuery::sqlite_constraint()
4261	{
4262	QFETCH( QString, dbName );
4263	QSqlDatabase db = QSqlDatabase::database( connectionName: dbName );
4264	CHECK_DATABASE( db );
4265
4266	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
4267	if (dbType != QSqlDriver::SQLite)
4268	QSKIP("Sqlite3 specific test");
4269
4270	QSqlQuery q(db);
4271	const QString trigger(qTableName(prefix: "test_constraint", __FILE__, db));
4272
4273	QVERIFY_SQL(q, exec("CREATE TEMP TRIGGER "+trigger+" BEFORE DELETE ON "+qtest+
4274	"\nFOR EACH ROW "
4275	"\nBEGIN"
4276	"\n SELECT RAISE(ABORT, 'Raised Abort successfully');"
4277	"\nEND;"
4278	));
4279
4280	QVERIFY(!q.exec("DELETE FROM "+qtest));
4281	QCOMPARE(q.lastError().databaseText(), QLatin1String("Raised Abort successfully"));
4282	}
4283
4284	void tst_QSqlQuery::sqlite_real()
4285	{
4286	QFETCH(QString, dbName);
4287	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4288	CHECK_DATABASE(db);
4289	const QString tableName(qTableName(prefix: "sqliterealtype", __FILE__, db));
4290	tst_Databases::safeDropTable( db, tableName );
4291
4292	QSqlQuery q(db);
4293	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER, realVal REAL)"));
4294	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, realVal) VALUES (1, 2.3)"));
4295	QVERIFY_SQL(q, exec("SELECT realVal FROM " + tableName));
4296	QVERIFY(q.next());
4297	QCOMPARE(q.value(0).toDouble(), 2.3);
4298	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4299
4300	q.prepare(query: "INSERT INTO " + tableName + " (id, realVal) VALUES (?, ?)");
4301	QVariant var((double)5.6);
4302	q.addBindValue(val: 4);
4303	q.addBindValue(val: var);
4304	QVERIFY_SQL(q, exec());
4305
4306	QVERIFY_SQL(q, exec("SELECT realVal FROM " + tableName + " WHERE ID=4"));
4307	QVERIFY(q.next());
4308	QCOMPARE(q.value(0).toDouble(), 5.6);
4309	}
4310
4311	void tst_QSqlQuery::aggregateFunctionTypes()
4312	{
4313	QFETCH(QString, dbName);
4314	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4315	CHECK_DATABASE(db);
4316	QVariant::Type intType = QVariant::Int;
4317	QVariant::Type sumType = intType;
4318	QVariant::Type countType = intType;
4319	// QPSQL uses LongLong for manipulation of integers
4320	const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
4321	if (dbType == QSqlDriver::PostgreSQL) {
4322	sumType = countType = QVariant::LongLong;
4323	} else if (dbType == QSqlDriver::Oracle) {
4324	intType = sumType = countType = QVariant::Double;
4325	} else if (dbType == QSqlDriver::MySqlServer) {
4326	sumType = QVariant::Double;
4327	countType = QVariant::LongLong;
4328	}
4329	{
4330	const QString tableName(qTableName(prefix: "numericFunctionsWithIntValues", __FILE__, db));
4331	tst_Databases::safeDropTable( db, tableName );
4332
4333	QSqlQuery q(db);
4334	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER)"));
4335
4336	// First test without any entries
4337	QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
4338	QVERIFY(q.next());
4339	if (dbType == QSqlDriver::SQLite)
4340	QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
4341	else
4342	QCOMPARE(q.record().field(0).type(), sumType);
4343
4344	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1)"));
4345	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2)"));
4346
4347	QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
4348	QVERIFY(q.next());
4349	QCOMPARE(q.value(0).toInt(), 3);
4350	QCOMPARE(q.record().field(0).type(), sumType);
4351
4352	QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
4353	QVERIFY(q.next());
4354	if (dbType == QSqlDriver::SQLite || dbType == QSqlDriver::PostgreSQL || dbType == QSqlDriver::MySqlServer
4355	|| dbType == QSqlDriver::Oracle) {
4356	QCOMPARE(q.value(0).toDouble(), 1.5);
4357	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4358	} else {
4359	QCOMPARE(q.value(0).toInt(), 1);
4360	QCOMPARE(q.record().field(0).type(), QVariant::Int);
4361	}
4362
4363	QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
4364	QVERIFY(q.next());
4365	QCOMPARE(q.value(0).toInt(), 2);
4366	QCOMPARE(q.record().field(0).type(), countType);
4367
4368	QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
4369	QVERIFY(q.next());
4370	QCOMPARE(q.value(0).toInt(), 1);
4371	QCOMPARE(q.record().field(0).type(), intType);
4372
4373	QVERIFY_SQL(q, exec("SELECT MAX(id) FROM " + tableName));
4374	QVERIFY(q.next());
4375	QCOMPARE(q.value(0).toInt(), 2);
4376	QCOMPARE(q.record().field(0).type(), intType);
4377	}
4378	{
4379	const QString tableName(qTableName(prefix: "numericFunctionsWithDoubleValues", __FILE__, db));
4380	tst_Databases::safeDropTable( db, tableName );
4381
4382	QSqlQuery q(db);
4383	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id REAL)"));
4384
4385	// First test without any entries
4386	QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
4387	QVERIFY(q.next());
4388	if (dbType == QSqlDriver::SQLite)
4389	QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
4390	else
4391	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4392
4393	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1.5)"));
4394	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2.5)"));
4395
4396	QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
4397	QVERIFY(q.next());
4398	QCOMPARE(q.value(0).toDouble(), 4.0);
4399	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4400
4401	QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
4402	QVERIFY(q.next());
4403	QCOMPARE(q.value(0).toDouble(), 2.0);
4404	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4405
4406	QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
4407	QVERIFY(q.next());
4408	QCOMPARE(q.value(0).toInt(), 2);
4409	QCOMPARE(q.record().field(0).type(), countType);
4410
4411	QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
4412	QVERIFY(q.next());
4413	QCOMPARE(q.value(0).toDouble(), 1.5);
4414	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4415
4416	QVERIFY_SQL(q, exec("SELECT MAX(id) FROM " + tableName));
4417	QVERIFY(q.next());
4418	QCOMPARE(q.value(0).toDouble(), 2.5);
4419	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4420
4421	QString field = "id";
4422
4423	// PSQL does not have the round() function with real type
4424	if (dbType == QSqlDriver::PostgreSQL) {
4425	field += "::NUMERIC";
4426	}
4427
4428	QVERIFY_SQL(q, exec("SELECT ROUND(" + field + ", 1) FROM " + tableName + " WHERE id=1.5"));
4429	QVERIFY(q.next());
4430	QCOMPARE(q.value(0).toDouble(), 1.5);
4431	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4432
4433	QVERIFY_SQL(q, exec("SELECT ROUND(" + field + ", 0) FROM " + tableName + " WHERE id=2.5"));
4434	QVERIFY(q.next());
4435	if (dbType == QSqlDriver::MySqlServer)
4436	QCOMPARE(q.value(0).toDouble(), 2.0);
4437	else
4438	QCOMPARE(q.value(0).toDouble(), 3.0);
4439	QCOMPARE(q.record().field(0).type(), QVariant::Double);
4440	}
4441	{
4442	const QString tableName(qTableName(prefix: "stringFunctions", __FILE__, db));
4443	tst_Databases::safeDropTable( db, tableName );
4444
4445	QSqlQuery q(db);
4446	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER, txt VARCHAR(50))"));
4447
4448	QVERIFY_SQL(q, exec("SELECT MAX(txt) FROM " + tableName));
4449	QVERIFY(q.next());
4450	if (dbType == QSqlDriver::SQLite)
4451	QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
4452	else
4453	QCOMPARE(q.record().field(0).type(), QVariant::String);
4454
4455	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, txt) VALUES (1, 'lower')"));
4456	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, txt) VALUES (2, 'upper')"));
4457
4458	QVERIFY_SQL(q, exec("SELECT MAX(txt) FROM " + tableName));
4459	QVERIFY(q.next());
4460	QCOMPARE(q.value(0).toString(), QLatin1String("upper"));
4461	QCOMPARE(q.record().field(0).type(), QVariant::String);
4462	}
4463	}
4464
4465	template<typename T>
4466	void runIntegralTypesMysqlTest(QSqlDatabase &db, const QString &tableName,
4467	const QString &type, bool withPreparedStatement,
4468	const QVector<T> &values)
4469	{
4470	QVector<QVariant> variantValues;
4471	variantValues.reserve(asize: values.size());
4472
4473	QSqlQuery q(db);
4474	QVERIFY_SQL(q, exec("DROP TABLE IF EXISTS " + tableName));
4475	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id " + type + ')'));
4476
4477	if (withPreparedStatement) {
4478	QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (id) VALUES (?)"));
4479	}
4480	for (int i = 0; i < values.size(); ++i) {
4481	const T v = values.at(i);
4482	if (withPreparedStatement) {
4483	q.bindValue(0, v);
4484	QVERIFY_SQL(q, exec());
4485	} else {
4486	QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (" + QString::number(v) + QLatin1Char(')')));
4487	}
4488	variantValues.append(QVariant::fromValue(v));
4489	}
4490
4491	// ensure we can read them back properly
4492	if (withPreparedStatement) {
4493	QVERIFY_SQL(q, prepare("SELECT id FROM " + tableName));
4494	QVERIFY_SQL(q, exec());
4495	} else {
4496	QVERIFY_SQL(q, exec("SELECT id FROM " + tableName));
4497	}
4498	QVector<T> actualValues;
4499	QVector<QVariant> actualVariantValues;
4500	actualValues.reserve(values.size());
4501	while (q.next()) {
4502	QVariant value = q.value(i: 0);
4503	actualVariantValues << value;
4504	actualValues << value.value<T>();
4505	QVERIFY(actualVariantValues.last().userType() != qMetaTypeId<char>());
4506	QVERIFY(actualVariantValues.last().userType() != qMetaTypeId<signed char>());
4507	QVERIFY(actualVariantValues.last().userType() != qMetaTypeId<unsigned char>());
4508	}
4509	QCOMPARE(actualValues, values);
4510	QCOMPARE(actualVariantValues, variantValues);
4511	}
4512
4513	template<typename T>
4514	void runIntegralTypesMysqlTest(QSqlDatabase &db, const QString &tableName,
4515	const QString &type, const bool withPreparedStatement,
4516	const T min = std::numeric_limits<T>::min(),
4517	const T max = std::numeric_limits<T>::max())
4518	{
4519	// insert some values
4520	const int steps = 20;
4521	const T increment = (max / steps - min / steps);
4522	QVector<T> values;
4523	values.reserve(steps);
4524	T v = min;
4525	for (int i = 0; i < steps; ++i, v += increment)
4526	values.append(v);
4527	runIntegralTypesMysqlTest(db, tableName, type, withPreparedStatement, values);
4528	}
4529
4530	void tst_QSqlQuery::integralTypesMysql()
4531	{
4532	QFETCH(QString, dbName);
4533	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4534	CHECK_DATABASE(db);
4535
4536	const QVector<bool> boolValues = QVector<bool>() << false << true;
4537	for (int i = 0; i < 2; ++i) {
4538	const bool withPreparedStatement = (i == 1);
4539	runIntegralTypesMysqlTest<bool>(db, tableName: "tinyInt1Test", type: "TINYINT(1)", withPreparedStatement, values: boolValues);
4540	runIntegralTypesMysqlTest<bool>(db, tableName: "unsignedTinyInt1Test", type: "TINYINT(1) UNSIGNED", withPreparedStatement, values: boolValues);
4541	runIntegralTypesMysqlTest<qint8>(db, tableName: "tinyIntTest", type: "TINYINT", withPreparedStatement);
4542	runIntegralTypesMysqlTest<quint8>(db, tableName: "unsignedTinyIntTest", type: "TINYINT UNSIGNED", withPreparedStatement);
4543	runIntegralTypesMysqlTest<qint16>(db, tableName: "smallIntTest", type: "SMALLINT", withPreparedStatement);
4544	runIntegralTypesMysqlTest<quint16>(db, tableName: "unsignedSmallIntTest", type: "SMALLINT UNSIGNED", withPreparedStatement);
4545	runIntegralTypesMysqlTest<qint32>(db, tableName: "mediumIntTest", type: "MEDIUMINT", withPreparedStatement, min: -(1 << 23), max: (1 << 23) - 1);
4546	runIntegralTypesMysqlTest<quint32>(db, tableName: "unsignedMediumIntTest", type: "MEDIUMINT UNSIGNED", withPreparedStatement, min: 0, max: (1 << 24) - 1);
4547	runIntegralTypesMysqlTest<qint32>(db, tableName: "intTest", type: "INT", withPreparedStatement);
4548	runIntegralTypesMysqlTest<quint32>(db, tableName: "unsignedIntTest", type: "INT UNSIGNED", withPreparedStatement);
4549	runIntegralTypesMysqlTest<qint64>(db, tableName: "bigIntTest", type: "BIGINT", withPreparedStatement);
4550	runIntegralTypesMysqlTest<quint64>(db, tableName: "unsignedBigIntTest", type: "BIGINT UNSIGNED", withPreparedStatement);
4551	}
4552	}
4553
4554	void tst_QSqlQuery::QTBUG_57138()
4555	{
4556	QDateTime utc = QDateTime(QDate(2150, 1, 5), QTime(14, 0, 0, 123), Qt::UTC);
4557	QDateTime localtime = QDateTime(QDate(2150, 1, 5), QTime(14, 0, 0, 123), Qt::LocalTime);
4558	QDateTime tzoffset = QDateTime(QDate(2150, 1, 5), QTime(14, 0, 0, 123), Qt::OffsetFromUTC, 3600);
4559
4560	QFETCH(QString, dbName);
4561	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4562	CHECK_DATABASE(db);
4563
4564	QSqlQuery create(db);
4565	QString tableName = qTableName(prefix: "qtbug57138", __FILE__, db);
4566	tst_Databases::safeDropTable(db, tableName);
4567
4568	QVERIFY_SQL(create, exec("create table " + tableName + " (id int, dt_utc datetime, dt_lt datetime, dt_tzoffset datetime)"));
4569	QVERIFY_SQL(create, prepare("insert into " + tableName + " (id, dt_utc, dt_lt, dt_tzoffset) values (?, ?, ?, ?)"));
4570
4571	create.addBindValue(val: 0);
4572	create.addBindValue(val: utc);
4573	create.addBindValue(val: localtime);
4574	create.addBindValue(val: tzoffset);
4575
4576	QVERIFY_SQL(create, exec());
4577
4578	QSqlQuery q(db);
4579	q.prepare(query: "SELECT dt_utc, dt_lt, dt_tzoffset FROM " + tableName + " WHERE id = ?");
4580	q.addBindValue(val: 0);
4581
4582	QVERIFY_SQL(q, exec());
4583	QVERIFY(q.next());
4584
4585	QCOMPARE(q.value(0).toDateTime(), utc);
4586	QCOMPARE(q.value(1).toDateTime(), localtime);
4587	QCOMPARE(q.value(2).toDateTime(), tzoffset);
4588	}
4589
4590	void tst_QSqlQuery::QTBUG_73286()
4591	{
4592	QFETCH(QString, dbName);
4593	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4594	CHECK_DATABASE(db);
4595
4596	QSqlQuery create(db);
4597	QString tableName = qTableName(prefix: "qtbug73286", __FILE__, db);
4598	tst_Databases::safeDropTable(db, tableName);
4599
4600	QVERIFY_SQL(create, exec("create table " + tableName + " (dec2 decimal(4,2), dec0 decimal(20,0), dec3 decimal(20,3))"));
4601	QVERIFY_SQL(create, prepare("insert into " + tableName + " (dec2, dec0, dec3) values (?, ?, ?)"));
4602
4603	create.addBindValue(val: "99.99");
4604	create.addBindValue(val: "12345678901234567890");
4605	create.addBindValue(val: "12345678901234567.890");
4606
4607	QVERIFY_SQL(create, exec());
4608
4609	QSqlQuery q(db);
4610	q.prepare(query: "SELECT dec2, dec0, dec3 FROM " + tableName);
4611	q.setNumericalPrecisionPolicy(QSql::HighPrecision);
4612
4613	QVERIFY_SQL(q, exec());
4614	QVERIFY(q.next());
4615
4616	QCOMPARE(q.value(0).toString(), "99.99");
4617	QCOMPARE(q.value(1).toString(), "12345678901234567890");
4618	QCOMPARE(q.value(2).toString(), "12345678901234567.890");
4619	}
4620
4621	void tst_QSqlQuery::dateTime_data()
4622	{
4623	QTest::addColumn<QString>(name: "dbName");
4624	QTest::addColumn<QString>(name: "tableName");
4625	QTest::addColumn<QString>(name: "createTableString");
4626	QTest::addColumn<QList<QDateTime> >(name: "initialDateTimes");
4627	QTest::addColumn<QList<QDateTime> >(name: "expectedDateTimes");
4628
4629	#if QT_CONFIG(timezone)
4630	// Using time zones which are highly unlikely to be the same as the testing machine's one
4631	// as it could pass as a result despite it.
4632	// +8.5 hours from UTC to North Korea
4633	const QTimeZone afterUTCTimeZone(30600);
4634	// -8 hours from UTC to Belize
4635	const QTimeZone beforeUTCTimeZone(-28800);
4636	const QTimeZone utcTimeZone("UTC");
4637
4638	const QDateTime dtWithAfterTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), afterUTCTimeZone);
4639	const QDateTime dtWithBeforeTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), beforeUTCTimeZone);
4640	const QDateTime dtWithUTCTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), utcTimeZone);
4641	#endif
4642	const QDateTime dt(QDate(2015, 5, 18), QTime(4, 26, 30));
4643	const QDateTime dtWithMS(QDate(2015, 5, 18), QTime(4, 26, 30, 500));
4644	const QList<QDateTime> dateTimes = {
4645	dt, dtWithMS,
4646	#if QT_CONFIG(timezone)
4647	dtWithAfterTZ, dtWithBeforeTZ, dtWithUTCTZ
4648	#endif
4649	};
4650	const QList<QDateTime> expectedDateTimesLocalTZ = {
4651	dt, dtWithMS,
4652	#if QT_CONFIG(timezone)
4653	dtWithAfterTZ.toLocalTime(), dtWithBeforeTZ.toLocalTime(), dtWithUTCTZ.toLocalTime()
4654	#endif
4655	};
4656	const QList<QDateTime> expectedTimeStampDateTimes = {
4657	dt, dtWithMS,
4658	#if QT_CONFIG(timezone)
4659	dtWithMS, dtWithMS, dtWithMS
4660	#endif
4661	};
4662	const QList<QDateTime> expectedDateTimes = {
4663	dt, dt,
4664	#if QT_CONFIG(timezone)
4665	dt, dt, dt
4666	#endif
4667	};
4668
4669	for (const QString &dbName : qAsConst(t&: dbs.dbNames)) {
4670	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4671	if (!db.isValid())
4672	continue;
4673	const QString tableNameTSWithTimeZone(qTableName(prefix: "dateTimeTSWithTimeZone", __FILE__, db));
4674	const QString tableNameTSWithLocalTimeZone(qTableName(prefix: "dateTimeTSWithLocalTimeZone", __FILE__, db));
4675	const QString tableNameTS(qTableName(prefix: "dateTimeTS", __FILE__, db));
4676	const QString tableNameDate(qTableName(prefix: "dateTimeDate", __FILE__, db));
4677	QTest::newRow(dataTag: QString(dbName + " timestamp with time zone").toLatin1())
4678	<< dbName << tableNameTSWithTimeZone
4679	<< QStringLiteral(" (dt TIMESTAMP WITH TIME ZONE)")
4680	<< dateTimes << dateTimes;
4681	QTest::newRow(dataTag: QString(dbName + " timestamp with local time zone").toLatin1())
4682	<< dbName << tableNameTSWithTimeZone
4683	<< QStringLiteral(" (dt TIMESTAMP WITH LOCAL TIME ZONE)")
4684	<< dateTimes << expectedDateTimesLocalTZ;
4685	QTest::newRow(dataTag: QString(dbName + "timestamp").toLatin1())
4686	<< dbName << tableNameTS << QStringLiteral(" (dt TIMESTAMP(3))")
4687	<< dateTimes << expectedTimeStampDateTimes;
4688	QTest::newRow(dataTag: QString(dbName + "date").toLatin1())
4689	<< dbName << tableNameDate << QStringLiteral(" (dt DATE)")
4690	<< dateTimes << expectedDateTimes;
4691	}
4692	}
4693
4694	void tst_QSqlQuery::dateTime()
4695	{
4696	QFETCH(QString, dbName);
4697	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4698	CHECK_DATABASE(db);
4699
4700	QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
4701	if (dbType != QSqlDriver::Oracle)
4702	QSKIP("Implemented only for Oracle");
4703
4704	QFETCH(QString, tableName);
4705	QFETCH(QString, createTableString);
4706	QFETCH(QList<QDateTime>, initialDateTimes);
4707	QFETCH(QList<QDateTime>, expectedDateTimes);
4708
4709	tst_Databases::safeDropTable(db, tableName);
4710
4711	QSqlQuery q(db);
4712	QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + createTableString));
4713	for (const QDateTime &dt : qAsConst(t&: initialDateTimes)) {
4714	QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " values(:dt)"));
4715	q.bindValue(placeholder: ":dt", val: dt);
4716	QVERIFY_SQL(q, exec());
4717	}
4718	QVERIFY_SQL(q, exec("SELECT * FROM " + tableName));
4719	for (const QDateTime &dt : qAsConst(t&: expectedDateTimes)) {
4720	QVERIFY(q.next());
4721	QCOMPARE(q.value(0).toDateTime(), dt);
4722	}
4723	}
4724
4725	void tst_QSqlQuery::sqliteVirtualTable()
4726	{
4727	// Virtual tables can behave differently when it comes to prepared
4728	// queries, so we need to check these explicitly
4729	QFETCH(QString, dbName);
4730	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4731	CHECK_DATABASE(db);
4732	const auto tableName = qTableName(prefix: "sqliteVirtual", __FILE__, db);
4733	QSqlQuery qry(db);
4734	QVERIFY_SQL(qry, exec("create virtual table " + tableName + " using fts3(id, name)"));
4735
4736	// Delibrately malform the query to try and provoke a potential crash situation
4737	QVERIFY_SQL(qry, prepare("select * from " + tableName + " where name match '?'"));
4738	qry.addBindValue(val: "Andy");
4739	QVERIFY(!qry.exec());
4740
4741	QVERIFY_SQL(qry, prepare("insert into " + tableName + "(id, name) VALUES (?, ?)"));
4742	qry.addBindValue(val: 1);
4743	qry.addBindValue(val: "Andy");
4744	QVERIFY_SQL(qry, exec());
4745
4746	QVERIFY_SQL(qry, exec("select * from " + tableName));
4747	QVERIFY(qry.next());
4748	QCOMPARE(qry.value(0).toInt(), 1);
4749	QCOMPARE(qry.value(1).toString(), "Andy");
4750
4751	QVERIFY_SQL(qry, prepare("insert into " + tableName + "(id, name) values (:id, :name)"));
4752	qry.bindValue(placeholder: ":id", val: 2);
4753	qry.bindValue(placeholder: ":name", val: "Peter");
4754	QVERIFY_SQL(qry, exec());
4755
4756	QVERIFY_SQL(qry, prepare("select * from " + tableName + " where name match ?"));
4757	qry.addBindValue(val: "Peter");
4758	QVERIFY_SQL(qry, exec());
4759	QVERIFY(qry.next());
4760	QCOMPARE(qry.value(0).toInt(), 2);
4761	QCOMPARE(qry.value(1).toString(), "Peter");
4762	}
4763
4764	void tst_QSqlQuery::mysql_timeType()
4765	{
4766	// The TIME data type is different to the standard with MySQL as it has a range of
4767	// '-838:59:59' to '838:59:59'.
4768	QFETCH(QString, dbName);
4769	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4770	CHECK_DATABASE(db);
4771	const auto tableName = qTableName(prefix: "mysqlTimeType", __FILE__, db);
4772	tst_Databases::safeDropTables(db, tableNames: { tableName });
4773	QSqlQuery qry(db);
4774	QVERIFY_SQL(qry, exec("create table " + tableName + " (t time(6))"));
4775
4776	// MySQL will convert days into hours and add them together so 17 days 11 hours becomes 419 hours
4777	const QStringList timeData = { "-838:59:59.000000", "-123:45:56.789", "000:00:00.0", "123:45:56.789",
4778	"838:59:59.000000", "15:50", "12", "1213", "0 1:2:3", "17 11:22:33" };
4779	const QStringList resultTimeData = { "-838:59:59.000000", "-123:45:56.789000", "00:00:00.000000",
4780	"123:45:56.789000", "838:59:59.000000", "15:50:00.000000", "00:00:12.000000", "00:12:13.000000",
4781	"01:02:03.000000", "419:22:33.000000" };
4782	for (const QString &time : timeData)
4783	QVERIFY_SQL(qry, exec("insert into " + tableName + " (t) VALUES ('" + time + "')"));
4784
4785	QVERIFY_SQL(qry, exec("select * from " + tableName));
4786	for (const QString &time : qAsConst(t: resultTimeData)) {
4787	QVERIFY(qry.next());
4788	QCOMPARE(qry.value(0).toString(), time);
4789	}
4790
4791	QVERIFY_SQL(qry, exec("delete from " + tableName));
4792	for (const QString &time : timeData) {
4793	QVERIFY_SQL(qry, prepare("insert into " + tableName + " (t) VALUES (:time)"));
4794	qry.bindValue(pos: 0, val: time);
4795	QVERIFY_SQL(qry, exec());
4796	}
4797	QVERIFY_SQL(qry, exec("select * from " + tableName));
4798	for (const QString &time : resultTimeData) {
4799	QVERIFY(qry.next());
4800	QCOMPARE(qry.value(0).toString(), time);
4801	}
4802
4803	QVERIFY_SQL(qry, exec("delete from " + tableName));
4804	const QList<QTime> qTimeBasedData = { QTime(), QTime(1, 2, 3, 4), QTime(0, 0, 0, 0), QTime(23,59,59,999) };
4805	for (const QTime &time : qTimeBasedData) {
4806	QVERIFY_SQL(qry, prepare("insert into " + tableName + " (t) VALUES (:time)"));
4807	qry.bindValue(pos: 0, val: time);
4808	QVERIFY_SQL(qry, exec());
4809	}
4810	QVERIFY_SQL(qry, exec("select * from " + tableName));
4811	for (const QTime &time : qTimeBasedData) {
4812	QVERIFY(qry.next());
4813	QCOMPARE(qry.value(0).toTime(), time);
4814	}
4815	}
4816
4817	void tst_QSqlQuery::ibaseArray()
4818	{
4819	QFETCH(QString, dbName);
4820	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4821	CHECK_DATABASE(db);
4822
4823	const auto arrayTable = qTableName(prefix: "ibasearray", __FILE__, db);
4824	tst_Databases::safeDropTable(db, tableName: arrayTable);
4825	QSqlQuery qry(db);
4826	QVERIFY_SQL(qry, exec("create table " + arrayTable + " (intData int[0:4], longData bigint[5], "
4827	"charData varchar(255)[5])"));
4828	QVERIFY_SQL(qry, prepare("insert into " + arrayTable + " (intData, longData, charData) "
4829	"values(?, ?, ?)"));
4830	const auto intArray = QVariant{QVariantList{1, 2, 3, 4711, 815}};
4831	const auto charArray = QVariant{QVariantList{"AAA", "BBB", "CCC", "DDD", "EEE"}};
4832	qry.bindValue(pos: 0, val: intArray);
4833	qry.bindValue(pos: 1, val: intArray);
4834	qry.bindValue(pos: 2, val: charArray);
4835	QVERIFY_SQL(qry, exec());
4836	QVERIFY_SQL(qry, exec("select * from " + arrayTable));
4837	QVERIFY(qry.next());
4838	QCOMPARE(qry.value(0).toList(), intArray.toList());
4839	QCOMPARE(qry.value(1).toList(), intArray.toList());
4840	QCOMPARE(qry.value(2).toList(), charArray.toList());
4841	}
4842
4843	void tst_QSqlQuery::ibase_executeBlock()
4844	{
4845	QFETCH(QString, dbName);
4846	QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
4847	CHECK_DATABASE(db);
4848	QSqlQuery qry(db);
4849	QVERIFY_SQL(qry, prepare("execute block (x double precision = ?, y double precision = ?) "
4850	"returns (total double precision) "
4851	"as "
4852	"begin "
4853	"total = :x + :y; "
4854	"suspend; "
4855	"end"));
4856	qry.bindValue(pos: 0, val: 2);
4857	qry.bindValue(pos: 1, val: 2);
4858	QVERIFY_SQL(qry, exec());
4859	QVERIFY(qry.next());
4860	QCOMPARE(qry.value(0).toInt(), 4);
4861	}
4862
4863	QTEST_MAIN( tst_QSqlQuery )
4864	#include "tst_qsqlquery.moc"
4865