mxODBC and apostrophe's in strings

[David Fuess]

On 12 Sep 2001 00:28:13 -0700, benc1 at today.com.au (Ben C) wrote:

This is not an mxODBC thing, it's a SQL thing. The spec says that text
items are quoted with a single quote. Double single quotes in quoted
strings evaluate to a single quote. That's why the string replace of
single quote with double single quotes works.

Dave

>Hi,
>
>I am uploading some random text to a MSSQL DB using the mxODBC module.
>
>ie.
>
>...
>_text = "This is some really 'extraordinary' text read from a file
>somewhere"
>
>_date = 10000232
>
>SQLExpress = "INSERT INTO table (Date, Time) VALUES (" + _date + ",'"
>+ _text + "')"
>
>cursor.execute(SQLExpress)
>...
>
>The probelm occurs if an apostrophe appears in the text (I assume
>because it has been used as a delimiter in the SQL Expression). The
>error message I recieve is similar to this
>
>...
>ProgrammingError: ('37000', 170, "[Microsoft][ODBC SQL Server
>Driver][SQL Server]Line 1: Incorrect syntax near 'rline'.", 4523)
>...
>
>Does anyone know of a workaround for this keeping in mind that any
>combination of ASCI characters could appear in the _text string.
>
>thanks in advance
>
>Ben