MySQL Bugs: #14666: SELECT statement works in MySQL but not through ODBC

Bug #14666	SELECT statement works in MySQL but not through ODBC
Submitted:	5 Nov 2005 2:05	Modified:	5 Nov 2005 4:31
Reporter:	Ib Dyhr	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51	OS:	Windows (Windows XP Pro)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
A SELECT statement from my asp-file does not work - returns an error:

  Microsoft OLE DB Provider for ODBC Drivers error '80040e21' 

  ODBC driver does not support the requested properties. 

  /McA_MySQL/palletBalance.asp, line 73

The same statement works fine in MySQL directly returns two rows as it should:

mysql> SELECT tblDepots.dp_ID AS From_ID, tblDepots.dp_Name AS From_Dep, tblDepots_1.dp_ID AS To_ID, tblDepots_1.dp_Name
 AS To_Dep, Sum(tblHauls.h_CHEP) AS CHEP, Sum(tblHauls.h_Loscam) AS Loscam FROM (tblDepots INNER JOIN tblHauls ON tblDep
ots.dp_ID = tblHauls.h_Sending_Depot) INNER JOIN tblDepots AS tblDepots_1 ON tblHauls.h_Receiving_Depot = tblDepots_1.dp
_ID WHERE (tblHauls.h_Arrive>='2005-10-6' And tblHauls.h_Arrive<='2005-11-6') GROUP BY tblDepots.dp_ID, tblDepots.dp_Nam
e, tblDepots_1.dp_ID, tblDepots_1.dp_Name, tblHauls.h_Sending_Depot, tblHauls.h_Receiving_Depot ORDER BY tblDepots.dp_Na
me, tblDepots_1.dp_Name;
+---------+-----------+-------+----------+------+--------+
| From_ID | From_Dep  | To_ID | To_Dep   | CHEP | Loscam |
+---------+-----------+-------+----------+------+--------+
|       4 | Melbourne |     5 | Adelaide | NULL |   NULL |
|       3 | Sydney    |     2 | Brisbane |   21 |     11 |
+---------+-----------+-------+----------+------+--------+
2 rows in set (0.00 sec)

The statement is:  

SELECT tblDepots.dp_ID AS From_ID, tblDepots.dp_Name AS From_Dep, tblDepots_1.dp_ID AS To_ID, tblDepots_1.dp_Name AS To_Dep, Sum(tblHauls.h_CHEP) AS CHEP, Sum(tblHauls.h_Loscam) AS Loscam FROM (tblDepots INNER JOIN tblHauls ON tblDepots.dp_ID = tblHauls.h_Sending_Depot) INNER JOIN tblDepots AS tblDepots_1 ON tblHauls.h_Receiving_Depot = tblDepots_1.dp_ID WHERE (tblHauls.h_Arrive>='2005-10-6' And tblHauls.h_Arrive<='2005-11-6') GROUP BY tblDepots.dp_ID, tblDepots.dp_Name, tblDepots_1.dp_ID, tblDepots_1.dp_Name, tblHauls.h_Sending_Depot, tblHauls.h_Receiving_Depot ORDER BY tblDepots.dp_Name, tblDepots_1.dp_Name;

How to repeat:
At will

Could you please provide a complete test case, create table script,
some inserts, select query and the expected resulted, the asp code.
Also specify the server server version which you are running the query
against.

Thanks in advance.

create table test (ID int, N int);

insert into test (ID,N) values (1,10);
insert into test (ID,N) values (2,20);
insert into test (ID,N) values (2,30);
insert into test (ID,N) values (3,30);

mysql> select * from test;
+------+------+
| ID   | N    |
+------+------+
|    1 |   10 |
|    2 |   20 |
|    2 |   30 |
|    3 |   30 |
+------+------+
4 rows in set (0.00 sec)

mysql> SELECT test.ID, Sum(test.N) FROM test GROUP BY ID;
+------+-------------+
| ID   | Sum(test.N) |
+------+-------------+
|    1 |          10 |
|    2 |          50 |
|    3 |          30 |
+------+-------------+
3 rows in set (0.00 sec)

-------------------------- which is all good, Mate ------------------------------

mysql>  status
--------------
mysql  Ver 14.12 Distrib 5.0.15, for Win32 (ia32)
Connection id:          574
Current database:       menagerie
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.15-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 5 days 4 hours 46 min 47 sec

Threads: 1  Questions: 8728  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 2  Queries per second avg: 0.019
--------------

-------------------------------------------------------------------------------

My Windows XP is Version 5.1 (Build 2600.xpsp_sp2_gdr.050301-1519 : Service Pack 2)

-------------------------------------------------------------------------------

My asp file:

//Application Constants
var adOpenDynamic = 2; 
var adOpenStatic = 3;
var adUseClient = 3;
var adLockOptimistic = 3;

	var dbConn = Server.CreateObject("ADODB.Connection");

	dbConn.Open ("McA");  // My DSN name defined for ODBC - works fine for other database accesses

	var Rs = Server.CreateObject("ADODB.Recordset");
	var SQL=
	"SELECT test.ID, Sum(test.N) " +
	"FROM test " +
	"GROUP BY ID;"
	
	Rs.Open (SQL,dbConn,adOpenStatic,adLockOptimistic);

- and this is where the error occurs:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21' 

ODBC driver does not support the requested properties.

Hi, Miguel.

After having condensed the problem it now seems to work ... I will have to bring it back to the original version, and see what happens.

Don't bother any more for the time being ... I'll be back if the problem persists - thank you for wasting your time.

Cheers,
Ib