save BYTE[] into postgres table

[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

[FFF]

Any reason not to simply use a "text" column in PG and let it do the work?

[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

[Juraj]

Hi Wolfgang, FFF
thank you for your advice, I am satisfied with the use of base64.

Juraj

[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?

[lumberjack]

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

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,

[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

[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

[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

[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