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

朴灿烈づ我的快乐病毒、 2022-03-17 05:30 242阅读 0赞

在项目文件里面加上：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();
 }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 1][]

数据源选默认数据库：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 4][]

通过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.

<table style="width:440.25pt;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> Keyword </td> 
 <td style="border-color:#000000;vertical-align:top;"> Description </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> Address </td> 
 <td style="border-color:#000000;vertical-align:top;"> 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. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> AnsiNPW </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, the driver uses ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation. When no, ANSI defined behaviors are not exposed. For more information about ANSI NPW behaviors, see Effects of SQL-92 Options. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> APP </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of the application calling SQLDriverConnect (optional). If specified, this value is stored in the master.dbo.sysprocesses column program_name and is returned by sp_who and the Transact-SQL APP_NAME function. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> AttachDBFileName </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of the primary file of an attachable database. Include the full path and escape any \ characters if using a C character string variable: AttachDBFileName=c:\\MyFolder\\MyDB.mdf This database is attached and becomes the default database for the connection. To use AttachDBFileName you must also specify the database name in either the SQLDriverConnnect DATABASE parameter or the SQL_COPT_CURRENT_CATALOG connection attribute. If the database was previously attached, SQL Server will not reattach it; it will use the attached database as the default for the connection. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> AutoTranslate </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, ANSI character strings sent between the client and server are translated by converting through Unicode to minimize problems in matching extended characters between the code pages on the client and the server: Client SQL_C_CHAR data sent to a SQL Server char, varchar, or text variable, parameter, or column is converted from character to Unicode using the client ANSI code page (ACP), then converted from Unicode to character using the ACP of the server. SQL Server char, varchar, or text data sent to a client SQL_C_CHAR variable is converted from character to Unicode using the server ACP, then converted from Unicode to character using the client ACP. These conversions are performed on the client by the SQL Server ODBC driver. This requires that the same ANSI code page (ACP) used on the server be available on the client. These settings have no effect on the conversions that occur for these transfers: Unicode SQL_C_WCHAR client data sent to char, varchar, or text on the server. char, varchar, or text server data sent to a Unicode SQL_C_WCHAR variable on the client. ANSI SQL_C_CHAR client data sent to Unicode nchar, nvarchar, or ntext on the server. Unicode char, varchar, or text server data sent to an ANSI SQL_C_CHAR variable on the client. When no, character translation is not performed. The SQL Server ODBC driver does not translate client ANSI character SQL_C_CHAR data sent to char, varchar, or text variables, parameters, or columns on the server. No translation is performed on char, varchar, or text data sent from the server to SQL_C_CHAR variables on the client. If the client and SQL Server are using different ACPs, then extended characters can be misinterpreted. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> DATABASE </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of the default SQL Server database for the connection. If Database is not specified, the default database defined for the login is used. The default database from the ODBC data source overrides the default database defined for the login. The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by DATABASE. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> DRIVER </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of the driver as returned by SQLDrivers. The keyword value for the SQL Server ODBC driver is "{SQL Server}". The braces are required when using version 2.65 or earlier of the SQL Server ODBC driver. The SERVER keyword is required if DRIVER is specified and DriverCompletion is set to SQL_DRIVER_NOPROMPT. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> DSN </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of an existing ODBC user or system data source. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> Fallback (SQL Server 6.5 only) </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, instructs the driver to attempt connection to a fallback server if connection to a primary server fails. The login time-out (set with ODBC SQLSetConnectAttr, attribute SQL_ATTR_LOGIN_TIMEOUT) must be set for fallback to occur. When no, no attempt at a fallback connection is made. This option applies only to standby servers. It does not apply to a virtual server in a cluster/failover configuration.&nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> FILEDSN </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of an existing ODBC file data source. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> LANGUAGE </td> 
 <td style="border-color:#000000;vertical-align:top;"> SQL Server language name (optional). SQL Server can store messages for multiple languages in sysmessages. If connecting to a SQL Server with multiple languages, Language specifies which set of messages are used for the connection. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> Network </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of a network library dynamic-link library. The name need not include the path and must not include the .dll file name extension, for example, Network=dbnmpntw. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> PWD </td> 
 <td style="border-color:#000000;vertical-align:top;"> The password for the SQL Server login account specified in the UID parameter. PWD need not be specified if the login has a NULL password or when using Windows Authentication (Trusted_Connection = yes). </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> SAVEFILE </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of an ODBC data source file into which the attributes of the current connection are saved if the connection is successful. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> SERVER </td> 
 <td style="border-color:#000000;vertical-align:top;"> Name of a server running SQL Server on the network. The value must be either the name of a server on the network, or the name of a SQL Server Client Network Utility advanced server entry. You can enter (local) as the server name on Microsoft Windows® NT 4.0 to connect to a copy of SQL Server running on the same computer. SQL Server 2000 supports multiple instances of SQL Server running on the same computer. To specify a named instance of SQL Server, the server name is specified as ServerName\InstanceName. For more information about server names, see Managing Clients. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> QueryLogFile </td> 
 <td style="border-color:#000000;vertical-align:top;"> Full path and file name of a file to use to log data on long-running queries. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> QueryLog_On </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, logging long-running query data is enabled on the connection. When no, long-running query data is not logged. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> QueryLogTime </td> 
 <td style="border-color:#000000;vertical-align:top;"> Digit character string specifying the threshold (in milliseconds) for logging long-running queries. Any query that does not get a response in the time specified is written to the long-running query log file. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> QuotedID </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, QUOTED_IDENTIFIERS is set ON for the connection, SQL Server uses the SQL-92 rules regarding the use of quotation marks in SQL statements. When no, QUOTED_IDENTIFIERS is set OFF for the connection. SQL Server then follows the legacy Transact-SQL rules regarding the use of quotation marks in SQL statements. For more information, see Effects of SQL-92 Options. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> Regional </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, the SQL Server ODBC driver uses client settings when converting currency, date, and time data to character data. The conversion is one way only; the driver does not recognize non-ODBC standard formats for date strings or currency values within; for example, a parameter used in an INSERT or UPDATE statement. When no, the driver uses ODBC standard strings to represent currency, date, and time data that is converted to string data. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> StatsLogFile </td> 
 <td style="border-color:#000000;vertical-align:top;"> Full path and file name of a file used to record SQL Server ODBC driver performance statistics. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> StatsLog_On </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, enables the capture of SQL Server ODBC driver performance data. When no, SQL Server ODBC driver performance data is not available on the connection. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> Trusted_Connection </td> 
 <td style="border-color:#000000;vertical-align:top;"> When yes, instructs the SQL Server ODBC driver to use Windows Authentication Mode for login validation. The UID and PWD keywords are optional. When no, instructs the SQL Server ODBC driver to use a SQL Server username and password for login validation. The UID and PWD keywords must be specified. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> UID </td> 
 <td style="border-color:#000000;vertical-align:top;"> A valid SQL Server login account. UID need not be specified when using Windows Authentication. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> UseProcForPrepare (SQL Server 6.5 and earlier only) </td> 
 <td style="border-color:#000000;vertical-align:top;"> When 1, instructs the SQL Server ODBC driver to create temporary stored procedures when statements are prepared with SQLPrepare. The temporary stored procedures are not dropped until the connection is broken. When 2, the SQL Server ODBC driver creates temporary stored procedures for SQLPrepare, but only one procedure is created per statement handle and the procedure is dropped when the statement handle becomes invalid or a new SQL statement is prepared. When 0, the SQL Server ODBC driver does not create temporary stored procedures for SQLPrepare. </td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000;vertical-align:top;"> WSID </td> 
 <td style="border-color:#000000;vertical-align:top;"> Workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the Transact-SQL HOST_NAME function. </td> 
 </tr> 
 </tbody> 
