mORMot Open Source

ivanilsonpacheco

Member

Registered: 2017-06-20

Posts: 3

Update part of a table | Execute Pure SQL

Hello AB, congratulations for your great work!

I have two questions:

1 - Is it possible to update only part of a table?
Ex: I have a product table, when there is a buying process I need to update only stock quantity, costs...
I know I can use Client.UpdateField to update only one field, but I think this is not the correct way to do that

2 - Can I execute pure SQL commands "overriding" the ORM?

PS: I'm using Client/Server http.

Forgive me if I typed something wrong, I'm not fluent in English.

JD

Member

Registered: 2015-08-20

Posts: 118

Re: Update part of a table | Execute Pure SQL

Hi there,

I can answer part 2 because I just got it to work for me. The small server-side method below inserts a new record in the country table. Note that fDbProps is of type TSQLDBConnectionProperties so you can use its Execute or ExecuteNoResult among other to execute pure SQL commands

function TRESTMethods.Country(aSchema: string; const aID: string; aDTO: RawJSON): RawJSON;
var
  Res: ISQLDBRows;
  aObj: TSQLCountry;
begin
    aObj := TSQLCountry.Create;
    try
      ObjectLoadJSON(aObj, aDTO);      // Load JSON into object
      Res := aServer.fDbProps.Execute(Format('INSERT INTO %s.country (name) VALUES (?) RETURNING country_id', [aSchema]), [aObj.Name]);
      while Res.Step do
        Result := Format('{"ID": "%s"}', [VariantSaveJSON(Res['country_id'], twNone)]);
    finally
      aObj.Free;
    end;
end;

See https://stackoverflow.com/a/22078557/458259 for an example of how to call it with HTTP post

Hope it helps,

JD

Last edited by JD (2017-10-17 15:55:04)

ivanilsonpacheco

Member

Registered: 2017-06-20

Posts: 3

Re: Update part of a table | Execute Pure SQL

Hello JD, thanks for your help, I will take a look at your example. In this meantime, I just got another way to do that directly in the database.

Using TSQLDBConnectionProperties and TQuery.