user1972670 user1972670 - 6 months ago 204
MySQL Question

NodeJS mySQL Insert Blob

I need a little help
with NodeJS and MySQL blob insertion.

Here's the code snippet i'm using

fs.open(temp_path, 'r', function (status, fd) {
if (status) {
console.log(status.message);
return;
}
var buffer = new Buffer(getFilesizeInBytes(temp_path));
fs.read(fd, buffer, 0, 100, 0, function (err, num) {
var query ="INSERT INTO `files` (`file_type`, `file_size`, `file`) VALUES ('img', " + getFilesizeInBytes(temp_path) + ",'" + buffer + "' );";
mySQLconnection.query(query, function (er, da) {
if (er)throw er;
});
});
});


Query inserts the file in the table and I get the correct file size, but when I try
to retrieve the file and open it ( for example a PDF file ) I get a message saying that
the file is corrupted.

I must be doing something wrong with the buffer reading from the file.

Answer

Try replacing:

var query ="INSERT INTO `files` (`file_type`, `file_size`, `file`) VALUES ('img', " + getFilesizeInBytes(temp_path) + ",'" + buffer + "' );";
mySQLconnection.query(query, function (er, da) {

with:

var query = "INSERT INTO `files` SET ?",
    values = {
      file_type: 'img',
      file_size: buffer.length,
      file: buffer
    };
mySQLconnection.query(query, values, function (er, da) {

You may also want to change file: buffer to file: buffer.slice(0, 100) since you are only reading the first 100 bytes of the file. If buffer.length > 100 then you may end up with a bunch of extra garbage bytes after the first 100 bytes in buffer.

Comments