#501 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 2021-12-05 21:28:31

The configuration is shown here: https://synopse.info/forum/viewtopic.php?id=6069

I am running the following code:

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,@sum);

if mytable is a table mapped to an external db, I am getting the following error:
Project Project1.exe raised exception class ERestStorage with message 'TRestStorageExternal.EngineList(ReturnedRowCount<>nil) for TOrmmytable'.

if mytable is a table of type TRestStorageInMemory I get no error



Second problem in the same configuration:
Same Tormmytable definition, which is created as mapped to an external database and the second test is created as TRestStorageInMemory
As external database, with the following code I am getting back the RowID as ID.

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,@sum);

If the same Tormmytable is created as TRestStorageInMemory  with the above code, I am not getting back the RowID as ID.
How can I change this?
I apologize for the complex way of presenting it. You have right

#502 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 2021-12-05 20:10:00

I think the above error is originated from mormot.orm.server line 1023

function TRestOrmServer.EngineList(const SQL: RawUtf8; ForceAjax: boolean;
  ReturnedRowCount: PPtrInt): RawUtf8;
var
  rest: TRestOrm;
  sqladapted: RawUtf8;
begin
  sqladapted := SQL;
  rest := InternalAdaptSql(fModel.GetTableIndexFromSqlSelect(SQL, false), sqladapted);
  if rest = nil then
    result := MainEngineList(SQL, ForceAjax, ReturnedRowCount)
  else
    result := rest.EngineList(sqladapted, ForceAjax, ReturnedRowCount);
end;
 

#503 Re: mORMot 1 » primary key fields in an existing table & and other indexes » 2021-12-05 20:00:56

What is the reason for this error? TRestStorageExternal.EngineList(ReturnedRowCount<>nil)
I really can not understand it

#504 mORMot 1 » primary key fields in an existing table & and other indexes » 2021-12-05 19:17:47

dcoun
Replies: 16

I have a table with an existing Integer primary key index in an external database and I map to the Virtual Sqlite table:

VirtualTableExternalRegister(model,TOrmmytable,extrndb,'mytable');

if this primary key is not named as 'ID', I can map it with:

 model.Props[TOrmmytable].ExternalDB.MapField('ID','mycustomname'); 

I am issuing the following:

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,@sum);

I am getting the following error:
Project Project1.exe raised exception class ERestStorage with message 'TRestStorageExternal.EngineList(ReturnedRowCount<>nil) for TOrmmytable'.

With the follwoing it works and I am getting back all fields including the RowID as 'ID':

myRestServerDB.orm.ExecuteJson([],'select * from mytable',false,nil);

if this table is not external and it is TRestStorageInMemory  then the RowID as ID is not returned for the same query and I do not get the above error.

I can use the RowID in the sql query but it can not retrieved. How can I change that?

I noticed also that if an Database integer field is declared in the Torm class as TRecordReference then when creating the database table it has also an non unique index
For a unique index, I must use the "stored AS_UNIQUE" in the property in the class definition
Multi-field indexes are automatically created in a other way or should I override the InitializeTable ?
In case I am using the InitializeTable, is there a way to to read if an index already exists?

Thank you in advance

#505 Re: mORMot 1 » TRestServerDB with TRestStorageInMemory and direct threadsafe access » 2021-11-30 17:39:46

I am using RetrieveList & xxFieldValue(s) and it works very fast
The TRestStorageInMemory tables are only in memory retrieved initially through a database from a rest server. Is binarystorage faster during queries?
Thank you a lot Arnauld for your work

#506 mORMot 1 » TRestServerDB with TRestStorageInMemory and direct threadsafe access » 2021-11-30 14:42:42

dcoun
Replies: 3

I have a number of small tables that have value-sets (max ~12000 records each) that they are used by different threads at the same time and their content does not change for long time.
The above tables are used for decoding with simple CRUD queries, but at the same time I need them to be available for other queries with other tables that change all the time.
I created TRestServerDB using Mormot2 with TRestStorageInMemory  for these "static" tables having the following model:

result:=tormmodel.Create([TOrmtest1,TOrmtest12,....]); 
result.VirtualTableRegister(TOrmtest1,TOrmVirtualTableBinary);
result.Props[TOrmtest1].Kind:=ovkCustomAutoID;

I create a TRestServerDB with the above model and I find the TRestStorageInMemory instance with the following:

srvmem:=TRestServerDB.Create(creatememorymodel); 
srvmem.CreateMissingTables;

var directstorage:TRestStorageInMemory;
directstorage:=(srvmem.Orm as TRestOrmServer).StaticVirtualTable[tormtest1] as TRestStorageInMemory;
directstorage.LoadFromJson('from a rawutf8 json');

