Problem with UPDATE query and EngineExecute

array81 · 2011-11-24 08:33:52

Hi,

On my application I use this code:

var
  ProgettoFatturaRecord: TSQLProjectsInvoices;
  ErrorDB: Boolean;
  i, IDFat: Integer;
begin
  ErrorDB := False;

  Screen.Cursor := crHourGlass;

  ProgettoFatturaRecord := TSQLProjectsInvoices.Create;

  try
    ProgettoFatturaRecord.IDProject := EditItem;

    ProgettoFatturaRecord.IDUser := IDUtenti.Counter[IDUtenti.IndexOf(insItemUserUser.AsString)];

    if insItemRecipientMode.ItemIndex = 0 then
      ProgettoFatturaRecord.IDCustomer := 0
    else
      ProgettoFatturaRecord.IDCustomer := IDClienti.Counter[IDClienti.IndexOf(insItemRecipientCustomer.AsString)];

    ProgettoFatturaRecord.Title := StringToUTF8(insItemGeneralTitleCustom.AsString);
    ProgettoFatturaRecord.Kind := insItemGeneralType.ItemIndex;
    ProgettoFatturaRecord.Date := insItemGeneralDate.AsDateTime;
    ProgettoFatturaRecord.Template := insItemUserTemplate.AsString;

    ProgettoFatturaRecord.Numer := StringToUTF8(insItemGeneralNumber.AsString);

    ProgettoFatturaRecord.TotalWithdrawals := StringToUTF8(FatturaCampi[5]);
    ProgettoFatturaRecord.TotalDeposits := StringToUTF8(FatturaCampi[4]);

    IDFat := Database.Add(ProgettoFatturaRecord, true);

    if IDFat = 0 then
      begin
        ErrorDB := True;
      end;

  finally
    ProgettoFatturaRecord.Free;
  end;

  if not ErrorDB then
    begin
      for i := 0 to grdValues.RowCount - 1 do
        begin
          if grdValues.Cell[1, i].AsBoolean then
            begin
              Database.TransactionBegin(TSQLProjectsFlows);
              try
                if not Database.EngineExecute('UPDATE ProjectsFlows SET IDInvoice="' + IntToStr(IDFat) + '" WHERE ID="' + IntToStr(grdValues.Cell[0, i].AsInteger) + '"') then
                  begin
                    ErrorDB := True;
                  end;

                Database.Commit;
              except
                Database.RollBack;
              end;
            end;
        end;
    end;

  // Esci in caso di errori
  if ErrorDB then
    begin
      Screen.Cursor := crDefault;

      // SHOW ERROR MESSAGE

      Exit;
    end;

I know it's not optimize code (for example for "i" cicle) but in this moment this is not the problem I need only test it before optimeze the code.

If I use it on my "server application" (I can use my application as standalone or client/server), or standalone application it works without problem.
If I use this on my "client application" I have a problem: the first part is ok but the second part (Database.EngineExecute) get me always FALSE result, I just have check the SQL and it's ok (I have test it with your editor demo).

Note the grdValues.RowCount value is 1 on my case, only sometimes it can be 2/3.

I have the problem both on my office LAN (all windows xp) and home lan (all windows 7)?

Do you have same idea?

ab · 2011-11-24 11:02:13

By default, the client security is enhanced - comparing to the server side execution.

You'll have to explicitly set the TSQLAccessRights.AllowRemoteExecute boolean to true for all corresponding users.

array81 · 2011-11-24 12:20:37

OK, but I don't use your native "user access system" because when I have start to use my application your framework didn't have it.
I use this code to connect my client to the server:

Database := TSQLite3HttpClient.Create(Server, IntToStr(Port), Model);

and this is the code of the server

    DatabaseServer := TSQLRestClientDB(Database).Server;
    DatabaseServer.CreateMissingTables(0);
    DatabaseServer.OnUpdateEvent := DatabaseAggiornatoEvento;
    Server := TSQLite3HttpServer.Create(IntToStr(Port), DatabaseServer);

Is there a way to set AllowRemoteExecute for all the client? then I will use my "user access system" to block my user when I need of it.

In alternative, in fact, I think I need rewrite many of my code.

Last edited by array81 (2011-11-24 14:44:50)

ab · 2011-11-24 15:58:54

If you do not use the authentication feature, it will use the SUPERVISOR_ACCESS_RIGHTS values.

You can force

 SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute := true;

in your code to allow this remote execution.

Constant shall be able to be modified - with default compilation settings.

array81 · 2011-11-24 21:09:00

ab wrote:

If you do not use the authentication feature, it will use the SUPERVISOR_ACCESS_RIGHTS values.
You can force
 SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute := true; 
in your code to allow this remote execution.
Constant shall be able to be modified - with default compilation settings.

SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute is a constant so I cannot add on my code (for example on create event of my main form) the code:

 SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute := true;

In alternative I get an error: "E2064 Left side cannot be assigned to"... Must I change it on your framework code? I hope no...

NOTE: I just have try to use "Assignable typed constant" on project option on my Delphi 2006.

Last edited by array81 (2011-11-24 22:00:11)

array81 · 2011-11-24 22:27:44

Ok I think I have found a solution:

I have put this line:

{$J+} {Assignable Typed Constants}

as first line of SQLite3Commons.pas file, now it works however can you make a small change on your framework to avoid the source edit by me or other user with my problem?

ab · 2011-11-25 06:32:22

As I wrote above:

Constant shall be able to be modified - with default compilation settings.

You can set this at project level, with the Project / Options window of the Delphi IDE.

Another possibility would be to change TSQLite3HttpServer.Request in SQLite3HttpServer.pas and replace @SUPERVISOR_ACCESS_RIGHTS by @FULL_ACCESS_RIGHTS

But it would not cost much (just some lines to add at client and server initialization) to handle authentication in your existing program.
And specify the user rights (even if you use only one user).

ab · 2011-11-28 14:50:15

I've added a parameter to TSQLite3HttpServer.AddServer method, and a corresponding property to set a custom access right.
See http://synopse.info/fossil/info/ff648168de

The main TSQLRest.URI method still expect the access rights to be part of the parameters.
As stated by the documentation, this ensure that this method is thread-safe and re-entrant.

In all cases, you shall better rely on the new authentication mechanism, which is light and secure, and will ensure e.g. that transactions are properly working (whereas it may not be the case otherwise with concurrent client access).

array81 · 2012-01-30 22:38:44

OK, I decided to implement an authentication system. can you give me some advice?
I already read the section "1.4.8.4. Framework authentication" of the documentation, however it does not seem very simple and there is not a demo about it.

ab · 2012-01-31 11:01:24

Why do you want to implement your own authentication system?

You stated above in this forum thread that the authentication system was not existing when you started your application.
OK. But this is very easy to add the authentication. Just new TSQLRestClientURI.SetUser() method to be called on the client, and aHandleUserAuthentication parameter to set to true at TSQLRestServer.Create constructor call.

For instance, the "04 - HTTP Client-Server" demos does use authentication. Check the source code of Project04Client.dpr and Unit2.pas:

program Project04Client;
 ....
  Form1.Database := TSQLite3HttpClient.Create(Server,'8080',Form1.Model);
  TSQLite3HttpClient(Form1.Database).SetUser('User','synopse'); // CLIENT side authentication
  Application.Run;
end.

unit Unit2;
  ....
procedure TForm1.FormCreate(Sender: TObject);
begin
  Model := CreateSampleModel;
  DB := TSQLRestServerDB.Create(Model,ChangeFileExt(paramstr(0),'.db3'),true); // ,TRUE for SERVER side authentication
  DB.CreateMissingTables(0);
  Server := TSQLite3HttpServer.Create('8080',[DB]);
end;

This will handled authentication at the communication layer level.
You can handle your own authentication mechanism at application layer level, if you want.
But in this case, you won't have to change the framework code, just adapt your application code for this (defining TSQLRecord sub classes for users, and such). You can override TSQLRest.RecordCanBeUpdated method to implement your own right management, if you need to.

array81 · 2012-01-31 12:57:00

I have wrote "implement" I meant "add" the officail authentication framework system to my application.

In this moment it works in this way:
1) The client connect to database;
2) The client check if there is a user with username and password inside the database;
3) If there IS NOT the user, the client close the database;
4) If there IS the user, the client retrieve user permission from database.

Do you think I might have benefits if I change it with "official" authentication system?

ab · 2012-01-31 13:43:04

This is what the "official" authentication system allows, but with a per-URI process, so it is really secured. Every remote access is a validated by the authentication system.

I just do not understand what you call a "client". Only the server side shall connect to the database.
I think you check the user at application startup, and close the client connection if the password does not match.

This is a basic scheme, which will work of course.
But it won't prevent any third-party application to access the server.

array81 · 2012-02-15 14:21:38

Hi,

after your last update my tricks about {$J+} don't work anymore, so I'm study your authentication system to try merge it with my current authentication system.
The problem is always the SQL, my client infact need call some SQL, for example UPDATE, but they cannot do it because don't have the permission.
After read the documentation I have make some test on demo numer 4 (client-server). I have add a button with this code:

Database.ExecuteList([],'UPDATE SampleRecord SET Question="' + UTF8ToString(QuestionMemo.Text) + '" WHERE Name="' + StringToUTF8(NameEdit.Text) + '"');

So I have created a new record and then I have try to update it. The record is not update. I think because the client use this code:

TSQLite3HttpClient(Form1.Database).SetUser('User','synopse');

