you’re not gonna believe this (URGENT!!!)

you’re not gonna believe this (URGENT!!!)

Postby hepek » Wed 26 Feb 2014 16:23

I have table "site" and bunch of columns in there.
One column in table is called FmsPilotDate.

We noticed that this column gets updates very frequently with some date,
even though my application does not EVER update this column. We don’t even use this column at all. Also important we NEVER UPDATE "site" table at all from my application. Not in a C# code, or SP procedure, or trigger.

In order to figure out how is this column get updated, we created a trigger and keep a update history in site_history table. So, every time this column gets updated we create a record in history table so we can track down updates on this column. We know who and when updated the column, old value and new value.

I did some investigation and here is what I discovered:
The column gets updated randomly, when I navigate to certain pages back and forth.
I could not find a pattern there. I know it happens from my app because I am watching this history table while navigating my app. It is a web application.

When I remove this column "FmsPilotDate" from my datacontext, "auto" update does not happen. That tells me that devart somehow updates this column.
I searched all of our .net code, stored procedures, oracle jobs for this column - no matches found. That's because we do not use it at all.

You must admit this is very odd.
In this particular case I can drop the column from devart diagram and problem is solved.
But I feel VERY VERY uncomfortable with this situation.

I would like to know why is this happening, and if you ever had a similar issue reported. What do I do about this? How do I know this is not happening with some other columns in my database?
Last edited by hepek on Wed 26 Feb 2014 16:32, edited 6 times in total.
hepek
 
Posts: 115
Joined: Thu 07 Jul 2011 13:59

Re: you’re not gonna believe this

Postby hepek » Wed 26 Feb 2014 16:24

I have dotConnect for oracle version 7.7

here is my table definition:

<Table Name="LINKMAIN.SITE" Member="Sites">
<Type Name="Site" ed:Guid="dbba7c10-a1d7-4894-853b-6ce1b9e4044a">
<Column Name="SITE_ID" Member="SiteId" Type="System.Int64" DbType="NUMBER(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" Precision="10" ed:ValidateRequired="True" ed:Guid="85aad026-8486-4c5b-9ce9-856fa0b3e1f9" />
<Column Name="NAME_SHORT" Member="NameShort" Type="System.String" DbType="CHAR(3) NOT NULL" CanBeNull="false" UpdateCheck="Never" FixedLength="true" MaxLength="3" ed:ValidateRequired="True" ed:Guid="d7f2ebe9-71d9-4751-a878-419c71c93523" />
<Column Name="NAME_LONG" Member="NameLong" Type="System.String" DbType="VARCHAR2(40) NOT NULL" CanBeNull="false" UpdateCheck="Never" MaxLength="40" ed:ValidateRequired="True" ed:Guid="41499373-0c70-4728-9a7c-89a00426a753" />
<Column Name="TIME_ZONE_CODE" Member="TimeZoneCode" Type="System.String" DbType="CHAR(3) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="3" ed:ValidateRequired="True" ed:Guid="7a2d733f-6455-4e10-ad44-b6bff7d0f49a" />
<Column Name="PHONE_NUMBER" Member="PhoneNumber" Type="System.String" DbType="VARCHAR2(20) NULL" CanBeNull="true" UpdateCheck="Never" MaxLength="20" ed:ValidateRequired="True" ed:Guid="0fff5270-8d94-42c2-ba80-a9a8eb02f7d3" />
<Column Name="FAX_NUMBER" Member="FaxNumber" Type="System.String" DbType="VARCHAR2(20) NULL" CanBeNull="true" UpdateCheck="Never" MaxLength="20" ed:ValidateRequired="True" ed:Guid="b5fc42bf-75f3-4505-97a6-f9b63df2d3ef" />
<Column Name="ADDRESS_1" Member="Address1" Type="System.String" DbType="VARCHAR2(40) NULL" CanBeNull="true" UpdateCheck="Never" MaxLength="40" ed:ValidateRequired="True" ed:Guid="dcff32cd-9c2b-4c6c-99c4-d69a7f10e5ed" />
<Column Name="ADDRESS_2" Member="Address2" Type="System.String" DbType="VARCHAR2(40) NULL" CanBeNull="true" UpdateCheck="Never" MaxLength="40" ed:ValidateRequired="True" ed:Guid="4ae9b964-1b4e-4e3e-aacb-50a51facda50" />
<Column Name="NAME_CITY" Member="NameCity" Type="System.String" DbType="VARCHAR2(20) NULL" CanBeNull="true" UpdateCheck="Never" MaxLength="20" ed:ValidateRequired="True" ed:Guid="b87dac0e-4d98-44a3-92f2-e1162c20e3bf" />
<Column Name="STATE_CODE" Member="StateCode" Type="System.String" DbType="CHAR(3) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="3" ed:ValidateRequired="True" ed:Guid="2f8b75ea-29c1-4c75-ba2b-fad269131663" />
<Column Name="COUNTRY_CODE" Member="CountryCode" Type="System.String" DbType="CHAR(3) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="3" ed:ValidateRequired="True" ed:Guid="db8e5def-f428-4c86-9493-5c83e7c6c265" />
<Column Name="ZIP_CODE" Member="ZipCode" Type="System.String" DbType="VARCHAR2(10) NULL" CanBeNull="true" UpdateCheck="Never" MaxLength="10" ed:ValidateRequired="True" ed:Guid="a3ed38b6-0c2b-444f-8e6b-08025dd59446" />
<Column Name="FLAG_DELETE" Member="FlagDelete" Type="System.String" DbType="CHAR(1) NULL" CanBeNull="true" UpdateCheck="Never" DefaultValue="'N'" FixedLength="true" MaxLength="1" ed:ValidateRequired="True" ed:Guid="85bb7cdb-1fc4-447c-a866-34a8c7c42484" />
<Column Name="EMPLOYEE_ID_CONTACT" Member="EmployeeIdContact" Type="System.Int64" DbType="NUMBER(10) NULL" CanBeNull="true" UpdateCheck="Never" Precision="10" ed:ValidateRequired="True" ed:Guid="b71af519-9a58-40bb-97ba-dcf321fd8010" />
<Column Name="TEXT_MAIL_STOP" Member="TextMailStop" Type="System.String" DbType="CHAR(5) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="5" ed:ValidateRequired="True" ed:Guid="63335177-9c75-4fc4-bbb2-7c4ee674d052" />
<Column Name="HOURLY_RATE" Member="HourlyRate" Type="System.Double" DbType="NUMBER(5,2) NULL" CanBeNull="true" UpdateCheck="Never" DefaultValue="0" Precision="5" Scale="2" ed:ValidateRequired="True" ed:Guid="21f38305-7944-4b80-912e-f5ee374c60c8" />
<Column Name="FLAG_SERVICE_CENTER" Member="FlagServiceCenter" Type="System.String" DbType="CHAR(1) NULL" CanBeNull="true" UpdateCheck="Never" DefaultValue="'N'" FixedLength="true" MaxLength="1" ed:ValidateRequired="True" ed:Guid="dd32675b-93ac-435b-9081-ddbf84f3c984" />
<Column Name="DIVISION_CODE" Member="DivisionCode" Type="System.String" DbType="CHAR(3) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="3" ed:ValidateRequired="True" ed:Guid="40f4582e-3f78-49b8-9104-6bed3b6ceeaa" />
<Column Name="SERVICE_CENTER_NO" Member="ServiceCenterNo" Type="System.String" DbType="CHAR(2) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="2" ed:ValidateRequired="True" ed:Guid="3c36281f-7166-4339-bf9b-52c565a84e26" />
<Column Name="FSSA_PREFIX" Member="FssaPrefix" Type="System.String" DbType="CHAR(3) NULL" CanBeNull="true" UpdateCheck="Never" FixedLength="true" MaxLength="3" ed:ValidateRequired="True" ed:Guid="4ae41df6-2ff6-48fc-a8dd-9b639fce74ef" />
<Column Name="DATE_PILOT_STARTED" Member="DatePilotStarted" Type="System.DateTime" DbType="DATE NULL" CanBeNull="true" UpdateCheck="Never" ed:ValidateRequired="True" ed:Guid="95a73567-b3b5-46f8-bf36-88fa9a5f34e8" />
<Column Name="FMS_PILOT_DATE" Member="FmsPilotDate" Type="System.DateTime" DbType="DATE NULL" CanBeNull="true" UpdateCheck="Never" ed:ValidateRequired="True" ed:Guid="aac595d5-6676-49a3-80ff-a6cc3203a313" />
<Column Name="EMPLOYEE_ID_INVOICE_CONTACT" Member="EmployeeIdInvoiceContact" Type="System.Int64" DbType="NUMBER(10) NULL" CanBeNull="true" UpdateCheck="Never" Precision="10" ed:ValidateRequired="True" ed:Guid="5e8c6960-51fc-48ef-adf2-4d05efdc9e46" />
<Column Name="ONE_PLACE_PILOT_IND" Member="OnePlacePilotInd" Type="System.Boolean" DbType="NUMBER(1) NOT NULL" CanBeNull="false" UpdateCheck="Never" DefaultValue="0" ed:ValidateRequired="True" ed:Guid="61613171-efe3-44ed-8519-1962d6967ea9" />
<Association Name="Site_Deal" ed:AssociationGuid="af21375a-ded1-485b-9c56-87141220ef31" Member="Deals" ed:Guid="5f946292-8e4e-48ed-b560-1486b269948b" ThisKey="SiteId" OtherKey="SiteId" Type="Deal" />
<Association Name="Site_DailyCompPriority" ed:AssociationGuid="f2eedf15-a2bc-4bf1-9cb8-6f3ce3196b03" Member="DailyCompPriorities" ed:Guid="f6293213-58ed-400a-a18f-be79b6be7623" ThisKey="SiteId" OtherKey="SiteId" Type="DailyCompPriority" />
<Association Name="Site_PbProofReq" ed:AssociationGuid="5a5dbdb2-2188-4dec-9744-82e53a6199b9" Member="PbProofReqs" ed:Guid="d162048a-af18-4c94-9328-bdbedf4d7dc6" ThisKey="SiteId" OtherKey="SiteIdProofing" Type="PbProofReq" />
<Association Name="Site_PbPostFold" ed:AssociationGuid="2a7bd2ef-277c-498f-a416-7587b117df1d" Member="PbPostFolds" ed:Guid="b5ee22c8-da57-44e3-bccf-332d274571a3" ThisKey="SiteId" OtherKey="SiteId" Type="PbPostFold" />
<Association Name="Site_PbFinishing" ed:AssociationGuid="0325401a-9de7-4b7c-8b5f-2fac6725b63d" Member="PbFinishings" ed:Guid="8c863112-4289-44dc-af6f-d5a40f9ce0d3" ThisKey="SiteId" OtherKey="SiteId" Type="PbFinishing" />
</Type>
</Table>
hepek
 
Posts: 115
Joined: Thu 07 Jul 2011 13:59

Re: you’re not gonna believe this (URGENT!!!)

Postby hepek » Wed 26 Feb 2014 21:40

never mind, I figured it out.

I have a Method ToLocalTime() as extension to my entities.
This method is using reflection to scan entity and child entities and convert any DateTime column to a local user time, based on user time offset.

what happened here is Site table was retrieved as child entity of some other table by using LoadWith() method.

After that, I call ToLocalTime().
Later on in a code I save another record that has "site" table as a child.
Devart self-tracking figure - ok, the site table changed as well, so let me apply the change. That explains this mystery update.

When I searched my c# code, I did not find any match for column "FmsPilotDate", because the column is being changed dynamically by reflection.
hepek
 
Posts: 115
Joined: Thu 07 Jul 2011 13:59

Re: you’re not gonna believe this (URGENT!!!)

Postby MariiaI » Thu 27 Feb 2014 10:50

Thank you for the feedback. Glad to see that the issue was resolved.
If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle