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 and SQL text field escape sequences
Forum Updated to NodeBB v4.3 + New Features

QSqlQuery and SQL text field escape sequences

Scheduled Pinned Locked Moved Solved General and Desktop
5 Posts 3 Posters 3.7k Views 1 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.
  • K Offline
    K Offline
    KroMignon
    wrote on 11 Apr 2017, 10:58 last edited by
    #1

    Hi all,

    I need to create "partial" SQL requests which only contains the "value" part.
    I some of those values there are text fields.
    I known, QSqlQuery can handle this very nicely using prepare() and bindValue().
    But is there a way to get the generated SQL query?

    I just need the SQL query string, not to execute the query.
    How can I achieve this?

    Best regards

    It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

    J 1 Reply Last reply 11 Apr 2017, 11:03
    0
    • V VRonin
      11 Apr 2017, 12:39

      from qsqlresult.cpp

      QString query = lastQuery();
      if (d->binds == NamedBinding) {
              int i;
              QVariant val;
              QString holder;
              for (i = d->holders.count() - 1; i >= 0; --i) {
                  holder = d->holders.at(i).holderName;
                  val = d->values.value(d->indexes.value(holder).value(0,-1));
                  QSqlField f(QLatin1String(""), val.type());
                  f.setValue(val);
                  query = query.replace(d->holders.at(i).holderPos,
                                         holder.length(), driver()->formatValue(f));
              }
          } else {
              QString val;
              int i = 0;
              int idx = 0;
              for (idx = 0; idx < d->values.count(); ++idx) {
                  i = query.indexOf(QLatin1Char('?'), i);
                  if (i == -1)
                      continue;
                  QVariant var = d->values.value(idx);
                  QSqlField f(QLatin1String(""), var.type());
                  if (var.isNull())
                      f.clear();
                  else
                      f.setValue(var);
                  val = driver()->formatValue(f);
                  query = query.replace(i, 1, driver()->formatValue(f));
                  i += val.length();
              }
          }
      

      It's not trivial but you can probably achieve it without accessing the private API either

      K Offline
      K Offline
      KroMignon
      wrote on 11 Apr 2017, 14:38 last edited by KroMignon 4 Nov 2017, 14:41
      #5

      @VRonin Thank's a lot! You save my day.

      In fact, it is easy, just setup a QSqlField, get the DB driver from QSqlDataBase, and call formatValue().

      QSqlDataBase db = QSqlDatabase::addDatabase(...)
      ...
      QSqlField f(QLatin1String(""), QVariant::String);
      f.setValue("The string to be converted");
      QString formatedValue = db->driver()->formatValue(f);
      

      It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

      1 Reply Last reply
      2
      • K KroMignon
        11 Apr 2017, 10:58

        Hi all,

        I need to create "partial" SQL requests which only contains the "value" part.
        I some of those values there are text fields.
        I known, QSqlQuery can handle this very nicely using prepare() and bindValue().
        But is there a way to get the generated SQL query?

        I just need the SQL query string, not to execute the query.
        How can I achieve this?

        Best regards

        J Offline
        J Offline
        J.Hilk
        Moderators
        wrote on 11 Apr 2017, 11:03 last edited by
        #2

        @KroMignon lmgtfy:

        ét voilà


        Be aware of the Qt Code of Conduct, when posting : https://forum.qt.io/topic/113070/qt-code-of-conduct


        Q: What's that?
        A: It's blue light.
        Q: What does it do?
        A: It turns blue.

        1 Reply Last reply
        0
        • K Offline
          K Offline
          KroMignon
          wrote on 11 Apr 2017, 11:45 last edited by KroMignon 4 Nov 2017, 11:47
          #3

          QSqlQuery::lastQuery() is not working, it returns the SQL query with the place holders, not the full query!

          It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

          1 Reply Last reply
          0
          • V Offline
            V Offline
            VRonin
            wrote on 11 Apr 2017, 12:39 last edited by VRonin 4 Nov 2017, 12:40
            #4

            from qsqlresult.cpp

            QString query = lastQuery();
            if (d->binds == NamedBinding) {
                    int i;
                    QVariant val;
                    QString holder;
                    for (i = d->holders.count() - 1; i >= 0; --i) {
                        holder = d->holders.at(i).holderName;
                        val = d->values.value(d->indexes.value(holder).value(0,-1));
                        QSqlField f(QLatin1String(""), val.type());
                        f.setValue(val);
                        query = query.replace(d->holders.at(i).holderPos,
                                               holder.length(), driver()->formatValue(f));
                    }
                } else {
                    QString val;
                    int i = 0;
                    int idx = 0;
                    for (idx = 0; idx < d->values.count(); ++idx) {
                        i = query.indexOf(QLatin1Char('?'), i);
                        if (i == -1)
                            continue;
                        QVariant var = d->values.value(idx);
                        QSqlField f(QLatin1String(""), var.type());
                        if (var.isNull())
                            f.clear();
                        else
                            f.setValue(var);
                        val = driver()->formatValue(f);
                        query = query.replace(i, 1, driver()->formatValue(f));
                        i += val.length();
                    }
                }
            

            It's not trivial but you can probably achieve it without accessing the private API either

            "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
            ~Napoleon Bonaparte

            On a crusade to banish setIndexWidget() from the holy land of Qt

            K 1 Reply Last reply 11 Apr 2017, 14:38
            2
            • V VRonin
              11 Apr 2017, 12:39

              from qsqlresult.cpp

              QString query = lastQuery();
              if (d->binds == NamedBinding) {
                      int i;
                      QVariant val;
                      QString holder;
                      for (i = d->holders.count() - 1; i >= 0; --i) {
                          holder = d->holders.at(i).holderName;
                          val = d->values.value(d->indexes.value(holder).value(0,-1));
                          QSqlField f(QLatin1String(""), val.type());
                          f.setValue(val);
                          query = query.replace(d->holders.at(i).holderPos,
                                                 holder.length(), driver()->formatValue(f));
                      }
                  } else {
                      QString val;
                      int i = 0;
                      int idx = 0;
                      for (idx = 0; idx < d->values.count(); ++idx) {
                          i = query.indexOf(QLatin1Char('?'), i);
                          if (i == -1)
                              continue;
                          QVariant var = d->values.value(idx);
                          QSqlField f(QLatin1String(""), var.type());
                          if (var.isNull())
                              f.clear();
                          else
                              f.setValue(var);
                          val = driver()->formatValue(f);
                          query = query.replace(i, 1, driver()->formatValue(f));
                          i += val.length();
                      }
                  }
              

              It's not trivial but you can probably achieve it without accessing the private API either

              K Offline
              K Offline
              KroMignon
              wrote on 11 Apr 2017, 14:38 last edited by KroMignon 4 Nov 2017, 14:41
              #5

              @VRonin Thank's a lot! You save my day.

              In fact, it is easy, just setup a QSqlField, get the DB driver from QSqlDataBase, and call formatValue().

              QSqlDataBase db = QSqlDatabase::addDatabase(...)
              ...
              QSqlField f(QLatin1String(""), QVariant::String);
              f.setValue("The string to be converted");
              QString formatedValue = db->driver()->formatValue(f);
              

              It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

              1 Reply Last reply
              2

              1/5

              11 Apr 2017, 10:58

              • Login

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