Pages: 1 2

#1 Re: mORMot 1 » interface based services: Bulk insert on the server side » 2020-04-20 13:03:05

alexdmatveev

The solution from [12.3.3.1. Several Batches] works like a charm.
Thanks a lot for the great help!

#2 mORMot 1 » interface based services: Bulk insert on the server side » 2020-04-20 10:36:11

alexdmatveev
Replies: 3

Hello,

pls show me the way or a part of the documentation...

Environment: UniDAC + MySQL.

I have interface based services.
On Server side I got a collection of TPersistent DTOs as in [..\SQLite3\Samples\20 - DTO interface based service] sample.
Everything works good but a bit slow.

unit uServiceBehandlerDef;

interface

uses
  SysUtils, mormot, uServiceInterfaces, uModel, uModelDB, uServer;

type
  TBehandlerDefPostService = class(TInterfacedObject, IBehandlerDefPostService)
  public
    procedure Post(const items: TDTOBehandlerDefs);
  end;

implementation

procedure TBehandlerDefPostService.Post(const items: TDTOBehandlerDefs);
var
  obj: TBehandlerDef;
  i: Integer;
  dto: TDTOBehandlerDef;
begin
  aRestServer.Delete(TBehandlerDef, Format('Username=''%s''', [ServiceContext.Request.SessionUserName]));

  for i := 0 to items.Count - 1 do
  begin
    obj := TBehandlerDef.Create;
    try
      dto := TDTOBehandlerDef(items[i]);
      obj.BDEF_NR := dto.BDEF_NR;
      obj.INTERN_NAME := dto.INTERN_NAME;
      obj.EXTERN_NAME := dto.EXTERN_NAME;
      aRestServer.Add(obj, True);
    finally
      obj.Free;
    end;
  end;
end;

end.

I would like to use bulk mysql insert on the server side.

I see the method Batch but do not understand how I should use it.

  TSQLRestServer = class(TSQLRest)
    procedure Batch(Ctxt: TSQLRestServerURIContext);

Thanks.

#3 Re: mORMot 1 » Current user of the session on interface based service » 2020-04-16 18:36:52

alexdmatveev

Thanks a lot! Works.

#4 Re: mORMot 1 » Current user of the session on interface based service » 2020-04-16 15:38:25

alexdmatveev

is this way correct?

var
  u: TSQLAuthUser;
begin
  u := aRestServer.SessionGetUser(ServiceContext.Request.Session);
end;

#5 mORMot 1 » Current user of the session on interface based service » 2020-04-16 15:32:14

alexdmatveev
Replies: 3

Hello,

I have interface based service with enabled authentication.

I need to get current user of the server.

aRestServer.SessionGetUser(aSessionID: Cardinal): TSQLAuthUser;

looks like almost what I need.

The point is I can not find is how to get current sessionId.

Could you help me?

Thanks.

#6 Re: mORMot 1 » problem in time of concurent access to table » 2016-11-10 16:06:47

alexdmatveev

five stars for support smile
thanks a lot.

tomorrow we will test it but on first look it works.

#7 Re: mORMot 1 » problem in time of concurent access to table » 2016-11-10 13:07:19

alexdmatveev

after an hour deep in mormot sources and docs... I give up.
Pls show me how to get access to TSQLRestStorageExternal object to set EngineAddUseSelectMaxID=True.

Is it part of client or server sides?

Thanks a lot.

#8 Re: mORMot 1 » problem in time of concurent access to table » 2016-11-10 11:21:23

alexdmatveev

the show is going on...

I can not understand the problem but after our tests it looks like mORMot Rest server does not use Autoincrement fields directly but via internal cache.

Is it possible?

The message from our customer that makes tests too:


The Server is not using the autoincrements, it has an internal counter setted when the server software start. When my software makes the insert using autoincrements it use some new values and after when the Server try to save data it has a duplicated key error.
I have just tested it the autoinc is setted to 57099 but the Server inserts value with ID 56711

Could you show me the way to make the server use autoincrement directly?

thanks.

#9 Re: mORMot 1 » problem in time of concurent access to table » 2016-11-09 09:46:40

alexdmatveev

Unfortunatelly, the offered solution with UseCache=false does not work.

Code snippet from server app:

  aProps := TSQLDBUniDACConnectionProperties.Create('MySQL', 'server', 'root', '*****');
  aProps.UseCache := False;
  aProps.SpecificOptions.Values['Server'] := 'localhost';
  aProps.SpecificOptions.Values['Port'] := '3306';
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';
  aProps.ThreadingMode := tmThreadPool;

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);

Has anybody other ideas?

Thanks a lot.

#10 Re: mORMot 1 » problem in time of concurent access to table » 2016-11-09 08:11:27

alexdmatveev

I use Unidac components to establish MySQL connection on the server.
I see your idea, thanks a lot.
I will try it today/tomorrow and will post the result.

