#1 Re: mORMot 1 » TransactionBegin and BatchStart -> right way? » 2017-05-23 11:57:03

ab wrote:

Never kill the connection while it is still to be used by the ORM.

Okay, but what should happen if the network is down for a couple of minutes!? oder the MySQL service is broken and down? How can mORMot handle this?

#2 Re: mORMot 1 » TransactionBegin and BatchStart -> right way? » 2017-05-23 10:19:42

ab wrote:

If you put 10000 for AutomaticTransactionPerRow ?

I start the batch with 3000 for AutomaticTransactionPerRow  and do BatchAdd for about 250 000 elements. Then I call .BatchSend and it is being executed about a 1 minute. During the executing I go to the mysql database and KILL the connection. Of course the mORMot is interrupted with the exceptions. Everything is good until the point where the object of TSQLDBFireDACConnectionProperties will be freed. At freeing I get an exception at ConnectionPool.Clear.


Maybe I am explaining everything confused..... The problem is...

  fModel := CreateModel;
  fServer := TSQLRestServerDB.Create(fModel, ':memory:', False);
  fServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

  fClient := TSQLRestClientDB.Create(fServer);
  fClient.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

   ....   EXECUTING SOME BATCH ...... AND DURING THE QUERY I KILL THE CONNECTION IN THE MySQL DATABASE ....
   
  fClient.Free;
  fServer.Free;    <-------------------- here EXCEPTION "MySQL should be connected"
  fModel.Free;   

#3 Re: mORMot 1 » TransactionBegin and BatchStart -> right way? » 2017-05-23 10:09:14

ab wrote:

As documented, the transaction should NOT be made on client side.
The transaction is made automatically on the server side, using the AutomaticTransactionPerRow parameter of BatchStart.

Get rid of your manual transaction commands, which conflicts with your AutomaticTransactionPerRow  = 3000 parameter.

What server do you mean? TSQLRestServerDB?

So, something like TSQLRestServerDB.TransactionBegin??

#4 Re: mORMot 1 » TransactionBegin and BatchStart -> right way? » 2017-05-23 07:57:54

ab wrote:

As documented, the transaction should NOT be made on client side.
The transaction is made automatically on the server side, using the AutomaticTransactionPerRow parameter of BatchStart.

Get rid of your manual transaction commands, which conflicts with your AutomaticTransactionPerRow  = 3000 parameter.


Okay, thanks, and what about the 2nd one exception if I am executing the batch without transaction?

Why do I get the exception at ConnectionPool (see the code in the previous post)?

#5 mORMot 1 » TransactionBegin and BatchStart -> right way? » 2017-05-23 05:03:52

cypriotcalm
Replies: 7

Hi,

is it a right way to do it?

try
  if fClient.TransactionBegin(RecordTable) then
  begin
    fClient.BatchStart(RecordTable, 3000);
    
    // this loop takes 20 seconds
    for I to 3000 do
    begin
      fClient.BatchAdd() ;
    end;

    BatchResult := fClient.BatchSend(ResultIDs);     <----------------------  I am getting here TransactionBegin Timeout
    Assert(BatchResult = HTTP_SUCCESS);
    fClient.Commit;
  end;
except
  on E:Exception do
  begin
    fClient.Rollback;
    raise;
  end;
end;

Where can I set the timeout?



if I am using the code below I am getting an exception in SynDB

destructor TSQLDBConnectionPropertiesThreadSafe.Destroy;
begin
  inherited Destroy; // do MainConnection.Free
  fConnectionPool.Free;                                          <-------------- mORMot exception "MySQL should be connected", [FireDAC] connection must be active
  DeleteCriticalSection(fConnectionCS);
end;

the code which I am using

        if fClient.BatchStart(RecordTable, 3000) then
        begin
          for I := 0 to 3000 do
          begin
            ....
          end;
          // Daten in die MDB schicken
          BatchResult := fClient..BatchSend(ResultIDs);
          Assert(BatchResult = HTTP_SUCCESS);
        end;
      except
        on E:Exception do
        begin
          E.Message := fClient.LastErrorMessage + 'My custom message';
          raise;
        end;
      end;

If I have loosed the connection, how can be it active?!

Could you help me pleas? Thank you!


And now I am getting this exception if I kill the mysql connection

{
"errorCode":500,
"error":
{"msg":"did break MyRecTable BATCH process","EORMException":{
	"ClassName":"EORMException",
	"Address":"0398DD00",
	"Message": "TSQLRestStorageExternal.InternalBatchStop(MyRecTable ).BatchMethod=mNone"
}}
}

#6 Re: mORMot 1 » Creating MySQL tables fails in case of using symlink » 2016-08-02 12:16:58

Hell AB,

are there some news for this problem?

Thx!
cc

#7 mORMot 1 » Creating MySQL tables fails in case of using symlink » 2016-07-25 15:51:10

