autoCommit is not working to for multiple queries

[cddm-gh]

Hi I have this small program that first checks if something exists on a table and if it doesn't it tries to insert it, the first query executes fine but when is time to insert the program fails with error: Error: NJS-012: encountered invalid bind data type in parameter 2, if I remove the 'autoCommit:true' and put: await oracle.execute('COMMIT WORK', db) on the line after [pmigradosId] = result.outBinds.pmigradosId; it works but I'm not sure if that the right way to do it. I'm guiding with this example: https://github.com/oracle/node-oracledb/blob/master/examples/insert1.js by using multiple inserts and commit on the last one to finish the transaction successfully and also with the answer from this question: https://stackoverflow.com/questions/56356869/use-transaction-in-node-oracledb

Code Here

CREATE TABLE "PMIGRADOS" 
   (	"COD_PMIGRADOS" NUMBER NOT NULL ENABLE, 
	"FECHA" DATE NOT NULL ENABLE, 
	"HORARIO" DATE, 
	"FECHA_ENTREGA" DATE, 
	"TRANSFER" VARCHAR2(1) NOT NULL ENABLE, 
	"ESTADO" VARCHAR2(1) NOT NULL ENABLE, 
	"NUMERO_CARGA" VARCHAR2(20) NOT NULL ENABLE, 
	"CODPRO" VARCHAR2(4) NOT NULL ENABLE, 
	"COD_DEPOSITO" VARCHAR2(10) NOT NULL ENABLE, 
	"USUARIO" VARCHAR2(20), 
	"HORARIO_FIN" DATE, 
	 PRIMARY KEY ("COD_PMIGRADOS")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "OPT_DATOS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "OPT_DATOS" ;

4. Run node and show the output of:

process.platform: 'darwin'
process.version: 'v10.20.0'
process.arch: 'x64'
require('oracledb').versionString: '3.1.2'

5. What is your Oracle Database version?
   Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

[cjbj]

Your code isn't immediately runnable (there is no sample input data, no entrypoint, logger isn't set up, unsatisfied references to oracledb) so how do you know the first query returns fine?

Some extra try/catch blocks would be handy for you to see what is working and what isn't.

Regardless, between your query and insert, someone else could have inserted a row, so you should revisit the design. Maybe use a SELECT FOR UPDATE, or maybe just do the INSERT and check whether it was successful or not. The latter would be most efficient, but does require that you already know the unique key. If you're doing a lot of batch processing, look at how you can utilize executeMany() and/or PL/SQL to upload data and then do the locking/inserting in PL/SQL. Take a look on https://asktom.oracle.com/ for related solutions.

Your statements do a lot of string interpolation like CODPRO = ${vendorId} AND COD_DEPOSITO='${codDeposito}' etc, which is alarming for security and scalability reasons. Use bind variables.

If this is a service or web app, make sure to use a connection pool.

[dmcghan]

@gorydev Why did you close this? Did you solve it? If so, what did you do? :)

[cddm-gh]

@dmcghan oh sorry my bad I guess it was a mistake, I managed to get it work but I don't know if what I did is the best way, I tried to apply some of the things @cjbj suggested so here is what I did:
Updated version of the code

this is a sample object of what I receive and has to be saved in the DB:

const headerData = {
  FECHA: '2020-05-26',
  FECHA_ENTREGA: '2020-05-27',
  TRANSFER: '1',
  ESTADO: 'E',
  NUMERO_CARGA: '1',
  CODPRO: '1234',
  COD_DEPOSITO: 'DEP-01',
  USUARIO: '',
  HORARIO_FIN: '2020-05-26 14:45:34'
};

[dmcghan]

I'm able to run the code after making the following edits:

• cleaning the create table DDL

• creating the sequence

• adding a reference to oracledb in main.js

• changing references from logger to console in oracle_db.js

• adding the following code to main.js

  const headerData = {
    FECHA: '2020-05-26',
    FECHA_ENTREGA: '2020-05-27',
    TRANSFER: '1',
    ESTADO: 'E',
    NUMERO_CARGA: '1',
    CODPRO: '1234',
    COD_DEPOSITO: 'DEP-01',
    USUARIO: '',
    HORARIO_FIN: '2020-05-26 14:45:34'
  };
  
  saveOrderHeader(headerData)
    .then(function(res) {
      console.log(res);
    })
    .catch(function(err) {
      console.log(err);
    });
  

After I ran the code the first time, it created the record. The second time, it said the record already exists so it didn't. Then I changed the COD_DEPOSITO from 'DEP-01' to 'DEP-02' and it created the record again.

I'm not sure how to reproduce the issue.

[cddm-gh]

Thanks for the reply @dmcghan thats exactly the behavior I want, let me try to explain a little bit:
Each vendor can only have 1 registration on PMIGRADOS each day, and when a Vendor send an order I have to first check if he already have a registration for that day, if so then return the COD_PMIGRADOS else I have to create a new record and return that ID and then write the order in another table but my question is if what I did is right to accomplish that? I mean is it working correctly as a transaction?? or should I do something else??

[dmcghan]

is it working correctly as a transaction??

That depends on your goals. If you had two requests come in at the exact same time, then it's possible you'll have more than one row.

The best way to prevent this in your case would be via a unique constraint on the CODPRO, COD_DEPOSITO, and FECHA columns on the table. That would keep your data clean by returning an error to the second one that tried to insert the duplicate data.

How high is the likelihood of multiple submissions at the exact same time? If it's high, you can add additional code to handle that better - otherwise, I probably wouldn't bother. At least the data will be protected.

[cddm-gh]

Unfortunately I can't create constraints on the table because I don't manage it, I can only query and insert data into it. And for the likelihood I'd say is low because the vendor sends the order it then goes through a queue and then is saved into DB after doing those queries.

[cjbj]

Closing - no activity.