Switching between two databases

[codeman]

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]

use this method QSqlDatabase::database.

[codeman]

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]

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]

The second argument in the method databse have to be a boolean! Or am I wrong??

[spirit]

from docs


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

that means, that third argument is bool. ;)

[codeman]

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]

sorry. yes, the first paremetr is QString, the second is bool.
I updated code above.

[codeman]

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]

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]

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]

I missed to post this. :)

[codeman]

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]

QSqlTableModel also has second parameter for setting a database.

[codeman]

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]

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]

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]

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]

did you try my exmaple? did it work? why don't you do in the same way? :confused:

[codeman]

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]

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))