1// Copyright (C) 2016 The Qt Company Ltd.
2// SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
3
4#include <QCoreApplication>
5#include <QtSql>
6#include <iostream>
7
8using namespace std;
9
10void QSqlDatabase_snippets()
11{
12 {
13//! [0]
14 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QPSQL");
15 db.setHostName("acidalia");
16 db.setDatabaseName("customdb");
17 db.setUserName("mojito");
18 db.setPassword("J0a1m8");
19 bool ok = db.open();
20//! [0]
21 Q_UNUSED(ok);
22 }
23
24 {
25//! [1]
26 QSqlDatabase db = QSqlDatabase::database();
27//! [1]
28 }
29}
30
31void QSqlField_snippets()
32{
33#if 0
34 {
35//! [2]
36 QSqlField field("age", QMetaType::fromType<int>());
37 field.setValue(QPixmap()); // WRONG
38//! [2]
39 }
40#endif
41
42 {
43//! [3]
44 QSqlField field("age", QMetaType::fromType<int>());
45 field.setValue(QString("123")); // casts QString to int
46//! [3]
47 }
48
49 {
50//! [4]
51 QSqlQuery query;
52//! [4] //! [5]
53 QSqlRecord record = query.record();
54//! [5] //! [6]
55 QSqlField field = record.field(name: "country");
56//! [6]
57 }
58}
59
60void doSomething(const QString &)
61{
62}
63
64void QSqlQuery_snippets()
65{
66 {
67 // typical loop
68//! [7]
69 QSqlQuery query("SELECT country FROM artist");
70 while (query.next()) {
71 QString country = query.value(i: 0).toString();
72 doSomething(country);
73 }
74//! [7]
75 }
76
77 {
78 // field index lookup
79//! [8]
80 QSqlQuery query("SELECT * FROM artist");
81 int fieldNo = query.record().indexOf(name: "country");
82 while (query.next()) {
83 QString country = query.value(i: fieldNo).toString();
84 doSomething(country);
85 }
86//! [8]
87 }
88
89 {
90 // named with named
91//! [9]
92 QSqlQuery query;
93 query.prepare(query: "INSERT INTO person (id, forename, surname) "
94 "VALUES (:id, :forename, :surname)");
95 query.bindValue(placeholder: ":id", val: 1001);
96 query.bindValue(placeholder: ":forename", val: "Bart");
97 query.bindValue(placeholder: ":surname", val: "Simpson");
98 query.exec();
99//! [9]
100 }
101
102 {
103 // positional with named
104//! [10]
105 QSqlQuery query;
106 query.prepare(query: "INSERT INTO person (id, forename, surname) "
107 "VALUES (:id, :forename, :surname)");
108 query.bindValue(pos: 0, val: 1001);
109 query.bindValue(pos: 1, val: "Bart");
110 query.bindValue(pos: 2, val: "Simpson");
111 query.exec();
112//! [10]
113 }
114
115 {
116 // positional 1
117//! [11]
118 QSqlQuery query;
119 query.prepare(query: "INSERT INTO person (id, forename, surname) "
120 "VALUES (?, ?, ?)");
121 query.bindValue(pos: 0, val: 1001);
122 query.bindValue(pos: 1, val: "Bart");
123 query.bindValue(pos: 2, val: "Simpson");
124 query.exec();
125//! [11]
126 }
127
128 {
129 // positional 2
130//! [12]
131 QSqlQuery query;
132 query.prepare(query: "INSERT INTO person (id, forename, surname) "
133 "VALUES (?, ?, ?)");
134 query.addBindValue(val: 1001);
135 query.addBindValue(val: "Bart");
136 query.addBindValue(val: "Simpson");
137 query.exec();
138//! [12]
139 }
140
141 {
142 // stored
143//! [13]
144 QSqlQuery query;
145 query.prepare(query: "CALL AsciiToInt(?, ?)");
146 query.bindValue(pos: 0, val: "A");
147 query.bindValue(pos: 1, val: 0, type: QSql::Out);
148 query.exec();
149 int i = query.boundValue(pos: 1).toInt(); // i is 65
150//! [13]
151 Q_UNUSED(i);
152 }
153
154 QSqlQuery query;
155
156 {
157 // examine with named or positional binding
158//! [14]
159 const QVariantList list = query.boundValues();
160 for (qsizetype i = 0; i < list.size(); ++i)
161 qDebug() << i << ":" << list.at(i).toString();
162//! [14]
163 }
164}
165
166void QSqlQueryModel_snippets()
167{
168
169//! [21]
170 QSqlQueryModel model;
171 model.setQuery(query: "SELECT name, salary FROM employee");
172 int salary = model.record(row: 4).value(name: "salary").toInt();
173//! [21]
174 Q_UNUSED(salary);
175
176 {
177//! [22]
178 int salary = model.data(item: model.index(row: 4, column: 1)).toInt();
179//! [22]
180 Q_UNUSED(salary);
181 }
182
183 for (int row = 0; row < model.rowCount(); ++row) {
184 for (int col = 0; col < model.columnCount(); ++col) {
185 qDebug() << model.data(item: model.index(row, column: col));
186 }
187 }
188}
189
190class MyModel : public QSqlQueryModel
191{
192public:
193 QVariant data(const QModelIndex &item, int role) const override;
194 void fetchModel();
195
196 int m_specialColumnNo;
197};
198
199//! [23]
200QVariant MyModel::data(const QModelIndex &item, int role) const
201{
202 if (item.column() == m_specialColumnNo) {
203 // handle column separately
204 }
205 return QSqlQueryModel::data(item, role);
206}
207//! [23]
208
209void QSqlTableModel_snippets()
210{
211 {
212//! [25]
213 QSqlTableModel model;
214 model.setTable("employee");
215 model.select();
216 int salary = model.record(row: 4).value(name: "salary").toInt();
217//! [25]
218 Q_UNUSED(salary);
219 }
220}
221
222void sql_intro_snippets()
223{
224 {
225//! [26]
226 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QMYSQL");
227 db.setHostName("bigblue");
228 db.setDatabaseName("flightdb");
229 db.setUserName("acarlson");
230 db.setPassword("1uTbSbAs");
231 bool ok = db.open();
232//! [26]
233 Q_UNUSED(ok);
234 }
235
236 {
237//! [27]
238 QSqlDatabase firstDB = QSqlDatabase::addDatabase(type: "QMYSQL", connectionName: "first");
239 QSqlDatabase secondDB = QSqlDatabase::addDatabase(type: "QMYSQL", connectionName: "second");
240//! [27]
241 }
242
243 {
244//! [28]
245 QSqlDatabase defaultDB = QSqlDatabase::database();
246//! [28] //! [29]
247 QSqlDatabase firstDB = QSqlDatabase::database(connectionName: "first");
248//! [29] //! [30]
249 QSqlDatabase secondDB = QSqlDatabase::database(connectionName: "second");
250//! [30]
251 }
252
253 {
254 // SELECT1
255//! [31]
256 QSqlQuery query;
257 query.exec(query: "SELECT name, salary FROM employee WHERE salary > 50000");
258//! [31]
259
260//! [32]
261 while (query.next()) {
262 QString name = query.value(i: 0).toString();
263 int salary = query.value(i: 1).toInt();
264 qDebug() << name << salary;
265 }
266//! [32]
267 }
268
269 {
270 // FEATURE
271//! [33]
272 QSqlQuery query;
273 int numRows;
274 query.exec(query: "SELECT name, salary FROM employee WHERE salary > 50000");
275
276 QSqlDatabase defaultDB = QSqlDatabase::database();
277 if (defaultDB.driver()->hasFeature(f: QSqlDriver::QuerySize)) {
278 numRows = query.size();
279 } else {
280 // this can be very slow
281 query.last();
282 numRows = query.at() + 1;
283 }
284//! [33]
285 Q_UNUSED(numRows);
286 }
287
288 {
289 // INSERT1
290//! [34]
291 QSqlQuery query;
292 query.exec(query: "INSERT INTO employee (id, name, salary) "
293 "VALUES (1001, 'Thad Beaumont', 65000)");
294//! [34]
295 }
296
297 {
298 // NAMED BINDING
299//! [35]
300 QSqlQuery query;
301 query.prepare(query: "INSERT INTO employee (id, name, salary) "
302 "VALUES (:id, :name, :salary)");
303 query.bindValue(placeholder: ":id", val: 1001);
304 query.bindValue(placeholder: ":name", val: "Thad Beaumont");
305 query.bindValue(placeholder: ":salary", val: 65000);
306 query.exec();
307//! [35]
308 }
309
310 {
311 // POSITIONAL BINDING
312//! [36]
313 QSqlQuery query;
314 query.prepare(query: "INSERT INTO employee (id, name, salary) "
315 "VALUES (?, ?, ?)");
316 query.addBindValue(val: 1001);
317 query.addBindValue(val: "Thad Beaumont");
318 query.addBindValue(val: 65000);
319 query.exec();
320//! [36]
321 }
322
323 {
324 // UPDATE1
325//! [37]
326 QSqlQuery query;
327 query.exec(query: "UPDATE employee SET salary = 70000 WHERE id = 1003");
328//! [37]
329 }
330
331 {
332 // DELETE1
333//! [38]
334 QSqlQuery query;
335 query.exec(query: "DELETE FROM employee WHERE id = 1007");
336//! [38]
337 }
338
339 {
340 // TRANSACTION
341//! [39]
342 QSqlDatabase::database().transaction();
343 QSqlQuery query;
344 query.exec(query: "SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
345 if (query.next()) {
346 int employeeId = query.value(i: 0).toInt();
347 query.exec(query: "INSERT INTO project (id, name, ownerid) "
348 "VALUES (201, 'Manhattan Project', "
349 + QString::number(employeeId) + ')');
350 }
351 QSqlDatabase::database().commit();
352//! [39]
353 }
354
355 {
356 // SQLQUERYMODEL1
357//! [40]
358 QSqlQueryModel model;
359 model.setQuery(query: "SELECT * FROM employee");
360
361 for (int i = 0; i < model.rowCount(); ++i) {
362 int id = model.record(row: i).value(name: "id").toInt();
363 QString name = model.record(row: i).value(name: "name").toString();
364 qDebug() << id << name;
365 }
366//! [40]
367 }
368
369 {
370 // SQLTABLEMODEL1
371//! [41]
372 QSqlTableModel model;
373 model.setTable("employee");
374 model.setFilter("salary > 50000");
375 model.setSort(column: 2, order: Qt::DescendingOrder);
376 model.select();
377
378 for (int i = 0; i < model.rowCount(); ++i) {
379 QString name = model.record(row: i).value(name: "name").toString();
380 int salary = model.record(row: i).value(name: "salary").toInt();
381 qDebug() << name << salary;
382 }
383//! [41]
384 }
385
386 {
387 // SQLTABLEMODEL2
388 QSqlTableModel model;
389 model.setTable("employee");
390
391//! [42]
392 for (int i = 0; i < model.rowCount(); ++i) {
393 QSqlRecord record = model.record(row: i);
394 double salary = record.value(name: "salary").toInt();
395 salary *= 1.1;
396 record.setValue(name: "salary", val: salary);
397 model.setRecord(row: i, record);
398 }
399 model.submitAll();
400//! [42]
401
402 // SQLTABLEMODEL3
403 int row = 1;
404 int column = 2;
405//! [43]
406 model.setData(index: model.index(row, column), value: 75000);
407 model.submitAll();
408//! [43]
409
410 // SQLTABLEMODEL4
411//! [44]
412 model.insertRows(row, count: 1);
413 model.setData(index: model.index(row, column: 0), value: 1013);
414 model.setData(index: model.index(row, column: 1), value: "Peter Gordon");
415 model.setData(index: model.index(row, column: 2), value: 68500);
416 model.submitAll();
417//! [44]
418
419//! [45]
420 model.removeRows(row, count: 5);
421//! [45] //! [46]
422 model.submitAll();
423//! [46]
424 }
425}
426
427//! [47]
428class XyzResult : public QSqlResult
429{
430public:
431 XyzResult(const QSqlDriver *driver)
432 : QSqlResult(driver) {}
433 ~XyzResult() {}
434
435protected:
436 QVariant data(int /* index */) override { return QVariant(); }
437 bool isNull(int /* index */) override { return false; }
438 bool reset(const QString & /* query */) override { return false; }
439 bool fetch(int /* index */) override { return false; }
440 bool fetchFirst() override { return false; }
441 bool fetchLast() override { return false; }
442 int size() override { return 0; }
443 int numRowsAffected() override { return 0; }
444 QSqlRecord record() const override { return QSqlRecord(); }
445};
446//! [47]
447
448//! [48]
449class XyzDriver : public QSqlDriver
450{
451public:
452 XyzDriver() {}
453 ~XyzDriver() {}
454
455 bool hasFeature(DriverFeature /* feature */) const override { return false; }
456 bool open(const QString & /* db */, const QString & /* user */,
457 const QString & /* password */, const QString & /* host */,
458 int /* port */, const QString & /* options */) override
459 { return false; }
460 void close() override {}
461 QSqlResult *createResult() const override { return new XyzResult(this); }
462};
463//! [48]
464
465int main(int argc, char **argv)
466{
467 QCoreApplication app(argc, argv);
468
469 QSqlDatabase_snippets();
470 QSqlField_snippets();
471 QSqlQuery_snippets();
472 QSqlQueryModel_snippets();
473 QSqlTableModel_snippets();
474
475 XyzDriver driver;
476 XyzResult result(&driver);
477}
478

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