#11 mORMot 1 » problem in time of concurent access to table » 2016-11-08 19:59:27

alexdmatveev
Replies: 8

Hello,

we have a mormot managed server that provides to client operations with MySQL database.
The server works intensively (but not critically) as I see.

Problem is so:
we have one basic table to store Transactions objects. Every transactions must be written there.
Everything works good with mormot as single client for this table.

2 days ago the developer from our customer created new application that writes data into the same table.
During writing by this application we have problem with mormot server and clients.

I provide the deleper's mail to me (maybe it will help to understand the problem and provide suitable solution).


Hello everybody,

I have written an application to move the transactions history from our secondary DB appllication to mormot DB application. When I add  transactions to the "transactions" table (using the autoincrement id) and a client application is sending, all clients are blocked.

To unblock, the server softwares need to be restarted and also every communicator (client side) that is blocked needs to be restarted.
From what I have seen the server side is not using the ID as autoincrement field and after some retries it blocks.

Could you write me how long you need to solve this problem?

Best regards,


Could you show me the way to search suitable solution?

Thanks a lot.

#12 Re: mORMot 1 » Add a object with blob in one POST » 2015-12-10 16:02:50

alexdmatveev

Just updated.

You have saved my night smile

Thanks a lot. Works fine.

#13 Re: mORMot 1 » Add a object with blob in one POST » 2015-12-10 14:59:29

alexdmatveev

Thanks for your answer.

I use crossplatform library.
The syntax is little bit other:

ConnectionManager.DocumentClient.BatchAdd(msg.Details, True, False, '*');

and when I use it I have the same error

20151210 10125718 EXC   EORMBatchException {"Message":"TSQLRestServerDB.EngineBatchSend: Missing ["} at 005A0712  stack trace 00596B66 0598425 005BE8DF 005233A7 004E59CB 00522E9F 00522ECC 00522F56 004BBD84 0040AD2A 764C3677 77529F42 77529F15

#14 mORMot 1 » Add a object with blob in one POST » 2015-12-10 09:27:14

alexdmatveev
Replies: 4

Hello gurus,

Is it possible to add and object with blobs with one POST request?

With other words:

with 2 POSTs:

Result := ConnectionManager.DocumentClient.Add(msg.Details, true) >= 0;
ConnectionManager.DocumentClient.UpdateBlobFields(msg.Details);

I tryed to make it with batch:

  ConnectionManager.DocumentClient.BatchStart(msg.Details.RecordClass);
  ConnectionManager.DocumentClient.BatchAdd(msg.Details, True, False);
  ConnectionManager.DocumentClient.BatchUpdate(msg.Details, 'Document');
  res := ConnectionManager.DocumentClient.BatchSend(ids);
  Result := res = HTML_SUCCESS;

but I have an exception on the server:

20151210 10125718 EXC   EORMBatchException {"Message":"TSQLRestServerDB.EngineBatchSend: Missing ["} at 005A0712  stack trace 00596B66 0598425 005BE8DF 005233A7 004E59CB 00522E9F 00522ECC 00522F56 004BBD84 0040AD2A 764C3677 77529F42 77529F15

I use:
1. Delphi XE7
2. CrossPlatform client
3. server is based on mongoDB

and common question:

what is the optiomal way to ADD full objects with blobs?
Pls show me the way.


Thanks a lot.

#15 Re: mORMot 1 » blob cache » 2015-12-10 09:22:48

alexdmatveev

ok, thanks a lot.
we have finished this experiment. and moved back to mongo.

#16 Re: mORMot 1 » blob cache » 2015-12-09 19:00:42

alexdmatveev

Maybe it is a problem of crossplatform library?

there is no method UpdateBlobFields

instead of the method it is offered to use Update method

function TSQLRest.Update(Value: TSQLRecord; FieldNames: string): boolean;

and that is the reason why UpdateBlobFields is not called on the server...

Is there a way to force update blob fields correctly via crossplaform library?


I have tryed to use asterix

ConnectionManager.Client.Update(msg.Details, '*');

as it is described here:

    /// update a member
    // - you can let default FieldNames='' to update simple fields, '*' to
    // update all fields (including BLOBs), or specify a CSV list of updated fields
    function Update(Value: TSQLRecord; FieldNames: string=''): boolean; virtual;

#17 Re: mORMot 1 » blob cache » 2015-12-09 17:52:49

alexdmatveev

and here I stopped to understand.
I set breakpoints into all methods UpdateBlobFields in mormot.pas and mormotdb.pas.

Then I call client method to update blob.
And my breakpoints are not fired. I mean code execution does not reach my breakpoints.

I think you mean method:

function TSQLRestStorageExternal.UpdateBlobFields(Value: TSQLRecord): boolean;

Here I see parameters binding... But the method is not called at all.

Please help me to understand it.

Thanks a lot.

#18 Re: mORMot 1 » blob cache » 2015-12-09 16:57:11

alexdmatveev

unit SynDBUniDAC

