| Bug #26110 | MyODBC ADO field attributes adFldIsNullable,adFldMayBeNull not working | ||
|---|---|---|---|
| Submitted: | 6 Feb 2007 9:43 | Modified: | 19 Oct 2007 15:09 |
| Reporter: | Erica Moss | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.0.11 | OS: | Windows (win xp) |
| Assigned to: | Georg Richter | CPU Architecture: | Any |
[8 Aug 2007 5:49]
Martina Jindrů
I got equal result for this configuration:
==============
ODBC driver 5.00.12
MySQL 5.0.45
Default charset CP1250 (server, database, table)
VFP 9.0 SP 1
script test.sql:
================
drop table XXT002I;
create table XXT002I (XX000 Integer, XX001 DECIMAL(10,4), XX002 FLOAT(10,4) ) ENGINE=INNODB;
INSERT INTO XXT002I (XX000,XX001,XX002) VALUES (1,20.22,10.11);
drop table XXT002M;
create table XXT002M (XX000 Integer, XX001 DECIMAL(10,4), XX002 FLOAT(10,4) ) ENGINE=MYISAM;
INSERT INTO XXT002M (XX000,XX001,XX002) VALUES (1,20.22,10.11);
VFP source code:
================
lihdbc=SQLSTRINGCONNECT("uid=XXX;pwd=XXX;driver={MySQL Connector/ODBC v5};Database=anydatabase;SERVER=localhost;PORT=3506;OPTION=0;STMT=SET NAMES cp1250")
** OPTION has no efect
?SQLEXEC(lihdbc,"SELECT * FROM XXT002I") && InnoDB
DISPLAY STRUCTURE to file c:\struc.txt noconsole
MODIFY FILE e:\struc.txt
?SQLEXEC(lihdbc,"SELECT * FROM XXT002M") && MyISAM
DISPLAY STRUCTURE to file c:\struc.txt noconsole
MODIFY FILE e:\struc.txt
?SQLDISCONNECT(lihdbc)
Note: ODBC driver 3.51.17 return correctly result - all fields support NULL value.
=====
Result:
=======
Field Field Name Type Width Dec Index Collate Nulls Next Step
1 XX000 Integer 4 No
2 XX001 Currency 8 4 Yes
3 XX002 Double 8 1 No
Result trace:
=============
Full log: http://disk.jabbim.cz/gorila@dione.zcu.cz/ODBC%205.00%20+%20NULL.zip
config 9a0-c4 EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)
HSTMT 023E1BB0
UWORD 1
UCHAR * 0x0012F3A4 [ 5] "XX000"
SWORD 255
SWORD * 0x0012F11A (5)
SWORD * 0x01C57314 (4)
SQLULEN * 0x01C5731C (11)
SWORD * 0x01C57320 (0)
SWORD * 0x0012F112 (0)
...
config 9a0-c4 EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)
HSTMT 023E1BB0
UWORD 2
UCHAR * 0x0012F3A4 [ 5] "XX001"
SWORD 255
SWORD * 0x0012F11A (5)
SWORD * 0x01C57328 (3)
SQLULEN * 0x01C57330 (10)
SWORD * 0x01C57334 (4)
SWORD * 0x0012F112 (1)
...
config 9a0-c4 EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)
HSTMT 023E1BB0
UWORD 3
UCHAR * 0x0012F3A4 [ 5] "XX002"
SWORD 255
SWORD * 0x0012F11A (5)
SWORD * 0x01C5733C (7)
SQLULEN * 0x01C57344 (10)
SWORD * 0x01C57348 (4)
SWORD * 0x0012F112 (0)
[19 Oct 2007 15:09]
Erica Moss
Tested this using MyODBC 5.1, and it appears to be resolved. Test case added to the ADO bug regression suite
Description: The drivers aren't reporting to ADO when mysql columns may be null. The attributes adFldIsNullable,adFldMayBeNull are not present. adFldMayBeNull: Indicates that you can read Null values from the field. adFldIsNullable Indicates that the field accepts Null values. How to repeat: mysql>CREATE TABLE ADO_TEST (C1 INT, C2 CHAR(1)); mysql>INSERT INTO ado_test VALUES (1, 'A'); ## IN VB OR VBS Dim strSQL As String Dim strConn As String Dim oConn As ADODB.Connection Dim oRs As ADODB.Recordset Dim fld As ADODB.Field Public Function myTest() Set oConn = New ADODB.Connection Set oRs = New ADODB.Recordset strSQL = "select * from ado_test" strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;DATABASE=ado" & _ ";USER=root;PASSWORD=mypass;OPTION=3;" oConn.ConnectionString = strConn oConn.CursorLocation = CInt(adUseClient) oConn.Open oRs.Open strSQL, oConn, adOpenStatic oRs.MoveFirst For Each fld In oRs.Fields If fld.Name = "c1" Then MsgBox adFldMayBeNull And fld.Attributes MsgBox adFldIsNullable And fld.Attributes End If Next End Function ******************************* This returns 0 for both adFldMayBeNull, and adFldIsNullable. Any null'able column type behaves identically so these attributes are evidently not working at all.