then I have change the code with it:

TSQLite3HttpClient(Form1.Database).SetUser('Admin','synopse');

and I have retry the update my record. The record is not update.

The questions:
1) Why my test not work?
2) If I understand to add your authentication system I need of AuthGroup and AuthUser on my DB and the client can access with 4 different type of account Admin, SuperVision, User and Guest, is it right?
3) If point 2) it's corrent which type of account need my clients to call UPDATE SQL?
4) If point 2) it's corrent, after this I need only call SetUser function on client, that's all, right?
5) If I need but only if I need I can overwrite RecordCanBeUpdated to controls/limits the database change, right?

Thanks

ab · 2012-02-15 15:05:22

1) You should not use ExecuteList but EngineExecute;

2) You can add whatever kind of access you want: just add/update AuthGroup and AuthUser objects;

3) See the doc and the definition of TSQLAccessRights.PUT;

4) Yes

5) RecordCanBeUpdated is about ORM / CRUD functions, not direct EngineExecute SQL update.

In all cases, EngineExecute() is not a good idea.
I'm sure you can do this with ORM methods instead.

array81 · 2012-02-15 15:32:33

Thanks for your reply.

1) Yes I know but Database: TSQLRest (object in the demo 4) don't have EngineExecute, for this reason I have used ExecuteList;
2) OK but for defualt there is a account that allow UPDATE SQL?
3) I will check it;
5) OK but if I need UPDATE same values of my record with the condition WHERE, what is the ORM methods that can help me?

ab · 2012-02-15 16:59:31

1) transtype (TSQLRest as TSQLRestClientURI)

2) Admin has full rights, but you can't make an update when using EngineList - by definition, and for security

5) OK it is a bit difficult, but I see at least one possibility (with the trunk 1.16 version): retrieve the list of records with the supplied fields using CreateAndFillPrepare() including aCustomFieldsCSV, then update via a BatchUpdate() - only the aCustomFieldsCSV fields will be updated

array81 · 2012-02-15 17:31:05

The point 5) it's a bit hard but I will try it, in this case I need always of Admin account? I'd like not use it.

ab · 2012-02-15 19:01:58

About point 5 what you can do is just create a service for this particular request.

This is much easier, since it will allow to make the update on the server side, in plain SQL, without touching the authentication scheme.

array81 · 2012-02-15 22:41:49

"A service", can you get me some information?

A small example is welcome.

Last edited by array81 (2012-02-15 23:57:58)

ab · 2012-02-16 07:48:43

RTFM - see "SOA" and "Service" within the SAD documentation.

For a sample, see http://synopse.info/fossil/dir?name=SQL … ST+Service

It is just a method defined on the server, which is called from the client with parameters.
On the server side, you can do whatever process you want, with plain quick DB access and such.

array81 · 2012-02-16 08:37:38

So I need:
1) create a service server and use some parameters as (for example) TABLE, CONDITION and NEW SET VALUE;
2) call the service by client;
3) in this way the server application run the service and update the server, right?

In this case the client need of some permission? I need of a special account (Admin, SuperVision, User and Guest) to call the service?
Inside the service, can I use the EngineExecute to update the database? or there is another way?

ab · 2012-02-16 08:44:03

This is right.

The service is available to all users by default.
You have access to the current session ID at the service implementation side.
Then, if you need to, you can check the session User level, and expect some additional account.

Inside the service, you can do whatever you want: you have even direct access to the DB layer if you need to, since the service it implemented at the TSQLRestServer final level (it is just a published method on your own derived class).

I guess this is the best implementation possible for your case.
The documentation stated about this use of services, when ORM level fails.

array81 · 2012-02-16 14:05:02

A small problem, on my server application I use this code:

  TServiceServer = class(TSQLRestClientDB)
  published
    function UpdateRecordsValue(var aParams: TSQLRestServerCallBackParams): Integer;
  end;

Database := TServiceServer.Create(Model, nil, Filename, TSQLRestServerDB);
DatabaseServer := TServiceServer(Database).Server;
DatabaseServer.CreateMissingTables(0);
DatabaseServer.OnUpdateEvent := DatabaseAggiornatoEvento;
Server := TSQLite3HttpServer.Create(IntToStr(Port), DatabaseServer);

function TServiceServer.UpdateRecordsValue(var aParams: TSQLRestServerCallBackParams): Integer;
begin
...
...
end;

but TSQLRestClientDB don't have JSONEncodeResult function...

Another think I have found some functions to decode the url (ex UrlDecodeInteger, UrlDecodeCardinal, ...) but how can i extract a string from a parameter?

Last edited by array81 (2012-02-16 14:56:09)

ab · 2012-02-16 17:08:56

Services must be methods of a TSQLRestServer class, not a TSQLRestClient class.

