[plt-scheme] PLT and Database access

> On the plus side it works nicely, 
Glad to hear it.

> and on the minus side, every once in a while I found myself having
> to contort values I wanted to store so the library could convert
> them to database values properly,

Please send me some examples if possible, maybe there's something that
can be improved.

> and I gave up using the macro language that came with the system
> because it wasn't really expressive enough for the queries I needed.

Yeah, it wasn't originally meant to be a complete mirror of SQL; it
was supposed to assist in the construction of common SQL statements.
It's really helpful if you want to have "Scheme-style" column names,
like first-name, PostgreSQL requires such columns to be double-quoted,
which quickly gets you mired in backslashes.  For more complex
statements, one can always make use of the fact that Scheme-PG
procedures such as execute-sql and result-open consume strings, (and
obviously the SQL macros generate strings) e.g. you can use the macro
language and write:

(result-open conn (select all (pers) (where (like name "M%")))

or, just write

(result-open conn "SELECT * FROM pers WHERE name LIKE 'D%'")

or you can mix the two:

(select all (pers) "WHERE name LIKE 'M%'")

However, the macro language is getting quite a bit of use on a project
that I'm working on, and the parts of SQL that it doesn't handle are
coming into play, so I think that you're correct that it could use
some updating.  Any suggestions would be very welcome!

Cheers,
David


On Fri, May 27, 2005 at 12:49:58PM -0500, Jacob Matthews wrote:
> David J. Neu wrote:
> 
> >For PostgreSQL, you can try (shameless self plug) Scheme-PG,
> >http://scheme-pg.sourceforge.net/.  We use it everyday.
> > 
> >
> I use Scheme-PG to communicate with postgresql from a web server. I 
> found it was the best option for what I wanted. On the plus side it 
> works nicely, and on the minus side, every once in a while I found 
> myself having to contort values I wanted to store so the library could 
> convert them to database values properly, and I gave up using the macro 
> language that came with the system because it wasn't really expressive 
> enough for the queries I needed. Once I got the hang of the library's 
> quirks, though, it worked fine.
> 
> I said before and I'll say again that once the web site project is done, 
> I'm going to try to write up an entry for the Scheme Cookbook 
> summarizing the current state of affairs in the world of connecting PLT 
> Scheme to a database. It's a topic that I think is in dire need of 
> better documentation.
> 
> -jacob