SELECT statement does not return BLOB string

[Kerim Gueney]

Hey all,

I have a very simple table with four columns, one of the columns is of type BLOB(10485760). I populated the table with a couple sample data and when querying it via the ACS' SQL Editor, I get the following output:

7   FOO     S   FFD8FFEE0E0...(snipped)

the same statement called via the idb-connector returns the following object:

{ 
    ID: '7',
    COMMENT: 'FOO',
    TYPE: 'S',
    IMG: '' 
}

Is this is a bug or do I need to extract the blob string in a different manner?

[Musse]

I also ran into this issue and spoke with @mengxumx. He is currently working on a solution.

[Musse]

@mengxumx can new version of idb 1.0.10 now return binary data?

[mengxumx]

@abmusse Yes, but it only supports SQL_VARBINARY / SQL_BINARY right now. BLOB/CLOB are not ready yet.

Ref-> https://bitbucket.org/litmis/nodejs-idb-connector/src/master/src/db2ia/dbstmt.h#lines-152

[Brian Jerome]

Any ETA on when BLOB will be ready?

[Musse]

@mengxumx @KerimG @bjerome Currently working on getting BLOB implemented. The Blob would returned back in the form of a Node Buffer. Would Also be able to insert Node Buffer and bind to a BLOB field.

[Brian Jerome]

@abmusse Any update on your BLOB implementation? Hoping to try it out soon! I see there are some new tests added.

[Musse]

@bjerome Very Soon, we have been refactoring this project to use N-API, Blobs will be part of the N-API version of idb-connector.

[mengxumx]

@bjerome idb-connector v1.1.0 is released now.

[Musse]

@bjerome if you run a SELECT statement like OP you will be returned a buffer containing the blob within the result set. As of v1.1.0

[Brian Jerome]

@abmusse Great! Tested it out with the idb-connector and I was able to get the Buffer values. Unrelated I updated idb-pconnector to 0.1.0 but am getting unhandled promise errors so couldn't try it with that yet.

[Musse]

@bjerome Working on Pushing an update to idb-pconnector today. v0.1.1

[Musse]

@bjerome Can you also try to bind a buffer to a blob field in the database. Maybe read a file from disk using fs to get a buffer.

For example:

const idb = require('idb-connector');
const fs = require('fs');

let dbconn = new idb.dbconn();

dbconn.conn('*LOCAL');

let dbStmt = new idb.dbstmt(dbconn);

let buffer = fs.readFileSync('Your File');

dbStmt.prepare('Insert Statement', (error)=>{
        if (error){
          throw error;
        }
        dbStmt.bindParam([[buffer, idb.SQL_PARAM_INPUT, idb.SQL_BLOB]], (error)=>{
          if (error){
            throw error;
          }
          dbStmt.execute( (result, error) =>{
            if (error){
              throw error;
            }
          });
        });
      });

[Brian Jerome]

@abmusse Awesome -- I'll look into testing the filesystem one as well.

[Brian Jerome]

@abmusse Was able to read from a file into a buffer and insert that buffer as the blob via your example Then I read that inserted blob and parsed the buffer with toString() to get the file contents.

[Musse]

@bjerome Nice!

[Musse]

@bjerome FYI v0.1.1 of idb-pconnector released.

[Brian Jerome]

@abmusse idb-pconnector tests look good on my end!

[Musse]

@TaskForce_Kerim have you had a chance to test your original select statement with v1.1.1 of the idb-connector?

Would like to mark this issue as resolved if all is well.

[mengxumx]

@TaskForce_Kerim Could we close this issue now?

[Kerim Gueney]

@mengxumx

Oh yes, I was not able to test this yet, because we used a workaround but I trust Brian's results.

Thanks a bunch!