Творец
Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант
Репутация: 20 Всего: 329
|
Импорт в базу Firebird из CSV средствами IBEBlock`ов IBExpert`а Код | execute ibeblock returns(outstr varchar(100)) as begin -- First, let's create a simple CSV-file with some data FS = ibec_fs_OpenFile('C:\MyData.csv', __fmCreate); if (not FS is null) then begin s = '1:John:Doe:M'; ibec_fs_Writeln(FS, s); s = '2:Bill:Gates:M'; ibec_fs_Writeln(FS, s); s = '3:Sharon:Stone:F'; ibec_fs_Writeln(FS, s); s = '4:Stephen:King:M'; ibec_fs_Writeln(FS, s); ibec_fs_CloseFile(FS); end
-- If table IBE$$TEST_PEOPLE exists we'll drop it if (exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_PEOPLE')) then begin s = 'DROP TABLE IBE$$TEST_PEOPLE'; execute statement s; commit; end
-- Let's create a new table that will store the imported data s = 'CREATE TABLE IBE$$TEST_PEOPLE ( ID integer, FIRST_NAME varchar(50), LAST_NAME varchar(50), SEX varchar(1))'; execute statement s; commit;
i = 0; -- Just a counter of inserted records FS = ibec_fs_OpenFile('C:\MyData.csv', __fmOpenRead); if (not FS is null) then begin while (not ibec_fs_Eof(FS)) do begin s = ibec_fs_Readln(FS); ValCount = ibec_ParseCSVLine(Vals, s, '', ':', __csvEmptyStringAsNull); INSERT INTO IBE$$TEST_PEOPLE (ID, FIRST_NAME, LAST_NAME, SEX) VALUES :Vals; commit; i = i + 1; end ibec_fs_CloseFile(FS); end
outstr = i || ' records inserted into IBE$$TEST_PEOPLE'; suspend; end
|
Добавлено @ 22:37Импорт сразу нескольких файлов, указав путь, тип (расширение) средствами IBEBlock`ов IBExpert`а Код | set names win1251; set sql dialect 3; set clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';
create database 'localhost/3060:D:\allscripts.fdb' user 'SYSDBA' password 'masterkey' page_size 8192 default character set WIN1251;
create generator gen_script_id;
create table scripts ( ID INTEGER NOT NULL PRIMARY KEY, FILENAME VARCHAR(2000), SCRIPT_TEXT BLOB sub_type text);
create trigger script_bi for scripts active before insert position 0 as begin if (new.id is null) then new.id = gen_id(gen_script_id, 1); end;
execute ibeblock as begin ibec_progress('Searching for script files...'); files_count = ibec_getfiles(files_list, 'D:\', '*.sql', __gfRecursiveSearch + __gfFullName);
if (files_count > 0) then begin i = 0; while (i < ibec_high(files_list)) do begin file_name = files_list[i]; if (ibec_filesize(file_name) < 10240000) then begin script_data = ibec_loadfromfile(file_name); ibec_progress('Adding script file ' || :file_name); insert into scripts (filename, script_text) values (:file_name, :script_data); commit; end i = i + 1; end end end;
|
Добавлено @ 22:41Пересоздание индексов средствами IBEBlock`ов IBExpert`а Код | execute ibeblock returns (info varchar(1000)) as begin i = 0;
for select i.rdb$index_name, i.rdb$relation_name, i.rdb$unique_flag, i.rdb$index_inactive, i.rdb$index_type from rdb$indices i left join rdb$relation_constraints rc on (i.rdb$index_name = rc.rdb$index_name) where (i.rdb$system_flag is null) and (rc.rdb$index_name is null) into :IdxName, :IdxRelName, :IdxUnique, :IdxInactive, :IdxType do begin sFields = ''; for select rdb$field_name from rdb$index_segments where rdb$index_name = :IdxName order by rdb$field_position into :ifields do begin if (sFields <> '') then sFields = sFields || ', '; sFields = sFields || ibec_formatident(ibec_trim(ifields)); end
DropStmt[i] = 'drop index ' || ibec_formatident(ibec_trim(IdxName)); CreateStmt[i] = 'create ' || ibec_iif(IdxUnique = 1, 'unique ', '') || ibec_iif(IdxType = 1, 'descending ', '') || ' index ' || ibec_formatident(ibec_trim(IdxName)) || ' on ' || ibec_formatident(ibec_trim(IdxRelName)) || ' (' || sFields || ')';
i = i + 1; end
i = 0; while (i <= ibec_high(DropStmt)) do begin s = DropStmt[i]; info = s; suspend; ibec_progress(info); execute statement :s; commit; s = CreateStmt[i]; info = s; suspend; ibec_progress(info); execute statement :s; commit;
i = i + 1; end end
|
Добавлено @ 22:42Второй способ пересоздания индексов Код | execute ibeblock returns (info varchar(1000)) as begin select i.rdb$index_name, i.rdb$relation_name, i.rdb$unique_flag, i.rdb$index_inactive, i.rdb$index_type from rdb$indices i left join rdb$relation_constraints rc on (i.rdb$index_name = rc.rdb$index_name) where (i.rdb$system_flag is null) and (rc.rdb$index_name is null) as dataset ds_indices;
while (not ibec_ds_eof(ds_indices)) do begin IdxName = ibec_trim(ibec_ds_getfield(ds_indices,0)); IdxRelName = ibec_trim(ibec_ds_getfield(ds_indices,1)); IdxUnique = ibec_ds_getfield(ds_indices,2); IdxInactive = ibec_ds_getfield(ds_indices,3); IdxType = ibec_ds_getfield(ds_indices,4);
sFields = ''; for select rdb$field_name from rdb$index_segments where rdb$index_name = :IdxName order by rdb$field_position into :IdxField do begin IdxField = ibec_trim(IdxField); if (sFields <> '') then sFields = sFields || ', '; sFields = sFields || ibec_formatident(IdxField); end
DropStmt = 'drop index ' || ibec_formatident(IdxName); CreateStmt = 'create ' || ibec_iif(IdxUnique = 1, 'unique ', '') || ibec_iif(IdxType = 1, 'descending ', '') || ' index ' || ibec_formatident(IdxName) || ' on ' || ibec_formatident(IdxRelName) || ' (' || sFields || ')';
info = DropStmt; suspend; ibec_progress(info); execute statement :DropStmt; commit; info = CreateStmt; suspend; ibec_progress(info); execute statement :CreateStmt; commit;
ibec_ds_next(ds_indices); end
close dataset ds_indices; end
|
/Скрипты не мои. Найдены на просторах интернета/
|