procedure TSQLDBUniDACStatement.DatasetExecSQL;
begin
  (fQuery as TUniQuery).Execute;
end;

When it executes an query to update myfield it has always same parameter value.

What should I do to force it to refresh parameter value?

Thanks

#19 Re: mORMot 1 » blob cache » 2015-12-09 16:01:22

alexdmatveev

could you write me name of the unit?
mORMot has lot of units and I want to be sure I will not make any troubles to mORMot server.

Thanks a lot for your help.

#20 Re: mORMot 1 » blob cache » 2015-12-09 15:36:13

alexdmatveev

I have placed a breakpoint in SynCrossPlatformREST.pas in 

function TSQLRest.Update(Value: TSQLRecord; FieldNames: string): boolean;
var tableIndex: Integer;
    json: string;
begin
  if (Value=nil) or (Value.ID<=0) then begin
    result := false;
    exit;
  end;
  tableIndex := Model.GetTableIndexExisting(Value.RecordClass);
  json := Model.Info[tableIndex].ToJSONUpdate(self,Value,FieldNames,false);
  result := ExecuteUpdate(tableIndex,Value.ID,json);
  if result then
    Value.fInternalState := InternalState;
end;

and I see that I send always different json value. It is correct.

But as result in DB I have the same blob value in all records.
All other fields (non-blob) work good.

#21 Re: mORMot 1 » blob cache » 2015-12-09 15:23:29

alexdmatveev

yeah, I have provided some code with RetrieveBlobFields call and creation of a new object to fill it with RetrieveBlodFields.
Do you need more?

Zeos - it is not allowed in the project. Sorry.

If I have no good answer I will prepare separate project.

#22 Re: mORMot 1 » blob cache » 2015-12-09 14:55:10

alexdmatveev

Thanks.

With mongoDB it worked pretty well.

Server part:

  aProps := TSQLDBUniDACConnectionProperties.Create('MySQL', 'mednet_server', 'root', 'Tiger4711');
  aProps.SpecificOptions.Values['Server'] := 'localhost';
  aProps.SpecificOptions.Values['Port'] := '3306';
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';
  aProps.ConnectionTimeOutMinutes := 480;

  aProps.ThreadingMode := tmThreadPool;
  //aProps.ThreadingMode := tmMainConnection;

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);
    VirtualTableExternalRegister(aModel, TServerAccounts, aProps, 'accounts');
    VirtualTableExternalRegister(aModel, TServerAccount_Institutions, aProps, 'account_institutions');
    VirtualTableExternalRegister(aModel, TPracticesoft_mail_params_default, aProps, 'practicesoft_mail_params');
    VirtualTableExternalRegister(aModel, TPracticesoft_form_params_default, aProps, 'practicesoft_form_params');

    MapFields(aModel);

    try
      DeleteFile('data1.db3');
      aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
      aRestServer.Cache.Clear;
      aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
      aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

My test:

  Result := ConnectionManager.Client.Add(msg.Details, true) >= 0;
  ConnectionManager.Client.UpdateBlobFields(msg.Details); //here I post different objects with different blob values

  body := TTransaction_form.Create(ConnectionManager.Client, msg.Details.ID); 
  ConnectionManager.Client.RetrieveBlobFields(body); // here I always get the SAME blob value
  //and the VALUE is the first value I posted after restarting server part

and Also I have the same values in MySQL tables. I checked them in my MySQL Manager without mORMot.

#23 mORMot 1 » blob cache » 2015-12-09 12:40:19

alexdmatveev
Replies: 13

Hello gurus,

I need your help to deal with blob fields posting.

Till today I had a server on mongoDB and my statements like:

client.UpdateBlobFields(rec);

worked fine.

Today I exchanged the server part with MySQL connection.
Now all my statements like

client.UpdateBlobFields(rec);

write always the same value in blob field.

What code should I provide to get your help?

I have read SAD "12.4.4. How to cache"...  and I tryed to make on server so:

aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
aRestServer.Cache.Clear;
aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

but without success.

Please show me the way.

Update:
MySQL database engine: InnoDB.
Maybe I should play with InnoDB buffer?
If yes, pls show me what should I do.
Thanks.

#24 Re: mORMot 1 » convert TSQLRawBlob (TByteDynArray) to String » 2015-07-28 13:23:00

alexdmatveev

for windows version it works so:

class function TRawBlobHelper.AsString(blob: TSQLRawBlob): RawUTF8;
var
  bytes: TByteDynArray;
begin
{$ifdef ISDWS}
{$else}
  bytes := TByteDynArray(blob);
{$endif}
  Result := TEncoding.ANSI.GetString(TBytes(bytes));
end;

#25 mORMot 1 » convert TSQLRawBlob (TByteDynArray) to String » 2015-07-28 12:34:32

alexdmatveev
Replies: 1

Please help me with the casting from the subject.
I am sure I have in the blob base64 encoded String.

