mORMot Open Source

aloe

Member

From: Greece

Registered: 2015-08-17

Posts: 6

SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands

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?

aloe

Member

From: Greece

Registered: 2015-08-17

Posts: 6

Re: SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands

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

https://github.com/info-synapse/debugm2

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands

It is hard to reproduce here because I don't have a recent Delphi IDE revision installed.
So I tried with the mORMot HTTP client, and it returned always an error 400.
Then I just wrote:

  resulttext := DB.ExecuteJson([], Editsql.Text);

and reproduced it.

Some remarks:

1) I am almost sure that such a custom function should not call the DB from itself.
This is not supposed to work, and a wrong idea.
Do your request in a single - more complex SQL select.

2) Always use inline parameters for you statements.

3) If I replace the plain parameter with an inlined parameter, I don't get any error:

'select count(*) as cc from wsbet_events where event_id=:('+IntToStr(aevent_id)+'):')

aloe

Member

From: Greece

Registered: 2015-08-17

Posts: 6

Re: SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands

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!

aloe

Member

From: Greece

Registered: 2015-08-17

Posts: 6

Re: SQLITE_MISUSE in fieldstojson request=0 on multiple execute commands

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!