Extract File (bytea) from PostgreSQL database into a document file (application)
Hello, Subjet, I make an application on Mendix to extract/insert data into a PostgreSQL database. In my database, I have one table (data_file_crate) contains a column with files stored in bytea format These files are of type pdf, csv, etc.. I would like to insert these files into an Object document in Mendix. For the csv files, I would like to make plots. And the pdf files will be viewed in the mendix application (with file Document viewer) Method I retrieve the data in the PostgreSQL database with the connector “Execute query” This is the command in this connector : 'SELECT * FROM public."data_file_crate";' I obtain a string such as \xffd8ffe000104a46494600010101004800480000ffe20d084943435f50524f46494c4500010100000cf86170706c021000006d6e74725247422058595a2007e400010002000a00000017616373704150504c000000004150504c000000000000000000000000000000010000f6d6000100000000d32d6170706c000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000…. Do you have a method to recover data or the file into my object document ? I would like to recover my files into the System.FileDocument Object, as if I load them with my application. Thank you in advance for your help Pierre-François.
I’m no expert in this, but, if the string you posted is as base64 encoding, the CommunityCommons module does contain a Java action that converts base64 to a file (Base64DecodeToFile).
Hope it helps!
Thank you for your answer.
In my table, my document are stored in Bytea type.
I not sure that the type is equivalent into the Base64.
I have a problem with this command : 'SELECT encode(files_data_crate::bytea, "escape") FROM public."data_file_crate" WHERE id_test_crate = 3;'
This command works on PgAdmin but it doesn’t work on the Execute Query connector into Mendix.