如何将二进制文件的全部内容保存到postgres数据库中? [英] How do save the entire content of a binary file into postgres database?

问题描述

我正在尝试将二进制数据保存到postgres中.部分代码如下所示:

I'm trying to save binary data into postgres. Parts of the code is shown below:

string readFile2(const string &fileName)
{
    ifstream ifs(fileName.c_str(), ios::in | ios::binary | ios::ate);
    ifs.seekg(0, ios::end);

    ifstream::pos_type fileSize = ifs.tellg();
    ifs.seekg(0, ios::beg);

    vector<char> bytes(fileSize);
    ifs.read(bytes.data(), fileSize);
    cout.write(bytes.data(),bytes.size());
    cout << "\n";
    cout << fileSize;
    cout << "\n";
    // return bytes.data();
    return string(bytes.data(), fileSize);
}

int main() {
  string content;
  string test = "h";
  char test1 = 'C';
  try {
    cout << "A1 \n";;
    content = readFile2("/var/opt/lizardfs/lib/lizardfs/metadata.mfs");
    pqxx::connection c("postgresql://mark@localhost:26257/metadata");
    pqxx::nontransaction w(c);


    w.exec("CREATE TABLE IF NOT EXISTS binary (id INT PRIMARY KEY, meta BLOB)");
    w.exec("INSERT INTO binary (id,meta) VALUES (18, '"+content+"')");
}

问题是，如果我尝试运行代码，则会收到错误消息:

The problem is that if I try to run the code I get the error:

ERROR:  lexical error: unterminated string
DETAIL:  source SQL:
INSERT INTO binary (id,meta) VALUES (18, 'LIZM 2.9

我认为这是因为下一个字符是空字符.十六进制转储显示 00

I think that is because the next character is a null character. Hexdump shows 00

如果不是使用 readFile2 函数，则返回 return string(bytes.data()，fileSize); 并返回 return bytes.data(); ，然后错误消失.但是，进入数据库后，我只能看到二进制文件的前几个字节，如下所示:

If instead of having the readFile2 function return return string(bytes.data(), fileSize); and returns return bytes.data(); then the error disappears. However, going into the database all I can see is the first few bytes of the binary file, shown below:

  id |   meta
-----+-----------
  15 | LIZM 2.9

当我有 return bytes.data(); 时，我不确定为什么它不能存储所有二进制内容.如何获取二进制文件的全部内容以保存在数据库中?

I'm not sure why it's not able to store all the binary content when I have return bytes.data();. How do I get the entire content of the binary file to save in the database?

推荐答案

使用 bytea 代替 BLOB ，然后在插入二进制数据时对其进行转义，例如:

Use bytea instead of BLOB, and then escape the binary data when inserting it, eg:

w.exec("CREATE TABLE IF NOT EXISTS binary (id INT PRIMARY KEY, meta bytea)");
w.exec("INSERT INTO binary (id,meta) VALUES (18, '"+w.esc_raw(content)+"')");

或者，将二进制数据放入 pqxx :: binarystring 代替:

Alternatively, put the binary data into a pqxx::binarystring instead:

pqxx::binarystring bs = content;
w.exec("CREATE TABLE IF NOT EXISTS binary (id INT PRIMARY KEY, meta bytea)");
w.exec("INSERT INTO binary (id,meta) VALUES (18, "+w.quote(bs)+")");

这篇关于如何将二进制文件的全部内容保存到postgres数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！