SQLITE和QT
2024-10-20 11:49:10
sqlite3数据库支持事务
例如：
BEGIN DEFERRED TRANSACTION;
INSERT INTO main.test_transaction (test_unique) VALUES ('test_6');
INSERT INTO main.test_transaction (test_unique) VALUES ('test_6');
COMMIT TRANSACTION;
第二个插入sql语句是不符合约束的，所以事务提交失败。
对比mysql，例如：
SET autocommit = 0;
START TRANSACTION;
INSERT INTO `他天天天天`.test_2 (test_column,test_column2) VALUES ('', 'test2');
INSERT INTO `他天天天天`.test_2 (test_column,test_column2) VALUES ('', 'test3');
COMMIT;
同样，第二个插入sql语句是不符合约束的，所以事务提交失败。
但是到了qt里面使用sqlite3数据库时却发现了个问题：提交事务时，sql语句有正确的、错误的，但是提交都会成功，commit函数返回true。
请看测试代码：
bool test__Qsql_transaction5()
{
bool is_success = false; do
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("dbName_test_transaction");
db.setHostName("AK-PC");
db.setUserName("Asterisk");
db.setPassword("");
//db.setPort(12345);
if (db.open("Asterisk", ""))
{
if (db.transaction())
{
QSqlQuery query; QString sql; sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";
query.exec(sql);
qDebug() << query.lastError(); sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('7','test2');";
bool b1 = query.exec(sql);
qDebug() << query.lastError(); sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('8','test2');";
bool b2 = query.exec(sql);
qDebug() << query.lastError(); if ( !db.commit() )
{
qDebug() << db.lastError();
if ( !db.rollback() )
qDebug() << db.lastError();
}
}
}
db.close(); } while ( false ); return is_success;
}
同样，第二个插入sql语句是不符合约束的，所以事务提交失败。但是“db.commit()”返回true，不知道问题出在哪里。
完成测试代码：
#include <QtCore/QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QString>
#include <QDebug>
#include <QSqlError>
#include <QSqlDriver> #include <iostream> bool test__Qsql_transaction()
{
bool is_success = false; do
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connectionName_test_transaction");
db.setDatabaseName("dbName_test_transaction");
db.setHostName("AK-PC");
db.setUserName("Asterisk");
db.setPassword("");
//db.setPort(12345);
if (db.open("Asterisk", ""))
{
if (db.transaction())
{
QSqlQuery query(db); QString sql; sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";
query.exec(sql);
qDebug() << query.lastError(); sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('7','test2');";
bool b1 = query.exec(sql);
qDebug() << query.lastError(); sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('8','test3');";
bool b2 = query.exec(sql);
qDebug() << query.lastError(); if ( !db.commit() )
{
qDebug() << db.lastError();
if ( !db.rollback() )
qDebug() << db.lastError();
}
}
}
db.close(); } while ( false ); return is_success;
}
bool test__Qsql_transaction2()
{
bool is_success = false; do
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connectionName_test_transaction");
db.setDatabaseName("dbName_test_transaction");
db.setHostName("AK-PC");
db.setUserName("Asterisk");
db.setPassword("");
//db.setPort(12345);
if (db.open("Asterisk", ""))
{
QSqlQuery query0(db); QString sql;
bool b; 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);";
b = query0.exec(sql);
qDebug() << query0.lastError(); 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);";
b = query0.exec(sql);
qDebug() << query0.lastError(); bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);
if (db.transaction())
{
QSqlQuery query(db); QString sql1, sql2, sql3, sql4, sql5;
bool b1, b2, b3, b4, b5;
QString qstrid = ""; sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES ('";
sql2 += qstrid;
sql2 += "','test_1','test_2');";
b2 = query.exec(sql2);
qDebug() << query.lastError(); sql5 = "DELETE FROM test_transaction1 WHERE tid = '";
sql5 += qstrid;
sql5 += "';";
b5 = query.exec(sql5);
qDebug() << query.lastError(); if ( !db.commit() )
{
db.rollback();
}
}
} } while ( false ); return is_success;
}
bool test__Qsql_transaction3()
{
bool is_success = false; do
{
bool bUse = QSqlDatabase::isDriverAvailable("QOCI");
QSqlDatabase db = QSqlDatabase::addDatabase("QOCI", "connectionName_test_transaction3");
db.setDatabaseName("dbName_test_transaction_by_oracle");
db.setHostName("AK-PC");
db.setUserName("Asterisk");
db.setPassword("");
//db.setPort(12345);
if (db.open("Asterisk", ""))
{
QSqlQuery query0(db); QString sql;
bool b; 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);";
b = query0.exec(sql);
qDebug() << query0.lastError(); 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);";
b = query0.exec(sql);
qDebug() << query0.lastError(); bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);
if (db.transaction())
{
QSqlQuery query(db); QString sql1, sql2, sql3, sql4, sql5;
bool b1, b2, b3, b4, b5;
QString qstrid = ""; sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES ('";
sql2 += qstrid;
sql2 += "','test_1','test_2');";
b2 = query.exec(sql2);
qDebug() << query.lastError(); sql5 = "DELETE FROM test_transaction1 WHERE tid = '";
sql5 += qstrid;
sql5 += "';";
b5 = query.exec(sql5);
qDebug() << query.lastError(); if ( !db.commit() )
{
db.rollback();
}
}
} } while ( false ); return is_success;
}
bool test__Qsql_transaction4()
{
bool is_success = false; do
{
bool bUse = QSqlDatabase::isDriverAvailable("QMYSQL");
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
qDebug() << db.lastError(); db.setDatabaseName("dbName_test_transaction_by_mysql");
db.setHostName("AK-PC");
db.setUserName("Asterisk");
db.setPassword("");
//db.setPort(12345);
if (db.open("Asterisk", ""))
{
QSqlQuery query0(db); QString sql;
bool b; 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);";
b = query0.exec(sql);
qDebug() << query0.lastError(); 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);";
b = query0.exec(sql);
qDebug() << query0.lastError(); bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);
if (db.transaction())
{
QSqlQuery query(db); QString sql1, sql2, sql3, sql4, sql5;
bool b1, b2, b3, b4, b5;
QString qstrid = ""; sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES ('";
sql2 += qstrid;
sql2 += "','test_1','test_2');";
b2 = query.exec(sql2);
qDebug() << query.lastError(); sql5 = "DELETE FROM test_transaction1 WHERE tid = '";
sql5 += qstrid;
sql5 += "';";
b5 = query.exec(sql5);
qDebug() << query.lastError(); if ( !db.commit() )
{
db.rollback();
}
}
} } while ( false ); return is_success;
}
bool test__Qsql_transaction5()
{
bool is_success = false; do
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("dbName_test_transaction");
db.setHostName("AK-PC");
db.setUserName("Asterisk");
db.setPassword("");
//db.setPort(12345);
if (db.open("Asterisk", ""))
{
if (db.transaction())
{
QSqlQuery query; QString sql; sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";
query.exec(sql);
qDebug() << query.lastError(); sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('7','test2');";
bool b1 = query.exec(sql);
qDebug() << query.lastError(); sql = "INSERT INTO test_transaction (tid,param_2) VALUES ('8','test2');";
bool b2 = query.exec(sql);
qDebug() << query.lastError(); if ( !db.commit() )
{
qDebug() << db.lastError();
if ( !db.rollback() )
qDebug() << db.lastError();
}
}
}
db.close(); } while ( false ); return is_success;
} int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv); //std::cout << std::boolalpha << test__Qsql_transaction() << std::endl;
//std::cout << std::boolalpha << test__Qsql_transaction2() << std::endl;
//std::cout << std::boolalpha << test__Qsql_transaction3() << std::endl;
//std::cout << std::boolalpha << test__Qsql_transaction4() << std::endl;
std::cout << std::boolalpha << test__Qsql_transaction5() << std::endl; return a.exec();
}
*注：希望哪个高手能够帮我指出是我对qt操作sqlite有问题，还是qt对sqlite的操作就是这样的。万分感谢。
最新文章
- Echarts使用
- 《Breakfast At Tiffanys》
- 基础_ _返回键back
- 如何查看 Apache 的版本
- bzoj1132
- mysql performance_schema 初探
- Oracle每10天删除数据，并重建索引
- node.js操作mongoDB数据库
- docke 网络配置2
- mysql三学习sql声明学习
- KTHREAD 线程调度 SDT TEB SEH shellcode中DLL模块机制动态
- 复杂xml格式报文和实体类之间的转化
- 如何像Python高手(Pythonista)一样编程
- vscode切换界面布局
- oracle12c安装+配置，plsql 13安装+激活
- C++笔记（2017/2/9）
- [ZOJ 4062][2018ICPC青岛站][Plants vs. Zombies]
- asp.net网站中增删文件夹会导致Session或cache等等丢失
- Appium 1.6安装步骤
- WebDriverException：Element is not clickable at point - selenium执行过程中遇到的相关报错
热门文章
- CentOS环境下yum安装LAMP(Linux+Apache+Mysql+php)
- leecode 树是否是平衡树 java
- C语言中的宏
- First Adventures in Google Closure -摘自网络
- storyboard ID
- Java编程常见问题汇总
- Linux防火墙iptables学习笔记（三）iptables命令详解和举例[转载]
- struts2自己定义拦截器
- 通过源码看android系列之multidex库
- Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)--reference