Switching between two databases

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.???

use this method QSqlDatabase::database.

hmm

Ok I connect my databses like this

Code:

---

QString driver= "QODBC";
    m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Database=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 ???

Code:

---

m_TrackDB::database(m_qstrConTrackDB,true);

---

thenn fill up the first view
and then

Code:

---

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

---

then fill up the second view

Is that right??

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

Code:

---

QString driver= "QODBC";
    m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Database=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
...

---

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

from docs

Quote:

---

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

---

that means, that third argument is bool. ;)

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

Code:

---

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

---

my DB don´t connects?????

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

I don´t understand it ;o(( hmmm

Why I have to ovveride my QSqldatabse m_TrackDB with

Code:

---

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

---

after I make this

Code:

---

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

---

both methods returns a QSqlDatabase object....

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.

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:

Code:

---

::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...
}

---

I missed to post this. :)

Ok when I make it like this

Code:

---

QString driver= "QODBC";
    m_qstrConTrackDB="DRIVER={SQL Server};Server=server1;Trusted_Connection=no;Database=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;Database=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 ????

QSqlTableModel also has second parameter for setting a database.

When I give the argument

Code:

---

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.....?????!!!!!!

try this example, works fine
h

Code:

---

#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

Code:

---

#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();
}

---

Quote:

---

Originally Posted by codeman

When I give the argument

Code:

---

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.

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

Code:

---

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

---

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

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

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