MySQL Bugs: #12805: ADO failed to retrieve the length of LONGBLOB columns

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:	Windows (Windows XP)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

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

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

Verified against MyODBC 3.51.16/3.51.17 and MySQL 5.0.45

Bug #13481 may be a duplicate of this bug.

Still a bug.

Needs to be re-verified with 3.51 and 5.1, and Bogdan reportedly has a preliminary fix.

patch and test case

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

LONGBLOB length is reported as 4G, however ADO as other Microsoft applications do not support more than 2G.

I think that Jess's fix for Bug #30890 in 5.1 may have fixed this in that tree.

patch and test case (with SET_DSN_OPTION etc.)

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

Patch with unsigned types for get_column_size()

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

Modified SQLULEN and other stuff related to column sizes

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

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.

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)

Final version of the patch

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

Please review the final version of the patch.

updated patch

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

patch!

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

SQLColAttribute returns SQLLEN to avoid compatibility issues with iODBC/unixODBC

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

The patch for this bug was committed, and will be included in 3.51.25.

An entry has been added to the 3.51.25 changelog.