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.

The following all works:
SELECT '''a''' FROM dual

SELECT * FROM dual WHERE dummy like '''a'

SELECT * FROM dual WHERE dummy =N'''aaaa'


I tried this with Oracle 11.2 and Oracle 10.2 server and Oracle 11.2
and 10.2 drivers for OLEDB

In the Netherlands some names start with '. It does not happen
often, so we did not notice this before and have no idea where in
our program this might occur.

Does anybody know this problem and a solution?

Joachim.

El 07/10/2011 20:30, Joachim Verhagen escribió/wrote:
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.




--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Oct 10, 5:02*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
I find this to be an interesting piece of information. As is the fact
a name can start with a sinle quote. In English names about the only
time you run into single quotes in names is with O'reilly, O'connor,
etc ... and it is not a starting or ending. Naturally I have seen
quoted strings being stored but apparently the access is not via ODBC.

HTH -- Mark D Powell --

On Mon, 10 Oct 2011 11:02:57 +0200, "Álvaro G. Vicario"
<alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:

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.

On Tue, 11 Oct 2011 19:11:28 +0200, Joachim Verhagen
<Joachim.Verhagen (AT) xs4all (DOT) nl> wrote:


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.

On Oct 10, 11:57*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
So this evening, I get home, and my kid is studying AP statistics. He
mentions how an example chart seems to be politically correct in the
variety of names. This starts a conversation about names in
computing, and I happen to mention I saw something online that claimed
some names in the Netherlands started with a quote character. He
immediately responds "Oh, some of my friends do that on Facebook so
they get listed first." Forehead slap.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...-blasts-orbit/