QT Database Operations (Qt-win-commercial-src-4.3.1,vc6,oracle,sql Server)

qt-win-commercial-src-4.3.1, qt-x11-commercial-src-4.3.1
Microsoft Visual C + + 6.0, KDevelop 3.5.0
Windows Xp, Solaris 10, Fedora 8
SQL Server, Oracle 10g Client

, driver compilation
There are two database drivers to be mentioned here, namely ODBC and OCI

The ODBC driver is compiled in the Windows operating system:
Execute the following command to generate Qsqlodbc4.dll under the%qtdir%\plugins\sqldrivers directory.

CD%QTDIR%\SRC\PLUGINS\SQLDRIVERS\ODBC
Qmake-o Makefile Odbc.pro
Nmake

The UNIX operating system compiles UNIXODBC drivers:
UNIXODBC can be downloaded from http://www.unixodbc.org . This assumes that UnixODBC is installed in the/USR/LOCAL/UNIXODBC.
Execute the following command to generate qsqlodbc4.a under the $qtdir/plugins/sqldrivers directory.

CD $QTDIR/SRC/PLUGINS/SQLDRIVERS/ODBC
Qmake "Includepath+=/usr/local/unixodbc/include" "Libs+=-l/usr/local/unixodbc/lib-lodbc"
Make

The Windows operating system compiles the OCI driver:
This assumes that the Oracle client is installed in C:\oracle. Add the environment variable C:\oracle\bin for the Oci.dll Dynamic Connection library.

Set Include=%include%;c:\oracle\oci\include
Set Lib=%lib%;c:\oracle\oci\lib\msvc
CD%qtdir%\src\plugins\sqldrivers\oci
Qmake-o Makefile Oci.pro
Nmake

The UNIX operating system compiles the OCI driver:
Of course according to your Oracle fix the corresponding version number.

CD $QTDIR/src/plugins/sqldrivers/oci
Qmake-o Makefile "includepath+=/usr/include/oracle/10.1.0.3/client/" "Libs+=-l/usr/lib/oracle/10.1.0.3/client/lib "Oci.pro
Make

Include header files in your program
#include <QtSql>
Add in the program's. Pro File
QT + = SQL

, database connections
Here you will mention a single database connection and multiple database connections:

Single Database connection:

static bool SqlConnection (const qstring& HostName,
Const qstring& DatabaseName,
Const qstring& UserName,
Const qstring& Password)
{
Qsqldatabase db = Qsqldatabase::adddatabase ("Qodbc");
Db.sethostname (HostName);
Db.setdatabasename (DatabaseName);
Db.setusername (UserName);
Db.setpassword (Password);

if (!db.open ())
{
Qmessagebox::critical (0, Qobject::tr ("Error"),
QOBJECT::TR ("The database reported an error:%1"). Arg (Db.lasterror (). Text ()));
return false;
}
After the QT database is connected, run the "set NAMES ' UTF8 '" statement or "set NAMES ' GBK '".
Db.exec ("SET NAMES ' UTF8 '");
return true;
}

Multiple database connections
static bool Sqlconnections ()
{
Create a connection that is named ODBC
Qsqldatabase *ODBC = qsqldatabase::adddatabase ("Qodbc", "ODBC");
if (! Defaultdb) {
Qwarning ("Failed to connect to ODBC driver");
return FALSE;
}
Odbc->setdatabasename (Db_odbc_dbname);
Odbc->setusername (Db_odbc_user);
Odbc->setpassword (DB_ODBC_PASSWD);
Odbc->sethostname (Db_odbc_host);
if (! Odbc->open ()) {
Qwarning ("Failed to open Sales database:" + odbc->lasterror (). Drivertext ());
Qwarning (Odbc->lasterror (). Databasetext ());
return FALSE;
}

To create a connection named Oracle
Qsqldatabase *oracle = qsqldatabase::adddatabase ("Qoci", "Oracle");
if (! Oracle) {
Qwarning ("Failed to connect to Oracle driver");
return FALSE;
}
Oracle->setdatabasename (Db_oracle_dbname);
Oracle->setusername (Db_oracle_user);
Oracle->setpassword (DB_ORACLE_PASSWD);
Oracle->sethostname (Db_oracle_host);
if (! Oracle->open ()) {
Qwarning ("Failed to open Orders database:" + oracle->lasterror (). Drivertext ());
Qwarning (Oracle->lasterror (). Databasetext ());
return FALSE;
}

return TRUE;
}

Qsqldatabase maintains a connection pointer returned through the static function of the Adddatabase ().
If you remove a connection, call Qsqldatabase::close () to close the connection, and then remove the connection by using the static function Qsqldatabase::removedatabase ().

