Welcome, Guest

Page:
1

TOPIC:

save BYTE[] into postgres table 15 Jan 2023 17:18 #24988

Juraj
Topic Author

Offline

Posts: 159

Hi All,
I need sabe long text into postgres table. I use net. compress method

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

Please Log in or Create an account to join the conversation.

Last edit: by robert.

save BYTE[] into postgres table 15 Jan 2023 17:27 #24989

FFF

Offline

Posts: 1422

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

Regards
Karl (X# 2.16.0.5; Xide 2.16; W8.1/64 German)

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 15 Jan 2023 18:16 #24991

wriedmann

Offline

Posts: 3367

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:
www.postgresql.org/docs/current/datatype-binary.html
Wolfgang

Wolfgang Riedmann
Meran, South Tyrol, Italy

www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 15 Jan 2023 18:57 #24992

Juraj
Topic Author

Offline

Posts: 159

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

Juraj

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 15 Jan 2023 19:16 #24993

FFF

Offline

Posts: 1422

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?

Regards
Karl (X# 2.16.0.5; Xide 2.16; W8.1/64 German)

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 16 Jan 2023 06:05 #24994

lumberjack

Offline

Posts: 721

juraj,

Juraj wrote:

Hi All,
I need sabe long text into postgres table. I use net. compress method

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,

______________________
Johan Nel
Boshof, South Africa

Please Log in or Create an account to join the conversation.

Last edit: by robert.

save BYTE[] into postgres table 16 Jan 2023 06:16 #24995

wriedmann

Offline

Posts: 3367

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

Wolfgang Riedmann
Meran, South Tyrol, Italy

www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 28 Jan 2023 07:21 #25080

wriedmann

Offline

Posts: 3367

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

Wolfgang Riedmann
Meran, South Tyrol, Italy

www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 28 Jan 2023 13:40 #25082

Juraj
Topic Author

Offline

Posts: 159

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

Please Log in or Create an account to join the conversation.

save BYTE[] into postgres table 28 Jan 2023 18:13 #25084

wriedmann

Offline

Posts: 3367

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.

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:

cStmt := String.Format( "update testtable set testblob = '{0}' where recid = '{1}'", cCodedString, cRecId )

HTH

Wolfgang

Wolfgang Riedmann
Meran, South Tyrol, Italy

www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Page:
1

Forum

Public Forums

Examples

save BYTE[] into postgres table