Custom User Aggregate Fuction - SQLite

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
m227
Posts: 62
Joined: Mon 06 Aug 2007 12:41

Custom User Aggregate Fuction - SQLite

Post by m227 » Thu 29 Aug 2019 13:53

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

MaximG
Devart Team
Posts: 1438
Joined: Mon 06 Jul 2015 11:34

Re: Custom User Aggregate Fuction - SQLite

Post by MaximG » Tue 10 Sep 2019 15:54

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.

m227
Posts: 62
Joined: Mon 06 Aug 2007 12:41

Re: Custom User Aggregate Fuction - SQLite

Post by m227 » Wed 11 Sep 2019 09:07

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?

Stellar
Devart Team
Posts: 359
Joined: Tue 03 Oct 2017 11:00

Re: Custom User Aggregate Fuction - SQLite

Post by Stellar » Thu 12 Sep 2019 13:34

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; 

Post Reply