emacsql-mysql - best way to close connection?

emacsql-mysql - best way to close connection?

[Guido Van Hoecke]

Hi,

I create a mysql connection in a let* form:

(let* ((host "mysql.host.com")
       (dummy (netrc-credentials host)) ;; needed to define remaining netrc stuff
       (info (netrc-machine (netrc-parse (expand-file-name "~/.netrc")) host))
       (id (cdr (assoc "login" info)))
       (pw (cdr (assoc "password" info)))
       (db (emacsql-mysql "database" :user id :password pw :host host)))
   ....)

Somehow I hoped that the connection would go away when the let* form
finishes but the connection does survive. The documentation at
[[https://github.com/skeeto/emacsql][EmacSQL]] does mention: 'It works
by maintaining a inferior process running (a "connection") for
interacting with the back-end database. Connections are automatically
cleaned up if they are garbage collected. All requests are synchronous.'

Each time the form is executed, a new connection is created. Typically
this happens once a day, but by the end of the week it accumulates 7
open connections :(

There's probably a better way to create and close connections, but up to
now I have not been able to find relevant documentation.

Please advise.

TIA,


--
Guido

  After Donald Trump's stretch limousine was stolen and found
  undamaged a few blocks away; he said, "Nothing was stolen. I had
  an honest thief."-International Herald Tribune, page 3, March 2,
  1992

Re: emacsql-mysql - best way to close connection?

[Guido Van Hoecke]

Hi,



On 1 April 2017 at 09:14, Guido Van Hoecke <guivho@gmail.com> wrote:

> Hi,
>
> I create a mysql connection in a let* form:
>
> (let* ((host "mysql.host.com")
>        (dummy (netrc-credentials host)) ;; needed to define remaining
> netrc stuff
>        (info (netrc-machine (netrc-parse (expand-file-name "~/.netrc"))
> host))
>        (id (cdr (assoc "login" info)))
>        (pw (cdr (assoc "password" info)))
>        (db (emacsql-mysql "database" :user id :password pw :host host)))
>    ....)
>
> Somehow I hoped that the connection would go away when the let* form
> finishes but the connection does survive. The documentation at
> [[https://github.com/skeeto/emacsql][EmacSQL]] does mention: 'It works
> by maintaining a inferior process running (a "connection") for
> interacting with the back-end database. Connections are automatically
> cleaned up if they are garbage collected. All requests are synchronous.'
>
> Each time the form is executed, a new connection is created. Typically
> this happens once a day, but by the end of the week it accumulates 7
> open connections :(
>
> There's probably a better way to create and close connections, but up to
> now I have not been able to find relevant documentation.
>

I added (delete-process "emacsql-mysql") as last elisp form of the let*
form. This does kill the process. Still I wonder if this is the way to go?

-- 
Guido

I fell asleep reading a dull book, and I dreamt that I was reading on,
so I woke up from sheer boredom.

Re: emacsql-mysql - best way to close connection?

[John Mastro]

Guido Van Hoecke <guivho@gmail.com> wrote:
>> I create a mysql connection in a let* form:
>>
>> (let* ((host "mysql.host.com")
>>        (dummy (netrc-credentials host)) ;; needed to define remaining netrc stuff
>>        (info (netrc-machine (netrc-parse (expand-file-name "~/.netrc")) host))
>>        (id (cdr (assoc "login" info)))
>>        (pw (cdr (assoc "password" info)))
>>        (db (emacsql-mysql "database" :user id :password pw :host host)))
>>    ....)
>>
> I added (delete-process "emacsql-mysql") as last elisp form of the let*
> form. This does kill the process. Still I wonder if this is the way to go?

It looks like there's a function `emacsql-close' for that.

If your code that uses the connection may raise an error (which would
cause evaluation to never reach the call to `emacsql-close' or similar),
then you may want to wrap it in `unwind-protect':

    (let* (...
           (db (emacsql-mysql "database" :user id :password pw :host host)))
      (unwind-protect
          (progn ...)
        (emacsql-close db)))

Where you code that uses the connection would go inside the `progn'.

There's also a macro `emacsql-with-connection' that encapsulates this
pattern: create a connection, evaluate some forms, then ensure
`emacsql-close' is called even in the event of an error.

        John

Re: emacsql-mysql - best way to close connection?

[Guido Van Hoecke]

> It looks like there's a function `emacsql-close' for that.
>
>
Well, somehow, (emacsql-close db) does not close the mysql connection, as
clearly indicated when executing (list-processes).


> If your code that uses the connection may raise an error (which would
> cause evaluation to never reach the call to `emacsql-close' or similar),
> then you may want to wrap it in `unwind-protect':
>
>     (let* (...
>            (db (emacsql-mysql "database" :user id :password pw :host
> host)))
>       (unwind-protect
>           (progn ...)
>         (emacsql-close db)))
>
> Where you code that uses the connection would go inside the `progn'.
>
>
So I'm sticking to my (delete-process "emacsql-mysql") hack, albeit wrapped
in an (unwind-protect) form thanks to your suggestion :)

-- 
Guido

"I would rather spend 10 hours reading someone else's source code than
10 minutes listening to Musak waiting for technical support which isn't."
(By Dr. Greg Wettstein, Roger Maris Cancer Center)