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

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Postby Bex » Thu Jan 17, 2008 5:01 am

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby mp3_monkey » Thu Jan 17, 2008 6:22 am

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!
mp3_monkey
 
Posts: 68
Joined: Wed Jan 10, 2007 7:52 pm

Postby kdrmradio » Thu Jan 17, 2008 11:10 am

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.
kdrmradio
 
Posts: 6
Joined: Sat Apr 09, 2005 4:05 pm

Postby Bex » Thu Jan 17, 2008 2:55 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby kdrmradio » Fri Jan 18, 2008 11:13 am

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.
kdrmradio
 
Posts: 6
Joined: Sat Apr 09, 2005 4:05 pm

Postby Bex » Mon Jan 21, 2008 9:57 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby Teknojnky » Mon Jan 21, 2008 10:04 pm

Shazaam, EZ queries for newbs like me!
Teknojnky
 
Posts: 5509
Joined: Tue Sep 06, 2005 11:01 pm

Postby MarineBrat » Tue Jan 22, 2008 12:55 am

When I run the query...

Select * from songs

I get a lockup every time.

MM 3.0.2.1131
SqlViewer2.0
MarineBrat
 
Posts: 485
Joined: Tue Jun 14, 2005 12:12 am
Location: Loony left coast, USA.

Postby trixmoto » Tue Jan 22, 2008 4:14 am

Looks like a fantastic update! You've been very busy!! :)
Download my scripts at trixmoto.net. If you're interested, check out my Uniface blog.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.

Image Image
trixmoto
 
Posts: 9907
Joined: Fri Aug 26, 2005 3:28 am
Location: Hull, UK

Postby Bex » Tue Jan 22, 2008 3:21 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Work for 3.0.1.1127?

Postby sbondi » Thu Jan 24, 2008 1:27 am

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
sbondi
 

Re: Work for 3.0.1.1127?

Postby RedX » Thu Jan 24, 2008 2:15 am

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
RedX
 
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Re: Work for 3.0.1.1127?

Postby sbondi » Thu Jan 24, 2008 2:56 am

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! :)
sbondi
 

Postby Bex » Thu Jan 24, 2008 12:00 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby sbondi » Thu Jan 24, 2008 12:51 pm

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!!! :(
sbondi
 

PreviousNext

Return to Need Help with Addons?

Who is online

Users browsing this forum: crap_inhuman and 10 guests