close prepared statement?

[Josh Kamau]

First question:

Let :  stmt, err := db.Prepare(.....) 

Should i call  defer stmt.Close() ?

Second question:

Is it ok to create several prepared statements at the beginning of a package and use them in the functions afterwords? e.g

package dao 

imports.....

var createStmt = db.Prepare(...);

var deleteStmt = db.Prepare(...);

func deleteRecord(...){

   deleteStmt.Exec(....)

} 

func createRecord(...){

  createStmt.QueryRow(....)

}

Thanks

Josh

[Konstantin Khomoutov]

On Thu, 4 Dec 2014 15:17:37 +0300
Josh Kamau <joshn...@gmail.com> wrote:

A bit of background: support for prepared statements might be
implemented in a particular driver in one of two major ways:

* If the database backend the driver interfaces actually supports
prepared statements, preparing a statement sends a certain command
(or commands) to the backend, which compiles the statement, creates
the execution plan for it etc and returns back something like
a handle on that statement.

The following executions of that statement will make the driver use
that handle when talking to the database backend. No statement text
is transferred when it's executed: just the handle and parameters,
if any, are passed to the database.

* If the database backend does not support prepared statements, the
driver might emulate them by parsing the statement itself and
then synthesizing the actual statement to send to the database
when the user executes the prepared statement -- properly escaping
the user-supplied parameters.

In either case, certain resources get allocated; in the real case
these are database engine resources.

> First question:
>
> Let : stmt, err := db.Prepare(.....)
>
> Should i call defer stmt.Close() ?

You should call stmt.Close() -- to free the resources discussed above.
If the database implements real prepared statements the Close() method
on the statement will send it a special command to deallocate anything
pertaining to the statement's handle.

Whether you do that via a deferred function call depends on your
requirements.

> Second question:
>
> Is it ok to create several prepared statements at the beginning of a
> package and use them in the functions afterwords? e.g
>
> package dao
>
> imports.....
>
> var createStmt = db.Prepare(...);
> var deleteStmt = db.Prepare(...);
>
> func deleteRecord(...){
> deleteStmt.Exec(....)
> }
>
> func createRecord(...){
> createStmt.QueryRow(....)
> }

That heavily depends on the package.

If it's a general-purpose ("go-gettable") package, I'd say no.
If it's an internal package used to properly structure the code,
you can do that but consider doing all the initialization in the
package's init() function instead, and even then there are things to
consider.

The main issue with this approach is that unless you're talking to an
embedded or in-memory database, you might experience network problems.
Now think what happens if there happens to be a temporary network
outage while your program is working? Say, execution of some statement
fails due to this. Now what do you do? It depends on the style of the
application: if you are OK with just bringing down the whole
application (possibly after doing some cleanup first), having
everything DB-related initialized up front is okay. But if your
application is some sort of a daemon/service which is supposed to be
resilient to transient outages of external resources, you're going to
wait for some time and try reconnecting to the database (maybe
gracefully failing the requests of your service users, if any, while
this happens). Now suppose you've reconnected after the timeout.
Since your original session is now gone, all resources allocated on it,
including prepared statements are now invalid. Hence should you
implement this approach, you might supposedly want to maintain the whole
"context" pertaining to the DB backend (the connection, prepared
statements etc) in a variable which can be reinitialized after an
outage happens.

[Josh Kamau]

Thanks Konstantin for taking your time and giving such a comprehensive answer. I now understand.

Josh

[Luis Furquim]

In this scenario, what is the correct behavior?

A) the database/sql package does automatic reconnection and I

just need to detect that the *sql.Stmt handles are invalid and 

rePrepare them; or

B) I need to detect that the *sql.Stmt handles are invalid and I

need to cal sql.Open again and after that rePrepare the *sql.Stmt

handles?

Thank you

Luis Otávio Furquim