[Qt-interest] postgres bytea
Andrew Maclean
andrew.amaclean at gmail.com
Tue May 5 06:02:45 CEST 2009
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'\\'.
-----Original Message-----
From: qt-interest-bounces at trolltech.com
[mailto:qt-interest-bounces at trolltech.com] On Behalf Of Diego Schulz
Sent: Monday, 4 May 2009 23:15
To: qt-interest at trolltech.com
Subject: Re: [Qt-interest] postgres bytea
On Mon, May 4, 2009 at 3:00 AM, Andrew Maclean <a.maclean at cas.edu.au> wrote:
> Has anyone a simple example of how to insert/get data from a bytea field in
> a postgres database. I am using QT4.5 and the documentation talks about
> automatic conversions but I seem to be unable to do this.
>
> I build and installed the postgresql plugin Ok and it returns true for bytea
> and also Unicode.
>
>
>
>
>
> Thanks for any help
>
>
>
>
>
> Andrew
>
>
Hi Andrew,
I've done a small proof of concept a few days ago. Beware that the
code might not be strictly correct, but may give you some ideas. This
is the function:
void
MainWindow::postBlob ()
{
if (connectDatabase ())
{
qDebug ("Connected!");
QSqlDatabase db = QSqlDatabase::database (tr ("default"));
{
QFile file (editInputFile->text ());
if (!file.open (QIODevice::ReadOnly))
{
return;
}
QSqlQuery query = QSqlQuery (db);
QByteArray ba = file.readAll ();
qlonglong len = ba.length ();
QByteArray sha1 =
QCryptographicHash::hash (ba, QCryptographicHash::Sha1);
QVariant sha1blob (sha1.toHex ());
QVariant blob (ba.toBase64 ());
// Using named parameters
/*
query.prepare("INSERT INTO archivo (nombre, rutaorig, descripcion,
tam , hash , blob) "
"VALUES ( :nombre, :rutaorig , :descripcion, :tam , :hash,
E\'\\\\:blob'\' ) ");
query.bindValue(":nombre", QFileInfo(file).fileName());
query.bindValue(":rutaorig", QFileInfo(file).absolutePath() +
QDir::separator());
query.bindValue(":descripcion", editDescription->text() );
query.bindValue(":tam", len);
query.bindValue(":hash", sha1blob.toString());
query.bindValue(":blob", blob );
*/
// Using positional parameters
query.
prepare
("INSERT INTO archivo (nombre, rutaorig, descripcion, tam, hash, blob) "
"VALUES (?,?,?,?,?,?)");
query.addBindValue (QFileInfo (file).fileName ());
query.addBindValue (QFileInfo (file).absolutePath () +
QDir::separator ());
query.addBindValue (editDescription->text ());
query.addBindValue (len);
query.addBindValue (sha1blob.toString ());
query.addBindValue (blob);
query.exec ();
qDebug () << query.lastQuery ();
QSqlError error = query.lastError ();
if (error.type () == QSqlError::NoError)
{
createModel ();
//QMessageBox::information(this,tr("Blob"), tr("Insert ok"),
QMessageBox::Ok);
}
else
{
QMessageBox::critical (this, tr ("Blob"),
tr ("Error inserting:\n\n %1").
arg (error.databaseText ()),
QMessageBox::Ok);
}
}
db = QSqlDatabase::database ();
QSqlDatabase::removeDatabase (tr ("default"));
}
}
------------------------
The file name comes from a QLineEdit component (editInputFile->text ()).
Note that I encoded the blob to base64 (so it is even possible to
store it even in a text field, though i'm not sure if it's desirable
to do this) to avoid warnings from the pg backend.
If you choose to insert blobs encoded in base64, and have the need to
do something with the blobs from the pg backend, you'll need the
decode(text,text)
[http://www.postgresql.org/docs/8.3/interactive/functions-binarystring.html].
For example, to calculate the actual size of a blob you'll do
something like
SELECT blobname, octet_length(decode(blob::text,'base64')) as
blob_size_in_bytes FROM myblobs WHERE id=2 ;
Bear in mind that doing this kind of operations in the postgresql
backend might imply a huge load in some environments. I'd prefer to
decode the blob in the client application.
Just try both methods, inserting the raw blobs, and then encoding in
base64. If you find a way to avoid the warnings from pg server
regarding the use of \E'...' notation, please let me know!
HTH,
diego
ps. excuse my faults writing english, I'm doing the best I can, but
not very fluent _yet_
_______________________________________________
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/
___________________________________________
More information about the Qt-interest-old
mailing list