MySQL Bugs: #31377: SQL Select statements return an E

Bug #31377	SQL Select statements return an E_Fail Status
Submitted:	3 Oct 2007 16:33	Modified:	29 Feb 2008 10:35
Reporter:	Will McKenzie	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When executing a SELECT * FROM or When selecting a number of fields from MySQL Database I get the following Error Message

Microsoft Cursor Engine error '80004005'

Data provider or other service returned an E_FAIL status. 

I've seen other bugs which sound like they are the same problem and the person has been told to upgrade their connector.  I'm using the alpha version of 5.1 but I've also tried this with the 3.51 connector and I get the same error, although I never used to when I used them before.  I'm testing this on a server running windows server 2003 x64 but iis is running in 32bit emulation mode and I'm coding using asp.  Any Ideas?

How to repeat:
Try a select * query.

Will, what I presume is happening is this:
 1) MS excluded default OleDB provider from all of their x64 OS (MSDASQL)
 2) All of MS SW is still 32 bit, so if you've installed c/ODBC x64 you will not be able to use it from, lets say, Office
 3) 1) + 2) means that the only way to use c/ODBC x64 is to load driver directly from your code...

Does this makes any sense in your case?

Hi Tonci

Some, not sure what you mean by MS SW.  How would I load the driver directly from my code?

Cheers
Will

Will, MS SW is "Microsoft software" meaning, if you install MyODBC x64 you will not be able to use it from MS Access (for example) as all of MS SW is still 32 bit. Direct load of driver is done when you do not reference any DSN (thus bypassing driver manager) via SQLDriverConnect (as opposed to SQLConnect and passing DSN name as server).

Hi Tonci

I have a feeling this is what I'm already doing.  I have 2 subroutines, one that opens the database and one that executes the query.  They are

Sub OpenDB
  Set dbConn = Server.CreateObject("ADODB.connection")
  dbConn.ConnectionTimeout = 15
  dbConn.CommandTimeout = 30
  dbConn.CursorLocation = 3
  connStr="Driver={MySQL ODBC 5.1 Driver};Server=192.168.2.50;Database=TDX;UID=Abacus;PWD=Leonard0; option=3; port=3306;"
  dbConn.Open connStr

End Sub

Sub OpenSQL(SQL)
	Set RS = Server.CreateObject("ADODB.Recordset")
  RS.CursorType = adOpenStatic
  RS.Open SQL,dbConn 
End Sub

It works if I only select 1 or 2 fields from the database but when I try and select more or all it crashes.

Will, this could be due to using MyODBC x64. What exact connector are you using (32bit or x64)?

Searching on this particular ErrNo is useless as MS seems to throw it in all sorts of situations...

Hi Tonci

It looks like I was originally using the 32 bit version that I installed using the Setup.exe.  I've uninstalled that version and replaced it with the no installer x64 version but now I'm getting an error saying it can't find the specified driver.  I've also tried with the 32bit no installer version and it returns an error saying it failed to load the driver.  So not sure which version I should be using.

Will

Will, what you should have seen is "Provider is not specified and there's no designated default.", right?
ADO is a part of MDAC which is "promised" by MS to be ported to x64... Please search for "MSDASQL" and Winx64 on the internet to get better view of problem. There are several of my posts in bugsdb too.

Hi Tonci

I've looked for more info on that but can't seem to find a workround anywhere, does one exist?  Also I've tried the same code on our live server which is running windows server 2003 web edition, mysql 5.1 and myodbc connector 5.1 and I get the same error and as far as I can tell that's all running on 32bit.

Cheers
Will

Will, there will be no workaround for Win x64 at least not until Longhorn... You can make sure of what's happening by trying MyODBC 3.51 *x64* on same box, it should fail like 5.1 does.

In case the registry entries are missing, you can check the registry and merge a setting from Bug#31510 (but this should not be the cause of your problem).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is not a bug but documented Win x64 behavior. Microsoft removed default oledb provider (MSDASQL) presuming everybody's using NET now...