mORMot Open Source

I get this error when the IList<TOrm> is zero referenced after using CreateAndFillPrepare.

// TOrmArticles is the same as IList<TArticle>
function TDHSSyncRepository.GetAllArticles(var aArts: TOrmArticles):TSyncRepoError;
var
  ormArt: TOrmArticle;
  i: integer;
begin
  result := srNotFound;
  i := 0;
//  result := Collections.NewList<TOrmArticle>; //Tested doing this call outside/inside this function but same result.
  ormArt := TOrmArticle.CreateAndFillPrepare(FRestOrm,'',[]);
  while ormArt.FillOne do begin
    aArts.Add(ormArt);
  end;
  result := srSuccess;
end;

When aArts no longer used  I get the error.
What's wrong here?

How should I deal with fields that sometimes contain NULL-values when using variants
The code below shows how I tried to deal with it.
You can't even check if the field is null before using it.
The only way I know is to have a

Try
except

around each assignment but that doesn't taste good.
I'm sure there is a better solution but I'm lost.

type
  TNames = packed record
      field1: integer;
      theNullField: RawUTF8;
      field3: RawUTF8;
  end;

var
  I: isqldbrows;
  row: variant;
  names: TNames;
begin
  I := Props.execute('select * from dbo.mytable where name = ?', ['Peter']);
  if I.Step(true) then begin
  I.RowDocVariant(row,[]);
  names.fImportDir := row.Field1;
  if not row.theNullField. = null then               // This raise an exception
      names.theNullField := row.theNullField      // This also raises an exception if executed.
  else
      names.theNullField := '';
  names.Field3 := row.Field3;
  I.ReleaseRows;
end;

I have a class of TSynPersistent containing an array of records that I want to populate from a DB-table.
For the moment while testing, I use SQLite3.

I worked out a TOrm descendant used for reading the data.
Everything reads correctly except for the array which is nil.

I thought I didn't need to register but I now realized that I was wrong.

But I don't know how to do it. Following instructions from the documentation I tried this:

The record:

  TWeek = packed record
    rYear: TYear;
    rIndex: TWeekBufferIndex;
    rWeekNo: TWeekNo;
    rAvailableAmt: TAvailableAmt;
    rRequiredAmt: TRequiredAmt;
    function getAvailableOnWeek: TAvailableOnWeek;
    property availableOnWeek: TAvailableOnWeek read getAvailableOnWeek;
  end;
  TWeekArray                 = array of TWeek;

The class:

  TWeeks = class(TSynpersistent)
    private
      fWeekArray  : TWeekArray;
      fActualWeek : TWeekNo;
    public
      function getWeek(index: integer): TWeek;
      procedure SetWeek(const index: integer; aValue: TWeek);
    published
      property weekArray: TWeekArray read fWeekArray write fWeekArray;
  end;

The registration:

initialization
var T: Pointer;
  T := TypeInfo(TWeekArray);
  TJSONSerializer.RegisterObjArrayForJSON(T,TWeekArray);

The TOrm descendant:

  TOrmWeeks = class(TOrm)
    private
      fWeeks      : TWeekArray;
      fActualWeek : TWeekNo;
    protected
    public
    constructor create;
    published
      property ActualWeek: TWeekNo read fActualWeek write fActualWeek;
      property Weeks: TWeekArray read fWeeks write fWeeks;
  end;

The problem is that the compiler doesn't like this: "  TJSONSerializer.RegisterObjArrayForJSON(T,TWeekArray);"
It say's: [dcc32 Error] dddLOBTypes.pas(294): E2029 '(' expected but ')' found
and next error: [dcc32 Error] dddLOBTypes.pas(295): E2250 There is no overloaded version of 'RegisterObjArrayForJson' that can be called with these arguments

If I replace TWeekArray with a class the compiler will be satisfied.
So what do I missing here?

Is there an alternative to mORMot_1:s TDDRepositoryRestFactory.ComputeSQLRecord ?

It would be handy to have one, I've been searching for a while but have not found any.

Completely new to MongoDB and tried one of your old examples:
https://blog.synopse.info/?post/2014/05 … ase-access

Installed the latest version (6.03) of MongoDB
and tried:

var Client: TMongoClient;
    DB: TMongoDatabase;
    serverTime: TDateTime;
    res: variant; // we will return the command result as TDocVariant
    errmsg: RawUTF8;
begin
  Client := TMongoClient.Create('localhost',27017);
  try
    DB := Client.Database['mydb'];
    writeln('Connecting to ',DB.Name); // will write 'mydb'
    errmsg := DB.RunCommand('hostInfo',res); // run a command
    if errmsg<>'' then
      exit; // quit on any error
    serverTime := res.system.currentTime; // direct conversion to TDateTime
    writeln('Server time is ',DateTimeToStr(serverTime));
  finally
    Client.Free; // will release the DB instance
  end;
end;

