Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite)
Forum Updated to NodeBB v4.3 + New Features

QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite)

Scheduled Pinned Locked Moved Solved General and Desktop
21 Posts 6 Posters 2.5k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • SGaistS Offline
    SGaistS Offline
    SGaist
    Lifetime Qt Champion
    wrote on last edited by
    #8

    Hi,

    Print the query that was generated to see if there's something wrong there.

    Interested in AI ? www.idiap.ch
    Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

    H 1 Reply Last reply
    0
    • SGaistS SGaist

      Hi,

      Print the query that was generated to see if there's something wrong there.

      H Offline
      H Offline
      hbatalha
      wrote on last edited by
      #9

      @SGaist

      QSqlQuery query;
          query.prepare("DELETE FROM " + table + " WHERE " + columnName + "= ?");
          query.addBindValue(valueToCompareAgainst);
          query.exec();
      
         qDebug() << query.lastQuery(); // returns "DELETE FROM tableName WHERE columnName= ?"
      
          qDebug() << (valueToCompareAgainst ==  query.boundValue(0).toString()); // returns true
      

      The query generated seems to be ok.

      1 Reply Last reply
      0
      • SGaistS Offline
        SGaistS Offline
        SGaist
        Lifetime Qt Champion
        wrote on last edited by
        #10

        Can you show it anyway ? Looking OK and being OK can be subtly different.

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        H 1 Reply Last reply
        0
        • H hbatalha

          @JonB

          Use the returned column from this SELECT statement to compare against the valueToCompareAgainst and/or the original key

          like this?

              QSqlQuery query;
              query.prepare("DELETE FROM " + table + " WRERE " + columnName + "= ?");
              query.addBindValue(valueToCompareAgainst);
              query.exec();
          
              qDebug() << (valueToCompareAgainst ==  query.boundValue(0).toString()); // returns true
          

          It returns true when comaparing them. But I can't pass it to DELETE, can't figure out how.

          In your INSERT statement you do not actually exec() anything. So if that is your real code you never put the item into the table. Which might explain why you can't delete it.....

          No it is not my real code. I forgot about it when type that minimal example.

          JonBJ Online
          JonBJ Online
          JonB
          wrote on last edited by JonB
          #11

          @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

          @JonB

          Use the returned column from this SELECT statement to compare against the valueToCompareAgainst and/or the original key

          like this?

          No,

          SELECT [columnname] FROM [table] WHERE [columnname] = '<value>`
          

          If that doesn't return the row it's not surprising it can't be deleted.

          SELECT [columnname] FROM [table]
          

          Compare the column value you get back for the row you want to delete against what you are passing for its value to DELETE FROM [table] WHERE [columnname] = '<value>'.

          All just trying to help you to debug.

          H 1 Reply Last reply
          0
          • SGaistS SGaist

            Can you show it anyway ? Looking OK and being OK can be subtly different.

            H Offline
            H Offline
            hbatalha
            wrote on last edited by
            #12

            @SGaist I thought that is the way to do it. So how do I print the query that was generated ?

            SGaistS 1 Reply Last reply
            0
            • JonBJ JonB

              @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

              @JonB

              Use the returned column from this SELECT statement to compare against the valueToCompareAgainst and/or the original key

              like this?

              No,

              SELECT [columnname] FROM [table] WHERE [columnname] = '<value>`
              

              If that doesn't return the row it's not surprising it can't be deleted.

              SELECT [columnname] FROM [table]
              

              Compare the column value you get back for the row you want to delete against what you are passing for its value to DELETE FROM [table] WHERE [columnname] = '<value>'.

              All just trying to help you to debug.

              H Offline
              H Offline
              hbatalha
              wrote on last edited by hbatalha
              #13

              @JonB What about now?

              QSqlQuery query;
                  if(! query.exec("SELECT * FROM " + table + " WHERE " + columnName + "= '" + valueToCompareAgainst + "'"))
                  {
                      qDebug() << "SQL QUERY ERROR: " + table + " : " + query.lastError().text();
                  }
              
                  while(query.next())
                      qDebug() << "RETURNED: " << query.value(columnName).toString();
              

              This does not return anything. Might the error be here? What should I do?

              All just trying to help you to debug.

              I really appreciate it

              1 Reply Last reply
              0
              • H hbatalha

                @SGaist I thought that is the way to do it. So how do I print the query that was generated ?

                SGaistS Offline
                SGaistS Offline
                SGaist
                Lifetime Qt Champion
                wrote on last edited by
                #14

                @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                @SGaist I thought that is the way to do it. So how do I print the query that was generated ?

                My bad ! I misread the code part.

                One thing that might be wrong is the missing space between the column name and the question mark.

                By the way, which version of Qt are you using ?

                Interested in AI ? www.idiap.ch
                Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                H 1 Reply Last reply
                0
                • SGaistS SGaist

                  @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                  @SGaist I thought that is the way to do it. So how do I print the query that was generated ?

                  My bad ! I misread the code part.

                  One thing that might be wrong is the missing space between the column name and the question mark.

                  By the way, which version of Qt are you using ?

                  H Offline
                  H Offline
                  hbatalha
                  wrote on last edited by
                  #15

                  @SGaist

                  One thing that might be wrong is the missing space between the column name and the question mark.

                  Where? You mean the space between columnName and the '=' sign.

                  By the way, which version of Qt are you using ?

                  Qt Creator IDE version? If so, it is Qt 4.15.1

                  jsulmJ 1 Reply Last reply
                  0
                  • H hbatalha

                    @SGaist

                    One thing that might be wrong is the missing space between the column name and the question mark.

                    Where? You mean the space between columnName and the '=' sign.

                    By the way, which version of Qt are you using ?

                    Qt Creator IDE version? If so, it is Qt 4.15.1

                    jsulmJ Offline
                    jsulmJ Offline
                    jsulm
                    Lifetime Qt Champion
                    wrote on last edited by
                    #16

                    @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                    Qt Creator IDE version? If so, it is Qt 4.15.1

                    No, Qt version.
                    QtCreator is an IDE, Qt is a C++ framework.
                    What you posted is the QtCreator version, question was which Qt version you're using.

                    https://forum.qt.io/topic/113070/qt-code-of-conduct

                    H 1 Reply Last reply
                    0
                    • jsulmJ jsulm

                      @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                      Qt Creator IDE version? If so, it is Qt 4.15.1

                      No, Qt version.
                      QtCreator is an IDE, Qt is a C++ framework.
                      What you posted is the QtCreator version, question was which Qt version you're using.

                      H Offline
                      H Offline
                      hbatalha
                      wrote on last edited by
                      #17

                      @jsulm @SGaist Then it is Qt 6.0.3

                      1 Reply Last reply
                      0
                      • Christian EhrlicherC Online
                        Christian EhrlicherC Online
                        Christian Ehrlicher
                        Lifetime Qt Champion
                        wrote on last edited by
                        #18

                        What qsql driver do you use? Can you reproduce it with a in-memory sqlite database so we can try to check it on our systems?

                        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                        Visit the Qt Academy at https://academy.qt.io/catalog

                        H 1 Reply Last reply
                        0
                        • Christian EhrlicherC Christian Ehrlicher

                          What qsql driver do you use? Can you reproduce it with a in-memory sqlite database so we can try to check it on our systems?

                          H Offline
                          H Offline
                          hbatalha
                          wrote on last edited by
                          #19

                          @Christian-Ehrlicher said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                          an you reproduce it with a in-memory sqlite database so we can try to check it on our systems?

                          Sorry, I don't know how to do that yet. I am a SQL newbie, just started learning it few weeks ago in college. I believe you mean something like this.

                          What qsql driver do you use?

                          QSQLITE

                          1 Reply Last reply
                          0
                          • Christian EhrlicherC Online
                            Christian EhrlicherC Online
                            Christian Ehrlicher
                            Lifetime Qt Champion
                            wrote on last edited by
                            #20

                            @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                            Sorry, I don't know how to do that yet. I am a SQL newbie, just started learning it few weeks ago in college

                            Don't see why this hinders you from creating a minimal, compilable reproducer for us.

                            Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                            Visit the Qt Academy at https://academy.qt.io/catalog

                            H 1 Reply Last reply
                            0
                            • Christian EhrlicherC Christian Ehrlicher

                              @hbatalha said in QSqlQuery query statement limit? Can't delete from table when the query statement is very long (Sqlite):

                              Sorry, I don't know how to do that yet. I am a SQL newbie, just started learning it few weeks ago in college

                              Don't see why this hinders you from creating a minimal, compilable reproducer for us.

                              H Offline
                              H Offline
                              hbatalha
                              wrote on last edited by hbatalha
                              #21

                              @Christian-Ehrlicher @jsulm @JonB @SGaist @KroMignon

                              I think I solved the problem! When I was inserting that obscured CHAR, the type I was using was QByteArray and when trying to delete the type was a QString. Even though they contained the same characters something I don't know what yet was stopping it from getting deleted.

                              .h

                              #ifndef MAINWINDOW_H
                              #define MAINWINDOW_H
                              
                              #include <QMainWindow>
                              #include <QtSql>
                              
                              QT_BEGIN_NAMESPACE
                              namespace Ui {
                              class MainWindow;
                              }
                              QT_END_NAMESPACE
                              
                              class MainWindow : public QMainWindow
                              {
                                  Q_OBJECT
                              
                              public:
                                  MainWindow(QWidget *parent = nullptr);
                                  ~MainWindow();
                              
                              private slots:
                                  void on_add_clicked();
                              
                                  void on_delete_2_clicked();
                              
                              private:
                                  Ui::MainWindow *ui;
                              
                                  QSqlDatabase d_base;
                              };
                              #endif // MAINWINDOW_H
                              
                              

                              .cpp

                              MainWindow::MainWindow(QWidget *parent)
                                  : QMainWindow(parent)
                                  , ui(new Ui::MainWindow)
                              {
                                  ui->setupUi(this);
                              
                                  d_base = QSqlDatabase::addDatabase("QSQLITE");
                                  d_base.setDatabaseName("database.sqlite");
                              
                                  if(!d_base.open())
                                  {
                                      qDebug() << "Database failed to open : " + d_base.lastError().text();
                                  }
                              
                                  QSqlQuery query;
                              
                                  bool success = false;
                              
                                  success = query.exec("CREATE TABLE IF NOT EXISTS mytable("
                                                       "attribute CHAR NOT NULL PRIMARY KEY)");
                              
                                  if(! success)
                                  {
                                      qDebug() << "SQL QUERY ERROR: " <<  query.lastError().text();
                                  }
                              }
                              
                              MainWindow::~MainWindow()
                              {
                                  delete ui;
                              }
                              
                              
                              void MainWindow::on_add_clicked()
                              {
                                  if( ! ui->lineEdit->text().isEmpty())
                                  {
                                      QSqlQuery query;
                              
                                      query.prepare("INSERT INTO mytable (attribute) VALUES(:attribute)");
                              
                                      QByteArray obscuredStr = ui->lineEdit->text().toUtf8();
                                      obscuredStr = obscuredStr.toBase64();
                                      QString str = obscuredStr;
                                      qDebug() << str;
                                      query.bindValue(":attribute", str);
                              
                                      if( ! query.exec())
                                      {
                                          qDebug() << "SQL QUERY ERROR: " + query.lastError().text();
                                      }
                                  }
                              }
                              
                              
                              void MainWindow::on_delete_2_clicked()
                              {
                                  QSqlQuery query;
                              
                                  QByteArray obscuredStr = ui->lineEdit->text().toUtf8();
                                  obscuredStr = obscuredStr.toBase64();
                                  qDebug() << obscuredStr;
                              
                                  QString strt = obscuredStr;
                                  qDebug() << strt;
                                  if( ! query.exec("DELETE FROM mytable WHERE attribute = '" + strt + "'" ))
                                  {
                                      qDebug() << "SQL QUERY ERROR:" <<  query.lastError().text();
                                  }
                              }
                              

                              .ui

                              <?xml version="1.0" encoding="UTF-8"?>
                              <ui version="4.0">
                               <class>MainWindow</class>
                               <widget class="QMainWindow" name="MainWindow">
                                <property name="geometry">
                                 <rect>
                                  <x>0</x>
                                  <y>0</y>
                                  <width>305</width>
                                  <height>161</height>
                                 </rect>
                                </property>
                                <property name="windowTitle">
                                 <string>MainWindow</string>
                                </property>
                                <widget class="QWidget" name="centralwidget">
                                 <widget class="QPushButton" name="add">
                                  <property name="geometry">
                                   <rect>
                                    <x>10</x>
                                    <y>70</y>
                                    <width>121</width>
                                    <height>25</height>
                                   </rect>
                                  </property>
                                  <property name="text">
                                   <string>Add to table</string>
                                  </property>
                                 </widget>
                                 <widget class="QPushButton" name="delete_2">
                                  <property name="geometry">
                                   <rect>
                                    <x>150</x>
                                    <y>70</y>
                                    <width>121</width>
                                    <height>25</height>
                                   </rect>
                                  </property>
                                  <property name="text">
                                   <string>Delete from table</string>
                                  </property>
                                 </widget>
                                 <widget class="QLineEdit" name="lineEdit">
                                  <property name="geometry">
                                   <rect>
                                    <x>10</x>
                                    <y>40</y>
                                    <width>261</width>
                                    <height>24</height>
                                   </rect>
                                  </property>
                                 </widget>
                                </widget>
                                <widget class="QMenuBar" name="menubar">
                                 <property name="geometry">
                                  <rect>
                                   <x>0</x>
                                   <y>0</y>
                                   <width>305</width>
                                   <height>25</height>
                                  </rect>
                                 </property>
                                </widget>
                                <widget class="QStatusBar" name="statusbar"/>
                               </widget>
                               <resources/>
                               <connections/>
                              </ui>
                              
                              

                              I also noticed from the example above that whenever I insert data into the table using a QByteArray object and then trying to delete the row of that data using the column that contains it nothing happens, no deletion and also and no error is thrown.

                              Thank you all for helping me debug!!

                              1 Reply Last reply
                              0

                              • Login

                              • Login or register to search.
                              • First post
                                Last post
                              0
                              • Categories
                              • Recent
                              • Tags
                              • Popular
                              • Users
                              • Groups
                              • Search
                              • Get Qt Extensions
                              • Unsolved