0

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.

2
  • 2
    Shouldn't there be a comma after 1000, like "(30, 1000, '"+w.esc_raw(content)+"')"? Commented Mar 6, 2022 at 20:19
  • I am so stupid...thanks, I've been looking at this for hours... Commented Mar 6, 2022 at 20:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.