This script lets you run SQL-statements directly in MM, using MM's "own" SQLite engine. It has a huge advantage compared to external SQLite programs.No more annoying: SQLite Error 1 - no such collation sequence: IUNICODE

You'll also get correct results when querying TEXT-fields in respect of Unicode characters, which isn't the case in external programs.

The script it not as fancy looking as some external SQLite viewing programs but it has some smart features which they lack.

This is how it looks like:

Bug-reports, suggestions and comments are of course welcome!Let me know what you think!

Enjoy!/Bex

For information only. You can download the installer package and read the installation instructions at the bottom of this post.---------------------------------------------------------------------------------

Call CreateDrd(Top1, 2, 2,700,20,"History",1+2+4,0,"History",History,"AddFromHist","Select a History record to add it to the SQL Window") Call CreateDrd(Top1, 2, 27,560,20,"Saves","",0,"Saves",Saves,"AddFromSave","Select a Saved record to add it to the SQL Window") Dim SavesItemIdx : SavesItemIdx=Form.Common.TopParent.Common.ChildControl("Saves").ItemIndex Call CreateBtn(Top1,564, 25, 40,24,"Save","SaveBtn","SavCurSql","","Save Current Query/Statement To Selected Name","","",Eval(SavesItemIdx>0)) Call CreateBtn(Top1,605, 25, 48,24," Save as","","Saveas","","Save Current Query/Statement to a New Name","","",True) Call CreateBtn(Top1,655, 25, 48,24,"Delete","DelBtn","DelSave","","Delete selected Save","","",Eval(SavesItemIdx>0))

Call CreateDrd(Top1, 2, 76,150,20,"Tables","",0,"Tables",Tables,"PopuFields","Select a Table and Press Add to add it to the SQL Window") Call CreateBtn(Top1,156, 74, 40,24,"&Add","","AddTable","","Adds Selected Table to the SQL Window","","",True) Call CreateDrd(Top1,200, 76,200,20,"Fields","",0,"Fields",Fields,"AddField","Select a Field to add it to the SQL Window") Call CreateCbx(Top1,404, 78,200,20,"Add as Table.Field","IfTable","If Checked:"& VbNewline &"Fields are Added with its TableName"&_ VbNewline &"If not Checked:"& VbNewline &"Only FieldName is added",INI.BoolValue("SqlViewer","AddTable"),"OnClickAddTable") Call CreateDrd(Top1,520, 76,182,20,"SongData","",0,"SongData",SongData,"AddSongData","Select a Field to add its data from the Selected song to the SQL Window") 'Call CreateDrd(Top1,520, 51,100,20,"Functions",0,"Functions",Functions,"AddFunction","Select a Function to add it to the SQL Window")

Sub OnClose(Btn) Set SDB.Objects("SQLThingy") = Nothing Set SDB.Objects("holdHTML") = Nothing DB.ExecSQL("DELETE FROM TmpSqlHist WHERE tmpIDnr NOT IN (SELECT tmpIDnr FROM TmpSqlHist ORDER BY tmpIDnr DESC LIMIT 200)")End Sub

Ver 2.0.1 (2008-01-24) [MM3 only]- Added RemVBS buttons which fixes the statement if it's copied from a VBS script- Added AddVBS which does the opposite- Remove The SQL-Statement from the result window- Fixed a bug in DropDown Fileds (not all fields where displayed)

Major update Ver 2.0 (2008-01-22)- Added a window in the window where SQL statement is entered (No more file workaround)- Added possibility to run DDL/DML statements (SDB.ExecSql) Be Careful!!!- Added new Fix so * can be used after SELECT- Added ran statements are saved (History)- Added possibility to save Runs/Templates (Saves)- Added DropDown With MM's all Tables- Added DropDown With all Fields from selected Table- Added various Buttons which adds SQL-keywords to the window- Tuned the VBScript code so the SQL's are processed much faster

---------------------------------------------------------------------------------Download:Latest version:MM3 (Installer)Note! You must have MM 3.0.2.1131 are later to use this script!SQL-Viewer 2.4

Previous version (in case a recent script update by me is malfunctioning):SQL-Viewer 2.3

Installation Instructions: - New Install or upgrade:MM3 (Installer)Avoid "Product installation error"- Vista Users:- - To be able to install scripts you must Run MM as an administrator.- - It means that you must right click the MM icon and select "Run as administrator" even if you are logged in as an administrator.- All Users:- - Check in your task manager that you only have one instance of MediaMonkey.exe running.1. Download the zipped mmip file and double click on it.2. Restart MediaMonkey (If needed)

- Upgrade:Just install the newer version over the old one. Restart MM.

Last edited by Bex on Sun Oct 25, 2009 7:09 pm, edited 15 times in total.

I found a way to always get the count of columns returned from a sql-statement. (Use Explain before the statement and look after Callback. But do it in sqlite spy.)
It seems to always work. It's a big step forward since then there won't be anymore critical error when the script sometimes misses a column.

But how to get the actual column names returned from an sql statement doesn't seems to be possible. At least I don't know how to do it...

This would be even cooler if there was a dialog box or form with something similar to the 'auto-organize' mask dialogs where you just click on common fields and statements to add them to the query. A Query Builder type thing so to speak. (tho I am sure that might add quite a bit of complexity you may not wish to code)

Yes, I agree that it would be cool but it's simply to complex for me to code. And there are already such tools available on the market. The main advantage with this script is that you can query MM's "internal sql-engine", in respect of collation and some functions, which you can't with external tools.

What about just a simple dialog/form with a input box with history (something like the search toolbar), this would avoid having to edit the text file (unless there is some reason this is more desirable?).

Theoretically you could have the edit box saved to the text file, or even use the text file to store the history of previously used queries.

Well, there are already programmes which can do all that, perhaps not in a MM specific way, but using those it's just a matter of copy/paste the code into the Statement1.sql file and run the query through this script.