1 /****************************************************************************

     2 **

     3 ** Copyright (C) 2010 Nokia Corporation and/or its subsidiary(-ies).

     4 ** All rights reserved.

     5 ** Contact: Nokia Corporation (qt-info@nokia.com)

     6 **

     7 ** This file is part of the documentation of the Qt Toolkit.

     8 **

     9 ** $QT_BEGIN_LICENSE:LGPL$

    10 ** No Commercial Usage

    11 ** This file contains pre-release code and may not be distributed.

    12 ** You may use this file in accordance with the terms and conditions

    13 ** contained in the Technology Preview License Agreement accompanying

    14 ** this package.

    15 **

    16 ** GNU Lesser General Public License Usage

    17 ** Alternatively, this file may be used under the terms of the GNU Lesser

    18 ** General Public License version 2.1 as published by the Free Software

    19 ** Foundation and appearing in the file LICENSE.LGPL included in the

    20 ** packaging of this file.  Please review the following information to

    21 ** ensure the GNU Lesser General Public License version 2.1 requirements

    22 ** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.

    23 **

    24 ** In addition, as a special exception, Nokia gives you certain additional

    25 ** rights.  These rights are described in the Nokia Qt LGPL Exception

    26 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.

    27 **

    28 ** If you have questions regarding the use of this file, please contact

    29 ** Nokia at qt-info@nokia.com.

    30 **

    31 **

    32 **

    33 **

    34 **

    35 **

    36 **

    37 **

    38 ** $QT_END_LICENSE$

    39 **

    40 ****************************************************************************/

    41 

    42 #include <QtGui>

    43 #include <QtSql>

    44 

    45 #include <iostream>

    46 

    47 using namespace std;

    48 

    49 QString tr(const char *text)

    50 {

    51     return QApplication::translate(text, text);

    52 }

    53 

    54 void QSqlDatabase_snippets()

    55 {

    56     {

    57 //! [0]

    58     QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");

    59     db.setHostName("acidalia");

    60     db.setDatabaseName("customdb");

    61     db.setUserName("mojito");

    62     db.setPassword("J0a1m8");

    63     bool ok = db.open();

    64 //! [0]

    65     Q_UNUSED(ok);

    66     }

    67 

    68     {

    69 //! [1]

    70     QSqlDatabase db = QSqlDatabase::database();

    71 //! [1]

    72     }

    73 }

    74 

    75 void QSqlField_snippets()

    76 {

    77 #if 0

    78     {

    79 //! [2]

    80     QSqlField field("age", QVariant::Int);

    81     field.setValue(QPixmap());  // WRONG

    82 //! [2]

    83     }

    84 #endif

    85 

    86     {

    87 //! [3]

    88     QSqlField field("age", QVariant::Int);

    89     field.setValue(QString("123"));  // casts QString to int

    90 //! [3]

    91     }

    92 

    93     {

    94 //! [4]

    95     QSqlQuery query;

    96 //! [4] //! [5]

    97     QSqlRecord record = query.record();

    98 //! [5] //! [6]

    99     QSqlField field = record.field("country");

   100 //! [6]

   101     }

   102 }

   103 

   104 void doSomething(const QString &)

   105 {

   106 }

   107 

   108 void QSqlQuery_snippets()

   109 {

   110     {

   111     // typical loop

   112 //! [7]

   113     QSqlQuery query("SELECT country FROM artist");

   114     while (query.next()) {

   115         QString country = query.value(0).toString();

   116         doSomething(country);

   117     }

   118 //! [7]

   119     }

   120 

   121     {

   122     // field index lookup

   123 //! [8]

   124     QSqlQuery query("SELECT * FROM artist");

   125     int fieldNo = query.record().indexOf("country");

   126     while (query.next()) {

   127         QString country = query.value(fieldNo).toString();

   128         doSomething(country);

   129     }

   130 //! [8]

   131     }

   132 

   133     {

   134     // named with named

   135 //! [9]

   136     QSqlQuery query;

   137     query.prepare("INSERT INTO person (id, forename, surname) "

   138                   "VALUES (:id, :forename, :surname)");

   139     query.bindValue(":id", 1001);

   140     query.bindValue(":forename", "Bart");

   141     query.bindValue(":surname", "Simpson");

   142     query.exec();

   143 //! [9]

   144     }

   145 

   146     {

   147     // positional with named

   148 //! [10]

   149     QSqlQuery query;

   150     query.prepare("INSERT INTO person (id, forename, surname) "

   151                   "VALUES (:id, :forename, :surname)");

   152     query.bindValue(0, 1001);

   153     query.bindValue(1, "Bart");

   154     query.bindValue(2, "Simpson");

   155     query.exec();

   156 //! [10]

   157     }

   158 

   159     {

   160     // positional 1

   161 //! [11]

   162     QSqlQuery query;

   163     query.prepare("INSERT INTO person (id, forename, surname) "

   164                   "VALUES (?, ?, ?)");

   165     query.bindValue(0, 1001);

   166     query.bindValue(1, "Bart");

   167     query.bindValue(2, "Simpson");

   168     query.exec();

   169 //! [11]

   170     }

   171 

   172     {

   173     // positional 2

   174 //! [12]

   175     QSqlQuery query;

   176     query.prepare("INSERT INTO person (id, forename, surname) "

   177                   "VALUES (?, ?, ?)");

   178     query.addBindValue(1001);

   179     query.addBindValue("Bart");

   180     query.addBindValue("Simpson");

   181     query.exec();

   182 //! [12]

   183     }

   184 

   185     {

   186     // stored

   187 //! [13]

   188     QSqlQuery query;

   189     query.prepare("CALL AsciiToInt(?, ?)");

   190     query.bindValue(0, "A");

   191     query.bindValue(1, 0, QSql::Out);

   192     query.exec();

   193     int i = query.boundValue(1).toInt(); // i is 65

   194 //! [13]

   195     Q_UNUSED(i);

   196     }

   197 

   198     QSqlQuery query;

   199 

   200     {

   201     // examine with named binding

   202 //! [14]

   203     QMapIterator<QString, QVariant> i(query.boundValues());

   204     while (i.hasNext()) {

   205         i.next();

   206         cout << i.key().toAscii().data() << ": "

   207              << i.value().toString().toAscii().data() << endl;

   208     }

   209 //! [14]

   210     }

   211 

   212     {

   213     // examine with positional binding

   214 //! [15]

   215     QList<QVariant> list = query.boundValues().values();

   216     for (int i = 0; i < list.size(); ++i)

   217         cout << i << ": " << list.at(i).toString().toAscii().data() << endl;

   218 //! [15]

   219     }

   220 }

   221 

   222 void QSqlQueryModel_snippets()

   223 {

   224     {

   225 //! [16]

   226     QSqlQueryModel *model = new QSqlQueryModel;

   227     model->setQuery("SELECT name, salary FROM employee");

   228     model->setHeaderData(0, Qt::Horizontal, tr("Name"));

   229     model->setHeaderData(1, Qt::Horizontal, tr("Salary"));

   230 

   231 //! [17]

   232     QTableView *view = new QTableView;

   233 //! [17] //! [18]

   234     view->setModel(model);

   235 //! [18] //! [19]

   236     view->show();

   237 //! [16] //! [19] //! [20]

   238     view->setEditTriggers(QAbstractItemView::NoEditTriggers);

   239 //! [20]

   240     }

   241 

   242 //! [21]

   243     QSqlQueryModel model;

   244     model.setQuery("SELECT * FROM employee");

   245     int salary = model.record(4).value("salary").toInt();

   246 //! [21]

   247     Q_UNUSED(salary);

   248 

   249     {

   250 //! [22]

   251     int salary = model.data(model.index(4, 2)).toInt();

   252 //! [22]

   253     Q_UNUSED(salary);

   254     }

   255 

   256     for (int row = 0; row < model.rowCount(); ++row) {

   257         for (int col = 0; col < model.columnCount(); ++col) {

   258             qDebug() << model.data(model.index(row, col));

   259         }

   260     }

   261 }

   262 

   263 class MyModel : public QSqlQueryModel

   264 {

   265 public:

   266     QVariant data(const QModelIndex &item, int role) const;

   267 

   268     int m_specialColumnNo;

   269 };

   270 

   271 //! [23]

   272 QVariant MyModel::data(const QModelIndex &item, int role) const

   273 {

   274     if (item.column() == m_specialColumnNo) {

   275         // handle column separately

   276     }

   277     return QSqlQueryModel::data(item, role);

   278 }

   279 //! [23]

   280 

   281 void QSqlTableModel_snippets()

   282 {

   283 //! [24]

   284     QSqlTableModel *model = new QSqlTableModel;

   285     model->setTable("employee");

   286     model->setEditStrategy(QSqlTableModel::OnManualSubmit);

   287     model->select();

   288     model->removeColumn(0); // don't show the ID

   289     model->setHeaderData(0, Qt::Horizontal, tr("Name"));

   290     model->setHeaderData(1, Qt::Horizontal, tr("Salary"));

   291 

   292     QTableView *view = new QTableView;

   293     view->setModel(model);

   294     view->show();

   295 //! [24]

   296 

   297     {

   298 //! [25]

   299     QSqlTableModel model;

   300     model.setTable("employee");

   301     QString name = model.record(4).value("name").toString();

   302 //! [25]

   303     }

   304 }

   305 

   306 void sql_intro_snippets()

   307 {

   308     {

   309 //! [26]

   310     QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

   311     db.setHostName("bigblue");

   312     db.setDatabaseName("flightdb");

   313     db.setUserName("acarlson");

   314     db.setPassword("1uTbSbAs");

   315     bool ok = db.open();

   316 //! [26]

   317     Q_UNUSED(ok);

   318     }

   319 

   320     {

   321 //! [27]

   322     QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first");

   323     QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second");

   324 //! [27]

   325     }

   326 

   327     {

   328 //! [28]

   329     QSqlDatabase defaultDB = QSqlDatabase::database();

   330 //! [28] //! [29]

   331     QSqlDatabase firstDB = QSqlDatabase::database("first");

   332 //! [29] //! [30]

   333     QSqlDatabase secondDB = QSqlDatabase::database("second");

   334 //! [30]

   335     }

   336 

   337     {

   338     // SELECT1

   339 //! [31]

   340     QSqlQuery query;

   341     query.exec("SELECT name, salary FROM employee WHERE salary > 50000");

   342 //! [31]

   343 

   344 //! [32]

   345     while (query.next()) {

   346         QString name = query.value(0).toString();

   347         int salary = query.value(1).toInt();

   348         qDebug() << name << salary;

   349     }

   350 //! [32]

   351     }

   352 

   353     {

   354     // FEATURE

   355 //! [33]

   356     QSqlQuery query;

   357     int numRows;

   358     query.exec("SELECT name, salary FROM employee WHERE salary > 50000");

   359 

   360     QSqlDatabase defaultDB = QSqlDatabase::database();

   361     if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) {

   362         numRows = query.size();

   363     } else {

   364         // this can be very slow

   365         query.last();

   366         numRows = query.at() + 1;

   367     }

   368 //! [33]

   369     }

   370 

   371     {

   372     // INSERT1

   373 //! [34]

   374     QSqlQuery query;

   375     query.exec("INSERT INTO employee (id, name, salary) "

   376                "VALUES (1001, 'Thad Beaumont', 65000)");

   377 //! [34]

   378     }

   379 

   380     {

   381     // NAMED BINDING

   382 //! [35]

   383     QSqlQuery query;

   384     query.prepare("INSERT INTO employee (id, name, salary) "

   385                   "VALUES (:id, :name, :salary)");

   386     query.bindValue(":id", 1001);

   387     query.bindValue(":name", "Thad Beaumont");

   388     query.bindValue(":salary", 65000);

   389     query.exec();

   390 //! [35]

   391     }

   392 

   393     {

   394     // POSITIONAL BINDING

   395 //! [36]

   396     QSqlQuery query;

   397     query.prepare("INSERT INTO employee (id, name, salary) "

   398                   "VALUES (?, ?, ?)");

   399     query.addBindValue(1001);

   400     query.addBindValue("Thad Beaumont");

   401     query.addBindValue(65000);

   402     query.exec();

   403 //! [36]

   404     }

   405 

   406     {

   407     // UPDATE1

   408 //! [37]

   409     QSqlQuery query;

   410     query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");

   411 //! [37]

   412     }

   413 

   414     {

   415     // DELETE1

   416 //! [38]

   417     QSqlQuery query;

   418     query.exec("DELETE FROM employee WHERE id = 1007");

   419 //! [38]

   420     }

   421 

   422     {

   423     // TRANSACTION

   424 //! [39]

   425     QSqlDatabase::database().transaction();

   426     QSqlQuery query;

   427     query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");

   428     if (query.next()) {

   429         int employeeId = query.value(0).toInt();

   430         query.exec("INSERT INTO project (id, name, ownerid) "

   431                    "VALUES (201, 'Manhattan Project', "

   432                    + QString::number(employeeId) + ')');

   433     }

   434     QSqlDatabase::database().commit();

   435 //! [39]

   436     }

   437 

   438     {

   439     // SQLQUERYMODEL1

   440 //! [40]

   441     QSqlQueryModel model;

   442     model.setQuery("SELECT * FROM employee");

   443 

   444     for (int i = 0; i < model.rowCount(); ++i) {

   445         int id = model.record(i).value("id").toInt();

   446         QString name = model.record(i).value("name").toString();

   447         qDebug() << id << name;

   448     }

   449 //! [40]

   450     }

   451 

   452     {

   453     // SQLTABLEMODEL1

   454 //! [41]

   455     QSqlTableModel model;

   456     model.setTable("employee");

   457     model.setFilter("salary > 50000");

   458     model.setSort(2, Qt::DescendingOrder);

   459     model.select();

   460 

   461     for (int i = 0; i < model.rowCount(); ++i) {

   462         QString name = model.record(i).value("name").toString();

   463         int salary = model.record(i).value("salary").toInt();

   464         qDebug() << name << salary;

   465     }

   466 //! [41]

   467     }

   468 

   469     {

   470     // SQLTABLEMODEL2

   471     QSqlTableModel model;

   472     model.setTable("employee");

   473 

   474 //! [42]

   475     for (int i = 0; i < model.rowCount(); ++i) {

   476         QSqlRecord record = model.record(i);

   477         double salary = record.value("salary").toInt();

   478         salary *= 1.1;

   479         record.setValue("salary", salary);

   480         model.setRecord(i, record);

   481     }

   482     model.submitAll();

   483 //! [42]

   484 

   485     // SQLTABLEMODEL3

   486     int row = 1;

   487     int column = 2;

   488 //! [43]

   489     model.setData(model.index(row, column), 75000);

   490     model.submitAll();

   491 //! [43]

   492 

   493     // SQLTABLEMODEL4

   494 //! [44]

   495     model.insertRows(row, 1);

   496     model.setData(model.index(row, 0), 1013);

   497     model.setData(model.index(row, 1), "Peter Gordon");

   498     model.setData(model.index(row, 2), 68500);

   499     model.submitAll();

   500 //! [44]

   501 

   502 //! [45]

   503     model.removeRows(row, 5);

   504 //! [45] //! [46]

   505     model.submitAll();

   506 //! [46]

   507     }

   508 }

   509 

   510 //! [47]

   511 class XyzResult : public QSqlResult

   512 {

   513 public:

   514     XyzResult(const QSqlDriver *driver)

   515         : QSqlResult(driver) {}

   516     ~XyzResult() {}

   517 

   518 protected:

   519     QVariant data(int /* index */) { return QVariant(); }

   520     bool isNull(int /* index */) { return false; }

   521     bool reset(const QString & /* query */) { return false; }

   522     bool fetch(int /* index */) { return false; }

   523     bool fetchFirst() { return false; }

   524     bool fetchLast() { return false; }

   525     int size() { return 0; }

   526     int numRowsAffected() { return 0; }

   527     QSqlRecord record() const { return QSqlRecord(); }

   528 };

   529 //! [47]

   530 

   531 //! [48]

   532 class XyzDriver : public QSqlDriver

   533 {

   534 public:

   535     XyzDriver() {}

   536     ~XyzDriver() {}

   537 

   538     bool hasFeature(DriverFeature /* feature */) const { return false; }

   539     bool open(const QString & /* db */, const QString & /* user */,

   540               const QString & /* password */, const QString & /* host */,

   541               int /* port */, const QString & /* options */)

   542         { return false; }

   543     void close() {}

   544     QSqlResult *createResult() const { return new XyzResult(this); }

   545 };

   546 //! [48]

   547 

   548 int main(int argc, char **argv)

   549 {

   550     QApplication app(argc, argv);

   551 

   552     QSqlDatabase_snippets();

   553     QSqlField_snippets();

   554     QSqlQuery_snippets();

   555     QSqlQueryModel_snippets();

   556     QSqlTableModel_snippets();

   557 

   558     XyzDriver driver;

   559     XyzResult result(&driver);

   560 }