Please keep in mind I use crossplatform library.
And please I need 2 conversions: for {$ifdef ISDWS} and {$ELSE}.

Thanks a lot.

#26 Re: mORMot 1 » Is crossplatform-mormot thread-safe? » 2015-07-21 12:02:07

alexdmatveev

ok, thanks a lot for the help.
We will try.

#27 Re: mORMot 1 » Is crossplatform-mormot thread-safe? » 2015-07-21 09:49:35

alexdmatveev

Will Every thread have own TSQLRestClientHTTP with own server-session  (authentication, session id and so on) ?

Do you offer that?

Thanks a lot.

#28 Re: mORMot 1 » Is crossplatform-mormot thread-safe? » 2015-07-21 09:35:33

alexdmatveev

Thanks a lot.

could you provide an example/manual/instruction please?

#29 mORMot 1 » Is crossplatform-mormot thread-safe? » 2015-07-21 08:29:30

alexdmatveev
Replies: 5

I try to use crossplatform library in my multithread application...
and I get some strange access violation exceptions...

To start discussion I have one question:

Is crossplatform library thread-safe at all ?

Thanks.

#30 Re: mORMot 1 » HELP: Why are there no encryption, decryption example? (AES) » 2015-07-16 18:53:49

alexdmatveev

And pls keep in mind: SynCommons.pas and mORMot AES classes are not crossplatform.

#31 Re: mORMot 1 » HELP: Why are there no encryption, decryption example? (AES) » 2015-07-16 11:18:37

alexdmatveev
var
  cryptedPassword, rawPassword: RawByteString;
  Key: TSHA256Digest;
begin
...
  rawPassword := password;
  SHA256Weak(MYSQL_PASSWORD_AESKEY, Key);
  cryptedPassword := SynCrypto.AES(Key, 256, rawPassword, true);
  password := BinToBase64(cryptedPassword);
...
end;

MYSQL_PASSWORD_AESKEY - string constrant containing aes key

Please try.
I hope it will be usefull.

#32 Re: mORMot 1 » mORMot project to OSX » 2015-07-14 14:17:53

alexdmatveev

thanks a lot.
yes, I studied the point some time ago.

I ask here about some guide or best practices using mORMot library in crossplatform code.
Maybe I am not quite clear...

I will explain my quiestion with other words.

For example I use in Win32:

uses
  SynCommons, SynCrypto;

What should I write in my crossplatform code?

{$IFDEF MSWINDOWS} 
uses
  SynCommons, SynCrypto;
{$ENDIF}
{$IFDEF MACOS} 
uses
  SynCrossPlatformSpecific, SynCrossPlatformCrypto;
{$ENDIF}

Right?

Second question:
in Win32 I use AES functions from SynCrypto.pas.
Have mORMot the functions for OSX?

I only see SHA functions in SynCrossPlatformCrypto.pas.

Thanks a lot!

#33 mORMot 1 » mORMot project to OSX » 2015-07-14 09:40:50

alexdmatveev
Replies: 3

For our project we use: Xe7 + Firemonkey + mORMot.

I have tryed to compile our project for OSX via PAServer on my Mac.
It looks like not trivial task... SynCommons.pas causes lot of questions.

Have we any any guide for this task?

Thanks a lot.

#34 Re: mORMot 1 » server does not creates tables » 2015-07-06 08:31:42

alexdmatveev

Currently in our project it is very critically to exchange ZeosLib with Unidac.
Could you check model creation problem with unidac that I described above?

What should I do for it? Create a "feature request" ticket?

Thanks a lot.

#35 Re: mORMot 1 » negative ID again » 2015-07-02 12:25:02

alexdmatveev

hmmm...

What do I do wrong?
I have spent already lot of time to solve the task...

I tryed now your offered way again.

  LocalClient.ExecuteFmt('delete from transfer where ID=?', [], [offlineTransferID]);

Result:

First chance exception at $74D5B727. Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) using 3.8.10.2 - 'no such column: ID' extended_errcode=1'. Process mednet.exe (4220)

Then I tryed:

LocalClient.ExecuteFmt('delete from transfer where RowID=?', [], [offlineTransferID]);

Result:

First chance exception at $74D5B727. Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) using 3.8.10.2 - 'SQL logic error or missing database' extended_errcode=1'. Process mednet.exe (12440)

What do I do wrong?

Is it possible on client side at all? Or do you mean with "direct SQL" only server side?

Thanks again for the help.

#36 Re: mORMot 1 » negative ID again » 2015-07-02 08:55:28

alexdmatveev

So... is there a way to use RowID with MySQL to delete a record with negative ID?
Does anybody know a solution?

Thanks.

#37 Re: mORMot 1 » Duplicated blobs posted » 2015-07-01 13:06:10

alexdmatveev

in second and next requests I do not see next row in log...

TSQLDBUniDACStatement(10BC2540).00CD6483 SynDBDataset.TSQLDBDatasetStatementAbstract.Prepare (449)

Does the server uses last prepared SQL from first transaction?

#38 mORMot 1 » Duplicated blobs posted » 2015-07-01 11:08:15

alexdmatveev
Replies: 2

Hello,

after some hours of fight I ask you about any idea...

I do not know what happened today but when I ask code above several time for different objects I have absolutelly identical blob fields for all created records in mysql.
All non-blob fields look good. They are different.

  FConnectionManager.DocumentClient.RetrieveBlobFields(form);
  LocalClient.Add(form, True, True);
  LocalClient.UpdateBlobFields(form);

Log from server for 2 records:

1:

20150701 12340157  +    TSQLRestServerDB(09ACBB30).URI(PUT root/OmFormDocument/5/Document inlen=90702)
20150701 12340157  +         TSQLDBUniDACStatement(10BC1F40).00CD6483 SynDBDataset.TSQLDBDatasetStatementAbstract.Prepare (449) 
20150701 12340157  -         00.000.222
20150701 12340157  +         TSQLDBUniDACStatement(10BC1F40).00CD65F5 SynDBDataset.TSQLDBDatasetStatementAbstract.ExecutePrepared (465) 
20150701 12340157 SQL             TSQLDBUniDACStatement(10BC1F40) update OmFormDocument set Document=*BLOB* where ID=5
20150701 12340201  -         00.091.883
20150701 12340201 srvr       TSQLRestServerDB(09ACBB30)   PUT root/OmFormDocument ORM-Write -> 200 with outlen=0 in 92263 us
20150701 12340201  -    00.092.334
20150701 12340201  +    TSQLRestServerDB(09ACBB30).URI(PUT root/OmFormDocument/5/Preview inlen=263947)
20150701 12340201  +         TSQLDBUniDACStatement(10BC2540).00CD6483 SynDBDataset.TSQLDBDatasetStatementAbstract.Prepare (449) 
20150701 12340201  -         00.000.271
20150701 12340201  +         TSQLDBUniDACStatement(10BC2540).00CD65F5 SynDBDataset.TSQLDBDatasetStatementAbstract.ExecutePrepared (465) 
20150701 12340201 SQL             TSQLDBUniDACStatement(10BC2540) update OmFormDocument set Preview=*BLOB* where ID=5
20150701 12340216  -         00.244.528
20150701 12340216 srvr       TSQLRestServerDB(09ACBB30)   PUT root/OmFormDocument ORM-Write -> 200 with outlen=0 in 244974 us
20150701 12340216  -    00.245.019

2:

20150701 12340628  +    TSQLRestServerDB(09ACBB30).URI(PUT root/OmFormDocument/13/Document inlen=134492)
20150701 12340628  +         TSQLDBUniDACStatement(10BC1F40).00CD65F5 SynDBDataset.TSQLDBDatasetStatementAbstract.ExecutePrepared (465) 
20150701 12340628 SQL             TSQLDBUniDACStatement(10BC1F40) update OmFormDocument set Document=*BLOB* where ID=13
20150701 12340633  -         00.073.610
20150701 12340633 srvr       TSQLRestServerDB(09ACBB30)   PUT root/OmFormDocument ORM-Write -> 200 with outlen=0 in 73712 us
20150701 12340633  -    00.073.760
20150701 12340633  +    TSQLRestServerDB(09ACBB30).URI(PUT root/OmFormDocument/13/Preview inlen=96058)
20150701 12340633  +         TSQLDBUniDACStatement(10BC2540).00CD65F5 SynDBDataset.TSQLDBDatasetStatementAbstract.ExecutePrepared (465) 
20150701 12340633 SQL             TSQLDBUniDACStatement(10BC2540) update OmFormDocument set Preview=*BLOB* where ID=13
20150701 12340639  -         00.100.866
20150701 12340639 srvr       TSQLRestServerDB(09ACBB30)   PUT root/OmFormDocument ORM-Write -> 200 with outlen=0 in 101018 us
20150701 12340639  -    00.101.104
20150701 12340639  +    TSQLHttpClientWinHTTP(0A07F980).00BD134E mORMotHttpClient.TSQLHttpClientGeneric.InternalURI (427) 
20150701 12340642 clnt       TSQLHttpClientWinHTTP(0A07F980) GET root?session_signature=3C1D9E470008644F33B00B40 status=200 state=1463
20150701 12340642  -    00.048.849

I see there correct inlen values. So I see I send correct values to write to DB...
But as result I have all blobs filled with values of first sent record's blobs.