cypriotcalm
Replies: 2

Hello ab,

my mysql database is per symlink connected. In this case mORMot can't create new tables through VirtualTableExternalRegister.

I suppose, it's because of the another error code which is

"Can't find file: 'C:\MySQL\MySQL Server 5.6\data\<mydatabase>\<mytable>.frm' (errno: 175 - File to short; Expected more data in file)"


Could you please fix that? Thank you very much!


Best regards,
cc

#8 mORMot 1 » TSQLDBConnectionProperties and freeing NewConnections » 2016-03-04 11:43:37

cypriotcalm
Replies: 2

Hi there!

I have the following code:

var
  ConnectionProperties: TSQLDBConnectionProperties;
  Conn1, Conn2: TSQLDBConnection;
begin
  ConnectionProperties := TSQLDBFireDACConnectionProperties.Create(
    'MySQL?Server=127.0.0.1;Port=3306',
    'db',
    'root',
    'pass');
  Conn1 := ConnectionProperties.ThreadSafeConnection;
  Conn2 := ConnectionProperties.NewConnection;
  try
    Conn1.Connect;
    Conn2.Connect;
  finally
    Conn1.Free;
    Conn2.Free;
    ConnectionProperties.Free; // ---> ERROR invalid pointer operation because Conn1 was freed before
  end;
end;

My question is: why is the memory management of .ThreadSafeConnection and .NewConnection handeled in different ways? Is it a bug or a feature? .ThreadSafeConnection calls internally the .NewConnection.

Thank you!

Best regards,
cc

#9 mORMot 1 » FireDAC pooled connections » 2016-03-01 11:39:51

cypriotcalm
Replies: 0

Hello!

It is possible to use pooled connections in FireDAC setting Pool to True in the connection parameters. How can I use this feature in mORMot? How can I set this property?

Thank you!

#10 Re: mORMot 1 » batch delete » 2016-02-15 08:22:47

ab wrote:

What is you GetClientDB class type?

TSQLRestClientDB

#11 Re: mORMot 1 » batch delete » 2016-02-12 08:58:56

ab wrote:

Use the internal TSQLRecord property name, which may be just ID in your case.
The framework should map it to the expected external column name.

Yes, I map the ID filed. And I tried both:

  // 1.
  GetClientDB.Delete(GetSQLRecordClass, GetPrimaryKeyFieldname + ' IN (1,2,3)');
  or the same
  GetClientDB.Delete(GetSQLRecordClass, GetClientDB.Model.Props[GetSQLRecordClass].ExternalDB.RowIDFieldName + ' IN (1,2,3)'); --------> error Error SQLITE_ERROR using 3.10.0 no such column: MyTableID
  // 2.
  GetClientDB.Delete(GetSQLRecordClass,  'ID IN (1,2,3)'); -----> error [FireDAC][Phys][MySQL] Unknwon column ID in where clause

What kind of problem could it be!?

#12 Re: mORMot 1 » batch delete » 2016-02-11 16:52:53

ab wrote:

Use TSQLRest.Delete() with a WHERE clause having "in (....)":

function TSQLRest.Delete(Table: TSQLRecordClass; const SQLWhere: RawUTF8): boolean;

I tried

   ...
   GetClientDB.Delete(GetSQLRecordClass, GetPrimaryKeyFieldname + ' IN (1,2,3)');
   ...

I have an error like "no such column IdMyTablename ..."

What could it be?

#13 mORMot 1 » Batch Insert How to Get ResultIDs on Exception » 2016-02-11 16:13:39

cypriotcalm
Replies: 0

Hello!

I save data in a batch mode into a MySQL table (MyISAM without transaction support). After a data batch is saved the result IDs should be assigned to the objects. The problem is if an exception is raised during the execution of BatchSend I didn't have any IDs in the Results array. So, the saved data is inconsistent, but I can not delete the data which was saved before the exception raised because I don't have any IDs in Results.

How could I handle the situation like this? Is there a better approach to do it?

procedure TDataAccessService<T>.SaveList(const AList: IList<T>);
var
  ClientDB: TSQLRest;
  SQLQuery: TSQLRecord;
  Data: IDataAccess;
  Batch: TSQLRestBatch;
  I: Integer;
  ResCode: Integer;
  Results: TIDDynArray;
begin
  Batch := nil;
  SQLQuery := nil;
  try
    ClientDB := GetClientDB;
    Batch := TSQLRestBatch.Create(ClientDB, GetSQLRecordClass);
    SQLQuery := GetSQLRecordClass.Create;
    for Data in AList do
    begin
      Batch.Add(DataToSQLQuery(Data, SQLQuery), True);
    end;

    ResCode := ClientDB.BatchSend(Batch, Results);

    if (ResCode <> HTML_SUCCESS) then
      raise Exception.Create(StatusCodeToErrorMsg(ResCode));

    for I := Low(Results) to High(Results) do
    begin
      Data := AList.Items[i];
      Data.ID := TDBKey(Results[i]);
      UpdateBlob(Data);
    end;
  finally
    FreeAndNil(SQLQuery);
    FreeAndNil(Batch);
  end;
