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?

 

[poolhall]

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:

Dim fld as ADODB.Field

OOk, this time I get the "Run-time error '70': Permission Denied" while writing the second set of fields 'poster_name' and 'body'. So, I got the line "M0ntY1,Arthur Abraham = 1.16,400< br />3,Under 8.5 = 1.36,100< br />8,Bradley Pryce = 1.15,200" in the text file, and the row "Set ts = FSO.CreateTextFile(Filename:="C:\TestMySQLOutput.txt", Overwrite:=True, Unicode:=False)" gets highlighted.

And I get the same error when trying this:

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)

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:

Though I get correct and full results in the immediate window.

 

[poolhall]

My setup:

MySQL client version: 5.0.51b
Apache Version Apache/2.2.8 (Unix)
PHP version 5.2.5

 

[xenou]

Nuts. That's frustrating. It seems it doesn't want to overwrite the file...the text you see is exactly the same as previously and probably was created before.

This will create a new file with a unique file name instead:

        Set ts = FSO.CreateTextFile(Filename:="C:\TestMySQLOutput" & Format(Now, "yymmddhhnnss") & ".txt", Overwrite:=True, Unicode:=False)

But in any case, we've determined that the data is all there but its not getting into Excel. Maybe some issue with transferring the field value because of its length (>255) ? Will output to text work?

Alex

 

[poolhall]

No, actually the line is re-written, the sub goes the whole one loop and stops when trying to write the second line into the file. I emptied the file before starting the sub and made sure this is a newly written line.

And I don't think that the text length is the issue because I tried retrieving fields of various length, and they get truncated even when fields value are short. And for some fields I got more than 300 symbols.

 

[xenou]

My code is probably wrong with the loop structure.
Change:

    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

To this:

    [COLOR="Blue"]Set FSO = CreateObject("Scripting.FileSystemObject")
    Set ts = FSO.CreateTextFile(Filename:="C:\TestMySQLOutput", Overwrite:=True, Unicode:=False)[/COLOR]
    rs.MoveFirst
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count
            ts.Write rs.Fields(i).Value
        Next i
        ts.WriteBlankLines (3)
        rs.MoveNext
    Loop

Moving the creation of the textstream object out of the loop (it only needs to be created once, not over and over...)

 

[poolhall]

Now it works, I only replaced, per your instructions above, this

For i = 0 To rs.Fields.Count

with this:

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

Now I have two other issues:
a) fields are concatenated together. Do you know how I can separate them using, let's say, a comma?
b) when a field length is really big, I'm getting the "Run-time error '5': Invalid procedure call or argument". The sub stops at the line "ts.Write rs.Fields(i).Value" while trying to write this field. Shorter fields from the same query stay recorded to the file. At this point I was able to record fields with the length just under 800 symbols, but writing a field of 2700 symbols produced that error.

Thanks a lot, Alex!!!

 

[xenou]

Hmmm...I wasn't aware of limits to a text string length here. Not sure what's going on.

More tries below. I'm going to see if we get different results using a different text output. Also, I'm using debug again to so you view the text in the immediate window. Guess its time to put some error handlers in here...

Wondering if there's some strange character or binary thingamabob in the data that's creating problems writing to a text file? I have to thing all your fields hold text data - but what are the datatypes of the fields?

    '//Purpose:
    'Attempting two write two textfiles with a different output syntax
    'First File uses Write # for each record, records have one field per line
    'Second File uses Write # with fields concatenated to a delimited string
    
    'New Declarations
    Dim A As Integer, B As Integer
    Dim strFileNameA As String, strFileNameB As String
    Dim strTemp As String
    Dim intCount As Long
    
    On Error GoTo Handler:
    
    intCount = 0
    A = FreeFile
    strFileNameA = "C:\TestMySQLOutput_A_" & Format(Now, "yyyymmdd_hhnnss") & ".txt"
    Open strFileNameA For Output As #A
    
    rs.f
    rs.MoveFirst
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            Debug.Print rs.Fields(i).Name & ":" & rs.Fields(i).Value
            Write #A, rs.Fields(i).Value '//Write Field value (each field is a line)
        Next i
        Write #A, '//blank line betweeen records
        rs.MoveNext
    Loop
    
    '-------------------------------------------
    'Second version - expected each record on a line with semicolons between fields
    intCount = 0
    B = FreeFile
    strFileNameB = "C:\TestMySQLOutput_B_" & Format(Now, "yyyymmdd_hhnnss") & ".txt"
    Open strFileNameB For Output As #B
    
    rs.MoveFirst
    Do While Not rs.EOF
        strTemp = ""
        For i = 0 To rs.Fields.Count - 1
            strTemp = strTemp & rs.Fields(i) & ";" '//Create string of entire record
        Next i
        strTemp = Left(strTemp, Len(strTemp) - 1) '//Remove ending semi-colon
        Debug.Print strTemp
        Write #B, strTemp '//Write record to text file - expected each record on a line, semi-colon delimited
        rs.MoveNext
    Loop


'Add at end of procedure before End Sub
'Note - probably close recordset and set rs = nothing etc. should all go here too (after Handler: and On Error Resume Next ... so all objects and files are cleaned up before exiting the sub
Handler:
On Error Resume Next
MsgBox "Error: " & Err.Number & Err.Description
Close #A
Close #B

End Sub

 

[poolhall]

Alex, now it works perfectly! Both files were created and contained all data they were supposed to contain. (I commented out "rs.f" line, though).

The datatypes for the fields are tinytext for 'poster_name' and text for the 'body' (the problem was this the latter).

I really appreciate your help and am happy that I can now move forward with my little project!