My server and client code:

  aProps := TSQLDBUniDACConnectionProperties.Create('MySQL',
        settings.ReadString(logonName, 'mysql_database', 'mednet_default'),
        logonName,
        password);
  aProps.SpecificOptions.Values['Server'] := settings.ReadString(logonName, 'mysql_host', 'localhost');
  aProps.SpecificOptions.Values['Port'] := settings.ReadString(logonName, 'mysql_port', MYSQL_PORT);
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';
  FLocalModel := CreateLocalModel;

  VirtualTableExternalRegisterAll(FLocalModel, aProps, [regMapAutoKeywordFields]);

  FLocalRestServer := TSQLRestServerDB.Create(FLocalModel, SQLITE_MEMORY_DATABASE_NAME); // authentication=true
  FLocalRestServer.CreateMissingTables(0, [itoNoIndex4UniqueField, itoNoIndex4RecordReference, itoNoIndex4NestedRecord]); // create tables or fields if missing
//  FLocalRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
//  FLocalRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

  FLocalClient := TSQLRestClientDB.Create(FLocalRestServer);

Have you any ideas ?

Thanks for advance.

#39 Re: mORMot 1 » negative ID again » 2015-06-30 13:03:20

alexdmatveev

ok,
should I make it so

  FConnectionManager.LocalClient.Execute(FormatUTF8('delete from transfer where RowId=?', [offlineTransferID]));

?

I ask because I do not get wished result.

Please write me correct way...

Is it possible at all with MySQL?

Or RowId is only for SQLite?

Thanks a lot.

#40 Re: mORMot 1 » negative ID again » 2015-06-30 10:25:41

alexdmatveev

Hello,

Let's go on with the discussion smile
At first, thanks a lot to everybody for the help.

Next question here is:

How to DELETE a record with NEGATIVE ID?

Please note for code snippets below that offlineTransferID < 0 (is negative).

The code below says that column ID does not exist.

FConnectionManager.LocalClient.Execute(Format('delete from transfer where id=%d', [offlineTransferID]));

The next code is incorrect in mormot framework because it generates wrong URI:

FConnectionManager.LocalClient. Delete(TTransfer, offlineTransferID]);

So... what is the way?

Thanks.

#41 Re: mORMot 1 » mormot+zeos+mysql threading » 2015-06-29 12:40:20

alexdmatveev

ok, thanks a lot!

PS. DateTime parameter: thanks too. I have corrected it.

#42 Re: mORMot 1 » mormot+zeos+mysql threading » 2015-06-29 11:47:19

alexdmatveev

>All your MySQL/ZDBC execution should be run in the very same TSynBackgroundThread.

What is correct settings of the server pls?

they are should be as below ?

aProps.ThreadingMode := tmMainConnection;
FLocalRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
FLocalRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

#43 Re: mORMot 1 » mormot+zeos+mysql threading » 2015-06-29 06:40:25

alexdmatveev

some log rows (I can take from it nothing useful for first look, so pls take a look):

20150629 08375315 SQL   TSQLRestServerDB(0978B8C0) 46.49ms  delete from link
20150629 08375315 srvr  	TSQLRestServerDB(0978B8C0)   POST root/ ORM-Get -> 200 with outlen=0 in 46544 us
20150629 08375315  -    00.046.563
20150629 08375315  +    TSQLRestServerDB(0978B8C0).URI(GET root inlen=134)
20150629 08375315  +    TSQLDBZEOSStatement(1050D9E0).00F98A4F SynDBZeos.TSQLDBZEOSStatement.Prepare (773) 
20150629 08375315  -    00.000.046
20150629 08375315  +    TSQLDBZEOSStatement(1050D9E0).00F998B0 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (905) 
20150629 08375315 SQL   	TSQLDBZEOSStatement(1050D9E0) select max(Updated) from Account
20150629 08375315  +    TSQLDBZEOSStatement(1050BF10).00F998B0 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (905) 
20150629 08375315 SQL   	TSQLDBZEOSStatement(1050BF10) select ID,Updated,Created,LogonName,Personal,PublicKey,IsDeleted,Fingerprint from Account where LogonName='Test1 Alex' limit 1
20150629 08375315  -    00.001.031
20150629 08375523 srvr  	TSQLRestServerDB(0978B8C0)   GET root/ ORM-Get -> 200 with outlen=470 in 2126531 us
20150629 08375523  -    02.126.629
20150629 08375523  +    TSQLRestServerDB(0978B8C0).URI(GET root/Personal/115 inlen=0)
20150629 08375523 clnt  	TSQLHttpClientWinHTTP(09EBF980) GET root?session_signature=3C1D9D7D003C71F544318457 status=200 state=55
20150629 08375523  -    02.492.349
20150629 08375525  -    02.161.719
20150629 08375525  +    TSQLDBZEOSStatement(1050C280).00F998B0 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (905) 
20150629 08375525 SQL   	TSQLDBZEOSStatement(1050C280) select ID,Updated,Created,Title,Name1,Name2,Strasse,Postfach,Plz,Ort,Telefon1,Telefon2,Telefon3,Fax,Handy1,Handy2,Mail1,Mail2,www,Language_ as Language,Country,Client,id_mandant from Personal where ID=115
20150629 08375625 EXC   	EZSQLException ("SQL Error: MySQL server has gone away") at 00EC8087 ZDbcMySqlUtils.CheckMySQLError (460)  stack trace API 00EC8087 ZDbcMySqlUtils.CheckMySQLError (460) 00ED5554 ZDbcMySqlStatement.TZMySQLStatement.ExecuteQuery (351) 00E6F4F8 ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQuery (3342) 00E6F6DC ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQueryPrepared (3409) 00F99BF5 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (963) 00C8A90E SynDB.TSQLDBStatement.ExecutePreparedAndFetchAllAsJSON (6768) 00C92661 mORMotDB.TSQLRestStorageExternal.EngineRetrieve (1300) 00B96293 mORMot.TSQLRestServerURIContext.ExecuteORMGet (33811) 00BC38D0 mORMot.BackgroundExecuteProc (47479) 00B1663A SynCommons.TSynBackgroundThreadMethod.Process (47059) 00B160CF SynCommons.TSynBackgroundThreadAbstract.Execute (46928) 004D8C5C System.Classes.ThreadProc (14161) 0040B852 System.ThreadWrapper (23680) 
20150629 08375628  -    01.034.938
20150629 08375706 EXC   	EZSQLException ("SQL Error: MySQL server has gone away") at 00B163BF SynCommons.TSynBackgroundThreadAbstract.RunAndWait (47016)  stack trace API

