FB 2.14 - Delphi 2007 - IBDAC TIBCScript
Although I'm certain this is not a Delphi problem, I've included a copy of the
commands used to create the SQL. Those commands work in FlameRobin, and in
IBExpert. I used both to create working DDL, which I copied into my delphi
program. Any help or suggestions will be greatly appreciated. I've been beating
my head against this for hours. Ugh.
When I execute the script below, Everything appears to work fine until it
reaches the line that says:
Script1.SQL.Add(' S = Replace(S, ''BB'', ''B''); ');
However : Script1.SQL.Add(' S = Replace(S, ''A'', ''''); '); seems to work.
Odd.
So the "BB" line is where I get the following error message. I don't
understand why since the single letter replacement seems to work. I tried other
letter combinations such as 'YY' or 'SSS', but nothing seems to work once there
are more than one character.
Error Message:
Project ABS3.exe raised exception class EIBCError with message '
Dynamic SQL Error
SQL error code = -104
Token unknown - line 57, column 19
SSS'.
Code Sample:
Script1:= TIBCScript.Create(nil);
Script1.Delimiter := '||'
begin
Script1.SQL.Clear;
Script1.SQL.Add('CREATE OR ALTER PROCEDURE STANDARDIZENAMEADDRESS (');
Script1.SQL.Add('S D_SEARCHFIELD) ');
Script1.SQL.Add('RETURNS ( ');
Script1.SQL.Add(' RESULT D_SEARCHFIELD) ');
Script1.SQL.Add('AS ');
Script1.SQL.Add('begin ');
Script1.SQL.Add(' S = Upper(S);');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Spaces. Do three times. ');
Script1.SQL.Add(' S = Replace(S, '' '', ''''); ');
Script1.SQL.Add(' S = Replace(S, '' '', ''''); ');
Script1.SQL.Add(' S = Replace(S, '' '', ''''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove apostrophy ');
Script1.SQL.Add(' S = Replace(S, ''#39'', ''''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Vowels ');
Script1.SQL.Add(' S = Replace(S, ''A'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''E'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''I'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''O'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''U'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''Y'', ''''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove non-alphanumeric ');
Script1.SQL.Add(' S = Replace(S, Chr(96), ''''); ');
Script1.SQL.Add(' S = Replace(S, ''~'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''!'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''@'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''#'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''$'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''%'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''^'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''&'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''*'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''('', ''''); ');
Script1.SQL.Add(' S = Replace(S, '')'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''_'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''-'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''{'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''}'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''['', ''''); ');
Script1.SQL.Add(' S = Replace(S, '']'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''|'', ''''); ');
Script1.SQL.Add(' S = Replace(S, ''\'', ''''); ');
Script1.SQL.Add(' S = Replace(S, '':'', ''''); ');
Script1.SQL.Add(' S = Replace(S, '';'', ''''); ');
Script1.SQL.Add(' S = Replace(S, '''''', ''''); ');
Script1.SQL.Add(' S = Replace(S, Chr(60), ''''); ');
Script1.SQL.Add(' S = Replace(S, Chr(62), ''''); ');
Script1.SQL.Add(' S = Replace(S, Chr(63), ''''); ');
Script1.SQL.Add(' S = Replace(S, ''/'', ''''); ');
Script1.SQL.Add(' S = Replace(S, '','', ''''); ');
Script1.SQL.Add(' S = Replace(S, Chr(46), ''''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Double Letters ');
Script1.SQL.Add(' S = Replace(S, ''BB'', ''B''); ');
// Error occurs in the above line. =====================================
Script1.SQL.Add(' S = Replace(S, ''CC'', ''C''); ');
Script1.SQL.Add(' S = Replace(S, ''DD'', ''D''); ');
Script1.SQL.Add(' S = Replace(S, ''FF'', ''F''); ');
Script1.SQL.Add(' S = Replace(S, ''GG'', ''G''); ');
Script1.SQL.Add(' S = Replace(S, ''HH'', ''H''); ');
Script1.SQL.Add(' S = Replace(S, ''JJ'', ''J''); ');
Script1.SQL.Add(' S = Replace(S, ''KK'', ''K''); ');
Script1.SQL.Add(' S = Replace(S, ''LL'', ''L''); ');
Script1.SQL.Add(' S = Replace(S, ''MM'', ''M''); ');
Script1.SQL.Add(' S = Replace(S, ''NN'', ''N''); ');
Script1.SQL.Add(' S = Replace(S, ''PP'', ''P''); ');
Script1.SQL.Add(' S = Replace(S, ''QQ'', ''Q''); ');
Script1.SQL.Add(' S = Replace(S, ''RR'', ''R''); ');
Script1.SQL.Add(' S = Replace(S, ''SS'', ''S''); ');
Script1.SQL.Add(' S = Replace(S, ''TT'', ''T''); ');
Script1.SQL.Add(' S = Replace(S, ''VV'', ''V''); ');
Script1.SQL.Add(' S = Replace(S, ''WW'', ''W''); ');
Script1.SQL.Add(' S = Replace(S, ''XX'', ''X''); ');
Script1.SQL.Add(' S = Replace(S, ''YY'', ''Y''); ');
Script1.SQL.Add(' S = Replace(S, ''ZZ'', ''Z''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Double Letters again (second time) ');
Script1.SQL.Add(' S = Replace(S, ''BB'', ''B''); ');
Script1.SQL.Add(' S = Replace(S, ''CC'', ''C''); ');
Script1.SQL.Add(' S = Replace(S, ''DD'', ''D''); ');
Script1.SQL.Add(' S = Replace(S, ''FF'', ''F''); ');
Script1.SQL.Add(' S = Replace(S, ''GG'', ''G''); ');
Script1.SQL.Add(' S = Replace(S, ''HH'', ''H''); ');
Script1.SQL.Add(' S = Replace(S, ''JJ'', ''J''); ');
Script1.SQL.Add(' S = Replace(S, ''KK'', ''K''); ');
Script1.SQL.Add(' S = Replace(S, ''LL'', ''L''); ');
Script1.SQL.Add(' S = Replace(S, ''MM'', ''M''); ');
Script1.SQL.Add(' S = Replace(S, ''NN'', ''N''); ');
Script1.SQL.Add(' S = Replace(S, ''PP'', ''P''); ');
Script1.SQL.Add(' S = Replace(S, ''QQ'', ''Q''); ');
Script1.SQL.Add(' S = Replace(S, ''RR'', ''R''); ');
Script1.SQL.Add(' S = Replace(S, ''SS'', ''S''); ');
Script1.SQL.Add(' S = Replace(S, ''TT'', ''T''); ');
Script1.SQL.Add(' S = Replace(S, ''VV'', ''V''); ');
Script1.SQL.Add(' S = Replace(S, ''WW'', ''W''); ');
Script1.SQL.Add(' S = Replace(S, ''XX'', ''X''); ');
Script1.SQL.Add(' S = Replace(S, ''YY'', ''Y''); ');
Script1.SQL.Add(' S = Replace(S, ''ZZ'', ''Z''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Tripple Letters just to be sure.');
Script1.SQL.Add(' S = Replace(S, ''BBB'', ''B''); ');
Script1.SQL.Add(' S = Replace(S, ''CCC'', ''C''); ');
Script1.SQL.Add(' S = Replace(S, ''DDD'', ''D''); ');
Script1.SQL.Add(' S = Replace(S, ''FFF'', ''F''); ');
Script1.SQL.Add(' S = Replace(S, ''GGG'', ''G''); ');
Script1.SQL.Add(' S = Replace(S, ''HHH'', ''H''); ');
Script1.SQL.Add(' S = Replace(S, ''JJJ'', ''J''); ');
Script1.SQL.Add(' S = Replace(S, ''KKK'', ''K''); ');
Script1.SQL.Add(' S = Replace(S, ''LLL'', ''L''); ');
Script1.SQL.Add(' S = Replace(S, ''MMM'', ''M''); ');
Script1.SQL.Add(' S = Replace(S, ''NNN'', ''N''); ');
Script1.SQL.Add(' S = Replace(S, ''PPP'', ''P''); ');
Script1.SQL.Add(' S = Replace(S, ''QQQ'', ''Q''); ');
Script1.SQL.Add(' S = Replace(S, ''RRR'', ''R''); ');
Script1.SQL.Add(' S = Replace(S, ''SSS'', ''S''); ');
Script1.SQL.Add(' S = Replace(S, ''TTT'', ''T''); ');
Script1.SQL.Add(' S = Replace(S, ''VVV'', ''V''); ');
Script1.SQL.Add(' S = Replace(S, ''WWW'', ''W''); ');
Script1.SQL.Add(' S = Replace(S, ''XXX'', ''X''); ');
Script1.SQL.Add(' S = Replace(S, ''YYY'', ''Y''); ');
Script1.SQL.Add(' S = Replace(S, ''ZZZ'', ''Z''); ');
Script1.SQL.Add(' ');
Script1.SQL.Add(' Result = S;');
Script1.SQL.Add(' suspend;');
Script1.SQL.Add('end ||');
try
Script1.Execute ;
Script1.SQL.Clear;
except
exit;
end;
... more Grants etc.follow
