Page 1 of 1
save BYTE[] into postgres table
Posted: Sun Jan 15, 2023 4:18 pm
by Juraj
Hi All,
I need sabe long text into postgres table. I use net. compress method
Code: Select all
PUBLIC METHOD MyZip( uncompressed AS STRING) AS BYTE[]
LOCAL ret AS BYTE[]
BEGIN USING VAR outputMemory := MemoryStream{}
BEGIN USING VAR gz := GZipStream{outputMemory, CompressionLevel.Optimal}
BEGIN USING VAR sw := StreamWriter{gz, Encoding.UTF8}
sw.Write(uncompressed)
END USING
END USING
ret := outputMemory.ToArray()
END USING
RETURN ret
END METHOD
how to save the result in pg table and then how to read it?
Juraj
save BYTE[] into postgres table
Posted: Sun Jan 15, 2023 4:27 pm
by FFF
Any reason not to simply use a "text" column in PG and let it do the work?
save BYTE[] into postgres table
Posted: Sun Jan 15, 2023 5:16 pm
by wriedmann
Hi Juraj,
to save binary content (and compressed values are binary text) I would always base64encode the values and store them to a a text column in PostgreSQL. This way you will exclude all conversion errors at the cost of a sligthly larger volume.
Otherwise you will have to use the bytea datatype and encode your data:
https://www.postgresql.org/docs/current ... inary.html
Wolfgang
save BYTE[] into postgres table
Posted: Sun Jan 15, 2023 5:57 pm
by Juraj
Hi Wolfgang, FFF
thank you for your advice, I am satisfied with the use of base64.
Juraj
save BYTE[] into postgres table
Posted: Sun Jan 15, 2023 6:16 pm
by FFF
Wolfgang,
i tend to disagree
- as Jurai already HAS string data, and a large one, as he writes. So PG will automatically accept the string in its text column and internally hold there a pointer to a location in its toast table, which in turn holds the data in compressed format. Why add another layer of complexity, if the DB handles it transparently?
save BYTE[] into postgres table
Posted: Mon Jan 16, 2023 5:05 am
by lumberjack
juraj,
Juraj post=24988 userid=1312 wrote:Hi All,
I need sabe long text into postgres table. I use net. compress method
Code: Select all
PUBLIC METHOD MyZip( uncompressed AS STRING) AS BYTE[]
LOCAL ret AS BYTE[]
BEGIN USING VAR outputMemory := MemoryStream{}
BEGIN USING VAR gz := GZipStream{outputMemory, CompressionLevel.Optimal}
BEGIN USING VAR sw := StreamWriter{gz, Encoding.UTF8}
sw.Write(uncompressed)
END USING
END USING
ret := outputMemory.ToArray()
END USING
RETURN ret
END METHOD
how to save the result in pg table and then how to read it?
Juraj
The best is to define the column in PG as type bytea. It use the text column table for storing but do all the work for you.
HTH,
save BYTE[] into postgres table
Posted: Mon Jan 16, 2023 5:16 am
by wriedmann
Hi Karl,
I have now read the PG documentation - didn't knew that PostgreSQL is automatically compressing data with over 2k.
So it may be superfluos to compress the data before storing it to the table (I'm doing that on DBFs, but had to insert a base64 layer when I moved to ADS and partial access with X#).
Wolfgang
save BYTE[] into postgres table
Posted: Sat Jan 28, 2023 6:21 am
by wriedmann
Hi Juraj,
I have now worked with the bytea datatype in PostgreSQL, and if you need some code how to work with it please let me know.
In short: you have to use a string in the PG hex format on write, and when reading the database driver returns you a byte array in the datatable.
I have the request from a customer to store files in a PG table.
Wolfgang
save BYTE[] into postgres table
Posted: Sat Jan 28, 2023 12:40 pm
by Juraj
Hi Wolfgang,
I have a request from the customer to save the text in a "hidden" state, for security reasons when viewing tables normally. My plan was to zip this text and save it in a table. I haven't solved this task yet and would appreciate any advice and help.
Juraj
save BYTE[] into postgres table
Posted: Sat Jan 28, 2023 5:13 pm
by wriedmann
Hi Juraj,
to save binary content to a bytea field you have to convert it using similar code (please don't forget that a crypted/compressed string should be always of byte[] (byte array) datatype.
Code: Select all
static method _Bytes2PGHex( aBytes as byte[] ) as string
local cReturn as string
local oSB as StringBuilder
oSB := System.Text.StringBuilder{ "x" }
foreach b as byte in aBytes
oSB:Append( b:ToString( "x2" ) )
next
cReturn := oSB:ToString()
return cReturn
The resulting string you can use then in a normal SQL statement:
Code: Select all
cStmt := String.Format( "update testtable set testblob = '{0}' where recid = '{1}'", cCodedString, cRecId )
HTH
Wolfgang