attributes getting updated sporadically

[CarlosPiccirillo]

Hi everyone,

I have a C# program that runs nightly on 17 layers, going through all records and updating 49 fields with data read from an Oracle table. The problem I am having is that sometimes the program runs successfully and sometimes it does not. On the times that it fails, the code runs to completion without any errors but none of the fields have updated values. Below is the program and I have removed most of the fields so that it could fit in this post without being huge.

The very first thing the script does is to check if the layer has locks on it and if it does, the layer is skipped. The next check it populates one field with dummy data and if succeeds, then it goes on to the rest of the code to update all fields.

Any idea what the problem might be? I am using ArcMap 9.3.1.

Thanks,
Carlos

private static void ProcessLayer(string permitType)
{
 IPropertySet pPropertySet = new PropertySetClass();
 IWorkspaceFactory pWorkspaceFactory = new SdeWorkspaceFactoryClass();
 IFeatureWorkspace pFeatureWorkspace = null;
 IFeatureClass pFeatureClass = null;
 IDataset pDataset = null;
 IWorkspace pWorkspace = null;
 IWorkspaceEdit pWorkspaceEdit = null;
 ITable pTable = null;
 ICursor pCursor = null;
 ITrackCancel pTrackCancel = new CancelTrackerClass();
 IProgressDialogFactory pProgressDialogFactory = new ProgressDialogFactoryClass();
 IProgressDialog2 pProgressDialog2 = null;
 IStepProgressor pStepProgressor = null;
 OracleConnection dataConnection = new OracleConnection();
 OracleCommand dataCommand = new OracleCommand();
 OracleDataReader dataReader = null;

 try
 {
  //Get the layer to update.
  pPropertySet.SetProperty("SERVER", "gresssde");
  pPropertySet.SetProperty("INSTANCE", "5195:rim");
  pPropertySet.SetProperty("USER", "rim");
  pPropertySet.SetProperty("PASSWORD", "S01stice");
  pPropertySet.SetProperty("VERSION", "RIM.DEFAULT");

  pFeatureWorkspace = pWorkspaceFactory.Open(pPropertySet, 0) as IFeatureWorkspace;
  pFeatureClass = pFeatureWorkspace.OpenFeatureClass(permitType);
  pDataset = pFeatureClass as IDataset;

  //If layer has locks on it, skip it.
  if (HasSchemaLocks(pDataset) == true)
  {
   string message = "layer " + pDataset.BrowseName + " had locks on it, layer skipped.";
   LogMessageToFile(LogsPath + "updatePermitDataAttrDaily_Log_", message);
   return;
  }

  //If layer cannot be attributed, skip it.
  if (CanUpdateAttributes(pFeatureClass) == false)
  {
   string message = "Could not update attributes on layer " + pDataset.BrowseName + ".";
   LogMessageToFile(LogsPath + "updatePermitDataAttrDaily_Log_", message);
   return;
  }

  pWorkspace = pDataset.Workspace;
  pWorkspaceEdit = pWorkspace as IWorkspaceEdit;

  //If permit type field not found, stop code.
  if (pFeatureClass.FindField("FEE_PERMIT_TYPE") != -1)
  {
   //Start an edit session and operation
   pWorkspaceEdit.StartEditing(false);
   pWorkspaceEdit.StartEditOperation();

   pTable = pFeatureClass as ITable;
   pCursor = pTable.Update(null, false);

   //Get the index numbers for every field being updated.
   int permitNoIdx = pCursor.FindField("PERMIT_NO");
   int appNoIdx = pCursor.FindField("APP_NO");
   int projectNameIdx = pCursor.FindField("PROJECT_NAME");
   int appStatusIdx = pCursor.FindField("APP_STATUS");
   int activeModIdx = pCursor.FindField("ACTIVE_MOD");

   string appNo = string.Empty;
   string permitNo = string.Empty;
   string projectName = string.Empty;
   string appStatus = string.Empty;
   string activeMod = string.Empty;

   //Set up and open the progress dialog.
   int featureCount = 1;
   int numberOfFeatures = pFeatureClass.FeatureCount(null);

   pProgressDialog2 = pProgressDialogFactory.Create(pTrackCancel, 0) as IProgressDialog2;
   pProgressDialog2.CancelEnabled = true;
   pProgressDialog2.Description = "Processing Record " + featureCount + " of " + numberOfFeatures;
   pProgressDialog2.Title = "Processing...";
   pProgressDialog2.Animation = esriProgressAnimationTypes.esriProgressGlobe;

   pStepProgressor = pProgressDialog2 as IStepProgressor;
   pStepProgressor.MinRange = 0;
   pStepProgressor.MaxRange = numberOfFeatures;
   pStepProgressor.StepValue = 1;
   pStepProgressor.Message = permitType;

   pProgressDialog2.ShowDialog();
   bool canContinue = true;

   //Read values from Oracle and update each feature with the info.
   dataConnection.ConnectionString = "Data Source=genp;" +
             "Persist Security Info=True;" +
             "User ID=pub;" +
             "Password=pub;" +
             "Unicode=True";

   dataConnection.Open();
   dataCommand.Connection = dataConnection;

   IRow pRow = pCursor.NextRow();

   while (pRow != null)
   {
    appNo = pRow.get_Value(appNoIdx).ToString();

    dataCommand.CommandText = "SELECT PERMIT_NO, PROJECT_NAME, APP_STATUS, ACTIVE_MOD, " +
            "FROM REG.REG_GIS WHERE app_no = '" + appNo + "'";

    dataReader = dataCommand.ExecuteReader();

    while (dataReader.Read())
    {
     if (!dataReader.IsDBNull(0))
     {
      permitNo = dataReader.GetString(0);
      permitNo = permitNo.Replace("'", "");
     }

     if (!dataReader.IsDBNull(1))
     {
      projectName = dataReader.GetString(1);
      projectName = projectName.Replace("'", "");
     }

     if (!dataReader.IsDBNull(2))
     {
      appStatus = dataReader.GetString(2);
      appStatus = appStatus.Replace("'", "");
     }

     if (!dataReader.IsDBNull(3))
     {
      activeMod = dataReader.GetString(3);
      activeMod = activeMod.Replace("'", "");
     }

     if (permitNoIdx != -1) pRow.set_Value(permitNoIdx, permitNo);
     if (projectNameIdx != -1) pRow.set_Value(projectNameIdx, projectName);
     if (appStatusIdx != -1) pRow.set_Value(appStatusIdx, appStatus);
     if (activeModIdx != -1) pRow.set_Value(activeModIdx, activeMod);
     pCursor.UpdateRow(pRow);
    }

    dataReader.Close();
    dataReader.Dispose();

    pRow = pCursor.NextRow();

    //Increment the progress dialog.
    canContinue = pTrackCancel.Continue();
    if (canContinue == true)
    {
     pStepProgressor.Step();
     featureCount++;
     pProgressDialog2.Description = "Processing Record " + featureCount + " of " + numberOfFeatures;
    }
    else
    {
     pProgressDialog2.HideDialog();
     return;
    }

    //Clear values.
    appNo = string.Empty;
    permitNo = string.Empty;
    projectName = string.Empty;
    appStatus = string.Empty;
    activeMod = string.Empty;
   }

   //Stop editing
   pWorkspaceEdit.StopEditOperation();
   pWorkspaceEdit.StopEditing(true);

   //Close progress dialog.
   pProgressDialog2.HideDialog();
  }   //if (pFeatureClass.FindField("FEE_PERMIT_TYPE") != -1)
 }
 catch (Exception ex)
 {
  MessageBox.Show(ex.StackTrace + "\r\n" + ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }
}

[JamesCrandall]

Carlos,

I may be mistaken here out of my unfamiliarity with C#, so please ignore if this is obvious.

if (!dataReader.IsDBNull(0))
{
 permitNo = dataReader.GetString(0);
 permitNo = permitNo.Replace("'", "");
}

So, am I incorrect to say that you are not dealing with the case when something actually IS null?  Again, I am a VB guy so I am looking for an else somewhere!  lol...  But I would think that if there are no errors but no data is actually updated, then maybe there was no data in the dataReader itself? 

When doing a similar implementation (ADO.NET DataTable-->FeatureClass/ITable write), I would test by writting something in the row/field IF something was null.  This would simply allow me to see that it was actually writting to it.

Again -- sorry if I am misreading your C# there!

[CarlosPiccirillo]

James,

Thanks for the reply. You are correct, I don't populate the fields if the query does not return a value from Oracle. However, 10 of the 49 fields cannot be null and always have values yet even these are not being updated.

I do the same thing you suggested when trying to write to a record, that is what the second check called CanUpdateAttributes in my code does. It picks one arbitrary record in the layer, populates one field with a bogus value and then immediately checks to see if the value is there. If it is, the function returns true, i.e. okay to process layer.

That's the biggest mystery. It works if I only update one field but chokes on 49. I might have to break it up into multiple scripts with each one updating fewer fields. The main reason I don't want to do this if I can avoid it is that the script takes four hours to run and it would be a huge time waster, especially considering that I have other scripts that need running at night also. My PC currently is busy running different automated scripts from Windows Task Scheduler from 6:30 p.m. to 10 a.m. the following morning so it's hard to find a slot to run this script when no one is accessing the data.

Do you know of a better (maybe faster) way to update attributes on a layer? If you have VB or even VBA code I can easily convert it to C#.

Thanks again for you help on this and in previous posts, I REALLY appreciate it!

Carlos

Carlos,

I may be mistaken here out of my unfamiliarity with C#, so please ignore if this is obvious.

if (!dataReader.IsDBNull(0))
{
 permitNo = dataReader.GetString(0);
 permitNo = permitNo.Replace("'", "");
}

So, am I incorrect to say that you are not dealing with the case when something actually IS null?  Again, I am a VB guy so I am looking for an else somewhere!  lol...  But I would think that if there are no errors but no data is actually updated, then maybe there was no data in the dataReader itself? 

When doing a similar implementation (ADO.NET DataTable-->FeatureClass/ITable write), I would test by writting something in the row/field IF something was null.  This would simply allow me to see that it was actually writting to it.

Again -- sorry if I am misreading your C# there!

[JamesCrandall]

James,

Do you know of a better (maybe faster) way to update attributes on a layer? If you have VB or even VBA code I can easily convert it to C#.

Thanks again for you help on this and in previous posts, I REALLY appreciate it!

Carlos

The alternative suggestion I am thinking is to skip the whole update with AO and do it directly with SQL on the database(s).  I think you may have mentioned that you don't have access to or cannot get the DBA's to work with you on this?  Not sure.  But just high-level thought here, I'd be inclined to approach this task at the database level as a scheduled job (if Oracle offers such a thing) and skip the whole invoke of AO into it.  Or as PL/SQL package/procedure that executes from whever you intend to invoke it from.

I wish I could see/state the obvious problem with the code you show here -- I'd probably end up with something very similar to what you have now.  Intermmitent issues are squirrely to resolve.  Will follow this thread to see suggestions from others and re-look at your code in hopes to see something else.

j

[JamesCrandall]

Edit: I just noticed -- you are not using a RowBuffer in your update.  Maybe try your update with such a thing instead?

Forgot to post the ADO.NET DataTable-->ITabl update code.  You could easily replace the ITable for your FeatureClasses. Hope you can gather some insight from it to help solve your issue...

Private Function ConvDT_To_ITab(ByVal inDataTable As DataTable, ByVal inITableName As String) As ITable

        Try

            '**Open the PGDB for temp layers
            Dim db_path As String = "C:\yourpathname\yourPGDBname.mdb"


            Dim pWS As IWorkspace
            Dim pFWS As IFeatureWorkspace
            Dim pWorkspaceFactory As IWorkspaceFactory
            pWorkspaceFactory = New ESRI.ArcGIS.DataSourcesGDB.AccessWorkspaceFactory
            pFWS = pWorkspaceFactory.OpenFromFile(db_path, 0)
            pWS = pFWS

            ' Set up a simple fields collection
            Dim pFields As ESRI.ArcGIS.Geodatabase.IFields
            Dim pFieldsEdit As ESRI.ArcGIS.Geodatabase.IFieldsEdit
            pFields = New ESRI.ArcGIS.Geodatabase.Fields
            pFieldsEdit = New ESRI.ArcGIS.Geodatabase.Fields
            pFieldsEdit = pFields

            Dim pField As ESRI.ArcGIS.Geodatabase.IField
            Dim pFieldEdit As ESRI.ArcGIS.Geodatabase.IFieldEdit
            pField = New ESRI.ArcGIS.Geodatabase.Field
            pFieldEdit = pField

            '' create the object id field
            pField = New ESRI.ArcGIS.Geodatabase.Field
            pFieldEdit = pField
            pFieldEdit.Name_2 = "OBJECTID"
            pFieldEdit.Type_2 = esriFieldType.esriFieldTypeOID
            pFieldsEdit.AddField(pField)

            For i = 0 To inDataTable.Columns.Count - 1
                pField = New ESRI.ArcGIS.Geodatabase.Field
                pFieldEdit = pField
                With pFieldEdit 'set all the properties according to the columns in the recordset
                    .Length_2 = inDataTable.Columns(i).MaxLength
                    .Name_2 = inDataTable.Columns(i).ColumnName
                    .Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeInteger
                End With

                Dim ColName As String = inDataTable.Columns(i).ColumnName.ToString
                Dim dtColType As String = inDataTable.Columns(i).DataType.ToString

                If inDataTable.Columns(i).DataType.FullName.ToString = "System.Int32" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeInteger
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.String" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeString
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.Double" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeDouble
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.Decimal" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeDouble
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.DateTime" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeDate
                End If

                pFieldsEdit.AddField(pField)
            Next i


            '**Check for existing FeatClass and delete if present
            Dim pExistingDs As ESRI.ArcGIS.Geodatabase.IDataset
            Dim pEnumDataset As IEnumDataset
            pEnumDataset = pWS.Datasets(esriDatasetType.esriDTTable)

            pExistingDs = pEnumDataset.Next
            Do Until pExistingDs Is Nothing
                If pExistingDs.Name = inITableName Then
                    If pExistingDs.CanDelete Then
                        pExistingDs.Delete()
                    End If
                End If
                pExistingDs = pEnumDataset.Next
            Loop

            Dim UID_FEATURE As String = "esriGeodatabase.Object"
            '** create a UID for simple features
            Dim pUID As ESRI.ArcGIS.esriSystem.UID
            pUID = New ESRI.ArcGIS.esriSystem.UID
            pUID.Value = UID_FEATURE

            ''** Create new FeatClass in PGDB
            Dim newITable As ITable = pFWS.CreateTable(inITableName, _
                                             pFields, _
                                             pUID, _
                                             Nothing, Nothing)

            
            Dim pCur As ICursor
            Dim pRowBuf As IRowBuffer
            pRowBuf = newITable.CreateRowBuffer
            pCur = newITable.Insert(True)

            Dim dtCol As DataColumn
            Dim dtColname As String
            Dim row As DataRow

            For i = 0 To inDataTable.Rows.Count - 1
                row = inDataTable.Rows(i)
                If Not IsDBNull(row.Item(0)) Then
                    '***add the rest of the attributes
                    For Each dtCol In inDataTable.Columns
                        dtColname = dtCol.ColumnName.ToString
                        Dim dtColType As String = dtCol.DataType.ToString

                        If Not IsDBNull(row.Item(dtColname)) Then
                            pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = row.Item(dtColname)
                        Else
                            If dtCol.DataType.FullName.ToString = "System.Int32" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CInt(0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.String" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CStr("NA")
                            ElseIf dtCol.DataType.FullName.ToString = "System.Double" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CDbl(0.0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.Decimal" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CDec(0.0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.DateTime" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CDate(Now())
                            End If
                        End If
                    Next
                    pCur.InsertRow(pRowBuf)
                Else
                End If
            Next
            pCur.Flush()

                    Return newITable

        Catch ex As Exception
            MsgBox(ex.ToString)
            Return Nothing
        End Try
    End Function

[CarlosPiccirillo]

James,

Thanks for the suggestion and sample code. I will give it a try and post back what the results were.

Yeah, intermmitent problems are never fun to debug especially when you are trying to show the problem to another programmer and the problem doesn't occur while they are watching.

[JamesCrandall]

James,

Thanks for the suggestion and sample code. I will give it a try and post back what the results were.

Yeah, intermmitent problems are never fun to debug especially when you are trying to show the problem to another programmer and the problem doesn't occur while they are watching.

See my edit I made above: try to implement your update with a RowBuffer instead.

[CarlosPiccirillo]

James,

We found the root cause of the sporadic update problem. Turns out two different versions of our data were conflicting. The update code was accessing default and unbeknownst to me, another newly created program written by the Python programmer was at times, trying to access the same version. The solution ended up being to create a new version for these updates and then when it is done, do a post in code. So far the approach is working so my boss doesn't want me to spend more time trying to re-write the code with your suggestions but I will certainly keep the code you gave me and if things start going badly again, I'll give it a try.

Just wanted to let you know and say thanks again for your help with this and other posts.

Carlos