SQLite Forum

Dispatch Sqlite Instance with event loop
If you were to open a connection for each request and each request is completed by returning zero or more rows of data, you would do

1) connect to DB
2) prepare statement
3) bind parameters (if the statement has parameters)
4) step until SQLITE_DONE (or you have all the answers you want)
5) reset statement (if you did not step to completion)
6) finalize statement
7) disconnect

Note that there is no BEGIN and no COMMIT; SQLite implicitly does a BEGIN on the first step and a COMMIT when SQLITE_DONE is returned or the statement is reset. In a request that needs to execute more than one statement while seeing a consistent state, execute BEGIN before the first step and COMMIT after the last reset.

Larry suggested doing steps 2-6 within the work thread. That would allow the "return connection to pool" function to check for and finalize any statements that an erroneous work thread may have left open, which would result in maximum safety.

Preparing commonly used statements during pool initialization would require keeping the statement handles together with the connection handle as an element of the pool. The "return to pool" function would then reset "known statements" (an extra reset does not do any harm) and finalize unknown statements, implicitly ending any transaction.