SQLITE和QT
浏览数：41 /
时间：2015年06月12日
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的操作就是这样的。万分感谢。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
