You are not logged in.
This is not yet supported - the idea is to make a manual Update then Add if the first failed...
I tried the above and it does not work
If record is missing, update returns true
InitArray* probably?
SQL is usually changed to Orm
I use the total commander to make searches in the files of Mormot2 source if something can not be found in the second attempt
I can confirm that for 2 or more items it works, but still not for one. I am using the commit 2.0.3913.
Just use the above code with any kind of Ormitem
It is an update function and it can have zero to 500 items....
Thank you a lot @ab!!!
With the latest commits boInsertOrReplace does not work again
The following code gives again only "insert' in the sql query
ba:=TRestBatch.Createnorest(srv.model,TOrmApiitem,100,[boInsertOrReplace,boExtendedJson]);
item:=TOrmApiitem.Create;
.....
item.IDValue:=insu[n].id;
ba.Add(item,True,true);
srv.Orm.BatchSend(ba);20220811 06034303 ! ERROR mormot.orm.server.TRestOrmServerBatchSend(0c8b82d0) EngineBatchSend json=138 B count=1 errors=1 post=1 simple=0 hex=0 hexid=0 put=0 delete=0 8.03s 0/s
20220811 06034262 ! EXC EZSQLException {Message:"SQL Error: Duplicate entry '28415238' for key 'PRIMARY' \r\nCode: 1062 SQL: insert into apiitem (ID,pid,tam,melos,amman,ama,emaes,lastactive,rered,updDate,validstart,validend,act) values (?,?,?,?,?,?,?,?,?,?,?,?,?)"} [Main] at f8a3c5
20220811 06033502 ! DB mormot.db.sql.zeos.TSqlDBZeosStatement(0c628280) Prepare 318us insert into apiitem (ID,pid,tam,melos,amman,ama,emaes,lastactive,rered,updDate,validstart,validend,act) values (?,?,?,?,?,?,?,?,?,?,?,?,?)
20220811 06033500 ! + mormot.orm.server.TRestOrmServerBatchSend(0c8b82d0).EngineBatchSend TOrmApiitem inlen=138
20220811 06033461 ! trace mormot.rest.sqlite3.TRestServerDB(063d7bc0) BatchSend {"TRestBatch(0c718938)":{Table:20346408,Count:1,SizeBytes:137}}Thanks a lot @ab
when using Batches we can set the 'boInsertOrReplace' to have replaced a row if its primary key is the same with the inserted new row.
How can we have it in ORM/REST add or addorupdate functions of mormot2?
If I understand it correctly, addorupdate is based on IDvalue to issue an add or an update.
How can we have an 'INSERT OR REPLACE' sql statement for ORM/REST in order not to have to check if a record with the same primary key value exists in the table ?
Thank you in advance
Apologies for not noticing it in the documentation. To be honest I check the TsqlRawBlob and it is not mentioned there. My thought is that this violates the standard for JSON.
If it is needed only to have to be base64 decoded for RawBlob fields and to be recognized by the framework, every content from REST before entered to a RawBlob field must anyway be base64 decoded or am I missing something?
I was afraid that this could be a kind of feature, but please help me understand the need for that.
I will follow your suggestion anyway and thank you
In the following REST response from Mormot2 everything seems ok and even with Postman nothing seems to be a problem. Mormot2 as a client consumes it ok.
{
"result": [
{
"RowID": 1,
"user": "jpapa",
"pass": "",
"dedo": "4EfSGzZt5QLJGW1rQlMIfjxAsj0RxnlOHqYguRB8upNmh674R5F4hxNHirHgYMDoliW9mxVTQt1ArUTieKXzTqkHKIrakbeIYV2AajqlOHSacOP9H4N+/D7asfAFFwJAB/raCA5Fo/AvWPzK5ddxyF99U+RHh+BcPhido5z+jMzOE/9Jmib6lR6pxHAV29wJuszq2RvvYcvKFD4CQxXNQGe8zgdjaZct3oZR4ENjIjkB8bIIjf+pKA2Af2YzHM8eigTxV6PJjlHDvCBAxLWnk2H1GExkdvwS",
"act": 1
},
true
]
}A flutter client does not like the above response and can not decode the base64 field
From Postman I save as file the REST response of Mormot2 and I open it with a binary viewer. Now the file contents can be shown as the following (check the beginning of the Rawblob encoded field):
{"result":[{"RowID":1,"user":"jpapa","pass":"","dedo":"οΏ°4EfSGzZt5QLJGW1rQlMIfjxAsj0RxnlOHqYguRB8upNmh674R5F4hxNHirHgYMDoliW9mxVTQt1ArUTieKXzTqkHKIrakbeIYV2AajqlOHSacOP9H4N+/D7asfAFFwJAB/raCA5Fo/AvWPzK5ddxyF99U+RHh+BcPhido5z+jMzOE/9Jmib6lR6pxHAV29wJuszq2RvvYcvKFD4CQxXNQGe8zgdjaZct3oZR4ENjIjkB8bIIjf+pKA2Af2YzHM8eigTxV6PJjlHDvCBAxLWnk2H1GExkdvwS","act":1},true]}Three bytes not acceptable for base64 encoded data are in the beginning (EF BF B0) and it is always there whatever record I am requesting. Do we need this BOM as we expect to have only base64 content? If needed, why not in other text values also?
The above response is provided by the following code in mormot2:
function Tintercode.Getuser(const id: int64; out userobj: TormDBuser):boolean;
begin result:=DBsrv.Orm.Retrieve('RowID=:('+inttostr(id)+'):',userobj,'RowID,user,dedo,act'); end;It seems a pointer problem that provides the base64 encoded content
After commit 3681 in file mormot.rest.sqlite3 line 224 if PUREmormot2 is defined we have compilation error with undefined Createmissingingtables
just for reporting
Thank you @ab.
It is very helpful if using memory tables to fill automatically comboboxes even with using sqlite's operator || for combobox's options label
Just a line of code for each combo box
I just noticed that when using the operator || you have to named with 'AS' like in the following:
memserver.Orm.OneFieldValues(TOrmtable,'(code || " " || title) as title','SQL condition GROUP BY Rowid',list); And it fills RowID as Tobject too
By curiosity only:
Does the following function work for you, when using sqlite/TRestStorageInMemory database without the above correction(RowID instead of ID in line 1154 of mormot.orm.rest)?
function OneFieldValues(Table: TOrmClass; const FieldName, WhereClause: RawUtf8; Strings: TStrings; IDToIndex: PID = nil): boolean; overload;
Thank you in advance
Thanks a lot Thomas!!!
In the above situation, the "ID" exists in the mormot2's code and it does not work with any external database that uses sqlite as medium.
RowID works for both as I tested.
mormot.orm.rest, line 1154
T := ExecuteList([Table], SqlFromSelect(Table.SqlTableName,
'ID,' + FieldName, WhereClause, ''));When using a TrestServerDB the ID needs to be RowID, and as it is now it returns an error
if instead of TrestServerDB, the procedure OneFieldValues runs from a TRestStorageInMemory then the ID is OK
I have noticed it in other functions too: TRestStorageInMemory behaves differently with the RowID/ID that the TrestServerDB, both RowID and ID are accepted in TRestStorageInMemory but thιs is not allowed for TrestServerDB. TrestServerDB returns an error for ID and works only with RowID.
Too complicated. I will stay with the empty TormSong json object in the response
Thank you a lot @ab
When using Interfaces for an API server, during processing Mormot2 returns an JSON object, like the following, describing Error:
{
"errorCode": 406,
"errorText": "IRestinterface.myprocedure failed parsing 2ndparameter: UTF8String from input JSON"
}
In an interface based rest call our code (inside the procedure/function called) will check the parameters/arguments provided if they are inside the accept range. If not an error should be returned.
If we want to have a custom response like the above, how can we do that?
Lets see the following interface function
function GetAsong(const SongId:integer; out song:TormSong):boolean;
begin
result:=db.orm.retrieve(SongId,song);
end;
if no song with the requested SongId exists, a message like the above Json object error will be a better solution to be returned, instead of an empty TormSong structure and a false value.
I tried to use the following:
ServiceRunningContext.Request.Call.OutBody:='';
ServiceRunningContext.Request.Call.OutStatus:=404;
ServiceRunningContext.Request.Error('%',['Song not found'],404);
but without success.
Is the above possible?
Thank you in advance
To be honest, I noticed the extended information in an interface that was public without authentication.
I agree with your arguments, it is better to have more extensive information on error and authentication is the solution. No problem for such a case
I will put the last question in an other thread as it is not connected with the above.
If I want to return such as the above kind of message as a response result after parameters' range check how can I do it?
I tried to use the following:
ServiceRunningContext.Request.Call.OutBody:='';
ServiceRunningContext.Request.Call.OutStatus:=406;
ServiceRunningContext.Request.Error('%',['Incorrect parameter'],406);
but without success
Thank you @ab
I noticed that if a parameter is missing when calling a Rest interface procedure, Mormot2 returns a useful description of the problem:
{
"errorCode": 406,
"errorText": "IRestinterface.myprocedure failed parsing 2ndparameter: UTF8String from input JSON"
}
This can be very helpful for debugging but I prefer a more generic error like "Missing parameter from call" or "Incorrect call" in a production environment.
Is it possible? Thank you in advance
It is OK. I was anxious if something is missing and I have a fake authentication from JWT and Mormot2
Thank you @ab
I am using JWT created with mormot2 that get validated by mormot2 and everything seems ok
They are created by the following code:
var jw:TJWTAbstract;
jw:=TJwtHS512.Create('Alibaba et 40 voleurs', 60000, [jrcIssuer, jrcSubject, jrcExpirationTime], [], 50);
try
result:=jw.Compute(['id',pt.IDValue],'MyServer','user');
finally
jw.Free;
end;The validation is done with one line code:
srv.JwtForUnauthenticatedRequest:=TJwtHS512.Create('Alibaba et 40 voleurs', 60000, [jrcIssuer, jrcSubject, jrcExpirationTime], [], 50);The above works.
From curiosity I copied the created JWT to the webpage for evaluation it exists in jwt.io & token.dev and signature is shown as invalid
I tried with 0 aSecretPbkdf2Round and with empty password or by entering the password in jwt.io but signature is always invalid.
Is there a mistake from my side in Mormot2?
Thank you a lot @ab
I was thinking that in an array only TRestServerDBObjArray[0] will process the inserts and above [0] will do the read process
Thank you @ab, no problem for that.
An other question I have is the following:
Lets use a scenario like that in https://synopse.info/forum/viewtopic.php?id=5788
multiple TREstServerDB like in TRestServerDBObjArray = array of TRestServerDB;
each client calls an interface and the interface is getting randomly one TREstServerDB instance to communicate.
if EngineAddUseSelectMaxID=false, each TRestServerDB will have its own fEngineLockedMaxID
when inserting new records using a randomly selected TRestServerDB instance, how the fEngineLockedMaxID is shared between them, without a select max(ID) query?
Difficult day today
TRestStorageExternal.EngineAdd does not find an ID so it requests a new from EngineLockedNextID
The problem is that in line 970 from mormot.orm.sql, if EngineAddUseSelectMaxID=true, it is always requested from DB, even fEngineLockedMaxID>0 and even if it is a batch of records for the same table
if (fEngineLockedMaxID = 0) or
EngineAddUseSelectMaxID then
RetrieveFromDB;
inc(fEngineLockedMaxID);As the previous record was not yet inserted in the database the fEngineLockedMaxID is getting again again the same value
I am using the following from mormot.orm.core line 4447:
function Add(Value: TOrm; SendData: boolean; ForceID: boolean = false;
const CustomFields: TFieldBits = []; DoNotAutoComputeFields: boolean = false): integer;SendData=true ForceID=false
it happens using the above code, but with
ba.Add(dr.objs[n],True,false)I am setting EngineAddUseSelectMaxID with the following:
((DBsrv.Orm as TRestOrmServer).StaticTable[Tormapiobjs] as TRestStorageExternal).EngineAddUseSelectMaxID:=true;where DBsrv is a TRestServerDB
An other bug I noticed using BatchSend is the following:
if EngineAddUseSelectMaxID for a table is enabled then in every record/orm item a select max is issued resulting to the same RowID for all new items in the batch
Now it works. Thank you a lot @ab
It is not solved
mormot.orm.sql, line 1071 Why the BatchOptions argument is empty?
SQL := JsonDecodedPrepareToSql(Decode, ExternalFields, Types,
Occasion, [], {array=}true);That will result in line 2129 to have again Batchoptions empty
result := EncodeAsSqlPrepared(Decoder, fTableName, Occasion,
fStoredClassMapping^.RowIDFieldName, BatchOptions);and EncodeInsertPrefix will not add a replace into
Thank a lot for your work
I am trying to have a TRestBatch to enter 10000 records in a table at server side. Connection is with Zeos 8.0-patches (from GitHub) to Mariadb using Delphi 11.1/win11
ba:=TRestBatch.Createnorest(DBsrvPool.GetDBsrv.Model, Tormapiobjs, 3000, [boInsertOrReplace,boExtendedJson]);
for n:=0 to 10000 do ba.Add(dr.objs[n],True,true);
BatchSend(DBsrvPool.GetDBsrv.Orm);If a record with the same primary id exists I am getting an error in ZDBcMySql, line 1263:
First chance exception at $7504F192. Exception class EZSQLException with message
'SQL Error: Duplicate entry '24' for key 'PRIMARY'
Code: 1062 SQL: insert into apiobjs (ID,ecode,bcode,galid,cnameonly) values (?,?,?,?,?)'.
Process mormottesting.exe (21148)
I expected a replace instead of insert, Mariadb & mysql support REPLACE.
Should I configure something and I did not?
Thank you in advance
Thanks a lot @Prometeus
[dcc32 Error] mormot.crypt.secure.pas(2093): E2250 There is no overloaded version of 'FormatString' that can be called with these arguments
When using the THttpRequestClass that it is returned by MainHttpClass function (both TWinHttp and TCurlHttp can be returned), and especially when using the class functions GET/POST/PUT/DELETE that are derived from THttpRequest , is it possible to enable automatic handling of received from the http server compressed data with deflate+gzip?
if not, is that possible though an example with mormot2's libraries?
Thank you in advance
Now it works perfectly!!!
Thank you a lot @ab
The idea is to have a generic function to create and fill a Ilist<Torm> from a json array, without using generics in the function definition
Reading from mormot2's code, I found that I can create a TIListParent with plist:=Table.NewIList(list);
Reading fast data with plist.Data.LoadFromJson(json); from json that came from a Retrievelistjson does not work. Only ID is set, all published properties from torm objects are not.
Does it need something special in the Json array?
Does plist.data or ilist<Trom> need something more before/after LoadFromJson?
Thank you in advance
Trying an other Torm class I do not get an access violation, but only the ID is correct, all the other is garbage
Try this orm class
TOrmApicities=class(Torm)
private
Fnam:rawutf8;
Fnomosid:ptrint;
Fact:boolean;
published
property nam:rawutf8 index 250 read Fnam write Fnam;
property nomosid:ptrint read Fnomosid write Fnomosid;
property act:boolean read Fact write Fact;
end;With the following json data:
[{"ID":1,"nam":"ΑΘΗΝΑ","nomosid":5,"act":1},{"ID":2,"nam":"ΠΕΡΙΣΤΕΡΙ","nomosid":5,"act":1},{"ID":3,"nam":"ΜΑΡΟΥΣΙ","nomosid":5,"act":1},{"ID":4,"nam":"ΑΙΓΑΛΕΩ","nomosid":5,"act":1},{"ID":5,"nam":"ΑΓΙΑ ΠΑΡΑΣΚΕΥΗ","nomosid":5,"act":1},{"ID":6,"nam":"ΧΑΛΑΝΔΡΙ","nomosid":5,"act":1}]Thank you @ab.
I tried first with a local rawutf8 variable but the same happens. Also the same problem happens with the new commit
The problem should be in TRttiJson.ValueLoadJson or somewhere in json parsing.
As I said I am using the result of a Retrievelistjson from an other retrieval that is like the following:
[{"ID":-1,"bcode":269,"galid":0,"cnameonly":"ΓΑΛΗΝΙΚΟ","formcod":""},{"ID":2,"bcode":269,"galid":0,"cnameonly":"ΓΑΛΗΝΙΚΟ","formcod":""}]Is something missing from TIListParent's processing?
I am trying to have a general procedure to fill an ilist<> from a json string
The following code seems to fill the ilist<> (it has correct amount of items) but when trying to access the items in the ilist<> I am getting a access violation error and contents are garbage.
procedure GetIList(Table: TOrmClass; var list; const json: Rawjson);
var plist: TIListParent;
begin IInterface(list):=nil;
plist:=Table.NewIList(list);
plist.Data.LoadFromJson(Putf8char(json));
end;The json is produced by a RetrieveListJson from a other call
What am I missing?
Thank you in advance
Do TObjectListPropertyHashed / TObjectListHashed exist in Mormot2 ?
How are they renamed?
About mobile apps ?
expired JWT automatic re-creation?
Too much in one message and I donot help....
For the Linux and the TSynThreadPool.QueueLength everything is ok.
run the example with fastmm4 and report leaks enabled in windows with delphi, press button1, many times button4 and then button2. When everything is finished, close the app. it will report all objects in the IOCP as leaked even with the above commit
I will check OnThreadStart/OnThreadTerminate. Thank you a lot @ab
The above commit did not help. The problem is the following in windows:
if I destroy the TSynThreadPool, all items in IOCP are not destroyed. The code in the commit should be in the destroy procedure, or not? Is it needed in the Execute procedure too?
In linux, we do not have the queue of IOCP if more requests are pushed than the available threads. There is a fPendingContext array with a maximum value of 10000 as length and we can use it, if it is created with
ttr:=TmySynThreadPool.Create(4,true);Am I right?
Also an other question for a perfect use of TSynThreadPool:
I need to initiate a couple of object for use by each thread.
Is it possible to have a derived class in TSynThreadPoolWorkThread to override its create/destroy procedures?
Or, should I use an external array coupled with TSynThreadPool.WorkThread for each TSynThreadPoolWorkThread object's data?
Do you propose something more in the following example?
https://gist.github.com/dkounal/993c8d5 … fe3c4f81bb
In linux I should create it like the following in order to have a queue for more that the running threads request in Push?
ttr:=TmySynThreadPool.Create(4,true);Also, I noticed in windows, that if destroyed before finishing all iocp queue, the objects in the iocp queue are not freed. How to overcome this?
I am already using TSynBackgroundTimer.Add and it is working excellent for having a serial list of tasks that they are completed one-by-one
TSynThreadPool is really interesting and can do what I am thinking about parallel processing using a predefined number of threads
Push is to add a procedure to be executed by a thread as I understand
Is there an example that I can play with?
Thank you in advance
When we have a websocket service that needs to use a thread to finish long time work, we can use the TRestThread to create a thread that will do the job as shown in the example restws_longworkserver.
In a production environment, I face two problems:
a) Many many websocket requests that create a TRestThread will slow down the server. A solution is to have a pool of requests to be completed by a limited number of threads and return the output. TsynDictionary is probably a good solution for that. I do not know if you can propose something else.
b) Creating a thread is time consuming, and to have a pool of threads to be used on request is probably a good solution. What do you propose for that?
- Delphi has a working library for that and a couple of other libraries to help with that. What about FPC? I tried for example Quicklib and it does not work with FPC for threads.
- When does it worth to have a thread pool mechanism to deal with time consuming works in a REST server?
- Does mormot2 has already such a threadpool class that could be used?
- Do you already use a multi-environment FPC library for that and which?
Thank you in advance
Check this thread too
https://synopse.info/forum/viewtopic.php?id=6395
Last months I am looking for a solution to use a cloud to store database data encrypted and having the encryption being applied at the client side. In this way,you can have a centralized datastorage (that does not know the real contents of data it stores) whose data are encrypted/decrypted when accessing it from the client that access it.
Reading the documentation for mirroring and have proxies with mormot I was wonder if the following is somehow possible:
Mormot uses SQLITE as mid-end node between and external database to create a backend.
Is it possible for sqlite to encrypt inside the mormot framework the data that will be saved in the database? With pure sqlite and a remote filesytem I believe it is possible. But what about an external database?
My main purpose is to have encrypted data in a cloud server with a mormot backend in an other place that does the encryption/decryption of data in the database for its clients
Thank you in advance
1. I believe using ZEOS database connection will allow you to use ADO
2. Take a look in examples from Mormot1 and use Mormot2 to start learning. Read the documentation, search the forum and invest time. Start creating only an interface based API and do not expose the database bindings.
3. It has to do with what you want to do. It is a feature rich framework for API creation.