reserved words as fieldname

BIZ TMS Aurelius
1

Hi,



One of my tables has a fieldname "begin", since begin is a reserved word in SQL I need to form my queries on this table like SELECT "begin" FROM testtable WHERE.....



I cannot use this table in Aurelius because apparently Aurelius does not include the fieldnames in doublequotes. I tried to change the mapping attributes to [Column(' "begin" ',[ ])] (I used spaces in this example for clarification but these aren't in my real code).



How can I map this table and its field "begin" to be used in Aurelius?

2

I fixed this by changing the Aurelius.SQL.AnsiSQLGenerator file:

I changed line 661 to:

    Result := Result + Field.Table.Alias + '."' + Field.Field + '"';

Now the SQL statement in NexusDB does include the doublequotes for all fieldnames.



Could you check if this is correct and if so could you include this in your code?

3

I was wondering why adding double quotes in the Column attribute didn't solve it? What exact problem did you have with this approach?

Your solution unfortunately cannot be applied to Aurelius because we can't add double quotes for all fields and all databases. More would be needed to make it compatible with all of them.
4

Hi Wagner,



When I use double quotes in the column attribute like so:



   [Column('"Begin"', [TColumnProp.Required])]

    FBegin_: TDateTime;





I get an error when I query the object:



NexusDB: <unnamed TnxQuery instance>: Query preparation failed:

Syntax error at line 1 pos 79: ',', ';', <eof>, EXCEPT, FROM, GROUP, HAVING, INTERSECT, INTO, ORDER, UNION or WHERE expected: "Begin"



5

To make it only for NexusDB I now changed Aurelius.Sql.NexusDB



I added a extra override for GetQualifiedColumnName



function TNexusDBSQLGenerator.GetQualifiedColumnName(

Field: TSQLSelectField): string;

begin

result := inherited GetQualifiedColumnName(Field);

if not Field.IsAggregated then

begin

    Result := Field.Table.Alias + '."' + Field.Field + '"' ;

end;

end;



Maybe you could include this in your code?



6

Actually, you could do that without changing Aurelius source code. You can just create a descendant of TNexusDBSQLGenerator, implement the method like you did, and then register that new class as an SQL Generator. You can then create your IDBConnection interface passing the name under which your generator was registered.