How do save the entire content of a binary file into postgres database?

Question

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

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

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

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?

Have you read the Postgres documentation yet? Binary Data Types and BinaryFilesInDB. — Remy Lebeau
– Remy Lebeau, Commented Mar 28, 2021 at 19:37

Arty · Accepted Answer · 2021-03-28 19:43:47Z

2

Instead of

w.exec("INSERT INTO binary (id,meta) VALUES (18, '"+content+"')");

try doing

w.exec_params("INSERT INTO binary (id,meta) VALUES ($1, $2)", 18, content);

edited Mar 28, 2021 at 19:43

answered Mar 28, 2021 at 19:28

Arty

16.9k6 gold badges60 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Mark Over a year ago

I get the syntax error Error at or near "%": syntax error DETAIL: source SQL: INSERT INTO binary (id,meta) VALUES (%s,%s), I copy and pasted.

Arty Over a year ago

@MarkShaio Try instead of (%s, %s) using ($1, $2). Does it help? If not try ($1::int, $2::bytea). And tell me if it helps.

Mark Over a year ago

It's still complaining about the first arguement for some reason. I did a show columns from metadata.binary and for the data_type I got INT8 for id and BYTES for meta

Arty Over a year ago

@MarkShaio Can you please provide full error that it shows when using ($1, $2) and when using ($1::int, $2::bytea)? Also try using different types, e.g. $2::blob and $1::int8.

Mark Over a year ago

noticed it was my bad, when I added the $1 and $2 I had a typo so there is no syntax error. But the content is still only showing the first few bytes LIZM 2.9

|

Remy Lebeau · Accepted Answer · 2021-03-28 19:56:50Z

1

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)+"')");

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)+")");

edited Mar 28, 2021 at 19:56

answered Mar 28, 2021 at 19:43

Remy Lebeau

609k36 gold badges516 silver badges875 bronze badges

Collectives™ on Stack Overflow

How do save the entire content of a binary file into postgres database?

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related