NodeJS mySQL Insert Blob
I need a little help with nesting NodeJS and MySQL blob.
Here's a snippet of code 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;
});
});
});
The request inserts the file into the table and I get the correct file size, but when I try to get the file and open it (like a PDF file), I get a message that the file is corrupted.
I must be doing something wrong with reading the buffer from the file.
source to share
Try replacing:
var query ="INSERT INTO `files` (`file_type`, `file_size`, `file`) VALUES ('img', " + getFilesizeInBytes(temp_path) + ",'" + buffer + "' );";
mySQLconnection.query(query, function (er, da) {
from:
var query = "INSERT INTO `files` SET ?",
values = {
file_type: 'img',
file_size: buffer.length,
file: buffer
};
mySQLconnection.query(query, values, function (er, da) {
You can also 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 might end up with a bunch of extra garbage bytes after the first 100 bytes in buffer
.
source to share
Thanks to mscdex for the snippet.
The problem was you pointed out that I only read the first 100 bytes of data. BTW thank you for the snippet and here's the whole solution. Hope this can help someone :-)
fs.open(temp_path, 'r', function (status, fd) {
if (status) {
console.log(status.message);
return;
}
var fileSize = getFilesizeInBytes(temp_path);
var buffer = new Buffer(fileSize);
fs.read(fd, buffer, 0, fileSize, 0, function (err, num) {
var query = "INSERT INTO files SET ?",
values = {
file_type: 'img',
file_size: buffer.length,
file: buffer
};
mySQLconnection.query(query, values, function (er, da) {
if(er)throw er;
});
});
});
source to share
For small files, you can try the code below:
var fileInsertSQL = "insert ignore into File(id, content, creationTime) values(?,?,?)";
db.query(fileInsertSQL, ["id1", fs.readFileSync(filepath), new Date().getTime()], function (err, dbRes) {
if(err){
console.error(err);
} else {
//Do something
}
})
source to share