end;

Thank you in advance for your help!

#14 mORMot 1 » batch delete » 2016-02-11 14:08:46

cypriotcalm
Replies: 6

Hello!

I want to delete data from a MySQL table in a batch mode by ID. There are about 300 000 (three hundred thousand) I have the following code:

procedure TDataAccessService<T>.DeleteListByID(const AList: IList<T>);
var
  ClientDB: TSQLRest;
  Batch: TSQLRestBatch;
  DataObj: IDataAccess;
begin
  ClientDB := GetClientDB;
  Batch := TSQLRestBatch.Create(ClientDB, GetSQLRecordClass);
  try
    for DataObj in AList do
    begin
      Batch.Delete(DataObj.ID);
    end;
    ClientDB.BatchSend(Batch);
  finally
    FreeAndNil(Batch);
  end;
end;  

1. Only 4096 data records are sent in one batch. How can I change this?
2. After BatchSend for each data record to be deleted one sql statement is executed, i.e. "delete from <table> where <ID> = 1" and the same 300 000 times. This is very slow. How can I avoid this?

I tried with a custom SQL statement like "delete from <table> where <ID> in (1,2,3,4,5,6, ...)"; this approach is more efficient. But I don't want to use sql directly.

How can I delete the data fast using the mORMot?

Thank you for your help!

#15 Re: mORMot 1 » Nullable types for mORMot » 2016-01-15 12:41:18

Hi there!

in order to get rid of the error above you have to change the default parameter data type in FireDAC to Variant

  FDManager.FormatOptions.DefaultParamDataType := Data.DB.TFieldType.ftVariant;

Have a good day! :-)

#16 Re: mORMot 1 » Exception in Destroy in FMX project » 2016-01-14 13:20:19

destructor TSQLModel.Destroy;
var i,j: integer;
begin
  for i := 0 to fTablesMax do begin
    with TableProps[i].Props do begin
      EnterCriticalSection(fLock); // may be called from several threads at once   <---- THEN EXCEPTION HERE
      try
        for j := 0 to fModelMax do
          if fModel[j].Model=self then begin
            // un-associate this TSQLRecord with this model
            Move(fModel[j+1],fModel[j],(fModelMax-j)*sizeof(fModel[j]));
            dec(fModelMax);
            break;
          end;
        TableProps[i].Free;
      finally
        LeaveCriticalSection(fLock);
      end;
    end;
  end;
  inherited;
end;

Is it okay when "EnterCriticalSection(fLock)" in the code above accesses the fLock property of the class TSQLRecordProperties?! Or is it a bug?

  TSQLRecordProperties = class
  protected
    ...
    fLock: TRTLCriticalSection;
    ...
  public

#17 Re: mORMot 1 » Nullable types for mORMot » 2015-12-11 09:44:53

Hello,

this is a nice feature! But a question, how can I save a NULL value. I am using MySQL+FireDAC.

TSQLCar = class(TSQLRecord)
public
  Color: TNullableUTF8Text;
end;

Reading from DB is good if the filed is NULL. As a result I get in the object an empty string.

But writing! I am doing the following:

Car.Color := ''; // -> it saves the empty string, not NULL

If I execute the following code, I get from FireDAC an exception something like "[FireDAC][Phys][MySQL]Datatype of the parameter is unknown...Please provide the TFDParam.DataType..."

Car.Color := NULL; // -> Exception
or 
Car.Color := NullableUTF8TextNull; // -> Exception

What am I doing wrong? Thanky you for the help!

#18 Re: mORMot 1 » TSQLRecord and BindArray » 2015-12-07 10:19:50

Thank you for your quick answer! :-)

#19 mORMot 1 » TSQLRecord and BindArray » 2015-12-07 09:39:03

cypriotcalm
Replies: 2

Hello,

is it possible to bind an array in a TSQLRecord-class?

Or what is the best way to do the following task?

I have a class and I would like to select all cars which IDs I pass in a list.

  TCar = class;
  TSQLCar = class(TSQLRecord);

  function GetCars(const AIDList: TList<Integer>): TList<TCar>;
  begin
    // My question here is how can I pass in the best way the IDs from the list?
    SQLCar := SQLCar.CreateAndFillPrepare(ClientDB, 'ID = ?');
    ...
  end;

The most obvious solution would be to iterate the list, create a comma separated ID-string and do something like 'ID IN (1,2,3,4)'. Is it a good way to do it?

What about the strings which should be escaped? E.g. 'Name IN (...)'

How can I parameterize a TSQLRecord?

Thank you for your help!

Greets CC

#20 Re: mORMot 1 » DateTime format in URI? » 2015-11-09 08:16:54

