Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

How to read binary data from database? #142

Open
DezerteR opened this Issue Sep 9, 2013 · 10 comments

Comments

Projects
None yet
3 participants

DezerteR commented Sep 9, 2013

We are storing some binaries(images, etc.) in database. Now I need to save them in hard drive. How can I read binary data? If I use connection.query I get string in strange encoding.
Could you tell me how to encode it?

Contributor

jkint commented Sep 16, 2013

Binary columns should be returned as node.js Buffer objects. The best "examples" we have are the unit tests.

Looking at params.js, there is a test "verify Buffer objects as input parameters" that shows how they work.

The most interesting snippet in that test is this one:

var b = new Buffer( '0102030405060708090a', 'hex' );
conn.queryRaw( "SELECT buffer_param FROM buffer_param_test WHERE buffer_param = ?", [ b ], function( e, r ) {
         assert.ifError( e );
         assert( r.rows.length = 1 );
         assert.deepEqual( r.rows[0][0], b );
         done();
});

Hopefully it is clear how the Buffer object is returned as column 0 of row 0 in the rows array.

If you have questions, let me know.

Thx for help, but it's still don't working;

[code]
var buf = new Buffer(size, 'hex');
connection.queryRaw(str, [buf], function(err, res, more){
if(!err){
if(!more) {
callback(buf);
}
}
else
console.log(err);
});
[/code]

buf is filled with nulls, or in res is the same crap which was previous.

Contributor

jkint commented Sep 17, 2013

In your code above, the buf variable would never change because msnodesql only works with input parameters right now. Without seeing the query in str, I can't advise you more than that.

However, as in the code sample I gave above, buffers are returned when binary objects are the result of a column in a query.

Query is just: select image from image_container where ID=1
I've tested it with menagment studio, and it returns hexs in console, which equals to hex in image(opened in hex editor).
I can convert it to utf8, and i have text (binary representation of this text is an image).
But query returns string which have 12x more characters than previous. Binary representation of this text isn't image.

Contributor

jkint commented Sep 23, 2013

I think there is some confusion on where the buffer is returned. In the example you give:

var buf = new Buffer(size, 'hex');
connection.queryRaw(str, [buf], function(err, res, more){
  if(!err){
    if(!more) { 
      callback(buf);
    }
  }
  else
    console.log(err);
  }); 

you seem to be expecting the results to be in the variable 'buf'. The variable buf is not modified, but rather the buffer containing your field is in the variable res, or more precisely, in an array within the res variable.

If you print res to the console, you should see something similar to this:

[ { image_container: <SlowBuffer 01 02 03 04 05 06 07 08 09 0a> } ]

You should see that the image_container field has the bytes you were expecting.

jkint, I have written below code:

sql.open(conn_str, function(err, connection){
if(!err){
var container=[];
connection.query(str, function(err, res, more){
container.push(res);
if(!more) {
callback(container);
}
});
}
else
console.log(err);
});

I use AdventureWorks2012 database with MS SQL 2012.
My query:

"SELECT ThumbNailPhoto FROM AdventureWorks2012.Production.ProductPhoto Where ProductPhotoID = 70"

But with debugging I have got:

data[0][0].ThumbNailPhoto
"GIF89aP1�������������������7BI��ϫ��������7<D�����ꆋ���������̪��������Ph�~��&'... (length: 3778)"
typeof data[0][0].ThumbNailPhoto
"string"

console.write(res):

[ [ { ThumbNailPhoto: 'GIF89aP\u00001\u0000?\u0000\u0000??????????????????7BI???
????????7<D????????????????????????Ph?~??&'*???gmu????????????????????????????[
u?\u0005\u0006\u0006mqx???\fs?????????Z]c??????????????????????????????????????
...
011\u0010\u0000\u0000;' } ] ]

As I try save it to disk my file is corrupted:

var buf = new Buffer(data[0][0].ThumbNailPhoto, 'binary');

        fs.writeFile(file_name + ".gif", buf, function(err) {
                if(err) {
                    console.log(err);
                } else {
                    console.log("The file was saved!");
                }
            });

Could you give mi some advice?

I use node.js: v0.10.18 x86. Maybe problem with server version? Or just something wrong with encoding binary string. How can I transform binary string to Buffer object?

BTW I have debugged sql.js code and in function onReadColumn results.data is SlowBuffer, but in rowsCompleted function row[0] in result is string 'GIF......'. Why you return binary string instead of Buffer object?

Contributor

jkint commented Sep 24, 2013

Interesting. I will look into it.

When I debugging, I have seen that one image from SQL was readed in two parts, which were accumulated. After first step there was SlowBuffer in rows array:
slowbuffers

but after second step (after "+" operation with second SlowBuffer) there was binary string:
slowbuffer_string

OK. I understand: buffer + buffer is string (http://comments.gmane.org/gmane.comp.lang.javascript.nodejs/39255)

BTW I have tested also with node 0.8.22 x86 and Microsoft compiled driver for this version. The result is the same.

I have resolved this problem changing code in sql.js:

function readall(q, notify, ext, query, params, callback) {

...

function onReadColumnMore( err, results ) {

   ...

    if (callback) {
        //MY CHANGE
        var c = rows[rows.length - 1][column];
        if (Buffer.isBuffer(data))  
            rows[rows.length - 1][column] = Buffer.concat([c,data]);        
        else    
            rows[rows.length - 1][column] += data;
        }

        //instead of    
        //  rows[rows.length - 1][column] += data;
    }

I get good image after saving to disk !!!!!!!!!!!!!!!!!!!!!!!!!!!

Contributor

jkint commented Sep 25, 2013

Nice detective work. I will look at it and get it fixed in a more general sense. Thanks again!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment