XSQL: Run on a schedule

[macrho]

Is it possible to have the XSQL plugin run on an unattended schedule?
I'd like it to scan all my DB files nightly

[nime5ter]

Yes. There are a variety of ways to do it.

You can create a command that sets an event timer (VC.SetEventTimer) for a particular time every night--name this event whatever you like (e.g. "RunXSQL"). You can use the VC.Loaded event to trigger this command when VC launches.

Then use the RunXSQL event as the trigger to initiate your XSQL.GenerateAll command.

If you leave VC running all the time, you'll want to have your XSql command re-initialize the event timer for 24 hours from now (or 23h 59m 59s from now), so that it knows to repeat the action again tomorrow.

Basically, you'll be doing something similar to this: http://voxcommando.com/forum/index.php?topic=1187.0

But instead of getting VC to talk to you at a certain time every day, you'll be asking it to run the genXML for you.

Or you could use the Google Calendar plugin if that is more convenient for you.

[nime5ter]

For more on these actions:

http://voxcommando.com/mediawiki/index.php?title=Actions#SetEventTimer

http://voxcommando.com/mediawiki/index.php?title=Plugin_XSQL

The above action descriptions can also be found within VoxCommmando in the Action Selection Tree. http://voxcommando.com/mediawiki/index.php?title=Logical_Command_Builder

[macrho]

Is Xsql only available for the local host?
I had set up JSON to point to my remote XBMC box, however I moved to xSQL because I could not read my whole library.

error from JSON is basically a timeout while reading my songs: I have 24,928 tracks

If I look at the log file when I try to send a command to my remote XBMC box, it looks like it's trying to send it to localhost and not the remote box:

10/22/2013 12:58:34 PM   669   Action:  XBMC.Send - execbuiltin(ActivateWindow(weather))
10/22/2013 12:58:35 PM   669   sorry! error connecting to XBMC
 ip: 127.0.0.1
port: 8080
port: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:8080
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress&

[jitterjames]

Is Xsql only available for the local host?
I had set up JSON to point to my remote XBMC box, however I moved to xSQL because I could not read my whole library.

error from JSON is basically a timeout while reading my songs: I have 24,928 tracks

If I look at the log file when I try to send a command to my remote XBMC box, it looks like it's trying to send it to localhost and not the remote box:

10/22/2013 12:58:34 PM   669   Action:  XBMC.Send - execbuiltin(ActivateWindow(weather))
10/22/2013 12:58:35 PM   669   sorry! error connecting to XBMC
 ip: 127.0.0.1
port: 8080
port: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:8080
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress&

XSQL reads the SQL files directly.  The XSQL plugin settings allow you to specify the paths of the files.  So normally it is simplest to use when XBMC is running on the same machine as VC.  It is possible to access remote files if they are shared on the network, you just need to enter the correct network path for the files.  If you are not in a position to share the database files on your netowork, then you will be forced to use either the XJSON method, or XMYSQL.

The log snippet you have posted does not appear to have anything to do with XJSON, XSQL, or GenXML in general.  It is hard to tell from just this small portion of the log, but it looks like an old EDEN action for opening the Weather page.

[jitterjames]

error from JSON is basically a timeout while reading my songs: I have 24,928 tracks

If that is really what is happening, it can probably be easily fixed with an adjustment to the plugin.  Please provide the exact error message.

[macrho]

I haven't set up anything in the EDEN settings under options, they are the defaults - should I change them?

Under plugins and XBMC JSON, I set my IP, Port and Username and the test connection was successful
I saved these settings as well

Under xSQL, I'm pointing to UNC paths for the XBMCBuntu box, e.g.
\\XBMCBUNTU\System\userdata\Database\MyMusic32.db



this is what I'm seeing in my log file (is there better day I can provide)?

Code: [Select]

10/22/2013 1:34:23 PM 456 action repeat set to: 1
10/22/2013 1:34:23 PM 456 Action:  XBMC.Send - execbuiltin&parameter=ActivateWindow(musicplaylist)
10/22/2013 1:34:24 PM 467 sorry! error connecting to XBMC
 ip: 127.0.0.1
port: 8080
port: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:8080
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.GetResponse()
   at VoxCommando.XBMCcommander.internalWebRequest(String strCommand)
10/22/2013 1:34:24 PM 468 action repeat set to: 1
10/22/2013 1:34:24 PM 469 Action:  XBMC.Send - SendKey(0xF024)
10/22/2013 1:34:25 PM 473 sorry! error connecting to XBMC
 ip: 127.0.0.1
