Asynchronous ODBC

[Michael FIG]

Hi all,

I'm new to (but falling in love with) Erlang, and I'm interested in porting a medium-sized project that is a soft realtime system.  It took a lot of work to get the database interactions correct, due to the latency of the database connection.  Eventually, we settled on a caching asynchronous database system that used a message queue for multiple requests.  Other constraints forced us to make the production system multithreaded, but I am hoping to get away from that in the Erlang implementation.

Looking at odbc.erl, everything is written in a synchronous style, so it appears the only way to use it asynchronously is to write an asynchronous wrapper for it and run the wrapper on another node so that when it blocks during each call() it doesn't block the caller of the wrapper.

I can think of a couple of ways to modify the odbc.erl code to allow asynchronous calls (whose results get sent to a user-specified pid, maybe with a ref that can be used to match up replies to requests), but I wanted to write here first to see if there is any advice for solving this problem, or anybody who's come up against it before.  It is quite important to me that this solution also allow for multiple ODBC connections within the same Erlang node, none of which would block the other.

Thanks in advance for any help you can offer,

--
Michael FIG <[hidden email]>, PMP
VP Technology
MarkeTel Multi-Line Dialing Systems, Ltd.
Phone: [phone] ext. 528

[Bengt Kleberg]

On 2006-05-31 04:35, Michael FIG wrote:
...deleted
> Looking at odbc.erl, everything is written in a synchronous style, so it appears the only way to use it asynchronously is to write an asynchronous wrapper for it and run the wrapper on another node so that when it blocks during each call() it doesn't block the caller of the wrapper.

imho it would be sufficient to have the wrapper in another process
(created by erlang:spawn/1). there is no need to involve another node.
please accept my apology if i have misunderstood something.


bengt
--
    EPO giudelines 1978: "If the contribution to the known art resides
    solely in a computer program then the subject matter is not
    patentable in whatever manner it may be presented in the claims."

[Michael McDaniel]

On Wed, May 31, 2006 at 10:09:38AM +0200, Bengt Kleberg wrote:

> On 2006-05-31 04:35, Michael FIG wrote:
> ...deleted
> >Looking at odbc.erl, everything is written in a synchronous style, so it
> >appears the only way to use it asynchronously is to write an asynchronous
> >wrapper for it and run the wrapper on another node so that when it blocks
> >during each call() it doesn't block the caller of the wrapper.
>
> imho it would be sufficient to have the wrapper in another process
> (created by erlang:spawn/1). there is no need to involve another node.
> please accept my apology if i have misunderstood something.
>
>
> bengt
> --
>    EPO giudelines 1978: "If the contribution to the known art resides
>    solely in a computer program then the subject matter is not
>    patentable in whatever manner it may be presented in the claims."

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I agree with bengt (with, apparently, the same understanding of your
question).

Here is an extract of code I use in a production system which works fine for the
last year ...

----------
-module(x).
-export([x/0]).


funA(Req)

 spawn_link(dbexp, callComplete, [Req#crec.callid]) ,

 ...

.
---------
-module(dbexp).
-export([callComplete/1]).

callComplete(CallId) ->
% used from command line for testing or as
% spawn(dbexp, callComplete, [CallId]) for background updates
%
  Ref = ?MODULE:conn(1000) ,    %% wrapper for odbc:connect/2
  Z = "update callinfo set complete_ts=NOW() where callid = " ++
      integer_to_list(CallId) ++ " ;" ,

  case odbc:sql_query(Ref, Z) of
    {updated, Return} -> odbc:disconnect(Ref), {updated, Return} ;
    {error, Error}    -> odbc:disconnect(Ref),
                       expmaster:log_error(?MODULE, ?LINE,
                 'dbexp:callComplete,odbc:sql_query', Error), {error, Error}
end
.
----------

~Michael

[Michael FIG]

Hi,

>> imho it would be sufficient to have the wrapper in another process
>> (created by erlang:spawn/1). there is no need to involve another node.
>> please accept my apology if i have misunderstood something.
>
>I agree with bengt (with, apparently, the same understanding of your
>question).

Ahh.  I think I see now.  I was under the mistaken understanding that all other processes on a node block during gen_server:call.  Now I think I understand that any "receive", no matter how deeply nested, allows other processes to run.  So then the "spawn"ed wrapper just insulates the caller from the process that blocks during gen_server:call.

Is that correct?  If so, then that's really good, and finally an escape from the nuisances of event-driven programming (my experience with Visual Basic 6, where DoEvents was truly horrid... you'd either reenter your code or blow up the stack if you aren't careful).

Nice.

--
Michael FIG <[hidden email]>, PMP
VP Technology
MarkeTel Multi-Line Dialing Systems, Ltd.
Phone: [phone] ext. 528

[Michael McDaniel]

Basically, spawned processes run independently (with certain caveats according
to how it was started (spawn, spawn_link, etc.) and child/parent
relationships).  If I do not need direct return information from a slow fun()
then I will spawn it and let it do its thing in the background.  


For more details of operation, see the 'Gettting Started' section of the
Erlang docs, particularly section 3, Concurrent Programming.

Here's a good spot to see: http://www.erlang.org/starting.html


On my system with documentation installed, I point my browser here:

   file:///usr/local/lib/erlang/doc/getting_started/part_frame.html


~Michael



On Wed, May 31, 2006 at 02:17:14PM -0600, Michael FIG wrote:

> Hi,
>
> >> imho it would be sufficient to have the wrapper in another process
> >> (created by erlang:spawn/1). there is no need to involve another node.
> >> please accept my apology if i have misunderstood something.
> >
> >I agree with bengt (with, apparently, the same understanding of your
> >question).
>
> Ahh.  I think I see now.  I was under the mistaken understanding that all other processes on a node block during gen_server:call.  Now I think I understand that any "receive", no matter how deeply nested, allows other processes to run.  So then the "spawn"ed wrapper just insulates the caller from the process that blocks during gen_server:call.
>
> Is that correct?  If so, then that's really good, and finally an escape from the nuisances of event-driven programming (my experience with Visual Basic 6, where DoEvents was truly horrid... you'd either reenter your code or blow up the stack if you aren't careful).
>
> Nice.
>
> --
> Michael FIG <[hidden email]>, PMP
> VP Technology
> MarkeTel Multi-Line Dialing Systems, Ltd.
> Phone: [phone] ext. 528
>

--
Michael McDaniel
Portland, Oregon, USA
[phone]
http://autosys.us