[Qt-interest] postgres bytea

[Diego Schulz]

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_