@Daniel
I've tried with the ''s :-) nothing happened :-(

@ab
is there some news about the error? is it a bug or a feature? ;-) Thanks for your help anyway! :-)

#21 Re: mORMot 1 » DateTime format in URI? » 2015-11-06 19:07:55

danielkuettner wrote:

Try to replace the "T" with " ".

It doesn't work :-(

#22 Re: mORMot 1 » DateTime format in URI? » 2015-11-06 17:20:26

ab wrote:

Sounds like an URI encoding problem.

Try to POST the date value as a JSON object in a HTTP body.

Done!

Request:

http://localhost:888/root/CustomDateTimeService.GetValues

BODY:
{
  "SpecificDateTime":"2015-05-01T23:59:23"
}

Response:

{
  "errorCode": 406,
  "errorText": "Shared execution failed (probably due to bad input parameters) for ICustomDateTimeService.GetValues"
}

#23 Re: mORMot 1 » DateTime format in URI? » 2015-11-06 17:06:20

mrgcms wrote:

Should CustomDateTimeService.GetValues not be CustomDateTimeService/GetValues?

No, because it is an interface based service

#24 mORMot 1 » DateTime format in URI? » 2015-11-06 16:58:14

cypriotcalm
Replies: 9

Hello,

the call in a browser returns nothing

  http://localhost:888/root/CustomDateTimeService.GetValues?SpecificDateTime=2015-05-01T23:59:23

On the server side I am getting the value of TDateTime  is zero.

Is this a bug, or how should I pass the DateTime?

Thank you!


Greets CC

#25 Re: mORMot 1 » Problems serializing TObjectList » 2015-10-16 16:09:53

Here is the reader method for the EstadoService

  IEstadoService = interface(IInvokable)
  ['{A56EA833-873A-4AA5-9786-DA7DC1A7D014}']    
    function GetEstados(out AList: TEstadoObjectList): boolean;
    procedure SaveEstados(const AList: TEstadoObjectList);
  end;

...

procedure TEstadoDataService.SaveEstados(const AList: TEstadoObjectList);
var
  I: Integer;
begin
  TRY
    if (AList.Count > 0) then
    begin
      Item1Name := AList[0].Name;
    end;
  FINALLY
    for I := (AList.Count-1) downto 0 do
    begin
      AList[i].Free;
      AList[i] := nil;
      AList.Delete(I);
    end;
  END;
end; 

...

  // THE READER 
  class function TJSONSerializerEstado.ReadJSON(const aValue: TObject; aFrom: PUTF8Char; var aValid: Boolean; aOptions: TJSONToObjectOptions): PUTF8Char;
  var
    I: Integer;
    ML: TEstadoObjectList;
    Estado: TEstado;
    JSONArray, JSONArrayRoot: TJSONArray;
    JSONObject: TJSONObject;
    lst: TEstadoObjectList;
    JSONString: string;
  begin
    aValid := false;
    Result := PUTF8Char('['+aFrom);
    if (aValue is TEstadoObjectList) then
    begin
      JSONArray := nil;
      JSONArrayRoot := nil;
      TRY
        JSONArrayRoot := TJSONObject.ParseJSONValue(Result) as TJSONArray;
        if Assigned(JSONArrayRoot) and (JSONArrayRoot.Count > 0) then
        begin
          JSONArray := JSONArrayRoot.Items[0] as TJSONArray;
          if Assigned(JSONArray) and (JSONArray.Count > 0) then
          begin
            ML := TEstadoObjectList(aValue);
            ML.Capacity := JSONArray.Count;
            for I := 0 to JSONArray.Count-1 do
            begin
              JSONString := JSONArray.Items[i].ToJSON;
              if (JSONString <> '') then
              begin
                Estado := TJson.JsonToObject<TEstado>(JSONString);
                if Assigned(Estado) then
                begin
                  ML.Add(Estado);
                end;
              end;
            end;
            aValid := true;
          end;
        end;
      FINALLY
        FreeAndNil(JSONArrayRoot);
      END;
    end;
  end;

Your POST request should look like this:

[[{
	"iD": 1,
	"name": "Estado 1"
},
{
	"iD": 2,
	"name": "Estado 2"
},
{
	"iD": 3,
	"name": "Estado 3"
}]]

#26 Re: mORMot 1 » Bug in TServiceMethodExecute.ExecuteJson by validatin input parameters » 2015-10-16 15:36:59

ab wrote:

Parameters are transmitted as a json array.

So you have to put your array parameter within another json array.


Okay, I have just tried this option

[[
      {
        "iD": 1,
        "name": "a"
      },
      {
        "iD": 2,
        "name": "b"
      }
]]

still missing the first bracket...

Could you please provide a right example of this array? Thank you!

#27 mORMot 1 » Bug in TServiceMethodExecute.ExecuteJson by validatin input parameters » 2015-10-16 15:02:45

