SQL-Viewer 2.4 [Script] 2009-10-25

Download and get help for different MediaMonkey for Windows 4 Addons.

Moderators: Peke, Gurus

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

Post by Bex »

What I meant was that this script can't yet do Update statements but I will fix that plus other nice stuff, when the next version of MM is released.

The reason you get error is that you are using '47'. Try this:
UPDATE Songs SET IDMedia = 47

To do all this in a script which you then can run. Do something like this:

Code: Select all

Sub RunUpdateSQL
   SDB.Database.ExecSQL("Update Songs Set IdMedia = Somenr")
   SDB.Database.ExecSQL("Update Songs Set SongPath = 'SomePath'")
End Sub
And then reference RunUpdateSQL in the scripts.ini file.
See the Wiki and other scripts and I'm sure you'll get it right.
If not post again and we'll help you out
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
mp3_monkey
Posts: 68
Joined: Wed Jan 10, 2007 7:52 pm

Post by mp3_monkey »

Bex wrote:To do all this in a script which you then can run. Do something like this:

Code: Select all

Sub RunUpdateSQL
   SDB.Database.ExecSQL("Update Songs Set IdMedia = Somenr")
   SDB.Database.ExecSQL("Update Songs Set SongPath = 'SomePath'")
End Sub
And then reference RunUpdateSQL in the scripts.ini file.
See the Wiki and other scripts and I'm sure you'll get it right.
If not post again and we'll help you out
Appreciate your patience !

For the numeric update to IDMedia, followed your advice and pleased to report success - finally!!

For the text update to SongPath, almost there - just need syntax advice. I want to gloabbly change the first 6 characters of the Songpath, eg from:
\\PC01\Users\Public\Music\Library\Avril Lavigne\I'm with You.MP3 to:
\\PC05\Users\Public\Music\Library\Avril Lavigne\I'm with You.MP3

I took a shot at this, but it didnt work ( it actually wiped out the SongPath and SongTitle and left only a "C" ). I was working on a backup copy so will restore that.

Here is what I used:

Sub UpdateSongPath5
SDB.Database.ExecSQL("Update Songs Set SongPath= '\\PC05'&substr(SongPath,7,255)")
End Sub

Where did I go wrong??


EDIT ... okay. figured it out! SQLite does not ise & to concatenence ; it uses ||.

So correct code is :

Sub UpdateSongPath5
SDB.Database.ExecSQL("Update Songs Set SongPath= '\\PC05'|| substr(SongPath,7,255)")
End Sub

Starting to see the light now!
kdrmradio
Posts: 6
Joined: Sat Apr 09, 2005 4:05 pm

Post by kdrmradio »

If I'm understanding this right, with the next release of Media Monkey it will be possible to edit text fields without receiving that lame IUNICODE error with an external SQLite tool?

Anyone looking for a SQLite tool similar to Microsoft Access may want to check out SQLite Maestro. Not only is the interface of SQLite Maestro very user-friendly, the customer/technical support is the best. They actually responded to a question I had within a few hours on Christmas Day! http://www.sqlmaestro.com/products/sqlite/maestro/

I've tried a few of the free SQLite tools and wasn't very happy. A few of them actually lost data that I had imported from my Access tables.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

No, you will always get the annoying IUNICODE error when trying to update text fields in external SQLite tools. (Unless you find one SQLite tool which contains the exact same IUNICODE collation as MM has.)

But the next release of MM contains some new scripting features (not releated to any collation) that lets me modify this script so it understands SELECT * and it also lets me implement a multiline input box instead of the file workaround. At the same time I will implement the possibility to run DML statements (i.e. UPDATE, INSERT, DELETE aso) to the script.

I could implement the possibility to run DML statements right now but I will wait until the next version of MM comes so I don't have to rewrite the script twice!
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
kdrmradio
Posts: 6
Joined: Sat Apr 09, 2005 4:05 pm

Post by kdrmradio »

SQLite Maestro will take a look at the possibility of becoming compatible with IUNICODE collation but because it is a third party extension, its not a high priority. It's too bad IUNICODE collation is not natively supported by SQLite.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Major update :D
Ver 2.0 (2008-01-22) [MM3 only]

Thanks to Jiri who implemented a couple of new scripting functionalities in the latest MM3 Beta 3.0.2.1131
I'm proud to release a major update of this script.

What's new?
- 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

Here's how it looks now:

Image



It's not perfect and it's more to do (more Buttons, DropDown with Functions, Export result,etc). But I release anyway it since it's nice update.
Btw, the installer doesn't delete the old file Statement1.sql, just in case you have some statements there you want to save. So you have to delete it yourself.

Bug-reports, suggestions and comments are as usual welcome!
Check it out and let me know what you think!


Enjoy!
/Bex
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
Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

Post by Teknojnky »

Shazaam, EZ queries for newbs like me!
MarineBrat
Posts: 490
Joined: Tue Jun 14, 2005 12:12 am
Location: Loony left coast, USA.

Post by MarineBrat »

When I run the query...

Select * from songs

I get a lockup every time.

MM 3.0.2.1131
SqlViewer2.0
trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Hull, UK
Contact:

Post by trixmoto »

Looks like a fantastic update! You've been very busy!! :)
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.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Thanks!

Yes, I've been busy with this one for a couple of days! :wink:

MarineBrat,
Songs is the biggest table in MM so it would take some time to open it. Especially if you have "only display rows" set to a high value.

How big is it?
How long time does it take?
How many rows do you display?
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
sbondi

Work for 3.0.1.1127?

Post by sbondi »

Hi Bex,

The screenshots make this look like one awesome script!!! I was already considering it before you added the UI, and now I am excited to use it!

However after I ran the install (Note: everything seems to be set up properly) and run the script from the Tools menu, I get an error on line 121 that the UI object does not support the "NewMultiLineEdit" method.

I do not see this method in the MediaMonkeyScripting.chm file, so I am guessing it is something very new just added. I thought I was using the latest MM3 version (3.0.1.1127), but maybe there is a beta version with the latest and greatest required for your script.

Please let me know what I have to do to get using your excellent script.

Thanks,

Steve
RedX
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Re: Work for 3.0.1.1127?

Post by RedX »

sbondi wrote: latest MM3 version (3.0.1.1127),
latest version is beta 2 (3.0.2.1131). Try it out, the script should work with no problems

Regards,
Red
sbondi

Re: Work for 3.0.1.1127?

Post by sbondi »

RedX wrote:
sbondi wrote: latest MM3 version (3.0.1.1127),
latest version is beta 2 (3.0.2.1131). Try it out, the script should work with no problems

Regards,
Red
Thanks, Red!

Bex, very, very nice work! And only ~600 lines of code ... impressive! :)
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Thanks sbondi.

I did put this info in the line above the download link:
Note! You must have MM3 3.0.2.1131 are later to use this script!
http://home.online.no/~kar-m-kr/SqlViewer2.0.mmip.zip
Kind of hard to miss! :D
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
sbondi

Post by sbondi »

Bex wrote:Thanks sbondi.

I did put this info in the line above the download link:
Note! You must have MM3 3.0.2.1131 are later to use this script!
http://home.online.no/~kar-m-kr/SqlViewer2.0.mmip.zip
Kind of hard to miss! :D
Sure is hard to miss, but having assumed I had the latest/greatest and not even considering the possibilities on Beta version dependencies, I pretty much ignored everything around the link. My bad!!! :(
Post Reply