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"
SQL / scripting help - search by Artist ID
Moderators: Gurus, Addon Administrators
Re: SQL / scripting help - search by Artist ID
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.
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.