From: Shamus Hammons Date: Wed, 3 Oct 2012 14:38:31 +0000 (-0500) Subject: Switched to ODBC driver for connections to MySQL database X-Git-Url: http://shamusworld.gotdns.org/cgi-bin/gitweb.cgi?p=schematic;a=commitdiff_plain;h=3d155f8bff56495df9ae70aa75f3a43ce5d83478 Switched to ODBC driver for connections to MySQL database I switched the driver from MySQL to ODBC because MXE doesn't support the MySQL driver in the QtSql module. We would have had to change this anyway, as we'll need to support connections to the ExactMax database. --- diff --git a/src/mainwin.cpp b/src/mainwin.cpp index fe7931f..c360a4e 100644 --- a/src/mainwin.cpp +++ b/src/mainwin.cpp @@ -106,22 +106,31 @@ MainWindow::MainWindow(): aboutWin(new AboutWindow(this)), // Finally, set up database connection - db = QSqlDatabase::addDatabase("QMYSQL"); +// db = QSqlDatabase::addDatabase("QMYSQL"); + db = QSqlDatabase::addDatabase("QODBC"); bool ok = false; // Prime the SQL Settings dialog (in case we need it) SQLSettingsDialog sqlSettings; - sqlSettings.edit1->setText(dbHostName); - sqlSettings.edit2->setText(dbName); - sqlSettings.edit3->setText(dbUserName); - sqlSettings.edit4->setText(dbPassword); - + sqlSettings.edit1->setText(dbDriver); + sqlSettings.edit2->setText(dbHostName); + sqlSettings.edit3->setText(dbName); + sqlSettings.edit4->setText(dbUserName); + sqlSettings.edit5->setText(dbPassword); + +#if 0 +db = QSqlDatabase::addDatabase("QODBC"); +db.setDatabaseName("Driver={MySQL ODBC 5.1 Driver};DATABASE=agp-dbserver01;"); +db.setUserName("xcdr"); +db.setPassword("xcdr"); +#endif do { // Set up the DB connection with saved settings + QString odbc = QString("DRIVER={%1};DATABASE=%2;").arg(dbDriver).arg(dbName); db.setHostName(dbHostName); - db.setDatabaseName(dbName); + db.setDatabaseName(odbc); db.setUserName(dbUserName); db.setPassword(dbPassword); ok = db.open(); @@ -136,10 +145,11 @@ MainWindow::MainWindow(): aboutWin(new AboutWindow(this)), { // User thinks this will work (hit OK button), so prime the variables // for the next attempt - dbHostName = sqlSettings.edit1->text(); - dbName = sqlSettings.edit2->text(); - dbUserName = sqlSettings.edit3->text(); - dbPassword = sqlSettings.edit4->text(); + dbDriver = sqlSettings.edit1->text(); + dbHostName = sqlSettings.edit2->text(); + dbName = sqlSettings.edit3->text(); + dbUserName = sqlSettings.edit4->text(); + dbPassword = sqlSettings.edit5->text(); } else return; // User cancelled the dialog, so quit @@ -147,6 +157,14 @@ MainWindow::MainWindow(): aboutWin(new AboutWindow(this)), } while (!ok); + // Check to see how many users are in the system; if less than 2, we don't do a login + QSqlQuery query; + query.prepare("SELECT COUNT(*) FROM User"); + query.exec(); + query.next(); + + if (query.value(0).toInt() > 1) + { // Do Login dialog LoginDialog loginDlg; bool done = false; @@ -160,11 +178,20 @@ MainWindow::MainWindow(): aboutWin(new AboutWindow(this)), done = true; else { +#if 0 // Search DB for this username/login pair QSqlQuery query("SELECT UID, name, login FROM User WHERE Login=? AND Password=?"); query.addBindValue(loginDlg.edit1->text()); query.addBindValue(loginDlg.edit2->text()); query.exec(); +#else + // Search DB for this username/login pair + QSqlQuery query; + query.prepare("SELECT UID, name, login FROM User WHERE Login=? AND Password=?"); + query.addBindValue(loginDlg.edit1->text()); + query.addBindValue(loginDlg.edit2->text()); + query.exec(); +#endif while (query.next()) { @@ -177,6 +204,13 @@ MainWindow::MainWindow(): aboutWin(new AboutWindow(this)), } } while (!done); + } + else + { + fullName = "Administrator"; + login = "admin"; + loggedInUID = 1; + } QString s = QString("User: %1 (%2)").arg(fullName).arg(login); scmWidget->username->setText(s); @@ -230,7 +264,8 @@ void MainWindow::HandleNewVendorDialog(void) // Presumably, the user has given us good data, so we try to populate the // database with this new vendor data. - QSqlQuery query1("INSERT INTO VALUES (?, ?, ?)"); + QSqlQuery query; + query.prepare("INSERT INTO VALUES (?, ?, ?)"); } @@ -238,7 +273,8 @@ void MainWindow::HandleNewVendorDialog(void) void MainWindow::FillVendorLevelCombo(QComboBox * combo) { - QSqlQuery query("SELECT VLID, Description FROM VendorLevel"); + QSqlQuery query; + query.prepare("SELECT VLID, Description FROM VendorLevel"); query.exec(); while (query.next()) @@ -253,7 +289,8 @@ void MainWindow::FillVendorLevelCombo(QComboBox * combo) void MainWindow::FillContactTypeCombo(QComboBox * combo) { - QSqlQuery query("SELECT CTID, Description FROM ContactType"); + QSqlQuery query; + query.prepare("SELECT CTID, Description FROM ContactType"); query.exec(); while (query.next()) @@ -271,7 +308,8 @@ void MainWindow::FillVendorClassList(QListWidget * list) std::vector groupList; // Pull in definitions from DB for Vendor Classes/Groups - QSqlQuery query1("SELECT vgid, description FROM VendorGroup ORDER BY seqNo"); + QSqlQuery query1; + query1.prepare("SELECT vgid, description FROM VendorGroup ORDER BY seqNo"); query1.exec(); while (query1.next()) @@ -282,18 +320,19 @@ void MainWindow::FillVendorClassList(QListWidget * list) groupList.push_back(v); } - QSqlQuery query2("SELECT vtid, vgid, description FROM VendorType ORDER BY seqNo"); - query2.exec(); +// QSqlQuery query2; + query1.prepare("SELECT vtid, vgid, description FROM VendorType ORDER BY seqNo"); + query1.exec(); int previousID = -1, groupListIndex = 0; QListWidgetItem * item; - while (query2.next()) + while (query1.next()) { // VendorType v; - int vtid = query2.value(0).toInt(); - int vgid = query2.value(1).toInt(); - QString description = query2.value(2).toString(); + int vtid = query1.value(0).toInt(); + int vgid = query1.value(1).toInt(); + QString description = query1.value(2).toString(); // Check to see if we need to insert new header yet. // If we're not still in same group, push the next group header into the list @@ -327,6 +366,7 @@ void MainWindow::ReadSettings(void) // ((TTEdit *)qApp)->charWnd->resize(size); // ((TTEdit *)qApp)->charWnd->move(pos); + dbDriver = settings.value("dbDriver", "myodbc-5.1").toString(); dbHostName = settings.value("dbHostName", "localhost").toString(); dbName = settings.value("dbName", "schematic").toString(); dbUserName = settings.value("dbUserName", "scm_user").toString(); @@ -342,6 +382,7 @@ void MainWindow::WriteSettings(void) // settings.setValue("charWndPos", ((TTEdit *)qApp)->charWnd->pos()); // settings.setValue("charWndSize", ((TTEdit *)qApp)->charWnd->size()); + settings.setValue("dbDriver", dbDriver); settings.setValue("dbHostName", dbHostName); settings.setValue("dbName", dbName); settings.setValue("dbUserName", dbUserName); diff --git a/src/mainwin.h b/src/mainwin.h index c277591..80c646f 100644 --- a/src/mainwin.h +++ b/src/mainwin.h @@ -48,6 +48,7 @@ class MainWindow: public QMainWindow QAction * vendorClassAct; QAction * newVendorAct; + QString dbDriver; QString dbHostName; QString dbName; QString dbUserName; diff --git a/src/sqlsettingsdialog.cpp b/src/sqlsettingsdialog.cpp index 30b1a53..a728983 100644 --- a/src/sqlsettingsdialog.cpp +++ b/src/sqlsettingsdialog.cpp @@ -18,7 +18,8 @@ SQLSettingsDialog::SQLSettingsDialog(QWidget * parent/*= 0*/): QDialog(parent), edit1(new QLineEdit()), edit2(new QLineEdit()), edit3(new QLineEdit()), - edit4(new QLineEdit()) + edit4(new QLineEdit()), + edit5(new QLineEdit()) { connect(buttonBox, SIGNAL(accepted()), this, SLOT(accept())); connect(buttonBox, SIGNAL(rejected()), this, SLOT(reject())); @@ -26,19 +27,22 @@ SQLSettingsDialog::SQLSettingsDialog(QWidget * parent/*= 0*/): QDialog(parent), edit4->setEchoMode(QLineEdit::Password); QFormLayout * formLayout = new QFormLayout; - formLayout->addRow(tr("&MySQL Server Hostname:"), edit1); - formLayout->addRow(tr("&Database Name:"), edit2); - formLayout->addRow(tr("&Database Username:"), edit3); - formLayout->addRow(tr("&Database Password:"), edit4); + formLayout->addRow(tr("&Database Driver:"), edit1); + formLayout->addRow(tr("&Database Server Hostname:"), edit2); + formLayout->addRow(tr("&Database Name:"), edit3); + formLayout->addRow(tr("&Database Username:"), edit4); + formLayout->addRow(tr("&Database Password:"), edit5); QVBoxLayout * mainLayout = new QVBoxLayout; mainLayout->addLayout(formLayout); mainLayout->addWidget(buttonBox); setLayout(mainLayout); - setWindowTitle(tr("MySQL Server Connection")); + setWindowTitle(tr("SQL Server Connection")); } + SQLSettingsDialog::~SQLSettingsDialog() { } + diff --git a/src/sqlsettingsdialog.h b/src/sqlsettingsdialog.h index e1a9c8c..e72a1ee 100644 --- a/src/sqlsettingsdialog.h +++ b/src/sqlsettingsdialog.h @@ -21,6 +21,7 @@ class SQLSettingsDialog: public QDialog QLineEdit * edit2; QLineEdit * edit3; QLineEdit * edit4; + QLineEdit * edit5; }; #endif // __SQLSETTINGSDIALOG_H__ diff --git a/src/vendorclassdialog.cpp b/src/vendorclassdialog.cpp index d39a5a0..ba670b9 100644 --- a/src/vendorclassdialog.cpp +++ b/src/vendorclassdialog.cpp @@ -156,32 +156,33 @@ void VendorClassDialog::LoadList(void) std::vector groupList; // Pull in definitions from DB for Vendor Classes/Groups - QSqlQuery query1("SELECT vgid, seqNo, description FROM VendorGroup ORDER BY seqNo"); - query1.exec(); + QSqlQuery query; + query.prepare("SELECT vgid, seqNo, description FROM VendorGroup ORDER BY seqNo"); + query.exec(); - while (query1.next()) + while (query.next()) { VendorType v; - v.key = query1.value(0).toInt(); - v.seqNo = query1.value(1).toInt(); - v.description = query1.value(2).toString(); + v.key = query.value(0).toInt(); + v.seqNo = query.value(1).toInt(); + v.description = query.value(2).toString(); v.isHeader = true; groupList.push_back(v); } - QSqlQuery query2("SELECT vtid, vgid, seqNo, description FROM VendorType ORDER BY seqNo"); - query2.exec(); + query.prepare("SELECT vtid, vgid, seqNo, description FROM VendorType ORDER BY seqNo"); + query.exec(); int previousID = -1, groupListIndex = 0; - while (query2.next()) + while (query.next()) { VendorType v; - v.key = query2.value(0).toInt(); - int vgid = query2.value(1).toInt(); - v.seqNo = query2.value(2).toInt(); - v.description = query2.value(3).toString(); + v.key = query.value(0).toInt(); + int vgid = query.value(1).toInt(); + v.seqNo = query.value(2).toInt(); + v.description = query.value(3).toString(); v.isHeader = false; // Check to see if we need to insert new header yet. diff --git a/src/vendorlevelwidget.cpp b/src/vendorlevelwidget.cpp index fde3ab1..f91b43b 100644 --- a/src/vendorlevelwidget.cpp +++ b/src/vendorlevelwidget.cpp @@ -49,7 +49,8 @@ VendorLevelWidget::VendorLevelWidget(QWidget * parent/*= 0*/): QWidget(parent), void VendorLevelWidget::DoQuery(int key) { - QSqlQuery query("SELECT vendorUsable, color, description FROM VendorLevel WHERE VLID=?"); + QSqlQuery query; + query.prepare("SELECT vendorUsable, color, description FROM VendorLevel WHERE VLID=?"); query.addBindValue(key); query.exec();