markdirish
Error executing delete sql when no rows are deleted
Hello!
I have odbc 2.4.8 installed and have noticed an error when deleting data. I believe I should NOT be receiving this error.
The error occurs when executing a delete statement and no rows are actually deleted. For example...
- Execute a delete statement to delete a row that does exist (delete works)
- Run the same exact statement to attempt to delete the now-deleted row (fails with an error)
I think odbc should NOT throw an error, but instead simply return a count of 0 since no rows were deleted. Here is a screen shot of the error I received. Notice that the odbcErrors array is empty.
Thanks all!
After more testing, I found out the update and execute behaves the same way. If I run an update that results in 0 rows being updated, it throws an error the same way as described above.
Hi @snotmare ,
I am trying to recreate but not having any luck. I created a table with 2 columns, added an entry, then double delete it with:
let result = await connection.query('DELETE FROM MIRISH.ODBCISSUE389 WHERE ID = 1');
console.log(result);
result = await connection.query('DELETE FROM MIRISH.ODBCISSUE389 WHERE ID = 1');
console.log(result);
When I run it, I get the following:
[
statement: 'DELETE FROM MIRISH.ODBCISSUE389 WHERE ID = 1',
parameters: [],
return: undefined,
count: 1,
columns: []
]
[
statement: 'DELETE FROM MIRISH.ODBCISSUE389 WHERE ID = 1',
parameters: [],
return: undefined,
count: 0,
columns: []
]
Is there something different you are doing that I could try?
try { const pool = odbc.pool('DSN=Sample', (error, pool) => { // pool now has open connections if (error) { console.log('Error creating connection pool:', error); return; // handle }
// Ensure the email is properly quoted in the SQL query
const query = "SELECT * FROM Sample.JobUser WHERE Email = '[email protected]'";
pool.query(query, (error2, result) => {
if (error2) {
console.log('Error executing the SQL query:', error2);
return; // handle
}
console.log('Query result:', result);
});
}); } catch (error) { console.log('Unexpected error:', error); } }
Error executing the SQL query: [Error: [odbc] Error executing the sql statement] { odbcErrors: [] }
@snotmare I am also getting same error with insert, update and select queries. Could you please help me to fix this issue?
@markdirish thanks for the response!
It looks like you're using the query() method to execute your SQL, I didn't realize you could do that. When I try your way, I get the same results (it works). Here is the code I'm running to produce the error...
let sql = `delete from gppuser where rcid = 9999999`;
let statement = await connection.createStatement();
await statement.prepare(sql);
let result = await statement.execute();
// let result = await connection.query(sql);
console.log(result);
With an update...
let sql = `update gppuser set fnam = 'test' where rcid = 9999999`;
let statement = await connection.createStatement();
await statement.prepare(sql);
let result = await statement.execute();
// let result = await connection.query(sql);
console.log(result);
I would prefer to use a prepared statement over the query method because we use parameter binding.
@harsh-savvient When I'm using a pool, I will use the .aquire() and .release() methods to get a connection and execute on that connection rather than the pool itself. Maybe that will help you?
let connection;
try {
connection = await this.pool.acquire();
let results = await connection.query(query);
//Do things
} catch(error) {
//Handle error
} finally {
if(connection) {
this.pool.release(connection);
}
}
connection
TypeError: pool.acquire is not a function
Please check the following updated code.
try {
const connectionString = 'DSN=Sample';
odbc.pool(connectionString, (error1, pool) => {
if (error1) {
console.log('Error executing the odbc connection:', error1);
return;
} // handle
pool.connect((error2, connection) => {
if (error2) {
console.log('Error executing the pool connection:', error2);
return;
} // handle
// now have a Connection to do work with
// Ensure the email is properly quoted in the SQL query
const sql = 'SELECT * FROM Sample.JobUser WHERE Email = [email protected]';
connection.query(sql, (error3, result) => {
if (error3) {
console.log('Error executing the SQL query:', error3);
return; // handle
}
console.log('Query result:', result);
});
});
});
} catch (error) {
console.log('Unexpected error:', error);
}
I am still getting the same error. Could you please help me with this? @snotmare
Error executing the SQL query: [Error: [odbc] Error executing the sql statement] { odbcErrors: [] }
@harsh-savvient Ah, I'm sorry, we're actually using a different pool library rather than the built-in one for odbc. We're using generic-pool.
Either way, try your code with a connection object instead of a pool object and see if that makes a difference.
connection @snotmare @markdirish @bbigras @theduderog https://www.npmjs.com/package/odbc I am using odbc npm package and facing same issue with connection object.
Please check the following code block:
async function connectToDatabase() {
try {
const connectionString = "DSN=Sample";
odbc.connect(connectionString, (error, connection) => {
if (error) {
console.log("Error executing the odbc connection:", error);
return;
} // handle
// now have a Connection to do work with
// Ensure the email is properly quoted in the SQL query
const sql = `SELECT * FROM Sample.JobUser WHERE Email = '[email protected]'`;
connection.query(sql, (error2, result) => {
if (error2) {
console.log("Error executing the SQL query:", error2);
return; // handle
}
console.log("Query result:", result);
});
});
} catch (error) {
console.log("Unexpected error:", error);
}
}
Error
Error executing the SQL query: [Error: [odbc] Error executing the sql statement] { odbcErrors: [] }
Hello @markdirish ! Just checking in... was my last response to you helpful in recreating the issue? Is there more information that I can provide to help?
Perhaps the issue is a difference between using the .query() method and a prepared statement.