0
Under review

Convert 2 UTF8

Илья Агафонов 7 years ago 0

Вчера от IBExpert Team пришло письмо с вложенным SQL скриптом конвертирования баз в различных кодировках в том числе NONE в кодировку UTF8. Вот я и решил поделится может быть пригодится и в библиотекаре...



execute ibeblock
as
begin
--Change these variables
source='LOCALHOST:C:\db\db1.fdb';
sourcecharset='ISO8859_1';
sourcecollate='DE_DE';
target='LOCALHOST:C:\db\db1_utf8.fdb';
scriptdir='c:\db\dump\';
bakfile='C:\db\test.fbk';
--End of variables

cbb = 'execute ibeblock (
LogLine variant)
as
begin
ibec_progress(LogLine);
end';

ibec_BackupDatabase(source,bakfile,'ClientLib=fbclient.dll;Password=masterkey; User=SYSDBA; G;',cbb);

--optionally you can also restore the source database; here it has been commented out.
--ibec_RestoreDatabase(bakfile, source,'ClientLib=fbclient.dll;Password=masterkey; User=SYSDBA; C; REP; O;',cbb);

--Create a connection to the source database and open it
sourceDB = ibec_CreateConnection(__ctFirebird,'DBName="'+source+'";ClientLib=fbclient.dll;User=SYSDBA; Password=masterke; Names='+sourcecharset+'; SqlDialect=3');
use sourceDB;

--Create a comma-separated list of table names, to transfer the data
tnlist='';
komma='';
for
select trim(rdb$relations.rdb$relation_name) from rdb$relations
where rdb$relations.rdb$relation_name not containing '$'
and rdb$relations.rdb$view_blr is null
into
:tn
do
begin
tnlist=tnlist||komma||tn;
komma=',';
end

--Call the Extract Metadata script function, to create a database dump including metadata, data and blobs.
ibec_ExtractMetadata(sourceDB, scriptdir,'GenerateConnect;IncludePassword;SetGenerators;ExtractDescriptions;
SeparateComputedBy;CommitAfter=50000;MaxFileSize=100;UseCreateOrAlter;ExtractBLOBs;
ExtractPrivileges;OnlySelectedPrivileges;UseReinsert;TrimStrings;SeparateFiles;
DateFormat=YYYY-MM-DD;DataTables='||tnlist,cbb);

--If the target database already exists, then drop it, if errors occur, then ignore.
try
res = ibec_dropdatabase(__ctFirebird,
'DBName="'+target+'";
ClientLib=fbclient.dll;
User=SYSDBA; Password=masterke;');
except
end;
if (res is not null)
then ibec_Progress('Datenbank erfolgreich gelцscht');

--Create a new database with the character set UTF8
TargetDb = ibec_createdatabase(__ctFirebird,'DBName="'+target+'";ClientLib=fbclient.dll;PageSize=16384;User=SYSDBA; Password=masterke; DefaultCharset=UTF8; SqlDialect=3;');
ibec_Progress('UTF8 Datenbank erfolgreich erzeugt');

--Reconnect to the source database and make some changes to the header script:
use sourcedb;
txt=ibec_LoadFromFile(scriptdir+'_ibe$start_.sql');

--All fields and variables longer that 8190 bytes need to be cropped, because UTF8 is a multibyte character set.
for select distinct rdb$fields.rdb$field_length from rdb$fields where rdb$fields.rdb$field_length>8190 into :lx
do txt=ibec_StringReplace(txt,'('||lx||')','(8190)', __rfReplaceAll + __rfIgnoreCase);

txt=ibec_StringReplace(txt,'SET NAMES NONE;','SET NAMES '+sourcecharset+';', __rfReplaceAll + __rfIgnoreCase);
txt=ibec_StringReplace(txt,'COLLATE '+sourcecollate,'COLLATE UTF8', __rfReplaceAll + __rfIgnoreCase);
txt=ibec_StringReplace(txt,source,target, __rfReplaceAll + __rfIgnoreCase);
ibec_SaveToFile(scriptdir+'_ibe$start_.sql',txt,__stfOverwrite);

--do the same in the footer script
txt=ibec_LoadFromFile(scriptdir+'_ibe$finish_.sql');
for select distinct rdb$fields.rdb$field_length from rdb$fields where rdb$fields.rdb$field_length>8190 into :lx
do txt=ibec_StringReplace(txt,'('||lx||')','(8190)', __rfReplaceAll + __rfIgnoreCase);
ibec_SaveToFile(scriptdir+'_ibe$finish_.sql',txt,__stfOverwrite);


txt=ibec_LoadFromFile(scriptdir+'runme.all.sql');
txt=ibec_StringReplace(txt,'INPUT ''','INPUT '''+scriptdir, __rfReplaceAll + __rfIgnoreCase);
ibec_SaveToFile(scriptdir+'runme.all.sql',txt,__stfOverwrite);

res = ibec_Exec('ibescript.exe '+scriptdir+'runme.all.sql -N -VD:\db\db1\runme.all.log ','',cbb);
end;

Answer

Answer
Under review
Cпасибо, может и пригодиться.
Answer
Under review
Cпасибо, может и пригодиться.