[SOLVED]How send binary file to Postgres server?



  • Hello friends

    I have this code:
    @
    QFile file("/home/freddy/Trabajo/bpg/diseno/rubro 7.xlsx");
    if (!file.open(QIODevice::ReadOnly))
    {
    file.close();
    exit(0);
    }

        QByteArray bytes = file.readAll();
        file.close();
        QString allBytes;
        for(int i = 0; i < bytes.length(); i++)
            allBytes+= bytes.at(i);
        qDebug(todosLosBytes.toLatin1());
        exit(0);
    

    @
    I'm trying to print in console the content of the file because I need the content of the file in a QString variable and it doesn't work.

    if I write inside the ciclo std::cout << bytes.at(i), the content of the file is printing in console, but when I add it to my QString variable it doesn't work.

    I can changethe ciclo and put only allBytes = bytes I have the same problem...

    what I'm suppose to to to fix my problem?? what i'm doing wrong ??

    best regards

    Freddy

    [edit, code tags, koahnig]



  • sorry.... the code was not pasted well...

    here is my code again
    @
    QFile file("/home/freddy/Trabajo/bpg/diseno/rubro 7.xlsx");
    if (!file.open(QIODevice::ReadOnly))
    {
    std::cout <<"error" << std::endl;
    exit(0);
    }

    QByteArray bytes = file.readAll();
    file.close();
    QString allBytes;
    for(int i = 0; i < bytes.length(); i++)
    allBytes+= bytes.at(i);
    qDebug(allBytes.toLatin1());
    @

    best regards

    [code tags added, koahnig]


  • Moderators

    Hi,

    .xlsx is a compressed file format. You can't convert it directly to a string because its data is not plain text. (If you try to open the .xlsx file with a text editor, you will see garbage values).

    If you create a .txt file and write some text inside, you can convert the bytes directly to a string.

    By the way, add '@' before and after your code to make it easier to read. If you make a mistake, you can click "edit" on the right -- you don't need to post twice.



  • Thank for answer JKSH and sorry for the code... I didn't know about @.

    I just need the binary data from file in a QString.... is that possible ?

    I had open an xlsx file as example, but I need to do this with any kinf of files: PDF, JPEG, PNG or anyone

    is that possible ??

    thanks



  • Binary data is not "print"-able. Do a google search for "how to output binary data".

    http://www.qtforum.org/article/19923/how-to-dump-a-qbytearray-to-a-qstring-with-one-sentence.html


  • Lifetime Qt Champion

    Edit: only repeated the same thing as my fellows, sorry


  • Moderators

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



  • 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


  • Moderators

    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:



  • 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


  • Moderators

    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.



  • 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)



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



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



  • 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



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



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



  • 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 ***
    @



  • 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]



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



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

    thanks anyway



  • 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]



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



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

    THANKS !!!!



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



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



  • 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();
    @



  • 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



  • 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



  • 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?



  • 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



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



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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.