mORMot Open Source

leus

Member

Registered: 2012-09-05

Posts: 79

MySQL / FireDAC / ORM test with existing database

I'm doing some experiments with using MySQL and FireDAC. My code is as follows (simple form with a single button named btnConnect):

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls;

type
  TForm1 = class(TForm)
    btnConnect: TButton;
    procedure btnConnectClick(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

uses SynCommons, mORMot, mORMotDB, mORMotSQLite3, SynDBFireDAC, SynDB,
  uADPhysMySQL, SynSQLite3Static;

type
  TSQLRecordFactura = class(TSQLRecord)
  private
    FRut: Integer;
    FFolio: Integer;
  published
    property Rut: Integer read FRut write FRut;
    property Folio: Integer read FFolio write FFolio;
  end;

{$R *.dfm}

procedure TForm1.btnConnectClick(Sender: TObject);
var
  props: TSQLDBFireDACConnectionProperties;
  aModel: TSQLModel;
  aFactura: TSQLRecordFactura;
  aRestServer: TSQLRestServerDb;
  aClient: TSQLRestClientDb;
begin
  TADPhysMySQLDriverLink.Create(Application).VendorLib :=
    'D:\Projects\test\Debug\Win32\libmysql.dll';

  props := TSQLDBFireDACConnectionProperties.Create('MySQL?localhost',
    'testdb', 'usr', 'pwd');
  try
    aModel := TSQLModel.Create([TSQLRecordFactura], 'root');
    try
      VirtualTableExternalRegister(aModel, TSQLRecordFactura, props,
        'facturas');
      aModel.props[TSQLRecordFactura].ExternalDB.MapField('ID', 'factura_id');
      aModel.props[TSQLRecordFactura].ExternalDB.MapField('Rut',
        'empresas_empresa_rut');
      aModel.props[TSQLRecordFactura].ExternalDB.MapField('Folio',
        'factura_folio');

      aRestServer := TSQLRestServerDb.Create(aModel, ':memory:', false);
      try
        aClient := TSQLRestClientDb.Create(aRestServer);
        try
          aFactura := TSQLRecordFactura.Create(aClient, 10810);
          ShowMessage(Format('Factura: %d', [aFactura.Rut]));
        finally
          aClient.Free;
        end;
      finally
        aRestServer.Free;
      end;
    finally
      aModel.Free;
    end;
  finally
    props.Free;
  end;
end;

end.

When trying to retrieve my object (aFactura := TSQLRecordFactura.Create(aClient, 10810);) I get the following exception, but the execution continues and the message gets shown with the text "Factura: 0":

Project Project1.exe raised exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) [SELECT RowID,Rut,Folio FROM Factura WHERE RowID=?;] using 3.18.0 - no such table: Factura, extended_errcode=1'.

Debugging I see that the connection seems to be working ok (dMySQL, and the mapping fields seem to be ok.) Also, if I access the connection object directly and execute queries against the database, it works, so it is connected to the actual MySQL database.

Any clues?

leus

Member

Registered: 2012-09-05

Posts: 79

Re: MySQL / FireDAC / ORM test with existing database

By the way, some log info:

20170406 02553523  +    mORMotSQLite3.TSQLRestServerDB(025CF020).URI(GET root/Factura/10810 inlen=0)
20170406 02553714 EXC   	ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT RowID,Rut,Folio FROM Factura WHERE RowID=?;] using 3.18.0 - no such table: Factura, extended_errcode=1"} at 00624CE2  stack trace API 0054376B 00543794 00406F60 
20170406 02553714 res   	{"TSQLDatabase(026D3488)":{"FileName":":memory:","IsMemory":true,"UseCache":true,"TransactionActive":false,"BusyTimeout":0,"CacheSize":-2000,"PageSize":4096,"PageCount":0,"FileSize":0,"WALMode":false,"Synchronous":"smFull","LockingMode":"lmNormal","MemoryMappedMB":0,"user_version":0,"OpenV2Flags":6,"BackupBackgroundInProcess":false,"BackupBackgroundLastTime":"","BackupBackgroundLastFileName":"","SQLite3Library":{"TSQLite3LibraryStatic(0260D1A0)":{"Version":"3.18.0 with internal MM"}}}}
20170406 02553714 debug 	mORMotSQLite3.TSQLRestServerDB(025CF020) TSQLRestRoutingREST.Error: {  "errorCode":404,  "errorText":"Not Found"  }
20170406 02553714 srvr  	mORMotSQLite3.TSQLRestServerDB(025CF020)   GET root/Factura ORM-Get -> 404 with outlen=47 in 1864500 us
20170406 02553714  -    01.864.506