You'll have to create your own TSQLRestServerDB child including the new published method, then use a TSQLRestClientDB instance and specify your own class to the constructor.

array81 · 2012-02-17 21:44:20

I tried to write the code but I have not been successful, I do not understand how to integrate the service in my code.
My code is working correctly and the following:

Database := TSQLRestClientDB.Create(Model, nil, Filename, TSQLRestServerDB);
DatabaseServer := TSQLRestClientDB(Database).Server;
DatabaseServer.CreateMissingTables(0);
DatabaseServer.OnUpdateEvent := DatabaseAggiornatoEvento;
Server := TSQLite3HttpServer.Create(IntToStr(Port), DatabaseServer);

1) how I need edit it to add support for my service?
2) how call my service functions from client? on demo 6 you use TSQLRestClientURINamedPipe class, do I need of it?

ab · 2012-02-17 21:58:33

Services must be methods of a TSQLRestServer class, not a TSQLRestClient class.
You'll have to create your own TSQLRestServerDB child including the new published method, then use a TSQLRestClientDB instance and specify your own class to the constructor.

See the documentation and the corresponding sample code.

array81 · 2012-02-18 11:23:31

OK before solve this problem I need solve another problem.

I don't understand the GroupRights of TSQLAuthUser. In TSQLAuthGroup I have the default group "Admin", "Supervision", "User".
I need create a new user (on TSQLAuthUser) with "User" group, to set the GroupRights of TSQLAuthUser I have extract a TSQLAuthGroup by "User" ID, then I have assign it to TSQLAuthUser.

If I try to open the database by the editor, on SQLAuthUser table, I see:

ID	LogonName	  DisplayName	PasswordHashHexa										GroupRights
1	Admin	          Admin		67aeea294e1cb515236fd7829c55ec820ef888e8e221814d24d83b3dc4d825dd	1
2	Supervisor	  Supervisor		67aeea294e1cb515236fd7829c55ec820ef888e8e221814d24d83b3dc4d825dd	2
3	User	          User			67aeea294e1cb515236fd7829c55ec820ef888e8e221814d24d83b3dc4d825dd	3
4	PIPPO		  PIPPO		827ccb0eea8a706c4c34a16891f84e7b							94792608

PIPPO is my new user, as you can see GroupRights have 94792608 value, it's normail.
For defualt users on GroupRights I see the ID of TSQLAuthGroup, besides I cannot retrieve TSQLAuthGroup for my new user.

So my question:
1) How can set an existing GroupRights to my user?
2) How can I retrieve from database the GroupRights of my user?

Last edited by array81 (2012-02-18 13:08:22)

ab · 2012-02-18 17:18:22

array81 wrote:

OPIPPO is my new user, as you can see GroupRights have 94792608 value, it's normail.

This is not normal.
TSQLAuthUser.GroupRights is a TSQLAuthGroup - that is, it shall store the ID of one TSQLAuthGroup instance, like 1,2, or 3.
See what the ORM documentation says about properties pointing to a TSQLRecord class: it does not store an instance, but an ID, via a typecast.

array81 wrote:

2) How can I retrieve from database the GroupRights of my user?

See class procedure TSQLAuthGroup.InitializeTable for how default users are created:

U := TSQLAuthUser.Create;
try
U.LogonName := 'Admin';
U.PasswordPlain := 'synopse';
U.DisplayName := U.LogonName;
U.GroupRights := TSQLAuthGroup(AdminID);
Server.Add(U,true);
U.LogonName := 'Supervisor';
U.DisplayName := U.LogonName;
U.GroupRights := TSQLAuthGroup(SupervisorID);
Server.Add(U,true);
U.LogonName := 'User';
U.DisplayName := U.LogonName;
U.GroupRights := TSQLAuthGroup(UserID);
Server.Add(U,true);
finally
U.Free;
end;

array81 · 2012-02-19 11:35:13

Ok, thanks. Now it works.

Another think. My server application can works also a stand alone application (without clients), in this case the user (the administrator) can set if ask always username and password or run without login data.

My application (when run as stand alone) need of it:
1) try to open the database;
2) check a record inside the database to know if it must ask username and password to user;
3) if it must ask then a form is open, the administrator set username and password then I use setUser() with these data;
4) if it NOT must ask them the application should works with administrator account without ask username and password to user;

if point 4) possibile (run an stand alone application as administrator without use setUser function)? How?
can I retrieve a simple value (point 2)) before setUser() function?

Obviously I would like to avoid saving the information on registry or files.

Last edited by array81 (2012-02-19 12:03:52)

ab · 2012-02-19 12:45:14

For security reasons, SetUser() is necessary if the server is set to use authentication.

What you can do is to use SetUser() everytime, but specify a low-right-leveled default user at first, then change the user to have an elevated user.

