Problem with CreateAndFillPrepare

Roberto Schneiders · 2013-02-05 12:48:22

I have a problem with the command CreateAndFillPrepare.

I have a mORMot server. Basically it is:

    fConnection := TODBCConnectionProperties.Create('teste', 'roberto', 'sysdba', 'password');
    VirtualTableExternalRegisterAll(Model,fConnection);

    fConnection.MainConnection.Connect;
    if fConnection.MainConnection.IsConnected then
    begin
      fServer := TSQLRestServerDB.Create(Model, 'test.db3', false);
      fServer.CreateMissingTables;

      HTTPServer := TSQLHttpServer.Create('888', [fServer]);
    end;

If I type this in the browser:

http://192.168.3.134:888/root/people/

I get a JSON with all table data. As expected.

In Delphi client I can not get the same result. When performing the method CreateAndFillPrepare of Tpeople class the data is not returned.

Client code:

  Client := TSQLHttpClient.Create('192.168.3.134', '888', Model);

  if not Client.ServerTimeStampSynchronize then
    ShowMessage(Client.LastErrorMessage);
...
  PeopleList := TPeople.CreateAndFillPrepare(Client, '1=1');

The server logs the following information:

20130205 10124217  -    TSQLRestServerDB(0225F9D0). 00.001.773
20130205 10124948  +    TSQLRestServerDB(0225F9D0).root
20130205 10124948 cache 	TSQLDatabase(0230AC20) not in cache
20130205 10124948 EXC   	ESQLite3Exception ("no such column: ID") at 004BC807  stack trace 0048D8AF 0049070B 004B8CF0 004B810B 004B7706 0044A7BD 00408006 74D5339A 770B9EF2 770B9EC5 
20130205 10124948 res   	null
20130205 10124948 srvr  	GET root ERROR=400 (Bad Request)
20130205 10124948  -    TSQLRestServerDB(0225F9D0). 00.004.176

Any tips?

ab · 2013-02-05 16:04:43

I guess you use an external table.
Is the structure correct in the external table?

What is the SQL statement run in FireBird?

Roberto Schneiders · 2013-02-05 16:46:59

Yes, is a external table.

I captured this command via debug.

SELECT ID,Name,Age,BirthDate FROM People WHERE 1=1

I run this command via IBExpert and works.

Last edited by Roberto Schneiders (2013-02-05 16:49:09)

ab · 2013-02-05 17:47:41

Could you test:

PeopleList := TPeople.CreateAndFillPrepare(Client, 'ID<>0');

Roberto Schneiders · 2013-02-05 17:54:20

It worked!

Roberto Schneiders · 2013-02-05 19:02:52

I had been using '1=1' because this parameter is required. Would not be better to let a default value for this parameter?

one more question.

I'm using the command UpdateFromServer to fetch updates the list of people. It's not working for me.

code:

var
  refreshed:boolean;
begin
  if Assigned(TableToGrid) then
  begin
    Client.UpdateFromServer([PeopleList], refreshed);   
    // I also tried  Client.UpdateFromServer([PeopleList.FillTable], refreshed); 
    if refreshed then
    begin
      TableToGrid.Refresh();
      DrawGrid1.Refresh;
    end;
  end
  else
  begin
    PeopleList := TPeople.CreateAndFillPrepare(Client, 'ID<>0');
    TableToGrid := TSQLTableToGrid.Create(DrawGrid1, PeopleList.FillTable, Client);
  end;

How should I use?

UpdateFromServer([PeopleList], refreshed) 
or 
UpdateFromServer([PeopleList.FillTable], refreshed)

I add a new record to the database via IBExpert. When executing this method (UpdateFromServer) the variable "refreshed" is false.

On the server log I notice that he does not perform a select on the database.

I suppose maybe this method only works when the table is changed exclusively thru the ORM. (Documentation: make use of the InternalState function to check the data content revision)

In this case, there is some method to update a table that eventually can be changed by another application?

I know that any manipulation in the database must pass through the server (ORM), but as we are trying to migrate an old application, it is not possible at the moment.

Last edited by Roberto Schneiders (2013-02-05 19:04:44)

ab · 2013-02-05 19:17:31

