<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- sql-programming.qdoc -->
<title>Qt 4.8: Executing SQL Statements</title>
<link rel="stylesheet" type="text/css" href="style/style.css" />
<script src="scripts/jquery.js" type="text/javascript"></script>
<script src="scripts/functions.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="style/superfish.css" />
<link rel="stylesheet" type="text/css" href="style/narrow.css" />
<!--[if IE]>
<meta name="MSSmartTagsPreventParsing" content="true">
<meta http-equiv="imagetoolbar" content="no">
<![endif]-->
<!--[if lt IE 7]>
<link rel="stylesheet" type="text/css" href="style/style_ie6.css">
<![endif]-->
<!--[if IE 7]>
<link rel="stylesheet" type="text/css" href="style/style_ie7.css">
<![endif]-->
<!--[if IE 8]>
<link rel="stylesheet" type="text/css" href="style/style_ie8.css">
<![endif]-->
<script src="scripts/superfish.js" type="text/javascript"></script>
<script src="scripts/narrow.js" type="text/javascript"></script>
</head>
<body class="" onload="CheckEmptyAndLoadList();">
<div class="header" id="qtdocheader">
<div class="content">
<div id="nav-logo">
<a href="index.html">Home</a></div>
<a href="index.html" class="qtref"><span>Qt Reference Documentation</span></a>
<div id="narrowsearch"></div>
<div id="nav-topright">
<ul>
<li class="nav-topright-home"><a href="http://qt.digia.com/">Qt HOME</a></li>
<li class="nav-topright-dev"><a href="http://qt-project.org/">DEV</a></li>
<li class="nav-topright-doc nav-topright-doc-active"><a href="http://qt-project.org/doc/">
DOC</a></li>
<li class="nav-topright-blog"><a href="http://blog.qt.digia.com/">BLOG</a></li>
</ul>
</div>
<div id="shortCut">
<ul>
<li class="shortCut-topleft-inactive"><span><a href="index.html">Qt 4.8</a></span></li>
<li class="shortCut-topleft-active"><a href="http://qt-project.org/doc/">ALL VERSIONS </a></li>
</ul>
</div>
<ul class="sf-menu" id="narrowmenu">
<li><a href="#">API Lookup</a>
<ul>
<li><a href="classes.html">Class index</a></li>
<li><a href="functions.html">Function index</a></li>
<li><a href="modules.html">Modules</a></li>
<li><a href="namespaces.html">Namespaces</a></li>
<li><a href="qtglobal.html">Global Declarations</a></li>
<li><a href="qdeclarativeelements.html">QML elements</a></li>
</ul>
</li>
<li><a href="#">Qt Topics</a>
<ul>
<li><a href="qt-basic-concepts.html">Programming with Qt</a></li>
<li><a href="qtquick.html">Device UIs & Qt Quick</a></li>
<li><a href="qt-gui-concepts.html">UI Design with Qt</a></li>
<li><a href="supported-platforms.html">Supported Platforms</a></li>
<li><a href="technology-apis.html">Qt and Key Technologies</a></li>
<li><a href="best-practices.html">How-To's and Best Practices</a></li>
</ul>
</li>
<li><a href="#">Examples</a>
<ul>
<li><a href="all-examples.html">Examples</a></li>
<li><a href="tutorials.html">Tutorials</a></li>
<li><a href="demos.html">Demos</a></li>
<li><a href="qdeclarativeexamples.html">QML Examples</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="wrapper">
<div class="hd">
<span></span>
</div>
<div class="bd group">
<div class="sidebar">
<div class="searchlabel">
Search index:</div>
<div class="search" id="sidebarsearch">
<form id="qtdocsearch" action="" onsubmit="return false;">
<fieldset>
<input type="text" name="searchstring" id="pageType" value="" />
<div id="resultdialog">
<a href="#" id="resultclose">Close</a>
<p id="resultlinks" class="all"><a href="#" id="showallresults">All</a> | <a href="#" id="showapiresults">API</a> | <a href="#" id="showarticleresults">Articles</a> | <a href="#" id="showexampleresults">Examples</a></p>
<p id="searchcount" class="all"><span id="resultcount"></span><span id="apicount"></span><span id="articlecount"></span><span id="examplecount"></span> results:</p>
<ul id="resultlist" class="all">
</ul>
</div>
</fieldset>
</form>
</div>
<div class="box first bottombar" id="lookup">
<h2 title="API Lookup"><span></span>
API Lookup</h2>
<div id="list001" class="list">
<ul id="ul001" >
<li class="defaultLink"><a href="classes.html">Class index</a></li>
<li class="defaultLink"><a href="functions.html">Function index</a></li>
<li class="defaultLink"><a href="modules.html">Modules</a></li>
<li class="defaultLink"><a href="namespaces.html">Namespaces</a></li>
<li class="defaultLink"><a href="qtglobal.html">Global Declarations</a></li>
<li class="defaultLink"><a href="qdeclarativeelements.html">QML elements</a></li>
</ul>
</div>
</div>
<div class="box bottombar" id="topics">
<h2 title="Qt Topics"><span></span>
Qt Topics</h2>
<div id="list002" class="list">
<ul id="ul002" >
<li class="defaultLink"><a href="qt-basic-concepts.html">Programming with Qt</a></li>
<li class="defaultLink"><a href="qtquick.html">Device UIs & Qt Quick</a></li>
<li class="defaultLink"><a href="qt-gui-concepts.html">UI Design with Qt</a></li>
<li class="defaultLink"><a href="supported-platforms.html">Supported Platforms</a></li>
<li class="defaultLink"><a href="technology-apis.html">Qt and Key Technologies</a></li>
<li class="defaultLink"><a href="best-practices.html">How-To's and Best Practices</a></li>
</ul>
</div>
</div>
<div class="box" id="examples">
<h2 title="Examples"><span></span>
Examples</h2>
<div id="list003" class="list">
<ul id="ul003">
<li class="defaultLink"><a href="all-examples.html">Examples</a></li>
<li class="defaultLink"><a href="tutorials.html">Tutorials</a></li>
<li class="defaultLink"><a href="demos.html">Demos</a></li>
<li class="defaultLink"><a href="qdeclarativeexamples.html">QML Examples</a></li>
</ul>
</div>
</div>
</div>
<div class="wrap">
<div class="toolbar">
<div class="breadcrumb toolblock">
<ul>
<li class="first"><a href="index.html">Home</a></li>
<!-- Breadcrumbs go here -->
<li>Executing SQL Statements</li>
</ul>
</div>
<div class="toolbuttons toolblock">
<ul>
<li id="smallA" class="t_button">A</li>
<li id="medA" class="t_button active">A</li>
<li id="bigA" class="t_button">A</li>
<li id="print" class="t_button"><a href="javascript:this.print();">
<span>Print</span></a></li>
</ul>
</div>
</div>
<div class="content mainContent">
<link rel="prev" href="sql-connecting.html" />
<link rel="next" href="sql-model.html" />
<p class="naviNextPrevious headerNavi">
<a class="prevPage" href="sql-connecting.html">Connecting to Databases</a>
<a class="nextPage" href="sql-model.html">Using the SQL Model Classes</a>
</p><p/>
<div class="toc">
<h3><a name="toc">Contents</a></h3>
<ul>
<li class="level2"><a href="#executing-a-query">Executing a Query</a></li>
<li class="level2"><a href="#navigating-the-result-set">Navigating the Result Set</a></li>
<li class="level2"><a href="#inserting-updating-and-deleting-records">Inserting, Updating, and Deleting Records</a></li>
<li class="level2"><a href="#transactions">Transactions</a></li>
</ul>
</div>
<h1 class="title">Executing SQL Statements</h1>
<span class="subtitle"></span>
<!-- $$$sql-sqlstatements.html-description -->
<div class="descr"> <a name="details"></a>
<p>The <a href="qsqlquery.html">QSqlQuery</a> class provides an interface for executing SQL statements and navigating through the result set of a query.</p>
<p>The <a href="qsqlquerymodel.html">QSqlQueryModel</a> and <a href="qsqltablemodel.html">QSqlTableModel</a> classes described in the next section provide a higher-level interface for accessing databases. If you are unfamiliar with SQL, you might want to skip directly to the next section (<a href="sql-model.html">Using the SQL Model Classes</a>).</p>
<a name="executing-a-query"></a>
<h3>Executing a Query</h3>
<p>To execute an SQL statement, simply create a <a href="qsqlquery.html">QSqlQuery</a> object and call <a href="qsqlquery.html#exec">QSqlQuery::exec</a>() like this:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>exec(<span class="string">"SELECT name, salary FROM employee WHERE salary > 50000"</span>);</pre>
<p>The <a href="qsqlquery.html">QSqlQuery</a> constructor accepts an optional <a href="qsqldatabase.html">QSqlDatabase</a> object that specifies which database connection to use. In the example above, we don't specify any connection, so the default connection is used.</p>
<p>If an error occurs, <a href="qsqlquery.html#exec">exec()</a> returns false. The error is then available as <a href="qsqlquery.html#lastError">QSqlQuery::lastError</a>().</p>
<a name="navigating-the-result-set"></a>
<h3>Navigating the Result Set</h3>
<p><a href="qsqlquery.html">QSqlQuery</a> provides access to the result set one record at a time. After the call to <a href="qsqlquery.html#exec">exec()</a>, <a href="qsqlquery.html">QSqlQuery</a>'s internal pointer is located one position <i>before</i> the first record. We must call <a href="qsqlquery.html#next">QSqlQuery::next</a>() once to advance to the first record, then <a href="qsqlquery.html#next">next()</a> again repeatedly to access the other records, until it returns false. Here's a typical loop that iterates over all the records in order:</p>
<pre class="cpp"> <span class="keyword">while</span> (query<span class="operator">.</span>next()) {
<span class="type"><a href="qstring.html">QString</a></span> name <span class="operator">=</span> query<span class="operator">.</span>value(<span class="number">0</span>)<span class="operator">.</span>toString();
<span class="type">int</span> salary <span class="operator">=</span> query<span class="operator">.</span>value(<span class="number">1</span>)<span class="operator">.</span>toInt();
<a href="qtglobal.html#qDebug">qDebug</a>() <span class="operator"><</span><span class="operator"><</span> name <span class="operator"><</span><span class="operator"><</span> salary;
}</pre>
<p>The <a href="qsqlquery.html#value">QSqlQuery::value</a>() function returns the value of a field in the current record. Fields are specified as zero-based indexes. <a href="qsqlquery.html#value">QSqlQuery::value</a>() returns a <a href="qvariant.html">QVariant</a>, a type that can hold various C++ and core Qt data types such as <tt>int</tt>, <a href="qstring.html">QString</a>, and <a href="qbytearray.html">QByteArray</a>. The different database types are automatically mapped into the closest Qt equivalent. In the code snippet, we call <a href="qvariant.html#toString">QVariant::toString</a>() and <a href="qvariant.html#toInt">QVariant::toInt</a>() to convert variants to <a href="qstring.html">QString</a> and <tt>int</tt>.</p>
<p>For an overview of the recommended types for use with Qt-supported Databases, please refer to <a href="sql-types.html">this table</a>.</p>
<p>You can iterate back and forth using <a href="qsqlquery.html#next">QSqlQuery::next</a>(), <a href="qsqlquery.html#previous">QSqlQuery::previous</a>(), <a href="qsqlquery.html#first">QSqlQuery::first</a>(), <a href="qsqlquery.html#last">QSqlQuery::last</a>(), and <a href="qsqlquery.html#seek">QSqlQuery::seek</a>(). The current row index is returned by <a href="qsqlquery.html#at">QSqlQuery::at</a>(), and the total number of rows in the result set is avaliable as <a href="qsqlquery.html#size">QSqlQuery::size</a>() for databases that support it.</p>
<p>To determine whether a database driver supports a given feature, use <a href="qsqldriver.html#hasFeature">QSqlDriver::hasFeature</a>(). In the following example, we call <a href="qsqlquery.html#size">QSqlQuery::size</a>() to determine the size of a result set of the underlying database supports that feature; otherwise, we navigate to the last record and use the query's position to tell us how many records there are.</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
<span class="type">int</span> numRows;
query<span class="operator">.</span>exec(<span class="string">"SELECT name, salary FROM employee WHERE salary > 50000"</span>);
<span class="type"><a href="qsqldatabase.html">QSqlDatabase</a></span> defaultDB <span class="operator">=</span> <span class="type"><a href="qsqldatabase.html">QSqlDatabase</a></span><span class="operator">::</span>database();
<span class="keyword">if</span> (defaultDB<span class="operator">.</span>driver()<span class="operator">-</span><span class="operator">></span>hasFeature(<span class="type"><a href="qsqldriver.html">QSqlDriver</a></span><span class="operator">::</span>QuerySize)) {
numRows <span class="operator">=</span> query<span class="operator">.</span>size();
} <span class="keyword">else</span> {
<span class="comment">// this can be very slow</span>
query<span class="operator">.</span>last();
numRows <span class="operator">=</span> query<span class="operator">.</span>at() <span class="operator">+</span> <span class="number">1</span>;
}</pre>
<p>If you iterate through a result set only using next() and seek() with positive values, you can call QSqlQuery::setForwardOnly(true) before calling exec(). This is an easy optimization that will speed up the query significantly when operating on large result sets.</p>
<a name="inserting-updating-and-deleting-records"></a>
<h3>Inserting, Updating, and Deleting Records</h3>
<p><a href="qsqlquery.html">QSqlQuery</a> can execute arbitrary SQL statements, not just <tt>SELECT</tt>s. The following example inserts a record into a table using <tt>INSERT</tt>:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>exec(<span class="string">"INSERT INTO employee (id, name, salary) "</span>
<span class="string">"VALUES (1001, 'Thad Beaumont', 65000)"</span>);</pre>
<p>If you want to insert many records at the same time, it is often more efficient to separate the query from the actual values being inserted. This can be done using placeholders. Qt supports two placeholder syntaxes: named binding and positional binding. Here's an example of named binding:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>prepare(<span class="string">"INSERT INTO employee (id, name, salary) "</span>
<span class="string">"VALUES (:id, :name, :salary)"</span>);
query<span class="operator">.</span>bindValue(<span class="string">":id"</span><span class="operator">,</span> <span class="number">1001</span>);
query<span class="operator">.</span>bindValue(<span class="string">":name"</span><span class="operator">,</span> <span class="string">"Thad Beaumont"</span>);
query<span class="operator">.</span>bindValue(<span class="string">":salary"</span><span class="operator">,</span> <span class="number">65000</span>);
query<span class="operator">.</span>exec();</pre>
<p>Here's an example of positional binding:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>prepare(<span class="string">"INSERT INTO employee (id, name, salary) "</span>
<span class="string">"VALUES (?, ?, ?)"</span>);
query<span class="operator">.</span>addBindValue(<span class="number">1001</span>);
query<span class="operator">.</span>addBindValue(<span class="string">"Thad Beaumont"</span>);
query<span class="operator">.</span>addBindValue(<span class="number">65000</span>);
query<span class="operator">.</span>exec();</pre>
<p>Both syntaxes work with all database drivers provided by Qt. If the database supports the syntax natively, Qt simply forwards the query to the DBMS; otherwise, Qt simulates the placeholder syntax by preprocessing the query. The actual query that ends up being executed by the DBMS is available as <a href="qsqlquery.html#executedQuery">QSqlQuery::executedQuery</a>().</p>
<p>When inserting multiple records, you only need to call <a href="qsqlquery.html#prepare">QSqlQuery::prepare</a>() once. Then you call <a href="qsqlquery.html#bindValue">bindValue()</a> or <a href="qsqlquery.html#addBindValue">addBindValue()</a> followed by <a href="qsqlquery.html#exec">exec()</a> as many times as necessary.</p>
<p>Besides performance, one advantage of placeholders is that you can easily specify arbitrary values without having to worry about escaping special characters.</p>
<p>Updating a record is similar to inserting it into a table:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>exec(<span class="string">"UPDATE employee SET salary = 70000 WHERE id = 1003"</span>);</pre>
<p>You can also use named or positional binding to associate parameters to actual values.</p>
<p>Finally, here's an example of a <tt>DELETE</tt> statement:</p>
<pre class="cpp"> <span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>exec(<span class="string">"DELETE FROM employee WHERE id = 1007"</span>);</pre>
<a name="transactions"></a>
<h3>Transactions</h3>
<p>If the underlying database engine supports transactions, QSqlDriver::hasFeature(<a href="qsqldriver.html#DriverFeature-enum">QSqlDriver::Transactions</a>) will return true. You can use <a href="qsqldatabase.html#transaction">QSqlDatabase::transaction</a>() to initiate a transaction, followed by the SQL commands you want to execute within the context of the transaction, and then either <a href="qsqldatabase.html#commit">QSqlDatabase::commit</a>() or <a href="qsqldatabase.html#rollback">QSqlDatabase::rollback</a>(). When using transactions you must start the transaction before you create your query.</p>
<p>Example:</p>
<pre class="cpp"> <span class="type"><a href="qsqldatabase.html">QSqlDatabase</a></span><span class="operator">::</span>database()<span class="operator">.</span>transaction();
<span class="type"><a href="qsqlquery.html">QSqlQuery</a></span> query;
query<span class="operator">.</span>exec(<span class="string">"SELECT id FROM employee WHERE name = 'Torild Halvorsen'"</span>);
<span class="keyword">if</span> (query<span class="operator">.</span>next()) {
<span class="type">int</span> employeeId <span class="operator">=</span> query<span class="operator">.</span>value(<span class="number">0</span>)<span class="operator">.</span>toInt();
query<span class="operator">.</span>exec(<span class="string">"INSERT INTO project (id, name, ownerid) "</span>
<span class="string">"VALUES (201, 'Manhattan Project', "</span>
<span class="operator">+</span> <span class="type"><a href="qstring.html">QString</a></span><span class="operator">::</span>number(employeeId) <span class="operator">+</span> <span class="char">')'</span>);
}
<span class="type"><a href="qsqldatabase.html">QSqlDatabase</a></span><span class="operator">::</span>database()<span class="operator">.</span>commit();</pre>
<p>Transactions can be used to ensure that a complex operation is atomic (for example, looking up a foreign key and creating a record), or to provide a means of canceling a complex change in the middle.</p>
</div>
<!-- @@@sql-sqlstatements.html -->
<p class="naviNextPrevious footerNavi">
<a class="prevPage" href="sql-connecting.html">Connecting to Databases</a>
<a class="nextPage" href="sql-model.html">Using the SQL Model Classes</a>
</p>
</div>
</div>
</div>
<div class="ft">
<span></span>
</div>
</div>
<div class="footer">
<p>
<acronym title="Copyright">©</acronym> 2015 The Qt Company Ltd.
Documentation contributions included herein are the copyrights of
their respective owners.</p>
<br />
<p>
The documentation provided herein is licensed under the terms of the
<a href="http://www.gnu.org/licenses/fdl.html">GNU Free Documentation
License version 1.3</a> as published by the Free Software Foundation.</p>
<p>
Documentation sources may be obtained from <a href="http://www.qt-project.org">
www.qt-project.org</a>.</p>
<br />
<p>
Qt and respective logos are trademarks of The Qt Company Ltd
in Finland and/or other countries worldwide. All other trademarks are property
of their respective owners. <a title="Privacy Policy"
href="http://en.gitorious.org/privacy_policy/">Privacy Policy</a></p>
</div>
<script src="scripts/functions.js" type="text/javascript"></script>
</body>
</html>
