How generate SQLUpdate statement at runtime

[malinsky]

Hello,

please, is possible auto generate default SQLUpdate statement of
UniQuery at runtime same as with using SQL Generator at design time?

Thanks for help
PMal

[AndreyZ]

Hello,

Here is a code example that demonstrates generating update SQL statements at run-time:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
var
  i: integer;
  KeyAndDataFields : TKeyAndDataFields;
  KeyLst: TStringList;
begin
  UniQuery.SQL.Text := 'select * from emp';
  UniQuery.KeyFields := 'empno;ename';
  UniQuery.Open;

  SetLength(KeyAndDataFields.DataFieldDescs, UniQuery.Fields.Count);
  for i := 0 to UniQuery.Fields.Count - 1 do
    KeyAndDataFields.DataFieldDescs[i] := UniQuery.GetFieldDesc(UniQuery.Fields[i]) as TCRFieldDesc;

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

    ShowMessage(TDBAccessUtils.SQLGenerator(UniQuery).GenerateSQLforUpdTable(TDBAccessUtils.GetTablesInfo(UniQuery).FindByName('emp'), KeyAndDataFields, stUpdate, False, nil));
  finally
    KeyLst.Free;
  end;
end;

Note that to run this code, you should add the CRAccess unit to the USES clause of your unit.

[malinsky]

Hello Andrey,

thanks for example, but don't work .

I create sample table:

CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10)
)

... on line ShowMessage() is raised exception

---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class $C0000005 with message 'access violation at 0x005cd3a9: read of address 0x0000000c'.
---------------------------
Break Continue Help
---------------------------

What I make is wrong ?

UniDac 4.1.6
Delphi XE2
DB Firebird 2.1
WinVista 32

Thanks for support!

[AndreyZ]

The problem occurs because the TCRTablesInfo.FindByName method uses case-sensitive searching. To solve the problem, you should use the following code:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
var
  i: integer;
  KeyAndDataFields : TKeyAndDataFields;
  KeyLst: TStringList;
  TablesInfo: TCRTablesInfo;
  oldCaseSensitive: boolean;
begin
  UniQuery.SQL.Text := 'select * from emp';
  UniQuery.KeyFields := 'empno;ename';
  UniQuery.Open;

  SetLength(KeyAndDataFields.DataFieldDescs, UniQuery.Fields.Count);
  for i := 0 to UniQuery.Fields.Count - 1 do
    KeyAndDataFields.DataFieldDescs[i] := UniQuery.GetFieldDesc(UniQuery.Fields[i]) as TCRFieldDesc;

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

    TablesInfo := TDBAccessUtils.GetTablesInfo(UniQuery);
    oldCaseSensitive := TablesInfo.CaseSensitive;
    try
      TablesInfo.CaseSensitive := False;
      ShowMessage(TDBAccessUtils.SQLGenerator(UniQuery).GenerateSQLforUpdTable(TablesInfo.FindByName('emp'), KeyAndDataFields, stUpdate, False, nil));
    finally
      TablesInfo.CaseSensitive := oldCaseSensitive;
    end;
  finally
    KeyLst.Free;
  end;
end;

[malinsky]

Hello Andrey,

thank you very much . Solved (case sensitivity...).

Thanks PMal

[AndreyZ]

It is good to see that this problem has been solved. If any other questions come up, please contact us.