Query bind with a lot of parameters ! #6
Description
Hi,
I have a problem and I wanted to know is there anyway to write a clean code for inserting data in for example 16 columns without using nested if & else?
Because if i do in a more clean way without nested if & else, it always returns randomly(sometimes it inserts sometimes returns error) an error which is like below
[Error: Error running instance method
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1937)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063)
at jdk.internal.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
] {
cause: nodeJava_java_sql_SQLException {},
level: 'error',
[Symbol(level)]: 'error'
}
And, also the code is not clean and looks like below
statement.setInt(1, l_id, function (err) {
if (err) {
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.toString() });
} else {
statement.setString(2, nome, function (err) {
if (err) {
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.toString() });
} else {
statement.setString(3, cognome, function (err) {
if (err) {
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.toString() });
} else {
statement.setString(4, stato, function (err) {
if (err) {
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.toString() });
} else {
.....
}
My idea is to use another .js file to do this job for me with using switch case for binding types and using a function for executing the query. So, my queryBind.js is like below
module.exports = {
setVal: setVal,
executeRicetta: executeRicetta,
executeRicettaDettagli: executeRicettaDettagli,
executePazienti: executePazienti
}
async function setVal(statement, type, col, val) {
switch (type) {
case "setInt":
statement.setInt(col, val, function (err) {
if (err) {
return false;
} else {
return true;
}
});
break;
case "setString":
statement.setString(col, val, function (err) {
if (err) {
return false;
} else {
return true;
}
});
break;
}
}
async function executeRicetta(statement, callback, res) {
statement.executeUpdate(function (err, count) {
if (err) {
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.message });
} else {
console.log('Statement executed for ricetta insert!');
callback(null, count);
//console.log(count)
}
});
}
async function executeRicettaDettagli(statement, callback, res) {
statement.executeUpdate(function (err, count) {
if (err) {
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.message });
} else {
console.log('Statement executed for ricetta_dettagli insert!');
callback(null, count);
//console.log(count)
}
});
}
async function executePazienti(statement, callback, res, req) {
statement.executeUpdate(function (err, count) {
var statement_str = JSON.stringify(statement)
if (err) {
console.log(err);
create_logFile(req, -1, JSON.stringify(statement));
console.log("SATEMENT",statement)
res.status(500).json({ "status": 0, "statusDescription": err.message });
} else {
console.log('Statement executed for pazienti insert!');
callback(null, count);
//console.log(count)
}
});
}
And, I call this file in my controller like below
oracledb.reserve(function (err, connObj) {
// // The connection returned from the pool is an object with two fields
// // {uuid: <uuid>, conn: <Connection>}
if (err) {
res.status(500).json({ "status": 0, "statusDescription": err.toString() });
logger.create_logFile(req, 1, err.message);
} else if (connObj) {
console.log("Using connection: " + connObj.uuid);
// Grab the Connection for use.
var conn = connObj.conn;
asyncjs.series([
function (callback) {
conn.prepareStatement(query.importaAnagrafica, function (err, statement) {
if (err) {
callback(err);
console.log(err);
res.status(500).json({ "status": 0, "statusDescription": err.toString() });
} else {
console.log('Statement prepared for pazienti insert');
l_step = "[/queryBind for pazienti]"
queryBind.setVal(statement, "setInt", 1, pazientiJson.id);
console.log("BIND 1", pazientiJson.id);
queryBind.setVal(statement, "setString", 2, pazientiJson.nome);
console.log("BIND 2", pazientiJson.nome);
queryBind.setVal(statement, "setString", 3, pazientiJson.cognome);
console.log("BIND 3", pazientiJson.cognome);
queryBind.setVal(statement, "setString", 4, pazientiJson.stato);
console.log("BIND 4", pazientiJson.stato);
queryBind.setVal(statement, "setString", 5, pazientiJson.sesso);
console.log("BIND 5", pazientiJson.sesso);
queryBind.setVal(statement, "setString", 6, pazientiJson.codice_fiscale);
console.log("BIND 6", pazientiJson.codice_fiscale);
queryBind.setVal(statement, "setString", 7, data_di_nascita_str);
console.log("BIND 7", data_di_nascita_str);
queryBind.setVal(statement, "setString", 8, pazientiJson.indirizzo_residenza);
console.log("BIND 8", pazientiJson.indirizzo_residenza);
queryBind.setVal(statement, "setString", 9, pazientiJson.cap_residenza);
console.log("BIND 9", pazientiJson.cap_residenza);
queryBind.setVal(statement, "setString", 10, pazientiJson.indirizzo_domicilio);
console.log("BIND 10", pazientiJson.indirizzo_domicilio);
queryBind.setVal(statement, "setString", 11, pazientiJson.cap_domicilio);
console.log("BIND 11", pazientiJson.cap_domicilio);
queryBind.setVal(statement, "setString", 12, pazientiJson.id_comune_domicilio);
console.log("BIND 12", pazientiJson.id_comune_domicilio);
queryBind.setVal(statement, "setString", 13, pazientiJson.id_provincia_domicilio);
console.log("BIND 13", pazientiJson.id_provincia_domicilio);
queryBind.setVal(statement, "setString", 14, pazientiJson.id_comune_residenza);
console.log("BIND 14", pazientiJson.id_comune_residenza);
queryBind.setVal(statement, "setString", 15, pazientiJson.id_provincia_residenza);
console.log("BIND 15", pazientiJson.id_provincia_residenza);
queryBind.setVal(statement, "setString", 16, data_inserimento_str);
console.log("BIND 16", data_inserimento_str);
console.log("STATEMENT", JSON.stringify(statement))
queryBind.executePazienti(statement, callback, res, req);
}
});
}
], function (err, results) {
// Results can also be processed here.
if (err) {
console.log(err)
logger.create_logFile(req, -1, err.message);
res.status(500).json({ "status": 0, "statusDescription": err.message });
} else {
console.log(results)
res.status(200).send({ "status": "1", "statusDescription": "Ok" });
l_message = "status:1, statusDescription: Ok";
logger.create_logFile(req, 1, l_message);
// Release the connection back to the pool.
oracledb.release(connObj, function (err) {
if (err) {
console.log(err.message);
res.status(500).json({ "status": 0, "statusDescription": err.message });
}
});
}
});
If I write above code(which is much more clean than nested if & else), I will get the error that I have mentioned at the beggining.
[Error: Error running instance method
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1937)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063)
at jdk.internal.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
] {
cause: nodeJava_java_sql_SQLException {},
level: 'error',
[Symbol(level)]: 'error'
}