Accessing DB file and "collation" errors

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

dtsig
Posts: 2833
Joined: Mon Jan 24, 2011 6:34 pm

Accessing DB file and "collation" errors

Post by dtsig » Mon Apr 15, 2013 12:53 pm

Trying to access the Sqlite DB with tools external to MM and having problems getting "no such collation sequence: IUNICODE (1)". After searching i find this:

From nohitter 151 on this thread dated May 2010 comes
From what I understand there aren't a lot of programs that understand MM's "IUNICODE" structure (required for unicode character support) so getting programs other than MM to read/write the database can be quite difficult.
Am I reading this right ... MM developers came up with their own way to handle unicode and that is why external tools can't access the DB file correctly? Really? Doesn't Sqlite handle unicode natively?
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig

nohitter151
Posts: 23640
Joined: Wed Aug 09, 2006 10:20 am
Location: NJ, USA
Contact:

Re: Accessing DB file and "collation" errors

Post by nohitter151 » Mon Apr 15, 2013 5:21 pm

I'm not sure exactly what you are trying to do but this addon can probably do whatever it is you want do do:
http://www.mediamonkey.com/forum/viewto ... =2&t=24841
MediaMonkey user since 2006
Need help? Got a suggestion? Can't find something?

Please no PMs in reply to a post. Just reply in the thread.

dtsig
Posts: 2833
Joined: Mon Jan 24, 2011 6:34 pm

Re: Accessing DB file and "collation" errors

Post by dtsig » Mon Apr 15, 2013 6:31 pm

Thanks for that nohitter151, that was really cool.

I am looks for ways to work on the db from tools that I am familiar with without having to learn something else ... limited time and all that :) But simple select ... where causes problems as noted. It *seems* that the db file created by MM isn't really accessible outside of MM
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig

Scottes
Posts: 139
Joined: Sat Mar 21, 2009 6:51 am

Re: Accessing DB file and "collation" errors

Post by Scottes » Wed Apr 17, 2013 5:27 pm

dtsig wrote:It *seems* that the db file created by MM isn't really accessible outside of MM
??? I've written several Python scripts that work the MM DB quite well, and I'm working on a C# program that has not (yet?) had an issue. SQLite Studio has no problems, and recognizes that it's IUnicode. I think it's just a matter of getting the right program, or sometimes - when possible - configuring the program correctly. Granted, I'm sure that many programs choke on the MM DB, especially older ones.
MonkeyMatch - Find and fix similar/incorrect spellings
MonkeyMatch Thread

dtsig
Posts: 2833
Joined: Mon Jan 24, 2011 6:34 pm

Re: Accessing DB file and "collation" errors

Post by dtsig » Thu Apr 18, 2013 2:28 pm

Scottes wrote:
dtsig wrote:It *seems* that the db file created by MM isn't really accessible outside of MM
??? I've written several Python scripts that work the MM DB quite well, and I'm working on a C# program that has not (yet?) had an issue. SQLite Studio has no problems, and recognizes that it's IUnicode. I think it's just a matter of getting the right program, or sometimes - when possible - configuring the program correctly. Granted, I'm sure that many programs choke on the MM DB, especially older ones.
That is great news Scottes. When I had problems with the tool I use normally I then downloaded the WinSql (and just tried razorsql) which was one of the suggested ones I found. If i do a simple "select * from artists" and press execute all is well. But when I ' select * from artists where artist = "Ten Years After" ' I get the "Error: no such collation sequence: IUNICODE (1) (State:S1000, Native Code: 1)". To be sure that it wasn't just a statement on "Ten Years After" i tried other artists to the same result :)

Thoughts
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig

Scottes
Posts: 139
Joined: Sat Mar 21, 2009 6:51 am

Re: Accessing DB file and "collation" errors

Post by Scottes » Fri Apr 19, 2013 3:04 pm

I was running numerous, somewhat complicated SQL queries last night using SQLite Studio and had no problem.

select * from songs where length(artist) < 5;
select * from songs where artist='rush' and album like '%2112%' and dateadded > 41300;
select * from songs order by dateadded desc;

Your query ran fine. Apparently I have two tracks by Ten Years After. OK, they're both "I'd love to change the world" on two different compilation albums.

Give SQLite Studio a shot. It does seem quite powerful - and thus dangerous - so use with caution.
MonkeyMatch - Find and fix similar/incorrect spellings
MonkeyMatch Thread

dtsig
Posts: 2833
Joined: Mon Jan 24, 2011 6:34 pm

Re: Accessing DB file and "collation" errors

Post by dtsig » Sun Apr 21, 2013 11:40 am

Thanks .. will check that out right now. Do you know if it uses the ODBC driver available or is it a COM connection?

Thanks again
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig

dtsig
Posts: 2833
Joined: Mon Jan 24, 2011 6:34 pm

Re: Accessing DB file and "collation" errors

Post by dtsig » Sun Apr 21, 2013 11:47 am

Well after downloading and trying it does work well. It must work with com connection as there is no selection of ODBC definition but directly to the file. So will look into connecting through COM.

Thanks again for the quick great response.

DSig
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig

Post Reply