Cant perform saving of object with JSON database field ?

BIZ TMS Aurelius
1

Hello,
i can't figure out how to save object in database which have field of json type
I have simple table:

CREATE TABLE public.data_import (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	importtype int4 NULL,
	import_data json NULL,
	CONSTRAINT data_import_pk PRIMARY KEY (id)
);

And object

  TDataImport = class(TMoonshineObject)
  private
    [Column('id', [TColumnProp.Required])]
    [Description('')]
    FID: Integer;
    [Column('importtype', [])]
    [Description('')]
    FImportType: TImportType;

    [Column('import_data', [TColumnProp.Lazy])]
    [DBTypeMemo]
    FImportData: TBlob;
  public
    property Id: Integer read FID write FID;
    property ImportType: TImportType read FImportType write FImportType;
    property ImportData: TBlob read FImportData write FImportData;
  end;

Further in my code i invoke inserting in database of several objects which fail with error:

...raised exception class EPgNativeException with message 
'[FireDAC][Phys][PG][libpq] ERROR: column "import_data" is of type json 
but expression is of type text.
You will need to rewrite or cast the expression.'.

This is how i insert object

    diObject := TDataImport.Create;
    try
      diObject.ImportType := itFDManufacturer;
      diObject.ImportData.AsString := line;
      ObjectManager.Save(diObject);
    finally
      diObject.Free;
    end;

What i do wrong ?

2

Note that Aurelius doesn't save or load anything in the database by itself. It always use another database component for that - in your case, FireDAC.

I'd suggest you first learn how FireDAC itself handles JSON fields. What parameter type does it need to properly save the JSON value? ftMemo? ftBlob? When doing a SELECT, how does FireDAC retrieves the field type? ftMemo? ftString? ftBlob?

That would give a hint about how to map it in Aurelius. It might be a TBlob with [DBTypeMemo], like you did. Or maybe just a regular TBlob without extra attributes.

3

When we do select FireDac retrieves field type as ftWideMemo. However when you do insert you need to cast it something like that:

   FramesData := TStringList.Create;
    FDQuery2.Active := false;
    FramesData.LoadFromFile('Manufacturers.json');
    FDQuery2.SQL.Clear;
    FDQuery2.SQL.ADD('INSERT INTO public.data_import ');
    FDQuery2.SQL.ADD('  (importtype, import_data) ');
    FDQuery2.SQL.ADD('VALUES (:importtype, :import_data::json);');
    line := FramesData.Text;
    FDQuery2.Params[0].AsInteger := 1;
    FDQuery2.Params[1].DataType :=ftWideMemo;
    FDQuery2.Params[1].asString := line;
    FDQuery2.Params[1].Size := Length(line);
    FDQuery2.ExecSQL;

Using simple sql like

INSERT INTO public.data_import (importtype, import_data) VALUES (:importtype, :import_data)

generate similar error

Is there a way to tell aurelius to generate sql lwhich do cast as json ?

4

What is the cast you are referring to, exactly?

If FireDAC can save the JSON as a simple ftWideMemo parameter, then it should work normally, that's what Aurelius does when you flag the field as [DBTypeWideMemo].

5

When you write FireDAC SQL like this:

INSERT INTO public.data_import (importtype, import_data)
VALUES (:importtype, :import_data::json)

You cast second parameter to be json type (not text as you provide it ) and it work fine.
On mOrmot i can tweak queries but here in Aureilius i don't see such option.

6

You can tweak queries using the OnSqlExecuting event.

7

Thank you, i just try it adding this method

initialization
TMappingExplorer.Default.Events.OnSqlExecuting.Subscribe(
  procedure(Args: TSQLExecutingArgs)
  var
    i: integer;
    strline: string;
    Param: TDBParam;
  begin
    if pos('insert into data_import', LowerCase(Args.SQL)) > 0 then
    begin
      Args.SQL :=  stringreplace(Args.SQL, 'p3', 'p3::json', [rfIgnoreCase]);
      strline := '';
      for Param in Args.Params do
      begin
        strline := strline + param.ParamName+'= '+param.ToString+#$D#$A;

      end;
      ShowMessage(strline);
    end;

  end
);

It looks like it is work fine because previous error disappeared but this event fire on every transaction to db which i make. Is it possible to make him active only for specific objects ?

Also now i get this error:

ERROR: cannot insert a non-DEFAULT value into column "id".
Column "id" is an identity column defined as GENERATED ALWAYS..
Use OVERRIDING SYSTEM VALUE to override.'.

I checked and see that it provides value for ID of TDataImport class

  [Entity]
  [Table('data_import')]
  [Description('')]
  [Sequence('data_import_id_seq')]
  [Id('FID', TIdGenerator.IdentityOrSequence)]

  TDataImport = class(TMoonshineObject)
  private
    [Column('id', [TColumnProp.Required])]
    [Description('')]
    FID: Integer;
    [Column('importtype', [])]
    [Description('')]
    FImportType: TImportType;

    [Column('import_data', [TColumnProp.Lazy])]
    [DBTypeWideMemo]
    FImportData: TBlob;
  public
    property Id: Integer read FID write FID;
    property ImportType: TImportType read FImportType write FImportType;
    property ImportData: TBlob read FImportData write FImportData;
  end;

I thought in case of identity field it should not send value for ID

8

No, it's fired for every SQL executed by Aurelius.

Since you defined a Sequence attribute for your class, Aurelius will use the sequence to retrieve the next value and use it in the INSERT statement (if the database supports sequences). If you don't want to use sequences and want Aurelius to leave the id generation to the database, then remove the Sequence attribute.