You can use a service if you want to check for a given user abilities without having to set the access rights to the low-right-leveled default user.

array81 · 2012-02-19 15:02:37

OK. I think a have found a solution.

Is it possible add some properties to TSQLAuthUser object?
Now I use a second table with information about the user, however in this way I need use 2 table to get all information about my user. If I can add some properties to TSQLAuthUser I can avoid my second table. I have see Data property but I prefer (if possible) add one property for any information about the user.

Another think, can you add a new function like setUser but without "SHA256('salt'+Password)" function. On my application the user can store the password on registry. For security reason I cannot save the password but I have the SHA256('salt'+Password) value, so I need set user without SHA256('salt'+Password).

Now I use a clear password, then setUser use SHA256('salt'+Password) to compare the password inside the database, it's ok.
I need use setUser with directly SHA256('salt'+Password) (backup on registry).

Is it possible?

Last edited by array81 (2012-02-19 22:45:59)

ab · 2012-02-20 09:24:31

As was told about months ago, you should better not change TSQLAuthUser object itself, for consistency with future versions of the framework: you shall not change the main mORMot code, in order to make code upgrade easier.
Use the Data property, or another custom table.
TSQLAuthUser is to be used at the mORMot authentication layer - you shall better create your own class for storing application-specific settings.

About SHA256('salt'+Password), you can store a SHA256() of the user password in the registry, then use it as mORMot's password.
Or you can use encryption to retrieve the plain password from registry - use e.g. TAESCBC for safe retrieval.

array81 · 2012-02-20 09:55:56

About TSQLAuthUser, ok I understand.

About SHA256 I think I have not explained. Now my application works in this way:
1. The user fill username and password fields (of application form).
2. The application call setUser fuction.
3. My application use SHA256('salt'+Password) function and store this value to registry.
...
until here all is ok. However when the user restart the application, the application load the password from registry. But in this case the password just have SHA256('salt'+Password)! so when I call setUser with the username (clear string) and the password (just with SHA256), the setUser return FALSE.
I think setUser should accept both clear and SHA256 password.

In alternative, in fact, I need always save a CLEAR password.

What do you think about it?

Last edited by array81 (2012-02-20 09:59:31)

ab · 2012-02-20 10:07:16

I think I understood your point, but I did not explain well.

Your asked modification is very small, and does make sense in some cases, so I've committed it:
See http://synopse.info/fossil/info/fed40c6cd6

Thanks for the idea!

array81 · 2012-02-20 13:25:43

Thanks.

Is there a way to avoid timeout session for some type of connection?

I need avoid timeout session if my user use my appliction to connect with the database and use timeout session if my user use web access (with browser) to connect with the databse.

Is it possible? Is there a way to disable timeout session?

array81 · 2012-02-22 14:17:45

I think I have solve my problem with user manager but I have problem with the server, this is my code:

Database := TSQLRestClientDB.Create(Model, nil, Filename, TSQLRestServerDB, True);
DatabaseServer := TSQLRestClientDB(Database).Server;
DatabaseServer.CreateMissingTables(0);
DatabaseServer.OnUpdateEvent := DatabaseAggiornatoEvento;
Server := TSQLite3HttpServer.Create(IntToStr(Port), DatabaseServer);

with this code I can use my application as client, as server or as stand alone application (the user can select it). I cannot understand how edit with with service support withoutv lost these 3 type of use inside the same application. Sorry I just have write about it but I have read the documentation and see thedemo without success. In your demo you use 2 application one as client and one as server (I have understand how it works). I need create a generic code to works on 3 different mode (client, as server or as stand alone application).

Can you give me some suggestions?

Another small think, is there a way to avoid session timeout for some users?

ab · 2012-02-22 18:23:18

Services must be methods of a TSQLRestServer class, not a TSQLRestClient class.
You'll have to create your own TSQLRestServerDB child including the new published method, then use a TSQLRestClientDB instance and specify your own class to the constructor.

Create your own TSQLRestServer class:

type
  TMyServer = class(TSQLRestServerDB)
  published
    function Service(var aParams: TSQLRestServerCallBackParams): Integer;
  end;

Then use this class for your Database variable:

Database := TSQLRestClientDB.Create(Model, nil, Filename, TMyServer, True);

About session timeout, set the TSQLAuthGroup.SessionTimeout property for the User's group to some huge value (e.g. 1000 minutes).

array81 · 2012-02-22 22:51:04

I'm an idiot, sorry

About timeout, so I cannot change timeout value at "runtime" for single user (not group), right?

Last edited by array81 (2012-02-22 23:03:11)

ab · 2012-02-23 07:42:48

Timeout is a group parameter.

But user can have its own group. Create a dedicated group.

array81 · 2012-03-08 15:22:20