I noticed that I can issue at the same time sql queries and ORM requests to the TRestServerDB instance and CRUD read requests to the TRestStorageInMemory directly from different threads without problem
To be honest, mormot2 solved a problem I had for years in an application with the above.

My question is, if I want after a period of time to reload new content to such a TRestStorageInMemory instance what should I do to TRestServerDB to flush cache, etc in order to have a proper operation?

Thank you in advance

#508 Re: mORMot 1 » EnterCriticalSection in Tsyslog causes http server+websockets to hang » 2021-11-29 10:28:40

If I move all log to a Tstringlist and add log output from this list to Tmemo after starting this problem does not exist

#509 mORMot 1 » EnterCriticalSection in Tsyslog causes http server+websockets to hang » 2021-11-29 10:19:34

dcoun
Replies: 1

Using a existing example I trying to create a websocket server:

srvinst:=TRestServerDB.Create(createRestModel); 
srvinst.Model.Owner:=srvinst;
....
srvinst.CreateMissingTables;
// register our ILongWorkService implementation as shared TLongWorkService
srvinst.ServiceDefine(TwbService,[IwbService],sicPerThread).ByPassAuthentication:=true;
try srv:=TRestHttpServer.Create('8888',[srvinst],'+',useBidirAsync);
srv.WebSocketsEnable(srvinst, websocketspass);
except on e:exception do begin result:=e.ClassName+'='+e.Message; exit; end; end;

Before the above code, logging is enabled with the following:

with TSynLog.Family do 
 begin 
  Level:=LOG_VERBOSE; 
  EchoCustom:=logmy2memo; 
 end;

where logmy2memo is just adding lines to a Tmemo in the main form

The server hangs before srv.WebSocketsEnable(srvinst, websocketspass);
The problem is from EchoCustom:=logmy2memo;

I try to debug it:
The problem starts from file mormot.net.async line 1663
log.Log(sllTrace, 'Create: started % threads', [fThreadPoolCount + 1], self);

If I comment the above the problem continues to exist in the file mormot.core.log line 4346
    EnterCriticalSection(GlobalThreadLock);

If I move EchoCustom:=logmy2memo after server starting I do not have this problem

Thank you in advance

#510 Re: mORMot 1 » Tormtable has too many fields: 71>64 » 2021-11-29 06:05:53

tbo wrote:
dcoun wrote:

I have an external table with 71 fields.
What should I do now?

Search for MAX_SQLFIELDS in Unit mormot.db.core.

With best regards
Thomas

Thank you a lot....
I was afraid I lost everything....

#511 mORMot 1 » Tormtable has too many fields: 71>64 » 2021-11-28 22:57:36

dcoun
Replies: 2

I have an external table with 71 fields.
What should I do now? Table is already used by existing software
Thank you in advance

#512 Re: mORMot 1 » What is best practice for multi-threaded in-memory database? » 2021-11-28 10:57:31

ab wrote:

1. still use TSQLRestServerFullMemory but create several tables for your application, and access directly each TSQLRestStorageMemory instance (bypassing the main TSQLRestServerFullMemory lock)

How can this be achieved?

#513 Re: mORMot 1 » tlist<record> inside a Torm/Tsqlrecord » 2021-11-27 22:19:26

if a Tlist<Dobjs> is needed, then the above set/get(ers) is a solution
If a Tarray<Dobjs> is used, you have a magic and effecient solution
I am new to mormot so, I am really missing a lot of procedures/functions
I apologize
With a list:Tarray<Dobjs> the above code is much more simpler:

function TOrmtest.GetObjs: rawutf8;
begin
if assigned(list) then
      result:=DynArraySaveJson(list,typeinfo(Tarray<Dobjs>))
else result:='';
end;

procedure TOrmtest.SetObjs(const Value: rawutf8);
begin
list:=nil;
if value='' then
    exit;
if not DynArrayLoadJson(list,value,typeinfo(Tarray<Dobjs>)) then
    list:=nil;
end;

By the way is the following registration needed with the above code?
TRttiJson.RegisterFromText(typeinfo(Dobjs),'iu integer nam string',
[jpoIgnoreUnknownProperty,jpoIgnoreUnknownEnum],[woFullExpand,woStoreStoredFalse,woDateTimeWithZSuffix]);

#514 Re: mORMot 1 » parse a JSON array with JsonArrayDecode in Mormot2 » 2021-11-27 22:09:53

Thank you again
You have right about the documentation of the not needed '['
But, is the "inc(n);" needed?

#515 Re: mORMot 1 » tlist<record> inside a Torm/Tsqlrecord » 2021-11-27 19:05:32

