PDA

View Full Version : Switching between two databases



codeman
13th May 2009, 10:36
Hello Friends,

how could I switch between two databases on different servers when I have two fill two views with two tables from databses. When I add the the two databases the last Database sets itself as default one and one view is not filled up.
Is there a mechanism two switch between the connections when I have two fire up a sql statement.???

spirit
13th May 2009, 10:41
use this method QSqlDatabase::database.

codeman
13th May 2009, 10:56
hmm

Ok I connect my databses like this



QString driver= "QODBC";
m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Datab ase=db1;Uid="";Pwd="";";
m_TrackDB = QSqlDatabase::addDatabase(driver);
m_TrackDB.setDatabaseName(m_qstrConTrackDB);
if (!m_TrackDB.open()) {
QSqlError err = m_TrackDB.lastError();
QMessageBox::information(0, QObject::tr("Cannot open database"), err.text());
}

m_qstrConDashDB="DRIVER={SQL Server Native Client 10.0};Server2;Trusted_Connection=yes;Database=db2; Uid="";Pwd="";";
m_DashDB = QSqlDatabase::addDatabase(driver);
m_DashDB.setDatabaseName(m_qstrConDashDB);
if (!m_DashDB.open()) {
QSqlError err = m_DashDB.lastError();
QMessageBox::information(0, QObject::tr("Cannot open database"), err.text());
}


How do I use the method you metioned ???


m_TrackDB::database(m_qstrConTrackDB,true);

thenn fill up the first view
and then


m_TrackDB::database(m_qstrConTrackDB,false);
m_DashDB::database(m_qstrConDashDB,true);

then fill up the second view

Is that right??

spirit
13th May 2009, 11:01
when you work with several databases you have to specify connectionName in QSqlDatabase::addDatabase and then you have to specify this parameter in QSqlDatabase::database.
so, you code should look like


QString driver= "QODBC";
m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Datab ase=db1;Uid="";Pwd="";";
m_TrackDB = QSqlDatabase::addDatabase(driver, "db1");//connection name specified
m_TrackDB.setDatabaseName(m_qstrConTrackDB);
if (!m_TrackDB.open()) {
QSqlError err = m_TrackDB.lastError();
QMessageBox::information(0, QObject::tr("Cannot open database"), err.text());
}

m_qstrConDashDB="DRIVER={SQL Server Native Client 10.0};Server2;Trusted_Connection=yes;Database=db2; Uid="";Pwd="";";
m_DashDB = QSqlDatabase::addDatabase(driver, "db2");//connection name specified
m_DashDB.setDatabaseName(m_qstrConDashDB);
if (!m_DashDB.open()) {
QSqlError err = m_DashDB.lastError();
QMessageBox::information(0, QObject::tr("Cannot open database"), err.text());
}
...
m_TrackDB = QSqlDatabase::database("db1");//connection name specified
m_DashDB = QSqlDatabase::database("db2");//connection name specified
...

codeman
13th May 2009, 11:07
The second argument in the method databse have to be a boolean! Or am I wrong??

spirit
13th May 2009, 11:09
from docs


QSqlDatabase QSqlDatabase::database ( const QString & connectionName = QLatin1String( defaultConnection ), bool open = true ) [static]

that means, that third argument is bool. ;)

codeman
13th May 2009, 11:24
Hard to believe cause I see only one comma between the parantheses and that means only two argument....

And when I specify a name as second argument as you mentioned



m_TrackDB = QSqlDatabase::addDatabase(driver,"db1");


my DB don´t connects?????

spirit
13th May 2009, 11:25
sorry. yes, the first paremetr is QString, the second is bool.
I updated code above.

