BLOBs only work if they're last in field list

[sanfords]

Howdy,

Thank you for your hard work on this plug-in. It is very much appreciated.

We've found that BLOBs only work reliably in insert statements if they appear at the end of a field list. If they are intermixed with other fields, they don't work. For example:

Doesn't work:

INSERT INTO sometable (p1, p2, p3) VALUES (:val1, EMPTY_BLOB(), :val3) RETURNING p2 INTO :v2

Does work:

INSERT INTO sometable (p1, p2, p3) VALUES (:val1, :val2, EMPTY_BLOB()) RETURNING p3 INTO :v3

I think I got that right. :-)

Thanks!

[cjbj]

Hi @sanfords what do you mean 'reliably'? Are you using 1.1? If not, please upgrade.
Can you give a node test case?

[sanfords]

We're using 1.1. By reliably, I mean they didn't work if BLOBs were not at the end. Hope this helps.

[cjbj]

@sanfords What is not reliable? Do you get an error?

[krismohan]

Checked on Linux and it works. Perhaps you didn't pass the bind parameters properly. Also it helps if you mention what error you see, what exactly you mean by saying 'doesn't work', Paste the js code snippets also.

[sanfords]

I'll roll an example for you. Please give me a bit.

[sanfords]

Thank you for your patience.

The following are the queries and results with BLOBs not at the end of my inserts:

SQL-0  SELECT TABLE_NAME FROM USER_TABLES 
VAR-0 []
RES-0 []
SQL-1 CREATE TABLE zft (id number(12))
VAR-1 []
RES-1 no result
SQL-2  SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = :tableName 
VAR-2 [ 'ZFT' ]
RES-2 [ { COLUMN_NAME: 'ID' } ]
SQL-3 ALTER TABLE zft ADD (sc NUMBER,fr1 NUMBER,si BLOB,ip BLOB)
VAR-3 []
RES-3 no result
*** NOTE THE ORDER HERE:
SQL-4 INSERT INTO ZFT (SI,IP,SC,FR1) VALUES (EMPTY_BLOB(),EMPTY_BLOB(),:val2,:val3) RETURNING SI,IP INTO :val0,:val1
VAR-4 [ { type: 2007, dir: 3003 }, { type: 2007, dir: 3003 }, 5, 6 ]
ERR-4 [Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB]
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

The following are the same queries but with BLOBs at the end of my inserts:

SQL-0  SELECT TABLE_NAME FROM USER_TABLES 
VAR-0 []
RES-0 []
SQL-1 CREATE TABLE zft (id number(12))
VAR-1 []
RES-1 no result
SQL-2  SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = :tableName 
VAR-2 [ 'ZFT' ]
RES-2 [ { COLUMN_NAME: 'ID' } ]
SQL-3 ALTER TABLE zft ADD (sc NUMBER,fr1 NUMBER,si BLOB,ip BLOB)
VAR-3 []
RES-3 no result
*** NOTE THE ORDER HERE:
SQL-4 INSERT INTO ZFT (FR1,SC,SI,IP) VALUES (:val3,:val2,EMPTY_BLOB(),EMPTY_BLOB()) RETURNING SI,IP INTO :val0,:val1
VAR-4 [ 6, 5, { type: 2007, dir: 3003 }, { type: 2007, dir: 3003 } ]
RES-4 no result
SQL-5 select si,sc,ip from zft
VAR-5 []
RES-5 ... Correct results returned...

I hope this makes sense.

[bjouhier]

Your parameter values are in the wrong order:

SQL-4 INSERT INTO ZFT (SI,IP,SC,FR1) VALUES (EMPTY_BLOB(),EMPTY_BLOB(),:val2,:val3) RETURNING SI,IP INTO :val0,:val1
VAR-4 [ { type: 2007, dir: 3003 }, { type: 2007, dir: 3003 }, 5, 6 ]

In your SQL statement the order of the parameters is :val2, :val3, :val0, :val1. So you should pass the parameters as:

VAR-4 [ 5, 6, { type: 2007, dir: 3003 }, { type: 2007, dir: 3003 } ]

Another solution would be to pass the parameters as an object instead of an array:

VAR-4 { val0: { type: 2007, dir: 3003 }, val1: { type: 2007, dir: 3003 }, val2: 5, val3: 6 }

[sanfords]

Sorry to bother you with this. Oracle SQL is a bit new to me. Is this also out of order? For clarity, I did a quick find/replace (from my first example) for the blobs and numbers.

SQL-3 ALTER TABLE zft ADD (n1 NUMBER,n2 NUMBER,b1 BLOB,b2 BLOB)
SQL-4 INSERT INTO ZFT (b1,b2,n1,n2) VALUES (EMPTY_BLOB(),EMPTY_BLOB(),:val2,:val3) RETURNING b1,b2 INTO :val0,:val1
VAR-4 [ { type: 2007, dir: 3003 }, { type: 2007, dir: 3003 }, 5, 6 ]

It's been a long few days. I may be going cross-eyed.

Edit, here it is for real with new names:

SQL-0  SELECT TABLE_NAME FROM USER_TABLES 
VAR-0 []
RES-0 []
SQL-1 CREATE TABLE zft (id number(12))
VAR-1 []
RES-1 no result
SQL-2  SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = :tableName 
VAR-2 [ 'ZFT' ]
RES-2 [ { COLUMN_NAME: 'ID' } ]
SQL-3 ALTER TABLE zft ADD (n1 NUMBER,n2 NUMBER,b1 BLOB,b2 BLOB)
VAR-3 []
RES-3 no result
SQL-4 INSERT INTO ZFT (B1,B2,N1,N2) VALUES (EMPTY_BLOB(),EMPTY_BLOB(),:val2,:val3) RETURNING B1,B2 INTO :val0,:val1
VAR-4 [ { type: 2007, dir: 3003 }, { type: 2007, dir: 3003 }, 5, 6 ]
ERR-4 [Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB]
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
executeTransaction Body [Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB]

I'm not seeing where the order is wrong. SQL-4 is inserting (blob, blob, number, number). And the VAR-4 has [ blob, blob, number, number ]

OracleSQL should follow the order of the INSERT, not the order of the columns in the table ALTER TABLE, right?

[bjouhier]

It's not the order of the columns that matter, it is the order of the :xxx variables in the string, regardless of what the SQL string means.

If the SQL string is INSERT INTO ZFT (B1,B2,N1,N2) VALUES (EMPTY_BLOB(), EMPTY_BLOB(), :val2, :val3) RETURNING B1,B2 INTO :val0,:val1, the order is :val2, :val3, :val0, :val1.

[sanfords]

Thank you very much for your time.

[cjbj]

@sanfords in the circumstances, since you might be exploring and changing SQL a bit, I'd recommend using the 'named' bind syntax that @bjouhier gave at the end of #205 (comment) This will prevent ordering errors occuring.

Also see https://github.com/oracle/node-oracledb/blob/master/doc/api.md#outbind

[sanfords]

Yes, probably a good idea. Thank you again.

[bjouhier]

@cjbj I answered because our developers ran into the problem. We had called our variables :0, :1, :2, ..., with the expectation that they would match the indexes in the array of values, regardless of their position in the SQL string. It worked initially because the variables appeared in the right order in our SQL statements but it broke when we introduced more complex statements.

The doc is not very explicit about this. Maybe a short note/warning would help.

[cjbj]

@bjouhier Good idea.