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

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

To copy to clipboard, switch view to plain text mode 

How do I use the method you metioned ???

m_TrackDB::database(m_qstrConTrackDB,true);

m_TrackDB::database(m_qstrConTrackDB,true);

To copy to clipboard, switch view to plain text mode 

thenn fill up the first view
and then

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

[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");

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

after I make this

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

[codeman]

Ok when I make it like this

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

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

To copy to clipboard, switch view to plain text mode 

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