Forums (Obsolete)

Bug reports Found a bug? Post it here please.

Version 5.x > Bug reports > Computed column added in Insert and Update queries

View modes:

Member

Member

Marko - 1/18/2011 5:02:11 AM

Computed column added in Insert and Update queries

Hi.
I have problem with managing custom data types. I added some computed columns to table definition via SQL Management Studio. After editing data type via CMSDesk, it (Kentico) regenerates update and insert queries and includes my computed columns in the queries. Of course it's wrong. I don't want to use 'Avoid updates' checkbox, because I want this updates to work.
So is this a bug?

Kentico Developer

Kentico Developer

kentico_ivanat - 1/18/2011 8:18:15 AM

RE:Computed column added in Insert and Update queries

Hi,

unless you have the source code it is not possible to change this behavior. Even if you had the source code, it would be really difficult to override appropriate method because this behavior is managed deeply in the system.

You could create a custom form control and add it to your custom document type to store your calculations. In definition of document type the field of your custom form control type has to have checked option Display attribute in the editing form in case it should work with data set in the other fields. To hide it in the Form tab you can use css. It means you can set Caption style: and Input style: to display:none; in definition of your custom field in document type - Fields tab.

Now to update fields you could you custom tree node handler and manage update of your custom field in OnAfterInsert method.

Alternativelly, you could get the value of another field directly in the form control. For inspiration you could take a look at ~/CMSModules/Content/FormControls/Tags/TagSelector.ascx.cs - public int GroupId property, which is set based on value of another field.

Best regards,
Ivana Tomanickova

Member

Member

Marko - 1/18/2011 8:44:41 AM

RE:Computed column added in Insert and Update queries

I see that you didn't understand me in any way.
Let me show you the problem on some examples.

1) I have created new CUSTOM DOCUMENT TYPE in Kentico named MyType
2) I have added one attribute named MyText, and one with primary key.

Kentico generated queries: delete, insert, update and other.

Is this still clear ?

Next...

3) I added computed column to MyType table in Kentico Datatbase named MyConvertedText

4) I changed in Kentico (Site Manager -> Development -> Custom document Types) some properties od MyText attribute in MyType.

5) Kentico REGENERATED sql queries. But NEW QUERIES ARE WRONG. They contains MyConvertedText column.

It looks like

UPDATE MyType SET [MyText] = @MyText, [MyConvertedText] = @MyConvertedText WHERE [PK] = @PK

The same in INSERT query.

That queries are wrong.

My question is: Why it happends?

Kentico Developer

Kentico Developer

kentico_ivanat - 1/18/2011 9:14:37 AM

RE:Computed column added in Insert and Update queries

Hi,

If you add some field or if you are updating a field, the queries, which are used for adding or editing data must be changed for appropriate document type. These queries are used by basic system methods working directly with the database.

In case the field was changed and the query was not, the methods would throw an SQL exception.

Best regards,
Ivana Tomanickova

Member

Member

Marko - 1/18/2011 9:19:39 AM

RE:Computed column added in Insert and Update queries

I know that very good. But if you ask someone who knows SQL he will tell you that COMPUTED columns could NOT be included in update and insert queries. In my opinion Kentico generates wrong queries. Did you try to reproduce my scenario and did you try to create or update document with query generated by Kentico?

Kentico Developer

Kentico Developer

kentico_ivanat - 1/19/2011 9:06:43 AM

RE:Computed column added in Insert and Update queries

Hi,

Unfortunately, the DataEngine provider is not able to detect if the column in COMPUTED or not. Therefore this type of columns in not supported in custom document types.

In other words:

3) I added computed column to MyType table in Kentico Datatbase named MyConvertedText

is not a standard/supported way of creating fields in your custom document type. The system in not designed to detect column, which were not added standard way.

To achieve similar functionality you will need to use add column to your document type using procedure described in the documentation. And to set the value of these fields you can use methods described in my first answer.

Best regards,
Ivana Tomanickova

Member

Member

Marko - 1/19/2011 9:43:24 AM

RE:Computed column added in Insert and Update queries

So I understand:
- customizations of DB are not supported
- it's not a bug (in Your opinion)

You wrote that "system is not designed to detec column which were not added standard way". I wish it would be true. But unfortunately it does. I don't need this COMPUTED column in Kentico at all. I need it for complete custom code.

Ok. I can move it to another table, write trigger, customize my queries, etc and decrease (already weak) system performance. It's not very optimistic.

Thanks for your time.

Kentico Developer

Kentico Developer

kentico_ivanat - 1/19/2011 11:30:54 AM

RE:Computed column added in Insert and Update queries

Hi Marko,

I meant that the system is not designed to check if the column added directly in the database is COMPUTED or not. By default there is not an option to add COMPUTED field using UI or API in a document type.

I will create a requirement to add this functionality into one of next version.

If you are facing performance issues please take a look at following articles describing how to design, develop and debug the website to achieve the best performance:

sql queries debugging
threads debugging
debugging options
performance of kentico
performance optimization
caching options
deep dive caching
performance optimization
speed up images
cache setting efficiency
testing page load with firebug

Best regards,
Ivana Tomanickova

Top