codeman
13th May 2009, 11:39
I don´t understand it ;o(( hmmm

Why I have to ovveride my QSqldatabse m_TrackDB with



m_TrackDB = QSqlDatabase::database("db1");


after I make this



QString driver= "QODBC";
m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Datab ase=db1;Uid="";Pwd="";";
m_TrackDB = QSqlDatabase::addDatabase(driver);


both methods returns a QSqlDatabase object....

spirit
13th May 2009, 11:46
this method QSqlDatabase::addDatabase(driver); returns a databse with hard-code connection name which is used under-the-hood in QSqlQuery/QSqlTableModel/QSqlQueryModel and if you add another database in such way old connection will be lost. so, to avoid this you need specify connectionName for correct determination of dtabase connections.

Lykurg
13th May 2009, 11:58
By the way: you don't have to store the databases at all. This can cause ugly warning messages when quitting the application. Better use:

::Constructor()
{
QSqlDatabase tempDB;

tempDB = QSqlDatabase::addDatabase("QODBC", "Database_01");
tempDB.set//XXX...

tempDB = QSqlDatabase::addDatabase("QODBC", "Database_02");
tempDB.set//XXX...
}

::MyFunction()
{
QSqlDatabase usedDB = (ifFancyStuffIsTrue) ? QSqlDatabase::database("Database_01") : QSqlDatabase::database("Database_02");

// use usedDB normal...
}

spirit
13th May 2009, 12:01
I missed to post this. :)

codeman
13th May 2009, 12:28
Ok when I make it like this


QString driver= "QODBC";
m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Datab ase=TrackingDB;Uid="";Pwd="";";
m_TrackDB = QSqlDatabase::addDatabase(driver);
m_TrackDB.setDatabaseName(m_qstrConTrackDB);
if (!m_TrackDB.open()) {
QSqlError err = m_TrackDB.lastError();
QMessageBox::information(0, QObject::tr("Cannot open database"), err.text());
}

m_qstrConDashDB="DRIVER={SQL Server Native Client 10.0};Server=server2;Trusted_Connection=yes;Databa se=Dashboard;Uid="";Pwd="";";
m_DashDB = QSqlDatabase::addDatabase(driver);
m_DashDB.setDatabaseName(m_qstrConDashDB);
if (!m_DashDB.open()) {
QSqlError err = m_DashDB.lastError();
QMessageBox::information(0, QObject::tr("Cannot open database"), err.text());
}

mainTab = new QTabWidget(this);
redTab = new QTabWidget();
model = new QSqlTableModel(mainTab);
model2 = new QSqlTableModel(redTab);
QString tableName = "tabel1";
QString tableRed = "table2";
model->setTable(tableName);
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();

//Filling up the columnnames
QStringList colnameList;
QSqlQuery query("SELECT COLUMN_NAME "
"FROM INFORMATION_SCHEMA.Columns "
"where TABLE_NAME = ' " + tableName +" ' ",m_TrackDB);
while (query.next()) {
QString qstrLine = query.value(0).toString();
colnameList << qstrLine;
}
int colCounter(0);
foreach (QString str, colnameList)
{
std::string mystdout = str.toAscii().constData();
std::cout << "ColHeader " << mystdout << std::endl;
QByteArray ba = str.toLatin1();
const char *c_str = ba.data();
model->setHeaderData(colCounter++, Qt::Horizontal, c_str);
}
QStringList redColnameList;

//Filling up the List with colNames
QSqlQuery query2("SELECT COLUMN_NAME "
"FROM INFORMATION_SCHEMA.Columns "
"where TABLE_NAME = ' " + tableRed +" ' ",m_DashDB);
while (query2.next()) {
QString qstrLine = query2.value(0).toString();
redColnameList << qstrLine;
}
int colCounter2(0);
foreach (QString str, redColnameList)
{
QByteArray ba = str.toLatin1();
const char *c_str = ba.data();
model2->setHeaderData(colCounter2++, Qt::Horizontal, c_str);
}
model2->setTable(tableRed);
model2->setEditStrategy(QSqlTableModel::OnManualSubmit);
model2->select();

//! [0] //! [1]
view = new QTableView;
view->setModel(model);
view->setAlternatingRowColors(true);
view->resizeColumnsToContents();
redView = new QTableView;
redView->setModel(model2);
redView->setAlternatingRowColors(true);
redView->resizeColumnsToContents();


QGroupBox *groupBox = new QGroupBox(tr("AutoRedress"));
QGroupBox *redressBox = new QGroupBox(tr("Redressen"));
QHBoxLayout *mainLayout = new QHBoxLayout;
QHBoxLayout *redressLayout = new QHBoxLayout;
QSplitter *HSplitter = new QSplitter(Qt::Vertical);
mainTab->addTab(view,tableName);
mainTab->setTabsClosable(true);

redTab->addTab(redView,tableRed);
redTab->setTabsClosable(true);
etc.......


only the redview is filled
and where comes your method here ????

spirit
13th May 2009, 12:34
QSqlTableModel also has second parameter for setting a database.

codeman
13th May 2009, 12:51
When I give the argument


model = new QSqlTableModel(mainTab,m_TrackDB);
redModel = new QSqlTableModel(redTab,m_DashDB);

it changes nothing only my second view with the second database are filled with the data from redtable.....?????!!!!!!

spirit
13th May 2009, 13:07
try this example, works fine
h


#ifndef TEST_H
#define TEST_H

#include <QWidget>

class QPushButton;

class Test: public QWidget
{
Q_OBJECT

public:
Test(QWidget *parent = 0);

private slots:
void connectToDb1();
void connectToDb2();
void showView1();
void showView2();

public:
QPushButton *m_pbConnectToDb1;
QPushButton *m_pbConnectToDb2;
QPushButton *m_pbShowView1;
QPushButton *m_pbShowView2;
};

#endif//TEST_H

cpp


#include <QtGui>
#include <QtSql>
#include "test.h"

Test::Test(QWidget *parent)
: QWidget(parent)
{
QGridLayout *gl = new QGridLayout(this);
m_pbConnectToDb1 = new QPushButton(tr("Connect to first database"));
m_pbConnectToDb2 = new QPushButton(tr("Connect to second database"));
m_pbShowView1 = new QPushButton(tr("Show first view"));
m_pbShowView2 = new QPushButton(tr("Show first view"));

m_pbShowView1->setEnabled(false);
m_pbShowView2->setEnabled(false);

gl->addWidget(m_pbConnectToDb1, 0, 0);
gl->addWidget(m_pbConnectToDb2, 0, 1);
gl->addWidget(m_pbShowView1, 1, 0);
gl->addWidget(m_pbShowView2, 1, 1);

connect(m_pbConnectToDb1, SIGNAL(clicked()), SLOT(connectToDb1()));
connect(m_pbConnectToDb2, SIGNAL(clicked()), SLOT(connectToDb2()));
connect(m_pbShowView1, SIGNAL(clicked()), SLOT(showView1()));
connect(m_pbShowView2, SIGNAL(clicked()), SLOT(showView2()));
}

void Test::connectToDb1()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", QLatin1String("db1"));
db.setDatabaseName("db1");
const bool isOpen = db.open();
m_pbConnectToDb1->setEnabled(!isOpen);
m_pbShowView1->setEnabled(isOpen);
if (!isOpen) {
QMessageBox::critical(this, tr("critical"), db.lastError().text());
return;
}
QSqlQuery query(db);
if (!db.tables().contains("table1") && !query.exec("CREATE TABLE table1 (id INT, name VARCHAR(20))")) {
QMessageBox::critical(this, tr("critical"), query.lastError().text());
return;
}

query.prepare("INSERT INTO table1 (id, name) 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()) {
QMessageBox::critical(this, tr("critical"), query.lastError().text());
return;
}
}

void Test::connectToDb2()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", QLatin1String("db2"));
db.setDatabaseName("db2");
const bool isOpen = db.open();
m_pbConnectToDb2->setEnabled(!isOpen);
m_pbShowView2->setEnabled(isOpen);
if (!isOpen) {
QMessageBox::critical(this, tr("critical"), db.lastError().text());
return;
}
QSqlQuery query(db);
if (!db.tables().contains("table2") && !query.exec("CREATE TABLE table2 (id INT, name VARCHAR(20))")) {
QMessageBox::critical(this, tr("critical"), query.lastError().text());
return;
}

query.prepare("INSERT INTO table2 (id, name) VALUES (?, ?)");

QVariantList ints;
ints << 5 << 6 << 7 << 8;
query.addBindValue(ints);

QVariantList names;
names << "Tom" << "Jim" << "John" << QVariant(QVariant::String);
query.addBindValue(names);

if (!query.execBatch()) {
QMessageBox::critical(this, tr("critical"), query.lastError().text());
return;
}
}

void Test::showView1()
{
QSqlDatabase db = QSqlDatabase::database(QLatin1String("db1"));
QSqlTableModel *model = new QSqlTableModel(0, db);
model->setTable("table1");
model->select();

QTableView *view = new QTableView();
view->setAttribute(Qt::WA_DeleteOnClose);
view->setModel(model);
view->show();
}

void Test::showView2()
{
QSqlDatabase db = QSqlDatabase::database(QLatin1String("db2"));
QSqlTableModel *model = new QSqlTableModel(0, db);
model->setTable("table2");
model->select();

QTableView *view = new QTableView();
view->setAttribute(Qt::WA_DeleteOnClose);
view->setModel(model);
view->show();
}

Lykurg
13th May 2009, 13:43
When I give the argument


model = new QSqlTableModel(mainTab,m_TrackDB);
redModel = new QSqlTableModel(redTab,m_DashDB);

it changes nothing only my second view with the second database are filled with the data from redtable.....?????!!!!!!
have you opened m_TrackDB? maybe there was an error while opening this database.

codeman
13th May 2009, 14:00
I think yes see the third post there I create two databases and I open them. But I think my problem is when I create or add the second database it ovverrides the first but my databasename are the connectionsstrings and when I add the databse like this



m_TrackDB = QSqlDatabase::addDatabase(driver,"db1");
m_TrackDB.setDatabaseName(m_qstrConTrackDB);



it don´t connect???? It don´t accepts the second argument from addDatabase!

spirit
13th May 2009, 14:03
did you try my exmaple? did it work? why don't you do in the same way? :confused:

codeman
13th May 2009, 14:05
Cause I have to understand it thatswhy I try to find the problem in my code, but thanx a lot I will try it asap

codeman
13th May 2009, 15:33
Ahhhhh now I am seeing the wood between the trees. ;o))

First I have to add the databases with the connectionnames and after that I have to use it like this



QSqlDatabase usedDB2 = QSqlDatabase::database("second") ;
usedDB2.open();
redModel = new QSqlTableModel(redTab,usedDB2);


Thanx a lot lot lot lot now I understand it ;o))