SQL Question: SDB.Database.OpenSQL with members

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

MoDementia
Posts: 1321
Joined: Thu Jun 15, 2006 3:26 pm
Location: Geelong, Victoria, Australia

SQL Question: SDB.Database.OpenSQL with members

Post by MoDementia » Mon Apr 07, 2008 10:39 pm

My SQL knowledge is almost non existant so I need a little help
I know how to get this to work using SDB.Database.QuerySongs

Code: Select all

Sub Sample

  Dim SampleID, ObjectNewAlbum
  Dim objSongList

  SampleID = 13123
  ObjectNewAlbum = "Rolling Stones"
  Set objSongList = SDB.NewSongList
  
  Set objSongListiterator = SDB.Database.QuerySongs("AND Songs.ID=" & SampleID)
  
    While Not objSongListiterator.EOF
      Set objSongData = objSongListiterator.Item
      objSongList.Add(objSongData)
      ObjectAlbum = objSongData.Album.Artist.Name
    objSongListiterator.Next
    Wend
  Set objSongListiterator=Nothing

  SDB.MessageBox "Value = '" & ObjectAlbum & "'", mtError, Array(mbOK)

'  objSongData.Album.Artist = ObjectNewAlbum 'Commented Out For Safety
'  objSonglist.UpdateAll              'Commented Out For Safety 
End Sub
But I wonder if the same result is possible with SDB.Database.OpenSQL?
objSongListiterator.StringByIndex(0) Returns the Album Name but objSongListiterator.StringByIndex(1) or objSongListiterator.StringByIndex(0).Artist.Name creates an error
And then the update code needs to be???

Code: Select all

Sub Sample

  Dim SampleID, ObjectNewAlbum
  Dim objSongList

  SampleID = 13123
  ObjectNewAlbum = "Rolling Stones"
  Set objSongList = SDB.NewSongList
  
      SQLString = "SELECT Album FROM Songs WHERE Songs.ID = " & SampleID
      Set objSongListiterator = SDB.Database.OpenSQL(SQLString)
        
    While Not objSongListiterator.EOF
    ObjectAlbum = objSongListiterator.StringByIndex(0)
    objSongListiterator.Next
    Wend
  Set objSongListiterator=Nothing

  SDB.MessageBox "Value = '" & ObjectAlbum & "'", mtError, Array(mbOK)

'  objSongData.Album.Artist = ObjectNewAlbum 'Commented Out For Safety
'  objSonglist.UpdateAll              'Commented Out For Safety 
End Sub

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex » Tue Apr 08, 2008 1:30 am

SDB.Database.QuerySongs returns SDBSongIterator Object from which you easy can get SongData Objects.

SDB.Database.OpenSQL returns SDBDBIterator Object from which you get text or number values.



objSongListiterator.StringByIndex(1)
Returns error because you only have one field in your Select (Album)

objSongListiterator.StringByIndex(0).Artist.Name
Returns error because .Artist.Name needs a songdata object to work. objSongListiterator.StringByIndex(0) is just text.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

MoDementia
Posts: 1321
Joined: Thu Jun 15, 2006 3:26 pm
Location: Geelong, Victoria, Australia

Post by MoDementia » Tue Apr 08, 2008 1:57 am

Thanks Bex,
I knew it would be an easy one for you :)

I have never understood things like album and artist in the SongData
The documentation doesn't say where it comes from :(
If you look at the DB directly only the Album Title is there :(

I presume MM itself runs an SQL statement(s) to create the extra SongData elements

So using SDB.Database.OpenSQL you would have to get the IDAlbum first then SDB.Database.OpenSQL the albums table to get the album artist yes?

Or can you do it in 1 line?

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex » Tue Apr 08, 2008 2:17 am

Yes, if you only want the AlbumArtist as text from a then this would do it:

Code: Select all

SDB.Database.OpenSQL("SELECT AlbumArtist FROM Songs WHERE Songs.ID = " & SampleID)
But if you have multiple assignments of Album Artists and you want them separate, then you need to do something like this:

Code: Select all

SDB.Database.OpenSQL("SELECT Artists.Artist FROM Songs, ArtistAlbums, Artists WHERE Songs.IDAlbum=ArtistAlbums.IDAlbum AND ArtistAlbums.IDArtist=Artists.Artist AND Songs.ID = " & SampleID)
The first query returns only one row while the second returns so many rows as it exist different Album Artists.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

Post Reply