[Qt-interest] postgres bytea

[Andrew Maclean]

I agree that it would be nice if QPSQL driver supported this
transparently - when warnings like this begin to appear it may mean
that this method of escaping may become deprecated.


Andrew

On Wed, May 6, 2009 at 1:33 AM, Diego Schulz <dschulz at gmail.com> wrote:
> 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
>
> _______________________________________________
> Qt-interest mailing list
> Qt-interest at trolltech.com
> http://lists.trolltech.com/mailman/listinfo/qt-interest
>



-- 
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney  2006  NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________