Page 1 of 1
Custom User Aggregate Fuction - SQLite
Posted: Thu 29 Aug 2019 13:53
by m227
Hi, you are boasting that "Custom SQL aggregate functions are supported" since UniDAC 7.1
I have devoted one hour and did not find anywhere how to use this facility.
Could you enlighten me?
The only trace I found was for LiteDAC:
viewtopic.php?f=48&t=38101&p=132659&hil ... ns#p132659
Re: Custom User Aggregate Fuction - SQLite
Posted: Tue 10 Sep 2019 15:54
by MaximG
The following code sample demonstrates how to register an aggregate function <your function name> with 1 argument:
Code: Select all
...
UniConnection.Connect;
TLiteUtils.RegisterAggregateFunction(UniConnection, '<your function name>', 1, YourStepProcedure, YourFinalFunction);
...
...
procedure YourStepProcedure(InValues: array of Variant);
begin
...
end;
...
function YourFinalFunction: Variant;
begin
...
end;
We'll add the description to the product documentation.
Re: Custom User Aggregate Fuction - SQLite
Posted: Wed 11 Sep 2019 09:07
by m227
Hi, thank you for a response. I managed to create MEDIAN function and it works. The only problem is that if I want to use such function twice in a query it fails, as when passing parameters to <YourStepProcedure> i don't know from which column they come. My sample SQLite query.
Code: Select all
CREATE TEMPORARY TABLE TestData1 (a INT);
CREATE TEMPORARY TABLE TestData2 (a INT);
INSERT INTO TestData1 VALUES (7),(14),(21),(3),(9),(2),(4); -- Median = 7
INSERT INTO TestData2 VALUES (7),(14),(21),(3),(9),(2),(4),(5); -- Median = 6
SELECT Median2(TestData1.A), Median2(TestData2.A)
FROM TestData1, TestData2;
Shall I use any MOD to differentiate myself from which column data came?
Re: Custom User Aggregate Fuction - SQLite
Posted: Thu 12 Sep 2019 13:34
by Stellar
When invoking a handler of a user defined function, SQLite doesn't pass any information about the function context.
You can try solving the issue in two ways:
- add two functions with identical logic
- add one more parameter to the function and pass a unique argument for each of the functions. For example:
Code: Select all
SELECT Median2(1, TestData1.A), Median2(2, TestData2.A) FROM TestData1, TestData2;