NPM Oracle: Inserting BLOBs

Question

I am trying to use the NPM oracle library and update some tables with BLOB values created from files on my computer. Oracle documentation says to use the createLob() function like the following in order to get a value the database will accept:

conn.createLob(oracledb.BLOB, function(err, templob) {
if (err) { . . . }
// ... else use templob
});

But I have no idea what "use templob" implies...

How do I get my data into these oracledb.BLOB objects?

EDIT: Example of update I am attempting:

const queryString = `UPDATE TABLENAME SET BLOB = :blob WHERE ID = 1234;`;
                this.oracleConnection.execute(queryString, 
                    {blob: await fs.readFileSync('/path/to/image.jpg')}
                )

Could you provide some more details about what you're doing? Where do the BLOBs come from (files on the server, web server, etc.)? How large are the BLOBs? — Dan McGhan
– Dan McGhan, Commented Jun 24, 2019 at 16:52
Files are from my computer as stated. Files are variable size but should all be bellow 1mb for now. — BGrossman
– BGrossman, Commented Jun 24, 2019 at 16:58

Dan McGhan · Accepted Answer · 2019-06-24 17:42:37Z

1

Based on your use case (small files and limited concurrency), I think the buffer APIs will be the best bet as they are very simple. From the doc:

Given the table:

CREATE TABLE mylobs (id NUMBER, c CLOB, b BLOB);

an INSERT example is:

var fs = require('fs');
var str = fs.readFileSync('example.txt', 'utf8');
. . .

conn.execute(
  `INSERT INTO mylobs (id, myclobcol) VALUES (:idbv, :cbv)`,
  { idbv: 1,
    cbv: str },  // type and direction are optional for IN binds
  function(err, result) {
    if (err)
      console.error(err.message);
    else
      console.log('CLOB inserted from example.txt');
. . .

Also, in case you ever need it, I'm wrapping up a mini-series on this topic but it's framed more around web server uploads: https://jsao.io/2019/06/uploading-and-downloading-files-with-node-js-and-oracle-database/

answered Jun 24, 2019 at 17:42

Dan McGhan

4,6691 gold badge14 silver badges15 bronze badges

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

6 Comments

BGrossman Over a year ago

This is still giving me an invalid character error on an offset that is inside the buffer. Does it matter that the column I am updating is a BLOB, not a CLOB?

BGrossman Over a year ago

Updated post with an example

Dan McGhan Over a year ago

It shouldn't matter if it's a CLOB or BLOB, the driver supports both. Could you please provide the exact error you're getting? Generally, these things are always easier to solve with a reproducible test case. Also, you don't need to await the call to readFileSync since it's a sync method.

BGrossman Over a year ago

[Error: ORA-00911: invalid character] errorNum: 911, offset: 64

Dan McGhan Over a year ago

Ah, with SQL statements, you don't supply the semi-colon at the end. Just remove it and try again.

|

Collectives™ on Stack Overflow

NPM Oracle: Inserting BLOBs

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related