cypriotcalm
Replies: 2

Hi Ab,

if I POST the following JSON

[
      {
        "iD": 1,
        "name": "a"
      },
      {
        "iD": 2,
        "name": "b"
      }
]

the first square bracket is removed by invalidating input parameters.

...
                              if Par^ in [#1..' '] then repeat inc(Par) until not(Par^ in [#1..' ']);
                              case Par^ of
                              '[': // input arguments as a JSON array , e.g. '[1,2,"three"]' (default)
 THIS REMOVES THE BRACKET-->       inc(Par);
                              '{': begin // retrieve parameters values from JSON object
...

#28 Re: mORMot 1 » Problems serializing TObjectList » 2015-10-16 12:20:38

In order to use generics with the mORMot server you could do as followed:

uses
  System.Generics.Collections;
  System.SysUtils,
  System.JSON,
  REST.Json,
  mORMot,
  SynCommons;
  
type
  TEstadoObjectList = class(TObjectList<TEstado>);

  IEstadoService = interface(IInvokable)
  ['{A56EA833-873A-4AA5-9786-DA7DC1A7D014}']
    function GetEstados(out AList: TEstadoObjectList): boolean;
  end;

  TEstadoService = class(TInterfacedObject, IEstadoService)
  public
    function GetEstados(out AList: TEstadoObjectList): boolean;
  end; 

 TJSONSerializerEstado = class(TObject)
  public
    class function ReadJSON(const aValue: TObject; aFrom: PUTF8Char; var aValid: Boolean; aOptions: TJSONToObjectOptions): PUTF8Char;
    class procedure WriteJSON(const aSerializer: TJSONSerializer; aValue: TObject; aOptions: TTextWriterWriteObjectOptions);
  end;

implementation

  class function TJSONSerializerEstado.ReadJSON(const aValue: TObject; aFrom: PUTF8Char; var aValid: Boolean; aOptions: TJSONToObjectOptions): PUTF8Char;
  begin
    // not yet written...
  end;

  class procedure TJSONSerializerEstado.WriteJSON(const aSerializer: TJSONSerializer; aValue: TObject; aOptions: TTextWriterWriteObjectOptions);
  var
    I: Integer;
    EL: TEstadoObjectList;
    JSONObject: TJSOnObject;
  begin
    EL := TEstadoObjectList(aValue);
    JSONObject := nil;
    TRY
      JSONObject := TJson.ObjectToJsonObject(EL);
      aSerializer.AddString(JSONObject.Values['items'].ToJSON);
    FINALLY
      for I := (EL.Count-1) downto 0 do
      begin
        EL[i].Free;
        EL[i] := nil;
        EL.Delete(I);
      end;
      FreeAndNil(JSONObject);
    END;
  end;

function TEstadoService.GetEstados(out AList: TEstadoObjectList): boolean;
var
  Estado: TEstado;
  I: Integer;
begin
  AList.Capacity := 10;
  for I := 1 to AList.Capacity do
  begin
    Estado := TEstado.Create;
    Estado.ID := 123456;
    Estado.Name := 'abcdefg';
    AList.Add(Estado);
  end;
  Result := true;
end;


initialization
  TJSONSerializer.RegisterCustomSerializer(TEstadoObjectList, TJSONSerializerEstado.ReadJSON, TJSONSerializerEstado.WriteJSON);

  TJSONSerializer.RegisterClassForJSON([TEstadoObjectList]);

  TInterfaceFactory.RegisterInterfaces([TypeInfo(IEstadoService)]);

finalization

Then, you can call your service as http://localhost:8080/root/EstadoService/GetEstados and you will get something like:

{
  "result": [
    [
      {
        "iD": 123456,
        "name": "abcdefg"
      }

     ...

    ],
  ],
  "id": 1
}

I hope, it helps! :-)

#29 Re: mORMot 1 » assertion failed in SynCrtSock in row 5284 » 2015-10-16 11:52:30

Great! It works! Thank you!



But I have here another Problem! Maybe you know what is going wrong! The interesting thing is that it has worked a couple days ago :-(

In the application I use a FireDAC connection to an external MySQL database. If I start the server-exe everything is working fine, if I start the server from the IDE, I get two successive erros:

1) EMySQLNativeException [FireDAC][Phys][MySQL]: Unknown MySQL server host "localhost" (0)
2) ESQLite3Exception [FireDAC][Phys][MySQL]: Unknown MySQL server host "localhost" (0)

Do you have some suggestions?

#30 mORMot 1 » assertion failed in SynCrtSock in row 5284 » 2015-10-15 14:11:33

cypriotcalm
Replies: 2

Hi Ab,

