天朝皇叔:学习笔记 Qt 连接数据库sql server

在项目文件里面加上：QT       += sql

注意连接字符串的多种方式：直接填连接字符串；字符串与函数调用拼接、使用数据源，示例代码：

​
#include <QtCore/QCoreApplication>
#include <QtSql>
#include <QStringList>
#include <QSqlQuery>
#include <QDebug>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    //qDebug() << "Available drivers:";

 //测试QT系统当前安装的驱动
    QStringList drivers = QSqlDatabase::drivers();
    foreach(QString driver, drivers)
    qDebug() << "\t" << driver;
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    qDebug() << "ODBC driver valid?" << db.isValid();

    //连接方式1 完全使用链接字符串 不用配置数据源
   QString dsn = QString::fromLocal8Bit("DRIVER={SQL SERVER};SERVER=192.168.1.42;Uid=sa;Pwd=woaini;DATABASE=Northwind");
  db.setDatabaseName(dsn);

  //连接方式2 使用链接字符串 拼接
    //QString dsn = QString::fromLocal8Bit("DRIVER={SQL SERVER};SERVER=192.168.1.42;DATABASE=Northwind");
    //db.setDatabaseName(dsn);
    // db.setUserName("sa");
    // db.setPassword("woaini");

    // 3.使用数据源 zfgdb是系统配置的sql server ODBC数据源名称，密码在数据源已保存
    // db.setDatabaseName("zfgdb");//zfgdb是系统配置的sql server  ODBC数据源名称
      
  //4 连接 Access数据库
//      //db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=myaccessfile.mdb");
//       db.setDatabaseName(dsn);

    if(db.open()){
        qDebug() <<"hostname:"<<db.hostName();
        QSqlQuery query;
        query.exec("select * from Region");
        while (query.next()) {
            qDebug() << "\t"<< query.value(1).toString();

        }
    }
    else
        qDebug() << "Database not opened!";

    return a.exec();
}



​

 数据源选默认数据库：

 通过ODBC数据库驱动的链接字符串， 见 sql server 联机文档关于  SQLDriverConnect 的描述(附后)
  Qt 最后都是调用了微软的驱动程序

  //Qt 关于关于连接字符串，见Qt 文档：
 void QSqlDatabase::setDatabaseName ( const QString & name ):

For the QOCI (Oracle) driver, the database name is the TNS Service Name.

For the QODBC driver, the name can either be:
1.   a DSN,
2.  a DSN filename (in which case the file must have a .dsn extension),
3.  or a connection string.

For example, Microsoft Access users can use the following connection string to open an .mdb file directly,
instead of having to create a DSN entry in the ODBC manager:

...
db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=myaccessfile.mdb");
if (db.open()) {
 // success!

看几个重要函数源代码，就会明白链接字符串的意义：

bool QSqlDatabase::open()
{
    return d->driver->open(d->dbname, d->uname, d->pword, d->hname,
                            d->port, d->connOptions);
}

}

bool QODBCDriver::open(const QString & db,
                        const QString & user,
                        const QString & password,
                        const QString &,
                        int,
                        const QString& connOpts)
{
    if (isOpen())
      close();
    SQLRETURN r;
    //....
    // Create the connection string  生成连接字符串
    QString connQStr;
    // support the "DRIVER={SQL SERVER};SERVER=blah" syntax
    if (db.contains(QLatin1String(".dsn"), Qt::CaseInsensitive))
        connQStr = QLatin1String("FILEDSN=") + db;
    else if (db.contains(QLatin1String("DRIVER="), Qt::CaseInsensitive)
            || db.contains(QLatin1String("SERVER="), Qt::CaseInsensitive))
        connQStr = db;
    else
        connQStr = QLatin1String("DSN=") + db;

    if (!user.isEmpty())
        connQStr += QLatin1String(";UID=") + user;
    if (!password.isEmpty())
        connQStr += QLatin1String(";PWD=") + password;

    SQLSMALLINT cb;
    QVarLengthArray<SQLTCHAR> connOut(1024);
    memset(connOut.data(), 0, connOut.size() * sizeof(SQLTCHAR));
    r = SQLDriverConnect(d->hDbc,
                          NULL,
                          toSQLTCHAR(connQStr).data(),
                          (SQLSMALLINT)connQStr.length(),
                          connOut.data(),
                          1024,
                          &cb,
                        0);   //  SQL_DRIVER_NOPROMPT  

   //....错误处理
    //    return true;
}

 

 

  本人实际的代码：

通过ODBC数据库驱动的链接字符串， 见 sql server 联机文档关于  SQLDriverConnect 的描述

SQLDriverConnect

The Microsoft® SQL Server™ ODBC driver and the ODBC driver manager recognize the following SQLDriverConnect connection string keywords.

Keyword	Description
Address	Network address of the server running an instance of SQL Server. Address is usually the network name of the server, but can be other names such as a pipe, or a TCP/IP port and socket address. For more information, see Managing Clients.
AnsiNPW	When yes, the driver uses ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation. When no