Drive letter from Medias

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

Moderator: Gurus

trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Barton, UK
Contact:

Drive letter from Medias

Post by trixmoto » Mon Jan 25, 2010 2:36 pm

I'm trying to select the drive letter from the Medias table. I know that I can first use SQL to get the number (in my case 6) and then use the VBScript "Chr" function to get the letter...

Code: Select all

Chr(6+65) = "G"
...but is there a "Chr" function equivalent available in SQLite? I've looked but I can't find anything. :-?
Download my scripts at my own MediaMonkey fansite.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
Send me BTC: 34VQPVsf9mCeR4nfhFvvBYZqQ7LkqNZ8Mn
Send me LTC: 3P1mzrfbyscdhbxRpXLgKz7tufGAU3SrEG
Send me DOGE: 9xPpYSqgF7P5yQiqvE1VqWb4UjxVCCLFJ6
Check out these great cryptocurrency faucets... BTC / LTC / DOGE

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

Re: Drive letter from Medias

Post by Bex » Mon Jan 25, 2010 3:58 pm

Yeah, I know what you mean. Chr() is missing and you don't have any equivalent. You could perhaps use a longer Case statement which would do what you want but the problem here is really not the lack of Chr(), it is instead the fact that the driveletter in the Medias table doesn't get updated if the true driveletter is changed, so you can't use Medias driveletter since it would not work for everybody. The workaround I use is to create a tmpTable which I populate with the correct driveletter from the API instead. I use it in several scripts and I've construct it so it doesn't conflict with other scripts using the table. It works well and is very fast, feel free to copy it! :)

Code: Select all

DB.ExecSQL("CREATE TEMP TABLE If NOT EXISTS TmpMedias (tmpIDMedia INTEGER PRIMARY KEY, tmpDriveLetter TEXT COLLATE IUNICODE)")

Sub CreateTmpMedias
  Dim iter,j,dl,values
  If DB.OpenSQL("SELECT COUNT(*) FROM TmpMedias").ValueByIndex(0) = 0 Then
    Set iter = DB.QuerySongs("Songs.ID IN (SELECT MIN(ID) FROM Songs WHERE IDFolder IN "&_
                             "(SELECT MAX(ID) FROM Folders GROUP BY IdMedia) GROUP BY IdMedia)")
    Do While Not iter.EOF
      j=j+1
      If iter.item.Media.DriveLetter = -1 then dl = "" else dl = Chr(iter.item.Media.DriveLetter+65) End If
      values = values & "UNION SELECT " & iter.item.Media.ID & ",'" & dl & "' "
      iter.Next
      If j=100 Then
        DB.ExecSQL("INSERT INTO TmpMedias (tmpIDMedia, tmpDriveLetter) " & Mid(values,7))
        values=""
        j=0
      End If
    Loop
    If Not values="" Then DB.ExecSQL("INSERT INTO TmpMedias (tmpIDMedia, tmpDriveLetter) " & Mid(values,7))
    Set iter = Nothing
  End If
End Sub
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

trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Barton, UK
Contact:

Re: Drive letter from Medias

Post by trixmoto » Mon Jan 25, 2010 6:40 pm

Cool, thanks for this :)
Download my scripts at my own MediaMonkey fansite.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
Send me BTC: 34VQPVsf9mCeR4nfhFvvBYZqQ7LkqNZ8Mn
Send me LTC: 3P1mzrfbyscdhbxRpXLgKz7tufGAU3SrEG
Send me DOGE: 9xPpYSqgF7P5yQiqvE1VqWb4UjxVCCLFJ6
Check out these great cryptocurrency faucets... BTC / LTC / DOGE

Post Reply