why do I get this assertion failed?!

  function RetrieveHeaders(const Request: HTTP_REQUEST; out RemoteIP: SockString): SockString; 

  ...

  if RemoteIP<>'' then begin
    move(REMOTEIP_HEADER[1],D^,REMOTEIP_HEADERLEN);
    inc(D,REMOTEIP_HEADERLEN);
    move(pointer(RemoteIP)^,D^,length(RemoteIP));
    inc(D,length(RemoteIP));
    PWord(D)^ := 13+10 shl 8;
  end;
  {$ifopt C+}
  inc(D,2);
  assert(D-pointer(result)=L);
  {$endif}

   ...

I don't know why but the variable RemoteIP is empty!?

If I start your sample project "\04 - HTTP Client-Server\Project04Server.dproj", everything is okay.

There is an error if I start the exe from the IDE. Otherwise if I run the exe as administrator everythig is fine.

What could be reasons?!

#31 Re: mORMot 1 » How to access TSQLDBConnectionProperties in TSQLRest objects? » 2015-10-12 13:11:51

Thank you for your advices! I will do it on the proper and right way! :-)

#32 Re: mORMot 1 » How to access TSQLDBConnectionProperties in TSQLRest objects? » 2015-10-09 10:09:13

ab wrote:

This process should be done at the TSQLRest level, not at TSQLCustomer level.

i.e. should create my own TSQLRestServerDB inherited from the TSQLRestServerDB and call in it VirtualTableExternalRegister and .ExternalDB.MapField?

#33 Re: mORMot 1 » How to access TSQLDBConnectionProperties in TSQLRest objects? » 2015-10-09 09:50:23

ab wrote:

You are mixing here the stored values (i.e. TSQLRecord inherited class), and the mean of storage (TSQLRest).

IMHO you should not define such an overloaded constructor to TSQLCustomer, but create a factory function in your own TSQLRestServer instance.

The problem is that I don't know if a database and a table already exist at the time I want to work with them. Their dynamic names are only known at runtime. So, the mORMot should create them if they not exists. How can I achiche this with mORMot?

#34 mORMot 1 » How to access TSQLDBConnectionProperties in TSQLRest objects? » 2015-10-09 07:08:41

cypriotcalm
Replies: 6

I initialize mORMot like below

  FConnProps := TSQLDBFireDACConnectionProperties.Create(GetServer, GetDatabase, GetUsername, GetPassword);

  FSQLModel := TSQLModel.Create([TSQLCustomer]);

  VirtualTableExternalRegister(FSQLModel, [TSQLCustomer], FConnProps, 'CUSTOMER');

  FRestServerDB := TSQLRestServerDB.Create(FSQLModel, false);
  FRestServerDB.CreateMissingTables();

  FRestClientDB := TSQLRestClientDB.Create(FRestServerDB);

I have also the client code like

  Customers := TSQLCustomer.CreateAndFillPrepare(FRestClientDB, 'SPECIFIC_CUSTOMER_DATABASE', 'SPECIFIC_CUSTOMER_TABLE', 'Company = "mORMot"')
  try
    while Customers.FillOne do
    begin
      // ...
    end; 
  finally
    Customers.Free;
  end;

In the class TSQLCustomer I have the following constructor and would like to have the access to the external db connection properties. Can you built it in?

  TSQLCustomer = class(TSQLRecord);

  TSQLCustomer.Create(aClient: TSQLRest; const aDatabase, aTable: string; const aSQLWhere: RawUTF8); overload; virtual;
  begin
    ConProps := TSQLRestClientDB(aClient).Server.ConnectionDefinition;
    // OR better
    ConProps := TSQLRestClientDB(aClient).Server.ConnectionProperties;
  end;

In the current version of mORMot if I call the code below the returned TSynConnectionDefinition object is empty :-(

  TSQLCustomer.Create(aClient: TSQLRest; const aDatabase, aTable: string; const aSQLWhere: RawUTF8); overload; virtual;
  var
    ConnDef: TSynConnectionDefinition;
  begin
    ConnDef := TSynConnectionDefinition.Create;
    try
      TSQLRestClientDB(aClient).Server.DefinitionTo(ConnDef);
      // ConnDef --> DOESN'T HAVE ANYTHING
    finally
      ConnDef.Free;
    end;
  end;  

Could you fix it? Or what am I doing wrong?


I hope, this improvement will be helpful for the mORMot framework in general! Thank you, AB, very much for you job! The mORMot is awesome! :-)

#35 Re: mORMot 1 » index creation in the mORMot » 2015-09-17 14:50:40

I want to disable the auto creation of the indexes.

This code works!

  CreateModel
  VirtualTableExternalRegister

  fSQLRestServerDB.CreateMissingTables(0, [itoNoIndex4TID]);

But in this case the auto indexes are created:

  CreateModel
  VirtualTableExternalRegister
  
  fMySQLModel.Table[TMySQLTable.SQLTableName].InitializeTable(fSQLRestServerDB, 'SomeTIDFiled', [itoNoIndex4TID]);

  CreateMissingTables

Is there some issue?

