I have extracted hex data from a postgres bytea column. The data which was inserted was from a JPG image. I have use the followed code to extract the data and write it to a jpg file.
library('RODBC') library('tidyverse') library('base64enc') # get the data from the database rc <- odbcConnect('odk_prod') img_tbl <- sqlQuery(rc, 'select * from odk_prod."GRAVEL_ROADS_1_3_GRAVEL_PHOTO_BLB"') # create the file for writing and squirt the 1's and 0's in it to.write = file("img.jpg","wb") writeBin( (unlist(img_tbl[1,'VALUE'])), to.write) close(to.write)
but when the jpg file is opened it is "corrupted". I assume I need to encode the output in a binary format. In this post: Storing and retrieving files/images in a database from Shiny app, someone is doing the same process, but they get out what they put in.
I am guessing I need to encode the hex data into binary data, but don't know how to do that in R.
Any function tips? or code tips would be great!