sdupdsed.pas

SQLDirect Component Library is a light-weight Borland Database Engine replacement for Borland Delphi

procedure TSDUpdateSQLEditForm.GenerateInsertSQL;
const
  InsertClause	= 'insert into ';
var
  sFields, sParams, sField, sParam, sTableName: string;
  i: Integer;
begin
  FInsertSQL.Clear;

  sTableName := cbTableName.Text;
  FInsertSQL.Add( InsertClause + sTableName );
	// collect fields and parameters (update fields)
  with lbUpdateFields do
    for i:=0 to Items.Count-1 do
      if Selected[i] then begin
        sField := Items[i];
        sParam := FindFieldAlias( sTableName, sField );

        if sFields <> '' then
          sFields := sFields + ', ';
        sFields := sFields + QuoteName(sField);

        if sParams <> '' then
          sParams := sParams + ', ';
        sParams := sParams + ':' + QuoteName(sParam);
      end;
  FInsertSQL.Add( '  (' + sFields + ')' );
  FInsertSQL.Add( 'values' );
  FInsertSQL.Add( '  (' + sParams + ')' );
end;

procedure TSDUpdateSQLEditForm.GenerateDeleteSQL;
const
  DeleteClause	= 'delete from ';
  WhereClause	= 'where';
  AndOp		= ' and';
  SetIndent	= '  ';
var
  sField, sStr, sTableName: string;
  i: Integer;
begin
  FDeleteSQL.Clear;
	// it is possible to remove a table alias, if it's present. For example, MSSQL do not support a table alias for DELETE ???
  sTableName := cbTableName.Text;
  FDeleteSQL.Add( DeleteClause + sTableName );

	// add WHERE clause (key fields)
  FDeleteSQL.Add( WhereClause );
  with lbKeyFields do
    for i:=0 to Items.Count-1 do
      if Selected[i] then begin
        sField := lbKeyFields.Items[i];
        sStr := Format( '%s%s = :%s',
        	[SetIndent, QuoteName(sField), QuoteName('OLD_' + FindFieldAlias(sTableName, sField))] );

        if FDeleteSQL.Strings[FDeleteSQL.Count-1] <> WhereClause then
          FDeleteSQL.Strings[FDeleteSQL.Count-1] := FDeleteSQL.Strings[FDeleteSQL.Count-1] + AndOp;
        FDeleteSQL.Add( sStr );
      end;
end;

procedure TSDUpdateSQLEditForm.FormCreate(Sender: TObject);
begin
  FRefreshSQL	:= TStringList.Create;
  FModifySQL	:= TStringList.Create;
  FInsertSQL	:= TStringList.Create;
  FDeleteSQL	:= TStringList.Create;

  FFieldInfo	:= TStringList.Create;
  FTableInfo	:= TStringList.Create;

  FUpdateSQL	:= nil;

  pcUpdateSQL.ActivePage := tsOptions;
end;

procedure TSDUpdateSQLEditForm.FormDestroy(Sender: TObject);
begin
  FFieldInfo.Free;
  FTableInfo.Free;

  FRefreshSQL.Free;  
  FModifySQL.Free;
  FInsertSQL.Free;
  FDeleteSQL.Free;
end;

procedure TSDUpdateSQLEditForm.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if ModalResult <> mrOk then begin
    CanClose := True;
    Exit;
  end;
  CanClose := False;
  if Length(Trim( FModifySQL.Text )) > 0 then
    CanClose := True
  else
    if MessageDlg( SSQLNotGenerated, mtConfirmation, [mbYes, mbNo, mbCancel], 0 ) = mrYes then
      CanClose := True;
end;

procedure TSDUpdateSQLEditForm.cbTableNameChange(Sender: TObject);
var
  EmptyTableName: Boolean;
begin
  EmptyTableName := Length(Trim( cbTableName.Text )) = 0;

  btGetTableFields.Enabled	:= not EmptyTableName;
  btSelectPrimKeys.Enabled      := not EmptyTableName;
  btGenerateSQL.Enabled		:= not EmptyTableName;
end;

procedure TSDUpdateSQLEditForm.TableNameChanged(Sender: TObject);
begin
  cbTableNameChange(Sender);

  if btGetTableFields.Enabled then
    GetTableFieldsClick( Self );
end;

procedure TSDUpdateSQLEditForm.GetTableFieldsClick(Sender: TObject);
  	{ extract only a table name (without owner) }
  function RemoveOwnerName(const ATableName: string): string;
  var
    i: Integer;
  begin
    Result := '';
    for i:=Length(ATableName) downto 1 do begin
      if (ATableName[i] = '.') and (i < Length(ATableName)) then begin
        Result := Copy( ATableName, i+1, Length(ATableName)-i );
        Break;
      end;
    end;
        // if the table name does not contain an owner name
    if Result = '' then
      Result := ATableName;
  end;

var
  List: TStrings;
  i, CurLen, MaxLen1, MaxLen2: Integer;
  sTableName, s: string;
begin
  ASSERT( FUpdateSQL.DataSet <> nil );
	// sTableName can contain a name of the Owner
  sTableName := cbTableName.Text;
  	// extract a table name without quote characters
  i := 1;
  while i <= Length(sTableName) do begin
    if AnsiChar(sTableName[i]) in ['"', ''''] then
      Delete( sTableName, i, 1 );
    Inc( i );
  end;

  List := TStringList.Create;
  try
  	// first, check a table with owner name to exclude duplicated fields in case some tables with one name
    FUpdateSQL.DataSet.DBSession.GetFieldNames(
    		FUpdateSQL.DataSet.DatabaseName,
                sTableName, List );
    	// if nothing is returned, check the table without owner name
    if List.Count = 0 then begin
      s := RemoveOwnerName(sTableName);
      if s <> sTableName then
        FUpdateSQL.DataSet.DBSession.GetFieldNames(
    		FUpdateSQL.DataSet.DatabaseName,
                s, List );
    end;
    if List.Count > 0 then begin
      lbKeyFields.Items.Clear;
      lbUpdateFields.Items.Clear;

      MaxLen1 := 0; MaxLen2 := 0;
      for i:=0 to List.Count-1 do
        if Length( List.Strings[i] ) > 0 then begin
          lbKeyFields.Items.Add( List.Strings[i] );
          lbUpdateFields.Items.Add( List.Strings[i] );

          CurLen := lbKeyFields.Canvas.TextWidth(List.Strings[i] + 'A');
          if CurLen > MaxLen1 then MaxLen1 := CurLen;
          CurLen := lbUpdateFields.Canvas.TextWidth(List.Strings[i] + 'A');
          if CurLen > MaxLen2 then MaxLen2 := CurLen;
        end;
      SendMessage(lbKeyFields.Handle, LB_SETHORIZONTALEXTENT, MaxLen1, 0);
      SendMessage(lbUpdateFields.Handle, LB_SETHORIZONTALEXTENT, MaxLen2, 0);
    end else
      MessageDlg( Format(SSQLDataSetOpen, [sTableName]), mtError, [mbOk], 0 );

    SelectAllFields;
    btDataSetDefaults.Enabled := True;
  finally
    List.Free;
  end;
end;

procedure TSDUpdateSQLEditForm.DataSetDefaultsClick(Sender: TObject);
var
  i: Integer;
begin
  lbKeyFields.Clear;
  lbUpdateFields.Clear;

  for i:=0 to FFieldInfo.Count-1 do
    if Length( FFieldInfo.Names[i] ) > 0 then begin
      lbKeyFields.Items.Add( FFieldInfo.Names[i] );
      lbUpdateFields.Items.Add( FFieldInfo.Names[i] );
    end;

  SelectAllFields;
  btDataSetDefaults.Enabled := False;  
end;

procedure TSDUpdateSQLEditForm.GenerateSQLClick(Sender: TObject);
begin
  if (lbKeyFields.SelCount = 0) or (lbUpdateFields.SelCount = 0) then begin
    MessageDlg( SSQLGenSelect, mtError, [mbOk], 0 );
    Exit;
  end;

  GenerateModifySQL;
  GenerateInsertSQL;
  GenerateDeleteSQL;
  GenerateRefreshSQL;  

  StatementTypeClick( Self );  
end;


procedure TSDUpdateSQLEditForm.SelectAllClick(Sender: TObject);
begin
  if pmFields.PopupComponent = lbKeyFields then
    SetSelectedAllItems( lbKeyFields, True )
  else if pmFields.PopupComponent = lbUpdateFields then
    SetSelectedAllItems( lbUpdateFields, True )
end;

procedure TSDUpdateSQLEditForm.ClearAllClick(Sender: TObject);
begin
  if pmFields.PopupComponent = lbKeyFields then
    SetSelectedAllItems( lbKeyFields, False )
  else if pmFields.PopupComponent = lbUpdateFields then
    SetSelectedAllItems( lbUpdateFields, False )
end;

procedure TSDUpdateSQLEditForm.StatementTypeClick(Sender: TObject);
begin
  case rgrStatementType.ItemIndex of
    0: meSQLText.Lines.Assign( FRefreshSQL );
    1: meSQLText.Lines.Assign( FModifySQL );
    2: meSQLText.Lines.Assign( FInsertSQL );
    3: meSQLText.Lines.Assign( FDeleteSQL );
  end;
end;

procedure TSDUpdateSQLEditForm.SQLTextExit(Sender: TObject);
begin
  case rgrStatementType.ItemIndex of
    0: FRefreshSQL.Assign( meSQLText.Lines );
    1: FModifySQL.Assign( meSQLText.Lines );
    2: FInsertSQL.Assign( meSQLText.Lines );
    3: FDeleteSQL.Assign( meSQLText.Lines );
  end;
end;

function TSDUpdateSQLEditForm.QuoteName(const AIdentName: string): string;
begin
  Result := AIdentName;
  if cbQuotedFields.Checked then
    Result := '"' + Result + '"';
end;

procedure TSDUpdateSQLEditForm.btSelectPrimKeysClick(Sender: TObject);
var
  ids: TDataSet;
  db: TSDDatabase;
  PrimFields, UIdxFields: TStrings;
  i, IdxType: Integer;
  sFld, sUniqueIndex, sCurIdxName: string;
begin
  db := FUpdateSQL.DataSet.DBSession.FindDatabase( FUpdateSQL.DataSet.DatabaseName );
  ids := db.GetSchemaInfo(stIndexes, cbTableName.Text);
  if not Assigned(ids) then
    Exit;
  sUniqueIndex := '';
  PrimFields   := TStringList.Create;
  UIdxFields   := TStringList.Create;
  try
    while not ids.EOF do begin
      if not ids.FieldByName(IDX_TYPE_FIELD).IsNull then begin
        IdxType := ids.FieldByName(IDX_TYPE_FIELD).AsInteger;
        if (IdxType and PrimaryIndexType) <> 0 then begin
          sFld := ids.FieldByName(IDX_COL_NAME_FIELD).AsString;
          PrimFields.Add(sFld);
        end;
        if (IdxType and UniqueIndexType) <> 0 then begin
          sCurIdxName := ids.FieldByName(IDX_NAME_FIELD).AsString;
                // mark the first unique index
          if sUniqueIndex = '' then
            sUniqueIndex := sCurIdxName;
          if sUniqueIndex = sCurIdxName then
            UIdxFields.Add( ids.FieldByName(IDX_COL_NAME_FIELD).AsString );
        end;
      end;
      ids.Next;
    end;
    for i:=0 to lbKeyFields.Items.Count-1 do
      lbKeyFields.Selected[i] := False;
    if PrimFields.Count > 0 then
      for i:=0 to lbKeyFields.Items.Count-1 do
        lbKeyFields.Selected[i] := PrimFields.IndexOf(lbKeyFields.Items[i]) >= 0
    else if UIdxFields.Count > 0 then
      for i:=0 to lbKeyFields.Items.Count-1 do
        lbKeyFields.Selected[i] := UIdxFields.IndexOf(lbKeyFields.Items[i]) >= 0;

  finally
    UIdxFields.Free;
    PrimFields.Free;
    ids.Free;
  end;
end;


end.