and gets: 'Unsupported OP_QUERY command: db.hostInfo. The client driver may require an upgrade. For more details see https://dochub.mongodb.org/core/legacy-opcode-removal'

Don't know if its possible use the latest version with mORMot1 any more or should I try with mORMot2?

Is the following SQL realizable with the "TSQLRecord.createJoined" function or is there better alternatives?

I try to avoid running the SQL-script in the program due to fear of"SQL-Injection".
It's about an old legacy database. I have tested the function in simpler case where you use only one common field which work perfectly.

SELECT TE.AddressName, TC.Customername FROM tbl_A TA
 join tbl_B TB ON TB.CustomerNo = TA.CustomerNo
 join tbl_C TC ON TC.CustomerNo = TA.CustomerNo
 join tbl_D TD ON TD.CustomerNo = TA.CustomerNo AND TD.AddressNo = TC.AddressNo
 join tbl_E TE ON TE.AddressNo = TD.AddressNo

I have a small project here where I included everything in one program because it only is about updating a single file with a text field.
My problem is that when the server tries to add a record I'll get an error which says in short: "Cannot insert explicit value into ----- when IDENTITY_INSERT is set to OFF"

I'm running Delphi11 and MSSQL.

Links to part of the code: [URL https://1drv.ms/u/s!AkzgaKIeNifrhaQI3gC … A?e=SpWAz3 /url]

the error message: [URL https://1drv.ms/u/s!AkzgaKIeNifrhaQLwLN … w?e=02zydi /url]

This happens when I use a class that have references to another class.
see below:

  TLogType = class(TSynPersistent)
    private
      fName: RawUTF8;
      fValue: integer;
    published
      property name: RawUTF8 read fName write fName;
      property value: integer read fValue write fValue;
  end;
  TLogTypeObjArray = array of TLogtype;


  TMyLog = class(TSynpersistent)
    private
      fLogdate: TDate;
      fComment: RawUTF8;
      fLogType: TLogType;    <<<<-- the reference
    published
      property logDate: TDate read fLogDate write fLogDate;
      property comment: RawUTF8 read fComment write fComment;
      property logType: TLogType read fLogType write fLogType;
  end;

The reference is flattened in the TSQLRecord so the field "LogType" will be two fields:
LogType_Name" and "LogType_value".

Don't have a clue any longer how to solve this. I have a small zip file of a test project to share but don't know where to put it.
The exception occurs in a section called "fake interface" in the mORMot unit.

Would appreciate any help to solve this. Also, a site where I could upload my little zip file (12k).

Maybe this link? https://1drv.ms/u/s!AkzgaKIeNifrhLQze1G … A?e=ZCbpMx

How to read from database feeding a filtering list without memory leakage?

I have a windows-service that reads data from a table into a TObjectList
which is customized to filter out some data i.e. duplicate records.
That's because I couldn't find out how to do that with SQL and mORMot.
I have created a solution that works but I think it might be a
smarter solution.
Anyone that can tell?

Here is my solution:
Alt 1.

Alt 1. Just need one list and the finally is simpler.
procedure TEvNServer.readPkgPrefix(out aPrefixList: TPrefixList);
var
  SQL: RawUTF8;
  ppf: TSQLPkgPrefix;
  newPkgPrefix: TSQLPkgPrefix;
begin
  SQL := 'usedBySync = ? ORDER BY DefaultPrefix desc ';
  ppf := TSQLPkgPrefix.CreateAndFillPrepare(fDBServer, SQL, [1]);
  aPrefixList := TPrefixList.create;
  try
    while ppf.FillOne do begin
      newPkgPrefix := TSQLPkgPrefix.create;       // Clone object before adding to list
      newPkgPrefix.assign(ppf);                   //
      if aPrefixList.add(newPkgPrefix) = -1 then  // if object not accepted by the list,
        newPkgPrefix.free;                        // it must be free:d, otherwise mem-leakage
    end;
  finally
    ppf.free;  // must be free:d otherwise mem-leakage
  end;
end;

Alt 2.

Alt 2. Dont need to clone and add the assign method to TSQLPkgPrefix class.
procedure TEvNServer.readPkgPrefix(out aPrefixList: TPrefixList);
var
  SQL: RawUTF8;
  ppf: TSQLPkgPrefix;
  tmpList: TObjectList<TSQLPkgPrefix>;
begin
  SQL := 'usedBySync = ? ORDER BY DefaultPrefix desc ';
  tmpList := fDBServer.RetrieveList<TSQLPkgPrefix>(SQL, [1], '');
  aPrefixList := TPrefixList.create;
  try
    for ppf in tmpList do begin
      if aPrefixList.add(ppf) = -1 then begin
        ppf.free;
      end;
    end;
  finally
    if assigned(tmpList) then begin
      tmpList.OwnsObjects := false;
      freeAndNil(tmpList);
    end;
  end;
end;

If I open the logfile with the log viewer, while the program is running and produces lines to the log file, and watching it over time the log go nuts!
It seems that the log viewer holds the logfile so that log rotation can not rename the file but still copies and compresses the filecontents into the rotate-list of files.
As it can't begin with an empty file it continues to run the rotate cycle for each line that adds to the log. This will cause huge cpu and memory load.

Is there a way to avoid this and still view the log? It's too easy to forget to close the viewer and unwatched it can cause a mess.

Otherwise I really like the viewer.

I tried this:

var
  body: variant;
  i: integer;
  files: array of RawUTF8;
  atm: array of variant;
  t: variant;
begin
  setLength(files, 2);
  files[0] := 'C:\temp\Faktura_se.pdf';
  files[1] := 'C:\temp\din.pdf';
  TDocVariant.new(body);
  if high(files) > -1 then
  begin
    SetLength(atm, high(files) + 1);
    for i := 0 to high(files) do
    begin
      TDocVariant.new(atm);
      t.name := extractFileName(files[i]);
      t.value := fileToBase64(files[i]);
      atm[i] := t;
    end;
    body.FileAttachments := _arr([atm]);
  end;

but that just set FileAttachments to '{"FileAttachments":[null]}'

Is there a simple correction of this code?

I tested using the dddUserTypes unit with the classes TPersonFullName, TPerson and TPersonContactable.
Instead of Tuser I created the TCustomer class that inherits from TsynAutoCreateFields and contains a field of type TPersonContactable.

  TCustomer = class(TSynAutoCreateFields)
  private
    fContact: TPersonContactable;
  published
    property Contact: TPersonContactable read fContact;
  end;

Calling –
TDDDRepositoryRestFactory.ComputeSQLRecord([TPersonContactable, TCustomer]);
creates the following classes:
•    TSQLRecordPerson
•    TSQLRecordPersonContactable
•    TSQLRecordCustomer

Calling - .CreateMissingTables creates the following tables:
•    PersonContactable
•    Customer
PersonContactable contains all the fields from TAddress, TPerson and TPersonFullName and it’s own.
Regression tests on TCustomer confirm access to all fields of these classes.
But testing using ORM doesn’t give access to the fields in the class TPersonFullName. There is no error or warnings when adding records But the fields Name_First, Name_Middle, and Name_Last will not be updated in the table  “PersonContactable” but all other fields are updated.
I’ve checked all I can think of but can not make this work. Strange that ORM creates the fields from TPerson-TPersonFullName but does not allow access to the fields from TPersonFullName.
I wonder what I may be missing, I’m sure this should work automatically if I did everything wright.
Hope anyone could spread some light over this.

In my little test project running some regression tests, I found that fields that have the type "currency" will not be updated on the database.
I thought it should work with "currency" but in my case apparently not. If I change type "TPrice" to be of type "double" it works.
Am I doing something wrong or should I avoid type "currency"?

---- A crippled piece of the code i use  ----

type
  TPrice                    = type currency;
  TDiscount                 = type double;
  TWeight                   = type double;
-------------------------------------------------

  TSQLRecordProduct = class(TSQLRecord)
  protected
    fweight: double; // TWeight
    fdiscount: currency; // TDiscount
    fPrice: double;//currency; // TPrice
  published
    property weight: double read fweight write fweight;
    property discount: double read fdiscount write fdiscount;
    property price: double read fPrice write fPrice;
  end;
--------------------------------------------------
  TProduct = class(TSynAutoCreateFields)
  private
    fWeight: TWeight;
    fPrice: TPrice;
    fDiscount: TDiscount;
  public
    constructor create(const aName: TProductName; const aWeight: TWeight; const aPrice: TPrice;
                       const aDiscount: TDiscount); overload;
  published
    property weight: TWeight read fWeight write fWeight;
    property price: TPrice read fPrice write fPrice;
    property discount: TDiscount read fDiscount write fDiscount;
  end;
    TProductObjArray = array of TProduct;
   
------------- part of server code --------
RestServer := TSQLRestExternalDBCreate( TSQLModel.Create([TSQLRecordProduct]),
RestServer.ServiceContainer.InjectResolver([TProductRepoFactory.Create(RestServer)],true);
   
---------------------- Test code below --------------------
var p1: TProduct;

p1 := TProduct.Create;
p1.weight := 12.3;
p1.price :=  249.99;
p1.discount := 25;
aCQRSRes := cmd.Add(p1);
Check(cqrsSuccess = aCQRSRes);
Check(cqrsSuccess = cmd.Commit);

p1.price is not persisted.

We have a huge system containing several hundreds of tables and I tries to replace pieces of delphi-code by using mORMot and the DDD-concept.
When I do some testing I find that it won't work when the tables don't contain the ID-field. Which is true in most tables.

I can not access these tables without getting the "Invalid column name 'ID'" - error
In the implementation of the service I'm intend not to use ORM on these tables to avoid such problems. But that's not enough.

Even the call of "CreateMissingTables" returns this error. So I wonder if there is a solution for this or if I need to treat these tables in a total different way?