MARSHMALLOW errors on some databases (firebird)

[Аров Марат]

I try to start work with Firebird database using Marshmallow ORM. It works finy with Mysql database connected via ADO ODBC connection. But Firebird connection via FireDAC gives me an error when exec query. I find function

function TAnsiSQLGenerator.GetEscapeChar: Char;

begin

//  Result := '"';

  Result := '`';

end;

that works fine for mysql and gives error with firebird. I change comments and firebird SELECT clause generates fine but WHERE clause also generates with quotes and Firebird gives me error there. When I change comments my Mysql project crashes with error on exec query. Than I turn back comments in this function and Mysql project works again.

How can I use Marshmallow with Firebird?

[Stefan Glienke]

You obviously already modified that code as the commented out code is the correct one.

I guess you already got that mistake when you started using MySQL via ADO as MySQL needs the backtick.

Set QueryLanguage of your IDBConnection properly (the valid values are in Spring.Persistence.SQL.Interfaces). Then the correct SQLGenerator will be used.

If you are using an adapter that is specific to a certain database then it sets the QueryLanguage properly but if you are using an adapter for components that can connect to multiple databases (like ADO or FireDAC) it does not, hence the need you specify it.

[Аров Марат]

Thanks for your advice! I uncommented the code that you said. In Mysql project I set QueryLanguage to qlMysql and it works fine! But in Firebird project I set QueryLanguage to qlFirebird and it still generate non valid sql command: in WHERE clause generates quotes around fieldnames and firebird gives me an error. Can you help me little bit more?

среда, 6 декабря 2017 г., 16:52:38 UTC+3 пользователь Stefan Glienke написал:

[Stefan Glienke]

But in Firebird project I set QueryLanguage to qlFirebird and it still generate non valid sql command: in WHERE clause generates quotes around fieldnames and firebird gives me an error. Can you help me little bit more?

No, you are causing an EInsufficientInformation exception with message "an error" on my side ;)

I know that Firebird supports quoted and unquoted identifiers and somehow causes errors depending on case sensitivity - make sure that is not the case.

[Аров Марат]

I've localize the problem. When using TSession.FindOne Framework works perfectly. But when using criteria API Marshmallow Framework generates WHERE clause with quoted fieldnames, but firebird in this case is case sensitive and error returning while executing query is "column unknown". Is there any workaround for uppercase WHERE clause fields or not generates quotes there?

среда, 6 декабря 2017 г., 18:35:37 UTC+3 пользователь Stefan Glienke написал:

[Аров Марат]

I've modified function TSimpleExpression.ToSqlString in unit Spring.Persistence.Criteria.Criterion.SimpleExpression.

//  whereField := TSQLWhereField.Create(fPropertyName, GetCriterionTable(command));
  whereField := TSQLWhereField.Create(AnsiUpperCase(fPropertyName), GetCriterionTable(command));


Now Firebird works with criteria API for me. Thanks for your attention.

среда, 6 декабря 2017 г., 18:35:37 UTC+3 пользователь Stefan Glienke написал:

[Stefan Glienke]

I've modified function TSimpleExpression.ToSqlString in unit Spring.Persistence.Criteria.Criterion.SimpleExpression.

//  whereField := TSQLWhereField.Create(fPropertyName, GetCriterionTable(command));
  whereField := TSQLWhereField.Create(AnsiUpperCase(fPropertyName), GetCriterionTable(command));

Now Firebird works with criteria API for me. Thanks for your attention.

While it might fix your immediate problem it is not the solution - and you could fix it easier by passing the uppercase "property name" to the criteria API without the need to alter the code.

However the criteria API currently has the design issue that the propertyName parameters you pass there are in fact the real database fieldNames. It does not do the fieldName lookup via the mapping as it should do.