Data Retrieved from MySQL Appears Truncated

[poolhall]

I'm using ODBC driver v.5.1, Excel 2007, Windows Vista 64bit.

I use this subroutine to retrieve some data from a remote MySQL server:

Sub ADOExcelSQLServer()
    Dim Cn As ADODB.Connection
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    
    SQLStr = "SELECT poster_name, body FROM `smf_messages` WHERE `id_topic` =14322"
     
    Set Cn = New ADODB.Connection
    
    Cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=cracked.no;Port=3306;charset=CP1250;Database=smf_forum;User=user;Password=userpass;Option=3;"

    rs.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("A1")
        .ClearContents
        .CopyFromRecordset rs
    End With

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

This sub retrieves data but it appears truncated. This is an example of what query produces when retrieved by this sub:

M0ntY	1,Arthur Abraham = 1.16,400<br />3,Under 8.5 = 1.36,100<br />
Америка	[hide]2,Mike Alvarado = 1.11, 500<br />5,Victor Ortiz = 1.22,
M0ntY	[hide]12,Misha Tyson = 1.06, 500<br />13,Over 5.5, 100[/hide]

and this is actual data retrieved from PhpMyAdmin:

M0ntY	1,Arthur Abraham = 1.16,400<br />3,Under 8.5 = 1.36,100<br />8,Bradley Pryce = 1.15,200
Америка	[hide]2,Mike Alvarado = 1.11, 500<br />5,Victor Ortiz = 1.22, 600<br />7,Over 9.5 = 2.2, 300<br />[/hide]
M0ntY	[hide]12,Misha Tyson = 1.06, 500<br />13,Over 5.5, 100[/hide]

Actually, there is a bunch of "< br />" in there, but this forum treats them as line breaks even in the CODE tags.

Why does the data appears truncated? Is this a ODBC driver problem or Excel's fauld or it's me doing something wrong?

 

[nbrcrunch]

might help if I read your whole post.

(Reply removed)

 

[xenou]

One thought - if there's return characters in there you might not be able to "see" the whole line in the cell - possibly just increase the row height and you'll find what you are looking for... (?)


If that fails, I might step through this and use a getRows method on the recordset to dump this into an array (you could then view the array in debug mode with the watch window). Another idea would be to output to text, where you can see the actual values better. Notepad++ (open source) is great for text editing as it has the option to show all characters from its view menu, even tabs, carriage returns, line feeds, etc.

Might work:

Sub ADOExcelSQLServer()
    Dim Cn As ADODB.Connection
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Dim FSO As Object
    Dim ts As Object
    Dim i As Long
    Dim a
    
    Set rs = New ADODB.Recordset
    
    SQLStr = "SELECT poster_name, body FROM `smf_messages` WHERE `id_topic` =14322"
     
    Set Cn = New ADODB.Connection
    
    Cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=cracked.no;Port=3306;charset=CP1250;Database=smf_forum;User=user;Password=userpass;Option=3;"

    rs.Open SQLStr, Cn, adOpenStatic

[COLOR="Navy"]    '-------------------------------
    'Output to array
    a = rs.GetRows
    
    '-------------------------------
    'Or output to text file

    rs.MoveFirst
    Do While Not rs.EOF
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set ts = FSO.CreateTextFile(Filename:="C:\TestMySQLOutput", Overwrite:=True, Unicode:=False)
        For i = 0 To rs.Fields.Count
            ts.Write rs.Fields(i).Value
        Next i
        ts.WriteBlankLines (3)
        rs.MoveNext
    Loop
    
    On Error Resume Next
    ts.Close
    Set ts = Nothing
    Set FSO = Nothing[/COLOR]
    
'    With Worksheets("Sheet1").Range("A1")
'        .ClearContents
'        .CopyFromRecordset rs
'    End With

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

 

[poolhall]

Alexander Barnes, thanks a lot for the reply!

No, there were no returns characters in the cells.

Using your subroutine, I can see the correct and full results in the watch window, but when it outputs to a text file, it writes the first line "M0ntY1,Arthur Abraham = 1.16,400< br />3,Under 8.5 = 1.36,100< br />8,Bradley Pryce = 1.15,200", which is first values for 'poster_name' and 'body' fields and then the sub stops with the Run-Time Error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal. It then highlights "ts.Write rs.Fields(i).Value" row. Can you help further, please?

 

[xenou]

ADVERTISEMENT

That's a bit odd...I think recordset fields are zero based. Let's try a different loop, perhaps?


Changing:

        For i = 0 To rs.Fields.Count
            ts.Write rs.Fields(i).Value
        Next i

To:

        For Each fld In rs.Fields
            ts.Write fld.Value
        Next fld

And adding the declaration:

[COLOR="Navy"]Dim fld as ADODB.Field[/COLOR]

 

[xenou]

In addition to the above, you may want to try a simple loop with values in the immediate window only, no text file output here:

    rs.Open SQLStr, Cn, adOpenStatic

    Do While Not rs.EOF
        For Each fld In rs.Fields
            Debug.Print "name: " & fld.Name
            Debug.Print "Type: " & fld.Type
            Debug.Print "Size: " & fld.ActualSize
            Debug.Print "Value: " & fld.Value
        Next fld
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing

Again, Adding:

[COLOR="Navy"]Dim fld as ADODB.Field[/COLOR]

 

[xenou]

ADVERTISEMENT

Ah, and yet a third response:
This needed to be:

For i = 0 To rs.Fields.Count - 1

(fields are zero based but field count is one based)

 

[poolhall]

thanks a lot for the follow-up!

I will try this when I get home tonight - I don't have access to the database at work.

 

[xenou]

Sure thing. I just loaded a MySql database to my laptop a few weeks ago, along with Apache. Maybe you can describe what kind of setup you have? I'd be interested how its working...

If possible, please confirm that the username and password above is falsified...otherwise, lets get an admin to put asterisks in there...

Alex.

 

[poolhall]

yes, Alex, the server name and account info are falsified.

The database is a MySQL database created by a Simple Machines Forum installation. I cannot give more info at this time since I'm at work and don't have access to anywhere, virtually, but if you need more info I can give it to you when I get home tonight.