Can I put arbitrary text in SQL comments when using ODBC?

Question

Consider a table defined by

CREATE TABLE Example (id INTEGER)

and an INSERT statement for adding tuples to Example, having a single quote in a SQL comment:

my_cmd = """-- Comment's device
INSERT INTO Example (id) VALUES (?)
"""

I am connecting to MySQL via the pyodbc module. The surprsing effect seems to be that the INSERT command then consistently fails .IFF. there is an odd number of apostrophes in the comment. The message mentions HY000 and:

'The SQL contains 0 parameter markers, but 1 parameters were supplied'

Update: The behavior has now been verified by MySQL support staff, so that there is a chance it will be classified as a bug, actually. See Gord's answer, which lists another specimen, and comments pointing to the MySQL bug database.

A full test script is listed below. I wonder if this quote thing is just a gotcha? But it is odd:

Is it wrong to assume that comments start at -- and extends to the end of line if the statement text is passed via ODBC? (I do put a space after --, and I use SQL comments for portability. Different comment characters, or MySLQ block comments don't change the effect.)

A trace of the ODBC session shows SQLNumParams of 1 and 0 for the successful and failing cases listed below, respectively. However, the statements themselves look as given. I was tempted to assume that the linebreaks ('\n') are ineffective. But this is somehow contradicted by the working examples below that have two quotes (my_cmd_with_two_qutoes), or none (i.e. my_cmd with "*" replacing "'").

Or is it a driver issue?

import pyodbc

my_cmd = """-- Comment's device
INSERT INTO Example (id) VALUES (?)
"""

my_cmd_with_two_quotes = """-- Comment's device's
INSERT INTO Example (id) VALUES (?)
"""

conn = pyodbc.connect("DSN=Abcdef;PWD=...")
thing_named_cursor = conn.cursor()

# success, replacing the single apostrophe:
thing_named_cursor.execute(my_cmd.replace("'", "*"), (123, ))

# success, too:
thing_named_cursor.execute(my_cmd_with_two_quotes, (456, ))

# failure:
try:
    thing_named_cursor.execute(my_cmd, (789, ))
except pyodbc.Error, e:
    print("Error: %s" % e)

conn.commit()
conn.close()

Answer 1

Or is it a driver issue?

Yes. I recreated your issue with the following test code against a MySQL database

import pyodbc
cnxn = pyodbc.connect("DSN=usbMySQL")
crsr = cnxn.cursor()
sql = """
-- Comment's device
INSERT INTO Example (id) VALUES (?)
"""
crsr.execute(sql, [11])
cnxn.commit()
cnxn.close()

but it works fine with a DSN pointing to a Microsoft SQL Server database (using SQL Server Native Client 11.0).

The code also fails when using pypyodbc against the MySQL database, so it's not just an issue with pyodbc.

Also, the following VBScript code ...

Option Explicit
Const adParamInput = 1
Const adInteger = 3
Dim con, cmd
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=usbMySQL"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = _
    "-- Gord's test" & vbCrLf & _
    "INSERT INTO Example (id) VALUES (?)"
cmd.Parameters.Append cmd.CreateParameter("?", adInteger, adParamInput, , 121)
cmd.Execute
con.Close

... fails with ...

[MySQL][ODBC 5.2(w) Driver][mysqld-5.6.13-log]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 2

... but changing the Gord's to Gord''s in the CommandText ...

cmd.CommandText = _
    "-- Gord''s test" & vbCrLf & _
    "INSERT INTO Example (id) VALUES (?)"

... allows it to run without complaint.

Additional information:

For the record, MySQL Connector/Python does not have a problem with the original comment string in the question. This works fine:

import mysql.connector
cnxn = mysql.connector.connect(port=3307, user='root', password='whatever', database='mydb')
crsr = cnxn.cursor()
sql = """
-- Comment's device
INSERT INTO Example (id) VALUES (%s)
"""
crsr.execute(sql, [101])
cnxn.commit()
cnxn.close()

Answer 2

Have you tried block comments? /* mycomment */

My guess would be the language's newline in such cases is not considered a newline by MySQL; or the connection may process "whitespace" down to a minimum to optimize sending the query (effectively stripping your newlines out).

Answer 3

I believe that the ODBC call converts your sql into a single-line stream. The double-dash comment then comments everything afterward, including valid sql. You need to use the the block comments /* and */. I believe some odbc drivers strip -- comments before submitting the sql, so you may not see this issue with all systems.