Advantage database throws an exception when attempting to delete a record with a like statement used
- by ChrisR
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();
}
}
}
}
