定位SQLServer数据库执行语句的二进制截断提示的超长字段-526互联

const
ConstTSQL = '|INSERT|UPDATE|DELETE|CREATE|ALTER|DROP|EXEC|';
function FindDbShortField(aQry: TFDQuery): String;
// 搜指定SQL关键字
function SearchSQLWord(const SQL, sWord: string; var index: Integer): Boolean;
var
I: Integer;
s: String;
IsQuoted: Boolean;
Ibracket: Integer;
begin
index := 0;
Result := False;
// 跳过空白
for I := IfThen(index <= 0, 1, index) to Length(SQL) do
if SQL[I] <= ' ' then
inc(index)
else
Break;
// 查找关键字
s := '';
IsQuoted := False;
Ibracket := 0;
for I := IfThen(index <= 0, 1, index) to Length(SQL) do
begin
if SQL[I] = '''' then
IsQuoted := not IsQuoted
else if (SQL[I] = '(') and not(IsQuoted) then
inc(Ibracket)
else if (SQL[I] = ')') and not(IsQuoted) then
Dec(Ibracket)
else if (SQL[I] > ' ') and not(SQL[I] in [',']) then
s := s + SQL[I]
else
begin
if IsQuoted or (Ibracket > 0) then
// nothing...
else if s = sWord then
begin
index := I - Length(s);
Result := True;
Break;
end;
s := ''
end;
end;
end;
// 查找sWords中任1个关键字最先出现的位置(若未找到，返回SQL长度+1)
function FirstSQLindex(SQL, sWords: string; out index: Integer): Boolean;
var
I: Integer;
s: String;
IsQuoted: Boolean;
Ibracket: Integer;
begin
Result := False;
s := '';
index := 0;
IsQuoted := False;
Ibracket := 0;
for I := 1 to Length(SQL) do
begin
if SQL[I] = '''' then
IsQuoted := not IsQuoted
else if (SQL[I] = '(') and not(IsQuoted) then
inc(Ibracket)
else if (SQL[I] = ')') and not(IsQuoted) then
Dec(Ibracket)
else if (SQL[I] > ' ') and not(SQL[I] in [',']) then
s := s + SQL[I]
else
begin
if IsQuoted or (Ibracket > 0) then
// nothing...
else if sWords.Contains('|' + s + '|') then
begin
Result := True;
if (index = 0) or (I - Length(s) < index) then
index := I - Length(s);
Break;
end;
s := ''
end;
end;
if not Result then
index := Length(SQL) + 1;
end;

