#bytea

[meilanhsiao]

#enlightenment #oftea #bytea #fortea https://www.teasommelier.be/tea-enlightenment/ (at Belgium Chinese Tea Arts Centre) https://www.instagram.com/p/B-jBr0vnJ3N/?igshid=czxf3voq4gi8

[k-a-z-mcc95]

Saving images to your database

When I was trying to work out how to do this, I didn't think there were enough code examples for it. After a while, I did find an example on Stack OverFlow. This is it, but refined a little.

function formatImageForDB(path, callback){ fs.readFile(path,'hex', function(err, data) { if (!err) { data = '\\x'+data; askDatabase('INSERT INTO table_name (image) VALUES (data)', callback); }else{ console.error(err); callback(err); } });

OK. So what does this do? Above is a function called formatImageForDB, it takes the path to the image and a callback. It uses fs to read the contents as hex, if there is an error, it prints it to the console, otherwise it will preppend a '\x' to the start and will insert that data into the database. After inserting it, askDatabase will call the callback. Hex is used here because its super good. It doesn't affect the file contents.

function formatImageForDisplay(req,res){ sql = "SELECT image FROM table WHERE id=1"; askDatabase(sql , function(err, result){ if (err) { console.error(err) } console.log(result); if (result.rowCount === 0){ console.log("not sending image") res.sendFile(__dirname +"/public/test.jpg"); } else if (result.rows[0].image == undefined) { console.log("not sending image") res.sendFile(__dirname +"/public/test.jpg"); } else { fs.writeFile('public/foo.jpg', result.rows[0].image, function (errr) { console.log("sending image"); res.sendFile(__dirname + "/public/foo.jpg"); } }) }

I've made the function "askDatabase" to abstract the actual database. You could make an askDatabase function to work with any old database.

[marcnuri]

bytea columns, JDBC and different PostgreSQL versions (8.x - 9.x)

When developing an application that uses a column of type bytea in PostgreSQL, be sure to use the most recent JDBC driver that will be compatible with any PostgreSQL version you are using. A project that worked with PostgreSQL 8.3 when deployed in a server that connects to 9.1, stopped working. Binary data was stored correctly in the database, but when read it appeared as corrupted. Apparently PostgreSQL from 9.0 outputs bytea column data in a new format "hex" instead of the old "escape". Using the most up to date JDBC driver solved the problem.