Re: [Chicken-users] postgresql: support for query parameters

[Matthew Welland]

On Tuesday 26 February 2008 07:47:27 pm Graham Fawcett wrote:
> Hi folks,
>
> I've just checked in a patch to the trunk of the release/3/postgresql
> egg, providing support for query parameters. If anyone else is using
> this egg, I'd appreciate your checking out this version and testing
> it.

I will test it within the next few days. 

> In all of the querying procedures, the new interface lets you replace
> the query-string argument with a list, (query-string . args). Given a
> list, the new code uses the PQsendQueryParams call, so escaping of
> parameters is handled automatically.

This is wonderful. I can now retire my snippet that tries to fake this.

> Examples:
>
> ;; old interface (still works)
> (pg:query-tuples "select foo, bar from baz" the-conn)
>
> ;; new interface
> (pg:query-tuples
>  (list "select foo, bar from baz where foo > $1 and baz < $2"
>        foo-low baz-high)
>  the-conn)

I don't know if it is a standard of sorts but all the db api's (perl, ruby, 
sqlite3 egg) I have used use a ? for the placeholder.

E.g. the sqlite3 egg:

....
(let ((sqlstmt
(sqlite3:prepare db "SELECT foo,bar FROM baz WHERE foo>? AND baz<?"))))
(sqlite3:for-each-row (lambda (f b)(print f " " b)) foo-val baz-val)
(sqlite3:finalize! sqlstmt)
....

It would be nice to be consistent IMHO, perhaps even going to the extent of 
a DBI like approach.