improve error reporting for unpreparable statements

[arnehormann]

This is a low-priority TODO, I'd like to wait until Brad's changes are in.

I got bitten by statments which can not be prepared. See MySQL documentation: SQL Syntax Allowed in Prepared Statements.
We should probably report errors in statments.go:Query(...) with len(args) > 0 if stmt.paramCount == 0 as an error caused by using prepared statements, right now it's a little confusing without background knowledge.
Currently, the error looks like sql: statement expects 0 inputs; got 1.
While checking this, I also spotted a missing closing brace in an error in packets.go.
I hit this while trying to use

SHOW TABLES FROM `?`

which should be a legitimate way to explore the databases and tables on a server.

[xaprb]

MySQL itself isn't going to allow you to prepare the SHOW TABLES query. It
only permits parameters to be used for values, not identifiers. So I'm not
sure if this is helpful or just noise in this conversation, but I just
wanted to point out that your last sentence assumes something MySQL is not
going to allow.

On the other hand, the following is legal to prepare:

SHOW TABLES LIKE ?

[arnehormann]

My last paragraph was misleading, by legitimate I only meant the query itself, not using it in a prepared statement (though I tried it this way at first). But that still leaves me wondering... If it is disallowed for identifiers, why is SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW} ok for prepared statements? What's used as a parameter if not an identifier? Something along the lines of WHERE name LIKE ...?

Concerning this issue, I only think the error message could and should be clearer, as it doesn't cause an error on prepare, only on execution. It's not even that bad when it is called as

stmt, err := db.Prepare("SHOW TABLES FROM ?")
rows, err := stmt.Query("mysql")

but it is very strange getting sql: statement expects 0 inputs; got 1 after

rows, err := db.Query("SHOW TABLES FROM ?", "mysql")

SHOW TABLES LIKE ? didn't work for me, but SHOW TABLES did. Still calls for USE ..., though. It's not a big issue, but I hoped I could get along without tracking the current schema.

Still, thanks!

[julienschmidt]

I get the following error in both cases, which seems completely fine to me:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '?' at line 1

Did you forget to check err somewhere?

[julienschmidt]

The error sql: statement expects 0 inputs; got 1 comes from database/sql/sql.go#1203. There is nothing we could do on driver side to change the error text.
I'm still wondering how you even got there without ignoring an err, which should already be returned on Prepare(..)

[arnehormann]

I was able to demonstrate to Julien I could get the error without black magic or ignoring errors, but we can't prevent it.

[alidawud]

Same issue here, solved by doing this:

stmt, err := mysql.Prepare("SELECT * FROM tags WHERE tag_name like ?")
rows, err := stmt.Query("%" + tagName + "%")

[itsjavi]

I got the same problem and it was because I had the ? between quotes

stmt, err := db.Prepare("SELECT id, name FROM foobar WHERE id='?' OR name='?'")
checkErrFail(err)
err = q.QueryRow(slug, slug).Scan(&m.id, &m.name)

error: sql: statement expects 0 inputs; got 2.

this was fine:

SELECT id, identifier FROM foobar WHERE id=? OR identifier=?