MyODBC & Excel VBA - Too long query ?
 

Hello,

I've tried to record a macro in ms excel to connect to mySQL server
using myODBC driver, then execute SQL query and return the data to
excel sheet. When I run the query through MS Query, it works fine. But
when I try to use this query in macro, it ends with run-time error 13:
type mismatch. I've also tried truncating lines to 127 chars with no
positive results.

My SQL query looks like this:

(
SELECT
'4',CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as
PID, trn.created_date as date_time,slacko.value as priority,
acv.description, att.attribute_name as Operation, acv.old_value
value_from, acv.new_value value_to,
concat(user.first_name,' ' ,user.last_name ) as Person,issuet.value as
IssueType
FROM scarab_issue iss
JOIN scarab_activity acv on iss.issue_id = acv.issue_id
JOIN scarab_transaction trn ON acv.transaction_id =
trn.transaction_id
left outer join scarab_issue_attribute_value slacko on
slacko.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 7 )
LEFT OUTER JOIN scarab_attribute att on att.attribute_id =
acv.attribute_id
JOIN turbine_user user ON trn.created_by = user.user_id
left join scarab_issue_type itype on iss.type_id =
itype.issue_type_id
left outer join scarab_activity ipr on iss.issue_id = ipr.issue_id
and ipr.attribute_id = 112
left outer join scarab_issue_attribute_value issuet ON
issuet.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 112 )
WHERE iss.module_id = 101
)
union
( SELECT '5', CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count),
4)) as PID, trn.created_date as date_time, slacko.value as priority,
null, 'To workgroup', null, area.value as value_to, null,null
FROM scarab_issue iss
JOIN scarab_activity acv on iss.issue_id = acv.issue_id and
acv.activity_id = (SELECT min( activity_id ) from scarab_activity
where acv.issue_id = issue_id )
JOIN scarab_transaction trn ON acv.transaction_id =
trn.transaction_id
LEFT OUTER JOIN scarab_issue_attribute_value status on
status.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 3 )
left outer join scarab_issue_attribute_value issuet ON
issuet.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 112 )
left outer join scarab_issue_attribute_value cldate ON
cldate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 111 )
left outer join scarab_issue_attribute_value asdate ON
asdate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 108 )
left outer join scarab_issue_attribute_value cmdate ON
cmdate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 109 )
left outer join scarab_issue_attribute_value area on area.value_id
= (SELECT max( value_id ) fROM scarab_issue_attribute_value where
issue_id = iss.issue_ID AND attribute_id = 12 )
left outer join scarab_issue_attribute_value slacko on
slacko.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 7 )
WHERE iss.module_id = 101
)



Any solution to this problem ?

--
Peter M.

MyODBC & Excel VBA - Too long query ?
 

Peter

I think you need to split up your SQL statement into an array where each
element has no more than 255 characters. Excel (or MSQuery, I'm not sure
which) will then join all the elements together into one long SQL statement.
You can run the string variable that contains your Sql through this function

Function StringToArray(sSQL As String) As Variant

Dim aTemp() As String
Dim i As Long, j As Long

ReDim aTemp(1 To (Len(sSQL) \ 255) + 1)
j = 1

For i = 1 To Len(sSQL) Step 255
aTemp(j) = Mid(sSQL, i, 255)
j = j + 1
Next i

StringToArray = aTemp

End Function


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Peter M. wrote:
Hello,

I've tried to record a macro in ms excel to connect to mySQL server
using myODBC driver, then execute SQL query and return the data to
excel sheet. When I run the query through MS Query, it works fine. But
when I try to use this query in macro, it ends with run-time error 13:
type mismatch. I've also tried truncating lines to 127 chars with no
positive results.

My SQL query looks like this:

