MediaMonkey forum

I haven't got time to look into this yet. But try to reload all scripts instead of just SQL-Viewer, it works for me.

Bex wrote:I haven't got time to look into this yet. But try to reload all scripts instead of just SQL-Viewer, it works for me.

Will try.
No big deal. Just posted the info from my testing so you would have it when and if.

Script is updated:
Ver 2.3 (2009-10-25)
- Added a Clear All Button
- Added a Save As Button and fixed the Save button so it works properly
- Fixed a random "This key is already associated bla, bla" bug


Let me know what you think!

Enjoy!
/Bex

I get a "file not found" error when clicking on the link.

Never mind it was a browser issue.

Something I've wondered at times about this script, is if a "Save/Save As" could be offered for the Select Statement outputs. Unless I'm missing something.

I'll put it on my todo-list! But it will take some time I think...

Bex wrote:Script is updated:
Ver 2.3 (2009-10-25)
- Added a Clear All Button
- Added a Save As Button and fixed the Save button so it works properly
- Fixed a random "This key is already associated bla, bla" bug


Let me know what you think!

Enjoy!
/Bex

Looks good.
One minor thing (was in prior release as well). Save As dialog box needs to be a bit bigger vertically.

Thanks, I'll get it sorted in the next version!

Hello Bex (hope I got that right)

Great solution you created here, I too got deeply ****ed off at not being able to use SQLLite to edit any database entry affected by this really dumb MM database collation sequence. I tried your solution and it worked as advertised first time.

My reason for using your utility was to update some of the sort orders that get applied in the "Lists" table behind MM. Except for very occasional reasons, why anybody would find an unsorted list useful is beyond most users (by the way, I worked in the software industry for over 10 years WITH end-users). MM is a great program, but the developers could help by making a few things a little less funky.

So my tip is that if the user wants to execute multiple statements in one foul-swoop, they should just terminate each statement with a semi-colon (;)

update Lists set SortOrder = 0 where IDListType = 2 and ID = 28;
update Lists set SortOrder = 1 where IDListType = 2 and ID = 11;
update Lists set SortOrder = 2 where IDListType = 2 and ID = 25;
etc.

I also have a separate suggestion. I'm not a SQLLite expert, but it's possible that the extra lines added below and shown in blue could make executing DML a bit safer - i.e. using transactions.

Sub ExecSql(Pnl)
.....
DB.BeginTransaction
DB.ExecSQL(sql)
DB.Commit
.....
End Sub

I guess it depends how MM is wrapping all this up. If MM weren't in front of the SQLLite database, as it has to be because of the custom collation sequence, then I would definitely use the "BeginTransaction"...."Commit" wrapped around my SQL, just in case a user does something really stupid. Even an experienced user can make a type or not pay attention to what their doing.

Anyway, thanks once again for your effort and sharing. Live long and prosper.

TTFN, JJ.

Thanks!

I have a new fancy version of this script laying around which actually can do multiple statements in one go. We'll see when I get the time to sort out some outstanding issues left to fix before I can release it.

To sort the Classifications lists in the gui, you can simply do so by dragging the nodes on top of each other.

BeginTransaction and Commit isn't really necessary as far as safety is concerned since I'm using MM's API to perform the queries which take care of that, but it has another advantage and that is increased speed, when performing multiple statements in one go. I guess I could add that but I'm not sure how important it is?

Hello Bex,

I already thank you for this script which is rather useful. I am a user of MM and I recently wanted to make modifications on the DataBase. While wanting to make simple query of update on the DataBase

UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “The Al”;

I have the following error: No such collation sequence: IUNICODE

I thus make use of SQL - Viewer 2.4 to carry out my queries. You said that with this version it will be possible to carry out multiples statement

UPDATE Songs SET Kind = “Pop” WHERE Artist = “Shakira Feat. Wyclef Jean”;
UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “Snow Patrol”;
UPDATE Songs SET Kind = “Pop” WHERE Artist = “Taylor Hicks”;
UPDATE Songs SET Kind = “Pop” WHERE Artist = “Teddy Geiger”;
UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “The Al”;
UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “The Fray”;

I tried to do it but it doesn't work. How to make it work ?

In the case that it doesn't work can you PLEASE propose me a solution to me to do it because I have 2100 queries to execute

An ultimate solution could be that I send you my database and my queries, you execute them and return me the DataBase.

Regards,

Bourbe

PS: can you look at this:

http://www.mediamonkey.com/forum/viewto ... 19&t=53222

There's no "Kind" column in the Songs table. Based on the information you're trying to insert, it looks as though you're looking for the "Genre" column. However, couldn't you just tag the songs within MM with the relevant genre? I forsee some potential breakage if you just update the Songs table with SQLViewer, as there is also a Genre and GenreSongs table.

Hi,

I made a mistake, the queries are:

UPDATE Songs SET Genre = “Pop” WHERE Artist = “Shakira Feat. Wyclef Jean”;
UPDATE Songs SET Genre = “Rock'n'roll” WHERE Artist = “Snow Patrol”;
UPDATE Songs SET Genre = “Pop” WHERE Artist = “Taylor Hicks”;
UPDATE Songs SET Genre = “Pop” WHERE Artist = “Teddy Geiger”;
UPDATE Songs SET Genre = “Rock'n'roll” WHERE Artist = “The Al”;
UPDATE Songs SET Genre = “Rock'n'roll” WHERE Artist = “The Fray”;

couldn't you just tag the songs within MM with the relevant genre?

like i said I have 2100 queries to do and it is very tiresome

I forsee some potential breakage if you just update the Songs table with SQLViewer, as there is also a Genre and GenreSongs table

These table are automatically update when the table Songs is updated

Bourbe.

Guest wrote:I forsee some potential breakage if you just update the Songs table with SQLViewer, as there is also a Genre and GenreSongs table

These table are automatically update when the table Songs is updated

No, they are not. Running a test update of "UPDATE Songs SET Genre = "TestGenre" WHERE ID=1" results in the Genre being changed for the first song in the database in the Songs table, however the Genre and GenreSongs tables are not updated. As suspected, this results in breakage of features as the "updated" Genre will appear in the Genre column within MM as well as in the Genre field on the songs properties, but the track will not show up in the Genre node under the Genre you have set (if it's a new Genre, that genre won't even be displayed, as it's not in the Genres table).

Sorry but multiple execution is not implemented yet. But it's better to do these kinds of changes in the gui since it updates the correct tables and writes the data to the tag of the songs.