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
    #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 Offline
      JonBJ Offline
      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 Offline
                    Christian EhrlicherC Offline
                    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 Offline
                        Christian EhrlicherC Offline
                        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