[sub0ptimal]

I use mysql as a backend for my 4 XBMC frontends. This works great.

However, I also have MS SQL Server set up for other projects, and it would be great if there was an option for XBMC to use MSSQL for its backend, in addition to mysql. Some developers, especially those coming from a Windows background, are more comfortable maintaining a MSSQL server than a mysql server. MSSQL Express is free and easy to install.

[Sureguy]

The advantage of using MySQL is that it runs natively on Linux, OS X and Windows. The likelihood of someone investing the time to get XBMC to talk to MSSQL is pretty low as the MySQL implementation works.

As far as I know you do have the option of running MySQL on the same box as your MSSQL box.

[xbmcg]

The choice of mySQL is mainly because the availability on all plattforms, also sqlite3 (the standard DBMS for XBMC) and mySQL are pretty similar from the design / language / interface point of view for the devs.

MSSQL is a different beast, you need a ODBC driver (config) or OLEDB (.net) or native MSSQL RPC (COM/DCOM) interfaces - mostly all working on Windows only.

I agree, from rapid development point of view - if you mainly work with Visual Studio .net, and DotNet is your target (vb/c#...) it is the easiest thing to use MSSQL - however I thing XBMC is more standard C++ rather than .net - so the ODBC support would add some more libraries and make it fat.

[bleze]

Most, if not all, queries must be redone and maintained. There really isn't much to gain from supporting another RDBMS IMHO.
For XBMC, I have setup MySQL on a WHS, but currently have a Synology NAS running MySQL. Both were easy to setup.

[sub0ptimal]

I agree with all these points. I only suggest SQL Server for users who already have MSSQL and don't want to operate two servers. I am running mysql on my Synology NAS and this works fine, and of course I could run mysql on Windows if I wanted.

Just googling a bit, FreeTDS seems to be interesting. It would not be necessary to support ODBC (which is ancient, anyway). This project just implements the TDS protocol invented by Sybase and used by Microsoft.

[xbmcg]

The issue ist not just about the database drivers, but also about the SQL dialect, MSSQL Queries have a slightly different syntax / functions for string manipulations, concatenations, table joins, triggers, stored procedures, parameter handling etc. If you have to support both, you have to re-write all queries to this target, or buld neutral wrapper that creates the right output for the different targets.

And - on the other hand - MSSQL is a windows-only beast. No Linux, no Mac - the question is: does it justifies the expense to support this. Also there are more RDBMS out there - how about Oracle, DB2, Informix, MS Access (Jet engine), OpenBase, PostgreSQL...?

I think, the choice to use sqlite / mySQL is quite good, they are lightweight DB engines with a very similar SQL dialect and run even on NAS boxes, routers and other devices.

[renhack]

Just googling a bit, FreeTDS seems to be interesting. It would not be necessary to support ODBC (which is ancient, anyway). This project just implements the TDS protocol invented by Sybase and used by Microsoft.

A REST based db like couch or mongo would probably be the better alternative.

[sub0ptimal]

For anybody who has searched for this thread....
My solution was to write a script to copy the mysql database to a MSSQL database. Now I'm able to integrate with my other HTPC projects without any changes to XBMC.

[red9r]

For anybody who has searched for this thread....
My solution was to write a script to copy the mysql database to a MSSQL database. Now I'm able to integrate with my other HTPC projects without any changes to XBMC.

Would you be willing to post a write up of what you did and a copy of your script?

[d1ddyp0p]

I plan on attempting to start a SQL pull from XBMC - it won't be built into XBMC at all, but it will allow jobs on an MSSQL server to grab data from an mySQL server - a sort of replication. (this will technically be two jobs - one on the mssql server and the mysql server)

Eventually, and with some help, I hope to somehow get a ODBC connection working through an add on, allowing a second DB choice to be made.


I have been a long time user of xbmc, but developing mainly SQL TSQL...The replication is needed as I have 100+ XBMC queries and procs for my 5000 Movie/ 15000 television show library. Some of the scripting is quite pretty actually. Anyone know coding better? I've ported the library structure pretty much - much procedure.. Perhaps I'll upload a company on TPB eventually.

[Oxyg3n]

The issue ist not just about the database drivers, but also about the SQL dialect, MSSQL Queries have a slightly different syntax / functions for string manipulations, concatenations, table joins, triggers, stored procedures, parameter handling etc. If you have to support both, you have to re-write all queries to this target, or buld neutral wrapper that creates the right output for the different targets.

Yes, however, if you was using stored procedures in the first place rather then directly querying the tables, you would be calling GetVideoLibrary() rather then SELECT * FROM [MyVideos]

This would be theoretically possible in both MySQL and MSSQL, and while would require a single time change in queries, would expand for DB options everywhere, if you was to interface it, and have the implementation as a plugin, that would potentially remove all problems and put the ball back into someone else's court (aka. any plugin developer that wants to spend time on it)



EDIT: If someone (a dev probably) with a little more experience in XBMC would be so kind as to chime in I would much appreciate knowing if it would be technically possible rather then theoretically

[sub0ptimal]

Revisiting my own thread...

The issue is that XBMC does not just run queries against the database. If it did, all queries could be made into stored procedures. The SQL dialect is different but queries could be written in such a way that the core XBMC does not have to be aware of the differences. The problem is that XBMC also does things like create new databases and tables when the schema changes, which is why you get things like multiple MyVideos* databases over time.

You could argue that XBMC should not handle deployment and schema changes inline, but this should be done as part of a standalone upgrade procedure. Among other things, this decision requires the XBMC's mysql credentials to have permission to create databases.

The reason I initially wanted to make XBMC use SQL Server is because I have written some code that imports all of IMDb into a SQL Server database. I wanted to do things like show the top 30 rated movies by year and whether they were in my XBMC collection. The ultimate goal was to create a system that would enter missing movies into a Netflix DVD rental queue, but Netflix discontinued their APIs for DVDs a couple of years ago.

I've now got a system where I use the MySQL Connector for .NET to dump all the tables into CSV files, which I then import into SQL Server with BULK LOAD scripts. I run this weekly and it works well for my purpose.