Exceptions when executing custom functions on the external tables

lfyey121 · 2026-05-25 01:18:06

I have two Torm classes: TEmploy and TDepartment.
Both of them are mapped to external tables. Additionally, I have defined a custom function named：“hzpy”, whose function is to obtain the pinyin of Chinese characters.
In the SOA function, I executed server.server.executeList([aOrmClass], 'SELECT ,ID,code,name,hzpy(name) FROM employ), and when aOrmClass is TEmploy or is an empty value, an error occurs, indicating that the hzpy function definition for this external database does not exist. However, when aOrmClass takes on other values, such as TDepartment, the code will execute correctly instead. By tracing the code, I found that at this point, TRestServerDb.MainEngineList will be executed. IRestOrm is there any way to directly call the mainEngineList? Or is it that I'm doing something wrong?

ab · 2026-05-25 07:28:41

What is the result of InternalAdaptSql during the call process?

What is the exact error message?

How do you define your tables? (internal/external...)
How/at which level is defined your function?

lfyey121 · 2026-05-25 08:24:44

the error is :
TSqlDBOleDBConnection:OleDB error [](0x80040E14)- (line 1):'hzpy' is not a recognized built-in function name. in file 'mormot.db.sql.oledb.pas' at line 1894:

TEmploy：ID（map to External empid):integer;code,name:RawUtf8;
TTrader: ID(map to external traderId):Integer;code,name,phone:RawUtf8;

My RestServer class uses TRestServerDb. External database:mssql 2012,
I used sql: select rowid as Id,code,name,hzpy(name) as helpcode from employ.
The custom function I created on the built-in SQLite database: Db.RegisterSQLFunction(hzpyfunc,1,'hzpy');

Last edited by lfyey121 (2026-05-25 08:33:59)

lfyey121 · 2026-05-25 08:43:21

My tracking and debugging revealed that at line 1105 of mormot.orm.server, in the TRestOrmServer.EngineList function, if a correct Tormclass is specified or if it is left as null, the result of InternalAdaptSql is: rest := TRestStorageExternal, and subsequently, rest.EngineList will be executed. And when a wrong but existing TOrmClass is filled in, then Rest is nil and the mainEngineList will be executed.
However, for my current application, it is actually necessary to execute the 'mainEngineList' to obtain the desired result, as it is a custom SQLite function.

Last edited by lfyey121 (2026-05-25 08:46:13)

lfyey121 · 2026-05-25 08:54:54

It seems that we don't necessarily need to specify TRestStorageExternal. The MainEngineList can completely perform external table statement queries.

lfyey121 · 2026-05-25 09:33:58

I have discovered this method: TOrmServer.setStaticVirtualTableDirect. By calling setStaticVirtualTableDirect(false), now TOrmClass can set the correct table name

mORMot Open Source

#1 2026-05-25 01:18:06

Exceptions when executing custom functions on the external tables

#2 2026-05-25 07:28:41

Re: Exceptions when executing custom functions on the external tables

#3 2026-05-25 08:24:44

Re: Exceptions when executing custom functions on the external tables

#4 2026-05-25 08:43:21

Re: Exceptions when executing custom functions on the external tables

#5 2026-05-25 08:54:54

Re: Exceptions when executing custom functions on the external tables

#6 2026-05-25 09:33:58

Re: Exceptions when executing custom functions on the external tables

Board footer