Devart Forums

Posted: **Tue 10 Apr 2012 07:36**

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

Posted: **Wed 11 Apr 2012 06:58**

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.

Posted: **Wed 11 Apr 2012 08:06**

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!

Posted: **Wed 11 Apr 2012 08:46**

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;

Posted: **Wed 11 Apr 2012 09:03**

Hello Andrey,

thank you very much

. Solved (case sensitivity...).

Thanks PMal

Posted: **Wed 11 Apr 2012 09:12**

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

Devart Forums

How generate SQLUpdate statement at runtime

How generate SQLUpdate statement at runtime