Error executing delete sql when no rows are deleted

[snotmare]

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...

1. Execute a delete statement to delete a row that does exist (delete works)
2. 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!

[snotmare]

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.

[markirish]

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?

[harsh-savvient]

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 = 'h.shah@savvient.com.au'";

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?

[snotmare]

@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.

[snotmare]

@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);
    }
}

[harsh-savvient]

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 = h.shah@savvient.com.au';
            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: [] }

[snotmare]

@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.

[harsh-savvient]

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 = 'h.shah@savvient.com.au'`;
          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: [] }

[snotmare]

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.