[SQLite] Multiple connections can make use of same prepared statement.

[ingoogni]

SQLite prepared statements are tied to a single connection. In Nim it is possible to use a prepared statement from other connections than the one it was initialised for.

import db_sqlite, sqlite3

var
  k, v: string

let 
  dBFile = "test.db3"
  dB1 = open(dBFile, "", "", "")
  dB2 = open(dBFile, "", "", "")

dB1.exec(sql"PRAGMA journal_mode=wal;")
dB2.exec(sql"PRAGMA journal_mode=wal;")
dB1.exec(sql"""
  CREATE TABLE IF NOT EXISTS kv(
       k TEXT PRIMARY KEY,
       v TEXT NOT NULL
  ) WITHOUT ROWID;
  """
)

var psPut = dB1.prepare("INSERT INTO kv(k, v) VALUES (?, ?);")
psPut.bindParams(k, v)

k = "key one"
v = "value one"
dB1.exec(psPut, k, v)

# should not work but does.
k = "key two"
v = "value two"
dB2.exec(psPut, k, v)

db_sqlite.close(dB1)

# can not work and does not work
# because dB1 is closed and prepared
# statement was tied to it. No new
# data in the database.
# There is no Error generated.
k = "key three"
v = "value three"
dB2.exec(psPut, k, v) 

finalize(psPut)
db_sqlite.close(dB2)

A possible way out is adding to \Nim-1.4.8\lib\wrappers\sqlite3.nim :

proc sqlite3_db_handle*(para1: Pstmt): PSqlite3 {.cdecl,
  dynlib: Lib, importc: "sqlite3_db_handle".}

and to \Nim-1.4.8\lib\impure\db_sqlite.nim :

template exec*(
  stmtName: SqlPrepared,
  args: varargs[typed]
): untyped =
  let db = sqlite3_db_handle(stmtName.PStmt)
  when untypedLen(args) > 0:
    if reset(stmtName.PStmt) != SQLITE_OK:
      dbError(db)
    if clear_bindings(stmtName.PStmt) != SQLITE_OK:
      dbError(db)
    stmtName.bindParams(args)
  if not tryExec(db, stmtName): dbError(db)

(and deprecate the current exec template, as it gives the impression that an other connections can be used)

one could then do:

...
...

var psPut = dB1.prepare("INSERT INTO kv(k, v) VALUES (?, ?);")
psPut.bindParams(k, v)

k = "some key"
v = "some value"

psPut.exec(k, v)

finalize(psPut)
db_sqlite.close(dB1)