PostgreSQL 使用 Bytea 二进制数据和 C libpq 保存和选取文件 [英] PostgreSQL Save and Pick files using Bytea binary data with C libpq

问题描述

这是我用 bytea 和 postgreSQL 和 libpq 进行实验的结果.我使用接收到的数据创建的结果文件是原始上传文件的两倍大小加上 2 个字节 (picture.png).我无法确切了解我做错了多少操作，因为这个过程对我的小动物大脑来说非常混乱.任何帮助或建议对我来说都是一个很大的帮助，在此先感谢您.

This was the result of my experiments with bytea with postgreSQL and libpq. The result file I am creating with the recieved data is double sized than the original uploaded plus 2 bytes (picture.png). I can not understand exactly how many operations I am doing incorrectly because the procedure is quite confusing for my critter brain. Any help or advice will be a big help for me, thank you in advance.

<2021 年 5 月 27 日编辑>你可以在下面找到解决方案

#include <string>
#include <fstream>
#include <iostream>

#include "libpq/libpq-fs.h"
#include "libpq-fe.h"

#pragma comment(lib, "libpq.lib")   /*!< Only for windows compilation */

int main(int argc, char* argv[])
{
    //************************ SAVING FILE TO DB AS BYTEA **********
    manager.conn = manager.ConnectDB();  // my manager, working fine    

    FILE* file = fopen("powerup.png", "rb");
    if (file == NULL) cout << endl << "FILE WAS UNABLE TO BE READED" << endl;
    fseek(file, 0, SEEK_END);
    long int fileSize = ftell(file);

    rewind(file);

    unsigned char* buffRead = (unsigned char*)malloc(fileSize);
    size_t bytes_read = fread(buffRead, 1, fileSize, file);
    if (bytes_read != fileSize) cout << endl << "fread reading Error";
    fclose(file);

    const char* paramValues[3];
    paramValues[0] = "1";
    paramValues[1] = "powerup.png";
    paramValues[2] = reinterpret_cast<const char*>(buffRead);       //._.' type cast required to PQexeParams?

    const int paramFormats[3]{ 0,0,1 };
    const int paramLenghts[3]{ strlen(paramValues[0]), strlen(paramValues[1]), fileSize};

    PGresult *res = PQexecParams(manager.conn, "INSERT INTO filebyte (id, filename, file) VALUES($1::text, $2::text, $3::bytea)",
        3,              /* params */
        NULL,           /* let the backend deduce param type */
        paramValues,
        paramLenghts,   /* don't need param lengths since text */
        paramFormats,   /* default to all text params */
        1);

    if (res && PQresultStatus(res) == PGRES_COMMAND_OK) cout << endl << "Inserted data to filebyte OK";

    PQfreemem(res);

    //********************** PICKING FILE FROM DB AS BYTEA **********
    
    const char* bytesFromDB = new const char[fileSize];
    int sizeR = -1;
    const char *sentenceEx = "SELECT file FROM filebyte WHERE id='1'";

    res = PQexec(manager.conn, sentenceEx);

    if (res && PQresultStatus(res) == PGRES_TUPLES_OK) 
    {
        sizeR = PQgetlength(res, 0, 0);
        bytesFromDB = PQgetvalue(res, 0, 0);
    }

    ofstream myFile("picture.png", ios::out | ios::binary);

    myFile.write(bytesFromDB, sizeR);
    myFile.close();

    PQfreemem(res);
    free(buffRead);
    delete sizeP;

    manager.CloseConn(manager.conn);

    return true;
}

推荐答案

这是代码工作正常的结果.我现在有时间发布它，以防它可以帮助某人.我有点难以理解 bytea 的工作原理，但最终解决了.向社区致以亲切的问候.

#include <string>
#include <fstream>
#include <iostream>
#include <sstream>

#include "libpq/libpq-fs.h"
#include "libpq-fe.h"

#pragma comment(lib, "libpq.lib")   /*!< Only for windows compilation */