#44 Re: mORMot 1 » mormot+zeos+mysql threading » 2015-06-28 19:20:20

alexdmatveev

my results:

remote servers now looks much more better.

But in my applicaton I have a connection to local mysql database via ZEOS too.

procedure TOmConnectionManager.CreateLocalServerMySQL(settings: ISettings);
var
  aProps: TSQLDBZEOSConnectionProperties;
  password: String;
  Key: TSHA256Digest;
begin
  FLocalModel := LocalModel;

  password := settings.ReadString(CurrentLogonName, 'mysql_password', '');
  password := Base64ToBin(password);
  SHA256Weak(MYSQL_PASSWORD_AESKEY, Key);
  password := SynCrypto.AES(Key, 256, password, false);

  aProps := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(dMySQL,
        settings.ReadString(CurrentLogonName, 'mysql_host', 'localhost') +
        ':' + settings.ReadString(CurrentLogonName, 'mysql_port', MYSQL_PORT)
        , 'libmysql.dll'),
      settings.ReadString(CurrentLogonName, 'mysql_database', 'mednet_default'),
      CurrentLogonName,
      password);
  aProps.ThreadingMode := tmMainConnection;

  VirtualTableExternalRegisterAll(FLocalModel, aProps, [regMapAutoKeywordFields]);

  FLocalRestServer := TSQLRestServerDB.Create(FLocalModel, SQLITE_MEMORY_DATABASE_NAME); // authentication=true
  FLocalRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
  FLocalRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
//FLocalRestServer.AcquireExecutionLockedTimeOut[execORMGet] := 120000;
//FLocalRestServer.AcquireExecutionLockedTimeOut[execORMWrite] := 120000;
  FLocalRestServer.CreateMissingTables;

  FLocalClient := TSQLRestClientDB.Create(FLocalRestServer);
end;

in the application I have several thread with some like

FConnectionManager.LocalClient.Add(link, true, true);

or

objs := FConnectionManager.Client.RetrieveList(cl, 'updated>?', [FormatDateTime('yyyy-mm-dd hh:nn:ss', lastSync)]);

After I have read the offered in previous post documentation I tryed all combinations for the local (in-app) mysql server (FLocalRestServer).
Periodicaly I get exceptions from mysql via zeos like
-Server has gone during query
-or some timeout exceptions


How should I organize the server to work with threads?

Should I try to Clone the server for every thread? Or does exists better way?

Thanks.

#45 Re: mORMot 1 » mormot+zeos+mysql threading » 2015-06-28 13:26:01

alexdmatveev

Thanks a lot as always smile

Will study and apply.

#46 Re: mORMot 1 » mormot+zeos+mysql threading » 2015-06-28 08:10:10

alexdmatveev

For the moment the server lives with:

aProps.ThreadingMode := tmMainConnection;

BUt I think it is not very good for perfomance.

#47 mORMot 1 » mormot+zeos+mysql threading » 2015-06-27 20:53:48

alexdmatveev
Replies: 10

Hello

I have a rest server with mysql connected via ZEOS.

and I have some clients.

Every SQL (as I see) that is generated on a client creates new thread on the server as I understand.
It is OK... but as I see in every thread new mysql connection is created.

Is it possible to use some like connections pool?

SynDB.pas:

  TSQLDBConnectionPropertiesThreadSafeThreadingMode = (
    tmThreadPool, tmMainConnection, tmBackgroundThread);

as I understand by default tmThreadPool is used.

and if I will use tmMainConnection then every next thread has to wait while previous SQL is executing... Right?

Now I have situation when my max_connections=100 is expired after 2 minutes of client work.