OK, I have add 2 service on my server, one service to UPDATE one record and one service to DELETE one or more record.
Now I think my code is ok, if I run my application as Admin it works without problem (both on server application and standalone application).
If I run the code on client application as Admin it works. but if I run on client application as Supervisor it NOT work, the Database.ExecuteList return false!

I understand that with a service I can do these operations as Supervisor, is it not right? In this case how I can do it?

ab · 2012-03-08 17:01:22

If you perform those operations within a service, you'll have the server-side access to the database, so you can do whatever you want.

If you want to add some security, just check the Group ID which launches the service.
With a method-based service, you can retrieve the user group ID from TSQLRestServerCallBackParams.Context.Group.

array81 · 2012-03-08 17:12:43

OK, this is clear but I have the opposite problem. Without limit my service code with group ID of my user, the Database.ExecuteList inside the service code return FALSE if the user that call the service is NOT an Admin.

Do you have any ideas? From my test the service code is always run but Database.ExecuteList return false for no Admin user.

ab · 2012-03-08 17:27:24

No, this is not possible to execute an UPDATE within "ExecuteList", by definition.
"ExecuteList" will let any "SELECT" statement pass, but it will block an UPDATE statement.
This is by design, and according to the security policy.

You need to have the reSQL right in the AllowRemoteExecute set.
This is only for administrator, by definition.
So if you set reSQL for the corresponding user group, it will work.

array81 · 2012-03-08 18:01:46

This not sound good. Is there another fast way to update a record with a condition without use POST commands? Somethings like this:

UPDATE mytable SET myfiled1=20, myfiled2="value2", myfiled3="value3" WHERE myparameter=3

Is there a way with the framework?

I think DELETE command is simple to reproduce with the framework functions but I don't know the way with UPDATE.

In the first posts you had suggested me to create a dedicated service, maybe I misunderstood.

Last edited by array81 (2012-03-08 21:26:56)

array81 · 2012-03-09 14:41:17

I think I have understand your advice, so I have write the follow code:

function TSQLRestServerDBWithService.UpdateRecordsValue(var aParams: TSQLRestServerCallBackParams): Integer;
var
  PTable, PCodition, PFieldsName, PFieldsValue: RawUTF8;
  FieldsName, FieldsValue: TRawUTF8DynArray;
  Results: TIntegerDynArray;
  Records: TSQLRecord;
  i: Integer;
begin
  Result := 404;

  if not UrlDecodeNeedParameters(aParams.Parameters,'A,B,C,D') then
    begin
      Exit;
    end;

  while aParams.Parameters <> nil do
    begin
      UrlDecodeValue(aParams.Parameters,'A=',PTable, @aParams.Parameters);
      UrlDecodeValue(aParams.Parameters,'B=',PCodition, @aParams.Parameters);
      UrlDecodeValue(aParams.Parameters,'C=',PFieldsName, @aParams.Parameters);
      UrlDecodeValue(aParams.Parameters,'D=',PFieldsValue, @aParams.Parameters);
    end;

  if (UTF8ToString(PTable) <> '') and (UTF8ToString(PCodition) <> '') and
     (UTF8ToString(PFieldsName) <> '') and (UTF8ToString(PFieldsValue) <> '') then
    begin
      if LowerCase(PTable) = 'project' then
        Records := TSQLProjects.CreateAndFillPrepare(Database, PCodition, PFieldsName)
      else if LowerCase(PTable) = 'contact' then
        Records := TSQLContacts.CreateAndFillPrepare(Database, PCodition, PFieldsName)
      else if LowerCase(PTable) = 'agenda' then
        Records := TSQLAgenda.CreateAndFillPrepare(Database, PCodition, PFieldsName)
      else if LowerCase(PTable) = 'document' then
        Records := TSQLDocuments.CreateAndFillPrepare(Database, PCodition, PFieldsName)
      else if LowerCase(PTable) = 'password' then
        Records := TSQLPassword.CreateAndFillPrepare(Database, PCodition, PFieldsName)
      else if LowerCase(PTable) = 'note' then
        Records := TSQLNotes.CreateAndFillPrepare(Database, PCodition, PFieldsName)
      else
        Records := nil;

      CSVToRawUTF8DynArray(PUTF8Char(PFieldsName), FieldsName, ',');
      CSVToRawUTF8DynArray(PUTF8Char(PFieldsValue), FieldsValue, ',');

      try
        Database.TransactionBegin(Records.RecordProps.Table);
        try
          Database.BatchStart(Records.RecordProps.Table);

          while Records.FillOne do
            begin
              for i := 0 to Length(FieldsName) - 1 do
                begin
                  Records.SetFieldVariant(UTF8ToString(FieldsName[i]), UTF8ToString(FieldsValue[i]));
                end;

              Database.BatchUpdate(Records);
            end;

          Result := Database.BatchSend(Results);

          Database.Commit;
        except
          Database.RollBack;
        end;
      finally
        Records.Free;
      end;
    end;

  aParams.Resp := JSONEncodeResult([Result]);
