📄 dmsql.pas
字号:
lcv, lcv2 : integer;
sPath : string;
sText : string;
begin
sPath := ExtractFilePath(paramstr(0));
MyIni := TIniFile.Create(spath + 'macros.ini');
try
// Clear it out.
oListView.Items.Clear;
// Clear the columns.
oListView.Columns.Clear;
// Create new columns.
oColumn := oListView.Columns.Add;
oColumn.Caption := 'Name';
oColumn.Width := 100;
oColumn := oListView.Columns.Add;
oColumn.Caption := 'Descr';
oColumn.Width := 120;
oColumn := oListView.Columns.Add;
oColumn.Caption := 'Text';
oColumn.Width := 200;
// Fill the columns.
for lcv := 1 to 500 do begin
if MyIni.SectionExists('MACRO' + inttostr(lcv)) then begin
oListItem := oListView.Items.Add;
oListItem.Caption := MyIni.ReadString('MACRO' + inttostr(lcv), 'NAME', '');
oListItem.SubItems.Add(MyIni.ReadString('MACRO' + inttostr(lcv), 'DESCR', ''));
oListItem.SubItems.Add(MyIni.ReadString('MACRO' + inttostr(lcv), 'TEXT1', ''));
for lcv2 := 1 to 1000 do begin
sText := MyIni.ReadString('MACRO' + inttostr(lcv), 'TEXT' + inttostr(lcv2), '');
if (sText <> '') then begin
// Note that the '~' is passed in as a space.
if sText = '~' then sText := '';
oListItem.SubItems.Add(sText);
end else break;
end;
end else break;
end;
finally
MyIni.free;
end;
end;
{
Format Template Text. There is a lot of repeated code in here, but it would
have been a real pain to make the text formatting work with both macros and
templates. So, here it is.
}
procedure Tbo_SQL.FormatTemplateText(oTreeNode : TTreeNode; oStrings : TStrings; dadLayout : TDadLayout);
var
lcv : integer;
iLastPos : integer;
s : string;
iTerminator : integer; {0=none, 1=',', 2='and'}
SQL_OBJ : variant; // The table object for the passed tree node.
SQL_COL : variant;
function GetDatatypeFormat : string;
begin
if (SQL_COL.PhysicalDatatype = 'char') or
(SQL_COL.PhysicalDatatype = 'nchar') or
(SQL_COL.PhysicalDatatype = 'varchar') or
(SQL_COL.PhysicalDatatype = 'nvarchar') or
(SQL_COL.PhysicalDatatype = 'sysname') or
(SQL_COL.PhysicalDatatype = 'text') or
(SQL_COL.PhysicalDatatype = 'ntext') then begin
result := SQL_COL.PhysicalDatatype + '(' + inttostr(SQL_COL.Length) + ')';
end else if (SQL_COL.PhysicalDatatype = 'float') or
(SQL_COL.PhysicalDatatype = 'decimal') or
(SQL_COL.PhysicalDatatype = 'money') or
(SQL_COL.PhysicalDatatype = 'numeric') or
(SQL_COL.PhysicalDatatype = 'real') or
(SQL_COL.PhysicalDatatype = 'smallmoney') then begin
result := SQL_COL.PhysicalDatatype + '(' +
inttostr(SQL_COL.NumericPrecision) + ',' +
inttostr(SQL_COL.NumericScale) + ')';
end else begin
// Default.
result := SQL_COL.PhysicalDatatype;
end;
end;
begin
SQL_OBJ := PdmoObject(oTreeNode.data)^.SQL_OBJ;
iLastPos := 0;
iTerminator := 0;
for lcv := 1 to SQL_OBJ.Columns.Count do begin
// Set the column variant (makes things easier).
SQL_COL := SQL_OBJ.Columns.Item(lcv);
case dadLayout of
dad_Field: begin
iLastPos := oStrings.add(' ' + SQL_COL.name + ',');
iTerminator := 1;
end;
dad_Alias: begin
iLastPos := oStrings.add(SQL_OBJ.name);
iTerminator := 0;
break;
end;
dad_aField: begin
iLastPos := oStrings.add(' @' + SQL_COL.name);
iTerminator := 0;
end;
dad_aField2: begin
iLastPos := oStrings.add(' @' + SQL_COL.name + ',');
iTerminator := 1;
end;
dad_Alias_Field: begin
iLastPos := oStrings.add(' ' + SQL_OBJ.name + '.' + SQL_COL.name + ',');
iTerminator := 1;
end;
dad_aField_Equals_Field: begin
iLastPos := oStrings.add(' @' + SQL_COL.name + ' = ' + SQL_COL.name + ',');
iTerminator := 1;
end;
dad_aField_DataType: begin
iLastPos := oStrings.add(' @' + SQL_COL.name + ' ' + GetDatatypeFormat + ',');
iTerminator := 1;
end;
dad_Field_Equals_aField_and: begin
iLastPos := oStrings.add(' ' + SQL_COL.name + ' = ' + '@' + SQL_COL.name + ' and');
iTerminator := 2;
end;
dad_Field_Equals_aField: begin
iLastPos := oStrings.add(' ' + SQL_COL.name + ' = ' + '@' + SQL_COL.name + ',');
iTerminator := 1;
end;
dad_Alias_Field_Equals_aField_and: begin
iLastPos := oStrings.add(' ' + SQL_OBJ.name + '.' + SQL_COL.name + ' = ' + '@' + SQL_COL.name + ' and');
iTerminator := 2;
end;
dad_SP_Alias: begin
iLastPos := oStrings.add('sp' + SQL_OBJ.name + '$');
iTerminator := 0;
break;
end;
dad_SP_Alias_Get: begin
iLastPos := oStrings.add('sp' + SQL_OBJ.name + '$get');
iTerminator := 0;
break;
end;
dad_SP_Alias_Ins: begin
iLastPos := oStrings.add('sp' + SQL_OBJ.name + '$ins');
iTerminator := 0;
break;
end;
dad_SP_Alias_Upd: begin
iLastPos := oStrings.add('sp' + SQL_OBJ.name + '$upd');
iTerminator := 0;
break;
end;
dad_SP_Alias_Del: begin
iLastPos := oStrings.add('sp' + SQL_OBJ.name + '$del');
iTerminator := 0;
break;
end;
end;
end;
// Clear up the last terminator.
s := oStrings[iLastPos];
case iTerminator of
0:;
1: Delete(s, Length(s), 1);
2: Delete(s, Length(s)-2, 3);
end;
oStrings[iLastPos] := s;
end;
{
procedure parses the template for the delimited fields, one line at a time,
and formats them accordingly. NOTE that I have set it up to only allow
one delimited on a line at a time. This is to make the parsing more simple.
}
procedure Tbo_SQL.ParseTemplate(oTreeNode: TTreeNode; oStrings : TStrings; sText : string; dadLayout : TdadLayout);
var
sFirst, sSecond : string; // Use to save off the text before and after the delimiter.
sUser : array[1..255] of char;
i : dword;
s : string;
iLastPos : integer;
begin
iLastPos := oStrings.Count;
if pos('<SP_PROCEDURE_NAME>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<SP_PROCEDURE_NAME>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<SP_PROCEDURE_NAME>', uppercase(sText))+19, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dadLayout);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if pos('<PARAMETERS>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<PARAMETERS>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<PARAMETERS>', uppercase(sText))+12, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dad_aField_DataType);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if pos('<MODIFICATION_STAMP>', uppercase(sText)) > 0 then begin
i := 255;
GetUserName(@sUser[1], i);
s := sUser;
setlength(s, i);
uniquestring(s);
s := '-- ' + trim(s) + ' ' + (FormatDateTime('mm/dd/yyyy', Date)) + #$9 + 'Created.';
oStrings.add(s);
end else if pos('<FIELD_LIST>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<FIELD_LIST>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<FIELD_LIST>', uppercase(sText))+12, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dad_Field);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if pos('<ALIAS_NAME>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<ALIAS_NAME>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<ALIAS_NAME>', uppercase(sText))+12, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dad_Alias);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if pos('<PK_FIELD=PARAMETER>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<PK_FIELD=PARAMETER>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<PK_FIELD=PARAMETER>', uppercase(sText))+20, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dad_Alias_Field_Equals_aField_and);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if pos('<PK_FIELD>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<PK_FIELD>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<PK_FIELD>', uppercase(sText))+10, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dad_Field);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if pos('<FIELD=PARAMETER>', uppercase(sText)) > 0 then begin
sFirst := trim(copy(sText, 0, pos('<FIELD=PARAMETER>', uppercase(sText))-1));
sSecond := trim(copy(sText, pos('<FIELD=PARAMETER>', uppercase(sText))+17, Length(sText)));
FormatTemplateText(oTreeNode, oStrings, dad_Field_Equals_aField);
if sFirst <> '' then
oStrings[iLastPos] := trim(sFirst + ' ' + trim(oStrings[iLastPos]) + sSecond)
else
oStrings[iLastPos] := oStrings[iLastPos] + ' ' + sSecond;
end else if sText = '~' then begin
oStrings.add(' ');
end else begin
oStrings.add(sText);
end;
end;
{
Mechanism to build an entire script based on the selected values in
the treeview and details list view. This part spins through the
INI file and calls the parse method on each line.
}
procedure Tbo_SQL.BuildFromTemplate(oTreeNode: TTreeNode; oStrings : TStrings; dadLayout : TDadLayout);
var
MyIni : TIniFile;
lcv : integer;
sPath : string;
sText : string;
sSection : string;
begin
sPath := ExtractFilePath(paramstr(0));
MyIni := TIniFile.Create(spath + 'templates.ini');
oStrings.Clear;
try
// Determine the INI section to use.
case dadLayout of
dad_SP_Alias_Get: sSection := 'GET';
dad_SP_Alias_Ins: sSection := 'INS';
dad_SP_Alias_Upd: sSection := 'UPD';
dad_SP_Alias_Del: sSection := 'DEL';
end;
// Read in the INI file and parse each line.
if MyIni.SectionExists(sSection) then begin
for lcv := 1 to 5000 do begin
sText := MyIni.ReadString(sSection, 'TEXT' + inttostr(lcv), '');
// An empty sText indicates the end of the script.
if (sText <> '') then begin
// Parse and Add the line.
ParseTemplate(oTreeNode, oStrings, sText, dadLayout);
end else break;
end;
end;
finally
MyIni.free;
end;
end;
{
Method to return the text for a particular view, stored proc, or trigger.
}
procedure Tbo_SQL.GetSQLScriptText(oMemo : TMemo; oTreeNode : TTreeNode);
begin
// Prevent unselected nodes from entering.
if (oTreeNode = nil) or
(not assigned(oTreeNode.Parent)) or
VarIsEmpty(PdmoObject(oTreeNode.data)^.SQL_OBJ) then exit;
// Get the script.
oMemo.text := PdmoObject(oTreeNode.data)^.SQL_OBJ.Script(SQLDMOScript_Default + SQLDMOScript_Drops);
end;
{
Central method to execute the script and test the results.
}
function Tbo_SQL.Execute(oSourceMemo, oResultMemo : TMemo; oTreeView : TTreeView; lClearResultsFirst : boolean; sDelimiter : string; lExecute : boolean) : boolean;
var
QueryResults : variant;
lcv, lcv2, lcv3 : integer;
s : string;
db : variant;
begin
result := false;
// Empty script won't execute.
if osourceMemo.text = '' then exit;
db := PdmoObject(oTreeView.Selected.data)^.SQL_DB;
// Execute the SQL and return any results.
if lExecute then begin
// Execute the script and return any results.
if oSourceMemo.SelLength > 0 then
QueryResults := db.ExecuteWithResults(oSourceMemo.SelText, length(oSourceMemo.SelText))
else
QueryResults := db.ExecuteWithResults(oSourceMemo.Text, length(oSourceMemo.Text));
// Spin thru the results sets and display the data to the user.
if QueryResults.ResultSets > 0 then begin
// Clear the results if the user wants.
if lClearResultsFirst then oResultMemo.Lines.Clear;
for lcv := 1 to QueryResults.ResultSets do begin
// Select the result set.
QueryResults.CurrentResultSet := lcv;
for lcv2 := 1 to QueryResults.Rows do begin
s := '';
for lcv3 := 1 to QueryResults.Columns do begin
s := s + trimright(QueryResults.GetColumnString(lcv2, lcv3)) + sDelimiter;
end;
oResultMemo.lines.Add(s);
end;
end;
result := true; // Has results.
end else begin
showmessage('Successful execution.');
result := false; // Don't have results.
end;
end else begin
// Only parse the SQL.
if oSourceMemo.SelLength > 0 then
db.ExecuteImmediate(oSourceMemo.SelText, SQLDMOExec_NoExec, length(oSourceMemo.SelText))
else
db.ExecuteImmediate(oSourceMemo.Text, SQLDMOExec_NoExec, length(oSourceMemo.Text));
ShowMessage('Successful Parse.');
end;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -