Problems with database collation

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

RedX
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Problems with database collation

Post by RedX » Tue Dec 25, 2007 12:49 pm

I've downloaded MM3 now that's left RC but i'm having a problem with the DB.

As it seems the devs have used a custom collation IUNICODE which unfortunately means that no other db explorer (like sqliteman or sqlite3explorer [tried both]) can sort the tables properly (i get the error: no such collation sequence: IUNICODE) and i need to have access to the db to be able to debug more complex sql queries for my scripts.

Does anyone know how to solve this?

Thx,
Red

PS: not even simple statements like "Select * from songs order by year" work

Peke
Posts: 13078
Joined: Tue Jun 10, 2003 7:21 pm
Location: Serbia
Contact:

Post by Peke » Tue Dec 25, 2007 1:42 pm

Some users reported that SQLSpy (I think) work without problems.
Best regards,
Pavle
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
How to add SCREENSHOTS to forum

RedX
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Post by RedX » Tue Dec 25, 2007 2:50 pm

SQLSpy is for Microsoft SQL Server. Not sqlite.

Thx anyway.

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Post by nynaevelan » Tue Dec 25, 2007 2:59 pm

I think Peke meant to SQLitespy.

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

CoachUK
Posts: 175
Joined: Sat May 26, 2007 1:24 pm

Post by CoachUK » Tue Dec 25, 2007 3:05 pm

I think you'll find that SQLite Expert Personal v.1.68 will do what you want.

It's available as a free download at http://www.sqliteexpert.com/index.html
I can access my database with it (MM 3.0.1.1127)
Hope this helps.

Merry Christmas and a Happy New Year.

RedX
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Post by RedX » Tue Dec 25, 2007 4:47 pm

Tried all. Same error on each one. No such collation IUNICODE
:(

ZvezdanD
Posts: 3101
Joined: Thu Jun 08, 2006 7:40 pm

Post by ZvezdanD » Tue Dec 25, 2007 5:45 pm

I tried many of them and all have same problem, SQLite Spy and SQLite Expert Personal among others. I found as a temporal solution to write COLLATE NOCASE after each textual field. Anyway, I contacted author of SQLite Spy and here is some part of his message:

"Yes, MediaMonkey registers its own IUnicode collation sequence because there is no native Unicode collation in SQLite.

SQLiteSpy has added a few Unicode enabled collation sequences based on the USER or SYSTEM sorting preferences, but it uses different names than MediaMonkey.

For a future version of SQLiteSpy I might add a "virtual" collation sequence option. It would allo users to map non-standard collation names to the build-in ones. The sorting would of course be slightly different from original applications, but queries would run sucessfully - even without using the COLLATE escape as you did it."

RedX
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Post by RedX » Tue Dec 25, 2007 7:32 pm

Using collate nocase i could try out the statements i needed :-) thx for the tip

What collation comes the closest to IUNICODE?

Thx

ZvezdanD
Posts: 3101
Joined: Thu Jun 08, 2006 7:40 pm

Post by ZvezdanD » Wed Dec 26, 2007 2:27 am

SQLite only has BINARY and NOCASE built-in collation sequences:
http://www.sqlite.org/datatype3.html#collation

Post Reply