end;

I can call the service with this code:

   Database.CallBackGetResult('UpdateRecordsValue',['a','MyTable','b','ID=20', 'c', 'Field1, Field2', 'd', 'Value1, Value2']);

The code will not be beautiful but don't get me exception and BatchSend result is 200. However if I check the Results array I get always 0, so my data are NOT store.

I have check the Records properties after SetFieldVariant and these are ok (with the new value).

Do you have any idea about it?

ab · 2012-03-09 16:03:21

You should better not use such a method, with fixed list of table names within...
And there is no need to use a Batch on the server side, since... you are already on the server side.
You are mixing client and server way of handling data.

I've added a new TSQLRest.EngineUpdateField protected method for a field content update (with PUT ModelRoot/TableName?setname=..&set=..&wherename=..&where=..) and removal of deprecated TSQLRestServer.UpdateField methods.
See http://synopse.info/fossil/info/2ab0c7d298

I'll add very soon a generic TSQLRest.Updatefield() method to allow execution of statements as this one:

UPDATE tablename SET fieldname=fieldvalue WHERE wherename=wherevalue

This will work with all kind of DB engines, including virtual tables, external tables and in-memory tables.

array81 · 2012-03-09 17:22:19

OK, so you advise me to wait for your new TSQLRest.Updatefield () method and then use it inside my service code, right? Or can I simply remove batch code and call update method for all my TSQLRecord?

Do you think you will add the feature in fast way?

Another thing, with your new method is possible update more that one field?

Thanks

Last edited by array81 (2012-03-09 17:29:55)

array81 · 2012-03-09 22:04:26

I have update my code:

function TSQLRestServerDBWithService.UpdateRecordsValue(var aParams: TSQLRestServerCallBackParams): Integer;
var
  PTable, PCodition, PFieldsName, PFieldsValue: RawUTF8;
  FieldsName, FieldsValue: TRawUTF8DynArray;
  Results: Boolean;
  Records: TSQLRecord;
  i: Integer;
begin
  Result := 404;
  Results := True;

  if not UrlDecodeNeedParameters(aParams.Parameters,'A,B,C,D') then
    begin
      Exit;
    end;

  while aParams.Parameters <> nil do
    begin
      UrlDecodeValue(aParams.Parameters,'A=',PTable, @aParams.Parameters);
      UrlDecodeValue(aParams.Parameters,'B=',PCodition, @aParams.Parameters);
      UrlDecodeValue(aParams.Parameters,'C=',PFieldsName, @aParams.Parameters);
      UrlDecodeValue(aParams.Parameters,'D=',PFieldsValue, @aParams.Parameters);
    end;

  if (UTF8ToString(PTable) <> '') and (UTF8ToString(PCodition) <> '') and
     (UTF8ToString(PFieldsName) <> '') and (UTF8ToString(PFieldsValue) <> '') then
    begin
      if LowerCase(PTable) = 'project' then
        Records := TSQLProjects.CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName)
      else if LowerCase(PTable) = 'contact' then
        Records := TSQLContacts.CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName)
      else if LowerCase(PTable) = 'agenda' then
        Records := TSQLAgenda.CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName)
      else if LowerCase(PTable) = 'document' then
        Records := TSQLDocuments.CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName)
      else if LowerCase(PTable) = 'password' then
        Records := TSQLPassword.CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName)
      else if LowerCase(PTable) = 'note' then
        Records := TSQLNotes.CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName)
      else
        Records := nil;

      CSVToRawUTF8DynArray(PUTF8Char(PFieldsName), FieldsName, ',');
      CSVToRawUTF8DynArray(PUTF8Char(PFieldsValue), FieldsValue, ',');

      try
        while Records.FillOne do
          begin
            for i := 0 to Length(FieldsName) - 1 do
              begin
                Records.SetFieldVariant(UTF8ToString(FieldsName[i]), UTF8ToString(FieldsValue[i]));
              end;

            if not Database.Update(Records) then
              Results := False;
          end;
      finally
        Records.Free;
      end;
    end;

  aParams.Resp := JSONEncodeResult([Result]);
end;

As you can see I have remove the batch code and add "id" field on aCustomFieldsCSV parameter. In this way the code works but there is a problem. On documentation about CreateAndFillPrepare I can read "since the missing fields will be left with previous values", however all fields that I can retrieve with aCustomFieldsCSV parameter, after the Database.Update are delete. For example after Database.Update all Integer fields not retrieve are = 0.
Is there a away to update only the retrieve fields without edit the other fields?

array81 · 2012-03-10 10:43:56

OK. While waiting for your answer I did some experiments. I have use the code of post #45 (batch again) and I have change only

