SQL / scripting help - search by Artist ID

Post a reply


In an effort to prevent automatic submissions, we require that you complete the following challenge.
Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:

BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: SQL / scripting help - search by Artist ID

Re: SQL / scripting help - search by Artist ID

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.

SQL / scripting help - search by Artist ID

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"

Top