Collation Swedish

Mar 30, 2010 at 3:13 PM

Hi.

I've been using phxsoftware port for SQLite for some testing. Normal sorting doesn't handle scandinavian characters as it should and in combination with case insensitivity is ehrm... very tricky. It's possible to solve the shortcommings with registering user collations and user functions. However doing this have a big impact on performance and memory consumption, making it a non optimal choice.

So I was very happy to find Effiproz and decided to test it but was very sad to see that it had the same sorting problems as sqlite, for example it should be sorted  abc...zyåäö  and in both sqlite and effiproz they get sorted aäåbcd...noöp..xyz.   Ä should be after Å and Ö should be after Z. All these should be at the end of the alphabet ...xyzåäö

Not a db pro but I normally handle this by collations, I read at http://www.effiproz.com/sql_ddl.aspx#CREATE_TABLE  that it should be possible with the <COLLATE CLAUSE> but I can't get it to work. I would be very grateful for any pointers.

Thanks in advance. BR Charlie

Coordinator
Mar 30, 2010 at 4:37 PM
Edited Mar 30, 2010 at 4:41 PM

Hi,

Still we don't support setting collation per column basis in CREATE TABLE statement ( How important is this? ).

But you can use following statement to set collation of the database (I guess this is what you are looking for).

SET DATABASE COLLATION "Swedish";

 Please note that you need to provide english name of the culture. Refer to http://www.csharp-examples.net/culture-names/

I used followng statements for testing,

 

SET DATABASE COLLATION "Swedish";
create table test(letter varchar(2));
insert into test values('ä');
insert into test values('å');
select * from test order by letter;

output is,
åä

Let me know if this works.

-Thanks.

 

 

Apr 9, 2010 at 12:41 PM

Hi igloobone!

Thank you very much for the extremely fast response, sad to say I wasn't that quick  ;-)

I missed that completely, now that I now what to look for I can find it in the documentation just right.

However...   I cannot get collations to work at all when I'm using EffiProzCF for compact framework. It doesn't complain on the statement, but it's like it wasn't executed.  The same piece of code works perfectly if I try it on EffiProzDB for .Net and Asp.net. Is the feauture implemented in the CF version?

Thanks in advance, br.

 

 

Coordinator
Apr 10, 2010 at 9:31 AM

Hi chosl,

Currently changing collation is not supported in CF version. Collation is hard set to InvariantCulture. We’ll fix it in next release or at least set it to CurrentCulture. 

-thanks, 

 

 

Apr 12, 2010 at 10:31 AM

Great news, thank! I must however ask the given ;-) When do you plan to release the next version? We're in the process of deciding the database engine and time is getting short. Any kind of estimate would be appreciated.

Btw, Couldn't find any note about CF 1.0 release in the source downloads. Does 54964 contain the CF 1.0 bits?

Kind regards, Charlie

Coordinator
Apr 13, 2010 at 8:54 AM

Still we don't have a fixed timeline but we hope to release 1.1 with some big changes by end of May.

It is not exactly the same but you may use it if you would like to do any customizations.

-thanks

Apr 17, 2010 at 2:44 PM

That's great news, impressive work so far!

I found in a simple way to change it to current culture, in CoreCF\Persist\EffiProzDatabaseProperties.cs I changed dbMeta.Add(sql_compare_in_locale, getMeta(sql_compare_in_locale, SQL_PROPERTY,  true));  Setting it to true instead of false solved all my problems and I was very happy, until...I switched from memory db to file based db.. then I got a whole lot of other problems. I'm starting a new thread for those though ;-) I was using 52621 btw as menitoned in another thread 54964 didn't compile.

Thanks!