ManyToOne Association Build Field Names are too long for Oracle

[Todd Flora]

Oracle has a column name limit of 30 characters. This includes Column Alias names.

When defining a many to one association with the following attributes for the table:

• TableName: SUBSIDIARY
• MappedBy: SBS_SID
• ColumnName: ACTIVE_PRICE_LVL_SID

The following alias is generated by the TManyToOneRelation.BuildColumnName method:

SUBSIDIARY_SBS_SID_ACTIVE_PRICE_LVL_SID

Since this is more than 30 characters Oracle fails with the following:

Another scheme needs to be considered for Oracle when building association alias names.

BTW removing the TableName and MappedByColumnName from the mix and just returning the columnName causes the ManyToOne Relationship to start working, but now of course the columns in the association with the same name as the parent end up getting the parents values.

class function TManyToOneRelation.BuildColumnName(const tableName, mappedByColumnName,
  columnName: string): string;
begin
  Result := columnName;  //Format('%0:S_%1:S_%2:S', [tableName, mappedByColumnName, columnName]);
end;

[Todd Flora]

In looking at this further, it seems that just the TableAlias_ColumnName would be all that would be necessary to uniquely identify each field in a SQL Statement. This would then only add 3 additional characters to the column alias. Most Oracle shops are smart enough not to make column names more than 25 or so characters for this reason so it should be safe for most, and a training issue for those who want to use Marshmallow with Oracle and long column names.

It is easy enough to pass the TableAlias when creating the SQL Statement, but the alias does not seem to be available when mapping the result set to the object. If the alias could somehow be passed along to this routine, then this would accomplish a reasonable fix.

Just a suggestion:

Todd.

[Todd Flora]

What is happening on this one. I am still maintaining my own code to resolve this.

Thanks,

[Linas Naginionis]

@Toddflora Can you show us how you solved it? Or is it the same code as in your first post?

[Todd Flora]

No we have essentially not solved this. The code above will blow when two tables are being joined that have the same column name.

Here is our current code but of course is fraught with issues as well

  Result := tableName.Substring(0, 3) + columnName;

For Instance:

1. what if two tables start with the same first 3 characters and have the same column names
2. What if this still produces a string longer than 30

One solution for 1 would be to use the Table Alias as the Prefix as this has to be unique but the table Alias is autogenerated and it not available in this method currently

As to 2 it would seem there is no easy way to solve this but to make it a training issue/document issue that Marshmallow does not support Oracle column names greater than 30 minus whatever prefix length you decide on. I don't know what else to suggest here.

Thanks for looking into this one.

[Todd Flora]

This is still an issue for us. We just pulled the latest Marshmallow branch and integrated, we are still having to maintain a fix for this issue. Just wanted to check on the status of this issue?

[Linas Naginionis]

I looked at this issue again and I think I managed to shorten generated column alias name as much as possible. Can you please check 8ef2bcc commit?

I doubt there could be done more with this without a huge code rewrite.

[Todd Flora]

Yes it looks like what I suggested. I will try it out. Thanks,

Todd

--