CODEWITHSUNDEEP
c++postgresqllibpqxx
Mark
Mark

Reputation: 123

syntax error while trying to save binary data to postgres like database

I'm getting a syntax error while saving binary data to a postgres like database. Below is part of the code:

void insertToDatabase(string content) {
  pqxx::connection c("postgresql://test@localhost:5433/testdb");
  pqxx::nontransaction w(c);
  cout << "ABC: "<< w.esc_raw(content) << endl;
  w.exec("INSERT INTO small_files (file_id, file_size, content) VALUES (30, 1000 '"+w.esc_raw(content)+"')");
}

int main(int argc, char* argv[]) {
  string content;
  try {
      insertToDatabase(content);
  }
  catch {...}
}

The error I am getting is:

ERROR:  syntax error at or near "'\x504b0304140000080 ..........
LINE 1: ...es (file_id, file_size, content) VALUES (30, 1000 '\x504b030...

I'm not sure why I am getting the error as based on what I am seeing from the second solution from How do save the entire content of a binary file into postgres database?, I think I'm doing the right thing? The error appears to be pointing at the single quotation mark before \x.

I should mention the schema of small_files look like:

  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 file_id   | bigint |           |          | 
 file_size | bigint |           |          | 
 content   | bytea  |           |          |

I should also mention that on line 4 where I'm printing out the content prints out the entire binary content, ie: (below is just a snippet of it)

\x504b030414000008000083236654856c398a2e0000002e000000080000006d696d65747970656170706c69636174696f6e2f766e642e6f617369732e6f70656e646f63756d656e742e7370726561647368656574504b0304140000080000832366540000000000000000000000001c000000436f6e66696775726174696f6e73322f61.....

Can someone explain to me why I'm getting this syntax error, when I can clearly see the binary content when I try to print it out, and how to fix it? Thank you.

Upvotes: 0

Views: 236

Answers (0)

Related Questions