Connect cached does not reconnect with sqlite

[lucashpandolfo]

When using connect-cached, after a disconnect the next connect-cached returns the previous connection but because of the disconnect the handle slot is unbound.

CL-USER> (let ((conn (dbi:connect-cached :sqlite3 :database-name "/tmp/test.db")))
  (let* ((query (dbi:prepare conn "PRAGMA stats"))
         (result (dbi:execute query)))
    (loop for row = (dbi:fetch result)
       while row
       do (format t "~A~%" row)))
  (dbi:disconnect conn))

"(table sqlite_master index NIL width 65 height 200)"
=> #<SQLITE:SQLITE-HANDLE {1006E2ED43}>

CL-USER> (let ((conn (dbi:connect-cached :sqlite3 :database-name "/tmp/test.db")))
  (let* ((query (dbi:prepare conn "PRAGMA stats"))
         (result (dbi:execute query)))
    (loop for row = (dbi:fetch result)
       while row
       do (format t "~A~%" row)))
  (dbi:disconnect conn))
=> The slot SQLITE::HANDLE is unbound in the object

The connections remain cached and is not detected by connect-cached because ping only checks if the sqlite db file exists not if the connection is valid (ie: handle is bound).

Relevant code:

• connect-cached doing a ping
• sqlite unbounding the handle
• sqlite ping

[rmhsilva]

@fukamachi, any idea when/if this will be fixed? Are you welcoming PRs?
Thanks!

[svetlyak40wt]

@rmhsilva @lucashpandolfo seems you just don't have to call disconnect manually. Idea of the cached-connection is that your connection reused between queries. Pool is an abstraction which gives you one connection per thread, to make database queries threadsafe.

Cl-dbi will

cl-dbi/src/dbi.lisp

Lines 118 to 121 in 35b9d68

	(unless (bt:thread-alive-p thread)
	(maphash (lambda (args conn)
	(declare (ignore args))
	(disconnect conn))

call disconnect after the thread will exit.