Nim: Module `db_sqlite` doesn't finalize statements with db_sqlite.rows after breaking the iterator's loop

[ghost]

Hi,

I'm using nim 0.17.2 and every time I try to close a database connection with close() I get this error:

unable to close due to unfinalized statements or unfinished backups

It's probably related to this SO problem.

[Araq]

Please provide an example program demonstrating the problem. We've been using this wrapper in production for years.

[ghost]

When I tried to create a minimal example I've realized it only happens with db.rows. My problem was that I called break in the for loops and it seems like db_sqlite doesn't call the finalize because it doesn't fetch the whole data first(?).
The docs says this about fastRows():

Breaking the fastRows() iterator during a loop will cause the next database query to raise a DbError exception unable to close due to ....

but for rows() it says it's safe:

same as FastRows, but slower and safe.

but it isn't safe.

[ghost]

So, minimal example:

import db_sqlite

const table1 = ("""
create table if not exists table1(
  id integer primary key,
  num integer not null
);""")

proc a(q: string, i: int): void =
  let db = open("d.db", nil, nil, nil)
  if i > 0:
    for row in db.rows(sql(q), i):
      echo(row)
      break
  else:
    db.exec(sql(q))
  db.close()

a(table1, 0)
a("insert into table1(num) values(1);", 0)
a("insert into table1(num) values(2);", 0)
a("select * from table1 where num >= ?;", 1)

Output:

@[1, 1]
Traceback (most recent call last)
main.nim(22)             main
main.nim(17)             a
db_sqlite.nim(297)       close
db_sqlite.nim(104)       dbError
Error: unhandled exception: unable to close due to unfinalized statements or unfinished backups [DbError]
****

[define-private-public]

I'd like to confirm that I'm also hitting this error on 0.19.2. This was in the context though of exec() first throwing a DbError, handling it, then outside of that block trying to close the connection of the database.

I can provide some sample code if requested, but it might be a bit.

[Araq]

Easy to fix, needs more try finally statements. :-)

[define-private-public]

I used choosenim to update to #head to see if the fix worked; It didn't for me. Here is a snippet of the code I was using to trip the error:

import db_sqlite


# Open the database
let db = open("asdf.db", "", "", "")

# Add a table
db.exec(sql"CREATE TABLE Asdf(value TEXT NOT NULL UNIQUE);")

# Insert some good values
db.exec(sql"""
INSERT INTO Asdf (value)
VALUES           ('abc'),
                 ('123');
""")

# Do a bad update
try:
  db.exec(sql"""
  UPDATE Asdf
  SET value = 'abc'
  WHERE value = '123';
  """)
except DbError:
  # This should be hit because the colum `value` must be unique
  echo("Tried to do an invalid update")

# close, throws an error here
db.close()

And here is the exact output:

Tried to do an invalid update
sqlite_fail.nim(29)      sqlite_fail
db_sqlite.nim(305)       close
db_sqlite.nim(103)       dbError
Error: unhandled exception: unable to close due to unfinalized statements or unfinished backups [DbError]
Error: execution of an external program failed: '/home/ben/Desktop/tmp/sqlite_fail '

[Araq]

Well the example worked for me, now you use a different snippet, make up your mind. ;-)

[kidandcat]

This is still happening, probably due to: https://stackoverflow.com/questions/373369/sqlite3unable-to-close-due-to-unfinalised-statements

I'm updating NimForum to compile with latest Nim version and this is a showstopper

[itsumura-h]

If query failed, finalize() doesn't run.
https://github.com/nim-lang/Nim/blob/version-1-0/lib/impure/db_sqlite.nim#L187

It should be changed like bellow

if r == SQLITE_OK:
    let x = step(stmt)
    if x in {SQLITE_DONE, SQLITE_ROW}:
      result = finalize(stmt) == SQLITE_OK
    else:
      discard finalize(stmt)
      return false