Example SQL queries in mORMot

Question

I'm using Synopse mORMot to access a SQLite database from Delphi 7. I wish to establish a connection and query the database directly via SQL without using the ORM (Object-relational mapping) features of mORMot.

Could you provide code examples which execute SQL queries without relying on the ORM functionality of mORMot?

Answer 1

In short: it is better to use the SynDB.pas layer over SynSQLite3.pas, via its SynDBSQLite3.pas classes, since it will allow your code to work in the future with any database accessible via OleDB / ODBC or even direct access (e.g. for Oracle).

For instance, using a variant for holding column data:

procedure Test(Props: TSQLDBConnectionProperties);
var Customer: Variant;
begin
  with Props.Execute('select * from Customers where AccountNumber like ?',
    ['AW000001%'],@Customer) do
    while Step do
      assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

var Props: TSQLDBConnectionProperties;

  Props := TSQLDBSQLite3ConnectionProperties.Create('databasefile.db3','','','');
  try
    Test(Props);
  finally
    Props.Free;
  end;

So for your question, read the SynDB-related part of the mORMot documentation, and all the related blog articles. Those classes are used by the ORM, but you can use those without the ORM.

To start with, there is a TQuery wrapper in SynDB.pas which works very well with SQLite3 and allow "classic" code-level programming.

But there is not 100% RAD direct access with those units. Just some TClientDataSet "fillers". This was not their purpose: they want fast and direct access to the DB, in code, not via the UI plumbing.