October 14, 2011

xeroblast xeroblast
Lab Rat
91 posts

help on my database class

 

i created a simple database wrapper so i just could call my query anywhere but i have a little problem on it. it displays “QSqlDatabasePrivate::addDatabase: duplicate connection name ‘qt_sql_default_connection’, old connection removed.

myquery.h

  1. #include <QSqlDatabase>
  2. #include <QSqlQuery>
  3.  
  4. class myQuery
  5. {
  6. public:
  7.  myQuery(QString, QString, QString, QString, int);
  8.  ~myQuery();
  9.  int insertData(QString, QHash<QString, QVariant>);
  10.  bool updateData(QString, QHash<QString, QVariant>, QHash<QString, QString>);
  11.  bool deleteData(QString, QHash<QString, QString>);
  12.  
  13. protected:
  14.  QSqlQuery query;
  15. };

myquery.cpp

  1. #include <QSqlRecord>
  2. #include <QSqlError>
  3. #include <QMessageBox>
  4. #include <QVariant>
  5. #include "myquery.h"
  6.  
  7. myQuery::myQuery(QString host, QString username, QString password, QString db_name, int port)
  8. {
  9.  if (!db.isOpen()) {
  10.   db = QSqlDatabase::addDatabase("QMYSQL");
  11.   db.setHostName(host);
  12.   db.setUserName(username);
  13.   db.setPassword(password);
  14.   db.setDatabaseName(db_name);
  15.   db.setPort(port);
  16.   if (!db.open()) QMessageBox::critical(0, "Connection Problem", db.lastError().text());
  17.  }
  18. }
  19.  
  20. myQuery::~myQuery()
  21. {
  22.  QSqlDatabase::removeDatabase("QMYSQL");
  23. }
  24.  
  25. QVector<QHash<QString, QVariant> > myQuery::getData(QString sql)
  26. {
  27.  if (!query.exec(sql)) {
  28.   QMessageBox::critical(0, "Query Problem : Getting Data", query.lastError().text());
  29.  } else {
  30.   QSqlRecord rec = query.record();
  31.   int i = 0;
  32.   while (query.next()) {
  33.    QHash<QString, QVariant> singleData;
  34.    for (int j=0; j < rec.count(); j++) {
  35.     singleData.insert(rec.fieldName(j), query.value(j));
  36.    }
  37.    result.insert(i++, singleData);
  38.   }
  39.  }
  40.  query.clear();
  41.  return result;
  42. }
  43.  
  44. int myQuery::insertData(QString table, QHash<QString, QVariant> data)
  45. {
  46.  if (table.isEmpty()) {
  47.   QMessageBox::warning(0, "Query Problem : Inserting Data", "Database Table NOT Defined!");
  48.   return false;
  49.  }
  50.  if (data.size() == 0) {
  51.   QMessageBox::warning(0, "Query Problem : Inserting Data", "NO Data!");
  52.   return false;
  53.  }
  54.  QHashIterator<QString, QVariant> i(data), j(data); QString tCol, tVal;
  55.  while (i.hasNext()) {
  56.   i.next();
  57.   if (i.hasNext()) {
  58.    tCol.append(i.key() + ",");
  59.    tVal.append("?, ");
  60.   } else {
  61.    tCol.append(i.key());
  62.    tVal.append("?");
  63.   }
  64.  }
  65.  query.prepare("INSERT INTO " + table + "(" + tCol +") VALUE (" + tVal + ")");
  66.  while (j.hasNext()) {
  67.   j.next();
  68.   query.addBindValue(j.value());
  69.  }
  70.  int id = 0;
  71.  if (query.exec()) {
  72.   id = query.lastInsertId().toInt();
  73.  } else {
  74.   QMessageBox::critical(0, "Query Problem : Inserting Data", query.lastError().text());
  75.  }
  76.  query.clear();
  77.  return id;
  78. }
  79.  
  80. bool myQuery::updateData(QString table, QHash<QString, QVariant> data, QHash<QString, QString> where)
  81. {
  82.  if (table.isEmpty()) {
  83.   QMessageBox::warning(0, "Query Problem : Updating Data", "Database Table NOT Defined!");
  84.   return false;
  85.  }
  86.  if (data.size() == 0) {
  87.   QMessageBox::warning(0, "Query Problem : Updating Data", "NO Data!");
  88.   return false;
  89.  }
  90.  if (where.size() == 0) {
  91.   QMessageBox::warning(0, "Query Problem : Updating Data", "NO Specific ID!");
  92.   return false;
  93.  }
  94.  QHashIterator<QString, QVariant> i(data), k(data); QHashIterator<QString, QString> j(where); QString tSet, tWhere;
  95.  while (i.hasNext()) {
  96.   i.next();
  97.   QVariant val = i.value();
  98.   if (i.hasNext()) {
  99.    if (val.isNull()) {
  100.     tSet.append(i.key() + "=NULL, ");
  101.    } else {
  102.     tSet.append(i.key() + "=?, ");
  103.    }
  104.   } else {
  105.    if (val.isNull()) {
  106.     tSet.append(i.key() + "=NULL");
  107.    } else {
  108.     tSet.append(i.key() + "=?");
  109.    }
  110.   }
  111.  }
  112.  while (j.hasNext()) {
  113.   j.next();
  114.   if (j.hasNext()) {
  115.    tWhere.append(j.key() + "='" + j.value() + "' AND ");
  116.   } else {
  117.    tWhere.append(j.key() + "='" + j.value() + "'");
  118.   }
  119.  }
  120.  query.prepare("UPDATE " + table + " SET " + tSet + " WHERE " + tWhere);
  121.  while (k.hasNext()) {
  122.   k.next();
  123.   query.addBindValue(k.value());
  124.  }
  125.  bool result = true;
  126.  if (!query.exec()) {
  127.   QMessageBox::critical(0, "Query Problem : Updating Data", query.lastError().text());
  128.   result = false;
  129.  }
  130.  query.clear();
  131.  return result;
  132. }
  133.  
  134. bool myQuery::deleteData(QString table, QHash<QString, QString> data)
  135. {
  136.  if (table.isEmpty()) {
  137.   QMessageBox::warning(0, "Query Problem : Deleting Data", "Database Table NOT Defined!");
  138.   return false;
  139.  }
  140.  if (data.size() == 0) {
  141.   QMessageBox::warning(0, "Query Problem : Deleting Data", "NO Data!");
  142.   return false;
  143.  }
  144.  bool result = true;
  145.  while (i.hasNext()) {
  146.   i.next();
  147.   if (!query.exec("DELETE FROM " + table + " WHERE " + i.key() + "='" + i.value() + "'")) {
  148.    QMessageBox::critical(0, "Query Problem : Deleting Data", query.lastError().text());
  149.    result = false;
  150.   }
  151.  }
  152.  query.clear();
  153.  return result;
  154. }

am i doing it right in removing the connection or did i miss something.
already read the documentation but on this class, i dont know how to put it.

thanks.

6 replies

October 14, 2011

Lukas Geyer Lukas Geyer
Lab Rat
2074 posts

Well, as you manage the database connection in your class’ constructor / destructor it would have been useful to see how your myQuery class is used, not how it looks like.

Most probably you are just creating another instance of your myQuery class (explicitly or implicitly by assignment or passing as argument for example) which obivously fail as your connection is created twice.

October 14, 2011

xeroblast xeroblast
Lab Rat
91 posts

thanks for the reply. i used it like this…

  1. void Window::sampleName() {
  2.  myQuery *q = new myQuery('host', 'username', 'password', 'db_name', 3306);
  3.  QVector<QHash<QString, QVariant> > items = q->getData("SELECT * FROM table");
  4.  q->~myQuery();
  5.  ... process the _items_ ...
  6. }

October 14, 2011

fluca1978 fluca1978
Lab Rat
529 posts

From the code you posted it does not seem the query is executed twice, but maybe the sampleName method is called more than once? I suggest placing the database connection configuration out of the constructor. Moreover do not call the destructor directly, delete the object if you don’t need it anymore.

October 14, 2011

Lukas Geyer Lukas Geyer
Lab Rat
2074 posts

You might call an objects destructor explicitly when placement new was used – otherwise just don’t do it. Never.

In addition, once a QSqlDatabase connection is created it is accessible from everywhere. Every QtSql operation will use the default database connection (if no other connection is specified). The usual workflow is

  1. int main(int argc, char* argv[])
  2. {
  3.     QApplication application(argc, argv);
  4.     QSqlDatabase database(...);
  5.     if(database.open())
  6.     {
  7.         MainWindow mainWindow;
  8.  
  9.         mainWindow.show();
  10.         ...

This prevents the connection from beeing opened twice.

It is quite easy to spot your error. Just place a breakpoint in the constructor and destructor of myClass and take a look at the order they are called. The stack trace will reveal who is calling the constructor or destructor out of order.

October 15, 2011

xeroblast xeroblast
Lab Rat
91 posts

fluca1978 : the sampleName method doesnt called more than once and if ever it is called, the destructor should do the trick but it wasnt. and if i delete the object itself, it wont compile.

Lukas Geyer : thanks for the suggestion but i prefer connecting it when needed. that is why i need to close the connection. and i did put breakpoints and the removeDatabase() is not truly working so when i instantiate the object again, the annoying problem appear.

October 15, 2011

Lukas Geyer Lukas Geyer
Lab Rat
2074 posts

Well, I have taken another look at your code and you should take another look at QSqlDatabase::addDatabase() and QSqlDatabase::removeDatabase(). The former one takes the database type as parameter whereas the latter one takes a connection name as parameter.

In addition, there is no need to use addDatabase() / removeDatabase() if you just want to connect / disconnect. Use open() / close() instead. Keep in mind that all your queries have to be gone out of scope before closing the database connection.

 
  ‹‹ manager->get(....) is going into trans state and not coming back.      painting on qimage or qpixmap taking time in real time plotting ››

You must log in to post a reply. Not a member yet? Register here!