0

Faced the following problem: inserting some binary data from the App to "bytea" field in our PostgreSQL 9.2 DB, we found out, that its length is doubled. The App is written in C++ and uses SQL API library to access Postgres. Reference to official doc: http://www.sqlapi.com/HowTo/blobs.html

Saying that data length is doubled means, that before insert command int the app we have: sContent.length() = 19 meanwhile in postgres we get: select length(bindata) = 38, bit_length( bindata ) = 304 from binpacket

In C++ App we do:

SAString sContent = SomeFunctionConvertsByteArrayToSAString(bindata);
cmd.Param("bindata").setAsLargeBinary() = sContent; //SA_dtLongBinary <=> BYTEA
//watch: sContent.length() = 19
cmd.Execute();

and have field length doubled in postgres (select length(bindata) = 38 ).

I wrote some simple Python3 script, that inserts binary data from file to the database:

bindataStream = open('C:\\Temp\\bin.dat', 'rb').read()
cursor.execute("INSERT INTO binpacket( bindata ) VALUES (%s)",
(psycopg2.Binary(bindataStream ),))

and the length of the data inserted is equal to the source binary data file.

That leads me to the conclusion, that it is possible to insert binary data to Postgres 9 without having its length(size) doubled. But is it possible when the data source is a bytearray?

Please, help to find an correct solution how to insert bytea binary data to Postgres. The explanations of the situation described would be appreciated too.

Best regards, Anthony

xacinay
  • 881
  • 1
  • 11
  • 29
  • What are the contents inside the field when the size is doubled? – Daniel Vérité Nov 29 '13 at 14:24
  • 1
    Why convert bindata to a SAString in the first place? Do you have SA_UNICODE set, so that SAString uses 16 bit characters? – jjanes Nov 29 '13 at 21:16
  • @jjanes, the suggestion about SA_Unicode looks right, havent completed checking it. But if it is so, is there a way to pass the data to sql server without doubling to 16bit characters? I mean, we havent found in SQLAPI++ doc any legal way to do it without SASting. And we would not like to have SASTring to be 8bit in the whole project just for the described insert issue. – xacinay Dec 05 '13 at 10:33
  • Unfortunately, I don't know anything about SQLAPI++, I was just thinking about it from the PostgreSQL side. – jjanes Dec 06 '13 at 17:55

1 Answers1

0

To fix the described Issue, update to SQLAPI++ 4.1.2 (it is free for legal customers, like we are). The developers fixed the porblem with binary data for Postgres 9.x. in SQLAPI++ 4.1.2 Release.

xacinay
  • 881
  • 1
  • 11
  • 29