int main(int argc, char* argv[])
{
    ManageDB manager;       

    manager.conn = manager.ConnectDB();  // My manager, connects ok

    const char* fileName = {"test.png"};
    const char* fileNameOut = { "testOut.png" };

    FILE* file = fopen(fileName, "rb");
    if (file == NULL) cout << endl << "FILE WAS UNABLE TO BE READED" << endl;
    fseek(file, 0, SEEK_END);
    long int fileSize = ftell(file);

    rewind(file);

    unsigned char* buffRead = (unsigned char*)malloc(fileSize);
    size_t bytes_read = fread(buffRead, 1, fileSize, file);
    if (bytes_read != fileSize) cout << endl << "fread reading Error";
    fclose(file);
    
    size_t* sizeP = new size_t(fileSize);

    const char* paramValues[3];
    paramValues[0] = "1";
    paramValues[1] = fileName;
    paramValues[2] = reinterpret_cast<const char*>(buffRead);   // type cast required to PQexeParams

    const int paramFormats[3]{ 0,0,1 };   //0 = text, 1 = binary
    const int paramLenghts[3]{ strlen(paramValues[0]), strlen(paramValues[1]), fileSize};

    PGresult *res = PQexecParams(manager.conn, "INSERT INTO filebyte (id, filename, file) VALUES($1::text, $2::text, $3::bytea)",
        3,              /* params num */
        NULL,           /* let the backend deduce param type */
        paramValues,
        paramLenghts,
        paramFormats,
        1);

    if (res && PQresultStatus(res) == PGRES_COMMAND_OK) cout << endl << "Inserted data to filebyte OK";

    PQfreemem(res);

    //************************download from DB*************************
    
    const char* bytesFromDB = new const char[fileSize];
    int sizeR;
    // Table columns = id(text) | filename(text) | file(bytea)
    const char* sentenceEx = "SELECT file FROM filebyte WHERE id='1'";

    res = PQexec(manager.conn, sentenceEx);

    if (res && PQresultStatus(res) == PGRES_TUPLES_OK)
    {
        sizeR = PQgetlength(res, 0, 0);
        bytesFromDB = PQgetvalue(res, 0, 0);
    }
    else cout << endl << "Error at inserting file data in filebyte table";

    string hex(bytesFromDB, 2, sizeR-2);  //removing the first '\x' characters of the result.

    // vars for converting to real bytes process
    std::basic_string<uint8_t> bytes;
    bytes.clear();
    std::string nextbyte; 
    nextbyte.clear();
    uint16_t byte;

    // Iterate over every pair of hex values in the input string (e.g. "18", "0f", ...)
    for (size_t i = 0; i < hex.length(); i += 2)
    {
        // Get current pair and store in nextbyte
        nextbyte = hex.substr(i, 2);

        // Put the pair into an istringstream and stream it through std::hex for
        // conversion into an integer value.
        // This will calculate the byte value of your string-represented hex value.
        std::istringstream(nextbyte) >> std::hex >> byte;

        // As the stream above does not work with uint8 directly, we have to cast it now.
        // As every pair can have a maximum value of "ff",
        // which is "11111111" (8 bits), we will not lose any information during this cast.
        bytes.push_back(static_cast<uint8_t>(byte));
    }

    // string obj from bytes-"array"
    std::string result(begin(bytes), end(bytes));

    ofstream myFile(fileNameOut, ios::out | ios::ios_base::binary);
    
    if (myFile.is_open())
    {
        myFile << result;
        myFile.close();
    }
    else cout << endl << "Impossible to writte the file " << fileNameOut;
    
    manager.CloseConn(manager.conn);   //closes connection with database internally ok

    PQfreemem(res);
    free(buffRead);
    delete sizeP;
    free((char*)fileName[8]);
    free((char*)fileNameOut[11]);
    
    return true;
}

这篇关于PostgreSQL 使用 Bytea 二进制数据和 C libpq 保存和选取文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！