Re: Single quote as first character in string fails in compare

From: Joachim Verhagen <Joachim.Verhagen_at_xs4all.nl>
Date: Thu, 13 Oct 2011 19:13:11 +0200
Message-ID: <js6e97lu1bki6c6r15n6c3u55op1li3sk7_at_4ax.com>



On Tue, 11 Oct 2011 19:11:28 +0200, Joachim Verhagen <Joachim.Verhagen_at_xs4all.nl> wrote:

>On Mon, 10 Oct 2011 11:02:57 +0200, "Álvaro G. Vicario"
><alvaro.NOSPAMTHANX_at_demogracia.com.invalid> wrote:
>
>>El 07/10/2011 20:30, Joachim Verhagen escribió/wrote:
>>> I got a very strange problem. If the first character in a string is
>>> a single quote I get a syntax error, but that only happens in a
>>> compare and not in sqlplus.
>>>
>>> This is what happens:
>>>
>>>
>>> SELECT * FROM dual WHERE dummy ='''aaaa'
>>>
>>> DBArecordSet.OpenSet: Error from: 'OraOLEDB' 0x80040E14 (-2147217900)
>>> ORA-00933: SQL command not properly ended Native: 933 (help=.0)
>>>
>>> This goes wrong with an OLEDB and ODBC connection. It works in
>>> sqlplus.
>>
>>I don't know what your client language is but here's an excerpt from the
>>ODBC chapter in the PHP manual:
>>
>>«Parameters in parameter_array will be substituted for placeholders in
>>the prepared statement in order. Elements of this array will be
>>converted to strings by calling this function.
>>
>>Any parameters in parameter_array which start and end with single quotes
>>will be taken as the name of a file to read and send to the database
>>server as the data for the appropriate placeholder.
>>If you wish to store a string which actually begins and ends with single
>>quotes, you must add a space or other non-single-quote character to the
>>beginning or end of the parameter, which will prevent the parameter from
>>being taken as a file name. If this is not an option, then you must use
>>another mechanism to store the string, such as executing the query
>>directly with odbc_exec()).»
>>
>>http://es.php.net/manual/en/function.odbc-execute.php
>>
>>It isn't exactly your case (your string does not end with single quote)
>>but this suggests that the ODBC implementation has a weird relationship
>>with single quotes.
>>
>>
>>
>
>Hi Alvaro,
>
>Thank you for your answer.
>
>The language is Visual Basic. Putting a space before the quote does
>not give an error but the compare fails. Nothing special happens.
>
>Joachim.

The problem is solved. It had nothing to do with Oracle. Very deep in the datalayer of our programs is a function that changes ='' because of the confusion between the empty string and NULL in Oracle. But that function did not take ='something into account. Problem solved. Thank you for the help.

Joachim. Received on Thu Oct 13 2011 - 12:13:11 CDT

Original text of this message