Error with MySQL query string longer than 255 characters

[JPLouw]

Hi,

I keep getting a Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. I am using Excel 2010. I am running a MySQL query through VBA that is longer than 255 characters, to get around it I am trying to input it as an array. I have read the previous blogs, but I am still stuck. Can you please help, I have attached the VBA coding below.

Thanks in advance,



Sub MySQL1()

'
' MySQL Macro - code sourced from MrExcel
'


Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim sqlstr As Variant
Dim Rs As ADODB.Recordset


sqlstr = "SELECT mfh.symbol, mfh.date, mfh.close, dch.div_amount, dch.cap_gain_amount, sh.num_new, sh.num_old, mfh.volume" _
& "FROM `datafeed`.csi_stock_etf_historical mfh left outer join `datafeed`.csi_div_cap_historical dch" _
& "on (mfh.csi_num = dch.csi_num and mfh.date = dch.date)" _
& "left outer join `datafeed`.csi_split_historical sh" _
& "on (mfh.csi_num = sh.csi_num and mfh.date = sh.date) where mfh.symbol = 'anv' order by mfh.date desc limit 3000"


Server_Name = "XXXXXX"
Database_Name = "XXXX"
User_ID = "XXXX"
Password = "XXXXX"


Set Cn = New ADODB.Connection
Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";OPTION=16427"



Set Rs = New ADODB.Recordset
Rs.Open StringToArray(sqlstring), Cn, adOpenStatic[/COLOR]
With ThisWorkbook.Worksheets("CSI").Range("A:D")
.ClearContents
.CopyFromRecordset Rs
End With



End Sub


'--------------------------------------------------------

Function StringToArray(sqlstring As Variant) As Variant

Const StrLen = 250

Dim NumElems As Integer
Dim Temp() As String

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

StringToArray = Temp

End Function

 

[Norie]

Why are you using an array?

Does it not work if you don't do that?

I'm not even sure there's a limit to the length of the SQL when using ADO.

I've only ever heard of the 255 character limit when the query is done through Excel itself.

 

[JPLouw]

Hi Norie,

I have found a solution, by changing the String to Variant.

Kind regards,
Jan

 

[Norie]

Jan

Glad you've found a solution but I still don't think you need to convert to an array.

MySQL doesn't really have a limit to the length of a query, if there is any it's measured in megabytes apparently.

Perhaps there is some limit in ADO based on the driver.

 

[JPLouw]

Hi Norie,

You are correct, you do not have to convert it to an array. I made the mistake of declaring the SQL query as a string. Declaring it as a Variant solved the problem. So we learn

Kind regards,
Jan

 

[Norie]

Jan

Sorry to ask but are you still passing the SQL statement as an array?

 

[JPLouw]

Hi Norie,

No, just declared my query as a Variant and not a String.

 

[Norie]

You just changed to variant and didn't use the function to convert to an array?

Sorry for being thick, just wondering really.

I don't usually use VBA/ADO with MySQL, it's usually php/MySQL.

 

[Ruddles]

sqlstr = "SELECT mfh.symbol, mfh.date, mfh.close, dch.div_amount, dch.cap_gain_amount, sh.num_new, sh.num_old, mfh.volume" _
& "FROM `datafeed`.csi_stock_etf_historical mfh left outer join `datafeed`.csi_div_cap_historical dch" _
& "on (mfh.csi_num = dch.csi_num and mfh.date = dch.date)" _
& "left outer join `datafeed`.csi_split_historical sh" _
& "on (mfh.csi_num = sh.csi_num and mfh.date = sh.date) where mfh.symbol = 'anv' order by mfh.date desc limit 3000"

I'm surprised it didn't complain at the fact that are no spaces before the FROM, ON and LEFT keywords. The above code gave me:-

SELECT mfh.symbol, mfh.date, mfh.close, dch.div_amount, dch.cap_gain_amount, sh.num_new, sh.num_old, mfh.volumeFROM `datafeed`.csi_stock_etf_historical mfh left outer join `datafeed`.csi_div_cap_historical dchon (mfh.csi_num = dch.csi_num and mfh.date = dch.date)left outer join `datafeed`.csi_split_historical shon (mfh.csi_num = sh.csi_num and mfh.date = sh.date) where mfh.symbol = 'anv' order by mfh.date desc limit 3000

Or did you fix that separately?

 

[Norie]

Ruddels

That was actually my first thought.

I've never actually heard of having to use an array to hold the SQL.