Code: Select all
procedure TV6ExportForm.QueryExportEIDs (stTable : String; stField : String);
var
stQ : String;
stList : String;
lgQ : Boolean;
begin
lgQ := False;
with ExportLiteQuery do
begin
Connection := WC6SourceLite;
SQL.Clear; // Query to check for source records
stQ := 'SELECT * FROM main.' + stTable;
SQL.Add(stQ);
stQ := 'WHERE "'+stField+'" IN (';
SQL.Add(stQ);
with SelectEidDS.DataSet do
begin
First;
while not Eof do
begin
stQ := '''' + FieldByName('EquipmentID').AsString;
Next;
if not Eof then
begin // Add comma and add to SQL string
stQ := stQ + ''',';
ExportLiteQuery.SQL.Add(stQ);
end;
end;
end;
stQ := stQ + ''');'; // Add paren for final EID and add to SQL string
SQL.Add(stQ);
try
stQ := FinalSQL;
Execute;
lgQ := IsQuery;
except
on E: Exception do
begin
end;
end;
if not lgQ then
MessageDlg ('No records returned from query:' + #13#10#13 + stQ, mtError,[mbOK],0);
Close;
if lgQ then
begin // Attach export database and insert records query
SQL.Clear; // Check source query
stQ := 'ATTACH DATABASE ''' + stExportSQLDBFileName+ ''' AS ''Export'';';
SQL.Add(stQ);
stQ := 'INSERT INTO Export.' + stTable;
SQL.Add(stQ);
stQ := 'SELECT * FROM main.' + stTable + ' AS ''Source''';
SQL.Add(stQ);
stQ := 'WHERE Source."'+stField+'" IN (';
SQL.Add(stQ);
with SelectEidDS.DataSet do
begin
First;
stQ := '';
while not Eof do
begin
stQ := '''' + FieldByName('EquipmentID').AsString;
Next;
if not Eof then
begin // Add comma and add to SQL string
stQ := stQ + ''',';
ExportLiteQuery.SQL.Add(stQ);
end;
end;
end;
stList := stQ;
stQ := stQ + ''');'; // Add paren for final EID and add to SQL string
SQL.Add(stQ);
stQ := 'DETACH DATABASE ''Export'';'; // Detach as final step of query
SQL.Add(stQ);
try
if not WC6ExportLite.InTransaction then
WC6ExportLite.StartTransaction;
stQ := FinalSQL;
Execute;
if WC6ExportLite.InTransaction then
WC6ExportLite.Commit;
except
on E: Exception do
begin
end;
end;
Close;
end;
end;
Code: Select all
procedure TV6ExportForm.QueryExportEIDsParam (stTable : String; stField : String);
var
stQ : String;
stList : String;
lgQ : Boolean;
begin
lgQ := False;
with ExportLiteQuery do
begin
Connection := WC6SourceLite;
SQL.Clear; // Check source query
stQ := 'SELECT * FROM main.' + stTable;
SQL.Add(stQ);
stQ := 'WHERE "'+stField+'" IN (:EIDList);';[
SQL.Add(stQ);
with SelectEidDS.DataSet do
begin
First;
stList := '';
while not Eof do
begin
stList := stList + '''' + FieldByName('EquipmentID').AsString;
Next;
if not Eof then // Add single quote and comma
stList := stList + ''','
else
stList := stList + ''''; // add last single quote
end;
end;
ParamByName('EIDList').AsString := stList;
try
stQ := FinalSQL;
Execute;
lgQ := IsQuery;
except
on E: Exception do
begin
end;
end;
if not lgQ then
MessageDlg ('No records returned from query:' + #13#10#13 + stQ, mtError,[mbOK],0);
Close;
if lgQ then
begin // Attach export database and insert records query
SQL.Clear; // Check source query
stQ := 'ATTACH DATABASE ''' + stExportSQLDBFileName+ ''' AS ''Export'';';
SQL.Add(stQ);
stQ := 'INSERT INTO Export.' + stTable;
SQL.Add(stQ);
stQ := 'SELECT * FROM main.' + stTable + ' AS ''Source''';
SQL.Add(stQ);
stQ := 'WHERE Source."'+stField+'" IN (:EIDList);';
SQL.Add(stQ);
stQ := 'DETACH DATABASE ''Export'';'; // Detach as final step of query
SQL.Add(stQ);
ParamByName('EIDList').AsString := stList;
try
if not WC6ExportLite.InTransaction then
WC6ExportLite.StartTransaction;
stQ := FinalSQL;
Execute;
if WC6ExportLite.InTransaction then
WC6ExportLite.Commit;
except
on E: Exception do
begin
end;
end;
Close;
end;
end;