Calling databasefunction from Aurelius

I am looking for a way to call more complicated SQL from Aurelius using a function.
With the Database modeller I created a function (in this case for PostgreSQL) that returns a table by calling

functionname(parameter).

This works great in traditional database connections, but is it possible to do this in some way with Aurelius? I have looked at the sql projection in the Aurelius manual, but all these projections start with a table and I don't have a table in this case.

The same 'problem' arises with a database View.
How can I use a View in Aurelius?
I built one with the Database Modeller, but there is no code generated for it in Aurelius.

You can just define the view name (or the function with a fixed parameter) in the [Table] attribute, either in your existing class or in a separated class just for that purpose. Of course, you would not be able to perform modification operations (save, update, delete), only retrieve data.

Ah, that could work.
Would you please take a look at the first post too, Wagner? Thank you.

I answered that already, it's the same approach: a view name, or the function with a fixed parameter. There is no way to pass a parameter dynamically though, unless you programatically change the table name every time you want to perform a query with that class.

So I guess it's not possible to do queries that a bit more complex in Aurelius, because a) Aurelius itself can't do it and b) I can't do it with a function. That's too bad, because Aurelius is a really good product, but it's not that usable if queries that are a little more complex are not possible.
Example of a query I can't get working:
I want to count the number of times the value 'gegeven' distinctly appears in a logfile for each customer during a certain period of time.
I can put that in a function, but if I can't run the function with a period of time it can't be done:


SELECT c.naam, COUNT(DISTINCT log.gegeven) as "gegevens"
FROM customers c
JOIN logging log ON c.licid = log.licid
WHERE (log.gegeven<>'') AND (log.datum>'20160131') AND (log.datum<'20160301')
GROUP BY c.naam
HAVING (COUNT(DISTINCT log.gegeven)>1)


Any chance this might be possible in the near future?

You can do that specific query with Aurelius. I don't know your exact model, but I'm supposing Log has an association (foreign key) to customer:



Results := Find<TLog>
  .CreateAlias('Customer', 'c')
  .Select(TProjections.ProjectionList
    .Add(TProjections.Group('c.Naam'))
    .Add(TProjections.Sql<Integer>('COUNT(DISTINCT {gegeven}')).As_('gegevens'))
  )
  .Where(
    not TLinq.Eq('gegeven', '') and TLinq.Gt('datum', '20160131') and TLinq.Lt('datum', '20160301')
    and TLinq.Gt('gegevens', 1)
  )
  .ListValues;


Wagner R. Landgraf2016-09-23 19:50:57

I see. By using the .Sql projection you add the distinct count.
I am looking for a way to retrieve datasets from the database using an existing IDBConnection Interface, but not using Aurelius, only a simple TQuery component, in my case TFDQuery (I am using FireDAC to connect). Is that possible, or do I have to set up a completely separate connection to execute these queries? It drives me nuts not being able to retrieve everything I want from the database.

Wagner, the query won't work. I corrected some small errors in the code, but I still get an error.
I now have:


Results := OM.Find<Tlogging>  .CreateAlias('customers', 'c')  .Select(TProjections.ProjectionList    .Add(TProjections.Group('c.naam'))    .Add(TProjections.Sql<Integer>('COUNT(DISTINCT {gegeven})').As_('gegevens'))  )  .Where(    not TLinq.Eq('gegeven', '') and TLinq.Gt('datum', '20160131') and TLinq.Lt('datum', '20160301')    and TLinq.Gt('gegevens', 1)  )  .ListValues


which gives me the error ' Property "customers" not found on class "Tlogging"'.

Ronald, the "customers" property not found is because of your mapping, I don't know your mapping.

But let's simplify, if you really just want to execute raw queries without using ORM, then you can just follow these steps:
http://www.tmssoftware.com/site/kb/KB_20151215.htm#9

Wagner, thanks for the answer. I thought that the mapping was obvious because the query shows where the joins are.
The possibility to execute raw queries is exactly what I need! If I read it correctly this is also the way to execute databasefunctions with parameters...
While implementing an example I found out that there are very little methods to use for IDBResultSet. I would very much like to have access to the number of fields and the fieldnames. That gives me the opportunity to create random lists with random queries. Now I have to save the number of fields and their names somewhere else when implementing a query.

You can do that by retrieving the IDBDatasetStatement from your existing statement (IDBStatement):



 var DBStmt: IDBDatasetStatement;



if not Supports(Statement, IDBDatasetStatement, DBStmt) then
    raise Exception.Create('not suppored');
Dataset := DBStmt.GetDataset;


this way you can retrieve the underlying TDataset used for the connection, and of course with all the methods and properties available in a TDataset (FieldCount, FieldByName, etc.).
Note it's built like that because not all Aurelius drivers (statement providers) support TDataset. The most notable example is our native SQLite driver. But if you are using a specific component (FireDac, dbExpress, etc.) that is dataset-based, you can safely use that.


Wagner R. Landgraf2016-09-30 13:32:25

Thanks, Wagner, works like a charm!

Sorry to be back at this, but I have one last issue that I can't solve.
I created a databasefunction that takes two arguments. It does not have to return a set, it just has to run. And it runs ok in PostgreSQL.
I do this:


var
  Statement : IDBStatement;
  ResultSet : IDBResultSet;
  cQuery    : string;
  OM  : TObjectManager;
....
  OM := TXDataOperationContext.Current.GetManager;
  Statement := OM.Connection.CreateStatement;
  // PostgreSQL query
  cQuery := 'select * from kopieer(' + IntToStr(nID) + ', ' + IntToStr(nID2) + ')';
  Statement.SetSQLCommand(cQuery);
  Resultset := Statement.ExecuteQuery;


When I run this function I always get an errormessage that says "ERROR: cached plan must not change result type".
I tried everything I could think of to give back as a return value: integer, string, NULL, Boolean, a table with one row, nothing helps. If I could get this running I can run big copy-statements in the database, which is much much faster.
I also tried Statement.EXEcute, but that doesn't work as well, because it states it gets a set back. Which is true, the function always returns something that looks like a set, even if the returnvalue is void.
I'm stuck now. Hope you can enlighten me.

Are you using FireDAC under the hood? What is the minimal FireDAC code that makes it work? I mean, is it working well when you just use FireDAC (no Aurelius interfaces involved)?

I use FireDAC under the hood, but I have only used it with Aurelius.
I use a datamodule with a TFDConnection and I initialise it like this:


  FDConnection1.DriverName:='PG';
  FDConnection1.Params.Clear;
  FDConnection1.Params.Add('DriverID=PG');
  FDConnection1.Params.Add('Database='+cDatabase);
  FDConnection1.Params.Add('Server='+cServer);
  FDConnection1.Params.Add('Port='+cPort);
  FDConnection1.Params.Add('User_Name='+cUserName);
  FDConnection1.Params.Add('Password='+cPassword);
  FDConnection1.Params.Add('LoginTimeout=30');
  FDConnection1.Params.Add('ExtendedMetadata=True');
  FDConnection1.Params.Add('OidAsBlob=True');
  FDConnection1.Params.Add('UnknownFormat=BYTEA');
  Try
    Verbinding := TFireDACConnectionAdapter.Create(FDConnection1,'PostgreSQL',True);


I use it when I start the server:


    FServer := THttpSysServer.Create;
    Try
      FDBConnectionFactory := TDBConnectionFactory.Create(
         function: IDBConnection
         var
            DM: TDM;
          begin
            DM := TDM.Create(nil);
           DM.VerbindPostgreSQL(FServerAdres,FPoort,cDatabaseNaam,FInlogNaam,FInlogPassword);
            Result := TFireDacConnectionAdapter.Create(DM.FDConnection1, DM);
          end
      );

      FConnectionPool := TDBConnectionPool.Create(nMaximumAantalVerbindingen,FDBConnectionFactory);
      FXDataServerModule := TXDataServerModule.Create(XDataServerBaseURL, FConnectionPool);
      FServer.AddModule(FXDataServerModule);
      FServer.Start;


Ok, but we have at least to isolate the problem to either Aurelius or FireDAC. I'm wondering if you have the same error if you use TFDQuery directly, without using IDBStatement.

I'm not sure how to test that. I use IDBStatement from a ServiceImplementation, where I can't reach the FD component directly.

I have made a small new program with only FireDAC, just to test it with
FDQuery, as you suggested, and that gives me the same error, so it is
not Aurelius related....
So I'm stuck now with this one, because getting answers from someone at Embarcadero is almost impossible. Thanks for your help.