| Bug #34535 | IsNumeric on numeric type field which is unsigned returns FALSE (3.51.23) | ||
|---|---|---|---|
| Submitted: | 14 Feb 2008 8:49 | Modified: | 8 Apr 2008 21:26 |
| Reporter: | Yog Daftary | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.12.0 | OS: | Windows (2003 server and XP-Prof SP2 client) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[14 Feb 2008 8:56]
Tonci Grgin
Hi Yog and thanks for your report. Can you please try latest MyODBC version as 3.51.12 is very very old one?
[14 Feb 2008 11:36]
Yog Daftary
Thanks Tonci Grgin I already tried MyODBC version 3.51.23, but the problem is still there, whereas I also tried MyODBC version 5.1 but many feature doesn't work for eg: Select Date_Format(Current_TimeStamp, '%d/%m/%Y %h:%i:%s %p') Will return ??/??/????/ ??:??:??. Is there any other MyODBC version available?
[14 Feb 2008 12:19]
Tonci Grgin
Hi Yog. > Select Date_Format(Current_TimeStamp, '%d/%m/%Y %h:%i:%s %p') Will return ??/??/????/ ??:??:??. This is not c/ODBC problem but server one (regarding insufficient metadata returned, search bugs db). There is a simple way around this until it's properly fixed in server, use CAST as DATE (or CHAR or whatever suits you). > Is there any other MyODBC version available? No. I think there's something wrong with VB6 as we are getting a lot of reports on it, whilst problems are not repeatable with VS2005 for example... Can you try your failing query in mysql cl client and paste the output here? Mind you to start mysql with -T option (mysql -uuser -p -T test). I want to see what MySQL server outputs as field type for Table1.FIELD1.
[14 Feb 2008 13:08]
Yog Daftary
Hi! Tonci Grgin, Now I updated the MyODBC driver with 3.51.23. but the problem of "Automation error" is still there (the Date_Format(Current_TimeStamp, '%d/%m/%Y %h:%i:%s %p') problem is solved now). Also I had not tried the same query in VS2005 as I don't have that, I am working on VB6.0 only. Any other options/parameters to set?
[18 Feb 2008 8:48]
Tonci Grgin
Hi Yog. Please review my test case attached. I have no problems running with windows scripting host, ie. everything works as expected especially "" Type: " & rs.Fields(0).Type & chr(10) & _" which returns correct value (2). My guess is that something is messed up with VB6... We can still try chasing the problem dow though. For that I will need DM trace file attached. Environment: MyODBC 3.51.23GA, MySQL server 5.0.58pb1083 on WinXP Pro SP2 localhost. mysql> create table bug34535( FIELD1 SMALLINT NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.05 sec) mysql> insert into bug34535 values (0),(1),(2); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from bug34535; +--------+ | FIELD1 | +--------+ | 0 | | 1 | | 2 | +--------+ 3 rows in set (0.00 sec)
[18 Feb 2008 8:51]
Tonci Grgin
VBS test case
Attachment: 34535.vbs (application/octet-stream, text), 1.73 KiB.
[20 Feb 2008 5:54]
Yog Daftary
Gr8, Tonci Grgin, It is working fine, but now to repeat the error, simply change the FIELD to UNSIGNED SMALLINT, and now you can see that the code line
If IsNumeric(rs.Fields(0)) then
if rs("FIELD1").Value = 0 then
msgbox "Yes"
end if
end if
will not execute at all, as the IsNumeric function is unable to determine the data type and to raise the error use following code directly:
'If IsNumeric(rs.Fields(0)) Then
If rs("FIELD1").Value = 0 Then
MsgBox "Yes"
End If
'End If
Thanks again to help me simplify the problem
[5 Mar 2008 5:57]
Yog Daftary
Tonci Grgin, I am waiting for your reply...
[5 Mar 2008 11:26]
Tonci Grgin
Yog, you are not following me... I asked for info on metadata: 4.1.22: mysql> select * from bug34535; Catalog: 'def' Database: 'test' Table: 'bug34535' Name: 'FIELD1' Type: 2 Length: 5 Max length: 1 Is_null: 0 Flags: 49187 Decimals: 0 == NOT_NULL_FLAG 1 PRI_KEY_FLAG 2 UNSIGNED_FLAG 32 PART_KEY_FLAG 16384 NUM_FLAG 32768 ------------------------------ total 49187 field->type: MYSQL_TYPE_SHORT 5.0.58: mysql> SELECT * FROM bug34535; Field 1: `FIELD1` Catalog: `def` Database: `test` Table: `bug34535` Org_table: `bug34535` Type: SHORT Collation: binary (63) Length: 5 Max_length: 1 Decimals: 0 Flags: NOT_NULL PRI_KEY UNSIGNED NO_DEFAULT_VALUE NUM PART_KEY Now, binary collation in 5.0.x is probably to blame but I will need to see ODBC trace attached here before I can make ruling. So please attach log from Start/Control Panel/Administrative Tools/Data Sources (ODBC)/Tracing - Start tracing now while you run your test case.
[5 Mar 2008 11:28]
Tonci Grgin
Ah, one more thing. Is the problem repeatable on both 4.1.x and 5.0.x servers?
[8 Mar 2008 13:48]
Yog Daftary
Tonci, I don't have control over the database server, so I can't start/stop it. Also as I said earlier, you can raise the error by removing the "'If IsNumeric(rs.Fields(0)) then" line from the code. Pls reply me at your earlies.
[8 Mar 2008 13:48]
Yog Daftary
Tonci, I don't have control over the database server, so I can't start/stop it. Also as I said earlier, you can raise the error by removing the "'If IsNumeric(rs.Fields(0)) then" line from the code. Pls reply me at your earliest.
[14 Mar 2008 19:31]
Tonci Grgin
Well, if I am to do anything I would at least need ODBC trace from driver manager...
[31 Mar 2008 14:12]
Tonci Grgin
Yog, looking into this again. One comment, if it's not absolutely necessary, please keep all of your comments/files public so others may profit from them.
[4 Apr 2008 10:45]
Tonci Grgin
I will change synopsis from "Automation error occurs after upgrading the MySQL Server" to "IsNumeric on numeric type field which is unsigned returns FALSE (3.51.23)" now. Hmm, my tests show 4.1 and 5.0 return same result (ie. fail in same way: SMALLINT: 4.1 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" "test", <Null>, "t_desc_col", "c7", 5, "smallint", 5, 2, 0, 10, 1, "", <Null>, 5, <Null>, <Null>, 1, TRUNC: "YE" 1 row fetched from 18 columns. 5.0 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" "test", <Null>, "t_desc_col", "c7", 5, "smallint", 5, 2, 0, 10, 1, "", <Null>, 5, <Null>, <Null>, 1, TRUNC: "YE" 1 row fetched from 18 columns. SMALLINT UNSIGNED: 4.1 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" "test", <Null>, "t_desc_col", "c7", 5, TRUNC: "smallint uns", 5, 2, 0, 10, 1, "", <Null>, 5, <Null>, <Null>, 1, TRUNC: "YE" 1 row fetched from 18 columns. 5.0 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" "test;", <Null>, "t_desc_col", "c7", 5, TRUNC: "smallint uns", 5, 2, 0, 10, 1, "", <Null>, 5, <Null>, <Null>, 1, TRUNC: "YE" 1 row fetched from 18 columns. I am verifying this as a bug as I'm unable to make IsNumeric(rs smallint unsigned field) return TRUE as it should according to http://msdn2.microsoft.com/en-us/library/6cd3f6w1.aspx: IsNumeric returns True if the data type of Expression is Boolean, Byte, Decimal, Double, Integer, Long, SByte, Short, Single, UInteger, ULong, or UShort, or an Object that contains one of those numeric types. It also returns True if Expression is a Char or String that can be successfully converted to a number.
[8 Apr 2008 18:58]
Bogdan Degtyariov
Tonci, you are right about the truncating problem... MyODBC truncates the TYPE_NAME column for both server versions (4.0 and 5.0)
[8 Apr 2008 19:08]
Bogdan Degtyariov
Although the TYPE_NAME column is not truncated in 3.51.24, IsNumeric() function is still returning false for SMALLINT UNSIGNED. Get Data All for SQLColumns(): Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" "test9", <Null>, "bug34535", "field1", 5, "smallint unsigned", 5, 2, 0, 10, 1, "", "0", 5, <Null>, <Null>, 1, "YES" 1 row fetched from 18 columns.
[8 Apr 2008 21:26]
Bogdan Degtyariov
Incapability of ADO library to process all UNSIGNED numeric types (not only SMALLINT) has been originated from Microsoft SQL Server column types that are all signed except the TINYINT unsigned type (however, you cannot change unsigned TINYINT column to signed TINYINT). That is why IsNumeric() VB function returns FALSE for UNSIGNED INT. When using the watches window to look inside the recordset properties the type of the value is reported as: EXPRESSION VALUE TYPE Value : 0 : Variant/<Unsupported variant type> For any signed type it looks as: EXPRESSION VALUE TYPE Value : 0 : Variant/Long This is expected behavior. The bug can be fixed only in ADO sources.
[4 Aug 2008 9:43]
Chee-Leong Lam
Hi there,
I've had this problem with VB6, but it seems to go away if I use CInt on the recordset field, i.e.:
Dim a As Integer
a = Cint(TB.Fields("field").value) '<-if the CInt is not here, it returns the 'Automation uses an unrecognized...' error.
See if this works.
cheers
Description: Previous MySQL Server version : 4.1.12a-nt Current MySQL Server version : 5.0.45-community-nt I am using VB6 with ODBC Connector version 3.51.12 at client (developer) side. The code is like this: dim cn as adodb.connection dim rs as adodb.recordset cn.open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=<ServerHostName>;DATABASE=<Database>;USER=<User>;PASSWORD=<Password>;OPTION=3;" rs.open "Select * From Table1", cn,adOpenStatic, adLockOptimistic if rs("FIELD1").Value = 1 then '<-- Line where error occurs msgbox "Yes" end if rs.close cn.close The datatype of the FIELD1 is SMALLINT(5) and before upgrading the server it was working fine, but after upgrading, VB6.0 is generating the error "Variable uses an Automation type not supported in Visual Basic" at the given line of code. I tried to change the datatype of FIELD1 to MEDIUMINT & INT but doesn't work, where as it works fine when the data type is TINYINT. I also tried to upgrade the ODBC connector to 3.51.23, still not working. Again, if I use code like this: if getNumber(rs("FIELD1").Value) = 1 then Here the "getNumber" is a simple function which accepts the VARIANT data and simply returns integer. Then it works fine again. I can't make out where the problem. If this remains unsolved, either I have to redesign all the databases or all my softwares. Pls tell me what to do now. How to repeat: Create a table with few fields, one should be of SMALLINT datatype, using ODBC connector in VB6.0 (Microsoft ActiveX Data Objects 2.8) connect to this table. Try to access the field directly as shown in the description, it will generate error.