xdata aurelius fieldnames with - display sql errors

[Freisinger_Arno]

i want make a xdata restserver with al table with fieldnames like

E-Mail

With a firedac mysql syntax

{
"error": {
"code": "MySQLNativeException",
"message": "[FireDAC][Phys][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-Mail, A.Internet AS A_Internet, A.Erster_Kontakt AS A_Erster_Kontakt, A.Letztes' at line 1"
}
}

[Column('E-Mail', [], 60)]
FEMail: Nullable<string>;

property EMail: Nullable<string> read FEMail write FEMail;

How i can solve this problem?

[wlandgraf (Wagner Landgraf)]

E-Mail is not a valid field name, as it contains a hyphen (which is a minus sign). Quickest way to solve this is simply wrap the field name with quotes or the back tick (MySQL specific):

[Column('"E-Mail"', [], 60)]

or

[Column('`E-Mail`', [], 60)]

Alternatively if you want ao more general approach you can use the OnGetIdName event:

Support for quoted table/fieldname

You can use OnGetIdName event to tell Aurelius which identifiers should be double quoted: (TSQLGeneratorRegister.GetInstance.GetGenerator('MSSQL') as TAnsiSQLGenerator) .OnGetIdName := function(AName: string): string begin Result := AName; if SameText(AName, 'User') then Result := '"' + AName + '"'; end;

[Freisinger_Arno]

it don't works

[Column('"E-Mail"', [], 60)]

SQL Error:

.....right syntax to use near '"E-Mail" AS A_"E-Mail", A

[Column('E-Mail', [], 60)]

SQL Error:

..... right syntax to use near '-Mail, A.Internet

[wlandgraf (Wagner Landgraf)]

Ah right, sorry about that. Then the approach will be using the OnGetIdName event:

  (TSQLGeneratorRegister.GetInstance.GetGenerator('MySQL') as TAnsiSQLGenerator)
    .OnGetIdName :=
      function(AName: string): string
      begin
        Result := AName;
        if Pos('-', Result) then
          Result := '"' + Result + '"';
      end;

[Freisinger_Arno]

unfortunately I don't know where to embed it. I can't find any instructions for it either. Is there an example of this?

[wlandgraf (Wagner Landgraf)]

Just put it at the beginning of your application, or any place that is executed before you do anything with Aurelius.

[Freisinger_Arno]

ok i have it now, my problem is not solved:

{
"error": {
"code": "MySQLNativeException",
"message": "[FireDAC][Phys][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"E-Mail" AS "A_E-Mail", A.Internet AS A_Internet, A.Erster_Kontakt AS A_Erster_K' at line 1"
}

uses ....

  Aurelius.Sql.Register,
  Aurelius.Sql.AnsiSqlGenerator,


procedure TServerContainer.DataModuleCreate(Sender: TObject);
begin
 (TSQLGeneratorRegister.GetInstance.GetGenerator('MySQL') as TAnsiSQLGenerator)
    .OnGetIdName :=
      function(AName: string): string
      begin
        Result := AName;
        if Pos('-', Result)>0 then
          Result := '"' + Result + '"';
      end;
end;```
}

[Freisinger_Arno]

now it works

Result := '`' + Result + '`';