I'm using ElevateDB with TMS Aurelius and I adopted code first approach, so Aurelius is responsible to create database tables reading mapped entity classes. Unfortunately, it's not possible to map a collation for a column and all columns are create without any collate information. In this case ElevateDB uses the default character set code "UNI". But, I would like to create all varchar and clob columns with "UNI_CI" collation. Is it possible to set a default collation with case modifier?

I read on ElevateDB manual that there is a SET INFORMATION ... , but it doesn't work with case modifier.

Leandro

I do not use collations, so I don't know the answer to your question.

However, it would be possible to write a procedure in EDB which could iterate the resulting database and ALTER all collations on all columns of type='VARCHAR' ... it would be a little bit of work, but it would be reliable, and would scale to any database with any number of tables / columns.

Adam

Leandro wrote:

> I'm using ElevateDB with TMS Aurelius and I adopted code first
> approach, so Aurelius is responsible to create database tables
> reading mapped entity classes. Unfortunately, it's not possible to
> map a collation for a column and all columns are create without any
> collate information. In this case ElevateDB uses the default
> character set code "UNI". But, I would like to create all varchar and
> clob columns with "UNI_CI" collation. Is it possible to set a default
> collation with case modifier?
>
> I read on ElevateDB manual that there is a SET INFORMATION ... , but
> it doesn't work with case modifier.

Hi Leandro, i had same problems so i have created following functions
to convert all to UNI_CI (you can change them slightly to use it with
default tedbquery components!):

function zChangeCollation_to(vtabelle:string; vfeld:string='';
vCollation:string='UNI_CI'):boolean;
var
   vquery:tedbquery;
   vtmp:string;
   i:integer;
begin
   try
       vquery:=tedbquery.Create(self);
       vtmp:='select * from
'+mxconnection.mxDatabase.Database+'.information.tablecolumns where
type=''VARCHAR'' and collation<>'+quotedstr(vcollation);
       if trim(vtabelle)<>'' then vtmp:=vtmp+' and
tablename='+quotedstr(vtabelle);
       if vfeld<>'' then begin
           if pos('%', vfeld)=0 then begin
               vtmp:=vtmp+' and name='+quotedstr(vfeld);
           end else begin
               vtmp:=vtmp+' and name like '+quotedstr(vfeld);
           end;
       end;

       LOG('Collation', vtabelle+'/'+vfeld+' -> '+vcollation, '...');

       mxConnection.execute(vtmp, nil, vquery);
       if vquery.recordcount=0 then result:=true;

       for i:=1 to vquery.recordcount do begin
           vtmp:=
               'alter table
"'+vquery.fieldbyname('tablename').asstring+'" '+
               'alter column "'+vquery.fieldbyname('name').asstring+'"
AS '+

vquery.fieldbyname('type').asstring+'('+inttostr(vquery.fieldbyname('len
gth').asinteger)+') COLLATE "'+vcollation+'"';

           if sqlrun(vtmp) then begin
               result:=true;
           end else begin
               LOG('Collation',
vquery.fieldbyname('tablename').asstring+'/'+vquery.fieldbyname('name').
asstring+' -> '+vcollation, 'ERROR');
               result:=false;
           end;

           vquery.next;
       end;

       if result=true then
           LOG('Collation', vtabelle+'/'+vfeld+' -> '+vcollation,
'OK', true);

   finally
       if assigned(vquery) then freeandnil(vquery);
   end;
end;


PS: use it at own risk or first try it in a test-database!

--
--
Yusuf Zorlu | MicrotronX

Thank you.