Parameter placeholder in SQL drivers

[Archos]

I was trying to do generic SQL statements for PostgreSQL[1], MySQL[2] and SQLite[3]  DBMS, but MySQL's prepared statements natively use "?" as the parameter placeholder, while PostgreSQL uses "$1", "$2", etc.

The idea of having different placeholders based on DB driver is horrible because we'd have to create all prepared statements for every DBMS with the same parameter placeholder.

Does this could be solved if all Go drivers could use the same parameter?


[1]: github.com/bmizerany/pq
[2]: code.google.com/p/go-mysql-driver/mysql
[3]: github.com/mattn/go-sqlite3

[Julien Schmidt]

Well it could be possible to add a "compatibility mode" where '$1', '$2', ... '$n' are simple replaced with '?'. 

(speaking for code.google.com/p/go-mysql-driver/mysql)

[Peter S]

Related issue: https://code.google.com/p/go/issues/detail?id=3602

Peter

--
 
 

[Archos]

This would be the easiest solution to fix --at least in MySQL/PostgreSQL-- but I'm afraid that others DBMS have a different format, i.e. SQLite enables several formats:

http://grox.net/doc/sqlite/lang_expr.html
http://grox.net/doc/sqlite/c3ref/bind_blob.html

[Archos]

What I'm doing is putting all statements into a map with the parameters used in PostgreSQL. Then, it's used a global variable to indicate the SQL engine, by default to PostgreSQL (in my case), and into a init function is checked if the SQL engine to use is another different, i.e. MySQL, to change all parameters in the map.
So, the greatest overhead is at the beginning and only if it is used a non-default engine.

[Mathieu Lonjaret]

Speaking of differences between mysql and postgres, since there is no
"replace into" to do an upsert in postgres, I wanted to do something
like:
// insert into rows (k, v) select 'foo', 'bar' where not exists
(select 1 from rows where k='foo');
as part of the solution, but I'm having some trouble with the
parameter placeholders for the select. if I try:

_, err = db.Exec(`INSERT INTO rows (k, v) SELECT $1, $2 WHERE NOT
EXISTS (SELECT 1 FROM rows WHERE k=$1)`, "foo", "bar")

I get this:
panic: pq: L:"305" D:"text versus character varying" C:"42P08" P:"32"
F:"parse_coerce.c" R:"coerce_type" M:"inconsistent types deduced for
parameter $1" S:"ERROR"

How do I pass these first $1 and $2 correctly so that they end up as
'foo' and 'bar' in the query?

the driver is github.com/bmizerany/pq

> --
>
>

[Daniel Theophanes]

This is one of the pit falls of the design of database/sql and some of the drivers: they don't usually take sql parameter structs with info about each parameter, just the value. This is often alright, but not always. In this case the easiest thing for you to do is to cast your input param to a varchar, so

... (select 1 from rows where k=cast($1 as varchar)

Ideally you could optionally pass in a SQL parameter type to specify a value type, and possibly a length.

-Daniel

[Mathieu Lonjaret]

That works, thanks.

I ended up creating a function instead to do the correct version, but the cast trick is good to know anyway.

--