[V4RB] Updating index of newly imported records seems to fail for me

[Richard Altenburg]

(development on Mac OS 9, RB 5.5.2, V4RB 1.10 - testing on Windows XP)

Ruslan (and others),

just as with our previous release of our software, we are in big troubles
right before the release weekend. The same thing is bugging me again!

I import a large textfile with 50,000 newsgroup names into a table, and I
can not set the index of this table to a certain value. In previous cases, I
used a REINDEX of the table to solve the issue, but that does not help me
now.

What I do is import groupnames into a table that has the serverURL as the
key. When I try to set the key of all groupnames with a serverURL of empty
to the current serverURL, I get 0 rows affected returned. Please, please
help me!

This is the code:

  If ( tmrNewGroups.listSuccessful ) Then
    
    pbtRemoveGroupFromFavourites.Enabled = False
    pbtRemoveGroupFromFavourites.Refresh
    lbxFavoriteGroups.Enabled = False
    lbxFavoriteGroups.Refresh
    
    fdiTemp = GetTemporaryFolderItem
    
    If ( fdiTemp <> Nil ) Then
      
      vcsAvailableGroup = Nil
      
      tosBufferStream = fdiTemp.CreateTextFile
      
      If ( tosBufferStream <> Nil ) Then
        
        cvsPreferencesStatus.SetKey("lng", kWritingNewsgroupsToTemporaryFile
)
        
        #If TargetCarbon
          
          Self.UpdateNow
          
        #Endif
        
        tosBufferStream.Write tmrNewGroups.theListOfGroups
        tosBufferStream.Close
        
        ' Delete previous entries for the current news server from the
database.
        
        cvsPreferencesStatus.setkey("lng",
kDeletingPreviousDatabaseEntriesFrom + " " + newsServerURL )
        
        #If TargetCarbon
          
          Self.UpdateNow
          
        #Endif
        
        rowsAffected = thePPOBase.SqlExecute("DELETE FROM Newsgroup WHERE
ServerURL='" + newsServerURL + "'")
        
        ' Import temporary textfile with all groupnames into the database.
        
        cvsPreferencesStatus.SetKey("lng",
kImportingTemporaryFileIntoDatabase )
        
        #If TargetCarbon
          
          Self.UpdateNow
          
        #Endif
        
        vcsImportGroup = New VCursor(thePPOBase, "SELECT GroupName FROM
Newsgroup WHERE ServerURL = ''", kV_Client, kV_ReadWrite)
        
        If ( vcsImportGroup <> Nil ) Then
          
          vcsImportGroup.ImportText(fdiTemp, " ", (Chr(13)+Chr(10)))
          vcsImportGroup = Nil
          
          thePPOBase.Flush
          
          ' Delete the last imported row, which has a name of "."
          
          rowsAffected = thePPOBase.SqlExecute("DELETE FROM Newsgroup WHERE
GroupName = '.'")
          
          ' Delete any row that has an empty GroupName (sometimes happens)
          
          rowsAffected = thePPOBase.SqlExecute("DELETE FROM Newsgroup WHERE
GroupName = ''")
          
          ' Link the newly imported groupnames to the current news server
URL.
          
          cvsPreferencesStatus.SetKey("lng",
kLinkingNewGroupsToCurrentNewsServer )
          
          #If TargetCarbon
            
            Self.UpdateNow
            
          #Endif

          ' ********** RUSLAN PLEASE LOOK HERE ! **********
          
          ' ********** THIS SQL EXECUTE RETURNS 0 (ZERO) IN rowsAffected
**********
          rowsAffected = thePPOBase.SqlExecute("UPDATE Newsgroup SET
ServerURL = '" + newsServerURL + "' WHERE ServerURL = ''")
          
          '' Because of a bug in Valentina (I think), reindex the
server_recid field, and then all is fine.
          '
          'cvsPreferencesStatus.SetKey("lng", "Re-index the newsgroups
table")
          '
          '#If TargetCarbon
          '
          'Self.UpdateNow
          '
          '#Endif
          '
          'rowsAffected = thePPOBase.SqlExecute("REINDEX TABLE Newsgroup")
          
          thePPOBase.Flush
          
          cvsPreferencesStatus.SetKey("lng", "")
          
        Else
          
          If ( DebugBuild ) Then
            
            MsgBox "Could not create import cursor in stopListNewGroups,
please contact Brainchild"
            
          End If
          
        End If
        
      Else
        
        If ( DebugBuild ) Then
          
          MsgBox "Could not create disk stream in stopListNewGroups, please
contact Brainchild"
          
        End If
        
      End If
      
      ' Make sure the temporary FolderItem is deleted when we are ready
importing it.
      
      fdiTemp.Delete
      
      populateListboxAvailableGroups
      
    Else
      
      If ( DebugBuild ) Then
        
        MsgBox "Could not create disk file in stopListNewGroups, please
contact Brainchild"
        
      End If
      
    End If
    
    pbtRemoveGroupFromFavourites.Enabled = True
    lbxFavoriteGroups.Enabled = True
    
    
  End If