procedure TForm1.Button28Click(Sender: TObject);
var
pdfs: string;
begin
UniConnection1.Loginprompt:=true;
UniScript_cr_linknames.Execute;
uniquery8.Execute;
uniquery9.sql.text:='Select * from refs order by id asc;';
uniquery9.execute;
while not(uniquery9.eof) do
begin
uniquery11.sql.text:='Select * from linknames where id = :vonid group by linkname order by linkname; ';
uniquery11.parambyname('vonid').asinteger:=uniquery9.fieldbyname('id').asinteger;
uniquery11.execute;
pdfs:='';
while not(uniquery11.eof) do
begin
if pdfs='' then
pdfs:=uniquery11.fieldbyname('linkname').asstring
else
pdfs:=pdfs+#13#10+ uniquery11.fieldbyname('linkname').asstring;
//if uniquery11.RecordCount > 1 then ShowMessage(format('Update refs set link_to_pdf = %s where id=%d;',[pdfs,uniquery9.fieldbyname('id').asinteger]));
uniquery11.Next;
end;
uniquery10.sql.text:='Update refs set link_to_pdf = :links where id=:vonid;';
uniquery10.parambyname('links').asstring:=pdfs;
uniquery10.parambyname('vonid').asinteger:=uniquery9.fieldbyname('id').asinteger;
uniquery10.execute;
if Pos(#13,pdfs)> 0 then WriteLOgMemo(Format('UPD: Id: %d done with more than one unique-LINK ) :',[uniquery9.fieldbyname('id').asinteger]));
uniquery9.next;
end;
end;
wherby Query8 и скрипт выглядят так:
insert into linknames (id,linkname)
(SELECT
refs.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(refs.link_to_pdf, '\n', numbers.n), '\n', -1) link_to_pdf
FROM
numbers INNER JOIN refs
ON CHAR_LENGTH(refs.link_to_pdf)
-CHAR_LENGTH(REPLACE(refs.link_to_pdf, '\n', ''))>=numbers.n-1
ORDER BY
id, n)
Таблица чисел просто таблица с числами:
CREATE TABLE `numbers` (
`n` varchar(255) DEFAULT NULL,
`recnum` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`recnum`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;