mORMot Open Source

Interesting. I've replaced the above part with this, and it appears not to leak, and it seems to be working correctly:

  var
    aList: TSQLTableJSON;
  begin
    aList := ServerDB.MultiFieldValues(TSomething, Fields, Where, [], Params);
    if aList <> nil then
      Result := TSynSQLTableDataSet.CreateOwnedTable(AOwner, aList);
...

I'm trying to come up with a solution for this (by the way, the updated documentation link is now https://synopse.info/files/html/api-1.1 … ADPROCESS).

Do anybody have some sample code that allows to program task to run at a *specific* time every day?

It was this (columns removed):

'SELECT RowID,...COLFROM,COLTO FROM SomeObject WHERE :(3001): BETWEEN COLFROM AND COLTO LIMIT 1'

It all went well until SynTable.TSynTableStatement.Create, around line 7616. Here the P buffer was

P = ':(3001): BETWEEN COLFROM AND COLTO LIMIT 1'

And in line 7616, GetPropIndex returns -1 and it causes Stmt.Where to remain empty, which causes the problem.

It seems that the problem is only when the parameter is first in the WHERE clause, because if I do '1=1 AND ? BETWEEN COLFROM AND COLTO' it works without problems (however it discarded it and switched engines because it was a complex statement).

Did some extra debugging, and the issue seems to be table case sensitivity (it appears to be something related to Zeos). There is a variable lower_case_table_names but I don't know yet where it is set.

Edit: I think mormot tries to get the columns in SynDBZeos.pas, around line 720:

