Hi,

next problem

In a Server Application, I have a dataset "MapTrack" with two commands:

- Select:
SELECT * FROM MapTrack;

- Update:
UPDATE "MapTrack" SET
"ID" = :ID,
"SourceID" = :SourceID,
"Track" = :Track
WHERE "ID" = :Old_ID;

The table definition:

CREATE TABLE IF NOT EXISTS "MapTrack"
(
  "ID" GUID,
  "SourceID" GUID NOT NULL,
  "Track" MEMO,
PRIMARY KEY ("ID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

So, it's the generated stuff. Then I try

   dsMapTrack.Open;
   try
     Database.LoadRows(dsMapTrack);
     if dsMapTrack.RowCount >0 then begin
       dsMapTrack.First;
       dsMapTrack.Update;
       dsMapTrack.Columns['Track'].AsString := 'test';
       dsMapTrack.Save;
     end;
   except
     on e: exception do begin
       aRequest.SendError(HTTP_BAD_REQUEST, e.message);
     exit;
     end;
   end;
   Request.SendContent('All good, ID is ' + dsMapTrack.Columns['Track'].AsString);


I get no exception, error or anything else. But what I also don't get is the "test" string in the field "Track". The table has one record, and RowCount is larger than 0. I have put a Request.SendContent('', HTTP_OK, IntToStr(dsMapTrack.RowCount)); directly after the LoadRows and .Save to be sure.

It's the last thing I need to implement the last feature. But for that, I have to store some data.

Oh, and if anyone knows a way to define a command on the server side and call it with parameters from the client, I'd be thankful I would not need the server application if something like that would be possible.

Forgot to mention that

- Database.AutoTransactions is set to True

- DataSet.IncludeInTransaction is set to True

Setting Autotransaction to False and wrapping Update to Save in an explicit transaction does not help.

On 7/21/2022 5:52 PM, Ralf Mimoun wrote:
> In a Server Application, I have a dataset "MapTrack" with two commands:
....
>
> So, it's the generated stuff. Then I try
>
>      dsMapTrack.Open;
>      try
>        Database.LoadRows(dsMapTrack);
>        if dsMapTrack.RowCount >0 then begin
>          dsMapTrack.First;
>          dsMapTrack.Update;
>          dsMapTrack.Columns['Track'].AsString := 'test';
>          dsMapTrack.Save;
>        end;
>      except
>        on e: exception do begin
>          aRequest.SendError(HTTP_BAD_REQUEST, e.message);
>        exit;
>        end;
>      end;
>      Request.SendContent('All good, ID is ' + dsMapTrack.Columns['Track'].AsString);
>
>
> I get no exception, error or anything else. But what I also don't get is the "test" string in the field "Track". The table has one record, and RowCount is larger than 0. I have put a Request.SendContent('', HTTP_OK, IntToStr(dsMapTrack.RowCount)); directly after the LoadRows and .Save to be sure.
>
> It's the last thing I need to implement the last feature. But for that, I have to store some data.

AFAIK loadrows is async so you need to wait for server response -
dataset AfterLoad event - and then can modify data there (this code
would always have rowcount=0 since it's not loaded yet).


>
> Oh, and if anyone knows a way to define a command on the server side and call it with parameters from the client, I'd be thankful I would not need the server application if something like that would be possible.
>

Can you expand a bit on this  - assuming you're on EWB3 server project
might be easiest

Raul

Raul wrote:

> AFAIK loadrows is async so you need to wait for server response -
> dataset AfterLoad event - and then can modify data there (this code
> would always have rowcount=0 since it's not loaded yet).

That's true - for client applications. In server applications, LoadRows loads the data immediately. See https://www.elevatesoft.com/manual?action=viewtopic&id=ewb3&topic=Creating_Loading_DataSets, "TDatabase LoadRows Method".

>> Oh, and if anyone knows a way to define a command on the server side and call it with parameters from the client, I'd be thankful I would not need the server application if something like that would be possible.
>>

> Can you expand a bit on this  - assuming you're on EWB3 server project
> might be easiest

We define commands named "Select", "Update", "Insert" etc do tell the EWB server where it can find the SQL statements when the client asks for data or call for an action (update, insert...). It would be very handy to define a command fot a dataset on the server side, like "SwitchToUppercase", with a sql statement like "UPDATE Memos SET Content=UPPER(Content) WHERE MemoType=:Type" (just a stupid example). Then some client side code like.

DataSet.Params.Values['Type'] := 'Shouted';
DataSet.ExecuteCommand('SwitchToUpperCase');

will execute this command. The client has no direct access to the database, no way for SQL injections etc. It is exactly as risky as what we can do right now. But we could circumvent some limitations we have now. I was able to work without a server application without adding data leakage, but the SQL stuff I had to write for Select and Update is a little bit freaky.

I have no idea how much work that would mean. It _sounds_ simple.