Bug #12805 ADO failed to retrieve the length of LONGBLOB columns
Submitted: 25 Aug 2005 14:02 Modified: 4 Jul 2008 14:38
Reporter: Bogdan Degtyariov Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.11, 3.51.19GA OS:Microsoft Windows (Windows XP)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[25 Aug 2005 14:02] Bogdan Degtyariov
Description:
VB.NET Can't get column count property if table contain LONG_BLOB fields

How to repeat:
Here is a code that shows the problem:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '.Net , ADO 2.6
        On Error GoTo TestMySQL4_Error
        Dim CatalogADO As ADOX.Catalog
        Dim cnTest As ADODB.Connection
        Dim strSQL As String, strConnect As String, strTableName As String
        Dim lTableCols As Long
        Dim iTablePos As Integer

        ' 'CREATE DATABASE test' run from MySql client if needed

        'Testing data
        ' Drivers
        '   MySQL Connector/ODBC v5
        '     5.00.00 00
        '   MySQL ODBC 3.51 Driver
        '     3.51.11 00
        ' Servers
        '   SuSE Linux Enterprise Server 9
        '     MySQL 5.0.03-beta-standard-log via TCP/IP (172.16.100.35 / kellyc / Chimo1!)
        '   RedHat Enterprise Linux 4 ES
        '     MySQL 5.0.11-beta-standard-log via TCP/IP (172.16.100.30 / CIS / ppdata)

        strConnect = "Provider=MSDASQL;" & _
                             " DRIVER=MySQL ODBC 3.51 Driver;" & _
                             " SERVER=192.168.0.2;" & _
                             " DATABASE=test;" & _
                             " UID=CIS; PWD=ppdata;" & _
                             " OPTION=3"

        'Open ADODB connection
        cnTest = New ADODB.Connection
        cnTest.Mode = ADODB.ConnectModeEnum.adModeReadWrite
        cnTest.CommandTimeout = 10000
        cnTest.ConnectionTimeout = 20
        cnTest.Open(strConnect)

        'create table
        strTableName = "TestTable"
        strSQL = "`SystemID` INT NOT NULL" & _
               ", `LongImageData` LONGBLOB NULL"

        strSQL = "CREATE TABLE `" & strTableName & "` (" & strSQL & ") ENGINE=MyISAM"
        cnTest.Execute(strSQL)

        'Open Catalog for Database TEST
        CatalogADO = New ADOX.Catalog
        CatalogADO.ActiveConnection = cnTest
        CatalogADO.Tables.Refresh()

        'find table
        For iTablePos = CatalogADO.Tables.Count - 1 To 0 Step -1
            If UCase$(CatalogADO.Tables(iTablePos).Name) = UCase$(strTableName) Then Exit For
        Next

        'Try to get count of fields in table 
        'Err.Number = 6, Err.Source=Interop.ADOX, Err.Description=Out of present range
        Dim adoxTab As ADOX.Table
        adoxTab = CatalogADO.Tables(iTablePos)

        Dim adoxColumns As ADOX.Columns
        adoxColumns = adoxTab.Columns

        lTableCols = adoxColumns.Count 'It will error on this line

        'Close
        CatalogADO.ActiveConnection = Nothing
        CatalogADO = Nothing
        cnTest.Close() : cnTest = Nothing

        Exit Sub
TestMySQL4_Error:
        MsgBox("Error #" & Format$(Err.Number) & vbCrLf & "Error Source:" & Err.Source & vbCrLf & "Error Desc:" & Err.Description)

        Resume Next

        End
        Resume
    End Sub
[8 Sep 2005 7:57] Bogdan Degtyariov
The most probably it's not MyODBC bug because abovementioned VB.NET fragment works well with MySQL Server 5.0.7. and earlier versions. On other hand versions 5.0.10/5.0.11/5.0.12 have this problem
[24 Jul 2007 16:21] Bogdan Degtyariov
Verified against MyODBC 3.51.16/3.51.17 and MySQL 5.0.45
[27 Jul 2007 23:18] Jim Winstead
Bug #13481 may be a duplicate of this bug.
[27 Aug 2007 11:37] Tonci Grgin
Still a bug.
[31 Jan 2008 23:01] Jim Winstead
Needs to be re-verified with 3.51 and 5.1, and Bogdan reportedly has a preliminary fix.
[4 Feb 2008 18:51] Bogdan Degtyariov
patch and test case

Attachment: patch12805.diff (application/octet-stream, text), 3.20 KiB.

[4 Feb 2008 18:52] Bogdan Degtyariov
LONGBLOB length is reported as 4G, however ADO as other Microsoft applications do not support more than 2G.
[4 Feb 2008 20:10] Jim Winstead
I think that Jess's fix for Bug #30890 in 5.1 may have fixed this in that tree.
[7 Feb 2008 15:28] Bogdan Degtyariov
patch and test case (with SET_DSN_OPTION etc.)

Attachment: patch12805_v2.diff (application/octet-stream, text), 2.62 KiB.

[7 Feb 2008 20:46] Bogdan Degtyariov
Patch with unsigned types for get_column_size()

Attachment: patch12805_v3.diff (application/octet-stream, text), 5.11 KiB.

[11 Feb 2008 16:49] Bogdan Degtyariov
Modified SQLULEN and other stuff related to column sizes

Attachment: patch12805_v5.diff (application/octet-stream, text), 7.06 KiB.

[14 Feb 2008 16:32] MC Brown
A note has been added to the 5.1.2 changelog: 

When accessing column data, FLAG_COLUMN_SIZE_S32 did not limi the octet length or display size reported for fields, causing problems with Microsoft Visual FoxPro.
[7 Mar 2008 18:31] Bogdan Degtyariov
Below is the list of ODBC functions that may cause failures in MS software when retrieving the length of LONGBLOB/LONGTEXT columns:
 * SQLColumns
 * SQLColAttribute 
 * SQLColAttributes
 * SQLDescribeCol
 * SQLSpecialColumns (theoretically can have the same problem)
[11 Mar 2008 22:58] Bogdan Degtyariov
Final version of the patch

Attachment: patch12805_v8.diff (application/octet-stream, text), 7.82 KiB.

[11 Mar 2008 23:00] Bogdan Degtyariov
Please review the final version of the patch.
[12 Mar 2008 11:01] Bogdan Degtyariov
updated patch

Attachment: patch12805_v9.diff (application/octet-stream, text), 7.82 KiB.

[12 Mar 2008 13:27] Bogdan Degtyariov
patch!

Attachment: patch12805_v10.diff (application/octet-stream, text), 7.81 KiB.

[20 Mar 2008 18:20] Bogdan Degtyariov
SQLColAttribute returns SQLLEN to avoid compatibility issues with iODBC/unixODBC

Attachment: patch12805_v11.diff (application/octet-stream, text), 8.49 KiB.

[14 Apr 2008 22:07] Jim Winstead
The patch for this bug was committed, and will be included in 3.51.25.
[4 Jul 2008 14:38] Tony Bedford
An entry has been added to the 3.51.25 changelog.