procedure TSQLDBZEOSConnectionProperties.GetFields(
  const aTableName: RawUTF8; out Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    n, i: integer;
    Schema, TableName: RawUTF8;
    sSchema, sTableName: string;
    F: TSQLDBColumnDefine;
    FA: TDynArray;
begin
  if GetDatabaseMetadata(meta) then begin
    SQLSplitTableName(aTableName, Schema,TableName);
    sSchema := UTF8ToString(Schema);
    { mormot does not create the Tables casesensitive but gives mixed cased strings as tablename
      so we normalize the identifiers to database defaults : }
    sTableName := meta.GetIdentifierConvertor.ExtractQuote(UTF8ToString(TableName));
    sTableName := meta.AddEscapeCharToWildcards(sTableName); //do not use "like" search patterns ['_','%'] so they need to be escaped
    res := meta.GetColumns('',sSchema,sTableName,'');

The line with sTableName := meta.GetIdentifierConvertor.ExtractQuote(UTF8ToString(TableName)) converts the table name to lowercase. This causes meta.GetColumns() to fail.

For the moment, I added a retry logic on the calling method:

    repeat
      if Result.Retries > 0 then
        TThread.Sleep(FPreferences.WaitBetweenRetries);
      Result.PreviouslyAssignedNumber := ReserveRecord(Branch, Terminal);
      Inc(Result.Retries);
    until (Result.PreviouslyAssignedNumber > 0) or (Result.Retries >= FPreferences.NumberOfRetries);

After some testing, I found out that the ReserveRecord() function takes about 50ms to complete. So I tried sending 5 requests at the same time, and have configured 0 retries.

Start
Thread: 1 - Error obtaining record (2 ms., 1 retries)
Thread: 1 - Error obtaining record (0 ms., 1 retries)
Thread: 1 - Error obtaining record (0 ms., 1 retries)
Thread: 1 - Error obtaining record (0 ms., 1 retries)
Thread: 1 - Assigned number: 2290 (52 ms., 1 retries)
End

Adding 10ms delay, 2 retries:

Start
Thread: 1 - Error obtaining record (53 ms., 2 retries)
Thread: 1 - Error obtaining record (59 ms., 2 retries)
Thread: 1 - Error obtaining record (64 ms., 2 retries)
Thread: 1 - Assigned number: 2292 (146 ms., 2 retries)
Thread: 1 - Assigned number: 2291 (173 ms., 1 retries)
End

Adding 50ms delay, 10 retries:

Start
Thread: 1 - Assigned number: 2296 (139 ms., 2 retries)
Thread: 1 - Assigned number: 2297 (195 ms., 3 retries)
Thread: 1 - Assigned number: 2295 (208 ms., 1 retries)
Thread: 1 - Assigned number: 2298 (295 ms., 5 retries)
Thread: 1 - Assigned number: 2299 (359 ms., 6 retries)
End

In my current case, I have about 20 terminals, so worst case scenario the server would receive 20 requests at the same time (and this is likely never going to happen), and a 100ms or so wait time for a ticket to complete is not big deal, so for my current needs this is OK. However, if I wanted to scale (for example, a server in the cloud attending thousands of terminals), what could be a good way to "reserve" a record? I imagine creating an in-memory concurrent cache (queue?) would do the trick, but I don't know how easy is to do that in Delphi and mORMot.

I was about to post my code, and found out that effectively, I was using the wrong object during the update.

This is my current code, which seems to be working fine:

procedure UpdateTrackIds(docs: TSomeList; TrackId: Int64);
var
  batchUpdater: TSQLRestBatch;
  i: Integer;
  fieldBits: TSQLFieldBits;
  rec: TMyRecord;
begin
  fieldBits := TMyRecord.RecordProps.FieldBitsFromRawUTF8
    ([StringToUTF8('TrackId')]);
  batchUpdater := TSQLRestBatch.Create(ServerDB, TMyRecord);
  try
    rec := TMyRecord.Create;
    try
      rec.TrackId := TrackId;
      for i := 0 to Length(docs) - 1 do
      begin
        rec.IDValue := docs[i].Id;
        batchUpdater.Update(rec, fieldBits);
      end;
    finally
      rec.Free;
    end;
    ServerDB.BatchSend(batchUpdater);
  finally
    batchUpdater.Free;
  end;
end;

That's the question.

Do you mean, overloading those methods in a descendant class?

TMyRestServer = class(TSQLRestServerFullMemory)
protected
  procedure BeginCurrentThread(Sender: TThread); override;
  procedure EndCurrentThread(Sender: TThread); override;
end;

procedure TMyRestServer.BeginCurrentThread(Sender: TThread);
begin
  inherited;
  CoInitialize(nil);
end;

procedure TMyRestServer.EndCurrentThread(Sender: TThread);
begin
  CoUninitialize;
  inherited;
end;

Found the issue - somewhere in my existing library path, there is a sqlite3.obj that is way old. I don't want to disturb my old old old AnyDAC libraries so I changed the {$L} directive in SynSQLite3Static.pas around line 340, but I'll have to keep an eye on it in the future.

  {$ifdef MSWINDOWS}
    {$ifdef CPU64}
      {$L sqlite3.o}  // compiled with C++ Builder 10.3 Community Edition bcc64
    {$else}
      {$L D:\delphi\mORMot\static\sqlite3.obj}  // compiled with free Borland C++ Compiler 5.5
    {$endif}

Yes, I'm slowly making progress.

I think people don't say this as often as we should - thank you for your incredible work, Arnaud.

I'm having similar issues. What should I put in "Server" to connect to MySQL using ZEOS?

localhost:
Requested database driver was not found

mysql:localhost
Project SynDBExplorer.exe raised exception class EZSQLException with message 'SQL Error: Unknown database 'localhost' Code: 1049 Message: Connect to "localhost" as user "root"'.

mysql:127.0.0.1
Project SynDBExplorer.exe raised exception class EZSQLException with message 'SQL Error: Unknown database '127.0.0.1' Code: 1049 Message: Connect to "127.0.0.1" as user "root"'.

Of course, I can connect without problems with any other program (heidisql, my own anydac based programs, etc.)

I don't see the full query, but

SELECT DISTINCT TOP 10 ...

should work. It is worth noticing that DISTINCT is applied first and then TOP (as it should.) Also, DISTINCT applies to all columns in the query, after grouping.

This is what I want to do. I want to avoid SQL because I'm looking for this project as a learning opportunity (and I've been using SQL for over twenty years, so no much point to keep learning that.)

I'm loving this thread!

Cool!

What did you tried? You clone a repository, make your local changes (including "git remove" and "git add" operations) then commit to your local branch. Then, pull request and if it is approved, all changes (including added and deleted files) get merged in.

If changing contents inside a folder wasn't allowed, git wouldn't be very useful, don't you think?

Well, I did:

This is precisely what I don't want to do.

Again, this sounds dangerously close to modify the existing database.

There we go again, creating tables (perhaps I'm reading it wrong, and it means tables on the client side?)

Also, in the API reference:

Still doesn't dispel my fear of it trying to modify the underlying database.

That's everything I could find in the document you linked regarding this function, and in the API reference. In fact, I had already read it. Perhaps I just don't understand it?

Arnaud, I don't mean to bash your documentation, but it is huge. Personally, I don't learn that way - I cannot consume a whole book and suddenly become an expert on the subject matter. I'm a slow learner, and I need to bit small pieces to understand what I'm doing. The function name itself (CreateMissingTables) informs me that it wants to modify my database. The documentation doesn't state otherwise. The code, while well written, is extremely complicated and difficult to understand (as it should be, it is doing some amazing magic.) This is a perfect storm of documentation to be drown in.

And some positive criticism: RTFM is not always the best response, especially when the person asking the question shows that it is trying hard.

Anyways, I'll keep trying. Thanks for your awesome work!