port: 8080
port: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:8080
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.GetResponse()
   at VoxCommando.XBMCcommander.internalWebRequest(String strCommand)
10/22/2013 1:34:25 PM 474 action repeat set to: 1
10/22/2013 1:34:25 PM 474 Action:  XBMC.Send - SetPlaylistSong(0)
10/22/2013 1:34:26 PM 479 sorry! error connecting to XBMC
 ip: 127.0.0.1
port: 8080
port: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:8080
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.GetResponse()
   at VoxCommando.XBMCcommander.internalWebRequest(String strCommand)
10/22/2013 1:34:26 PM 480 action repeat set to: 1
10/22/2013 1:34:26 PM 480 Action:  XBMC.Send - Action(79)
10/22/2013 1:34:27 PM 489 sorry! error connecting to XBMC
 ip: 127.0.0.1
port: 8080
port: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection


[macrho]

If that is really what is happening, it can probably be easily fixed with an adjustment to the plugin.  Please provide the exact error message.

ERROR: (xbmcSongs.xml) Failed to connect to XMBC:
System.Net.WebException: The Operation has timed out at System.New.HttpWebRequest.GetResponse() at vcPlugins.xJsonHttpCommunicator.PostRequest(String strRequest)

I typed the error as I couldn't see it in my application event viewer

The other pieces are successful [xbmcArtists.xml, etc]

[jitterjames]

this is what I'm seeing in my log file (is there better day I can provide)?

It is best if you attach the entire actual log file to your post, by clicking "Additional Options" and then attaching the file.

[Kalle]

sorry, but do you have a full VC restart after the changes done?

[jitterjames]

I am very confused, but after looking at your log file I am thinking that you did not watch the installation tutorial.

What version of XBMC are you using?

What version of VC are you using?

What configuration did you select when you first ran VoxCommando?

Which commands (if any) currently work?

Did you create or modify any of the XBMC commands in the command tree?

[macrho]

I am very confused, but after looking at your log file I am thinking that you did not watch the installation tutorial.

What version of XBMC are you using?

What version of VC are you using?

What configuration did you select when you first ran VoxCommando?

Which commands (if any) currently work?

Did you create or modify any of the XBMC commands in the command tree?

I actually wound up doing 2 installs of voxCommando. I was going to use it on an HTPC but decided against it and it's running on an older PC in a closet. I do believe I followed the directions but just in case, I'm going to do a fresh install now -- I'm not that far in

I'm running XMBC 12.2 on Ubuntu [xbmcbuntu]
I'm running VC 1.150 [latest downloaded from vox website]
English XBMC 12 Frodo.zip is the configuration

I set up XJSON and XSQL again and attempted to control the XBMC box and some commands are working now.
I attached my log file as it did show an error but not sure it matters

When I start the VoxCommando app, it takes a few minutes to become functional, I gather it's parsing through my library, which is large:

683 movies
14,788 episodes of TV
24,928 music tracks

Anyway to increase the load speed?

[Kalle]

You can take a look on this excellent video http://www.youtube.com/watch?v=O-9QNjEdNhI
this will show you the setup steps for VC and XBMC Frodo.

[macrho]

You can take a look on this excellent video http://www.youtube.com/watch?v=O-9QNjEdNhI
this will show you the setup steps for VC and XBMC Frodo.

It's running great now. I must say with a HUGE library of music, movies and TV -- this is such an easier way to navigate my library.
"Play album the wall" and blamo

[jitterjames]

So everything is working correctly now?  You've mentioned 2 installations, and you've mentioned HTPC, older PC in a closet, and XMBC on Ubuntu, and "the XBMC box".  So I don't really know which is which any more but if it is all working then I guess it does not matter.

How can you speed it up?

When you first do a genXML the rebuild will take a longer time to calculate everything.  After that reloading should be faster, but if it is still slow you can:
- install VC on a faster computer
- remove commands that you don't use, especially ones that reference large lists like the "play song X" commands
- reduce the size of your payloadXML files by removing content from your library.
- change the payloadXML types so that they don't use subset matching, but then you will need to say the exact name of the item, instead of just part of the name.

Your log file looks fine, but you are not issuing any commands or attempting to perform a genXML so I don't know if there is any problem or not.

EDIT: OK great.  I did not see your last post before posting this one!

[macrho]

I actually have 3 XBMC boxes all running on different OSs:
 -XBMCBuntu running on an XTreamer Ultra 2
 -OpenELEC running on a celeron NUC
 -Windows 8.1 running on an i3 NUC