CreateAndFillPrepare(Database, PCodition, PFieldsName);

with

CreateAndFillPrepare(Database, PCodition, 'id, ' + PFieldsName);

In alternative in fact the TSQLRecord don't have the ID record so I cannot update it. With this code my applica works as client, as server or as standalone.

I think is code is the code that you have advise me on post #16, however on post #46 you tell me don't use it.

I have a bit of confusion in the head, I need of a way to update one or more fields of my table (with condition WHERE), and I need of it on client (User), server (Admin) or standalone (Admin).

If my code with batch (the only code that works for me) is not the better solution, what is the better code?

ab · 2012-03-10 21:23:38

Using CreateAndFillPrepare + BatchUpdate will work as expected.
You'll have a bit more data transmitted, in comparison with the server-side implementation using a service.
But it is perfectly working, and thanks to the BATCH implementation, it will be very fast, since only one command will be sent to the server.
And thanks to the latest version of the framework (upcoming 1.16): only fields retrieved by CreateAndFillPrepare() will be transmitted back to the server.

So it is a good solution if you want only to have client-side coding (no service to be added).

mORMot Open Source

#1 2011-11-24 08:33:52

Problem with UPDATE query and EngineExecute

#2 2011-11-24 11:02:13

Re: Problem with UPDATE query and EngineExecute

#3 2011-11-24 12:20:37

Re: Problem with UPDATE query and EngineExecute

#4 2011-11-24 15:58:54

Re: Problem with UPDATE query and EngineExecute

#5 2011-11-24 21:09:00

Re: Problem with UPDATE query and EngineExecute

#6 2011-11-24 22:27:44

Re: Problem with UPDATE query and EngineExecute

#7 2011-11-25 06:32:22

Re: Problem with UPDATE query and EngineExecute

#8 2011-11-28 14:50:15

Re: Problem with UPDATE query and EngineExecute

#9 2012-01-30 22:38:44

Re: Problem with UPDATE query and EngineExecute

#10 2012-01-31 11:01:24

Re: Problem with UPDATE query and EngineExecute

#11 2012-01-31 12:57:00

Re: Problem with UPDATE query and EngineExecute

#12 2012-01-31 13:43:04

Re: Problem with UPDATE query and EngineExecute

#13 2012-02-15 14:21:38

Re: Problem with UPDATE query and EngineExecute

#14 2012-02-15 15:05:22

Re: Problem with UPDATE query and EngineExecute

#15 2012-02-15 15:32:33

Re: Problem with UPDATE query and EngineExecute

#16 2012-02-15 16:59:31

Re: Problem with UPDATE query and EngineExecute

#17 2012-02-15 17:31:05

Re: Problem with UPDATE query and EngineExecute

#18 2012-02-15 19:01:58

Re: Problem with UPDATE query and EngineExecute

#19 2012-02-15 22:41:49

Re: Problem with UPDATE query and EngineExecute

#20 2012-02-16 07:48:43

Re: Problem with UPDATE query and EngineExecute

#21 2012-02-16 08:37:38

Re: Problem with UPDATE query and EngineExecute

#22 2012-02-16 08:44:03

Re: Problem with UPDATE query and EngineExecute

#23 2012-02-16 14:05:02

Re: Problem with UPDATE query and EngineExecute

#24 2012-02-16 17:08:56

Re: Problem with UPDATE query and EngineExecute

#25 2012-02-17 21:44:20

Re: Problem with UPDATE query and EngineExecute

#26 2012-02-17 21:58:33

Re: Problem with UPDATE query and EngineExecute

#27 2012-02-18 11:23:31

Re: Problem with UPDATE query and EngineExecute

#28 2012-02-18 17:18:22

Re: Problem with UPDATE query and EngineExecute

#29 2012-02-19 11:35:13

Re: Problem with UPDATE query and EngineExecute

#30 2012-02-19 12:45:14

Re: Problem with UPDATE query and EngineExecute

#31 2012-02-19 15:02:37

Re: Problem with UPDATE query and EngineExecute

#32 2012-02-20 09:24:31

Re: Problem with UPDATE query and EngineExecute

#33 2012-02-20 09:55:56

Re: Problem with UPDATE query and EngineExecute

#34 2012-02-20 10:07:16

Re: Problem with UPDATE query and EngineExecute

#35 2012-02-20 13:25:43

Re: Problem with UPDATE query and EngineExecute

#36 2012-02-22 14:17:45

Re: Problem with UPDATE query and EngineExecute

#37 2012-02-22 18:23:18

Re: Problem with UPDATE query and EngineExecute

#38 2012-02-22 22:51:04

Re: Problem with UPDATE query and EngineExecute

#39 2012-02-23 07:42:48

Re: Problem with UPDATE query and EngineExecute

#40 2012-03-08 15:22:20