[SOLVED]How send binary file to Postgres server?

JKSH

[quote author="freddy311082" date="1390831690"]Thank for answer JKSH and sorry for the code... I didn't know about @.[/quote]That's ok :) Thank you for editing your posts.

[quote]I just need the binary data from file in a QString....[/quote]Why?

If you have binary data, it's best to store it as binary data (QByteArray). It doesn't make sense to put non-strings inside QString.

freddy311082

well JKSH...

i need put the binary data in a QString because I'm trying to save a file into PostgreSQL databse.... to make that, I need to pass a query string to QSqlDatabse::exec() method and for that, I need to pass it a QString...

let me show what I'm doing:

@
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);
}

    QString bytes = file.readAll();
    file.close();
    std::cout << "sending query..." << std::endl;
    
    db.exec&#40;(QString("insert int files values (66, '{"&#41;+ bytes  +"}')"));
    db.close();
}
exit(EXIT_SUCCESS);

@

as you can see, into the query execution I need to pass a QString, so, I need to convert the binary file into a QString to send it to my database...

any idea ?

thanks anyway my friend for your answer

freddy

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...