| 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: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51.12 | OS: | Windows (Windows 2003 server) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ADO | ||
[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
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 ...