Tips and Frequently Asked Questions
Executing SQL statements directly with IDBConnection
You might have some situations where you want to execute an SQL statement directly in the database, bypassing the object manager. Of course you could use the database access component directly (FireDac, dbExpress, ADO, etc.). But in case you want to keep the code abstract when it comes to the database access layer, and benefit from existing Aurelius connection, you can use IDBConnection to do so.
Here is how you would do it:
uses {...}, Aurelius.Drivers.Interfaces;
var
Statement: IDBStatement;
Statement := Manager.Connection.CreateStatement;
Statement.SetSQLCommand(TheSQLStatement);
Statement.Execute;
If the statement is queryable, you can retrieve results using ExecuteQuery method which returns an IDBResultSet interface:
var
Statement: IDBStatement;
ResultSet: IDBResultSet;
Statement := Manager.Connection.CreateStatement;
Statement.SetSQLCommand(TheSQLStatement);
ResultSet := Statement.ExecuteQuery;
while ResultSet.Next do
Value := ResultSet.GetFieldValue(SomeFieldName);
Finally, for both types of commands you can set parameter values:
{...}
Params := TObjectList<TDBParam>.Create;
try
Statement := Manager.Connection.CreateStatement;
Params.Add(TDBParam.Create('id', ftInteger, 15));
Statement.SetSQLCommand('Delete from Customer Where Id = :id');
Statement.SetParams(Params);
Statement.Execute;
finally
Params.Free;
end;
Changing the way Aurelius saves dates in SQLite database
By default, Aurelius saves date values in SQLite as native Delphi TDateTime values, i.e., as double (float) values. This works fine if you are only using Aurelius to access the SQLite database. But if you have a legacy SQLite database or want other applications to read the database directly, you might need to use a different format. Aurelius offers two other alternative formats, which is Julian (saves the date values as Julian date times) or Text (which saves in text format “yyyy-mm-dd”).
To do that, add this code to the beginning of your application (choose the date time you want and uncomment the correct line).
Uses
Aurelius.Sql.SQLite, Aurelius.Sql.Register;
Var
SQLiteGenerator: TSQLiteSQLGenerator;
begin
SQLiteGenerator := TSQLiteSQLGenerator.Create;
// SQLiteGenerator.DateType := TSQLiteSQLGenerator.TDateType.Delphi;
// SQLiteGenerator.DateType := TSQLiteSQLGenerator.TDateType.Julian;
SQLiteGenerator.DateType := TSQLiteSQLGenerator.TDateType.Text;
TSQLGeneratorRegister.GetInstance.RegisterGenerator(SQLiteGenerator);
Composite keys
Aurelius fully supports tables that use composite keys. One of our goals was to provide fully flexibility and maximum possibility of using Aurelius with existing applications. If you already have a database model with composite keys that is running over many years with many customers, it’s fully understandble it’s hard to change that and you can use Aurelius with it, no problem.
But if you are designing a new application or have an opportunity to change that, we strongly advise you to do so. The reason is that it will increasing complexity and you will lose some Aurelius automatic features. Just as an example, when you do something like this in Aurelius:
Manager.SaveOrUpdate(Object);
It will automatically INSERT (Save) or UPDATE the record in database given the current state of Object. If the Object has an Id, it will be updated. Such mechanism is not possible with composite keys (or “None” generators) because even if you are inserting a new record, you must provide the Object with some of id fields already filled, so in this case Aurelius can’t tell if it must do a Save or Update.
As another consequence of this, cascades become also trickier. Suppose you have a complex object tree that you have several associated objects. When you persist the object, Aurelius will try (if you configured to do so by using SaveUpdate cascades) to also persist associated objects so you don’t need to do it manually for each of them. Such mechanism becomes also complicated with “None” generators because Aurelius can’t tell if it must save or update the objects, thus you would have to do it manually.
In conclusion, as previously said, Aurelius fully supports complex database structures (it can even use relationships that do not relates to primary keys, but unique keys, for example). However, the most “standard” (or simple) your structure is, the more automatic and neat features of Aurelius you can use. So please take that in consideration when designing your database with composite keys.
