2.61.4 bug ???

Here is some code that worked perfectly using previous versions.

   settdate  = {13/09/2007}
  
   d = new database()
   d.databaseName = "SSTbde"
   d.active = true

 
   qSuppliers = new query()  
   qSuppliers.database = d
   qSuppliers.sql = [select * from suppliers where date1 is null]
   qSuppliers.active = true
   rsuppliers = qSuppliers.rowset

// update suppliers table by adding the settlement date
   rsuppliers.first()
        
   do while not rsuppliers.endofset
        do while not rsuppliers.lockrow()    // loop until row is locked
        enddo

        rsuppliers.fields["date1"].value = settDate 
        rsuppliers.save()
        rsuppliers.unlock()
        rsuppliers.next()
   enddo   

I have tried the same code with other tables using the same scenario above and
it always produces the same result - It locks on the 101st row of the rowset.

Using debug it seemed that the rowset could not get a lock on the 101st row and
therefore went into an infinite loop. Regardless of which table I tested it on
it was always the same row (101st)

If the sql statement is changed to [SELECT * FROM suppliers] it works OK. By
adding 'settlementDate is NULL' creates the problem. 

Something to do with the following I suspect-
QAID 6361 - Fixed problem with automatic row locks that allowed the Save()
method to automatically unlock an explicIt lock obtained by calling lockrow().
" 

Hi John,


> Here is some code that worked perfectly using previous versions.
>
>   settdate  = {13/09/2007}
>
>   d = new database()
>   d.databaseName = "SSTbde"
>   d.active = true
>
>
>   qSuppliers = new query()
>   qSuppliers.database = d
>   qSuppliers.sql = [select * from suppliers where date1 is null]
>   qSuppliers.active = true
>   rsuppliers = qSuppliers.rowset
>
> // update suppliers table by adding the settlement date
>   rsuppliers.first()
>
>   do while not rsuppliers.endofset
>        do while not rsuppliers.lockrow()    // loop until row is locked
>        enddo
>
>        rsuppliers.fields["date1"].value = settDate
>        rsuppliers.save()
>        rsuppliers.unlock()
>        rsuppliers.next()
>   enddo
>
> I have tried the same code with other tables using the same scenario above

> and it always >produces the same result - It locks on the 101st row of
the 
> rowset.

> Using debug it seemed that the rowset could not get a lock on the 101st 
> row and therefore went into an infinite loop. Regardless of which table I 
> tested it on it was always the same row (101st)

Is this a dBASE table?

Did it successfully lock and update the first 100 rows?
Are you saying it will NOT lock after the first 100 rows?

If the answer is yes, there may be a problem with unlock() failing to
unlock() in this situation.

Remember there is a limit in the dBASE driver of 100 row locks in effect at 
the same time.


> If the sql statement is changed to [SELECT * FROM suppliers] it works OK. 
> By adding >'settlementDate is NULL' creates the problem.

Okay, so there is a workaround for now...


> Something to do with the following I suspect-
> QAID 6361 - Fixed problem with automatic row locks that allowed the Save()

> method to automatically unlock an explicIt lock obtained by calling 
> lockrow(). "

Possibly - we will investigate further.
If you can post a working test form (using the sample tables) that would be 
very helpful.

Thanks,

- Marty -

Martin Kay
dataBased Intelligence, Inc.

Marty Kay Wrote:

>> Is this a dBASE table?

Yes


> 
> Did it successfully lock and update the first 100 rows?

Yes


> Are you saying it will NOT lock after the first 100 rows?

Yes
 


> If the answer is yes, there may be a problem with unlock() failing to
> unlock() in this situation.
> 
> Remember there is a limit in the dBASE driver of 100 row locks in effect at

> the same time.
> 

> If you can post a working test form (using the sample tables) that would be

> very helpful.
> 

Try this prg 

First time I've used the sample database so hope you can get the code to work.



// create ref to database
d = new database()
d.databasename = "dBaseSamples"
d.active = true



// add drop table statement here if sales table already exists
** Dont know how to do this



// create table
qCreate = new query()
qCreate.database = d
qCreate.sql = [CREATE TABLE SALES (SALESID CHAR(6), CUSTOMERID CHAR(10),
ORDERDATE DATE,ORDERNMBR NUMERIC(7,0), ORDERAMT NUMERIC(9,2), DELIVERED
BOOLEAN)]
qCreate.active = true



// add 200 rows to table
qSales = new query()
qSales.database = d
qSales.sql = [Select * from sales]
qSales.active = true
rSales = qSales.rowset
for i = 1 to 200
	rSales.beginAppend()
   rSales.fields[1].value = "111111"
   rSales.fields[2].value = "AAAAAAAAAA"
   // keep date field as is
   rSales.fields[4].value = 12345
   rSales.fields[5].value = 123.45
   rSales.fields[6].value = true
   rSales.save()
endfor




// test query
qTest = new query()
qTest.database = d
qTest.sql = [SELECT * FROM sales WHERE orderdate is null]
qTest.active = true
rTest = qTest.rowset


newOrderDate = {11/11/1111}

do while not rTest.endofset
	
   do while not rTest.lockrow()    
   enddo

   rTest.fields["orderdate"].value = newOrderDate
   rTest.save()
   rTest.unlock()
   rTest.next()

enddo

Hi John. I submitted this as QAID 6404. Marty will take a look at this 
as soon as possible.

thanks, kathy
 

> Marty Kay Wrote:
> 
> >> Is this a dBASE table?
> 
> Yes
> 
> 
> > 
> > Did it successfully lock and update the first 100 rows?
> 
> Yes
> 
> 
> > Are you saying it will NOT lock after the first 100 rows?
> 
> Yes
>  
> 
> 
> > If the answer is yes, there may be a problem with unlock() failing to
> > unlock() in this situation.
> > 
> > Remember there is a limit in the dBASE driver of 100 row locks in
effect at 
> > the same time.
> > 
> 
> > If you can post a working test form (using the sample tables) that
would be 
> > very helpful.
> > 
> 
> Try this prg 
> 
> First time I've used the sample database so hope you can get the code to
work.
> 
> 
> 
> // create ref to database
> d = new database()
> d.databasename = "dBaseSamples"
> d.active = true
> 
> 
> 
> // add drop table statement here if sales table already exists
> ** Dont know how to do this
> 
> 
> 
> // create table
> qCreate = new query()

Hi John,

We have run your test case and about half a dozen more and I can report the 
following:
There is a change in behavior due to the change made for QAID: 6361as you 
pointed out.

In your test case, the where clause in your sql statement is the source of 
the problem.
The rowset for your query is restricted by the where clause to rows whose 
orderdate is null.
As you update each row ( by setting orderdate to a non-null value and 
calling the rowset's save() method)
the BDE determines that the row no longer meets the where clause of the sql 
statement and
removes row from the rowset.  By the time you are able to call the rowset's 
unlock method
the row is no longer in the rowset and is no longer the current row.
This prevents the unlock() method from unlocking the row.

A workaround, for the moment would be to just use the automatic locking and 
unlocking.
The save() method will unlock an automatic row lock.

As a longer term solution, there are 2 options that I can see:
   1. Back out the change made for QAID: 6361

or

  2. Add a parameter to the Save() method that allows you to tell dBASE to 
unlock an explicitly locked
      row during the Save() processing (before the BDE re-evaluates the 
where clause or any other
      filter conditions for the row).

Right now I'm leaning toward the second option.

What do you think?

- Marty -

Martin Kay
dataBased Intelligence, Iinc.