MySQL Bugs: #3760: Wrong VarChar field reading with VB6 using ODBC v. 3.51.07.

Bug #3760	Wrong VarChar field reading with VB6 using ODBC v. 3.51.07.
Submitted:	14 May 2004 11:58	Modified:	13 Jun 2013 13:25
Reporter:	Andrea Canova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.07	OS:	Windows (Windows Xp Professional)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
Hi.

I just upgraded the ODBC connector from v. 3.51.06 to v. 3.51.07 to check if the memory problem usage when programming with VB6, Service pack 5 or 6 (just released), has been solved.

It seems working!
All the memory used by recordsets and connections is correctly released when they are closed. Thanks.

But I found a problem reading some fieds in various tables in my database.
In the example provided, I was trying to read a varchar field containing the IP address of a machine.

I found that the chars are grouped together forming a double byte code like when using Unicode.
The problem is that it is NOT Unicode.
Then I wrote a function to convert the varchar field to a string.

When I try to read a text field, for instance, that problem doesn't occur.

The field could contain a value like this:
192.168.1.1

When I examine the varchar field, I found "?????" instead of "192.168.1.1" (using the 3.51.06 ODBC connector).

Data is stored in this way:
2 chars are grouped together.

1 is in the Hi byte.
9 is in the Low byte.

2 is in the next Hi byte.
. is in the next Low byte.

... and so on.

It is possible to examine the bytes using the AscW function instead of the Asc one.

I would like to know:
Is it considered a bug or not?
Is there an option to tell to the ODBC connector to retrive the data in the "old" manner? (without the need to convert it?).
Must I convert all the varchar fields to text (and don't use them anymore)?

The problem is that I must invoke the conversion only when I need to read a varchar field.
It is possible to modify the conversion function to understand if the bytes are grouped or not (to use it even if I need to read a text field), but the conversion is slow.

Thank you very much.
I hope I will receive a quick answer from someone...

Andrea.
canova@logomat.it

How to repeat:
I give you some information about the program I wrote.
I removed all the source code I don't consider relevant to understand the 
problem.

This is one of the field that causes the anomaly.
Field       Type          Attributes   Null   Default value   Extra 
MachineIP   varchar(15)   BINARY       No     0.0.0.0  

    Dim connConnection As ADODB.Connection
    Dim rsRecordset As ADODB.Recordset
    Dim intIndex As Integer
        
    connConnection.Open "Test ODBC"
    'It is the new driver: ODBC 3.51.07.
    
    'I read the machines_settings table.
    'It contains a field called MachineIP. (IP address).
    'It is a 15 varchar field.

    Set rsRecordset = New ADODB.Recordset
    rsRecordset.CursorType = adOpenStatic
    rsRecordset.CursorLocation = adUseClient
    rsRecordset.Open "SELECT * FROM machines_settings", connConnessione, , , adCmdText
    
    gintMachinesNo = rsRecordset.RecordCount
    
    If gintMachinesNo > 0 Then
        intIndex = 0
        
        Do While Not rsRecordset.EOF
            'I read all the records.
            garrstrMachineIP(intIndex) = VarCharConversion(rsRecordset!MachineIP)

            rsRecordset.MoveNext
            intIndex = intIndex + 1
        Loop
    End If
    
    'I close the recordset.
    rsRecordset.Close
    
    Set rsRecordset = Nothing

Suggested fix:
Public Function VarCharConversion(strString As String) As String
    Dim intIndex As Integer
    Dim strConvertedString As String
    
    strConvertedString = ""
    
    For intIndex = 1 To Len(strString)
        Debug.Print Chr(AscW(Mid(strString, intIndex, 1)) And 255)
        Debug.Print Chr(AscW(Mid(strString, intIndex, 1)) / 256)
    
        strConvertedString = strConvertedString & Chr(AscW(Mid(strString, intIndex, 1)) And 255)
        strConvertedString = strConvertedString & Chr(AscW(Mid(strString, intIndex, 1)) / 256)
    Next intIndex

    Debug.Print strConvertedString
    
    VarCharConversion = strConvertedString
End Function

I have created a test case which seems to show the problem indicated by User. This is, in deed, a bug. Fix being worked out.

ADO is requesting the column data as BINARY. This is a result of the column being defined with the BINARY option.

Its not clear to me why the column needs to have the BINARY option so I suggest defining the column without the BINARY option. This solves the problem in my testing.

Having said this; there is something in the driver which probably needs to be fixed here - I just hope that leaving BINARY option off can be something you can do to correct the problem right now. If so then; we can leave this as a bug but downgrade the priority.

VB 6 test

Attachment: BUG-3760.tar.gz (application/x-gzip, text), 3.76 KiB.

Could you please try the latest released version 3.51.20. Thanks in
advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Yes, i am facing same problem now.
using vb6 / mysql 5
odbc driver 3.51.24.00 and 5.01.05.00

Table 
CREATE TABLE  `testdata`.`transfer` (
  `Book` char(2) NOT NULL,
  `Vrno` char(6) NOT NULL,
  `Srno` smallint(6) NOT NULL,
  `ITEMCODE` char(12) NOT NULL,
  `QTY` smallint(6) NOT NULL,
  PRIMARY KEY  (`Book`,`Vrno`,`Srno`),
  KEY `Index_transfer_itemcode` (`ITEMCODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 11264 kB';

VB6 Code

Private ConnWrite As New ADODB.Connection
Private rsTemp As New ADODB.Recordset

ConnWrite.Open connstr
ConnWrite.BeginTrans
Public Const connstr = "Provider=MSDASQL;" + _
                "DRIVER={MySQL ODBC 5.1 driver};" + _
                "Server=wserver;UID=root;PWD=admin;" + _
                "database=testdata;option=16387;"

Dim cmdado As New ADODB.Command
   
cmdado.ActiveConnection = ConnWrite
cmdado.CommandType = adCmdText   

If StatusBarTransfer.Panels("Mode").text = "Add Mode" Then
        Set rsTemp = ConnWrite.Execute("select lpad(ifnull(max(vrno),0) +1,6,' ') vrno from transfer where book = '" & mBook & "'")
        mvrno = rsTemp("vrno").Value
        cmdado.CommandText = "insert into mymsg select lpad(ifnull(max(vrno),0)+1,6,' ') vrno from transfer where book = '" & mBook & "'"
        cmdado.Execute
        rsTemp.Close
        Set rsTemp = Nothing
End If

*****
Now here when i am checking mymsg table it is giving me correct value but when i am checking value of mvrno it is giving me like  '††?'

This bug needs to be re-checked

No problem found. Even if the bug existed it must have been fixed in the later versions of Connector/ODBC.
Closing the report.