SQL / scripting help - search by Artist ID

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

SQL / scripting help - search by Artist ID

Post by mhendu » Sun Feb 10, 2019 11:07 am

I'm trying to write a script that will either look up genre from Musicbrainz or find the most common genre from the same artist in the existing database. A few artists have special characters (') so the script errors out when searching by itm.ArtistName, otherwise this query works:

"SELECT Genre, Count(Genre) AS SortField FROM Songs WHERE Artist = '" & itm.ArtistName & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"

I'm trying to search by Artist ID instead to avoid the above error but getting nowhere with this - I just don't know SQL that well and I'm not sure of the syntax. Here's where I am right now - any thoughts on why this isn't working correctly?

"SELECT Songs.Genre, Count(Songs.Genre) AS SortField FROM Songs, ArtistsSongs WHERE Songs.ID = ArtistsSongs.IDSong AND ArtistsSongs.IDArtist = '" & itm.Artist.ID & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

Re: SQL / scripting help - search by Artist ID

Post by mhendu » Sun Feb 10, 2019 11:42 am

Would love to know how to do this for this and future scripts, but for now I came up with what I think is a workable solution:

Dim ArtistNameTemp
ArtistNameTemp = itm.ArtistName
ArtistNameTemp = Replace(ArtistNameTemp,"'","''")
SQL = "SELECT Genre, Count(Genre) AS SortField FROM Songs WHERE Artist = '" & ArtistNameTemp & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"

If I'm reading correctly this should escape out the single quotes - appears to work correctly in my script now, but there may be other special characters that I'm not accounting for.

Post Reply