SQLITE和QT

sqlite3数据库支持事务

例如：

1 BEGIN DEFERRED TRANSACTION;
2 INSERT INTO main.test_transaction (test_unique) VALUES ('test_6');
3 INSERT INTO main.test_transaction (test_unique) VALUES ('test_6');
4 COMMIT TRANSACTION;

第二个插入sql语句是不符合约束的，所以事务提交失败。

对比mysql，例如：

1 SET autocommit = 0;
2 START TRANSACTION;
3 INSERT INTO `他天天天天`.test_2 (test_column,test_column2) VALUES ('12', 'test2');
4 INSERT INTO `他天天天天`.test_2 (test_column,test_column2) VALUES ('12', 'test3');
5 COMMIT;

同样，第二个插入sql语句是不符合约束的，所以事务提交失败。

但是到了qt里面使用sqlite3数据库时却发现了个问题：提交事务时，sql语句有正确的、错误的，但是提交都会成功，commit函数返回true。

请看测试代码：

 1 bool test__Qsql_transaction5()
 2 {
 3     bool is_success = false;
 4 
 5     do 
 6     {
 7         QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
 8         db.setDatabaseName("dbName_test_transaction");
 9         db.setHostName("AK-PC");
10         db.setUserName("Asterisk");
11         db.setPassword("0");
12         //db.setPort(12345);
13         if (db.open("Asterisk", "0"))
14         {
15             if (db.transaction())
16             {
17                 QSqlQuery query;
18 
19                 QString sql;
20 
21                 sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";
22                 query.exec(sql);
23                 qDebug() << query.lastError();
24 
25                 sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('7','test2');";
26                 bool b1 = query.exec(sql);
27                 qDebug() << query.lastError();
28 
29                 sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('8','test2');";
30                 bool b2 = query.exec(sql);
31                 qDebug() << query.lastError();
32 
33 
34                 if ( !db.commit() )
35                 {
36                     qDebug() << db.lastError();
37                     if ( !db.rollback() )
38                         qDebug() << db.lastError();
39                 }
40             }
41         }
42         db.close();
43 
44     } while ( false );
45 
46     return is_success;
47 }

同样，第二个插入sql语句是不符合约束的，所以事务提交失败。但是“db.commit()”返回true，不知道问题出在哪里。

