[racket] DB and named query parameters

On 02/15/2014 05:11 PM, Sean McBeth wrote:
> All of the examples in the documentation for the db package use either
> question-mark placeholders or numbered placeholders, i.e.
> "select * from some_table where some_column = ?"
> "select * from some_table where some_column = $1"
>
> The three databases that I use (SQL Server, MySQL, and Postgres) all
> support the question mark syntax, but you can only refer to a single
> parameter once in a query with the question mark.
>
> MySQL and Postgres support numbered parameters, but SQL Server does not.
> Or at least, not that I'm aware of. I've never seen anyone use it that
> way, and I can't find any examples online.
>
> Regardless, I rather much prefer named parameters, anyway, as it makes
> the queries easier to read.
> "select * from some_table where some_column = $param1"
>
> I know MySQL, Postgres, AND SQL Server all can support this style (well,
> replacing $ with @ for SQL Server), because I have a boat load of C#
> code doing it right now.
>
> But there is no indication of how to execute such a query in Racket with DB.

The PostgreSQL server itself only supports "$N" parameters. The MySQL 
server (AFAIK) only supports "?" parameters.

Support for other parameter syntaxes may be added by the particular 
library or driver you're using to communicate with the back end. For 
example, I think ODBC drivers all support "?" placeholders.

The Racket db library doesn't add support for other parameter syntaxes, 
and I have no plans to add it. (Not in terms of rewriting strings of SQL 
code, anyway; possibly from a structured representation of SQL code.)

You could write the translation(s) using proper tokenizer(s) or regexps 
that happen to work on the queries in your program and then create a 
statement wrapper using prop:statement that would run the translator for 
the appropriate dialect.

Ryan