How to insert binary data into sql server using node-mssql

Question

I'm downloading an image using node/request module, and I'm trying to figure out how to insert that image into a varbinary field in sql server using the node/mssql module. So far I have tried putting a cast into the insert statement, converting the body (buffer) to a string, all to no avail. I'm trying to figure out how to do this without using a stored procedure.

Thanks!

Have you tried leaving it as a Buffer? The docs for node-mssql note that Buffer and sql.VarBinary are considered related. — Jonathan Lonowski
– Jonathan Lonowski, Commented Dec 20, 2015 at 18:01
Can you please share the code you're using for the query? What is happening that indicates it "didn't work?" Are you receiving any errors? — Jonathan Lonowski
– Jonathan Lonowski, Commented Dec 20, 2015 at 18:26

K Jackson · Accepted Answer · 2017-03-17 01:21:43Z

1

I've read in a .png image file from disk as 'binary', and then put that into a 'binary' buffer, and then was able to insert that into SQL Server DB using a prepared statement:

fs.readFile(<path-to-file>, 'binary', function(err, fileData) {
    var binBuff = new Buffer(fileData, 'binary');
    var ps = new sql.PreparedStatement(<connection>);
    ps.input('theImage', sql.VarBinary);
    ps.prepare('INSERT INTO ImageTable (BinaryImage) VALUES (@theImage)', function (err) {
        // check err
        ps.execute({theImage: binBuff}, function(err, records) {
            // check err
            ps.unprepare(function(err) {
                // check err
                // If no error, it's been inserted!
            });
        });
    });
});

answered Mar 17, 2017 at 1:21

K Jackson

3731 gold badge4 silver badges13 bronze badges

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

2 Comments

K Jackson Over a year ago

Yes, works well for me. I can retrieve the image from the DB and renders just fine in a webpage.

sirius2013 Over a year ago

thanks, it works, how about audio file? actually I tried to record audio binary file and want to play as remote url.

Collectives™ on Stack Overflow

How to insert binary data into sql server using node-mssql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related