Advantage database throws an exception when attempting to delete a record with a like statement used in the where clause

Question

The code below shows that a record is deleted when the sql statement is:

select * from test where qty between 50 and 59  

but the sql statement:

select * from test where partno like 'PART/005%'

throws the exception:

Advantage.Data.Provider.AdsException: Error 5072:  Action requires read-write access to the table

How can you reliably delete a record with a where clause applied?
Note: I'm using Advantage Database v9.10.1.9, VS2008, .Net Framework 3.5 and WinXP 32 bit

using System.IO;
using Advantage.Data.Provider;
using AdvantageClientEngine;
using NUnit.Framework;

namespace NetworkEidetics.Core.Tests.Dbf
{
  [TestFixture]
  public class AdvantageDatabaseTests
  {
    private const string DefaultConnectionString = @"data source={0};ServerType=local;TableType=ADS_CDX;LockMode=COMPATIBLE;TrimTrailingSpaces=TRUE;ShowDeleted=FALSE";
    private const string TestFilesDirectory = "./TestFiles";

    [SetUp]
    public void Setup()
    {
      const string createSql = @"CREATE TABLE [{0}] (ITEM_NO char(4), PARTNO char(20), QTY numeric(6,0), QUOTE numeric(12,4)) ";
      const string insertSql = @"INSERT INTO [{0}] (ITEM_NO, PARTNO, QTY, QUOTE) VALUES('{1}', '{2}', {3}, {4})";
      const string filename = "test.dbf";

      var connectionString = string.Format(DefaultConnectionString, TestFilesDirectory);

      using (var connection = new AdsConnection(connectionString)) {
        connection.Open();

        using (var transaction = connection.BeginTransaction()) {
          using (var command = connection.CreateCommand()) {
            command.CommandText = string.Format(createSql, filename);
            command.Transaction = transaction;
            command.ExecuteNonQuery();
          }

          transaction.Commit();
        }

        using (var transaction = connection.BeginTransaction()) {
          for (var i = 0; i < 1000; ++i) {
            using (var command = connection.CreateCommand()) {
              var itemNo = string.Format("{0}", i);
              var partNumber = string.Format("PART/{0:d4}", i);
              var quantity = i;
              var quote = i * 10;

              command.CommandText = string.Format(insertSql, filename, itemNo, partNumber, quantity, quote);
              command.Transaction = transaction;
              command.ExecuteNonQuery();
            }
          }
          transaction.Commit();
        }

        connection.Close();
      }
    }

    [TearDown]
    public void TearDown()
    {
      File.Delete("./TestFiles/test.dbf");
    }

    [Test]
    public void CanDeleteRecord()
    {
      const string sqlStatement = @"select * from test";

      Assert.AreEqual(1000, GetRecordCount(sqlStatement));
      DeleteRecord(sqlStatement, 3);
      Assert.AreEqual(999, GetRecordCount(sqlStatement));
    }

    [Test]
    public void CanDeleteRecordBetween()
    {
      const string sqlStatement = @"select * from test where qty between 50 and 59";

      Assert.AreEqual(10, GetRecordCount(sqlStatement));
      DeleteRecord(sqlStatement, 3);
      Assert.AreEqual(9, GetRecordCount(sqlStatement));
    }

    [Test]
    public void CanDeleteRecordWithLike()
    {
      const string sqlStatement = @"select * from test where partno like 'PART/005%'";

      Assert.AreEqual(10, GetRecordCount(sqlStatement));
      DeleteRecord(sqlStatement, 3);
      Assert.AreEqual(9, GetRecordCount(sqlStatement));
    }

    public int GetRecordCount(string sqlStatement)
    {
      var connectionString = string.Format(DefaultConnectionString, TestFilesDirectory);
      using (var connection = new AdsConnection(connectionString)) {
        connection.Open();

        using (var command = connection.CreateCommand()) {
          command.CommandText = sqlStatement;
          var reader = command.ExecuteExtendedReader();
          return reader.GetRecordCount(AdsExtendedReader.FilterOption.RespectFilters);
        }
      }
    }

    public void DeleteRecord(string sqlStatement, int rowIndex)
    {
      var connectionString = string.Format(DefaultConnectionString, TestFilesDirectory);
      using (var connection = new AdsConnection(connectionString)) {
        connection.Open();

        using (var command = connection.CreateCommand()) {
          command.CommandText = sqlStatement;

          var reader = command.ExecuteExtendedReader();

          reader.GotoBOF();
          reader.Read();

          if (rowIndex != 0) {
            ACE.AdsSkip(reader.AdsActiveHandle, rowIndex);
          }
          reader.DeleteRecord();
        }

        connection.Close();
      }
    }
  }
}

Answer 1

LIKE results in a static cursor instead of a live cursor, meaning it is a read-only dataset. To remove a row in this situation it would be better to use an SQL DELETE statement.

DELETE FROM test where partno LIKE 'PART/005%'

I'm assuming your tests are just that, only tests. They are using some fairly inefficient mechanisms to locate and remove rows.

Update after comment that there is no key field:

How about using the LEFT scalar instead of LIKE (might not work for all cases, but does for your example). If the size is always the same you could also add an index on left(partno,8) to increase the performance:

select * from test where left(partno,8) = 'PART/005' 

Then you could use the Delete function of the extended data reader directly on this live result set (no gotop and skip).

Update after Alex's ROWID comment I didn't know our ROWID came from the base table, even in static cursors. Alex's comment is the solution to your problem. First:

SELECT t.*, t.rowid FROM test t WHERE x LIKE 'PART/005%'

then:

DELETE FROM test WHERE rowid = :thisid