After the solution in  https://synopse.info/forum/viewtopic.php?id=6064 My Geter and Seter follow:

function TOrmtest.GetObjs: rawutf8;
begin
result:='';
if assigned(list) then
   for var i in list do
     result:=result+','+RecordSaveJson(i,typeinfo(Dobjs));
if result<>'' then
     result[1]:='[' 
else result:='[';
result:=result+']';
end;

procedure TOrmtest.SetObjs(const Value: rawutf8); 
var
     a:TPUtf8CharDynArray;
     n,o,m:integer;
     c:Dobjs;
     s:rawutf8;
begin
list.clear;
if (value='') or (not JsonArrayDecode(putf8char(value)+1,a)) then exit;
m:=length(a)-1;
for n:=0 to m do
  begin
   if n<m then
     SetString(s,a[n],a[n+1]-a[n]-1)
   else begin
     s:=a[n];
     s:=copy(s,1,length(s)-1);
   end;
  RecordLoadJson(c,s,typeinfo(Dobjs));
  list.Add(c); //list:=list+[c]; for Tarray<Dobjs>
end;
end;

list is a Tlist<Dobjs> (can be also be a TArray<Dobjs>) and GetObjs needs a modification for Delphi's mobile apps

The above implementation results to a database field filled with a Json Array that can be searchable by SQL queries

Comments to improve it are welcome, I am a hobbyist programmer
Mormot2 is really wonderful and many, MANY, many thanks to Arnaud

The above needs also the following for Recordloadjson/Recordsavejson to work:
TRttiJson.RegisterFromText(typeinfo(Dobjs),'iu integer nam string',
[jpoIgnoreUnknownProperty,jpoIgnoreUnknownEnum],[woFullExpand,woStoreStoredFalse,woDateTimeWithZSuffix]);

#516 Re: mORMot 1 » parse a JSON array with JsonArrayDecode in Mormot2 » 2021-11-27 17:58:29

I think I found something:
a) The P should not start with '[', last ']' is needed
b) an inc(n) is missing and at the end the values are always nil

function JsonArrayDecode(P: PUtf8Char; out Values: TPUtf8CharDynArray): boolean;
var
  n, max: integer;
  parser: TJsonGotoEndParser;
begin
  result := false;
  max := 0;
  n := 0;
  {%H-}parser.Init({strict=}false, nil);
  P := GotoNextNotSpace(P);
  if P^ <> ']' then
    repeat
      if max = n then
      begin
        max := NextGrow(max);
        SetLength(Values, max);
      end;
      Values[n] := P;
      P := parser.GotoEnd(P);
      if P = nil then
        exit; // invalid content, or #0 reached
//todo: needs an  inc(n); n never incs
      if P^ <> ',' then
        break;
      inc(P);
    until false;
  if P^ = ']' then
  begin
    SetLength(Values, n);
    result := true;
  end
  else
    Values := nil;
end;

#517 Re: mORMot 1 » parse a JSON array with JsonArrayDecode in Mormot2 » 2021-11-27 16:27:40

Using Mormot2, is there an existing way to parse a json array to have the same deserializing with RecordLoadJson without using variants?
I am a bit confused with this function (JsonArrayDecode), can you help me please?

#518 mORMot 1 » parse a JSON array with JsonArrayDecode in Mormot2 » 2021-11-27 13:44:41

dcoun
Replies: 6

I noticed that JsonArrayDecode is nowhere used in Mormot2 but it exists as a function in file mormot.core.json
is it a kind of unfinished code? Can I use it in cross platform apps?

how can I use it to parse an array of of JSON objects created by RecordSaveJson like the following?
[{"id":1,"nam":"name1"},{"id":2,"nam":"name2"}]

I was thinking of getting each JsonObject as rawutf8 and transform it with RecordLoadJson

Thank you in advance

#519 Re: mORMot 1 » Client-server ORM, static tables and caching » 2021-11-26 09:13:10

I wrote the following and it works

var o:TOrmtest; t:RawJson;
begin
testlist:=tlist<tormtest>.create;
t:=mydata.EngineList('SELECT ID,nam FROM table',false);
o:=TOrmtest.CreateAndFillPrepare(t);
while o.FillOne do
     testlist.Add(tormtest(o.CreateCopy));
o.FillClose;
o.Free;
end;


Thank you again Arnaud

#520 Re: mORMot 1 » Client-server ORM, static tables and caching » 2021-11-26 08:22:43

