{"id":68,"date":"2003-11-07T20:16:00","date_gmt":"2003-11-07T20:16:00","guid":{"rendered":"http:\/\/jclark.org\/weblog\/Programming\/worstbugfix.html"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T04:00:00","slug":"worstbugfix","status":"publish","type":"post","link":"https:\/\/jclark.org\/weblog\/2003\/11\/07\/worstbugfix\/","title":{"rendered":"Worst. Bugfix. Ever."},"content":{"rendered":"

I hope that none of you ever, ever<\/strong> have to deal with Sybase ASE ODBC<\/span>\/ADO drivers. Nevertheless, I’m going to document a bug (and a fix\/workaround) for using Sybase with Microsoft ADO,<\/span> because I still can’t believe it.<\/p>\n

I first used ADO<\/span> against a Sybase server about 6 years ago, on a major website project. Over the years, I (and other members of my team) have found that every version of the ADO<\/span>\/ODBC drivers has various issues when used with MSADO. <\/span>Each new release of ADO<\/span> and\/or Sybase Drivers seems to change subtle behaviors in ADO. <\/span>We did identify a single version (11.004, I think) that work exactly as it should. Subsequent versions had bugs, so we stuck with what worked.<\/p>\n

We recently deployed all new webserver hardware for that original website project. It’s an important website, it processes $12-$15 Billion (yes, that’s a B) in transactions monthly. Needless to say, I spent alot of time installing and testing. Now, the server admins put a new version of the Sybase drivers on ther servers. I was hesitant, but was told that this version worked correctly. And in my testing, it did.<\/p>\n

Until we deployed. Seems there was one function I didn’t test, which used ADO<\/span>‘s .UpdateBatch() against a Recordset opened with a client-side cursor. Shame on me for not testing it; nevertheless, the .UpdateBatch() was failing. Remembering similar issues from the bad-old days of driver shuffling, I tried switching to a server-side cursor. This fixed the problem. Unfortunately, it introduced another… the Sybase server side cursor doesn’t support Recordset filtering (and of course, the Client-side cursor library does).<\/p>\n

So now, I was officially damned-if-I-do, damned-if-I-don’t. I immediately (well, right after some choice words) did what any good developer would do… I Googled. And lo, Google was good to me, for I found what I needed immediately. And I was shocked.<\/p>\n

The post I found said that you could correct the ‘UpdateBatch failing on client-side cursors’ issue by adding the following line to your data source’s section of your ODBC<\/span>.ini file:<\/p>\n

WorkArounds2=24<\/code><\/p><\/blockquote>\n

Now, I don’t know if this affects the Sybase software or the Microsoft ADO<\/span> software, but what we have here is a hack. An undocumented hack at that. Why this isn’t a configurable option on the ADO<\/span> library or the OLEDB<\/span>\/ODBC driver is beyond me. But that’s not the worst part. The person who posted this nugget was in fact posting a solution to a problem they had themselves posted… and they found this answer in a newsgroup post from 1998<\/strong>. Why is this nasty hack still needed, five years later<\/em>?<\/p>\n

So, for the search engines, the above hack<\/em> will fix and ADO<\/span> failure cause when trying to run .UpdateBatch against a client-side (adUseClient) Recordset with Sybase ASE<\/span> drivers. The error message is:<\/p>\n

\n\n
\nRun-time error '2147467259 (80004005)';\nData provider or other service returned an E-FAIL status.\n<\/pre><\/blockquote>\n
You may also see a message that the error was raised by the Microsoft Cursor Engine.  Also note, ODBC<\/span>.ini now lives in the registry, at HKLM<\/span>\/Software\/ODBC.ini.<\/p>\n
      <\/p>","protected":false},"excerpt":{"rendered":"
I hope that none of you ever, ever have to deal with Sybase ASE ODBC\/ADO drivers. Nevertheless, I’m going to document a bug (and a fix\/workaround) for using Sybase with Microsoft ADO, because I still can’t believe it. I first used ADO against a Sybase server about 6 years ago, on a major website project. […]<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-68","post","type-post","status-publish","format-standard","hentry","category-programming"],"_links":{"self":[{"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":0,"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"wp:attachment":[{"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jclark.org\/weblog\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}