| 18.1. QSqlDatabase: Connecting to SQL from Qt | ||
|---|---|---|
 | Chapter 18. Database Programming |  |
| 18.1. QSqlDatabase: Connecting to SQL from Qt | ||
|---|---|---|
| | Chapter 18. Database Programming | |
[ fromfile: database.xml id: qsqldatabase ]
QSqlDatabase is a somewhat misleading name for this class. It does not represent a database on disk, but rather a connection to a database. A better name for this class would be QSqlConnection.
Connecting initially to a database server requires these pieces of information: driver type, host name, user name, password, and database name, as shown in Example 18.1.
In the case of SQLite, you need only a filename, which is passed to QSqlDatabase::setDatabaseName().
A connection (i.e., an instance of QSqlDatabase) is created initially with the static QSqlDatabase::addDatabase().
The instance can be given an optional connection name and can later be retrieved again using that name.
The default connection can be reused with the QSqlDatabase::database() method.
Example 18.1. src/sql/testprepare/testprepare.cpp
[ . . . . ]
void testprepare::testPrepare() {
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setUserName("amarok");
db.setPassword("amarok");
db.setDatabaseName("amarok");
QVERIFY(db.open());
Each database has a collection of tables.
A query statement that changes the definition of a table is called a DDL statement.
A table is very much like an array of struct,
where each data member corresponds to a column,
and each object roughly corresponds to a record, or a row in the table.
To define a table, we must describe what a record looks like.
That means describing each of the columns, which can also be thought of as fields, properties, or data members.
Example 18.2 defines a table in SQL called MetaData.
Example 18.2. src/libs/sqlmetadata/metadatatable.cpp
[ . . . . ] bool MetaDataTable::createMetadataTable() { QSqlDatabase db = DbConnectionSettings::lastSaved(); if (m_driver == "QMYSQL") m_createTableQStr = QString("CREATE TABLE if not exists %1 (" "TrackTitle text, Artist text, " "AlbumTitle text, TrackTime integer, TrackNumber integer, " "Genre varchar(30), Preference integer, Comment text, " "FileName varchar(255) PRIMARY KEY, INDEX(Genre) ) " "DEFAULT CHARSET utf8").arg(m_tableName); else m_createTableQStr = QString("CREATE TABLE IF NOT EXISTS %1 (""TrackTitle text, Artist text, AlbumTitle text, " "TrackTime integer, TrackNumber integer, Genre varchar(30), " "Preference integer, Comment text, FileName varchar(255) " "PRIMARY KEY)").arg(m_tableName); QSqlQuery q(m_createTableQStr); if (!q.isActive()) { qDebug() << "Create Table Fail: " << q.lastError().text() << q.lastQuery(); return false; } db.commit(); return true; }
| Tested with SQLite3. |
We have different SQL create strings depending on which QSqlDriver is in use.
Each additional database we want to support must be tested separately, because SQL syntax can vary from one server to another.
For example, we started out using time as the type for the TrackTime column in MySQL, but we changed it to integer[74] so that we can use the same schema with both databases.
After the database connection is opened, we use a powerful class called QSqlQuery, which has a member function exec().
Using the QSqlQuery, there are two ways to execute SQL statements:
exec(QString) is slower because it requires the server to parse each SQL statement.
Prepared statements are safer because you do not need to escape strings.
They are also faster, especially when the same SQL statement is executed repeatedly with different parameter values.
The SQL driver needs to parse the query string only once.
Example 18.3 shows an example using prepared statements to insert or update rows.
We use named parameters here, but positional parameters are also possible with the use of addBindValue and parameters of the form of :1, :2, etc.
The SQL syntax for a single operation that can insert or update a row in MySQL is slightly different from that of SQLite, so again, we have two different insert strings.
Example 18.3. src/libs/sqlmetadata/metadatatable.cpp
[ . . . . ] MetaDataTable::MetaDataTable(QObject* parent) : QObject(parent), m_tableName("MetaData") { setObjectName(m_tableName); m_mdl = Abstract::MetaDataLoader::instance(); m_driver = DbConnectionSettings::lastSaved().driverName(); Q_ASSERT(createMetadataTable()); QString preparedQuery = "INSERT into MetaData"
| Tested with MySQL5. |
Example 18.4 shows the prepared statement in use.
First, we call bindValue() on the query for each column, and then call exec().
Example 18.4. src/libs/sqlmetadata/metadatatable.cpp
[ . . . . ] bool MetaDataTable::insert(const MetaDataValue &ft) { using namespace DbUtils; QSqlDatabase db = DbConnectionSettings::lastSaved(); QSqlRecord record = db.record(m_tableName); if (record.isEmpty() && !createMetadataTable()) { qDebug() << "unable to create metadata: " << db.lastError().text(); return false; } m_insertQuery.bindValue(":artist", ft.artist()); m_insertQuery.bindValue(":title", ft.trackTitle()); m_insertQuery.bindValue(":album", ft.albumTitle()); m_insertQuery.bindValue(":track", ft.trackNumber()); QTime t = ft.trackTime(); int secs = QTime().secsTo(t); m_insertQuery.bindValue(":time", secs); m_insertQuery.bindValue(":genre", ft.genre()); m_insertQuery.bindValue(":filename", ft.fileName()); int pref = ft.preference().intValue(); m_insertQuery.bindValue(":preference", pref); m_insertQuery.bindValue(":comment", ft.comment()); bool retval = m_insertQuery.exec(); if (!retval) { qDebug() << m_insertQuery.lastError().text() << m_insertQuery.lastQuery(); abort(); } emit inserted(ft); return retval; }
| Generated: 2012-03-02 | © 2012 Alan Ezust and Paul Ezust. |
| |  | |
| Chapter 18. Database Programming |  | 18.2. Queries and Result Sets |