SQL-Scripting

hurrlipurr · Post by **hurrlipurr** » Tue Jul 06, 2004 2:58 pm

I would like to fill the Custom fields automatically from other fields in the database. I know SQL very well, but I don't get the starting point how to do it within Mediamonkey with a script. Where would I be supposed to look for documentation about that topic?

Post by **jiri** » Tue Jul 06, 2004 3:22 pm

You can try the reference downloadable from this forum and Export.vbs in Scripts folder of MM installation. You can also download example scripts from MM web. And ask here if you miss anything.

We plan to make a special web for MM developers, but I cannot say how long will it take.

Jiri

hurrlipurr · Post by **hurrlipurr** » Wed Jul 07, 2004 12:34 pm

Thanks, jiri, I missed out the sample scripts section. It's a good starting point. Another question: is there a possibility to run scripts automatically (e.g. at startup of MM)?

Post by **Lowlander** » Wed Jul 07, 2004 12:50 pm

There is no setting in MM that scripts can run at startup. I believe it has been requested.

hurrlipurr · Post by **hurrlipurr** » Wed Jul 07, 2004 2:21 pm

Do I need MS Access to test sample queries on mediamonkey.mdb or is there any (free) query tool that can do this?

Post by **Lowlander** » Wed Jul 07, 2004 3:10 pm

You can try queries in whatever programming language you are using. Just check the results if they are what was expected.

If you are going to work with queries that modify things in the DB you should backup the DB in case something goes wrong.

The easiest way of creating queries for me is using Access query wizard and than copy the SQL code afterwards (don't use the query in the DB in code as that is not part of other users DBs). Just make sure you define the relationships between tables when writing queries.

And generally programming software give errors when mistakes are made in the query.

Post by **jiri** » Wed Jul 07, 2004 3:26 pm

As for running script on some events (e.g. MM start up), the next release will have a number of new scripting related features and this is one of them. You'll also be able to add new tree nodes, etc. All developers, get ready!

Jiri

hurrlipurr · Post by **hurrlipurr** » Thu Jul 08, 2004 11:59 pm

Ok, I managed to write a really easy script that simply runs an Update-SQL on the database. Thanks a lot!

But still I'm not happy:

My plan was to make (numerical) categories (0..3 meaning NEVER..OFTEN) from the PLAYED#-values into the CUSTOM1 field. This works fine with the following SQL:

Code: Select all

Sub SetCUSTOM1

   'Update CUSTOM1
   SDB.Database.ExecSQL("UPDATE Songs SET Custom1 =  IIf([PlayCounter] =0 , 0 , IIf([PlayCounter] <3 , 1 , IIf([PlayCounter]<10 , 2 , 3 ) ) );")

End Sub

I then wanted to put a sorting on that CUSTOM1 field into my auto-playlists, but unfortunately it is not possible to sort on the custom-fields.

Any ideas?

hurrlipurr · Post by **hurrlipurr** » Fri Jul 09, 2004 12:09 am

Another question: is it possible to put the query into a separate file? Something like:

Code: Select all

SDB.Database.ExecSQL(query.sql)

Post by **jiri** » Fri Jul 09, 2004 1:01 am

- Sorting by Custom fields will be possible in the next release.

- You can have the query in a separate file, but you would have to open it somehow (FileSystem object could do it - see VBScript reference at http://www.microsoft.com/scripting and Export.vbs for examples).

Jiri

ninerays · Post by **ninerays** » Fri Jul 09, 2004 1:25 am

hurrlipurr wrote:Ok, I managed to write a really easy script that simply runs an Update-SQL on the database. Thanks a lot!

But still I'm not happy: My plan was to make (numerical) categories (0..3 meaning NEVER..OFTEN) from the PLAYED#-values into the CUSTOM1 field. This works fine with the following SQL:
Code: Select all
Sub SetCUSTOM1

   'Update CUSTOM1
   SDB.Database.ExecSQL("UPDATE Songs SET Custom1 =  IIf([PlayCounter] =0 , 0 , IIf([PlayCounter] <3 , 1 , IIf([PlayCounter]<10 , 2 , 3 ) ) );")

End Sub
I then wanted to put a sorting on that CUSTOM1 field into my auto-playlists, but unfortunately it is not possible to sort on the custom-fields.

Any ideas?

One way would be to run your query, make sure your custom field is visible & click on it's header to sort by that field. It will give you some functionality until the next version.

hurrlipurr · Post by **hurrlipurr** » Sun Jul 11, 2004 7:20 am

Hi jiri,

jiri wrote:As for running script on some events (e.g. MM start up), the next release will have a number of new scripting related features ...
Jiri

will there also be an event before/after a song is played?

Post by **jiri** » Sun Jul 11, 2004 8:54 am

Yes, most likely these and other events will be added. Btw, check 'scripting' topic in the MM.chm help file, it's possible even now to run a script on start of playback.

Jiri

hurrlipurr · Post by **hurrlipurr** » Mon Feb 21, 2005 8:20 am

jiri wrote:As for running script on some events (e.g. MM start up), the next release will have a number of new scripting related features and this is one of them. You'll also be able to add new tree nodes, etc. All developers, get ready!

I read something about beta-support for running scripts based on events in 2.2.2. Is there a possibility to run scripts on MM start up right now?

Greetings
hurrlipurr

hurrlipurr · Post by **hurrlipurr** » Mon Feb 21, 2005 8:53 am

jiri wrote:Btw, check 'scripting' topic in the MM.chm help file, it's possible even now to run a script on start of playback.

I tried this like

Code: Select all

[SetCustom1]
FileName=SetCUSTOM1.vbs
ProcName=SetCUSTOM1
Order=5
DisplayName=Set CUSTOM1
Description=Group Played# into CUSTOM1
Language=VBScript
ScriptType=2

and it doesn't work - it works fine with ScriptType=0 by manually selecting it from the menu. Any idea?

SQL-Scripting

SQL-Scripting

Autostart scripts on events