[SOLVED]How send binary file to Postgres server?

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

freddy311082

Sorry... de end of the byte of files is like this...

@
56236313266343130313030"}"
@

I don't know why in my last post I can not see it

regards

TheBadger

that is strange indeed, the email i got is correct :/ (Perhaps the code brackets cant handle lines without white spaces, it does not know how to break it).

With that output in the QByteArray I am wondering if the Qt driver actually converts the array back to valid data from the BYTEA field in the database. The \x at the start makes me wonder about that since that is how it is supposed to look in the database according to my knowledge. I have a feeling that the driver sees the bracket and then try to convert it to a string and not format it from the BYTEA.

Can you perhaps debug and step into line 7 of your read code and check to see what type the driver sees the data as. My code steps into qsql_psql.cpp into the following function:
@
QVariant QPSQLResult::data(int i)
{
//...
case QVariant::ByteArray: {
size_t len;
unsigned char data = PQunescapeBytea((unsigned char)val, &len);
QByteArray ba((const char*)data, len);
qPQfreemem(data);
return QVariant(ba);
}
@

On my side it does step into the ByteArray field, and then it converts the bytes containing the \x to a valid byte array without that information.

The Values are (according to the debugger):
@
val = "\x536f6d6520746573742062797465206172726179"
ba = "Some test byte array"
@

So if it gets the type correct, then I am afraid that the driver does not unescape the data correctly.

I see that PQunescapeBytea() is a libpq function so I am wondering, is your Qt PostgreSQL driver compiled against the correct version of the source code of libpq and it does correspond to the version of your server?

freddy311082

bro... I think that I have found the problem....

I use the libpqxx to check if the problem is when I send the data to server or when I get it.... well, using libpqxx I'm writing a 14.5 Mb, the same data which I'm writing with Qt... so, seem like the problem is when I convert the data to QString...

I'm going to rectify... the original file has 4.2 Mb and I'm writing !4.5 Mb

TheBadger

I have asked a question on the "PostgreSQL mailing list":http://www.postgresql.org/message-id/CAAxNqarOGOzdJ46Smv-GBXB3rkSme5610nq5ZCDhnd1oJdU54Q@mail.gmail.com, and it seems that the initial query 'should' work on your server (9.3):
@
INSERT INTO files VALUES(18,'\x536f6d6520746573742062797465206172726179');
@
Can you try to do that directly on the server, perhaps using a tool like pgAdmin III.

(I know this discussion is now moving more to a PostgreSQL discussion but I just want to find out where the problem might be).

freddy311082

Yes brother. Using directly the pgAdmin the query work fine. How I sad in my las message, I think that converting QByteArray to QString is the problem or when I request the file from DB i need to do something more to me a conversion of bytes or something... I really don't know.