How are SQL reserved words handled

dualarrow · 2018-07-23 15:00:46

I'm now building a large (automated) schema for my ctree server with about 170 tables. I'm having some problems with field names that are reserved words in SQL. Eg "order"

I have editoed the enumerations that specify the server types and have added my ctree definition and filled in all the entries that had compiler errors. In SynDB.pas theres the class function TSQLDBConnectionProperties.IsSQLKeyword and I have added a few of the reserved words into it (including order) and have made sure they are listed in alphabetical order. Assuming it locates a reserved word in there, what should it do with the reserved word ?

In ctree, you use double quotes to escape reserved words but I'm not seeing them get escaped.

Also in SynDB is TSQLDBConnectionProperties.SQLTableName which has a BeginQuoteChar and EndQuoteChar which for me is using a back tick character (`) to eascape. This appears to work for ctree (possibly as I'm using the ODBC driver and perhaps it's doing a translate of the back tick).

Is there anywhere else I should look to see how it's handling reserved word escaping ?

ab · 2018-07-23 17:49:12

For reserved words, the usual trick is to map the DB field name with the property name.
Isn't it enough?

dualarrow · 2018-07-24 00:02:30

Good idea. I just tried that but I must be doing something wrong. I have the following declared

  aModel.AddTable(TSQLBBRctCat);
  VirtualTableExternalMap(aModel, TSQLBBRctCat, aODBC, 'BBRctCat').MapField('id', '"key"').
                                                                   MapField('order1','"order"');

which correctly maps the table field named key which needed the double quotes as its a reserved word into the class property ID,
however, the 2nd MapField complains about the table field named order being a duplicate even though I renamed the class property to order1 and did the mapping. The TSQLRecord definition for this record is

  TSQLBBRctCat = Class(TSQLRecord)
  Private
    FDescription: String;
    FOrder1: BYTE;
    FIsAlive: Boolean;
    FWhoCreated: TSQLAuthUser;
    FWhoModified: TSQLAuthUser;
    FDateCreated: TDateTime;
    FDateModified: TDateTime;
  Published
    Property Description: String Read FDescription Write FDescription;
    Property Order1: BYTE Read FOrder1 Write FOrder1;
    Property IsAlive: Boolean Read FIsAlive Write FIsAlive;
    Property WhoCreated: TSQLAuthUser Read FWhoCreated Write FWhoCreated;
    Property WhoModified: TSQLAuthUser Read FWhoModified Write FWhoModified;
    Property DateCreated: TDateTime Read FDateCreated Write FDateCreated;
    Property DateModified: TDateTime Read FDateModified Write FDateModified;
  End;

Is this correct or have I made a mistake in my interpretation of VirtualTableExternalMap ?

ab · 2018-07-24 06:56:49

Why the quotes inside the quoted?

 MapField('id', 'key')

should be enough.

dualarrow · 2018-07-24 10:28:58

Without the double quotes, mORMot raises an exception in SynDBODBC EODBCException with a message of '[2a504] [FairCom][ODBC FairCom Driver 11.2.2.24260(Build-170219)][ctreeSQL] -20003 Syntax error (-20003)'

with the following SQL

'select Code,Name,ShortName,Alias,Phone1,Phone2,Extension1,Extension2,Address,FloorNo,ContactPerson,ContactDetail,DateExpiry,HospitalKey,AlwaysPrint,AllowBill,LinkHostEpisode,BlockMedicalRecords,ForceReportAsFinalPermanent,UrgentPhoneReportNotification,SuppressRedirectReports,DischargeWard,Category,DefaultWardPatientType,TransitionCode,DefaultUnit,CostCentreKey,WhoCreated,WhoModified,DateCreated,DateModified,key from Ward'

Note at the end it has

key from Ward

Thats where the syntax error comes from. If I surround key with double quotes (ctree's escape character) then it executes correctly.

With the double quotes it generates this SQL

'select Code,Name,ShortName,Alias,Phone1,Phone2,Extension1,Extension2,Address,FloorNo,ContactPerson,ContactDetail,DateExpiry,HospitalKey,AlwaysPrint,AllowBill,LinkHostEpisode,BlockMedicalRecords,ForceReportAsFinalPermanent,UrgentPhoneReportNotification,SuppressRedirectReports,DischargeWard,Category,DefaultWardPatientType,TransitionCode,DefaultUnit,CostCentreKey,WhoCreated,WhoModified,DateCreated,DateModified,"key" from Ward'

and all works ok.

dualarrow · 2018-07-24 11:01:09

I eventually got it to map using the following

  VirtualTableExternalMap(aModel, TSQLBBReact, aODBC, 'BBReact').MapFields(['id', '"key"']).SetOptions([rpmAutoMapKeywordFields]);

It didnt seem to matter how I tried to manually map it, it just kept failing claiming I had a duplicate order field. With the above mapping I renamed the property to order_ and it seems to work now.

ab · 2018-07-24 20:34:57

Isn't it because SQl reserved are refused in the mapping?

mORMot Open Source

#1 2018-07-23 15:00:46

How are SQL reserved words handled

#2 2018-07-23 17:49:12

Re: How are SQL reserved words handled

#3 2018-07-24 00:02:30

Re: How are SQL reserved words handled

#4 2018-07-24 06:56:49

Re: How are SQL reserved words handled

#5 2018-07-24 10:28:58

Re: How are SQL reserved words handled

#6 2018-07-24 11:01:09

Re: How are SQL reserved words handled

#7 2018-07-24 20:34:57

Re: How are SQL reserved words handled

Board footer