managing connecting/disconnecting with odbc

[Steve Prior]

I'm trying to figure out the best way to manage odbc database connections in my
system and I haven't been happy with what I've come up with so far.  Below is a
generic version of my code.  What I'd like is for the other parts of my code to
be able to call  db_query(Column) bound as a test, but also unbound and able to
be backtracked through to return multiple answers.  The problem of course is
that once you go through the disconnect you can't backtrack into the query again
for the next value.

I'd like to be able to call db_query(C). from the SWI command line, have it
return a value, then be able to use ';' to find the next result, but it seems
that I've have to expose the detail of managing the db connection to do so.  It
seems like I'm looking for the ability to register a callback for the db_query
predicate that always runs the disconnect_db code when we know that we're not
going to backtrack through it anymore.


db_query(Column):-
    connect_db(C),
    bare_db_query(C,Column),
    disconnect_db(C).

bare_db_query(C, Column):-
    odbc_query(C,'SELECT * from my_table', row(Column)).

connect_db(C) :-
    odbc_connect('dbname', C, [ user(dbuser), password(dbpassword),
    open(multiple) ]).

disconnect_db(C) :-
    odbc_disconnect(C).

Has anyone come up with a better way of dealing with this?

Steve

_______________________________________________
SWI-Prolog mailing list
SWI-Prolog@...
https://mailbox.iai.uni-bonn.de/mailman/listinfo.cgi/swi-prolog

[Steve Prior]

Steve Prior wrote:

> I'm trying to figure out the best way to manage odbc database
> connections in my system and I haven't been happy with what I've come up
> with so far.  Below is a generic version of my code.  What I'd like is
> for the other parts of my code to be able to call  db_query(Column)
> bound as a test, but also unbound and able to be backtracked through to
> return multiple answers.  The problem of course is that once you go
> through the disconnect you can't backtrack into the query again for the
> next value.
>
> I'd like to be able to call db_query(C). from the SWI command line, have
> it return a value, then be able to use ';' to find the next result, but
> it seems that I've have to expose the detail of managing the db
> connection to do so.  It seems like I'm looking for the ability to
> register a callback for the db_query predicate that always runs the
> disconnect_db code when we know that we're not going to backtrack
> through it anymore.
>
>
> db_query(Column):-
>    connect_db(C),
>    bare_db_query(C,Column),
>    disconnect_db(C).
>
> bare_db_query(C, Column):-
>    odbc_query(C,'SELECT * from my_table', row(Column)).
>
> connect_db(C) :-
>    odbc_connect('dbname', C, [ user(dbuser), password(dbpassword),
>    open(multiple) ]).
>
> disconnect_db(C) :-
>    odbc_disconnect(C).
>
> Has anyone come up with a better way of dealing with this?

I just came across setup_call_cleanup/3 as a possible solution to do what I
want.  A question about the documentation:

"Note that it is impossible to implement this predicate in Prolog other than by
reading all terms into a list, close the file and call member/2."

Does this mean that setup_call_cleanup does in fact read all terms into a list,
close the file and call member/2, or does that mean it would have to do those
things if it were implemented in the Prolog language, but actually it's
implemented in C so it isn't implemented that way?

Steve

_______________________________________________
SWI-Prolog mailing list
SWI-Prolog@...
https://mailbox.iai.uni-bonn.de/mailman/listinfo.cgi/swi-prolog

[Ulrich Neumerkel]

I just came across setup_call_cleanup/3 as a possible solution to do what I
>want.  A question about the documentation:
>
>"Note that it is impossible to implement this predicate in Prolog other than by
>reading all terms into a list, close the file and call member/2."
>
>Does this mean that setup_call_cleanup does in fact read all terms into a list,
>close the file and call member/2, or does that mean it would have to do those
>things if it were implemented in the Prolog language, but actually it's
>implemented in C so it isn't implemented that way?

It should read rather like:

|Note that it is impossible to implement this predicate in Prolog.  The
|closest approximation would be to read all terms into a list, close
|the file and call member/2.

There is no way to implement setup_call_cleanup/3 in Prolog directly
(except you do everything by meta-interpretation).

Please note also, that call_cleanup/2 should rather not be used at
all.  Asynchronous interrupts that happen between opening the
file/connection and the installation of the cleanup handler would
produce memory or resource leakage.

Thank for pointing this out.
_______________________________________________
SWI-Prolog mailing list
SWI-Prolog@...
https://mailbox.iai.uni-bonn.de/mailman/listinfo.cgi/swi-prolog

[Jan Wielemaker-3]

Hi Ulrich,

Thanks for the patch on the docs. As you know (but many other not), the
best way to get patches through is by submitting them as GIT patches.
This reminds me I should write a page for the website on this issue.

        Cheers --- Jan

On Tuesday 23 June 2009 11:44:45 am Ulrich Neumerkel wrote:

> I just came across setup_call_cleanup/3 as a possible solution to do what I
>
> >want.  A question about the documentation:
> >
> >"Note that it is impossible to implement this predicate in Prolog other
> > than by reading all terms into a list, close the file and call member/2."
> >
> >Does this mean that setup_call_cleanup does in fact read all terms into a
> > list, close the file and call member/2, or does that mean it would have
> > to do those things if it were implemented in the Prolog language, but
> > actually it's implemented in C so it isn't implemented that way?
>
> It should read rather like:
> |Note that it is impossible to implement this predicate in Prolog.  The
> |closest approximation would be to read all terms into a list, close
> |the file and call member/2.
>
> There is no way to implement setup_call_cleanup/3 in Prolog directly
> (except you do everything by meta-interpretation).
>
> Please note also, that call_cleanup/2 should rather not be used at
> all.  Asynchronous interrupts that happen between opening the
> file/connection and the installation of the cleanup handler would
> produce memory or resource leakage.
>
> Thank for pointing this out.
> _______________________________________________
> SWI-Prolog mailing list
> SWI-Prolog@...
> https://mailbox.iai.uni-bonn.de/mailman/listinfo.cgi/swi-prolog

_______________________________________________
SWI-Prolog mailing list
SWI-Prolog@...
https://mailbox.iai.uni-bonn.de/mailman/listinfo.cgi/swi-prolog