I see that it is effectively trying to do a query against some SQLite database, and it is not using my MySQL database nor mappings.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: MySQL / FireDAC / ORM test with existing database

Calling CreateMissingTables, as documented, may help.

leus

Member

Registered: 2012-09-05

Posts: 79

Re: MySQL / FireDAC / ORM test with existing database

Calling CreateMissingTables, as documented, may help.

Really? Where in the documentation? I thought this method would create missing tables in the database, which I really don't want to do. I just want to retrieve records from the existing table...

russocorp

Member

Registered: 2017-03-29

Posts: 2

Re: MySQL / FireDAC / ORM test with existing database

I dont know mormot yet, but maybe CreateMissingTables will help with maping names, because system is trying acess table "Factura" and not "Facturas". (sorry for bad english)

leus

Member

Registered: 2012-09-05

Posts: 79

Re: MySQL / FireDAC / ORM test with existing database

Well, I'll be. Calling CreateMissingTables did the trick. I still cannot find the documentation part where this is stated, and I don't understand what's going on. Can somebody explain it to me? I did some debugging but couldn't wrap my head around it.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: MySQL / FireDAC / ORM test with existing database

It is just written everywhere in the documentation and the samples...
Search CreateMissingTables in https://synopse.info/files/html/Synopse … 01.18.html

leus

Member

Registered: 2012-09-05

Posts: 79

Re: MySQL / FireDAC / ORM test with existing database

It is just written everywhere in the documentation and the samples...
Search CreateMissingTables in https://synopse.info/files/html/Synopse … 01.18.html

Well, I did:

Create all missing tables, via the CreateMissingTables method - and not compute by hand a "CREATE TABLE IF NOT EXISTS..." SQL statement;

This is precisely what I don't want to do.

As you can see, there is no difference with using the local SQLite3 engine or a remote database engine.
From the Client point of view, you just call the usual RESTful CRUD methods, i.e. Add() Retrieve() Update() UnLock() Delete() - or their faster Batch*() revision - and you can even handle advanced methods like a FillPrepare with a complex WHERE clause, or CreateSQLMultiIndex / CreateMissingTables on the server side.
Even the creation of the table in the remote database (the 'CREATE TABLE...' SQL statement) is performed by the framework when the CreateMissingTables method is called, with the appropriate column properties according to the database expectations (e.g. a TEXT for SQLite3 will be a NVARCHAR2 field for Oracle).

Again, this sounds dangerously close to modify the existing database.

Using this class will include the CreateMissingTables call to create both AuthGroup and AuthUser tables needed for authentication. But the resulting executable will be lighter: only 200 KB when compiled with Delphi 7 and our LVCL classes, for a full service provider.

There we go again, creating tables (perhaps I'm reading it wrong, and it means tables on the client side?)

Also, in the API reference:

procedure CreateMissingTables(user_version: cardinal=0; Options: TSQLInitializeTableOptions=[]); override;
Missing tables are created if they don't exist yet for every TSQLRecord class of the Database Model
- you must call explicitely this before having called StaticDataCreate()
- all table description (even Unique feature) is retrieved from the Model
- this method also create additional fields, if the TSQLRecord definition has been modified; only field adding is available, field renaming or field deleting are not allowed in the FrameWork (in such cases, you must create a new TSQLRecord type)

Still doesn't dispel my fear of it trying to modify the underlying database.

That's everything I could find in the document you linked regarding this function, and in the API reference. In fact, I had already read it. Perhaps I just don't understand it?

Arnaud, I don't mean to bash your documentation, but it is huge. Personally, I don't learn that way - I cannot consume a whole book and suddenly become an expert on the subject matter. I'm a slow learner, and I need to bit small pieces to understand what I'm doing. The function name itself (CreateMissingTables) informs me that it wants to modify my database. The documentation doesn't state otherwise. The code, while well written, is extremely complicated and difficult to understand (as it should be, it is doing some amazing magic.) This is a perfect storm of documentation to be drown in.

And some positive criticism: RTFM is not always the best response, especially when the person asking the question shows that it is trying hard.

Anyways, I'll keep trying. Thanks for your awesome work!