editorquery.pas

DBDesigner 4 is a database design system that integrates database design, modelling, creation and ma

  //Read IniFile
  theIni:=TMemIniFile.Create(DMMain.SettingsPath+DMMain.ProgName+'_Settings.ini');
  try

    theSaveDialog:=TSaveDialog.Create(nil);
    try
  {$IFDEF MSWINDOWS}
      //On Windows use native Win32 Open Dlg
      theSaveDialog.UseNativeDialog:=True;
      theSaveDialog.OnShow:=DMMain.OnOpenSaveDlgShow;
  {$ENDIF}

      theSaveDialog.Title:=DMMain.GetTranslatedMessage('Save Field As ...', 91);
      theSaveDialog.Width:=600;
      theSaveDialog.Height:=450;
      theSaveDialog.DefaultExt:='';

      RecentSaveBlobFieldDir:=theIni.ReadString('RecentDirectories', 'RecentSaveBlobFieldDir', '');
      if(Not(DirectoryExists(RecentSaveBlobFieldDir)))then
        RecentSaveBlobFieldDir:=ExtractFilePath(Application.ExeName)+
          'Models'+PathDelim;

      theSaveDialog.InitialDir:=RecentSaveBlobFieldDir;


      {theSaveDialog.Position:=Point((Screen.Width-theSaveDialog.Width) div 2,
        (Screen.Height-theSaveDialog.Height) div 2);}

      theSaveDialog.Filter:=DMMain.GetTranslatedMessage('All Files', 90)+' (*.*)|*.*';

      if(theSaveDialog.Execute)then
      begin
        theFileName:=theSaveDialog.Filename;
        if(FileExists(theFileName))then
          if(MessageDlg(DMMain.GetTranslatedMessage('The file [%s] already exists. '#13#10+
            'Do you want to overwrite this file?', 92, ExtractFileName(theFileName)), mtInformation,
            [mbYes, mbNo], 0)=mrNo)then
            Exit;

        RecentSaveBlobFieldDir:=ExtractFilePath(theSaveDialog.FileName);

        if(DBGrid.SelectedField.ClassNameIs('TMemoField'))then
          TMemoField(DBGrid.SelectedField).SaveToFile(theFileName)
        else if(DBGrid.SelectedField.ClassNameIs('TGraphicField'))then
          TGraphicField(DBGrid.SelectedField).SaveToFile(theFileName)
        else if(DBGrid.SelectedField.ClassNameIs('TBlobField'))then
          TBlobField(DBGrid.SelectedField).SaveToFile(theFileName)
        else if(DBGrid.SelectedField.ClassNameIs('TStringField'))then
        begin
          theStringList:=TStringList.Create;
          try
            theStringList.Text:=TStringField(DBGrid.SelectedField).AsString;
            theStringList.SaveToFile(theFileName);
          finally
            theStringList.Free;
          end;
        end;


        theIni.WriteString('RecentDirectories', 'RecentSaveBlobFieldDir', RecentSaveBlobFieldDir);
      end;
    finally
      theSaveDialog.Free;
    end;

    theIni.UpdateFile;
  finally
    theIni.Free;
  end;
end;

procedure TEditorQueryForm.DBGridDblClick(Sender: TObject);
begin
  if(BlobPnl.Visible=False)then
    if(DBGrid.SelectedField<>nil)then
      if(DBGrid.SelectedField.ClassNameIs('TMemoField'))or
        (DBGrid.SelectedField.ClassNameIs('TGraphicField'))or
        (DBGrid.SelectedField.ClassNameIs('TBlobField'))then
        ShowBLOBImgClick(Self);
end;



procedure TEditorQueryForm.SaveSQLBtnClick(Sender: TObject);
var name, initalFolderName: string;
  theSQLCmd: TStoredSQLCmd;
  i: integer;
begin
  if(TForm(Application.MainForm).ActiveMDIChild=nil)then
    Exit;

  {initalFolderName:='';
  if(StoredSQLTreeView.Items.Count>0)then
    if(StoredSQLTreeView.Items[0].Data=nil)then
      initalFolderName:=StoredSQLTreeView.Items[0].Text+'/';

  name:=initalFolderName+name;}
  initalFolderName:='';
  name:='';

  if(DMMain.ShowStringEditor(DMMain.GetTranslatedMessage('Store SQL Command ... (use slashes / to create directories)', 93),
    DMMain.GetTranslatedMessage('Name:', 94),
    name, Length(initalFolderName)))then
  begin
    theSQLCmd:=nil;
    for i:=0 to theEERModel.StoredSQLCmds.Count-1 do
      if(CompareText(TStoredSQLCmd(theEERModel.StoredSQLCmds[i]).StoredPosition,
        name)=0)then
      begin
        theSQLCmd:=TStoredSQLCmd(theEERModel.StoredSQLCmds[i]);
        theSQLCmd.SQLText:=GetSQLMemoText;

        theEERModel.ModelHasChanged;
      end;

    if(theSQLCmd=nil)then
    begin
      //new(theSQLCmd);
      theSQLCmd:=TStoredSQLCmd.Create;
      theSQLCmd.SQLCmdType:=ct_SQLCmd;
      theSQLCmd.StoredPosition:=name;
      theSQLCmd.SQLText:=GetSQLMemoText;
      theEERModel.StoredSQLCmds.Add(theSQLCmd);

      theEERModel.ModelHasChanged;
    end;

    if(Not(StoredSQLBtn.Down))then
      StoredSQLBtn.Click;

    RefreshStoredSQLTreeView(theEERModel);
  end;
end;

procedure TEditorQueryForm.ExecSQLBtnClick(Sender: TObject);
var i, Output, HistoryCount, totalRowsAffected: integer;
  theSQLCmd: TStoredSQLCmd;
  cmds, s: string;
  StartExecTime, EndExecTime: TDateTime;
begin
  //when not connected to DB, connect now
  if(DMDB.CurrentDBConn=nil)then
  begin
    if(TForm(Application.MainForm).ActiveMDIChild<>nil)then
    begin
      if(TForm(Application.MainForm).ActiveMDIChild.Classname='TEERForm')then
      begin
        DMDB.GetDBConnButtonClick(Sender, TEERForm(TForm(Application.MainForm).ActiveMDIChild).EERModel.DefQueryDBConn);
        if(DMDB.CurrentDBConn<>nil)then
          TEERForm(TForm(Application.MainForm).ActiveMDIChild).EERModel.DefQueryDBConn:=DMDB.CurrentDBConn.Name;
      end;
    end
    else
      DMDB.GetDBConnButtonClick(Sender);

    if(DMDB.CurrentDBConn=nil)then
      Exit;
  end
  else
  begin
    DBGrid.Columns.Clear;
    DBMemo.DataField:='';
    DBImage.DataField:='';


    //Apply changes before closing
    if(OutputClientDataSet.Active)then
      if(OutputClientDataSet.ChangeCount>0)then
        OutputClientDataSet.ApplyUpdates(-1);

    OutputQry.Close;
    OutputClientDataSet.Close;

    DBGrid.DataSource:=OutputDataSrc;
  end;

  //if there is no sql statement, exit
	if(GetSQLMemoText='')then
    Exit;

  //For undo/Redo-btns
  currentSQLText:=GetSQLMemoText;
  currentHistoryPos:=0;

  //Keep only 20 SQL Cmds in History
  if(TForm(Application.MainForm).ActiveMDIChild<>nil)then
  begin
    HistoryCount:=0;
    i:=0;
    while(i<theEERModel.StoredSQLCmds.Count)do
    begin
      if(TStoredSQLCmd(theEERModel.StoredSQLCmds[i]).SQLCmdType=ct_SQLHistory)then
      begin
        inc(HistoryCount);
        if(HistoryCount>=20)or
          (TStoredSQLCmd(theEERModel.StoredSQLCmds[i]).SQLText=GetSQLMemoText)then
        begin
          theEERModel.StoredSQLCmds.Delete(i);

          dec(HistoryCount);
        end
        else
          inc(i);
      end
      else
        inc(i);
    end;

    //Make Entry in History
    theSQLCmd:=TStoredSQLCmd.Create;
    theSQLCmd.SQLCmdType:=ct_SQLHistory;
    theSQLCmd.StoredPosition:=FormatDateTime('DD.MM HH:NN:SS', Now);
    theSQLCmd.SQLText:=GetSQLMemoText;
    theEERModel.StoredSQLCmds.Insert(0, theSQLCmd);

    if(Not(DMGUI.IgnoreSQLHistoryChange))then
      theEERModel.ModelHasChanged;

    RefreshStoredSQLTreeView(theEERModel);
  end;

  Output:=1;
  //No command is longer than 8 chars
  s:=Copy(GetSQLMemoText, 1, 8);

  if((CompareText(Copy(s, 1, 6), 'update')=0)
  	or(CompareText(Copy(s, 1, 6), 'insert')=0)
    or(CompareText(Copy(s, 1, 6), 'delete')=0)
    or(CompareText(Copy(s, 1, 4), 'drop')=0)
    or(CompareText(Copy(s, 1, 5), 'alter')=0)
    or(CompareText(Copy(s, 1, 6), 'create')=0)
    or(CompareText(Copy(s, 1, 3), 'set')=0)
    or(CompareText(Copy(s, 1, 4), 'save')=0)
    or(CompareText(Copy(s, 1, 4), 'load')=0)
    or(CompareText(Copy(s, 1, 4), 'move')=0)
    or(CompareText(Copy(s, 1, 5), 'flush')=0)
    or(CompareText(Copy(s, 1, 6), 'commit')=0)
    or(CompareText(Copy(s, 1, 8), 'rollback')=0)
    or(CompareText(Copy(s, 1, 1), '<')=0)
    or(CompareText(Copy(s, 1, 1), '/')=0))then
  	Output:=0;

  if(Output=0)then
  begin
    StartExecTime:=Now;
    totalRowsAffected:=DMDB.ExecuteSQLCmdScript(GetSQLMemoText);
    EndExecTime:=Now;

    DMGUI.SetStatusCaption('Query(s) executed. '+FormatFloat('##,###,##0', totalRowsAffected)+' Rows affected. Time: '+
      FormatDateTime('nn:ss:zzz', StartExecTime-EndExecTime));
  end
  else if(Output=1)then
  begin
    //Only take first select
    cmds:=GetSQLMemoText;
    cmds:=DMDB.GetFirstSQLCmdFromScript(cmds);

    OutputQry.SQL.Text:=cmds;

    StartExecTime:=Now;
    try

      //OutputQry.Open;
      OutputClientDataSet.Open;

      EndExecTime:=Now;
    except
      on x: Exception do
      begin
        //After timeout, try again
        if(Pos('Lost connection to MySQL', x.Message)>0)or
          (Pos('Cannot perform this operation on a closed dataset', x.Message)>0)then
        begin
          OutputClientDataSet.Open;
          
          EndExecTime:=now;
        end
        else
        begin
          MessageDlg('ERROR while executing Query: '+#13#10#13#10+
            Trim(OutputQry.SQL.Text)+#13#10#13#10+
            'ERROR Message: '+#13#10+
            x.Message, mtError, [mbOk], 0);

          Exit;
        end;
      end;
    end;

    //OutputClientDataSet.Open;

    SizeGridCols;

    DMGUI.SetStatusCaption('Query opened. '+FormatFloat('##,###,##0', OutputClientDataSet.RecordCount)+' Record(s) fetched. Time: '+
      FormatDateTime('nn:ss:zzz', StartExecTime-EndExecTime));
  end;
end;



procedure TEditorQueryForm.GetClauses(s: string; Clauses: TStringList);
var i: integer;
  clause: string;
begin
  s:=DMMain.ReplaceText(s, #13#10, ' ');

  for i:=0 to Clauses.Count-1 do
  begin
    clause:=GetClause(s, Clauses.Names[i], Clauses);

    if(clause<>'')then
      Clauses.ValueFromIndex[i]:=clause;
  end;
end;

function TEditorQueryForm.GetClause(s, clause: string; Clauses: TStringList): string;
var i, p1, p2: integer;
  theClause: string;
begin
  theClause:='';

  //Get Start Pos of the clause
  p1:=Pos(clause, UpperCase(s));
  if(p1>0)then
  begin
    p1:=p1+Length(clause)+1;

    //check all possible following clauses for the end if the clause
    for i:=Clauses.IndexOfName(clause)+1 to Clauses.Count-1 do
    begin
      p2:=Pos(Clauses.Names[i], UpperCase(Copy(s, p1, Length(s))));
      if(p2>0)then
      begin
        theClause:=Trim(Copy(s, p1, p2-1));
        break;
      end;
    end;

    //if at end of line
    if(theClause='')then
      theClause:=Copy(s, p1, Length(s));
  end;

  GetClause:=theClause;
end;

procedure TEditorQueryForm.ProcessKey(Key: Word; col: TEERColumn);
var columnPos: integer;
  s: string;
begin
  columnPos:=-1;

  s:=Copy(GetSQLMemoText, 1, 8);

  //Select Command
  if(Copy(s, 1, 6)='SELECT')then
  begin
    if(Key=wtSQLSelect)then
      columnPos:=cpSelectClause
    else if(Key=wtSQLWhere)then
      columnPos:=cpWhereClause
    else if(Key=wtSQLGroup)then
      columnPos:=cpGroupClause
    else if(Key=wtSQLHaving)then
      columnPos:=cpHavingClause
    else if(Key=wtSQLOrder)then
      columnPos:=cpOrderClause;
  end
  //UPDATE Command
  else if(Copy(s, 1, 6)='UPDATE')then
  begin
    if(Key=wtSQLSet)then
      columnPos:=cpSetClause
    else if(Key=wtSQLWhere)then
      columnPos:=cpWhereClause;
  end
  //DELETE Command
  else if(Copy(s, 1, 6)='DELETE')then
  begin
    if(Key=wtSQLWhere)then
      columnPos:=cpDelWhereClause;
  end;

  if(columnPos>=0)then
    AddColumnToSQLCommand(columnPos, col);
end;


procedure TEditorQueryForm.UndoMIShow(Sender: TObject);
begin
{$IFDEF USE_SYNEDIT}
  if(DMGUI.UseSQLSyntaxHighlighting)then
    TMenuItem(Sender).Enabled:=SQLSynEdit.CanUndo
  else
{$ENDIF}
    TMenuItem(Sender).Enabled:=SQLMemo.CanUndo;
end;

procedure TEditorQueryForm.RedoMIShow(Sender: TObject);
begin
{$IFDEF USE_SYNEDIT}
  if(DMGUI.UseSQLSyntaxHighlighting)then
    TMenuItem(Sender).Enabled:=SQLSynEdit.CanRedo
  else
{$ENDIF}
    TMenuItem(Sender).Enabled:=SQLMemo.CanRedo;
end;

procedure TEditorQueryForm.UndoMIClick(Sender: TObject);
begin
{$IFDEF USE_SYNEDIT}
  if(DMGUI.UseSQLSyntaxHighlighting)then
    SQLSynEdit.Undo