they all access my HDHomerun Prime for live TV - so XBMC is it for consuming media!

Eventually I'd like to be able to control all 3

Right now, I have an old ASRock that is running VoxCommando and it's controlling my XBMCBuntu box, pretty much seamlessly. I'm thinking of moving that eventually to my NUC i3 when the haswell i5 NUCs come out. I figure that should hopefully have enough horsepower over my Intel core i3 M 370

I want to keep all media available

I gotta say what a great product you have here.

So everything is working correctly now?  You've mentioned 2 installations, and you've mentioned HTPC, older PC in a closet, and XMBC on Ubuntu, and "the XBMC box".  So I don't really know which is which any more but if it is all working then I guess it does not matter.

How can you speed it up?

When you first do a genXML the rebuild will take a longer time to calculate everything.  After that reloading should be faster, but if it is still slow you can:
- install VC on a faster computer
- remove commands that you don't use, especially ones that reference large lists like the "play song X" commands
- reduce the size of your payloadXML files by removing content from your library.
- change the payloadXML types so that they don't use subset matching, but then you will need to say the exact name of the item, instead of just part of the name.

Your log file looks fine, but you are not issuing any commands or attempting to perform a genXML so I don't know if there is any problem or not.

EDIT: OK great.  I did not see your last post before I posting this one!

[jitterjames]

It sounds like you may want to consider using MySQL to manage your xbmc media, if you are using the same content on all 3 machines.  Once you figure out how to do that, you can then use the XMYSQL plugin. 

The advantages are many.  First you have the advantage of centrally managed data in XBMC, so (for example) when you watch a show on one machine, it will be marked as watched on all systems.  From the point of view of VC, it is good because you will have a more powerful genXML and also, your media IDs will be the same for all machines which means you can use one payloadXML for any or all your XBMCs.

There are tutorials out there for settings up the MySQL on XBMC.  If you decide to go that way, and manage to get it all working on the XBMC end then the VC part is pretty easy to do, and we can help with that if necessary.

[macrho]

Ok, mySQL is now setup -- well, it's still going through my music library but the other libraries are ready
so how to use voxCommando with mySQL and be able to command all 3 boxes

[jitterjames]

Cool.  

First thing you will want to do is enable the MYXSQL plugin for VC, enter the info for your SQL server and try to perform a GenXML.

If that goes OK you are pretty much all set.  Then you just need to use the XJson.SetConnection action to indicate which XBMC machine you want to send commands to.

http://voxcommando.com/mediawiki/index.php?title=Plugin_XJson#SetConnection

[macrho]

When I enter the IP for mySQL, nothing generates. I verified that mySQL has data in  _music32 _video75
It's running on a Synology DiskStation. What have I done wrong?

10/24/2013 6:08:57 AM   847   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcArtists.xml
10/24/2013 6:08:57 AM   850   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcAlbums.xml
10/24/2013 6:08:57 AM   851   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcSongs.xml
10/24/2013 6:08:57 AM   853   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcGenres.xml
10/24/2013 6:08:57 AM   855   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\XbmcSongsByArtist.xml
10/24/2013 6:08:57 AM   857   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcTvShowTitles.xml
10/24/2013 6:08:57 AM   858   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcMovieIDs.xml
10/24/2013 6:08:57 AM   860   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcMovies.xml
10/24/2013 6:08:57 AM   862   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcRadioChannels.xml
10/24/2013 6:08:57 AM   864   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcTvChannels.xml
10/24/2013 6:09:30 AM   793   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcArtists.xml
10/24/2013 6:09:30 AM   795   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcAlbums.xml
10/24/2013 6:09:30 AM   796   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcSongs.xml
10/24/2013 6:09:30 AM   798   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcGenres.xml
10/24/2013 6:09:30 AM   800   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\XbmcSongsByArtist.xml
10/24/2013 6:09:30 AM   801   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcTvShowTitles.xml
10/24/2013 6:09:30 AM   803   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcMovieIDs.xml
10/24/2013 6:09:30 AM   804   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcMovies.xml
10/24/2013 6:09:30 AM   805   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcRadioChannels.xml
10/24/2013 6:09:30 AM   807   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcTvChannels.xml
10/24/2013 6:09:40 AM   31   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcArtists.xml
10/24/2013 6:09:40 AM   33   dictionary empty, not saving xml for file: C:\voxCommando\XbmcPayloads\xbmcAlbums.xml

[jitterjames]