int main (int argc, char *argv[])
{
Qapplication app (argc, argv);
Qtextcodec::setcodecforcstrings (Qtextcodec::codecforlocale ());
QTEXTCODEC::SETCODECFORTR (Qtextcodec::codecforlocale ());
Qfont font ("Times", 9, Qfont::normal, FALSE);
App.setfont (font);

Connect to a single database
if (!sqlconnection ("10.0.0.3", "qjkzdb", "sa", "syth7777"))
return 1;

Connect multiple databases
if (!sqlconnections ())
return 1;
The database is opened successfully, getting pointers to them:
Qsqldatabase *oracledb = qsqldatabase::d atabase ("ORACLE");
Now we can execute SQL commands on the Oracle connection or the default connection
...

return App.exec ();
}

, SQL execution action

Qsqlquery provides a select, Insert, Update, delete operation for database records.

Select operation:

Qsqlquery query;
Query.exec ("Select name, salary from employee WHERE salary > 50000");
while (Query.next ()) {
QString name = Query.value (0). toString ();
int salary = Query.value (1). ToInt ();
Qdebug () << name << salary;
}

by Qsqlquery::next (), Qsqlquery::p revious (), Qsqlquery::first (), Qsqlquery::last (), Qsqlquery::seek (),
You can get the position of the next, previous, first, last, or any record.

Insert operation:

Single Insert Data
Qsqlquery query;
Query.prepare ("INSERT into employee (ID, name, salary)"
"VALUES (: ID,: Name,: Salary)");
Query.bindvalue (": id", 1001);
Query.bindvalue (": Name", "Thad Beaumont");
Query.bindvalue (": Salary", 65000);
Query.exec ();

BULK INSERT Data
Qsqlquery query;
Query.prepare ("INSERT into myTable values (?,?)");

Qvariantlist ints;
INTs << 1 << 2 << 3 << 4;
Query.addbindvalue (INTs);
Qvariantlist names;
Names << "Harald" << "Boris" << "Trond" << qvariant (qvariant::string);
Query.addbindvalue (names);

if (!query.execbatch ())
Qdebug () << query.lasterror ();

Update operation:

Qsqlquery query;
Query.prepare ("UPDATE employee SET salary =?") WHERE id = 1003 ");
Query.bindvalue (0, 70000);
Query.exe ();

Delete operation:

Qsqlquery query;
Query.exec ("DELETE from employee WHERE ID = 1007");

Transaction processing:

qsqldatabase::d atabase (). transaction ();
Qsqlquery query;
Query.exec ("Select ID from employee WHERE name = ' Torild Halvorsen '");
if (Query.next ()) {
    int employeeId = query.value (0). ToInt ();
    query.exec ("INSERT into Project (ID, name, ownerid)"
                        "VALUES (201, ' Manhattan Project ', '
                        + qstring::number (employeeId) + ")");
}
Qsqldatabase::d atabase (). commit ();

If the database engine supports transaction processing, the function qsqldriver::hasfeature (qsqldriver::transactions) returns True.
You can initialize a transaction by calling Qsqldatabase::transaction (). After performing the work you want to do in the transaction.
Then execute Qsqldatabase::commit () to commit the transaction or Qsqldatabase::rollback () to cancel the transaction.

Here is an example of Qsqldriver::hasfeature (Qsqldriver::querysize), which can record the number of rows in a faster statistical query.

Qsqlquery query;
int numrows;
Query.exec ("Select name, salary from employee WHERE salary > 50000");
Qsqldatabase defaultdb = qsqldatabase::d atabase ();
if (Defaultdb.driver ()->hasfeature (qsqldriver::querysize)) {
NumRows = Query.size ();
} else {
This can is very slow
Query.last ();
NumRows = query.at () + 1;
}

Stored procedures:

Asciitoint () is a stored procedure in the database.
But I've seen it online before. The stored procedure in SQL Server is done through "EXEC", not "call", I'm not sure! Leave a Question ~

Qsqlquery query;
Query.prepare ("Call Asciitoint (?,?)");
Query.bindvalue (0, "A");
Query.bindvalue (1, 0, qsql::out);
Query.exec ();
int i = Query.boundvalue (1). ToInt (); I is 65

, using the SQL model class

Qsqlquerymodel: A read-only model that reads database data.
Qsqltablemodel: A single tabular model that can read and write, without having to write SQL statements.
A subclass of Qsqlrelationaltablemodel:qsqltablemodel that can be associated with multiple tables.
These classes are inherited from Qabstracttablemodel, and they all inherit from Qabstractitemmodel.

Qsqlquerymodel read-only mode, based on SQL query.

Qsqlquerymodel model;
Model.setquery ("SELECT * from employee");
for (int i = 0; i < Model.rowcount (); ++i) {
int id = Model.record (i). Value ("id"). toint ();
QString name = Model.record (i). Value ("name"). ToString ();
Qdebug () << ID << name;
}

Qsqltablemodel can perform read and write operations on a single table operation.