Thank you a lot Arnaud
I noticed that I could also run the following:
jsonstring:=h.Orm.RetrieveListJson(tormtest,'',[],'*');
Which is the best and cross-platform way to transform this json string to a Tlist<tormtest> and to an array of records, keeping the ownership of the instances to my code?
Will it be efficient for 5-10 records as with 5000 records?

Also please notice the following:
Using Mormot2, I tried to write the following:
mydata:=TRestStorageInMemory.Create(TOrmtest,nil);

After that I am feed it with data, every ok
I am going now to retrieve data and I am starting something easy
mydata.RetrieveIList(tormtest,tt,'',[],'*')

I get a access violation due to line 2991 in mormot.orm.storage
            ForceAjax := ForceAjax or
                         not Owner.Owner.NoAjaxJson;

Owner here is null and this is the cause for the violation
Changing it to the following, no error is created:
            ForceAjax := ForceAjax or
                              ( assigned(owner) and (not Owner.Owner.NoAjaxJson));

#521 Re: mORMot 1 » tlist<record> inside a Torm/Tsqlrecord » 2021-11-26 06:51:10

ab wrote:

It is not supported.

You need to use a dynamic array for such content.
Try with TArray<Dobjs>.

Dobjs is a record.
I usually prefer Tarray<> but in this case this property is created as BLOB and the content is not JSON
With Tlist the content is JSON and the database field is TEXT.
How do I force TArray<Dobjs> to be saved as JSON?
Thank you in advace

#522 Re: mORMot 1 » Client-server ORM, static tables and caching » 2021-11-25 21:45:11

Thank you a lot Arnaud
An other problem I am not sure is the cross-platform way to retrieve data from an ORM table and put them inside a local table:
I looked at
var list:ilist<tormtable>;
Trestclient.orm.RetrieveIList(tormtable,list,'',[],'*')
but the data in the ilist should be copied to a TRestStorageInMemory for example.
ilist owns the data and I should copy them to insert them in a TRestStorageInMemory
How to stop the ilist<tormtable> that it is returned by RetrieveIList to free the items?
Do you propose an other way?

#523 mORMot 1 » tlist<record> inside a Torm/Tsqlrecord » 2021-11-25 20:27:15

dcoun
Replies: 6

Project Project1.exe raised exception class EOrmException with message 'TOrmPropInfoRtti.CreateFrom: Unhandled oftUnknown/rkClass type for property objs'.

I am getting the above error when trying to use the following object

type
Dobjs= packed record iu:integer; nam:string; end;

TOrmtest = class(TOrm)
    private
      FName: RawUTF8;
      Fobjs:tlist<Dobjs>;
    published
      property Name: RawUTF8 index 50 read FName write FName stored AS_UNIQUE;
      property objs: tlist<Dobjs> read Fobjs write Fobjs;
  end;

how can I use a Tlist<record> in a TOrm/TSQLrecord? Should I register it somewhere?

Is it possible to force an array of records (that exists as a property in a Torm/TSQLrecord) to be saved in a TEXT field in the database as JSON Array of JSON objects?

#524 mORMot 1 » Client-server ORM, static tables and caching » 2021-11-24 08:35:40

dcoun
Replies: 7

Probably stupid questions, I apologize, but I am not sure if the following is supported and I am a bit confused:

I am having a number of tables in an external database mapped to TormXXX classes in Mormot2 that can seldom be changed.
The client does not modify them, an other external process can modify them.

1. When using RetrieveIlist or OneFieldValues or any other content request, is this data stored in a possible client's cache?
2. Is it possible by the framework to retrieve a local memory/disk copy using Torm functions to do queries locally and update them when needed?
3. Should I have to create different models?
4. Which is the most efficient way to have a local memory DB with ORMs retrieved by a server running mormot?

Plus: To have an offline work in ORMs and upload them after being online is really fantastic also, but in such a case how does mormot2 deal with new Torms instances that are related to other Torms?

Thank you in advance

#525 Re: mORMot 1 » Loading & Creating from a TRestHttpClient Definition file ? » 2021-11-23 21:06:13

I can confirm this error using Mormot2

The error happens in mormot.net.client, line 2383 but this is caused by the previous call in line 2375 due the contents of fProxyName and fProxyByPass that have a content of '='
  fSession := WinHttpApi.Open(
    pointer(Utf8ToSynUnicode(fExtendedOptions.UserAgent)),
    access,
    pointer(Utf8ToSynUnicode(fProxyName)),
    pointer(Utf8ToSynUnicode(fProxyByPass)), 0);

From mormot.rest.http.client line 652,
fProxyName and fProxyByPass have a content of '='

