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