Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC:

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

  • Juraj
  • Juraj's Avatar
  • Topic Author


  • Posts: 158
  • 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
    • FFF's Avatar


  • Posts: 1391
  • Any reason not to simply use a "text" column in PG and let it do the work?
    Regards
    Karl (X# 2.14.0.4; Xide 1.33; 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
    • wriedmann's Avatar


  • Posts: 3242
  • 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
    • Juraj's Avatar
    • Topic Author


  • Posts: 158
  • 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
    • FFF's Avatar


  • Posts: 1391
  • 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.14.0.4; Xide 1.33; 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
    • lumberjack's Avatar


  • Posts: 720
  • juraj,

    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
    • wriedmann's Avatar


  • Posts: 3242
  • 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
    • wriedmann's Avatar


  • Posts: 3242
  • 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
    • Juraj's Avatar
    • Topic Author


  • Posts: 158
  • 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
    • wriedmann's Avatar


  • Posts: 3242
  • 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