[racket] Racket DB Library, data conversion

From: George Neuner (gneuner2 at comcast.net)
Date: Sat Feb 14 15:27:26 EST 2015

Hi Sean,

On 2/14/2015 11:22 AM, Sean Kanaley wrote:
> Nevermind, that seems to be what prepared statements do.
>
> On Sat, Feb 14, 2015 at 9:34 AM, Sean Kanaley <skanaley at gmail.com 
> <mailto:skanaley at gmail.com>> wrote:
>
>     Hello All,
>
>     I would like to query based on an incoming hash (with help of json
>     library), but I don't see a built-in way to generate query needed.
>     There is a rows->dict but I don't see either a dict->where-clause
>     or at least whatever query uses in that dollar sign magic to
>     convert Racket-val->SQL-string.
>
>     Currently I have a function which takes a hash and makes
>     "<col1>=$1 <boolean> <col2>=$2..." so I can then do something like
>     (apply query <the above string> <the vals returned by that same
>     function as it built that string>). It would be nice and probably
>     more efficient if the value could just be spliced in while I build
>     the string to begin with, but it seems like only query has such
>     dollar sign conversion power available.
>
>     Am I missing something?
>
>

Prepared statements will be the most performant approach, but be aware 
that prepared statements are bound to the connection - if you close the 
connection you will lose them.  Nor can you release the connection to a 
pool because you can't be certain to get the same connection back when 
you need a particular prepared statement. Additionally the DBMS will 
have limits on the number of prepared statements per connection - 
prepare will fail if you exceed the limit.  Prepared statements may cost 
considerable amounts of memory on the server side if that is a 
consideration.

The client side alternative is to to hash both the SQL and the variables 
and use apply (as you noted already).  It is slightly less performant, 
but it has no impact on connection pooling or on the server so it may be 
the better way if you have many threads or many clients.

Hope this helps,
George

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20150214/f5c92bb3/attachment.html>

Posted on the users mailing list.