[SOLVED]How send binary file to Postgres server?

JKSH

I see; I misunderstood your original intent, sorry.

You can use byteArray.toBase64() to encode your file as a binary string. toHex() works too, but produces longer strings than toBase64(). See https://qt-project.org/forums/viewthread/12281 for an example.

By the way, for safety, it is a good idea to use SQL bindings instead of building a string directly. See:

freddy311082

Hi JKSH

Thanks for your advices, they are very useful...

Well, as I could see, I think that the problem is not with QString and QByteArray... I think that I can make a new post with my QSqlQuery question.

the table files, which is where I'm trying to save the files, has only 2 filds. The first one is a fileid, who is an integer, and the second one is a bytea[], where I'm trying to save the binary file.

anyway, here is my code exaple of what I'm trying to do... someone in a Postgres list told me that use a query.addBindValue, I try and nothing happend.

here is my new code example:

@
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("localhost");
db.setPort(5432);
db.setDatabaseName("bpg");
db.setUserName("postgres");
db.setPassword("");
if (db.open())
{
QFile file("/home/freddy/Trabajo/bpg/diseno/rubro 7.xlsx");
if (!file.open(QIODevice::ReadOnly))
{
qDebug("file error...");
exit(0);
}
QByteArray bytesArr;
QBuffer buffer(&bytesArr);
buffer.open(QBuffer::ReadWrite);
buffer.write(file.readAll());
std::cout << "sending query..." << std::endl;

    QSqlQuery query(db);
    query.prepare("insert into files values(:id,:data)");
    query.bindValue(":id",18);
    query.bindValue(":data",bytesArr.toHex());
    //query.addBindValue(bytesArr.toHex());
    if (!query.exec&#40;&#41;)
    {
        qDebug(query.lastError().driverText().toLatin1());
    }
    cout << "query sent..." << endl;
    file.close();
    db.close();
}

@

and this is the output of my program:

@
sending query...
QPSQL: Unable to create query
query sent...
Unable to free statement: connection pointer is NULL

*** Exited normally ***
@

do you remomend me that open a new post or continue here ??

what i'm do wrong ??

best regards

JKSH

You're welcome :)
[quote]
@
query.prepare("insert into files values(:id,:data)");
@
[/quote]Your string shouldn't have ':'

Check the QSqlQuery documentation again.
[quote]
@
QByteArray bytesArr;
QBuffer buffer(&bytesArr);
buffer.open(QBuffer::ReadWrite);
buffer.write(file.readAll());
@
[/quote]The buffer is unnecessary. Just read your QFile straight into your QByteArray.

[quote]
@
query.bindValue(":data",bytesArr.toHex());
@
[/quote]I'm not sure how the bytea type works. Maybe you can save space by using toBase64() instead of toHex(), and store your data in a string column? (I don't use PostgreSQL)

[quote]do you remomend me that open a new post or continue here ??[/quote]You can continue posting here, since this is your actual question. Just edit your original post to change the title.

TheBadger

You can do the following to store a QByteArray directly in PostgreSQL:
@
// Create QSqlField type for your byte array
QSqlField byteField("Data", QVariant::ByteArray);
// Set the data in the QSqlField
byteField.setValue(bytesArr);
// Create the query
QString queryString = QString("INSERT INTO files VALUES(%1, %2);")
.arg(18)
.arg(db.driver()->formatValue(byteField));
// Execute the query
query.exec(queryString);
@

Then in PostgreSQL the column type should be BYTEA.

Hope this helps (its the way that I am doing it)

TheBadger

To continue on my previous post:
To read the data again you just do:
@
// Query
query.exec("SELECT id, data FROM files LIMIT 1");
query.next();
QByteArray dataArray = query.value(query.record().indexOf("data")).toByteArray();
@

That should do it.

freddy311082

Thanks for help for everybody for your help...

I had change the post name as JKSH recomend...

well Badger, your solution doesn't work my friend... I have try this:

@
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("localhost");
db.setPort(5432);
db.setDatabaseName("bpg");
db.setUserName("postgres");
db.setPassword("");
if (db.open())
{
QFile file("/home/freddy/Trabajo/bpg/diseno/rubro 7.xlsx");
if (!file.open(QIODevice::ReadOnly))
{
qDebug("file error...");
exit(0);
}
QByteArray bytesArr(file.readAll());
std::cout << "sending query..." << std::endl;
QSqlField byteField("filedata",QVariant::ByteArray);
QString queryStr = QString("insert into files values(%1,%2);").arg(18).arg(db.driver()->formatValue(byteField));
QSqlQuery query(db);
query.prepare(queryStr);
//query.addBindValue(bytesArr.toHex());
if (!query.exec(queryStr))
{
qDebug("query error...");
qDebug(query.lastError().databaseText().toLatin1());
}
cout << "query sent..." << endl;
file.close();
db.close();
}
@
and the output is this:
@
Starting: /home/freddy/Trabajo/bpg/kdevelop/bpg/build/bpg
sending query...
query error...
ERROR: array value must start with "{" or dimension information
LINE 1: insert into files values(18,'\x504b030414000600080000002100e...
^
(22P02)
query sent...
*** Exited normally ***
@

I try to add a '{' to the begining of the QArrayBuffer, after append the bytesArray reading from the file, and finaly, y append to the end of the end of the array the '}' character and ther error is the same.

Postgres require that the data for the insert query for the bytea field must be between characters {} or with le length information at the beginin...

i think that the proble is exactly how put the binary field between {}... i havd try with this and the result is the same:
@
QByteArray bytesArr;
bytesArr.append('{');
bytesArr.append(file.readAll());
bytesArr.append('}')
@

best regards...

TheBadger

That is very interesting... I cant see a difference between your code and my code (except for the application specific stuff).

What version of Qt are you using and PostgreSQL?
Mine is: Qt 4.8.1
PostgreSQL: 9.1

I am still looking into the problem, hopefully I can post an update that might help.

Sorry for that

freddy311082

That's Ok Badger...

I'm using Qt5.2, Postgres 9.3 and Archlinux.

I have try with the post which told me JKSH but the problem still remain. The problem is how I can put the binary data between {} for postgres query....

TheBadger

Quick though, try:
@
QString queryStr = QString("insert into files values(%1,'%2');").arg(18).arg(db.driver()->formatValue(byteField));
@
or
@
QString queryStr = QString("insert into files values(%1,'{%2}');").arg(18).arg(db.driver()->formatValue(byteField));
@

or something based on the second without the quotes.

freddy311082

with the first code the output is this:
@
Starting: /home/freddy/Trabajo/bpg/kdevelop/bpg/build/bpg
sending query...
query error...
ERROR: syntax error at or near ""
LINE 1: insert into files values(18,''\x504b030414000600080000002100...
^
(42601)
query sent...
*** Exited normally ***
@
and with the second is this one:
@
Starting: /home/freddy/Trabajo/bpg/kdevelop/bpg/build/bpg
sending query...
query error...
ERROR: syntax error at or near ""
LINE 1: insert into files values(18,'{'\x504b03041400060008000000210...
^
(42601)
query sent...
*** Exited normally ***
@

TheBadger

My best guess is it is then something with the versions. An example in my PostgreSQL log of a successful query is:
@
SELECT SCHEMA.update_element(1, 9, '2014-1-29 16:55:03.411', '\xaaaaaaaa00000000000000000000000
@

(only the first part is shown, but that is out of the log, with some name updates )

[edit: clicked post instead of preview]

TheBadger

The difference is that I am using a stored procedure in postgreSQL to update the table, the stored procedure is defined as:
@
CREATE OR REPLACE FUNCTION SCHEMA.update_element(
nr_1 INTEGER,
nr_2 INTEGER,
time_arg TIMESTAMP WITH TIME ZONE,
data_arg BYTEA
)
RETURNS VOID AS
$$
DECLARE
BEGIN
-- input into table with update
END;
$$
@

But from my limited understanding is that that should be basically the same in handling the BYTEA field.

freddy311082

yes brother... I will see in a Postgres forum

thanks anyway

TheBadger

My conclusion is PostgreSQL version change that made this happen (I might be wrong).

My code:
@
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
/* Set the default values for the database connection */
db.setHostName("127.0.0.1");
db.setPort(5432);
db.setDatabaseName("my_db");
db.setUserName("postgres");
db.setPassword("postgres");

if (db.open())
{
    QByteArray bytesArr("Some test byte array");
    qDebug() << "sending query...";
    QSqlField byteField("filedata", QVariant::ByteArray);
    byteField.setValue(bytesArr); // need this
    QString queryStr = QString("INSERT INTO files VALUES(%1,%2);")
                       .arg(18)
                       .arg(db.driver()->formatValue(byteField));
    QSqlQuery query(db);
    query.prepare(queryStr);

    qDebug() << "Query: " << queryStr;
    //query.addBindValue(bytesArr.toHex());
    if (!query.exec&#40;queryStr&#41;&#41;
    {
        qDebug("query error...");
        qDebug(query.lastError().databaseText().toLatin1());
    } else {
        qDebug() << "query sent...";
    }
    db.close();
}

@

My output:
@
sending query...
Query: "INSERT INTO files VALUES(18,'\x536f6d6520746573742062797465206172726179');"
query sent...
@

The result, an updated column in my database.

Somewhere I think there is a version incompatibility. Perhaps one can make a bug request on some place, not sure where the solution should be.

Good luck, these types of problems are very annoying.

[edit: took out IP and db name]

TheBadger

Me again :P

Try something like this, to check the PostgreSQL server:
@
QString hackedString;
hackedString = db.driver()->formatValue(byteField);
hackedString.insert(1, '{');
hackedString.insert(hackedString.length() - 1, '}');
qDebug() << "hacked string: " << hackedString;
QString queryStr = QString("INSERT INTO files VALUES(%1,%2);")
.arg(18)
.arg(hackedString);
@

*just replace the relevant code in your code.

The string looks fine according to what the error says, but my server responds with "ERROR: invalid input syntax for type bytea".

freddy311082

YES MY FRIEND !!! THAT IS THE SOLUTION !!!

THANKS !!!!

TheBadger

Its a hack... but if it works :/

I think the Qt PostgreSQL Driver code might need to be updated to support the BYTEA field in the latest PostgreSQL versions (this is just a thought).

Glad I could have helped.

freddy311082

Badger my friend.... sorry for ask you again

Yes, I can write the file, but when I read it from database, and write it to my HD, I'm writing a 14 Mb and the original file has only 3 Mb.

here is my code to retrieve and write the file...

@QString queryStr = QString("select from * from files where fileid=%1;").arg(18);
QSqlQuery query(db);
if (query.exec(queryStr))
{
qDebug("saving file..");
query.next();
QByteArray newFile = query.value(query.record().indexOf("filedata")).toByteArray();
QFile newF("/home/freddy/example.xlsx");
if (newF.open(QIODevice::WriteOnly))
{
qDebug("salvando el fichero...");
newFile.remove(0,1); // to remove the "{"
newFile.remove(newFile.length() - 1, 1); // to remove the "}"
newF.write(newFile);
newF.close();
}
}
cout << "query sent..." << endl;
db.close();@

do you know about it my friend.... sorry for my new answer...

TheBadger

No problem, I will try to assist.

Can you post the output of newFile just after you retrieved it from the database.
Something like
@
qDebug() << "File Contents: " << newFile;
@

The reason why I am asking, since the driver did not add the brackets by itself, I am wondering how the driver would have returned the data, if it even contains the curly braces ({}).

Also check the size of the byte array that you write to the database and the size of the byte array after you have read it from the database. Perhaps there can also be a clue as to where something goes wrong.
@
nrBytes = newFile.count();
@

freddy311082

thanks for answer my friend...

yes brother, the things that you told me I did it before ask you...

this is the data:

1.- The original fila has 8780956 bytes and from Postgres I'm receving 17561909 bytes.
2.- The file received from database is like this:
@
"{"\x783466363736373533303030323030303030303030303030303030303063393830623436663030303030303030616163356364643130313261383037343638363536663732363130333032303130303336303031663030303336303030303165363030306130303030326565303030303030336538303030303031303030303031303031653834383033306330346636373637353330303032303030303030303030303030303030306338383062343666303030303030303061393763393731363031316530313736366637323632363937333030303030303030303134346163303030306666666666666666393....
todos aqui son bytes, no los voy a poner todos.... y el fichero termina así
... 0303030633938306234366634653030303030303236373761656533303130303466363736373533303030343462363030653030303030303030303063383830623436663036303030303030356236313266343130313030"}"
@

is like the representation of each byte is in 2 bytes and not in one, because the length of the string received is almost the double of the original file. Has 5 character plus than the original file, and for that I'm removing the braces in my code, but even when the length of the original file is not the double exactly, is obvious that the file is not the same...

I test with other file... it's original length is 7.7 Mb and the saved file from Postgres has 16.4 Mb....

I can give you my new code again, but is the same that I posted the last time... I just add the qDebug() << to see the length

regards my friend