</table>

![20190222171406872.png][]

**Note**  Regional conversion settings apply to currency, numeric, date, and time data types. The conversion setting is only applicable to output conversion and is only visible when currency, numeric, date, or time values are converted to character strings.

The driver uses the locale registry settings for the current user. The driver does not honor the current thread's locale if the application sets it after connection by, for example, calling SetThreadLocale.

Altering the regional behavior of a data source can cause application failure. An application that parses date strings, and expects date strings to appear as defined by ODBC, could be adversely affected by altering this value.

The SQL Server ODBC driver defines connection attributes that either replace or enhance connection\-string keywords. Several connection\-string keywords have default values specified by the SQL Server ODBC driver. For more information about SQL Server connection attributes and driver default behaviors, see SQLSetConnectAttr.

When the **SQLDriverConnect** *DriverCompletion*parameter value is SQL\_DRIVER\_PROMPT, SQL\_DRIVER\_COMPLETE, or SQL\_DRIVER\_COMPLETE\_REQUIRED, the SQL Server ODBC driver retrieves keyword values from the displayed dialog box. If the keyword value is passed in the connection string and the user does not alter the value for the keyword in the dialog box, the SQL Server ODBC driver uses the value from the connection string. If the value is not set in the connection string and the user makes no assignment in the dialog box, the driver uses the default.