#36 Re: mORMot 1 » field as auto-increment for a MySQL table » 2015-09-17 14:44:30

ab wrote:

...
If you want an auto-incremented field, the TSQLRecord.ID would be one, available on all target databases.

What dou you mean?

Maybe we don't understand each other ;-) I explain my use case:

In generel, I want to have a possibility the mORMot framework define TSQLRecord.ID as an auto-increment ID.

I have en external DB. There are many tables which have the auto-increment ID.

I define the class and let mORMot create the corresponding table:

TMyTable = class(TSQLRecord)
published
  Field1: string;
  Field2: string;
end;
...
fServer.CreateMissingTables();
...

The table "mytable" is created, but the ID field is not auto_increment. I know that not all database support this functionality.

Maybe I repeat myself, but would it make sense to have something like:

  fServer.CreateMissingTables(0, [SetAutoIncrement4TSQLRecordID]);

Sorry for bothering you, maybe it's nonsense what I propose, but anyway ;-)

And thank you for your help!

#37 mORMot 1 » field as auto-increment for a MySQL table » 2015-09-17 12:05:34

cypriotcalm
Replies: 2

Hi Ab,

is it possible to define a field as auto-increment within mORMot? Or should I exceute a custom SQL like "ALTER TABLE <fieldname> auto_increment" after a table was created?

Thank you!

#38 Re: mORMot 1 » JSON result format » 2015-09-11 16:23:54

:-) Ah ok, thank you very much for the explanation!

#39 mORMot 1 » JSON result format » 2015-09-11 15:15:49

cypriotcalm
Replies: 9

Hello!

What is the reason for the JSON result as an object with the array "result" {result:[...]}?

Why can't be returned something like {obj1} for one object and [{obj1},{obj2}] for a list of objects? Is there an important reason for that?

 TFullname = record
   Firstname: String;
   Lastname: String;
 end;

 TFullnames = array of TFullname;

 IFullnameService = interface(IInvokable)
   function GetFullnameArray: TFullnames;
 end;

// result JSON
{  
   result:[  
      [  
         {  
            Firstname:"My",
            Lastname:"SQL"
         },
         {  
            Firstname:"Maria",
            Lastname:"DB"
         }
      ]
   ]
}

// wish
      [  
         {  
            Firstname:"My",
            Lastname:"SQL"
         },
         {  
            Firstname:"Maria",
            Lastname:"DB"
         }
      ]

Thank you for your answer!

#40 Re: mORMot 1 » How to serialize in ObjectToJSONFile an enum data type? » 2015-09-11 06:50:28

Ok, thank you for the answer!

Yes, this workaroung is also my approach, but I wanted to avoid this. Thanks, ab!

#42 Re: mORMot 1 » How to serialize in ObjectToJSONFile an enum data type? » 2015-09-09 06:13:29

Thank you for your help! It works! But only if I change TNumber as follows:

  // NO SERIALIZATION
  TNumber = (
    One = 1,
    Two = 2,
    Three = 3
  );

  // SERIALIZATION
  TNumber = (
    One,
    Two,
    Three
  );

Is it possible to serialize despite of the assigned values?

I would like to use a code like this

  TNumberGreaterThenTen = (
    Ten = 10,
    Twenty = 20,
    Thirty = 30
  );

#43 mORMot 1 » How to serialize in ObjectToJSONFile an enum data type? » 2015-09-08 09:53:16

cypriotcalm
Replies: 6

Hello!

I have the following code:

  TNumber = (
    One = 1,
    Two = 2,
    Three = 3
  );

  TMyNumbers = class
  private
    fNumber: TNumber;
  published
    property Number: TNumber read fNumber; 
  end;

  ...
  var
    MyNumbersObject: TMyNumber;

  ObjectToJSONFile( MyNumbersObject );

  Why doesn't this function serialize the data type? And how can I do it?

  Thank you!

#44 Re: mORMot 1 » How to save efficiently a bunch of blobs into a MySQL table? » 2015-08-19 12:06:38

ab wrote:

I've just refined documentation about BATCH process and BLOB fields.
See http://synopse.info/fossil/info/4324d186e7

Happy it works well for you!

Great! Thank you!

#45 Re: mORMot 1 » How to save efficiently a bunch of blobs into a MySQL table? » 2015-08-19 11:35:39

ab wrote:

UpdateBlob won't be part of the batch!

You have to specify the field names in BatchAdd(), including the blob field.

Thank you, ab! That's it! It works now like a charm! *feeling happy* :-D


The only one question, is this information how to work with batches in the documentation!? Unfortunately, I was not able to find something in the doc. :-(

But a big thank you for your help!!! I reduced the import time of my test data from about 6 minutes down to less than 1 minute! :-)

#46 Re: mORMot 1 » How to save efficiently a bunch of blobs into a MySQL table? » 2015-08-19 06:10:41

ab wrote:

