I feel like I am coming late to this topic. All the pundits have already had there say and the blogosphere has been rampant. I have been talking with a lot of MySQL folks lately, encouraging them to at least test PostgreSQL as an alternative. MySQL folks are nervous. They don't like the opportunity Oracle brings to the table. This morning I was asked quite bluntly, "From your perspective what is the future of MySQL?".
At PostgreSQL Conference East, Platinum Sponsor EnterpriseDB raffled two Netbooks. This is the video of the raffle (only 5 minutes). Of particular interest is a certain Major Contributors response.
We had an interesting issue crop up this past week. The question was, "How do we properly escape the following string...?". The string was:
You can't have it that way can you?
That seems like a pretty simple string right? On insert you would do one of the following:
(E'You can\'t have it that way can you?');
($$You can't have it that way can you?$$);
('You can''t have it that way can you?');
You would think that would be the end of it. However, If you are using ODBC with a pass through query you will receive the error, "The # of binded parameters is < than the # of parameter markers."
Yes, that's right. ODBC will parse the ? and interpret it as a parameter. This affects psqlodbc and ODBCng. Apparently it is actually not a bug . I am not sure I agree with that, regardless of what Microsoft says. What is particularly interesting here is that it is specifically the ? that is the problem. Not the single quote.
To work around this problem you can execute the query like this:
INSERT INTO foo VALUES ($$You can't have it that way can you?$$);
INSERT INTO foo VALUES ('You can''t have it that way can you?');