[Qt-interest] postgres bytea

[Diego Schulz]

On Tue, May 5, 2009 at 12:02 AM, Andrew Maclean
<andrew.amaclean at gmail.com> wrote:
> Thankyou for your help Diego. It was a good example and it really helped me.
> I cannot see a way to get rid of the warnings from pg server
> regarding the use of \E'...' notation. If you look at the warning
> message at the end of my test this seems to be a QT thing.
>
>
> So if anyone knows a way to get rid of this warning, please let us know.
>
>
> I have managed to get a solution as follows:
>
> The table is defined by:
> CREATE TABLE resource_store
> (
>  content_id serial NOT NULL,
>  description text,
>  "content" bytea,
>  CONSTRAINT content_id_rs_pk PRIMARY KEY (content_id)
> );
>
> To insert a bytea value into the table:
>
>      QSqlDatabase db = QSqlDatabase::database();
>      bool blob = db.driver()->hasFeature(QSqlDriver::BLOB);
>      bool unicode = db.driver()->hasFeature(QSqlDriver::Unicode);
>      if ( db.open() && blob && unicode )
>      {
>        QApplication::setOverrideCursor(Qt::BusyCursor);
>        // read in the data
>        QFile f(fileName);
>        if(f.open(QIODevice::ReadOnly)){
>          QVariant binaryData = f.readAll();
>
>          QString s = "insert into ";
>          s += table;
>          s += "(description, content) "
>            "VALUES ( :description, :content )";
>          QSqlQuery query;
>          query.prepare(s);
>          query.bindValue(":description", description);
>          query.bindValue(":content", binaryData);
>
>          QSqlDatabase::database().transaction();
>          if (!query.exec())
>          {
>              QApplication::restoreOverrideCursor();
>              QMessageBox msgBox;
>              msgBox.setText("SQL command failed\n" + query.lastError().text());
>              msgBox.exec();
>          }
>          QSqlDatabase::database().commit();
>        }
>        QApplication::restoreOverrideCursor();
>     }
>
>
> To get a bytea value from the table;
>
>
>      QSqlDatabase db = QSqlDatabase::database();
>      if ( db.open() )
>      {
>        QApplication::setOverrideCursor(Qt::BusyCursor);
>        // Saving to a file called path/name
>        path += "/";
>        path += name;
>
>        QString s = "select";
>        s += " description, content from ";
>        s += table;
>        s += " where description = :description";
>        QSqlQuery query;
>        query.prepare(s);
>        query.bindValue(":description", description);
>        QSqlDatabase::database().transaction();
>        if (!query.exec())
>        {
>            QApplication::restoreOverrideCursor();
>            QMessageBox msgBox;
>            msgBox.setText("SQL command failed\n" + query.lastError().text());
>            msgBox.exec();
>        }
>        QSqlDatabase::database().commit();
>        if (query.next())
>        {
>          QFile f(path);
>          if(f.open(QIODevice::WriteOnly))
>          {
>            QVariant binData = query.value(1);
>            f.write(binData.toByteArray());
>          }
>        }
>        QApplication::restoreOverrideCursor();
>      }
>
>
>  However there is one remaining issue, relating to inserting the bytea
> value. When the above code runs I get this message:
>
> WARNING:  nonstandard use of \\ in a string literal
> LINE 1: EXECUTE qpsqlpstmt_1 ('zzz', '\\377\\330\\377\\340\\000\\020...
>                                     ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
>

This warning is exactly what I was talking about :)
Note the HINT suggesting the use of   E'\\..'  notation (postgresql specific).
I think it would be nice if QPSQL driver supported this transparently.

The only way to get rid of the warning that I'm aware of is inserting
base64 encoded blobs, actually inserting a long ASCII string instead
of binary data.

regards,


diego