I'll have to beef up the logging on this plugin but it looks like it is failing to connect to the MySQL server.

Make sure you have the correct IP address AND username and password that you set up when you created the server (the same ones that you use to allow XBMC to connect to the server).

Once you are 100% sure that you have all the settings correct, you might need to restart VC.  I don't think you do, but maybe...

[jitterjames]

When you click generate, is there a long delay, or a short delay, before the message pops up?

[macrho]

It's a short delay, pretty much instantaneous

[jitterjames]

Usually that means that the server is denying access for some reason (such as wrong user/pass or if your ip is not allowed etc).  Are all your XBMC machines able to connect to the MySql server OK?

I am testing now and actually I can connect fine from the localhost only.  When I try to connect to the mySQL server from another computer it fails, but there is a very long delay (like 2 minutes or so).

I think I have never tested from a remote computer before.  I will look into it.  Obviously there are not too many people using this plugin!

[jitterjames]

My problem was actually the firewall on the MySQL machine.  I solved the problem by disabling my firewall.  Since that is not a good long term solution, I re-enabled the firewall and then created an exception for port 3306.

Another possible problem can be from "too many connection errors" in mysql.  You can set a variable that allows for more errors.

I will post a version with better error reporting soon, but in the mean time you can look into those things...

see: http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html

Is MySQL running on your NAS, or is the NAS just serving files and MySQL is running on Windows?

http://windows.microsoft.com/en-ca/windows-vista/open-a-port-in-windows-firewall

[jitterjames]

Hopefully this will make it a bit easier to track down the problem.

[macrho]

Hopefully this will make it a bit easier to track down the problem.

Thank you, that immediately alerted me to the problem that the error was the name of the database, doh!
Synology was naming them xbmc_music32 and xbmc_video75 - so I updated them and was able to generate almost all of the filenames...

I received an error but am not sure if it's on voxCommando's end or my Synology. I did remove any limits on queries running from the XBMC user so perhaps it's the timeout on the voxCommando side?

10/24/2013 3:09:27 PM   834   [plugin] xMySQL error: MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at vcPlugin.DBConnect.dictSelect(String query, String strDatabase)
   at vcPlugin.Plugin.GenerateAll()

[jitterjames]

Is it just a certain query that throws that error?  If so, which one?  Song titles?

[macrho]

Is it just a certain query that throws that error?  If so, which one?  Song titles?

It appears to be this query:

select distinct idSong,strTitle,strArtists from songview where strArtists!=""

I can run it on the synology box, it has 25k records

I'm assuming that the queries are running sequentially:

xbmcArtists
xbmcAlbums
xbmcSongs
xbmcGenres
XbmcSongsByArtist

I see results after the error message for the first 4 tablles but not the 5th

[jitterjames]

OK, When I was mucking about with my connection problems I was experimenting with timeouts and I set the command timeout to 1 second which is clearly too short for large libraries.

This update sets the timeout to 10 seconds (per query).  Please let me know if this cures it for you.

[jitterjames]

It appears to be this query:

select distinct idSong,strTitle,strArtists from songview where strArtists!=""

I can run it on the synology box, it has 25k records

I'm assuming that the queries are running sequentially:

xbmcArtists
xbmcAlbums
xbmcSongs
xbmcGenres
XbmcSongsByArtist

I see results after the error message for the first 4 tablles but not the 5th

That makes sense.  I think that is probably the most demanding query.  Yes, they should run from top to bottom, one after the other.

[macrho]

that worked though I did notice that I don't have a TV database, strangely in the XBMC wiki, it doesn't mention it. Digging around I found that you can add it to your advancedsettings.xml:

    <tvdatabase>
       <type>mysql</type>
       <host>x.x.x.x</host>
       <port>3306</port>
       <user>xbmc</user>
       <pass>xbmc</pass>
       <name>xbmc_tv</name>
    </tvdatabase>

You can do the same with EPG

But I haven't the foggiest idea on how you populate the tvdatabase - any ideas?

[jitterjames]

I have no idea.  I don't have any kind of PVR hardware.  You should be able to find what you need on the XBMC wki and or forum though if you are using PVR stuff.  Those extra queries were created with the help of Mace.

If you want to get rid of them, I think you need to edit your options.xml in the plugin folder.  Does not seem to be possible to delete rows at the moment in the plugin window. 

[macrho]

Unfortunately, the XBMC wiki didn't have any information on it.
I rebooted the XBMC box a couple of times and it finally populated the tv database
Changed the database name and now I'm rolling!

now to auto update the xMySQL - very cool stuff