1. Do not use a Pic: TSynPicture variable, but directly the file content from disk into memory using StringFromFile().

2.A Concatenate blobs in a single big binary, send it in one step to the server, then write a dedicated method-based service to de-concatenate those blobs on the server side, and update the blobs within a transaction (and perhaps direct SQL access) at once.

2.B As an alternative, try to use a Batch process, with explicit sending of the blob content with the other fields: blobs would be sent as Base64, but in one step, so it may be worth it.

Hi ab,

thank you for your help!

I have tried to implement 2B, it works, but the blobs are not transmitted. And the documentation says:

"- by default, TSQLRawBlob properties are serialized as null, unless woSQLRawBlobAsBase64 is defined"


I don't understand where I should set this option!?


The 2B implementation looks like:

  TSQLTable= class(TSQLRecord)
  private
    fFirstname: RawUTF8;
    fLastname: RawUTF8;
    fPicture: TSQLRawBlob;
   ...
  end;
   
  ...

  fClient.ForceBlobTransfert := True;
  fClient.ForceBlobTransfertTable[TSQLTable] := True; 

  fClient.BatchStart(TSQLTable);

  for I := 0 to aFiles.Count-1 do
  begin
    ...
    DataTable.Firstname:= 'John';
    DataTable.Lastname := 'Dow';
    DataTable.Picture := StringFromFile(aFiles[i]);

    fClient.BatchAdd(DataTable, True);
    ...
  end;

  fClient.BatchSend(ResultIDs);

I have tried also both TSQLRawBlob and RawByteString, but the same result the blobs are not sent.

I have also tried this variant:

  ...
    DataTable.Firstname:= 'John';
    DataTable.Lastname := 'Dow';

    RecID := fClient.BatchAdd(DataTable, True);
    fClient.UpdateBlob(TSQLTable, RecID, 'Picture', StringFromFile(aFiles[i]));
  ...

Nothing works! :-(

What am I doing wrong!?

Thank you for your help!

#47 mORMot 1 » How to save efficiently a bunch of blobs into a MySQL table? » 2015-08-17 20:08:37

cypriotcalm
Replies: 6

Good evening! :-)

I have a bunch of pictures stored as files and I want to save all of them into the MySQL table.

I have a code which works good but slow for a big amount of data. How could the following code be improved in respect of speed? It would be great if somebody could help me!

I am using FireDAC.

var
  Pic: TSynPicture;
  PicRawBlob: TSQLRawBlob;
  RecID: TID;
begin

  for I := 0 to aFiles.Count-1 do
  begin
    ...
    Pic.LoadFromFile(aFiles[i]);
    SaveAsRawByteString(Pic, PicRawBlob, gptJPG);
    RecID := fClient.Add(TSQLTable, True);
    fClient.UpdateBlob(TSQLTable, RecID, 'BlobField', PicRawBlob);
    ...
  end;

end;

#48 Re: mORMot 1 » How transfer a TSQLRawBlob field as JSON » 2015-07-28 20:37:54

ab wrote:

AFAIK, in Base64 encoding, the incoming length should always be a multiple of 4 bytes, by design.

To be honestly, I don't know, but the value is still emtpy :-(

#49 Re: mORMot 1 » How transfer a TSQLRawBlob field as JSON » 2015-07-28 18:55:43

ab wrote:

Try with RawByteString type.

I've tried it, the value is still empty. I used the debugger and discovered this code in the unit SynCommons:

function Base64ToBinLength(sp: PAnsiChar; len: PtrInt): PtrInt;
var Table: ^TConvertBase64ToBinTable absolute ConvertBase64ToBin;
begin
  if (len=0) or (len and 3<>0) then begin <--- COULD BE THIS CHECK AN ISSUE??
    result := 0;
    exit;
  end;
  if ConvertBase64ToBin=nil then
    InitConvertBase64ToBin;
  if Table[sp[len-2]]>=0 then
    if Table[sp[len-1]]>=0 then
      result := 0 else
      result := 1 else
      result := 2;
  result := (len shr 2)*3-result;
end;

#50 Re: mORMot 1 » TObjectList<T> as a service method parameter » 2015-07-28 18:47:21

ab wrote:

Any BLOB would be transmitted as Base64 encoded, so is not very efficient.

What would you recommend to do instead of it in order to get the efficiency?


ab wrote:

In your previous code, instead of calling ObjArrayAdd(), you may just use SetLength() before the loop, since you know how many items there will be, then fill each array item.
It would be slightly faster than ObjArrayAdd(), which is re-allocating the array length at each call.

Okay, I will do this. But what I actually wanted to know if it is possible to get rid of the declaration of "Measurements: TMeasurements;" and do directly ObjArraySetLength(aJob.Measurements, 3)? If I write this code, I get a compile error "Constant object cannot be passed as var parameter".

Is there another way?

Board footer

Powered by FluxBB