My server code:

var
  aModel: TSQLModel;
  aProps: TSQLDBZEOSConnectionProperties;
  aRestServer: TSQLRestServerDB;
  aNamedPipeServer: TSQLRestServerDB;
  aRestClient: TSQLRestClientDB;
  aHttpServer: TSQLHttpServer;

begin
  with TSQLLog.Family do
  begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE; // log all events to the console end;
  end;

  aProps := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(dMySQL,
        'localhost:3306', 'libmysql.dll'),
      '***',
      '***',
      '***');

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);

    MapFields(aModel);

    try
      DeleteFile('data1.db3');
      aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
      try
        aRestServer.CreateMissingTables; // create tables or fields if missing
        if not aRestServer.TableHasRows(TSQLAuthGroup) then
        begin
          TSQLAuthGroup.InitializeTable(aRestServer, '', [itoNoIndex4ID, itoNoIndex4UniqueField, itoNoIndex4NestedRecord, itoNoIndex4RecordReference]);
        end;

        // serve aRestServer data over HTTP
        aHttpServer := TSQLHttpServer.Create('888',[aRestServer],'+',useHttpApiRegisteringURI, 32);
        try
          aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
          writeln('Background server is running.'#10);
          write('Press [Enter] to close the server.');
          readln;
        finally
          aHttpServer.Free;
        end;
      finally
        aRestClient.Free;
        aRestServer.Free;
      end;
    finally
      aModel.Free;
    end;
  finally
    aProps.Free;
  end;
end.

Thanks a lot.

#48 Re: mORMot 1 » zeos-mysql + mormot as sql proxy » 2015-06-24 15:13:45

alexdmatveev

I can try to redesign my SQL to avoid horizontal GREATEST function and use UNION+MAX to get vertical max().

Or have we a way with mormot?

#49 mORMot 1 » zeos-mysql + mormot as sql proxy » 2015-06-24 15:11:59

alexdmatveev
Replies: 2

Hello,

I know that mormot was not designed as sql proxy.

But maybe there is a way...

I have mysql database and mormot connected via zeos to the database.
Everything works fine but now I need to execute complex SQL and I get as error.

I tryed to execute the SQL via SynDBExplorer and it works fine.
So I have a hope.

connection to mysql:

  aProps := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(dMySQL,
        settings.ReadString(CurrentLogonName, 'mysql_host', 'localhost') +
        ':' + settings.ReadString(CurrentLogonName, 'mysql_port', MYSQL_PORT)
        , 'libmysql.dll'),
      settings.ReadString(CurrentLogonName, 'mysql_database', 'mednet_default'),
      CurrentLogonName,
      password);

  VirtualTableExternalRegisterAll(FLocalModel, aProps, [regMapAutoKeywordFields]);

  FLocalRestServer := TSQLRestServerDB.Create(FLocalModel, SQLITE_MEMORY_DATABASE_NAME); // authentication=true
  FLocalRestServer.CreateMissingTables;

  FLocalClient := TSQLRestClientDB.Create(FLocalRestServer);

sql:

SELECT 
GREATEST(
	(SELECT MAX(transfer.SendDateTime) FROM transfer WHERE transfer1.id=transfer.MasterBackLink), 
	(SELECT MAX(transfer.RecieveDate) FROM transfer WHERE transfer1.id=transfer.MasterBackLink)) AS maxdate, 
(SELECT COUNT(*) FROM transfer WHERE transfer1.id=transfer.MasterBackLink) AS messagecount, 
transfer1.id, transfer1.id_user_sender, transfer1.id_user_reciever  FROM transfer AS transfer1 
WHERE transfer1.MessageFormat=1 AND transfer1.id=transfer1.MasterBackLink AND (transfer1.id_user_sender='Test1 Alex' OR transfer1.id_user_reciever='Test1 Alex') 
ORDER BY GREATEST((SELECT MAX(transfer.SendDateTime) FROM transfer WHERE transfer1.id=transfer.MasterBackLink), 
(SELECT MAX(transfer.RecieveDate) FROM transfer WHERE transfer1.id=transfer.MasterBackLink)) DESC

and I would like to get TSQLTableJSON:

var
  tab: TSQLTableJSON;
....
  tab := FConnectionManager.LocalClient.ExecuteList([], sql);

Error:

First chance exception at $762BB727. Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) using 3.8.10.2 - 'no such function: greatest' extended_errcode=1'. Process mednet.exe (14492)

Have I any chance?

What way should I use to connect to execute the SQL as SynDBExplorer does it?

Thanks.

#50 mORMot 1 » mysql embedded » 2015-06-16 13:32:50

alexdmatveev
Replies: 0

I see it is possible to work with some embedded RDBSM (Firebird, SQLite and so on).

Question: Could I organize a rest server based on mysql embedded edition?

Have you example?

Thanks.

Pages: 1 2

Board footer

Powered by FluxBB