You can set '' as parameter - it will retrieve the whole table.

Did you try UpdateFromServer without Firebird, but with the SQLite3 engine?

Roberto Schneiders · 2013-02-05 19:36:42

Now tested with SQLite. Apparently, the behavior is the same.

Roberto Schneiders · 2013-02-06 10:09:33

That same software. The table tpeople has 10 thousand records. Every time a customer requests the data (CreateAndFillPrepare) memory consumption of the server increases almost 1 MB. Debugging realized that memory allocation is done in JSON parser. But I could not figure out why this memory is not freed.

There are no memory leaks after closing the server.

ab · 2013-02-06 16:51:27

Requests are stored in internal mORMot cache (search for this keyword in the documentation).
I guess this is what is occurring: the JSON results is cached until an Update/Add/Delete is performed.

In practice, we found out this cache ability to be very efficient.

Roberto Schneiders · 2013-02-06 17:19:39

About memory consumption. Ok

...and about UpdateFromServer?

Roberto Schneiders · 2013-02-08 10:25:29

I still have with this problem with "UpdateFromServer".

Can anyone help me?

ab · 2013-02-08 18:11:58

Could you step in the server side code via the debugger, and see what is not working?
And/or in TSQLRestClientURI.UpdateFromServer() method first?

Roberto Schneiders · 2013-02-08 19:24:45

Any tips of which method debug in the server?

ab · 2013-02-08 20:02:22

1. What does occur in TSQLRestClientURI.UpdateFromServer()
It it as expected?

2. What is the data retrieved from server?
It it as expected?

3. Use a local TSQLRestClientDB with embedded server, which allows to trace the process one line per one line, without any issue of client/server sides.

Roberto Schneiders · 2013-02-11 10:32:06

I think I found it.

in TSQLRestClientURI.UpdateFromServer method the "T.InternalState" is equal to "State" (ServerInternalState). In this case the conditional test in line 20531 don't pass and nothing happens.

ab · 2013-02-17 06:23:17

Yes - I did not notice what you wrote above: "I added a new record via IBExpert".

In fact, the ORM layer uses this InternalState variable to avoid unneeded DB requests.
If you do not bypass mORMot framework, this optimization won't work.

So if your request is about to be refreshed outside the scope of the framework, you should not trust UpdateFromServer, but execute the true query on each time.
What you can do is a custom server-side cache, and a dedicated UpdateFromServer-like method in an interface-based service.
But perhaps UpdateFromServer() has to be enhanced to support external DB modification.

Roberto Schneiders · 2013-02-18 12:20:54

Ok. Thank you.

mORMot Open Source

#1 2013-02-05 12:48:22

Problem with CreateAndFillPrepare

#2 2013-02-05 16:04:43

Re: Problem with CreateAndFillPrepare

#3 2013-02-05 16:46:59

Re: Problem with CreateAndFillPrepare

#4 2013-02-05 17:47:41

Re: Problem with CreateAndFillPrepare

#5 2013-02-05 17:54:20

Re: Problem with CreateAndFillPrepare

#6 2013-02-05 19:02:52

Re: Problem with CreateAndFillPrepare

#7 2013-02-05 19:17:31

Re: Problem with CreateAndFillPrepare

#8 2013-02-05 19:36:42

Re: Problem with CreateAndFillPrepare

#9 2013-02-06 10:09:33

Re: Problem with CreateAndFillPrepare

#10 2013-02-06 16:51:27

Re: Problem with CreateAndFillPrepare

#11 2013-02-06 17:19:39

Re: Problem with CreateAndFillPrepare

#12 2013-02-08 10:25:29

Re: Problem with CreateAndFillPrepare

#13 2013-02-08 18:11:58

Re: Problem with CreateAndFillPrepare

#14 2013-02-08 19:24:45

Re: Problem with CreateAndFillPrepare

#15 2013-02-08 20:02:22

Re: Problem with CreateAndFillPrepare

#16 2013-02-11 10:32:06

Re: Problem with CreateAndFillPrepare

#17 2013-02-17 06:23:17

Re: Problem with CreateAndFillPrepare

#18 2013-02-18 12:20:54

Re: Problem with CreateAndFillPrepare

Board footer