mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Create FireDAC/SQLModel/RestClient for a single MySQL table

Hi there!

I have just discovered the mORMot framework and I am pretty impressed of it! A good work! *thumbs up*, buuut.... I have a question!

After reading the documantation which is really great I unfortunately wasn't able to map a single MySQL table. I used this code:

type
  TMeasurement = class(TSQLRecord)
  private
    FName: RawUTF8;
  published
    property Name: RawUTF8 read FName write FName;
  end;


procedure SetupDataBase;
var
  dbConnProps: TSQLDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  MJ: TMeasurement;
begin
  // 1. Populate the FireDAC MySQL ConnectionProperties
  dbConnProps := TSQLDBFireDACConnectionProperties.Create('MySQL?localhost`;Port=3306', 'dbname', 'user', 'pass');

  // 2. Connecting 
  dbConnProps.ThreadSafeConnection.Connect;

  // 3. Create the Model
  Model := TSQLModel.Create([TMeasurement], 'root');

  // 4. Register an external table 
  VirtualTableExternalRegister(Model, TMeasurement, dbConnProps, 'measurement');

  // 5. Create a REST Client
  Client := TSQLRestClientDB.Create(Model, nil, string('SQliteDb.sqlite'), TSQLRestServerDB, false, '');

  // 6. Retrieve data
  MJ := TMeasurement.CreateAndFillPrepare(Client, 'Name LIKE ?',['new measure%']);

  Form1.ListBox1.Items.BeginUpdate;
  try
    while MJ.FillOne do
    begin
      Form1.ListBox1.Items.Add(MJ.Name);
    end;
  finally
    MJ.Free;
    Form1.ListBox1.Items.EndUpdate;
  end;

end;

My questions are:
5. What should I define instead of string('SQliteDb.sqlite') for the MySQL database!?
6. I have an error like "... no such table measurement"

Could you help me? Thx!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Create FireDAC/SQLModel/RestClient for a single MySQL table

5. The SQLite3 database is needed if some tables are not external.
If you use VirtualTableExternalRegisterAll(), then the Sqlite3 would never be used for actual storage, just for "virtual tables" mapping the external tables, so you can use SQLITE_MEMORY_DATABASE_NAME as SQlite3 database file name.

6. You need to create the tables.
Please start from existing sample, like "28 - Simple RESTful ORM Server".
TSQLRestServer.CreateMissingTables is the key method here.
It is not a good idea to use TSQLRestClientDB hosting a TSQLRestServerDB with external tables.
Please create a TSQLRestServerDB instance, with its own model, and its external tables settings.
Then create a TSQLRestClient, e.g. a TSQLRestClientDB pointing to the TSQLRestServerDB directly, if needed.

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: Create FireDAC/SQLModel/RestClient for a single MySQL table

Thx for your help! Now it works! I have modified a bit the example "28 - Simple RESTful ORM Server".

Is it a right way to map my existing MySQL database in case I don't want to use RESTful?

Could you take a look at it if I am doing it right?

  TMeasurement = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    /// ORM will create a NAME VARCHAR(80) column
    property Name: RawUTF8 index 80 read fName write fName;
  end;

  TAnalyses = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    /// ORM will create a NAME VARCHAR(80) column
    property Name: RawUTF8 index 80 read fName write fName;
  end;

  function DataModel: TSQLModel;
  begin
    Result := TSQLModel.Create([TMeasurement, TAnalyses], SERVER_ROOT);
    TMeasurement.AddFilterOrValidate('Name',TSynValidateText.Create); // ensure exists
    TAnalyses.AddFilterOrValidate('Name',TSynValidateText.Create); // ensure exists
  end;

procedure CreateMeasurementAndAnalyses;
begin
  aProps := TSQLDBFireDACConnectionProperties.Create('MySQL?localhost;Port=3306', 'dbname', 'user', 'pass');
  try
    // get the shared data model
    aModel := DataModel;

    // use PostgreSQL database for all tables
    VirtualTableExternalRegisterAll(aModel,aProps);
    try
      aModel.Props[TMeasurement].ExternalDB.MapField('ID','IdMeasurement');
      aModel.Props[TAnalyses].ExternalDB.MapField('ID','IdAnalyses');

      // create the main mORMot server
      aRestServer := TSQLRestServerDB.Create(aModel,':memory:',false);
      try
        // create tables or fields if missing
        aRestServer.CreateMissingTables;

        // create a mORMot client
        aClient := TSQLRestClientDB.Create(aRestServer);
        try
          aMeasurement := TMeasurement.Create;
          try
            Randomize;
            aMeasurement.Name := 'Name'+Int32ToUtf8(Random(10000));
            aID := aClient.Add(aMeasurement,true);
          finally
            aMeasurement.Free;
          end;

          aAnalyses := TAnalyses.Create;
          try
            Randomize;
            aAnalyses.Name := 'Name'+Int32ToUtf8(Random(10000));
            aID := aClient.Add(aAnalyses,true);
          finally
            aAnalyses.Free;
          end;

        finally
          aClient.Free;
        end;
      finally
        aRestServer.Free;
      end;
    finally
      aModel.Free;
    end;
  finally
    aProps.Free;
  end;
end.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Create FireDAC/SQLModel/RestClient for a single MySQL table

Sounds just fine to me.

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: Create FireDAC/SQLModel/RestClient for a single MySQL table

Sounds just fine to me.

Thx for your help!