**SQLDriverConnect** must be given a valid *WindowHandle* when any *DriverCompletion* value requires (or could require) the display of the driver's connection dialog box. An invalid handle returns SQL\_ERROR.

Specify either the DRIVER or DSN keywords. ODBC states that a driver uses the leftmost of these two keywords and ignores the other if both are specified. If DRIVER is specified, or is the leftmost of the two, and the **SQLDriverConnect** *DriverCompletion* parameter value is SQL\_DRIVER\_NOPROMPT, the SERVER keyword and an appropriate value are required.

When SQL\_DRIVER\_NOPROMPT is specified, user authentication keywords must be present with values. The driver ensures that either the string "Trusted\_Connection=yes" or both the UID and PWD keywords are present.

If the *DriverCompletion* parameter value is SQL\_DRIVER\_NOPROMPT or SQL\_DRIVER\_COMPLETE\_REQUIRED and the language or database comes from the connection string and either is invalid, **SQLDriverConnect** returns SQL\_ERROR.

If the *DriverCompletion* parameter value is SQL\_DRIVER\_NOPROMPT or SQL\_DRIVER\_COMPLETE\_REQUIRED and the language or database comes from the ODBC data source definitions and either is invalid, **SQLDriverConnect** uses the default language or database for the specified user ID and returns SQL\_SUCCESS\_WITH\_INFO.

If the *DriverCompletion* parameter value is SQL\_DRIVER\_COMPLETE or SQL\_DRIVER\_PROMPT and if the language or database is invalid, **SQLDriverConnect** redisplays the dialog box.

**Examples**

The following call illustrates the least amount of data required for **SQLDriverConnect**:

SQLDriverConnect(hdbc, hwnd,

(SQLTCHAR\*) "DRIVER=\{SQL Server\};" SQL\_NTS, szOutConn,

MAX\_CONN\_OUT, cbOutConn, SQL\_DRIVER\_COMPLETE);

The following connection strings illustrate minimum required data when the *DriverCompletion* parameter value is SQL\_DRIVER\_NOPROMPT:

"DSN=Human Resources;UID=Smith;PWD=Sesame"

"DSN=Human Resources;Trusted\_Connection=yes"

"FILEDSN=HR\_FDSN;UID=Smith;PWD=Sesame"

"FILEDSN=HR\_FDSN;Trusted\_Connection=yes"

"DRIVER=\{SQL Server\};SERVER=hrserver;UID=Smith;PWD=Sesame"

"DRIVER=\{SQL Server\};SERVER=hrserver;Trusted\_Connection=yes"

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70]: /images/20220317/99e8bf916942494b84f7bacef61ae087.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 1]: /images/20220317/f80659ecc1cc4ca284c792d04193c839.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 2]: /images/20220317/0a89e23b49e24046a60d06575ba887b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 3]: /images/20220317/d89dd82341294592bd30bd4bca701dc6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NpYmlyZW4yMDEx_size_16_color_FFFFFF_t_70 4]: /images/20220317/e7e1c7e25fc44604a68a502a79fd4698.png
[20190222171406872.png]: /images/20220317/b611cc70dc0e469883952a381daffeeb.png