MySQL Bugs: #10527: SUM function doesn't work using VB 6.0 / ADO

Bug #10527	SUM function doesn't work using VB 6.0 / ADO
Submitted:	11 May 2005 3:48	Modified:	11 Jun 2007 15:47
Reporter:	Javier Rodriguez Paiva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11,3.51.12	OS:	Windows (Windows XP Home SP2)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
The SUM aggregate function doesn't work when using VB 6.0/ ADO to connect to a MySQL 5.0.4-beta Server. I'm using MyODBC 3.51.11-2 as ODBC Driver. It always returns the following error message:
"Multiple-step operation generated errors. check each status value"

The query worked allright when using MySQL 5.0.2-alfa Server.

How to repeat:
Just make any query involving SUM using the configuration described above.

10-05-2005 23:08 GMT -0500: Updated Severity

Could you please submit a test code case (for to be tested using Access)
together with your database schema and dump file with data.

Thanks in advance.

Yes you can comment in Spanish if you want.

Thank you.

I have experiences with a similar problem. Also using VB 6.0 with ado and got this error. Fixed the problem in my case with replacing the summed datatype from integer to double and no more errors occured. I tried several datatypes, but only double worked without errors.

how do i generate a dump file?

Javier,

You can use the mysqldump.exe client which you find in your
bin directory or you can use the MySQL Administrator you
find it at:
http://dev.mysql.com/downloads/administrator/index.html

How to use mysqldump please read:

http://dev.mysql.com/doc/mysql/en/mysqldump.html

Hi Miguel, the command fails even using a very simple database schema. Here's a test case:

1. Create a test table and populate it (using MySQL 5.0.4-beta both client and server)

mysql> use test;
mysql> create table T_Prueba ( PruebaID INT AUTO_INCREMENT, data INT, CONSTRAINT PK_T_Prueba PRIMARY KEY (PruebaID));
mysql> INSERT INTO T_Prueba (data) VALUES (100); 
mysql> INSERT INTO T_Prueba (data) VALUES (120);
mysql> INSERT INTO T_Prueba (data) VALUES (180);

2. Check the query works
mysql> SELECT SUM(data) FROM T_Prueba;
Query OK, returns 400.

3. Create a VB6.0 (SP6) Proyect and add a reference to Microsoft ActiveX Data Objects 2.8 Library

4. Put this code into Proyect

Option Explicit

Private Sub Form_Load()
    Dim oCn As New ADODB.Connection
    Dim oRs As New ADODB.Recordset
    
    Dim strSQL As String
    
    oCn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=javier;PASSWORD=javier;"
    oCn.Open
      
    strSQL = "SELECT SUM(data) FROM t_Prueba"
    oRs.CursorLocation = adUseClient
    oRs.Open strSQL, oCn, adOpenForwardOnly, adLockReadOnly
    
    MsgBox oRs.Fields(0)
    
    oRs.Close
    Set oRs = Nothing
    
    oCn.Close
    Set oCn = Nothing
End Sub

5. Run the program and watch it crash: "Multiple-step operation generated errors. check each status value"

It does work if you just use 'SELECT data FROM T_Prueba' instead of using SUM.

I created test-case and tried to run. oRs.Open() returns the error with we use adUseClient in CursorLocation. if we set adUseServer oRs.Open() returns oRs.Fields(0).Type as adEmpty. 
I attached my test-case.

Test case

Attachment: 10527.zip (application/x-zip-compressed, text), 3.22 KiB.

I'm not really sure whether the problem is the connector or the database server, because the problem only appears since from 5.0.3-beta and on (I'm having a production server running on 5.0.2-alfa!!!!) , yet, if you make the query directly from the client, it works without problems (in 5.0.4-beta).

Anyway oRs.Fields(0) is returned with bad value. I set this is as a bug.
Tested on Win 2000 Sp4, MySQL server 5.0.6 beta,Microsoft Visual Basic .NET,
MyODBC 3.51.11

Have you guys already found out whether this is a server bug or a connector bug?

I have similar problem when i open recordset adUseclient just like select sum(X) from t_table (t_table is empty - no rows). When i use adUseServer, it works good, but it does not return 1 row with NULL value as it should, but returns empty recordset. For example select COUNT(x)  from t_table returns 1 row with 0, and select count(x), sum(x) from t_table returns empty recordset... I guess here is something wrong.

Marek

Added new data type MYSQL_TYPE_NEWDECIMAL. (Changeset@1.476)

This example works well with the  MySQL 5.0.4, but if the similar problems will appear it is strongly recommended to use MySQL 5.0.6.

When will this patch be released? Is a connector or a server patch?

I just saw the patch in the development tree, when will it be released as a binary?

Hi Javier,

MYSQL_TYPE_NEWDECIMAL patch will be applied in the version 3.51.12.
We expect to make this release in about 2 weeks

Only for the commentary, I already had reported this error in following bugs:

http://bugs.mysql.com/bug.php?id=10103 (MyODBC)
and
http://bugs.mysql.com/bug.php?id=10293 (Server)

