SELECT id, name
FROM doc_type /* this won't work! */
WHERE name LIKE ?
If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works.
Is this a known bug (couldn't find it in the KB)?
Thanks,
Bob Kline
SQL Server interprets the embedded apostrophe as the end of string marker (since SQL Server uses apostrophes to delimit string data.
There are a couple of things to try.
The simplest one is to 'escape' the apostrophe -by replacing a single apostrophe with two single apostrophes.
The other possible solution requires that the server be configured in accordance. That one allows using double quotes as a string delimiter.
|||Thanks for your reply, Arnie. Actually, it's not SQL Server which is misbehaving in this case. If, for example, you do the same thing in, say ADO.Net, or JDBC, SQL Server treats the apostrophe inside the comment as just another character in the comment (as it should). So this bug is specific to ADODB.So, once again, is this a known bug and I just haven't been using the right query terms to find it in the knowledge base? Or should I file a bug report with Microsoft?
Cheers,
Bob Kline
|||
You're right Bob, I meant to implicate ADODB as the culprit.
This is known behavior, I don't if it is considered a 'bug', or just an 'inconvenience'.
However, please post your comments and concerns about the behavior (in the feedback section). It will get to the 'right' parties, and if enough folks 'vote' and validate the issue, it will esculate and perhaps be 'fixed'.
Suggestions for SQL Server
http://connect.microsoft.com/sqlserver
|||
An apostrophe in a VB query string is not a bug. rofl
Please do not report this as a bug.
Adamus
|||> This is known behavior ...You wouldn't happen to have a KB number, would you?
> ... don't [know] if it is considered a 'bug' ....
I would respectfully suggest that anyone who doesn't recognize this as a defect doesn't understand the meaning of the phrase "ignores all characters within a comment" (from Microsoft's SQL Server documentation).
> ... please post your comments ... in the feedback section ....
Will do.
Thanks,
Bob
|||
Adamus Turner wrote:
An apostrophe in a VB query string is not a bug. rofl
Please do not report this as a bug.
Adamus
Hello, Adamus.
Are you trying to say that you think an apostrophe embedded within a double-quoted string constant is interpreted by Visual Basic as the beginning of a VB comment? After you pick yourself back up off the floor you might want to go back and re-read the VB docs.
Cheers,
Bob
|||
> This is known behavior ... You wouldn't happen to have a KB number, would you?
> ... don't [know] if it is considered a 'bug' ....
I would respectfully suggest that anyone who doesn't recognize this as a defect doesn't understand the meaning of the phrase "ignores all characters within a comment" (from Microsoft's SQL Server documentation).
> ... please post your comments ... in the feedback section ....
Will do.
Thanks,
Bob
Hi Bob,
To further elaborate:
First, It's not a (SQL) comment 'yet' while it's in the vb string. It's just a vb string.
Second, it's normal that INSERT/UPDATE commands have values that contain apostrophe's especially when you're dealing with varchar's and text
Third, it is not a bug, glitch, or a pin hole in the design. It's standard 101 programming.
Lastly, why on earth are you executing a SQL comment in a vb query string?
P.S. This should be posted in the VB Forums not SQL Server
Correction: This should be posted on Comedy Central
Adamus
|||Adamus:
It's possible (though I realize unlikely) that one day you'll take a deep breath, go back and re-read this thread more carefully, and realize that you've missed some of the details. I'll give you a clue: your initial contribution to the thread assumed (without any basis of evidence from earlier postings in the thread) that we are using Visual Basic. This assumption was, in fact, not correct. That's one of the reasons I know that your idea about VB comment delimiters is a red herring.
Regards,
Bob
|||
Are you saying the query runs fine in ADO(.net) or runs fine with OLEDB?
Yes.
I'll try one more time. The apostrophe is inside an SQL comment. An SQL parser is required to ignore all characters inside an SQL comment (confirmed by Microsoft's own documentation). The query (as originally posted) runs fine when submitted from Query Analyzer, ADO.Net, JDBC, or any other data access layer except ADODB.
Cheers,
Bob
|||
Correction:
When submitting the query in Query Analyzer, the placeholder is replaced with the literal string value. This doesn't affect the value of using Query Analyzer to demonstrate that an SQL query containing an embedded comment, which in turn contains an apostrophe character, is correct syntax.
Bob
|||
Bob Kline wrote:
Correction:
When submitting the query in Query Analyzer, the placeholder is replaced with the literal string value. This doesn't affect the value of using Query Analyzer to demonstrate that an SQL query containing an embedded comment, which in turn contains an apostrophe character, is correct syntax.
Bob
Arnie has answered this a long time ago. Escape the apostrophe.
|||
Arnie has answered this a long time ago. Escape the apostrophe.
I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.
Regards,
Bob
|||
Adamus Turner wrote:
Arnie has answered this a long time ago. Escape the apostrophe.
I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.
Regards,
Bob
Why do you think it's a workaround? Correct syntax is not a workaround. You have to escape a single quote in a query string. It does work. It will work. You are just coding it wrong. You need to swallow some pride and post your code before you lose any more integrity.
Adamus
|||
Adamus Turner wrote:
Arnie has answered this a long time ago. Escape the apostrophe.
I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.
My apologies, I tested again and the workaround (doubling the apostrophe) does eliminate the failure.
I've opened a case with Microsoft, and they've confirmed that this behavior of ADODB is a bug.
Cheers,
Bob
No comments:
Post a Comment