You are not logged in.
- Topics: Active | Unanswered
- Index
- » Search
- » Posts by aloe
Pages: 1
#1 Re: mORMot 2 » SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands » 2024-01-26 22:34:06
Hello,
Regarding inline parameters:
Changed as you instructed and all "SQLITE_MISUSE" errors disappeared !!!
The version without inline parameters was working with mormot v1.
In production and with 4-5 million calls daily, never seen such errors.
I would never find the problem without your help.
----------------
Regarding the recommended use of these custom functions:
Of course i would just use more complex sql instead of functions if i could.
But these functions execute logic and they return json datasets (multiple rows) in a text field.
We use them only for "in memory" database.
That acts as fast and scalable sql data cache for a website.
Something like below from python world (or am i wrong?):
https://docs.peewee-orm.com/en/latest/p … e_function
https://stackoverflow.com/questions/451 … -in-python
https://docs.peewee-orm.com/en/latest/p … -functions
But the mORMot framework allows much simpler implementation and it runs 500 times faster.
----------------
THANK YOU!
#2 Re: mORMot 2 » SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands » 2024-01-26 08:33:47
Thank you for your time looking at this issue. I will check and revert based on your comments and will reply back today later. Thanks again!
#3 Re: mORMot 2 » SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands » 2024-01-25 21:38:41
I am adding a git project with code to reproduce.
Pls see readme of the project first, it has a screen shot and error log text
#4 mORMot 2 » SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands » 2024-01-25 10:31:34
- aloe
- Replies: 4
Hi everybody,
i have an issue when trying to upgrade a project from mormot 1 to 2:
------
It is a rest server serving data from an in memory sqlite3 database.
And it includes several custom functions like:
DB.RegisterSQLFunction(InternalSQLFunction_Get_Some_Other_TableData,2,'function_getdata');
------
used like this: select some_id, function_getdata(some_id) from one_table;
------
Procedure InternalSQLFunction_Get_Some_Other_TableData
is implementing logic and returns a json dataset from another table.
for example
sql= select id1,name1 from another_table where master_id=input_id
----------
The problem is error SQLITE_MISUE in "fieldsToJson" method of unit mormot.db.raw.sqlite3,
called from execute of the same unit.
----------
select some_id, funcion_getdata(some_id) from one_table order by some_id limit 1;
select some_id, funcion_getdata(some_id) from one_table order by some_id limit 70;
When running the first sql, the error never appears.
When running the 2nd sql, the error appears almost always
it seems like when running the sql WITHIN the custom function multiple times, for each row,
the "Request" (integer) property is zeroed somehow and producing the error in fieldsToJson.
----------
I tried following:
- mutexes/critical sections in the custom function code: no luck
- the function to get data from a separate identical database : same result
- running
TRestServerDB(aDB).DB.ExecuteJson(sql) instead of
TRestServerDB(aDB).ExecuteJson([],sql)
made some difference, i was happy it would be solved, but when testing the error still comes up, not so frequent.
----------
Now, either i am doing something terribly wrong, that was working fine with mormot v1,
or i need to try changes in mormot.db.raw.sqlite3 witch i don't dare to even think about 
I can send code and reproduce examples...
Can anybody help please?
#5 Re: mORMot 1 » Visual Studio 2015 and mORMot » 2016-03-24 14:33:32
Hi Corrado, answering also to your private message (i received an email through the auto mailer).
As Arnaud Bouchez wrote above, such a client needs to implement just an http get + json.
There are many ways, a very simple one:
run a mormot server, listening to IP 127.0.0.1 port 7000 for example.
At the client side, from Visual Studio C# or VB.NET:
http get this link: http://127.0.0.1:7000/root/?SQL=select * from your_table_name
it will return a json
Put the link in a browser to see it, if security is disabled you ll see data.
You have to convert this json to C# dataset and then you have it all.
Depends on what else you want to do.
If you want to edit data, I suppose above is not the best way to work.
If you need any example code for the mormot server side, i would start from the examples in the package.
#6 Re: mORMot 1 » Softwares created using mORMot » 2015-12-12 13:23:33
A use case "use mORMot to scale a web application to unlimited users"
Online Gaming Platform
Database layer: Interbase 2009, Intebase XE7, Oracle
Application server layer: mix of Delphi and NET
web server layer: NET
New added:
In memory database + application server layer: mORMot
The problems:
The amount of concurrent active web users increased rapidly,
and we needed to create a kind of cache layer that would serve data instead of the database.
Sounds like a classic easy to solve problem using cache or mirror databases, but:
- amount of data changes in ive betting datasets is 4.000.000 per day (800 updates/sec at peak time)
- data need to be available if requested by the end user in real time (max 500 millisec after change)
- The nature of service required complex select statements (each user can have his own data filters,
means almost all db tables need to be in cache and accessible with standard sql commands)
We tried several solutions, including mirroring and in memory databases:
- SQL Server with in memory tables: needs 128GB RAM per instance. Queued data after 80 inserts/sec
- Oracle Times Ten: runs ok for selects, but too slow to load data, no way to udpate the cache in real time
- MySQL mirrors: needs the commercial version and expensive hardware to run really "in memory"
- No SQL or key-value (Mongo, Couch etc): It would require to rewrite half the system.
Could be a solution if we had 50 developers only for this.
- NewSQL (these new fancy NuoDB, VoltDB etc): They dont seem to be ready for main production systems.
- finally, made a try with mORMot and guess what...
The final solution using mORMot:
An "all in one" in-memory SQLite3 + http server as implemented within mORMot.
-Data is loaded at startup (in 30 sec) from the "main" database.
-Real time data updates after initial load:
Interbase XE7 --> creates an "Interbase event" --> updates the in-memory SQLite3 in mORMot
-In parallel, the mORMot serves all http requests at the application server layer.
In production since October 2015.
Runs super fast and reliable.
hardware required: usual servers, 12-16GB RAM
Can scale to theoretically unlimited users by just adding low cost machines to run separate instances of mORMot based service.
And a big THANK YOU to Arnaud Bouchez and his friends over there!
Pages: 1
- Index
- » Search
- » Posts by aloe