SELECT statement does not return BLOB string

[abmusse]

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

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?

[abmusse]

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

[abmusse]

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

[abmusse]

Original comment by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

@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

[abmusse]

Original comment by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

Any ETA on when BLOB will be ready?

[abmusse]

@dmabupt @KerimG @brianmjerome 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.

[abmusse]

Original comment by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

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

[abmusse]

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

[abmusse]

Original comment by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

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

[abmusse]

@brianmjerome 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

[abmusse]

Original comment by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

@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.

[abmusse]

@brianmjerome Working on Pushing an update to idb-pconnector today. v0.1.1 :slight_smile:

[abmusse]

@brianmjerome 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:

#!js

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

[abmusse]

Original comment by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

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

[abmusse]

Original comment by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

@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.

[abmusse]

@brianmjerome 👍 Nice!

[abmusse]

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

[abmusse]

Original comment by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

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

[abmusse]

@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.

[abmusse]

Original comment by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

@TaskForce_Kerim Could we close this issue now?

[abmusse]

Original comment by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

@dmabupt

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

Thanks a bunch!