Queries and Result Sets

In Example 18.6, we define a function that queries an existing amaroK[3] MySQL database. amaroK is a jukebox program for KDE that can use a variety of different back ends for storing its Mp3 metadata. Instead of storing all tag data for a song in a single row, like we did in Example 18.5, amaroK spreads the tag data across many tables with relatively fewer rows. import () will query the "tags" table and, for each URL, insert a row of preference data into the "statistics" table.

[3] http://amarok.kde.org/

Example 18.6. src/mmjbamarok/tool.cpp

[ . . . . ]
void import() {
 using namespace qstd;
 RatingMapper mapper;
 FileTagger ft;
 QSqlDatabase db = connect();

 QSqlQuery insert;
 int entries=0;
 insert.prepare("INSERT INTO statistics (rating, url)"
 " VALUES (?, ?) "
 " ON DUPLICATE KEY UPDATE rating=? "); <-- 1
 QSqlQuery query;
 query.exec("select url from tags"); <-- 2
 while (query.next()) { <-- 3
 QString filename = query.value(0).toString();
 ft.setFilename(filename);
 QString preference = ft.getPreference();
 int rating = mapper.toRating(preference);
 if (rating == 0) continue;
 insert.addBindValue(rating); <-- 4
 insert.addBindValue(filename);
 insert.addBindValue(rating);
 if (insert.exec()) {
 cerr << rating << " : " << filename << endl;
 entries++;
 }
 else {
 cerr << "Error inserting " << filename << endl;
 }
 }
 cerr << "Entries imported:" << entries << endl;
}

 

(1)Prepare an SQL statement that inserts a new (rating, url) record or updates the current record (new rating) if the record is already there. Each ? is a positional parameter to which we later addBindValue().

(2)Find all urls in the "tags" table. There is one for each song.

(3)Iterate through result set.

(4)first positional parameter

In this example, a JDBC programmer might observe that QSqlQuery serves the purpose of at least two JDBC classes. It is being used in two ways.

1. As a PreparedStatementsomething to store the query, add bind values to, etc.
2. As a cursor into the ResultSetsomething to iterate through the query results

The function iterates through all songs that are in amaroK's library. For each song, it extracts the preference string from a MusicMatch ID3v2 tag and, with the help of RatingMapper, TRanslates the preference string into an integer number of "stars." Finally, the number of stars is inserted into amaroK's statistics table, under the "rating" column.

Database Models