完成测试代码：

  1 #include <QtCore/QCoreApplication>
  2 #include <QSqlDatabase>
  3 #include <QSqlQuery>
  4 #include <QString>
  5 #include <QDebug>
  6 #include <QSqlError>
  7 #include <QSqlDriver>
  8 
  9 #include <iostream>
 10 
 11 bool test__Qsql_transaction()
 12 {
 13     bool is_success = false;
 14 
 15     do 
 16     {
 17         QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connectionName_test_transaction");
 18         db.setDatabaseName("dbName_test_transaction");
 19         db.setHostName("AK-PC");
 20         db.setUserName("Asterisk");
 21         db.setPassword("0");
 22         //db.setPort(12345);
 23         if (db.open("Asterisk", "0"))
 24         {
 25             if (db.transaction())
 26             {
 27                 QSqlQuery query(db);
 28 
 29                 QString sql;
 30 
 31                 sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";
 32                 query.exec(sql);
 33                 qDebug() << query.lastError();
 34 
 35                 sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('7','test2');";
 36                 bool b1 = query.exec(sql);
 37                 qDebug() << query.lastError();
 38 
 39                 sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('8','test3');";
 40                 bool b2 = query.exec(sql);
 41                 qDebug() << query.lastError();
 42 
 43 
 44                 if ( !db.commit() )
 45                 {
 46                     qDebug() << db.lastError();
 47                     if ( !db.rollback() )
 48                         qDebug() << db.lastError();
 49                 }
 50             }
 51         }
 52         db.close();
 53 
 54     } while ( false );
 55 
 56     return is_success;
 57 }
 58 bool test__Qsql_transaction2()
 59 {
 60     bool is_success = false;
 61 
 62     do 
 63     {
 64         QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connectionName_test_transaction");
 65         db.setDatabaseName("dbName_test_transaction");
 66         db.setHostName("AK-PC");
 67         db.setUserName("Asterisk");
 68         db.setPassword("0");
 69         //db.setPort(12345);
 70         if (db.open("Asterisk", "0"))
 71         {
 72             QSqlQuery query0(db);
 73 
 74             QString sql;
 75             bool b;
 76 
 77             sql = "CREATE TABLE IF NOT EXISTS test_transaction1 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";
 78             b = query0.exec(sql);
 79             qDebug() << query0.lastError();
 80 
 81             sql = "CREATE TABLE IF NOT EXISTS test_transaction2 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";
 82             b = query0.exec(sql);
 83             qDebug() << query0.lastError();
 84 
 85             bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);
 86             if (db.transaction())
 87             {
 88                 QSqlQuery query(db);
 89 
 90                 QString sql1, sql2, sql3, sql4, sql5;
 91                 bool b1, b2, b3, b4, b5;
 92                 QString qstrid = "1";
 93 
 94                 sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES ('";
 95                 sql2 += qstrid;
 96                 sql2 += "','test_1','test_2');";
 97                 b2 = query.exec(sql2);
 98                 qDebug() << query.lastError();
 99 
100                 sql5 = "DELETE FROM test_transaction1 WHERE tid = '";
101                 sql5 += qstrid;
102                 sql5 += "';";
103                 b5 = query.exec(sql5);
104                 qDebug() << query.lastError();
105 
106                 if ( !db.commit() )
107                 {
108                     db.rollback();
109                 }
110             }
111         }
112 
113     } while ( false );
114 
115     return is_success;
116 }
117 bool test__Qsql_transaction3()
118 {
119     bool is_success = false;
120 
121     do 
122     {
123         bool bUse = QSqlDatabase::isDriverAvailable("QOCI");
124         QSqlDatabase db = QSqlDatabase::addDatabase("QOCI", "connectionName_test_transaction3");
125         db.setDatabaseName("dbName_test_transaction_by_oracle");
126         db.setHostName("AK-PC");
127         db.setUserName("Asterisk");
128         db.setPassword("0");
129         //db.setPort(12345);
130         if (db.open("Asterisk", "0"))
131         {
132             QSqlQuery query0(db);
133 
134             QString sql;
135             bool b;
136 
137             sql = "CREATE TABLE IF NOT EXISTS test_transaction1 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";
138             b = query0.exec(sql);
139             qDebug() << query0.lastError();
140 
141             sql = "CREATE TABLE IF NOT EXISTS test_transaction2 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";
142             b = query0.exec(sql);
143             qDebug() << query0.lastError();
144 
145             bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);
146             if (db.transaction())
147             {
148                 QSqlQuery query(db);
149 
150                 QString sql1, sql2, sql3, sql4, sql5;
151                 bool b1, b2, b3, b4, b5;
152                 QString qstrid = "1";
153 
154                 sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES ('";
155                 sql2 += qstrid;
156                 sql2 += "','test_1','test_2');";
157                 b2 = query.exec(sql2);
158                 qDebug() << query.lastError();
159 
160                 sql5 = "DELETE FROM test_transaction1 WHERE tid = '";
161                 sql5 += qstrid;
162                 sql5 += "';";
163                 b5 = query.exec(sql5);
164                 qDebug() << query.lastError();
165 
166                 if ( !db.commit() )
167                 {
168                     db.rollback();
169                 }
170             }
171         }
172 
173     } while ( false );
174 
175     return is_success;
176 }
177 bool test__Qsql_transaction4()
178 {
179     bool is_success = false;
180 
181     do 
182     {
183         bool bUse = QSqlDatabase::isDriverAvailable("QMYSQL");
184         QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
185         qDebug() << db.lastError();
186 
187         db.setDatabaseName("dbName_test_transaction_by_mysql");
188         db.setHostName("AK-PC");
189         db.setUserName("Asterisk");
190         db.setPassword("0");
191         //db.setPort(12345);
192         if (db.open("Asterisk", "0"))
193         {
194             QSqlQuery query0(db);
195 
196             QString sql;
197             bool b;
198 
199             sql = "CREATE TABLE IF NOT EXISTS test_transaction1 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";
200             b = query0.exec(sql);
201             qDebug() << query0.lastError();
202 
203             sql = "CREATE TABLE IF NOT EXISTS test_transaction2 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";
204             b = query0.exec(sql);
205             qDebug() << query0.lastError();
206 
207             bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);
208             if (db.transaction())
209             {
210                 QSqlQuery query(db);
211 
212                 QString sql1, sql2, sql3, sql4, sql5;
213                 bool b1, b2, b3, b4, b5;
214                 QString qstrid = "1";
215 
216                 sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES ('";
217                 sql2 += qstrid;
218                 sql2 += "','test_1','test_2');";
219                 b2 = query.exec(sql2);
220                 qDebug() << query.lastError();
221 
222                 sql5 = "DELETE FROM test_transaction1 WHERE tid = '";
223                 sql5 += qstrid;
224                 sql5 += "';";
225                 b5 = query.exec(sql5);
226                 qDebug() << query.lastError();
227 
228                 if ( !db.commit() )
229                 {
230                     db.rollback();
231                 }
232             }
233         }
234 
235     } while ( false );
236 
237     return is_success;
238 }
239 bool test__Qsql_transaction5()
240 {
241     bool is_success = false;
242 
243     do 
244     {
245         QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
246         db.setDatabaseName("dbName_test_transaction");
247         db.setHostName("AK-PC");
248         db.setUserName("Asterisk");
249         db.setPassword("0");
250         //db.setPort(12345);
251         if (db.open("Asterisk", "0"))
252         {
253             if (db.transaction())
254             {
255                 QSqlQuery query;
256 
257                 QString sql;
258 
259                 sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";
260                 query.exec(sql);
261                 qDebug() << query.lastError();
262 
263                 sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('7','test2');";
264                 bool b1 = query.exec(sql);
265                 qDebug() << query.lastError();
266 
267                 sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('8','test2');";
268                 bool b2 = query.exec(sql);
269                 qDebug() << query.lastError();
270 
271 
272                 if ( !db.commit() )
273                 {
274                     qDebug() << db.lastError();
275                     if ( !db.rollback() )
276                         qDebug() << db.lastError();
277                 }
278             }
279         }
280         db.close();
281 
282     } while ( false );
283 
284     return is_success;
285 }
286 
287 int main(int argc, char *argv[])
288 {
289     QCoreApplication a(argc, argv);
290 
291     //std::cout << std::boolalpha << test__Qsql_transaction() << std::endl;
292     //std::cout << std::boolalpha << test__Qsql_transaction2() << std::endl;
293     //std::cout << std::boolalpha << test__Qsql_transaction3() << std::endl;
294     //std::cout << std::boolalpha << test__Qsql_transaction4() << std::endl;
295     std::cout << std::boolalpha << test__Qsql_transaction5() << std::endl;
296 
297     return a.exec();
298 }

*注：希望哪个高手能够帮我指出是我对qt操作sqlite有问题，还是qt对sqlite的操作就是这样的。万分感谢。

posted @ 2014-10-11 17:39 仙人球球 Views(499) Comments(0) Edit 收藏举报

刷新页面返回顶部

仙人球球

SQLITE和QT

公告