var
SQL: String;
s, v: String;
index,i,n: Integer;
Idx_insert, Idx_update: Integer;
Has_insert, Has_update: Boolean;
Sel1, Sel2: String;
SQL1, SQL2: string;
TbName1, TbName2: String;
IsValues: Boolean;
IsQuoted: Boolean;
Ibracket: Integer;
Qry1,Qry2:TFDQuery;
SL:Tstringlist;
label Again;
begin
Result := '';
SL := ListPool.GetObj;
Qry1 := QryPool.GetObj;
Qry2 := QryPool.GetObj;
Qry1.Connection := DATABASE;
Qry2.Connection := DATABASE;
Qry1.SQL.Clear;
Qry2.SQL.Clear;
SQL := aQry.SQL.Text;
SQL := SQL.ToUpper;
// Insert into TBNAME
// Update TBNAME
try
Again:
Sel1 := '';
Sel2 := '';
TbName1 := '';
TbName2 := '';
SQL1 := '';
SQL2 := '';
Has_insert := SearchSQLWord(SQL, 'INSERT', Idx_insert);
Has_update := SearchSQLWord(SQL, 'UPDATE', Idx_update);
if (Has_insert) and (not(Has_update) or (Idx_insert <= Idx_update)) then
begin
index := Idx_insert; // Pos('insert',SQL);
System.Delete(SQL, 1, index - 1 + 6);
index := Pos('INTO', SQL);
if index = 0 then
inc(index)
else
inc(index, 4);
for I := index to Length(SQL) do // 跳过Into后空白
if SQL[I] <= ' ' then
inc(index)
else
Break;
for I := index to Length(SQL) do // 取Into后表名
if (SQL[I] >= '0') or (SQL[I] in ['#', '.']) then
TbName1 := TbName1 + SQL[I]
else
Break;
System.Delete(SQL, 1, index + Length(TbName1) - 1);
SQL := TrimLeft(SQL); // 跳过表名后空白
if Copy(SQL, 1, 4) = 'WITH' then
begin
IsQuoted := False;
for I := 5 to Length(SQL) do
if SQL[I] = '(' then
IsQuoted := True
else if (SQL[I] = ')') and IsQuoted then
begin
System.Delete(SQL, 1, I);
Break;
end;
if not IsQuoted then
Exit;
end;
SQL := TrimLeft(SQL); // 跳过后空白
if Copy(SQL, 1, 1) = '(' then
begin
Sel1 := '';
IsQuoted := False;
for I := 2 to Length(SQL) do
if (SQL[I] = ')') and not(IsQuoted) then
begin
System.Delete(SQL, 1, I);
Break;
end
else
begin
Sel1 := Sel1 + SQL[I];
if SQL[I] = '[' then
IsQuoted := True;
if SQL[I] = ']' then
IsQuoted := False;
end;
end
else
begin
Sel1 := '*';
end;
if Copy(SQL, 1, 6) = 'SELECT' then
begin
index := Pos('SELECT', SQL);
if index = 0 then
Exit;
System.Delete(SQL, 1, index - 1 + 6);
// 查from
SQL := TrimLeft(SQL);
index := 1;
if SearchSQLWord(SQL, 'FROM', index) then
begin
Sel2 := Copy(SQL, 1, index - 1);
System.Delete(SQL, 1, index - 1 + 4);
// 取from后表名
// 发现下一段SQL
SQL := TrimLeft(SQL);
FirstSQLindex(SQL, ConstTSQL, index);
TbName2 := Copy(SQL, 1, index - 1);
System.Delete(SQL, 1, index - 1);
end
else
begin
Sel2 := SQL;
end;
SQL1 := 'select top 1 ' + Sel1 + ' from ' + TbName1;
if TbName2 = '' then
SQL2 := 'select ' + Sel2
else
SQL2 := 'select ' + Sel2 + ' from ' + TbName2;
end
else
if Pos('VALUES', SQL) > 0 then
begin
System.Delete(SQL, 1, Pos('VALUES', SQL) + 5);
SQL := TrimLeft(SQL);
if Copy(SQL, 1, 1) <> '(' then
Exit;
Sel2 := '';
IsQuoted := False;
for I := 2 to Length(SQL) do
if (SQL[I] = ')') and not(IsQuoted) then
begin
System.Delete(SQL, 1, I);
Break;
end
else
begin
Sel2 := Sel2 + SQL[I];
if SQL[I] = '''' then
IsQuoted := not IsQuoted;
end;
SQL1 := 'select top 1 ' + Sel1 + ' from ' + TbName1;
SQL2 := 'select ' + Sel2;
end;
end
else if Has_update then // if PosEx('update',SQL)>0 then
begin
index := Idx_update; // Pos('update',SQL);
System.Delete(SQL, 1, index - 1 + 6);
index := 1;
for I := index to Length(SQL) do // 跳过后空白
if SQL[I] <= ' ' then
inc(index)
else
Break;
for I := index to Length(SQL) do // 取表名
if (SQL[I] >= '0') or (SQL[I] in ['#', '.']) then
TbName1 := TbName1 + SQL[I]
else
Break;
System.Delete(SQL, 1, index + Length(TbName1) - 1);
// 找Set
index := 1;
if not SearchSQLWord(SQL, 'SET', index) then
Exit;
System.Delete(SQL, 1, index + 3 - 1);
// 分解字段Sel1,Sel2
SQL := TrimLeft(SQL);
IsValues := False;
IsQuoted := False;
s := '';
for I := 1 to Length(SQL) do // 取 xxx = xxx
begin
if SQL[I] = '''' then
IsQuoted := not IsQuoted;
//
if not(IsQuoted) and (SQL[I] = '=') then
begin
if Sel1 <> '' then
Sel1 := Sel1 + ',';
Sel1 := Sel1 + s;
s := '';
IsValues := True;
end
else if not(IsQuoted) and IsValues and (Trim(s) <> '') and
((SQL[I] <= ' ') or (SQL[I] in [','])) then
begin
if Sel2 <> '' then
Sel2 := Sel2 + ',';
Sel2 := Sel2 + s;
s := '';
IsValues := False;
end
else if not(IsQuoted) and not(IsValues) and
((SQL[I] <= ' ') or (SQL[I] in [','])) then
begin
if Pos('|' + Trim(s) + '|', '|WHERE|FROM' + ConstTSQL) > 0 then
Break;
end
else
begin
s := s + SQL[I];
end;
end;
System.Delete(SQL, 1, I - 1);
// 查From
if Trim(s) = 'FROM' then
begin
SQL := TrimLeft(SQL);
FirstSQLindex(SQL, ConstTSQL, index);
TbName2 := Copy(SQL, 1, index - 1);
System.Delete(SQL, 1, index - 1);
v := '';
// 查别名
s := UpperCase(TbName2) + ' ';
s := StringReplace(s, 'AS', ' ', [rfReplaceAll]);
s := StringReplace(s, ',', ' ', [rfReplaceAll]);
s := StringReplace(s, #13, ' ', [rfReplaceAll]);
index := Pos(' ' + UpperCase(TbName1) + ' ', s);
if index > 0 then
begin
Dec(index);
for I := index downto 1 do // 跳过前空白
if s[I] <= ' ' then
Dec(index)
else
Break;
for I := index downto 1 do // 查真实表名
if s[I] <= ' ' then
Break
else
v := s[I] + v;
end;
/// /
if v <> '' then
begin
SQL1 := 'select top 1 ' + Sel1 + ' from ' + v + ' ' + TbName1;
SQL2 := 'select ' + Sel2 + ' from ' + TbName2;
end
else
begin
SQL1 := 'select top 1 ' + Sel1 + ' from ' + TbName1;
SQL2 := 'select ' + Sel2 + ' from ' + TbName1 + ',' + TbName2;
end;
end
else
begin
SQL1 := 'select top 1 ' + Sel1 + ' from ' + TbName1;
SQL2 := 'select ' + Sel2 + ' from ' + TbName1;
if Trim(s) = 'WHERE' then
begin
FirstSQLindex(SQL, ConstTSQL, index);
TbName2 := Copy(SQL, 1, index - 1);
System.Delete(SQL, 1, index - 1);
SQL2 := SQL2 + ' where ' + TbName2;
end;
end;
end;
if (SQL1 = '') or (SQL2 = '') then
Exit;
// 执行SQL对比
try
Qry1.SQL.Text := SQL1;
Qry2.SQL.Text := SQL2;
for I := 0 to Qry2.ParamCount - 1 do
begin
if aQry.Params.FindParam(Qry2.Params[I].Name) <> nil then
begin
Qry2.Params[I].Assign
(aQry.Params.FindParam(Qry2.Params[I].Name));
end;
end;
Qry1.Open;
Qry2.Open;
if Qry1.Fields.Count = Qry2.Fields.Count then
begin
for I := 0 to Qry1.Fields.Count - 1 do
begin
if (Qry1.Fields[I].Size > 0) and
(Qry1.Fields[I].DataType in [ftString, ftMemo, ftWideString,
ftWideMemo, ftFmtMemo, ftBytes, ftVarBytes]) then
begin
n := Qry2.Fields[I].Size;
if (n <= 0) then
begin
Qry2.First;
if (Qry1.Fields[I].DataType in [ftWideString, ftWideMemo]) then
n := Length(VarToWideStr(Qry2.Fields[I].Value))
else
n := Length(VarToStr(Qry2.Fields[I].Value));
while not Qry2.Eof do
begin
if (Qry1.Fields[I].DataType in [ftWideString, ftWideMemo]) then
index := Length(VarToWideStr(Qry2.Fields[I].Value))
else
index := Length(VarToStr(Qry2.Fields[I].Value));
if index > n then
n := index;
Qry2.Next;
end;
end
else if not(Qry1.Fields[I].DataType in [ftWideString, ftWideMemo])
and (Qry2.Fields[I].DataType in [ftWideString, ftWideMemo]) then
begin
Qry2.First;
while not Qry2.Eof do
begin
index := Length(VarToStr(Qry2.Fields[I].Value));
if index > n then
n := index;
Qry2.Next;
end;
end;
if (Qry1.Fields[I].Size < n) then
begin
s := '目标表[' + UpperCase(TbName1) + ']字段[' +
UpperCase(Qry1.Fields[I].FieldName) + ']长度:' +
IntToStr(Qry1.Fields[I].Size) + '，待写字符串(' +
VarToWideStr(Qry2.Fields[I].Value) + ')长度';
v := IntToStr(n);
index := SL.IndexOfName(s);
if index < 0 then
SL.Add(s + '=' + v)
else if StrToIntDef(SL.ValueFromIndex[index], 0) < n then
SL.ValueFromIndex[index] := v;
end;
end;
end;
end;
except
// Exit;
end;
goto Again;
finally
Result := SL.Text.TrimRight;
ListPool.PutObj(SL);
QryPool.PutObj(Qry1);
QryPool.PutObj(Qry2);
end;
end;