Error trying to insert image to BLOB type in Oracle database using node-oracledb

Question

Following this answer, I was trying to save a .jpg file to a BLOB type column inside a table. This is what I have so far:

I created a test table:

CREATE TABLE test_lob(
    ID NUMBER
  , C  CLOB
  , B  BLOB
);

Then I wrote this code:

async function getFile() {
  var str = fs.readFileSync('/path/to/image', 'utf8');
  await insertBlob(str);
}
...
async function insertBlob(str) {
  var connection;

  try {
    connection = await oracledb.getConnection({
      user:          process.env.USER,
      password:      process.env.PASS,
      connectString: process.env.SERVER_CONNECT_STRING
    });

    const result = await connection.execute(
      `
        INSERT INTO test_lob(id, b) VALUES(1, :b)
      `,
      { b: str },
      { autoCommit: true }
    );

    console.log(result);
  } catch(err) {
    console.error(err);
  } finally {
    if(connection) {
      try {
        await connection.close();
      } catch(err) {
        console.error(err);
      }
    }
  }
}

But I always get Error: ORA-01461: can bind a LONG value only for insert into a LONG column. What am I missing?

O. Jones · Accepted Answer · 2021-03-02 15:47:50Z

1

You read your image file as if it were a Unicode text string. That's not correct; you should read it as a buffer.

 var imageBuffer = fs.readFileSync('/path/to/image');
  await insertBlob(imageBuffer);

I believe Oracle complains because you try to INSERT a Unicode string to a binary column. Try inserting the buffer.

edited Mar 2, 2021 at 15:47

answered Mar 2, 2021 at 14:21

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

2 Comments

matheuseuseus Over a year ago

Oof, that was simpler than I expected. Thank you!

Christopher Jones Over a year ago

This is also shown in the node-oracledb sample github.com/oracle/node-oracledb/blob/master/examples/…

Collectives™ on Stack Overflow

Error trying to insert image to BLOB type in Oracle database using node-oracledb

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