where the initial JSON is:
'{"Kind":"TRestHttpClientWinHttp","ServerName":"localhost:11111","DatabaseName":"IgnoreSSLCertificateErrors=0&ConnectTimeout=30000&SendTimeout=30000&ReceiveTimeout=30000&ProxyName=&ProxyByPass=","User":"","Password":""}'
I think the problem is that ProxyName ProxyByPass are empty after the '=' for UrlDecodeValue

#526 Re: mORMot 1 » Mormot2: Setting fDatabase.ResourceOptions for Firedac » 2021-11-22 11:11:14

ab wrote:

I have just created a new speCreated event which could be used to customize a database connection just after its creation, and before the connection.
It should work on all connection classes.

Thank you very much. That's helps a lot

#527 Re: mORMot 1 » Mormot2: Setting fDatabase.ResourceOptions for Firedac » 2021-11-22 09:50:13

I apologize. I was looking the TSqlDBConnection.
Migrated to ZEOS finally, to follow linux developments too

#528 mORMot 1 » Mormot2: Setting fDatabase.ResourceOptions for Firedac » 2021-11-21 17:53:35

dcoun
Replies: 4

Looking to set the TfdConnection.ResourceOptions for manual queries from interface functions (using the mormot's connection pool), I found that it is not possible without changing the Mormot2 source.
I can get a TSqlDBFireDACConnection but it is not possible to set the fDatabase.ResourceOptions because it is readonly as a property.
I need to disable Macros (MacroCreate, MacroExpand) that cause problems to some queries and set ServerOutput based on Debug settings


Which is the best way to have access and change them when needed?

#529 mORMot 1 » Mormot2, example extdb-bench, TTestDatabaseExternalAbstract problem » 2021-11-20 10:50:12

dcoun
Replies: 2

I tried to compile and run the extdb-bench example.
I created a Mysql test as follows:

   TTestmysql = class(TTestDatabaseExternalAbstract)
  private
  published
    procedure _SynDBmysql;
  end;

procedure TTestmysql._SynDBmysql;              var a:TSqlDBFireDACConnectionProperties;
begin
  a:=TSqlDBFireDACConnectionProperties.Create('MySQL?Server=localhost;Port=3306','db','user','pass');
  a.Parameters.Add('CharacterSet=utf8');
  RunExternal(a);
end;

I noticed that the test always fails (Using Delphi 11 and MariaDB 10.6.5).
The problem is caused by the following code:

procedure TTestDatabaseExternalAbstract.ClientCreate;
var
  lTables: TRawUtf8DynArray;
begin
  if (Props <> nil) and (dbDropTable in Flags) then
  begin
    // drop only if table exist
    Props.GetTableNames(lTables);
    if FindPropName(lTables, 'SAMPLERECORD') >= 0 then
    begin
      Props.ClearConnectionPool;
      Props.ThreadSafeConnection.Disconnect;
      Props.ExecuteNoResult('drop table SAMPLERECORD', []);
    end;
  end;
  inherited ClientCreate;
end;

Inside lTables, the table names are in the form dbname.tablename and it should be changed to the following:
    if FindPropName(lTables, <dbname>+'.'+'SAMPLERECORD') >= 0 then

As a newbie in mormot, I am not sure if mysql config needs something more for ITables to not include the dbname or something else.
Where is the error?

By the way:
The same happens with Postgres that it is included in the example:
  mormot.db.sql.postgres, is missing in uses clause
and a "public." is needed before in the same statement:

    if FindPropName(lTables, <dbname>+'.'+'SAMPLERECORD') >= 0 then

Even with that direct access postgres fails

Just FYI. Thanks again.

#530 Re: mORMot 1 » mormot2: TAescbc gives different decrypted output for the same inputs » 2021-04-19 15:08:19

I  apologize. Mkey is tbytes And it worked as it follows:
e:=TAescbc.Create(mkey[0],128);

Thank you a lot

#531 mORMot 1 » mormot2: TAescbc gives different decrypted output for the same inputs » 2021-04-19 13:51:40

dcoun
Replies: 2

Hi,
I am trying to decrypt a rijndael encrypted piece of data that is encrypted with DCPcrypt2's encryptCBC function, using mormot2's TAescbc with the following code:

var dat,test,dIV:array of byte; i:cardinal
begin
e:=TAescbc.Create(mkey,128);
e.IV:=PHash128(@dIV[8])^;
i:=length(dat)
setlength(test,i);
e.Decrypt(dat,test,i);
e.Free;

What I notice is that even if I put the same data in mkey, dIV, dat, I am getting every time it runs different results in "test"
Am I missing something?
Thank you in advance

#532 synopse.info » blog is down » 2021-01-25 14:52:41

dcoun
Replies: 1

Hi,
Blog.synopse.info is down

Board footer

Powered by FluxBB