NodeJS mySQL Insert Blob

Question

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.

You can't just concatenate a buffer into a query. In fact, you should never concatenate anything into a query. How you insert a Blob depends entirely on what MySQL library you're using. Can you tell us? — Brad
– Brad, Commented Sep 20, 2014 at 17:21

mscdex · Accepted Answer · 2014-09-20 17:48:29Z

16

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.

answered Sep 20, 2014 at 17:48

mscdex

107k15 gold badges201 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joeyipod Over a year ago

I am curious. I have a similar problem. But I have not just a file in my query. But also normal numbers and text.

Philipp Kyeck · Accepted Answer · 2017-01-30 13:46:03Z

6

Thank you mscdex for the snippet.

The problem was as you pointed out that i was reading only first 100 bytes of data. BTW thank you for the snippet and here's the whole solution. Hope it 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;
        });

    });
});

edited Jan 30, 2017 at 13:46

Philipp Kyeck

18.9k19 gold badges90 silver badges126 bronze badges

answered Sep 20, 2014 at 19:47

user1972670

3971 gold badge4 silver badges15 bronze badges

Comments

magicpanda · Accepted Answer · 2016-05-30 10:17:13Z

1

For the small files, you can try below code:

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
    }
})

answered May 30, 2016 at 10:17

magicpanda

663 bronze badges

Comments

jales cardoso · Accepted Answer · 2018-05-08 14:12:54Z

0

try using multer buffer:

let query = "UPDATE yourtable SET image=? WHERE id = '1'"
let obj = req.file.buffer;
conn.query(query, obj, function (err, rows, fields) {})

answered May 8, 2018 at 14:12

jales cardoso

6368 silver badges14 bronze badges

Collectives™ on Stack Overflow

NodeJS mySQL Insert Blob

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related