1. After installing the SQLServer database
In SQL Server Configuration Manager
After setting, you need to restart the SQL Server service
2. Key points* Configure ODBC data source
Unable to connect because ODBC is not configured
Start – ODBC Data Source Manager (64-bit)
Afterwards select: SQL Server Authentication using user input login ID and password
Connect to SQL Server to get default settings for other configuration options
Login ID: sa
Password: xxx
Change the default database later
Select Next and click Test Data Source – if Test Successful is displayed, it is complete.
3. Write code with QT and connect to the database
DatabaseProject.pro add sql
#include "widget.h"
#include <QApplication>
#include <QDebug>
#include <QSqlDatabase>
#include <QMessageBox>
#include <QSqlError>
bool OpenDatabase()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
qDebug()<<"ODBC driver is valid: "<<db.isValid();
QString dsn = QString::fromLocal8Bit("QTDSN");
db.setHostName("localhost");
db.setDatabaseName(dsn);
db.setUserName("sa");
db.setPassword("xxx");
if(db.open()) // Note here, it is open(), not isOpen()
{
qDebug()<<"open database success!";
return true;
}
else
{
qDebug()<<db.lastError().text();
QMessageBox::critical(0, QObject::tr("Database error"), db.lastError().text());
return false;
}
}
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
Widget w;
w.show();
OpenDatabase();
return a.exec();
}
If open database success is output, the connection to the database is successful.
4. Try to connect a remote computer to the database
Connect to remote computer database
First, clarify the IP address of the remote computer and see if the ping is successful.
Secondly, if you cannot connect, check whether the computer’s firewall is turned off.
Then, in ODBC data source management, fill in the ip address at the server
Finally, select the specified database and test to see if the connection is successful.
5. Implement the function of adding, deleting, modifying and checking
#include "widget.h"
#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlQuery>
#include <sql_info.h>
//Connect to sqlserver database
// Qt GUI application "Qt QODBC database driver" SQL database
// Target:
//Connect to the host database and obtain data in the database table
//To implement add delete update select table data, just implement it to a table
//Insert data into the specified table
//Add data
bool insert_info(QSqlDatabase db, QString insert_command, Sql_Info *info)
{
QSqlQuery ins_qry(db);
ins_qry.prepare(insert_command);
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
ins_qry.bindValue(":xx", info->get_xx());
bool ins_res = ins_qry.exec();
if(ins_res)
qDebug()<<"Data inserted successfully";
else
qDebug()<<"Failed to insert data";
return ins_res;
}
// delete data
bool delete_info(QSqlDatabase db, QString del_command, QString del_name)
{
QSqlQuery del_qry(db);
del_qry.prepare(del_command);
del_qry.addBindValue(del_name);
bool del_res = del_qry.exec();
if(del_res)
qDebug()<<"Deletion successful";
else
qDebug()<<"Deletion failed";
return del_res;
}
//Modify data in the table
bool update_info(QSqlDatabase db, QString update_command, int xx, int xx, QString xx)
{
QSqlQuery update_qry(db);
update_qry.prepare(update_command);
update_qry.addBindValue(xx);
update_qry.addBindValue(xx);
update_qry.addBindValue(xx);
bool update_res = update_qry.exec();
if(update_res)
qDebug()<<"Update successful";
else
qDebug()<<"Update failed";
return update_res;
}
//Query the data in the specified table
QList<Sql_Info*> query_table_info(QSqlDatabase db, QString query_command)
{
QList<Sql_Info*> sql_list;
// Inquire
QSqlQuery qry(db); // Get the database
if(qry.exec(query_command)){
int sql_size = 0; // Record the number of data in the table
while(qry.next())
{
if(qry.isValid())
{
int xx= qry.value(0).toUInt();
QString xx= qry.value(1).toString();
QString xx= qry.value(2).toString();
int xx= qry.value(3).toUInt();
Sql_Info * si = new Sql_Info(xx, xx, xx, xx);
sql_list.append(si);
qDebug()<<QString("xx:%1; xx:%2; xx:%3").arg(xx).arg(xx).arg(xx);
}
sql_size + + ;
}
}
//qDebug()<<sql_list[1]->get_name();
return sql_list;
}
//Open the database and check whether the connection is successful
bool OpenDatabase()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC"); // Database driver type SQLServer
qDebug()<<"ODBC driver is valid: "<<db.isValid();
QString dsn = QString::fromLocal8Bit("QTHostDBDSN"); // Database name
db.setHostName("10.40.19.9");
db.setDatabaseName(dsn);
db.setUserName("sa");
db.setPassword("123");
if(db.open())
{
qDebug()<<"Database opened successfully";
//Query the specified table data in the database
//QString command = "SELECT * FROM xx table";
//QList<Sql_Info*> res = query_table_info(db, command);
//Insert data into the table
//QString insert_command = "INSERT INTO xx table (xx,xx,xx,xx,xx,xx,xx,xx) VALUES (:xx,:xx,:xx,:xx,:xx,:xx,:xx, :xx)";
//Sql_Info *v = new Sql_Info("12345679", "Zhang San", 2, "xx", 9, 10, 80, 0);
//insert_info(db, insert_command, v);
QSqlQuery insert_qry(db);
QString sel_command = "SELECT * FROM table xx";
if(insert_qry.exec(sel_command))
{
QString ins_qry_str = "INSERT INTO xx table"
}
//Delete data in the table
//QString delete_command = "DELETE FROM table xx WHERE xx=?";
//QString delete_name = "Zhang San";
//delete_info(db, delete_command, delete_name);
//Modify data in the table
//QString update_command = "UPDATE xx table SET xx=?, xx=? WHERE xx=?";
//update_info(db, update_command, 11, 80, "xx");
return true;
}
else
{
qDebug()<<"Database opening failed";
qDebug()<<db.lastError();
QMessageBox::critical(0, QObject::tr("Database Error"), db.lastError().text());
return false;
}
}
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
Widget w;
w.show();
OpenDatabase();
return a.exec();
}