<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hi Sean,<br>
<br>
On 2/14/2015 11:22 AM, Sean Kanaley wrote:<br>
</div>
<blockquote
cite="mid:CA+APS0Dt8n-i7waUZVRDQ4bwyY3aNAq8vHkMM=Y==mPNJPPdRA@mail.gmail.com"
type="cite">
<div dir="ltr">Nevermind, that seems to be what prepared
statements do.</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Sat, Feb 14, 2015 at 9:34 AM, Sean
Kanaley <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:skanaley@gmail.com" target="_blank">skanaley@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Hello All,
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>Am I missing something?</div>
</div>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
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.<br>
<br>
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.<br>
<br>
Hope this helps,<br>
George<br>
<br>
</body>
</html>