Building a database interface in Emacs

Building a database interface in Emacs

[Mathias Dahl]

I have been looking into using Emacs for doing queries etc against an
Oracle database, for certain work purposes. I use the nice functions
in sql.el (M-x sql-oracle in my case) for doing ad-hoc queries and
such, but I want to build some small forms or similar to process
certain data, with the intent of replacing some of the boring and
heavy GUI apps we have.

At first I just want to see if it is possible to do, by making up some
simple form to browse records or similar.

What would be the best approach for doing this? First I tried adding
something on top of sql.el and comint.el but it seemed overkill as I
am not interested in a command line interface. My current plan is to
use `start-process' to start sqlplus in a process, and to send input
and parse the output I get back from that process.

Is there any smarter way of doing it?

I want to do this on w32, btw.

/Mathias

Re: Building a database interface in Emacs

[aartist]

You might want to look at Widget and Skeleton.
M-x Customize-browse is one classic example, you might want to follow.


C-h i m widget
Mathias Dahl wrote:
> I have been looking into using Emacs for doing queries etc against an
> Oracle database, for certain work purposes. I use the nice functions
> in sql.el (M-x sql-oracle in my case) for doing ad-hoc queries and
> such, but I want to build some small forms or similar to process
> certain data, with the intent of replacing some of the boring and
> heavy GUI apps we have.
>
> At first I just want to see if it is possible to do, by making up some
> simple form to browse records or similar.
>
> What would be the best approach for doing this? First I tried adding
> something on top of sql.el and comint.el but it seemed overkill as I
> am not interested in a command line interface. My current plan is to
> use `start-process' to start sqlplus in a process, and to send input
> and parse the output I get back from that process.
>
> Is there any smarter way of doing it?
> 
> I want to do this on w32, btw.
> 
> /Mathias

Re: Building a database interface in Emacs

[Mathias Dahl]

"aartist" <aartist@gmail.com> writes:

> You might want to look at Widget and Skeleton.  M-x Customize-browse
> is one classic example, you might want to follow.

I am afraid I did not express myself clearly enough; I am not so
interested in the actual building of a form or similar, what I want to
know is what the best way is to communicate with the database.

I have read up on receiving data from an asynchronous process and
there is one thing I have trouble with, how do I send input to the
process and wait until I get output back, from the same function?

An example:

(defun foo ()
 (process-send-string process "blabla")
 (wait-for-and-get-result)
 (use-the-result))

I can attach a process filter function to the process but I don't
understand how to "pause" my main code until my filter function has
been called. Well, I came up with a hack, that seems really ugly:

(defvar foo-output nil)

(defun foo ()
 (process-send-string process "blabla")
 (setq foo-output nil)
 (while (not foo-output)
   (sleep-for 0 10))
 (use-result))

(defun foo-filter (proc string)
 (setq foo-output string))

There must be a neater way to do this, right?

/Mathias

Re: Building a database interface in Emacs

[Mathias Dahl]

Mathias Dahl <brakjoller@gmail.com> writes:

> I can attach a process filter function to the process but I don't
> understand how to "pause" my main code until my filter function has
> been called. Well, I came up with a hack, that seems really ugly:
>
> (defvar foo-output nil)
>
> (defun foo ()
>  (process-send-string process "blabla")
>  (setq foo-output nil)
>  (while (not foo-output)
>    (sleep-for 0 10))
>  (use-result))
>
> (defun foo-filter (proc string)
>  (setq foo-output string))

And I just discovered that even the above won't work because I receive
data in chunks. I guess the question I am asking is: how to I know
when there is no more output? Maybe I need to look for signs in the
output itself?

Re: Building a database interface in Emacs

[aartist]

SQL : count(*)  for number of rows may help.
Mathias Dahl wrote:
> Mathias Dahl <brakjoller@gmail.com> writes:
>
> > I can attach a process filter function to the process but I don't
> > understand how to "pause" my main code until my filter function has
> > been called. Well, I came up with a hack, that seems really ugly:
> >
> > (defvar foo-output nil)
> >
> > (defun foo ()
> >  (process-send-string process "blabla")
> >  (setq foo-output nil)
> >  (while (not foo-output)
> >    (sleep-for 0 10))
> >  (use-result))
> >
> > (defun foo-filter (proc string)
> >  (setq foo-output string))
>
> And I just discovered that even the above won't work because I receive
> data in chunks. I guess the question I am asking is: how to I know
> when there is no more output? Maybe I need to look for signs in the
> output itself?

Re: Building a database interface in Emacs

[Mathias Dahl]

"aartist" <aartist@gmail.com> writes:

> SQL : count(*)  for number of rows may help.

Good idea! Currently my approach is to look for the "SQL>" prompt
though.

Re: Building a database interface in Emacs

[Lennart Borgman]

Mathias Dahl wrote:
> Mathias Dahl <brakjoller@gmail.com> writes:
>
>   
>> I can attach a process filter function to the process but I don't
>> understand how to "pause" my main code until my filter function has
>> been called. Well, I came up with a hack, that seems really ugly:
>>
>> (defvar foo-output nil)
>>
>> (defun foo ()
>>  (process-send-string process "blabla")
>>  (setq foo-output nil)
>>  (while (not foo-output)
>>    (sleep-for 0 10))
>>  (use-result))
>>
>> (defun foo-filter (proc string)
>>  (setq foo-output string))
>>     
>
> And I just discovered that even the above won't work because I receive
> data in chunks. I guess the question I am asking is: how to I know
> when there is no more output? Maybe I need to look for signs in the
> output itself?
>   