(
SELECT
'4',CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as
PID, trn.created_date as date_time,slacko.value as priority,
acv.description, att.attribute_name as Operation, acv.old_value
value_from, acv.new_value value_to,
concat(user.first_name,' ' ,user.last_name ) as Person,issuet.value as
IssueType
FROM scarab_issue iss
JOIN scarab_activity acv on iss.issue_id = acv.issue_id
JOIN scarab_transaction trn ON acv.transaction_id =
trn.transaction_id
left outer join scarab_issue_attribute_value slacko on
slacko.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 7 )
LEFT OUTER JOIN scarab_attribute att on att.attribute_id =
acv.attribute_id
JOIN turbine_user user ON trn.created_by = user.user_id
left join scarab_issue_type itype on iss.type_id =
itype.issue_type_id
left outer join scarab_activity ipr on iss.issue_id = ipr.issue_id
and ipr.attribute_id = 112
left outer join scarab_issue_attribute_value issuet ON
issuet.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 112 )
WHERE iss.module_id = 101
)
union
( SELECT '5', CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count),
4)) as PID, trn.created_date as date_time, slacko.value as priority,
null, 'To workgroup', null, area.value as value_to, null,null
FROM scarab_issue iss
JOIN scarab_activity acv on iss.issue_id = acv.issue_id and
acv.activity_id = (SELECT min( activity_id ) from scarab_activity
where acv.issue_id = issue_id )
JOIN scarab_transaction trn ON acv.transaction_id =
trn.transaction_id
LEFT OUTER JOIN scarab_issue_attribute_value status on
status.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 3 )
left outer join scarab_issue_attribute_value issuet ON
issuet.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 112 )
left outer join scarab_issue_attribute_value cldate ON
cldate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 111 )
left outer join scarab_issue_attribute_value asdate ON
asdate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 108 )
left outer join scarab_issue_attribute_value cmdate ON
cmdate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 109 )
left outer join scarab_issue_attribute_value area on area.value_id
= (SELECT max( value_id ) fROM scarab_issue_attribute_value where
issue_id = iss.issue_ID AND attribute_id = 12 )
left outer join scarab_issue_attribute_value slacko on
slacko.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 7 )
WHERE iss.module_id = 101
)



Any solution to this problem ?

MyODBC & Excel VBA - Too long query ?
 

I remember this 255 character trouble when I started with SQL in Excel via
MS Query.
There is no problem with SQL statements longer than 255 characters when you
run
your SQL with ADO and it may be worth it to start using this rather than MS
Query.

RBS


"Peter M." wrote in message
om...
Hello,

I've tried to record a macro in ms excel to connect to mySQL server
using myODBC driver, then execute SQL query and return the data to
excel sheet. When I run the query through MS Query, it works fine. But
when I try to use this query in macro, it ends with run-time error 13:
type mismatch. I've also tried truncating lines to 127 chars with no
positive results.

My SQL query looks like this:

(
SELECT
'4',CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as
PID, trn.created_date as date_time,slacko.value as priority,
acv.description, att.attribute_name as Operation, acv.old_value
value_from, acv.new_value value_to,
concat(user.first_name,' ' ,user.last_name ) as Person,issuet.value as
IssueType
FROM scarab_issue iss
JOIN scarab_activity acv on iss.issue_id = acv.issue_id
JOIN scarab_transaction trn ON acv.transaction_id =
trn.transaction_id
left outer join scarab_issue_attribute_value slacko on
slacko.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 7 )
LEFT OUTER JOIN scarab_attribute att on att.attribute_id =
acv.attribute_id
JOIN turbine_user user ON trn.created_by = user.user_id
left join scarab_issue_type itype on iss.type_id =
itype.issue_type_id
left outer join scarab_activity ipr on iss.issue_id = ipr.issue_id
and ipr.attribute_id = 112
left outer join scarab_issue_attribute_value issuet ON
issuet.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 112 )
WHERE iss.module_id = 101
)
union
( SELECT '5', CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count),
4)) as PID, trn.created_date as date_time, slacko.value as priority,
null, 'To workgroup', null, area.value as value_to, null,null
FROM scarab_issue iss
JOIN scarab_activity acv on iss.issue_id = acv.issue_id and
acv.activity_id = (SELECT min( activity_id ) from scarab_activity
where acv.issue_id = issue_id )
JOIN scarab_transaction trn ON acv.transaction_id =
trn.transaction_id
LEFT OUTER JOIN scarab_issue_attribute_value status on
status.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 3 )
left outer join scarab_issue_attribute_value issuet ON
issuet.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 112 )
left outer join scarab_issue_attribute_value cldate ON
cldate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 111 )
left outer join scarab_issue_attribute_value asdate ON
asdate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 108 )
left outer join scarab_issue_attribute_value cmdate ON
cmdate.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 109 )
left outer join scarab_issue_attribute_value area on area.value_id
= (SELECT max( value_id ) fROM scarab_issue_attribute_value where
issue_id = iss.issue_ID AND attribute_id = 12 )
left outer join scarab_issue_attribute_value slacko on
slacko.value_id = (SELECT max( value_id ) fROM
scarab_issue_attribute_value where issue_id = iss.issue_ID AND
attribute_id = 7 )
WHERE iss.module_id = 101
)



Any solution to this problem ?

--
Peter M.