Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

Postby Dennis Wanke » Thu 24 Apr 2014 09:35

Hard to believe, but this ordinary SSDL type definition

Code: Select all
<EntityType Name="CatalogItem">
   <Key>
      <PropertyRef Name="Id" />
   </Key>
   <Property Name="Id" Type="int" Nullable="false" />
   <Property Name="SKU" Type="NVARCHAR2" MaxLength="100" />
   <Property Name="URL" Type="NVARCHAR2" MaxLength="100" />
</EntityType>


causes the following statement to be generated as a part of the database script (note SKU and URL columns are not escaped):

Code: Select all
CREATE TABLE "CatalogItem" (
  "Id" NUMBER(10) NOT NULL,
  SKU NVARCHAR2(100) NULL,
  URL NVARCHAR2(1000) NULL,
  PRIMARY KEY ("Id")
)


I think the two columns are not escaped because the corresponding SSDL properties are in uppercase. I'm not quite sure if this can lead to problems, but wouldn't it be safer to always escape all SQL identifiers? Just to be always on the safe side, avoiding any possible collisions with reserved words and the like.
Last edited by Dennis Wanke on Mon 28 Apr 2014 08:44, edited 2 times in total.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

Postby Shalex » Mon 28 Apr 2014 08:14

If there are no quotes around the name of your table/column, the resulting table/column name will be converted to upper case in Oracle. That's why we do not quote names in upper case. Why do you need quotation in this case?
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

Postby Dennis Wanke » Mon 28 Apr 2014 08:39

I think you misunderstood the problem. We have an SSDL schema (as a part of Entity Model) with some entity type defined. The type includes some properties with names in camel case and some in upper case. Once ObjectContext.CreateDatabaseScript() get called given a model with that SSDL schema, the provider (dotConnect) iterates over type definitions, encounters the mentioned one and generates a CREATE TABLE statement from it. While doing this, the provider deliberately escapes some column identifiers but not the others. My suggestion would be to always escape all SQL identifiers - this would make the generated script more reliable and simplify the provider logic the same time (as there is probable some extra logic to avoid collisions with reserved words and so on). I edited my original post to stress this point.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

Postby Shalex » Tue 29 Apr 2014 14:09

Dennis Wanke wrote:I think the two columns are not escaped because the corresponding SSDL properties are in uppercase.

This is correct.

Dennis Wanke wrote:I'm not quite sure if this can lead to problems

Our current implementation quotes SQL identifiers only if this is required. Have you encountered any particular problems with quotation?
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

Postby Dennis Wanke » Tue 29 Apr 2014 16:42

No problems so far.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49


Return to dotConnect for Oracle