How to store blob data in mysql using nodejs and javascript properly

Question

I have an array of integers from 0 to 255 in javascript;

var arr = [249, 13, 105, 170];

And it's required to store this data in mysql database according this rule:

1 number = 1 byte

So, if the array length equals 4, then the size of blob data in mysql DB must be 4 bytes. And it works fine with numbers less than 128.

var res = "";    
for(var i = 0; i < arr.length; i++) {
    res += String.fromCharCode(arr[i]);
}

But numbers from 128 to 256 takes 2 bytes.

I tried to use nodejs buffer

var Buffer = require('buffer').Buffer,
    buf = new Buffer(arr.length);
for(var i = 0; i < arr.length; i++) {
    buf[i] = arr[i];
}
buf.toString('binary');

but the same result. I have no idea how to make it work.

to store data in mysql database I use node-mysql

var Client = require('mysql').Client,
    client = new Client();
client.user = DB_USER;
client.password = DB_PASS;
client.host = DB_HOST;
client.connect(function(error, results) {
    if(error) {
        client.end();
        return;
    }
    client.query('USE ' + DB_SCHEME, function(error, results) {
        if(error) {
            client.end();
            return;
        }

        var sql = "INSERT INTO b SET `data` = ?";
        var values = [buf];

        client.query(sql, values,
            function(error, results) {
                if(error) {
                    return;
                }
                return;
            }
        );
    });
});

Do you have any idea?

Mikhail.Mamaev · Accepted Answer · 2011-08-17 14:22:53Z

2

I've managed to do it using built-in mysql CHAR function:

var arr = [249, 13, 105, 170];
var sql = "INSERT INTO b SET `data` = CHAR( " + arr + " )";

client.query(sql);

where data is blob data

answered Aug 17, 2011 at 14:22

Mikhail.Mamaev

8,0776 gold badges27 silver badges29 bronze badges

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

2 Comments

Sayak Choudhury Over a year ago

can u plz tell the same for prepared statement

philo Over a year ago

I got an error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect INTEGER

Collectives™ on Stack Overflow

How to store blob data in mysql using nodejs and javascript properly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related