Reading data
Qsqltablemodel model;
Model.settable ("employee");
Model.setfilter ("Salary > 50000");
Model.setsort (2, Qt::D escendingorder);
Model.select ();
for (int i = 0; i < Model.rowcount (); ++i) {
QString name = Model.record (i). Value ("name"). ToString ();
int salary = Model.record (i). Value ("Salary"). ToInt ();
Qdebug () << name << salary;
}
modifying data with Qsqltablemodel::setrecord ()
for (int i = 0; i < Model.rowcount (); ++i) {
Qsqlrecord record = Model.record (i);
Double salary = Record.value ("Salary"). ToInt ();
Salary *= 1.1;
Record.setvalue ("salary", salary);
Model.setrecord (i, record);
}
Model.submitall ();
Update a record with Qsqltablemodel::setdata ()
Model.setdata (Model.index (Row, column), 75000);
Model.submitall ();
Insert a record
Model.insertrows (row, 1);
Model.setdata (Model.index (row, 0), 1013);
Model.setdata (Model.index (row, 1), "Peter Gordon");
Model.setdata (Model.index (row, 2), 68500);
Model.submitall ();
Delete a record
Model.removerows (row, 5);
Model.submitall ();

The function Qsqltablemodel::submitall () ensures that records are written to the database.

Qsqlrelationaltablemodel enables multiple table associations with foreign keys.

The employee table is associated with the city table and the country table.
Model->settable ("employee");
Model->setrelation (2, Qsqlrelation ("City", "id", "name"));
Model->setrelation (3, Qsqlrelation ("Country", "id", "name"));

, the Data rendering view

Qsqlquerymodel, Qsqltablemodel, Qsqlrelationaltablemodel are generally with the help of Qlistview, Qtableview, Qtreeview bar data presented ~
Here I do not want to qtableview detailed explanation, here do not do too much introduction, perhaps in the future alone Qtableview, Qtablewidget detailed introduction.
The database part is the focus of this time!
Continue to add to the Qsqlrelationaltablemodel, as the above mentioned is too little.

qsqlrelationaltablemodel model;
Model->settable ("employee");
Model->setrelation (2, Qsqlrelation ("City", "id", "name"));
Model->setrelation (3, Qsqlrelation ("Country", "id", "name"));
//Set Caption Header tag Information
Model->setheaderdata (0, Qt::horizontal, qobject::tr ("ID"));
Model->setheaderdata (1, Qt::horizontal, Qobject::tr ("Name"));
Model->setheaderdata (2, Qt::horizontal, Qobject::tr ("City"));
Model->setheaderdata (3, Qt::horizontal, Qobject::tr ("Country"));
//It is worth noting that the data information for that table should be clearly specified at query time, and the following two methods are equivalent.
Model.setfilter (tr ("City.name = '%1 '"). Arg ("Mucich"));
//model.setfilter (tr ("Employee.cityid =%1"). ARG (312));
Model.select ();
//With Qtableview, the data information is displayed,
Qtableview *view = new Qtableview;
View->setmodel (model);
//To design items in the table as not editable mode
View->setedittriggers (qabstractitemview::noedittriggers);
View->show ();

An INSERT, Update, delete operation is performed by Qsqlfield. The above example, continue ~

Qsqlfield IDfield ("id", qvariant::int);
Qsqlfield NameField ("name", qvariant::string);
Qsqlfield Cityidfield ("Cityid", qvariant::int);
Qsqlfield Countryidfield ("Countryid", qvariant::int);

One record Id = 12, Name = Vic. MINg, city = Shenyang, country = China. (Shenyang area code 024, China 086)
Idfield.setvalue (12);
Namefield.setvalue ("Vic. MINg ");
Cityidfield.setvalue (24);
Countryidfield.setvalue (86);

Insert a record, 1 means to join at the end of the line
Qsqlrecord record;
Record.append (IDfield);
Record.append (NameField);
Record.append (Cityidfield);
Record.append (Countryidfield);
Model->insertrecord ( -1, record);

Update a record where row represents the line to be modified
Qsqlrecord record = Model->record (row);
Record.replace (1, NameField);
Record.replace (2, Cityidfield);
Record.replace (3, Countryidfield);
Model->setrecord (row, record);

Delete a record, row represents the line to be modified
Model->removerow (row);

, the Data rendering form

You can associate a form control with a record in a database by Qdatawidgetmapper.

Qdatawidgetmapper *mapper = new Qdatawidgetmapper;
Mapper->setmodel (model);
Mapper->addmapping (idspinbox, 0);
Mapper->addmapping (Namelineedit, 1);
Mapper->addmapping (Citycombobox, 2);
Mapper->addmapping (Countrycombobox, 3);
The current record position can be set by Tofirst (), Tonext (), toprevious (), Tolast (), SetCurrentIndex () to display the corresponding data
Mapper->tofirst ();
Signal, slot mechanism model, view, mapper three links together
Connect (View->selectionmodel (), SIGNAL (currentrowchanged (Qmodelindex,qmodelindex)),
Mapper, SLOT (Setcurrentmodelindex (Qmodelindex)));

http://cool.worm.blog.163.com/blog/static/64339006200833024214394/

QT Database Operations (Qt-win-commercial-src-4.3.1,vc6,oracle,sql Server)