I try a sum function on mySQL 5.0.11 beta from an ASP page

	Dim myConn
	Set myConn = Server.CreateObject("ADODB.Connection")

	Dim myRSet
	Set myRSet = Server.CreateObject("ADODB.Recordset")

	Dim laRisposta

	myConn.Open "DRIVER={MySQL ODBC 3.51 Driver}; " & _
                "SERVER=xxxxxxxxx; " & _
                "DATABASE=prova; " & _
                "UID=root; PWD=xxxxxxx; option=3 "

	theSQL = "SELECT sum(qtain) AS QIN, sum(scarti) AS QFAILS " & _
			 "FROM avanzamento " & _
			 "WHERE id_prodotto = 1 AND " & _
	         "id_fase = 1"

	myRSet.Open theSQL, myConn

but NOTHING, the Recordset is EMPTY, but if i use Query Browser, it's ok. So is possible that the problem is in OPTION value ????

Hi Armando Basile,

The problem is in the MyODBC, the Query Browser don't use the MyODBC, the patch already has approved, but only in the next version 3.51.12

good bye

Is there an estimate date for the release of myODBC 3.51.12?

also i would like to know when will be release the version .12, approximately of course

Can we know an estimate date for the release of myODBC 3.51.12?

I would like to use mysql on my website, but if odbc connector don't work... how can i do ?

:-)

Can I possibly use this?

Connect.open = "Provider=MySqlProv;eng=;datasource;dbn=dbname;uid;balh;pwd;blah")

in Visual Basic.. :) But anyway, I successfully used MyODBC but I am
curious in it..

i`m waiting for the release of the ODBC wich solves this case.
While we wait, can someone tell me if there is a oledb provider designed for mysql?

hey, i have downloaded and installed all the official releases of server, tools and odbc and the problem persists, am i doing something wrong or the bug has not been solved

A work around for this problem is to create a view and grab the information from the view.  That way you don't need to use the SUM function in the ODBC driver.

This is not working for me either.
I also tested the workaround by using a view. This only results in that the MySQL server terminate and has to be restarted. I should really need the Aggregate functions to work over MyODBC....

Still does not work with ODBC connect 3.51.12 and server 5.0.21.

The test case I used was
create table test (data as int);
insert into test values (4),(5),(6);

The visual studio 2005 express code was

            long hithere;
            MySqlConnection connSource = new MySqlConnection("database=peter;host=10.101.0.164;user id=remote2;password=hello;");
            connSource.Open();

            //MySqlCommand command = new MySqlCommand("select CAST( avg(data) as SIGNED INTEGER) from test", connSource);
            MySqlCommand command = new MySqlCommand("select avg(data) from test", connSource);

            MySqlDataReader reader  = command.ExecuteReader();
				MySqlDataReader queryReader;

                while (reader.Read())
                {
                    hithere = (long) reader.GetInt64(0);
                }

The version with the CAST in it does work but the version without it doesn't.
It works fine on version 4 of the server as the other bug reports also point out.

When I capture the MySQL protocol in ethereal I get
0000  00 05 9a 3c 78 00 da e7  20 00 01 00 08 00 45 00   ...<x...  .....E.
0010  00 6d 64 b8 40 00 80 06  80 33 0a 65 00 a4 0a 65   .md.@... .3.e...e
0020  00 32 0c ea 06 34 16 c2  50 30 69 22 48 82 50 18   .2...4.. P0i"H.P.
0030  ff 34 50 30 00 00 01 00  00 01 01 1f 00 00 02 03   .4P0.... ........
0040  64 65 66 00 00 00 09 61  76 67 28 64 61 74 61 29   def....a vg(data)
0050  00 0c 3f 00 10 00 00 00  f6 80 00 04 00 00 05 00   ..?..... ........
0060  00 03 fe 00 00 22 00 07  00 00 04 06 35 2e 30 30   .....".. ....5.00
0070  30 30 05 00 00 05 fe 00  00 22 00                  00...... .". 

In particular byte 58 states that the field is a LONGLONG but byte 5B gives the integer 4 decimal places.

The dump from version 4 server is
0000  00 05 9a 3c 78 00 da e7  20 00 01 00 08 00 45 00   ...<x...  .....E.
0010  00 69 89 05 40 00 80 06  5b eb 0a 65 00 a3 0a 65   .i..@... [..e...e
0020  00 32 0c ea 06 3f 2b 55  b0 61 d4 2a 5a fc 50 18   .2...?+U .a.*Z.P.
0030  fb 4c 8d 6e 00 00 01 00  00 01 01 1f 00 00 02 03   .L.n.... ........
0040  64 65 66 00 00 00 09 61  76 67 28 64 61 74 61 29   def....a vg(data)
0050  00 0c 08 00 11 00 00 00  05 00 00 04 00 00 01 00   ........ ........
0060  00 03 fe 07 00 00 04 06  35 2e 30 30 30 30 05 00   ........ 5.0000..
0070  00 05 fe 00 00 22 00                               .....". 
and this is different because it identifies the result in byte 58 as a decimal and with 4 decimal places.

It appears that the change to version 5 has changed the values put into the protocol.

hola tengo varias dudas en realidad con lo que respecta a la conexion de mysql hacia visual basic 6.0. Como funciona exactamente la conexion entre ambos... pueden poner un poco de informacion sobre mysql en español? ya que a veces como estudiantes no tenemos mucho tiempo de traducir!

The last fragment of code seems to be working with Connector/.NET but not ODBC.

MyODBC 3.51.14/3.51.15 display the correct results for both VB6 and VB.NET test cases

Nothing to be documented here, closing the bug.