Field Identity Exclude SQLGenerate

[leivio]

Hi,
Please information about builder SQL Generation in Runtime Mode where a clause filter exclude a field Identity.

I am try following code:

Code: Select all

var
  i: integer;
  KeyAndDataFields : TKeyAndDataFields;
  KeyLst: TStringList;
  TablesInfo: TCRTablesInfo;
  oldCaseSensitive: boolean;
  ID: TCRFieldDesc;
begin
  UniQuery1.SQL.Text := 'select * from tbteste where 1 = 2';
  UniQuery1.KeyFields := 'ID';
  UniQuery1.Open;

  SetLength(KeyAndDataFields.DataFieldDescs, UniQuery1.Fields.Count);

  for i := 0 to UniQuery1.Fields.Count - 1 do
  begin
    if not SameText(UniQuery1.Fields[i].FieldName, 'ID') then
    begin
      ID := (UniQuery1.GetFieldDesc(UniQuery1.Fields[i]) as TCRFieldDesc);
      KeyAndDataFields.DataFieldDescs[i] := ID;
    end
    else
      KeyAndDataFields.DataFieldDescs[i] := UniQuery1.GetFieldDesc(UniQuery1.Fields[i]) as TCRFieldDesc;
  end;

  KeyLst := TStringList.Create;
  try
    KeyLst.Delimiter := ';';
    KeyLst.DelimitedText := UniQuery1.KeyFields;
    SetLength(KeyAndDataFields.KeyFieldDescs, KeyLst.Count);
    for i := 0 to KeyLst.Count - 1 do
    begin
      KeyAndDataFields.KeyFieldDescs[i] := UniQuery1.GetFieldDesc(KeyLst[i]) as TCRFieldDesc;
    end;

    TablesInfo := TDBAccessUtils.GetTablesInfo(UniQuery1);
    oldCaseSensitive := TablesInfo.CaseSensitive;
    try
      TablesInfo.CaseSensitive := False;
      mmo.Lines.Clear;
      mmo.Lines.Add('GenerateSQL') ;                                    mmo.Lines.Add('');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQL(stInsert, False, nil));
      mmo.Lines.Add('UPDATE');  mmo.Lines.Add('');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stUpdate, False, nil));
      mmo.Lines.Add('INSERT');  mmo.Lines.Add('');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stInsert, False, nil));
      mmo.Lines.Add('DELETE');                    mmo.Lines.Add('');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stDelete, False, nil));
      mmo.Lines.Add('REFRESH') ;                                    mmo.Lines.Add('');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stRefresh, False, nil));


    finally
      TablesInfo.CaseSensitive := oldCaseSensitive;
    end;
  finally
    KeyLst.Free;
  end;

My expected SQL Code without "ID" Column.


Thank you for your help,

Leivio

[leivio]

I found one solution:

Code: Select all

var
  i: integer;
  KeyAndDataFields : TKeyAndDataFields;
  KeyLst: TStringList;
  TablesInfo: TCRTablesInfo;
  oldCaseSensitive: boolean;
  ID: TCRFieldDesc;
begin
  UniQuery1.SQL.Text := 'select id, nome, sobrenome from tbteste where 1 = 2';
  UniQuery1.KeyFields := 'ID';
  UniQuery1.Open;

  SetLength(KeyAndDataFields.DataFieldDescs, 2);

  KeyAndDataFields.DataFieldDescs[0] := UniQuery1.GetFieldDesc(UniQuery1.FieldByName('nome')) as TCRFieldDesc;
  KeyAndDataFields.DataFieldDescs[1] := UniQuery1.GetFieldDesc(UniQuery1.FieldByName('sobrenome')) as TCRFieldDesc;
  SetLength(KeyAndDataFields.KeyFieldDescs, 1);
  KeyAndDataFields.KeyFieldDescs[0] := UniQuery1.GetFieldDesc('ID') as TCRFieldDesc;

  TablesInfo := TDBAccessUtils.GetTablesInfo(UniQuery1);
  oldCaseSensitive := TablesInfo.CaseSensitive;
  try
    TablesInfo.CaseSensitive := False;
    mmo.Lines.Clear;
    mmo.Lines.Add('GenerateSQL') ;                                    mmo.Lines.Add('');
    mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQL(stInsert, False, nil));
    mmo.Lines.Add('UPDATE');  mmo.Lines.Add('');
    mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stUpdate, False, nil));
    mmo.Lines.Add('INSERT');  mmo.Lines.Add('');
    mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stInsert, False, nil));
    mmo.Lines.Add('DELETE');                    mmo.Lines.Add('');
    mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stDelete, False, nil));
    mmo.Lines.Add('REFRESH') ;                                    mmo.Lines.Add('');
    mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stRefresh, False, nil));


  finally
    TablesInfo.CaseSensitive := oldCaseSensitive;
  end;

[AndreyZ]

Hello,

To generate SQL statements without an IDENTITY field, you should exclude it from the DataFieldDescs array. Here is a code example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  i, k: integer;
  KeyAndDataFields : TKeyAndDataFields;
  KeyLst: TStringList;
  TablesInfo: TCRTablesInfo;
  oldCaseSensitive: boolean;
  ID: TCRFieldDesc;
begin
  UniQuery1.SQL.Text := 'select * from tbteste where 1 = 2';
  UniQuery1.KeyFields := 'ID';
  UniQuery1.Open;

  SetLength(KeyAndDataFields.DataFieldDescs, UniQuery1.Fields.Count);
  k := 0;
  for i := 0 to UniQuery1.Fields.Count - 1 do
    if not (UniQuery1.GetFieldDesc(UniQuery1.Fields[i]) as TCRFieldDesc).IsAutoIncrement then begin
      KeyAndDataFields.DataFieldDescs[k] := UniQuery1.GetFieldDesc(UniQuery1.Fields[i]) as TCRFieldDesc;
      inc(k);
    end;
  SetLength(KeyAndDataFields.DataFieldDescs, k);

  KeyLst := TStringList.Create;
  try
    KeyLst.Delimiter := ';';
    KeyLst.DelimitedText := UniQuery1.KeyFields;
    SetLength(KeyAndDataFields.KeyFieldDescs, KeyLst.Count);
    for i := 0 to KeyLst.Count - 1 do
      KeyAndDataFields.KeyFieldDescs[i] := UniQuery1.GetFieldDesc(KeyLst[i]) as TCRFieldDesc;

    TablesInfo := TDBAccessUtils.GetTablesInfo(UniQuery1);
    oldCaseSensitive := TablesInfo.CaseSensitive;
    try
      TablesInfo.CaseSensitive := False;
      mmo.Lines.Clear;
      mmo.Lines.Add('GenerateSQL');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQL(stInsert, False, nil));
      mmo.Lines.Add('');
      mmo.Lines.Add('UPDATE');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stUpdate, False, nil));
      mmo.Lines.Add('');
      mmo.Lines.Add('INSERT');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stInsert, False, nil));
      mmo.Lines.Add('');
      mmo.Lines.Add('DELETE');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stDelete, False, nil));
      mmo.Lines.Add('');
      mmo.Lines.Add('REFRESH');
      mmo.Lines.Add(TDBAccessUtils.SQLGenerator(UniQuery1).GenerateSQLforUpdTable(TablesInfo.FindByName('tbteste'), KeyAndDataFields, stRefresh, False, nil));
      mmo.Lines.Add('');
    finally
      TablesInfo.CaseSensitive := oldCaseSensitive;
    end;
  finally
    KeyLst.Free;
  end;
end;