Error: invalid BLOB ID when selecting blob from stored procedure (in 0.3.0)

[lafras-h]

Node Firebird 0.3.0 throws : Error: invalid BLOB ID when selecting blob from stored procedure

Selecting blobs from a table works. Selecting blobs form a stored procedure works in 0.1.4,

(using Firebird 2.5.2)

I have set-up a test branch on my new project, with tests on V0.3.0 and V0.1.4 https://github.com/quale-quest/sql-mvc/tree/fbdriver (feedback appreciated)

Basic test case:

var test_0_3_0_B = function () { //
    //in node firebird 0.3.0 throws :  Error: invalid BLOB ID  when selecting blob from stored procedure
    /*
    SET TERM ^ ;
    CREATE PROCEDURE SPTEST (
    INX VARCHAR(40)
    )
    RETURNS (
    INFO VARCHAR(1000),
    RES BLOB SUB_TYPE 1 )
    AS
    BEGIN
    info=INX;
    res=INX;
    suspend;
    END^
    SET TERM ; ^
     */

    Firebird.attach(load_config(), function (err, db) {

        if (err)
            throw err;

        db.query("SELECT a.info,a.RES FROM SPTEST ('12345') a;", function (err, result) {
        console.log("SELECT a.info,a.RES FROM SPTEST ('12345') a  : \n", err, result);

            //Error: invalid BLOB ID thrwon here >>>
            result[0].res(function (err, name, e) {
                if (err)
                    throw err;

                e.on('data', function (chunk) {
                    // reading data
                    console.log("receive chunk : \n", chunk.length, chunk);
                });

                e.on('end', function () {
                    // end reading
                    // IMPORTANT: close the connection
                    console.log("receive end : \n");
                    db.detach();
                });
            });

            db.detach();
        });

    });

    return;
};

[agebrock]

Any change to get the bug fixed or work around ?

[malpacasville]

This is still a problem.

[gnatowski]

The problem is that fetch_blob_async starts new transaction. After that are blob IDs invalid. I've patched the file index.js for me:

after row 3818:

function fetch_blob2_async(statement, id, name) {

    if (!id)
        return null;

    return function(transaction, callback) {
        // callback(err, buffer, name);
        // statement.connection.startTransaction(ISOLATION_READ_UNCOMMITTED, function(err, transaction) {

        //     if (err) {
        //         callback(err);
        //         return;
        //     }
            statement.connection._pending.push('openBlob');
            statement.connection.openBlob(id, transaction, function(err, blob) {
    
                callback(err, name, e);
                read();

            });
        // });
    };
}

and after row 3054:

 if (custom.asObject) {
                            if (item.type === SQL_BLOB)
                                value = fetch_blob2_async(statement, value, data.fcols[data.fcolumn]);
                            data.frow[data.fcols[data.fcolumn]] = value;
                        }

you need then self open transcation and change

result[0].BLOB(function (err, name, e) {...})

to

result[0].BLOB(transaction, function (err, name, e) {...})

[sdnetwork]

The implementation problem (in node-firebird) is described here :

http://tracker.firebirdsql.org/browse/CORE-2086

Adriano is correct, this is not a bug. Nobody should rely on a blob ID outside the transaction it has been retrieved in. Even without transliteration and temporary blobs, the permanent blob can be concurrently deleted and garbage collected in the meantime, thus also causing the "invalid blob ID" error.

i search a good solution, the problem is not only when we start a new transaction into fetch_blob_async, but also when we do a query the result callback is called after transaction.commit so the blobid are potentialy bad at this time ...

[sdnetwork]

it should fix the problem

https://github.com/sdnetwork/node-firebird

[albanirneves]

I changed my BLOBs to Binary, removing charset ISO8859_1

Changed from: BLOB SUB_TYPE 1 SEGMENT SIZE 16384

To: BLOB SUB_TYPE 0 SEGMENT SIZE 16384

It works for me, like expained here: http://tracker.firebirdsql.org/browse/CORE-2086

[mreis1]

As @albanirneves states, using SUB_TYPE 0 (Binary) instead of SUB_TYPE 1 (TEXT) does the job but this is nothing more than a workaround.

If you, just like me, are working with existing databases where you are not the only one accessing and managing the db this workaround is not viable.

I tried @sdnetwork fork and the problem is fixed there. 👍

Why isn't the fix merged in this project? :(

NOTE: BLOB SUB_TYPE 1 (TEXT) works If there's no transliteration (example Node UTF-8 << >> DB UTF-8) but in my case the DB uses ISO 88951 and that's were the INVALID BLOB ID is thrown..

[johnjaider1000]

I had the same problem and the solution I gave was the following:

SELECT 
CAST(CAST(a.NEW_INFO  AS BLOB SUB_TYPE TEXT CHARACTER SET utf8) AS VARCHAR(8191) CHARACTER SET utf8) 
AS NEW_INFO FROM AUDITORIA a;

Change the name of the table (AUDITORIA) and (NEW_INFO) in the query for your field and that's it.

You can now process your BLOB as a string from nodejs.

[eiagit]

I had a partial solver , but my pictiure are limited in 32765bit app.get("/usuario/todos",(req,res)=>{ var query = 'SELECT JUSO.*,TIU_NOME' +' ,SUBSTRING(CAST(USO_FOTO AS VARCHAR(32765))FROM 1 FOR 32765) USO_FOTO ' +' From JUSO ' +' LEFT JOIN JTIU ON TIU_ID=USO_TIPO' +' ORDER BY USO_ID' const params=[]; Execsql(query,params,function(err,result){ if (err) { res.status(500).json(err + " deu erro"); } else { res.status(200).json(result); } } )
})