Board index » delphi » LocalSQL + MemoryTable (RX) + SQL-UPDATE => doesn't work?

LocalSQL + MemoryTable (RX) + SQL-UPDATE => doesn't work?

I am trying to use a MemoryTable (of RX-Lib 2.40, Delphi 1) in order
to store the results of a computation for a statistic.
I would like to compute/store the results directly into the MemTable
(I need several passes to get the final results).

Unfortunately the SQL command Update doesn't work on MemTables - or
at least this seems to be the case. Can anyone confirm this?

Each time I try it I am getting a GPF in IDASCI01.DLL or the error
message 'query makes no sense' (german: 'kein sinnvolles Ergebnis fr
Abfrage m?glich').

Is there a way to get 'update' to work with MemTables?

Here are the details:

  var
    Query: TQuery;
    tblResult: TMemoryTable;

intialize/prepare tblResult:

  with tblResult do begin
    Close;
    with FieldDefs do begin
      Clear;
      Add('Indexx',ftInteger,0,true);
      Add('Bezeichnung',ftString,80,true);
      Add('Soll',ftFloat,0,true);
      Add('Ist',ftFloat,0,true);
      Add('Saldo',ftFloat,0,true);
    end;
    CreateTable;
    Fields[0].Visible:=false;
    Fields[1].DisplayWidth:=30; Fields[2].DisplayWidth:=8;
    Fields[3].DisplayWidth:=8; Fields[4].DisplayWidth:=8;
  end;

copy some starting records from another table (values soll/ist/saldo are
to calculate):

  with tblTemp2 do begin
    Close;
    TableName:='K_STELLE.DB';
    Open;
    First;
    while not EOF do begin
      tblResult.AppendRecord([FieldByName('Indexx').AsInteger,
                              FieldByName('Nummer').AsString+' '
                                      +FieldByName('Bezeichnung').AsString,
                              0,0,0]);
      Next;
    end;
  end;

Everything fine up to now but I'd like to add values to the Soll field in the
first step. And I want to use SQL-Update in order to take advantage of the
memory table). tblResult.TableName='result' and rechnungspreis is a field
of S_Arbeit.

  with Query do begin
    Close;
    SQL.Clear;
    SQL.Add('UPDATE result as r');
    SQL.Add('SET soll = soll + ');
    SQL.Add('(SELECT SUM(rechnungspreis) FROM s_arbeit WHERE r.indexx = kostenstelle)');
    ExecSQL;
  end;

This variant results in 'query makes no sense'. When I simplify the query ...

  with Query do begin
    Close;
    SQL.Clear;
    SQL.Add('UPDATE result');
    SQL.Add('SET soll = soll + 5');
    ExecSQL;
  end;

... I am getting a GPF in IDASCI01.DLL.

(1) are there errors in my SQL statement?
(2) does Update work on MemTables at all?

Werner

 

Re:LocalSQL + MemoryTable (RX) + SQL-UPDATE => doesn't work?


MemTables can't have Indices. Therefore you only can use First, Next,
Previous,
and Last. No chance for SELECT, subselect etc.

This is a BDE-Problem, not a problem of RxControls!

Sigi

Werner Lehmann schrieb in Nachricht <35BBCAA9.CF128...@bwl.uni-kiel.de>...

Quote
>I am trying to use a MemoryTable (of RX-Lib 2.40, Delphi 1) in order
>to store the results of a computation for a statistic.
>I would like to compute/store the results directly into the MemTable
>(I need several passes to get the final results).
...[cut]

>Werner

Re:LocalSQL + MemoryTable (RX) + SQL-UPDATE => doesn't work?


Quote
Siegfried Bauer wrote:

> MemTables can't have Indices. Therefore you only can use First, Next,
> Previous,
> and Last. No chance for SELECT, subselect etc.

> This is a BDE-Problem, not a problem of RxControls!

Thank you for clearing this point. Although I thought that SELECT
would only be slower without any available indices.

I am using dynamically created temp-tables now. That doesn't seem to be
slower but a MemTable would be the 'cleaner' approach.

Werner

PS: What about 'UPDATE x SET y = (SELECT z FROM x2 WHERE x2.y = x.y)'?
Any chance to get this to work in LocalSQL? (I mean an UPDATE with nested
SELECT)

Other Threads