Bug #18157 Bad data type of Recordset
Submitted: 11 Mar 2006 17:02 Modified: 31 Jul 2007 9:45
Reporter: Marcel Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.12 OS:Windows (Windows 2003 server)
Assigned to: CPU Architecture:Any
Tags: ADO

[11 Mar 2006 17:02] Marcel
Description:
Hello, I have a problem...
MySQL in ASP works OK, but have problem with using SUM, AVG ...

This works OK:
<%
 objRs.Open("SELECT AVG(id) AS c FROM users")
 Response.Write objRs("c")
 objRs.Close
%>

but if I want to Increase result of query it crashes with Type mismatch error:

<%
 objRs.Open("SELECT AVG(id) AS c FROM users")
 Response.Write objRs("c") +1
 objRs.Close
%>

same problem with SUM ...

I think that problem is with decimal separator - in our country there is a comma separator 

no 3.15 but 3,15

Should I set another decimal separator instead of dot (.) in MySQL!?!

Thanks for tips ...

How to repeat:
Can repeat with the source - always crash :-(

Suggested fix:
I think that optional decimal separator should help ...
[23 Mar 2006 15:54] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version of the ODBC driver, 3.51.12, and inform about the results.
[23 Mar 2006 19:07] Marcel
No, the same - if I try

objRs.Open("SELECT AVG(id) AS c FROM users")
Response.Write "<br />"
Response.Write "<br />c: "
Response.Write objRs("c")
Response.Write "<br />c+1: "
Response.Write objRs("c")+1
objRs.Close

it write:
---
c: 214,303
c+1:

Microsoft VBScript runtime error '800a000d'

Type mismatch

/wwwroot/main.spshk, line 29 
---

I (of course) should redeclare it (   Response.Write CLng(objRs("c"))+1 ) and after it - that works but - Why the result isn't a declared right?

If I try the same using Microsoft Excel (connect with ODBC) that works perfect ...
[24 Mar 2006 11:43] Marcel
Oh, in Microsoft Access it's OK not in Excel (I didn't try it with M$ Excel)
[13 Apr 2006 15:07] Tonci Grgin
Hi. Can you please post a small program which reproduces this problem?
[5 May 2006 10:59] bravo he
sql = "select sum(m_remit_p),sum(m_remit_w) from pl_matter 
rs.open sql, conn
a = 5 + rs(0)

Microsoft VBScript 執行階段錯誤 錯誤 '800a000d' 
型態不符合

OS:windows 2003 + sp1 IIS6
DB Server:mysql  Ver 14.12 Distrib 5.0.16, for portbld-freebsd5.4 (i386) using  4.3
[11 May 2006 21:07] Tonci Grgin
Hi, thanks for your bug report. I was able to verify your findings with code provided.
My test environment:
  Suse Linux 10 with mysql 4.1.20-debug and 5.0.22-debug, both bk build
  WIN XP Pro SP2 with 4.1.18-nt-max-log and 5.0.18-debug (bk build), MyODBC 3.51.12
My findings: 
  Both 4.1.x servers work with code provided.
  Both 5.0.x servers fail with error reported.
  Decimal separator as defined in control panel makes no difference.
  Using AGG functions on float or integer fields make no difference.
[6 Oct 2006 18:22] Eric Herman
Also confirmed running on WindowsXP with 5.0.22 MyODBC 3.51.12

TestBug18157
Error while running script
Type mismatch
Unknown error 0x800A000D
Error: Source: Microsoft VBScript runtime error  Line 39  Column 4
Type mismatch

when running the following code:

 24 : sub TestBug18157()
 25 :     
 26 :     'I wish to move table creation into function so we may track the table names for teardown.
 27 :     adoConn.Execute "DROP TABLE IF EXISTS bug18157"
 28 :     adoConn.Execute "CREATE TABLE bug18157(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, foo INTEGER NOT NULL, PRIMARY KEY(id)) CHARACTER SET utf8 ENGINE=InnoDB"
 29 : 
 30 :     adoConn.Execute "insert into bug18157 VALUES(NULL, 3)"
 31 :     adoConn.Execute "insert into bug18157 VALUES(NULL, 3)"
 32 :     adoConn.Execute "insert into bug18157 VALUES(NULL, 6)"
 33 : 
 34 :     adoRS.Open "SELECT AVG(foo) AS c FROM bug18157"
 35 :     Assert.IsEqual adoRS("c"), 4
 36 :     adoRS.Close
 37 : 
 38 :     adoRS.Open "SELECT AVG(foo) AS c FROM bug18157"
 39 :     Assert.IsEqual adoRS("c")+1, 5
 40 :     adoRS.Close
 41 : end sub
 42 :
[10 Oct 2006 2:55] Nilam Doctor
"Select concat(avg(amount),' ') as AVGAmt from tblsalary"

Works fine with ASP / MYSQL
[26 Jan 2007 15:24] Ricardo García
i have the same problem with this code:

<%
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open MySQLcnx

sql = " select A.rut, A.nombre, A.curso, A.rdnat, A.rddec, A.rdfra, A.rdent, "
sql = sql & " ((select count(*) from tblregistro where rut= A.rut and hecho = 1) / 4) as velocidad "
sql = sql & " from tblacceso as A where institucion = '41268557' order by curso, nombre "
set rs = conn.execute(sql)

response.write (rs("velocidad") + 100)
%>
[2 Feb 2007 10:56] Tonci Grgin
Hello all.

Microsoft VBScript runtime error '800a000d'
Type mismatch: ...
	
Cause: You are trying to convert a non-numeric variable into numeric.
Solution: Make sure the variable is numeric.
Use If IsNumeric( ) function to validate first.

We can test this easily, just put '&' instead of '+' in your formula and the error goes away.

Now what we can do is rewrite formula to get numeric value out of it by using CDbl or CInt functions where appropriate. The following test case works as expected:

Option Explicit
Const DSN = "Uid=root;Pwd=;Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=solusd;Port=3306;OPTION=3"

 Const adOpenForwardOnly = 0
 Const adOpenDynamic = 2
 Const adOpenStatic = 3
 Const adLockReadOnly = 1
 Const adLockPessimistic = 2
 Const adLockOptimistic = 3

 Const adUseNone = 1
 Const adUseServer = 2
 Const adUseClient = 3

 '- ParameterDirectionEnum Values -
 Const adParamUnknown = &H0000
 Const adParamInput = &H0001
 Const adParamOutput = &H0002
 Const adParamInputOutput = &H0003
 Const adParamReturnValue = &H0004
 '- CommandTypeEnum Values -
 Const adCmdUnknown = &H0008
 Const adCmdText = &H0001
 Const adCmdTable = &H0002
 Const adCmdStoredProc = &H0004
 Const adCmdFile = &H0100
 Const adCmdTableDirect = &H0200
 Const adInteger=3
 Const adDate=7
 Const adVarChar=200

DIM i
Dim cnxDatabase
Dim strSQL
' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)

' querying data, KolVS is a FLOAT field, PC is DECIMAL
strSQL = "SELECT SUM(KolVS) AS c, AVG(PC) AS d, COUNT(Artikl) AS e FROM dobra"
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = 3
    .CursorType = 3'1
    .CursorLocation = adUseClient
    .Open(strSQL)
End With
Wscript.Echo rs("c")
Wscript.Echo rs("d")
Wscript.Echo rs("e")
i = CInt(rs("e"))+1 <<<
p = CDbl(rs("d"))+10000.00 <<<
Wscript.Echo "i is now"
Wscript.Echo i
Wscript.Echo "p is now"
Wscript.Echo p

rs.Close

Now I just have to figure out is this MySQL bug or just an expected behavior.
[31 Jul 2007 9:45] Tonci Grgin
The behavior remains the same with 3.51.17GA.

Server version: 5.0.44-max-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SUM(KolVS) AS c, AVG(PC) AS d, COUNT(Artikl) AS e FROM solusd.dobr
a;
Field   1:  `c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     36
Max_length: 11
Decimals:   4
Flags:      BINARY

Field   2:  `d`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     18
Max_length: 10
Decimals:   6
Flags:      BINARY

Field   3:  `e`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 4
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+-------------+------------+------+
| c           | d          | e    |
+-------------+------------+------+
| 251361.0000 | 118.588398 | 6223 |
+-------------+------------+------+
1 row in set (0.27 sec)

mysql>

is mapped to:
c: Type 131    adNumeric
d: Type 131    adNumeric
e: Type 20     adBigInt

which is correct so obviously ADO is not capable of syntax like "rs(fld)+1"...

Addition to my test case:
WScript.echo _
			rs.Fields(0).Name & chr(10) & _
			" Type: " & rs.Fields(0).Type & chr(10) & _
			" Size: " & rs.Fields(0).DefinedSize & chr(10) & _	
			" Scale: " & rs.Fields(0).NumericScale & chr(10) & _
			" Precision: " & rs.Fields(0).Precision

WScript.echo _
			rs.Fields(1).Name & chr(10) & _
			" Type: " & rs.Fields(1).Type & chr(10) & _
			" Size: " & rs.Fields(1).DefinedSize & chr(10) & _	
			" Scale: " & rs.Fields(1).NumericScale & chr(10) & _
			" Precision: " & rs.Fields(1).Precision
WScript.echo _
			rs.Fields(2).Name & chr(10) & _
			" Type: " & rs.Fields(2).Type & chr(10) & _
			" Size: " & rs.Fields(2).DefinedSize & chr(10) & _	
			" Scale: " & rs.Fields(2).NumericScale & chr(10) & _
			" Precision: " & rs.Fields(2).Precision

After all this checking, I would call "Not a bug" here because:
 - query is executed
 - results are retrieved
 - field types are correctly reported