Perhaps these helps?

   (info "(elisp) Filter Functions")
   (info "(elisp) Accepting Output")
   (info "(elisp) Sentinels")

Re: Building a database interface in Emacs

[Mathias Dahl]

Lennart Borgman <lennart.borgman.073@student.lu.se> writes:

> Perhaps these helps?
>
>   (info "(elisp) Filter Functions")
>   (info "(elisp) Accepting Output")
>   (info "(elisp) Sentinels")

Believe me, I have read those. More than once... :)

The current approach seems to work quite well though. In my process
filter I check for the SQL prompt and as soon as it is not seen I
append all output to a global variable that I later use.

Re: Building a database interface in Emacs

[Magnus Henoch]

Mathias Dahl <brakjoller@gmail.com> writes:

> (defvar foo-output nil)
>
> (defun foo ()
>  (process-send-string process "blabla")
>  (setq foo-output nil)
>  (while (not foo-output)
>    (sleep-for 0 10))
>  (use-result))
>
> (defun foo-filter (proc string)
>  (setq foo-output string))
>
> There must be a neater way to do this, right?

Have you seen (info "(elisp) Transaction Queues") ?  If I understand
the interface of SQLPlus correctly, this is exactly what you want.

You might find pg.el (interface for postgresql) interesting:
http://www.online-marketwatch.com/pgel/pg.html

Magnus

Re: Building a database interface in Emacs

[Mathias Dahl]

Magnus Henoch <mange@freemail.hu> writes:

> Have you seen (info "(elisp) Transaction Queues") ?  If I understand
> the interface of SQLPlus correctly, this is exactly what you want.

Yes I saw that, and at first it seemed promising, but then I saw
something that made me dismiss it. However, I took a look at it again
and it might actually work... :)

Re: Building a database interface in Emacs

[jason haslup]

Mathias Dahl <brakjoller <at> gmail.com> writes:

> 
> I have been looking into using Emacs for doing queries etc against an
> Oracle database, for certain work purposes. I use the nice functions
...
> What would be the best approach for doing this? First I tried adding
> something on top of sql.el and comint.el but it seemed overkill as I
> am not interested in a command line interface. My current plan is to
> use `start-process' to start sqlplus in a process, and to send input
> and parse the output I get back from that process.
> 
> Is there any smarter way of doing it?

I did something using comint's filter functions, but it requires having a *SQL*
buffer open to the database you're interested in:

  http://www.haslup.com/index.cgi/emacs/2006/12/20/

I'm not too familiar with sqlplus, but I know the mysql client has a raw mode
and also an XML mode for queries that might help in looking for the end of a
query's results.  Or, if sqlplus doesn't work, I wonder if a simple helper
client using oracle's libraries would be something you could use with
start-process.  Then you could have complete control over the output of the data?

jason

Re: Building a database interface in Emacs

[Mathias Dahl]

jason haslup <jason@haslup.com> writes:

>> Is there any smarter way of doing it?
>
> I did something using comint's filter functions, but it requires
> having a *SQL* buffer open to the database you're interested in:
>
>   http://www.haslup.com/index.cgi/emacs/2006/12/20/

Nice! There are probably some ideas worth checking up in there.

> I'm not too familiar with sqlplus, but I know the mysql client has a
> raw mode and also an XML mode for queries that might help in looking
> for the end of a query's results.

Yes, sqlplus has an option to output things as HTML. That might makes
things easier to parse as well as easier to see when the output is
done.

> Or, if sqlplus doesn't work, I wonder if a simple helper client
> using oracle's libraries would be something you could use with
> start-process.

Yes, using OCI would be the best I guess, but I am too lazy and do not
have experience fiddling with that.

My experiements are working OK, now I just have to figure out what the
heck I *really* want to use it for... :)

Re: Building a database interface in Emacs

[Hadron Quark]

Mathias Dahl <brakjoller@gmail.com> writes:

> jason haslup <jason@haslup.com> writes:
>
>>> Is there any smarter way of doing it?
>>
>> I did something using comint's filter functions, but it requires
>> having a *SQL* buffer open to the database you're interested in:
>>
>>   http://www.haslup.com/index.cgi/emacs/2006/12/20/
>
> Nice! There are probably some ideas worth checking up in there.
>
>> I'm not too familiar with sqlplus, but I know the mysql client has a
>> raw mode and also an XML mode for queries that might help in looking
>> for the end of a query's results.
>
> Yes, sqlplus has an option to output things as HTML. That might makes
> things easier to parse as well as easier to see when the output is
> done.
>
>> Or, if sqlplus doesn't work, I wonder if a simple helper client
>> using oracle's libraries would be something you could use with
>> start-process.
>
> Yes, using OCI would be the best I guess, but I am too lazy and do not
> have experience fiddling with that.
>
> My experiements are working OK, now I just have to figure out what the
> heck I *really* want to use it for... :)

Create a simple customer loyalty interface : have a product table, a
customer table and a purchase history table and log the purchases over a
session and over a period. If you get anywhere with it, I would be
interested in collaberating. I was kind of thinking of doing something
similar to provide a simple text interface on a small monitor for a
friends shop.



--