Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Default Collation Case Insensitive |
Mon, Apr 27 2020 9:00 AM | Permanent Link |
Leandro | 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. |
Mon, May 25 2020 7:53 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Mon, May 25 2020 9:40 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | 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 |
Tue, May 26 2020 8:27 PM | Permanent Link |
Leandro | Thank you.
|
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |