1/****************************************************************************
2**
3** Copyright (C) 2016 The Qt Company Ltd.
4** Contact: https://www.qt.io/licensing/
5**
6** This file is part of the documentation of the Qt Toolkit.
7**
8** $QT_BEGIN_LICENSE:BSD$
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** BSD License Usage
18** Alternatively, you may use this file under the terms of the BSD license
19** as follows:
20**
21** "Redistribution and use in source and binary forms, with or without
22** modification, are permitted provided that the following conditions are
23** met:
24** * Redistributions of source code must retain the above copyright
25** notice, this list of conditions and the following disclaimer.
26** * Redistributions in binary form must reproduce the above copyright
27** notice, this list of conditions and the following disclaimer in
28** the documentation and/or other materials provided with the
29** distribution.
30** * Neither the name of The Qt Company Ltd nor the names of its
31** contributors may be used to endorse or promote products derived
32** from this software without specific prior written permission.
33**
34**
35** THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
36** "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
37** LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
38** A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
39** OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
40** SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
41** LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
42** DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
43** THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
44** (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
45** OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE."
46**
47** $QT_END_LICENSE$
48**
49****************************************************************************/
50
51#include <QCoreApplication>
52#include <QtSql>
53#include <QMap>
54#include <iostream>
55
56using namespace std;
57
58void QSqlDatabase_snippets()
59{
60 {
61//! [0]
62 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QPSQL");
63 db.setHostName("acidalia");
64 db.setDatabaseName("customdb");
65 db.setUserName("mojito");
66 db.setPassword("J0a1m8");
67 bool ok = db.open();
68//! [0]
69 Q_UNUSED(ok);
70 }
71
72 {
73//! [1]
74 QSqlDatabase db = QSqlDatabase::database();
75//! [1]
76 }
77}
78
79void QSqlField_snippets()
80{
81#if 0
82 {
83//! [2]
84 QSqlField field("age", QVariant::Int);
85 field.setValue(QPixmap()); // WRONG
86//! [2]
87 }
88#endif
89
90 {
91//! [3]
92 QSqlField field("age", QVariant::Int);
93 field.setValue(QString("123")); // casts QString to int
94//! [3]
95 }
96
97 {
98//! [4]
99 QSqlQuery query;
100//! [4] //! [5]
101 QSqlRecord record = query.record();
102//! [5] //! [6]
103 QSqlField field = record.field(name: "country");
104//! [6]
105 }
106}
107
108void doSomething(const QString &)
109{
110}
111
112void QSqlQuery_snippets()
113{
114 {
115 // typical loop
116//! [7]
117 QSqlQuery query("SELECT country FROM artist");
118 while (query.next()) {
119 QString country = query.value(i: 0).toString();
120 doSomething(country);
121 }
122//! [7]
123 }
124
125 {
126 // field index lookup
127//! [8]
128 QSqlQuery query("SELECT * FROM artist");
129 int fieldNo = query.record().indexOf(name: "country");
130 while (query.next()) {
131 QString country = query.value(i: fieldNo).toString();
132 doSomething(country);
133 }
134//! [8]
135 }
136
137 {
138 // named with named
139//! [9]
140 QSqlQuery query;
141 query.prepare(query: "INSERT INTO person (id, forename, surname) "
142 "VALUES (:id, :forename, :surname)");
143 query.bindValue(placeholder: ":id", val: 1001);
144 query.bindValue(placeholder: ":forename", val: "Bart");
145 query.bindValue(placeholder: ":surname", val: "Simpson");
146 query.exec();
147//! [9]
148 }
149
150 {
151 // positional with named
152//! [10]
153 QSqlQuery query;
154 query.prepare(query: "INSERT INTO person (id, forename, surname) "
155 "VALUES (:id, :forename, :surname)");
156 query.bindValue(pos: 0, val: 1001);
157 query.bindValue(pos: 1, val: "Bart");
158 query.bindValue(pos: 2, val: "Simpson");
159 query.exec();
160//! [10]
161 }
162
163 {
164 // positional 1
165//! [11]
166 QSqlQuery query;
167 query.prepare(query: "INSERT INTO person (id, forename, surname) "
168 "VALUES (?, ?, ?)");
169 query.bindValue(pos: 0, val: 1001);
170 query.bindValue(pos: 1, val: "Bart");
171 query.bindValue(pos: 2, val: "Simpson");
172 query.exec();
173//! [11]
174 }
175
176 {
177 // positional 2
178//! [12]
179 QSqlQuery query;
180 query.prepare(query: "INSERT INTO person (id, forename, surname) "
181 "VALUES (?, ?, ?)");
182 query.addBindValue(val: 1001);
183 query.addBindValue(val: "Bart");
184 query.addBindValue(val: "Simpson");
185 query.exec();
186//! [12]
187 }
188
189 {
190 // stored
191//! [13]
192 QSqlQuery query;
193 query.prepare(query: "CALL AsciiToInt(?, ?)");
194 query.bindValue(pos: 0, val: "A");
195 query.bindValue(pos: 1, val: 0, type: QSql::Out);
196 query.exec();
197 int i = query.boundValue(pos: 1).toInt(); // i is 65
198//! [13]
199 Q_UNUSED(i);
200 }
201
202 QSqlQuery query;
203
204 {
205 // examine with named binding
206//! [14]
207 QMap<QString, QVariant> sqlIterator(query.boundValues());
208 for (auto i = sqlIterator.begin(); i != sqlIterator.end(); ++i) {
209 cout << i.key().toUtf8().data() << ": "
210 << i.value().toString().toUtf8().data() << "\n";
211 }
212//! [14]
213 }
214
215 {
216 // examine with positional binding
217//! [15]
218 QList<QVariant> list = query.boundValues().values();
219 for (int i = 0; i < list.size(); ++i)
220 cout << i << ": " << list.at(i).toString().toUtf8().data() << "\n";
221//! [15]
222 }
223}
224
225void QSqlQueryModel_snippets()
226{
227
228//! [21]
229 QSqlQueryModel model;
230 model.setQuery(query: "SELECT name, salary FROM employee");
231 int salary = model.record(row: 4).value(name: "salary").toInt();
232//! [21]
233 Q_UNUSED(salary);
234
235 {
236//! [22]
237 int salary = model.data(item: model.index(row: 4, column: 1)).toInt();
238//! [22]
239 Q_UNUSED(salary);
240 }
241
242 for (int row = 0; row < model.rowCount(); ++row) {
243 for (int col = 0; col < model.columnCount(); ++col) {
244 qDebug() << model.data(item: model.index(row, column: col));
245 }
246 }
247}
248
249class MyModel : public QSqlQueryModel
250{
251public:
252 QVariant data(const QModelIndex &item, int role) const override;
253 void fetchModel();
254
255 int m_specialColumnNo;
256};
257
258//! [23]
259QVariant MyModel::data(const QModelIndex &item, int role) const
260{
261 if (item.column() == m_specialColumnNo) {
262 // handle column separately
263 }
264 return QSqlQueryModel::data(item, role);
265}
266//! [23]
267
268void QSqlTableModel_snippets()
269{
270 {
271//! [25]
272 QSqlTableModel model;
273 model.setTable("employee");
274 model.select();
275 int salary = model.record(row: 4).value(name: "salary").toInt();
276//! [25]
277 Q_UNUSED(salary);
278 }
279}
280
281void sql_intro_snippets()
282{
283 {
284//! [26]
285 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QMYSQL");
286 db.setHostName("bigblue");
287 db.setDatabaseName("flightdb");
288 db.setUserName("acarlson");
289 db.setPassword("1uTbSbAs");
290 bool ok = db.open();
291//! [26]
292 Q_UNUSED(ok);
293 }
294
295 {
296//! [27]
297 QSqlDatabase firstDB = QSqlDatabase::addDatabase(type: "QMYSQL", connectionName: "first");
298 QSqlDatabase secondDB = QSqlDatabase::addDatabase(type: "QMYSQL", connectionName: "second");
299//! [27]
300 }
301
302 {
303//! [28]
304 QSqlDatabase defaultDB = QSqlDatabase::database();
305//! [28] //! [29]
306 QSqlDatabase firstDB = QSqlDatabase::database(connectionName: "first");
307//! [29] //! [30]
308 QSqlDatabase secondDB = QSqlDatabase::database(connectionName: "second");
309//! [30]
310 }
311
312 {
313 // SELECT1
314//! [31]
315 QSqlQuery query;
316 query.exec(query: "SELECT name, salary FROM employee WHERE salary > 50000");
317//! [31]
318
319//! [32]
320 while (query.next()) {
321 QString name = query.value(i: 0).toString();
322 int salary = query.value(i: 1).toInt();
323 qDebug() << name << salary;
324 }
325//! [32]
326 }
327
328 {
329 // FEATURE
330//! [33]
331 QSqlQuery query;
332 int numRows;
333 query.exec(query: "SELECT name, salary FROM employee WHERE salary > 50000");
334
335 QSqlDatabase defaultDB = QSqlDatabase::database();
336 if (defaultDB.driver()->hasFeature(f: QSqlDriver::QuerySize)) {
337 numRows = query.size();
338 } else {
339 // this can be very slow
340 query.last();
341 numRows = query.at() + 1;
342 }
343//! [33]
344 Q_UNUSED(numRows);
345 }
346
347 {
348 // INSERT1
349//! [34]
350 QSqlQuery query;
351 query.exec(query: "INSERT INTO employee (id, name, salary) "
352 "VALUES (1001, 'Thad Beaumont', 65000)");
353//! [34]
354 }
355
356 {
357 // NAMED BINDING
358//! [35]
359 QSqlQuery query;
360 query.prepare(query: "INSERT INTO employee (id, name, salary) "
361 "VALUES (:id, :name, :salary)");
362 query.bindValue(placeholder: ":id", val: 1001);
363 query.bindValue(placeholder: ":name", val: "Thad Beaumont");
364 query.bindValue(placeholder: ":salary", val: 65000);
365 query.exec();
366//! [35]
367 }
368
369 {
370 // POSITIONAL BINDING
371//! [36]
372 QSqlQuery query;
373 query.prepare(query: "INSERT INTO employee (id, name, salary) "
374 "VALUES (?, ?, ?)");
375 query.addBindValue(val: 1001);
376 query.addBindValue(val: "Thad Beaumont");
377 query.addBindValue(val: 65000);
378 query.exec();
379//! [36]
380 }
381
382 {
383 // UPDATE1
384//! [37]
385 QSqlQuery query;
386 query.exec(query: "UPDATE employee SET salary = 70000 WHERE id = 1003");
387//! [37]
388 }
389
390 {
391 // DELETE1
392//! [38]
393 QSqlQuery query;
394 query.exec(query: "DELETE FROM employee WHERE id = 1007");
395//! [38]
396 }
397
398 {
399 // TRANSACTION
400//! [39]
401 QSqlDatabase::database().transaction();
402 QSqlQuery query;
403 query.exec(query: "SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
404 if (query.next()) {
405 int employeeId = query.value(i: 0).toInt();
406 query.exec(query: "INSERT INTO project (id, name, ownerid) "
407 "VALUES (201, 'Manhattan Project', "
408 + QString::number(employeeId) + ')');
409 }
410 QSqlDatabase::database().commit();
411//! [39]
412 }
413
414 {
415 // SQLQUERYMODEL1
416//! [40]
417 QSqlQueryModel model;
418 model.setQuery(query: "SELECT * FROM employee");
419
420 for (int i = 0; i < model.rowCount(); ++i) {
421 int id = model.record(row: i).value(name: "id").toInt();
422 QString name = model.record(row: i).value(name: "name").toString();
423 qDebug() << id << name;
424 }
425//! [40]
426 }
427
428 {
429 // SQLTABLEMODEL1
430//! [41]
431 QSqlTableModel model;
432 model.setTable("employee");
433 model.setFilter("salary > 50000");
434 model.setSort(column: 2, order: Qt::DescendingOrder);
435 model.select();
436
437 for (int i = 0; i < model.rowCount(); ++i) {
438 QString name = model.record(row: i).value(name: "name").toString();
439 int salary = model.record(row: i).value(name: "salary").toInt();
440 qDebug() << name << salary;
441 }
442//! [41]
443 }
444
445 {
446 // SQLTABLEMODEL2
447 QSqlTableModel model;
448 model.setTable("employee");
449
450//! [42]
451 for (int i = 0; i < model.rowCount(); ++i) {
452 QSqlRecord record = model.record(row: i);
453 double salary = record.value(name: "salary").toInt();
454 salary *= 1.1;
455 record.setValue(name: "salary", val: salary);
456 model.setRecord(row: i, record);
457 }
458 model.submitAll();
459//! [42]
460
461 // SQLTABLEMODEL3
462 int row = 1;
463 int column = 2;
464//! [43]
465 model.setData(index: model.index(row, column), value: 75000);
466 model.submitAll();
467//! [43]
468
469 // SQLTABLEMODEL4
470//! [44]
471 model.insertRows(row, count: 1);
472 model.setData(index: model.index(row, column: 0), value: 1013);
473 model.setData(index: model.index(row, column: 1), value: "Peter Gordon");
474 model.setData(index: model.index(row, column: 2), value: 68500);
475 model.submitAll();
476//! [44]
477
478//! [45]
479 model.removeRows(row, count: 5);
480//! [45] //! [46]
481 model.submitAll();
482//! [46]
483 }
484}
485
486//! [47]
487class XyzResult : public QSqlResult
488{
489public:
490 XyzResult(const QSqlDriver *driver)
491 : QSqlResult(driver) {}
492 ~XyzResult() {}
493
494protected:
495 QVariant data(int /* index */) override { return QVariant(); }
496 bool isNull(int /* index */) override { return false; }
497 bool reset(const QString & /* query */) override { return false; }
498 bool fetch(int /* index */) override { return false; }
499 bool fetchFirst() override { return false; }
500 bool fetchLast() override { return false; }
501 int size() override { return 0; }
502 int numRowsAffected() override { return 0; }
503 QSqlRecord record() const override { return QSqlRecord(); }
504};
505//! [47]
506
507//! [48]
508class XyzDriver : public QSqlDriver
509{
510public:
511 XyzDriver() {}
512 ~XyzDriver() {}
513
514 bool hasFeature(DriverFeature /* feature */) const override { return false; }
515 bool open(const QString & /* db */, const QString & /* user */,
516 const QString & /* password */, const QString & /* host */,
517 int /* port */, const QString & /* options */) override
518 { return false; }
519 void close() override {}
520 QSqlResult *createResult() const override { return new XyzResult(this); }
521};
522//! [48]
523
524int main(int argc, char **argv)
525{
526 QCoreApplication app(argc, argv);
527
528 QSqlDatabase_snippets();
529 QSqlField_snippets();
530 QSqlQuery_snippets();
531 QSqlQueryModel_snippets();
532 QSqlTableModel_snippets();
533
534 XyzDriver driver;
535 XyzResult result(&driver);
536}
537

source code of qtbase/src/sql/doc/snippets/sqldatabase/sqldatabase.cpp