I am building an api rest with node-oracledb, I can retrieve all kind of data but the program breaks every time I try to do an insert. This is the generic method I use to get a connection from the pool and perform the query.
import oracledb from "oracledb";
export const executeQuery = async ({ query, binds, options, type, res }) => {
let connection = null;
try {
connection = await oracledb.getConnection();
} catch (error) {
console.log("Error al conectar OracleDB");
}
let result = null;
try {
result =
type === "insertOne"
? await connection.execute(query, binds, options)
: type === "insertMany"
? await connection.executeMany(query, binds, options)
: null;
console.log(result);
} catch (err) {
console.error("error", err.message);
res.status(500).json("Error recuperando datos");
} finally {
if (connection) {
try {
res.status(200).json(result.rows);
// Always release the connection back to the pool
await connection.close();
} catch (err) {
return console.error(err.message);
}
}
}
};
This is the controller method with which I am trying to insert a single record, in production the bind data would come from a post request.
insertOneExample: async (req, res) => {
const { items } = req.body;
const query = `MERGE INTO SCHEMA.TABLE USING dual ON (CODIGO_HOSPI = :CODIGO_HOSPI AND CENTRO_ID = :CENTRO_ID)
WHEN MATCHED THEN UPDATE SET PREV1 = :PREV1, PREV2 = :PREV2, PREV3 = :PREV3, PREV4 = :PREV4, PREV5 = :PREV5, PREV6 = :PREV6, PREV7 = :PREV7, PREV8 = :PREV8, PREV9 = :PREV9, PREV10 = :PREV10, PREV11 = :PREV11, PREV12 = :PREV12,
WHEN NOT MATCHED THEN INSERT (CODIGO_HOSPI, PREV1, PREV2, PREV3, PREV4, PREV5, PREV6, PREV7, PREV8, PREV9, PREV10, PREV11, PREV12, CENTRO_ID)
VALUES (:CODIGO_HOSPI, :PREV1, :PREV2, :PREV3, :PREV4, :PREV5, :PREV6, :PREV7, :PREV8, :PREV9, :PREV10, :PREV11, :PREV12, :CENTRO_ID)`
const options = {
autoCommit: true,
bindDefs: {
CODIGO_HOSPI: { type: oracledb.STRING, maxSize: 20 },
PREV1: { type: oracledb.NUMBER },
PREV2: { type: oracledb.NUMBER },
PREV3: { type: oracledb.NUMBER },
PREV4: { type: oracledb.NUMBER },
PREV5: { type: oracledb.NUMBER },
PREV6: { type: oracledb.NUMBER },
PREV7: { type: oracledb.NUMBER },
PREV8: { type: oracledb.NUMBER },
PREV9: { type: oracledb.NUMBER },
PREV10: { type: oracledb.NUMBER },
PREV11: { type: oracledb.NUMBER },
PREV12: { type: oracledb.NUMBER },
CENTRO_ID: { type: oracledb.STRING, maxSize: 10 },
},
};;
executeQuery({
query,
binds: {
CODIGO_HOSPI: "101",
PREV1: 52600,
PREV2: 870,
PREV3: 123,
PREV4: 564,
PREV5: 846,
PREV6: 625,
PREV7: 897,
PREV8: 124,
PREV9: 656,
PREV10: 456,
PREV11: 324,
PREV12: 212,
CENTRO_ID: "10346",
},
options,
type: "insertOne",
res,
});
}
When executing the method, the server crashes without any error message.
*** sql statement is not the problem, it also crashes with a simple insert.
result.rowswhenresultis null, and also the case when there is no connection doesn't useres. Try simplifying your code by removing columns & bind values (even all of them to check the basic flow works). You may think the statement isn't the problem but you need to verify it. Don't chain the.jsonbut put the return in a variable and check it is OK. If you still need more help, update the question with a runnable script and include the CREATE TABLE. See stackoverflow.com/help/minimal-reproducible-example - Christopher Jones