Converting from DBISAM3 to EDB has me looking critically at every SQL in my
app.

The problem relates to updating a field that is also the primary key of the
table.

In navigational code there is a danger of double updating.  i.e. if I'm
doing a

while not EOF do
begin
   //edit the record and change the primary key
  Next
end;

.... if the editing moves the record towards the end of the table, then I
will encounter it again and it will get processed and moved again.  Maybe a
perpetual loop.

To avoid this I have done this in DBISAM3:-   (TenantCode,ChangeDate is the
Primary and only key)

SELECT * INTO MEMORY TempRents
FROM Rents
WHERE TenantCode = '1234'
;
'DELETE FROM Rents
WHERE TenantCode = ' 1234'
;
UPDATE MEMORY TempRents
SET ChangeDate = ChangeDate + 20
;
INSERT INTO Rents
SELECT * FROM MEMORY TempRents
;

My question is, can I safely do this SQL:-

UPDATE Rents
SET ChangeDate = ChangeDate + 20
WHERE TenantCode = ' 1234'

.... and not hit the multiple update problem

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


You should be able to. Indices don't exist as such in SQL, the code you've shown should simply wade through the table and make the changes.

Roy Lambert [Team Elevate]

Thanks Roy

I guess was guilty of over-thinking things when I wrote the original code.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:57DB763F-E1F6-44A2-AFB2-5FCBA099B38F@news.elevatesoft.com...
> Jeff
>
>
> You should be able to. Indices don't exist as such in SQL, the code you've
> shown should simply wade through the table and make the changes.
>
> Roy Lambert [Team Elevate]
>

Jeff,

in addition to Roy's answer:

Here a sample script, which will not work like expected, because it's a
sensitive result. Since the dataset is changed when iterating it, EOF
doesn't work here.

   DECLARE FID INTEGER;
   DECLARE Result CURSOR FOR Stmt;

   PREPARE Stmt FROM
      'SELECT * from Test where Done = false';
   OPEN Result;

   FETCH FIRST FROM Result('ID') INTO FID;
   WHILE NOT EOF(Result) DO
     UPDATE Result SET 'Done' = true;
     FETCH NEXT FROM Result('ID') INTO FID;
   END WHILE;

In such a case make sure that the dataset is readonly. Since you cannot
update a readonly cursor, you have to change the update line.

   DECLARE FID INTEGER;
   DECLARE Result INSENSITIVE CURSOR FOR Stmt;

   PREPARE Stmt FROM
      'SELECT * from Test where Done = false';
   OPEN Result;

   FETCH FIRST FROM Result('ID') INTO FID;
   WHILE NOT EOF(Result) DO
     Execute Immediate 'UPDATE Test set Done = true where ID = ?' using
FID;
     FETCH NEXT FROM Result('ID') INTO FID;
   END WHILE;

Hope that helps.

Uli

Uli

You have baffled me here. First Jeff was asking about a simple SQL statement not SQL/PSM and second I'm confused with your example.

>Here a sample script, which will not work like expected, because it's a
>sensitive result. Since the dataset is changed when iterating it, EOF
>doesn't work here.
>
> DECLARE FID INTEGER;
> DECLARE Result CURSOR FOR Stmt;
>
> PREPARE Stmt FROM
> 'SELECT * from Test where Done = false';
> OPEN Result;
>
> FETCH FIRST FROM Result('ID') INTO FID;
> WHILE NOT EOF(Result) DO
> UPDATE Result SET 'Done' = true;
> FETCH NEXT FROM Result('ID') INTO FID;
> END WHILE;

To save me carrying out my own tests - what happens. Is the result the one I'd expect if I did the same using Delphi? What I'd expect is the loop to terminate with c50% of the rows not set to DONE = True

Again not tried but I'd expect to use something like:

DECLARE FID INTEGER;
DECLARE Result CURSOR FOR Stmt;

PREPARE Stmt FROM
'SELECT * from Test where Done = false';
OPEN Result;

FETCH FIRST FROM Result;
WHILE NOT EOF(Result) DO
UPDATE Result SET 'Done' = true;
FETCH FIRST FROM Result;
END WHILE;

Roy Lambert [Team Elevate]

Uli

Roys way is good. If you have an ID field (single field containing a primary key) on the table you can also do this:

DECLARE FIDs VARCHAR;
DECLARE Result CURSOR FOR Stmt;

PREPARE Stmt FROM
'SELECT LIST(CAST(ID) as VARCHAR)) as IDs from Test where Done = false';
OPEN Result;

FETCH FIRST FROM Result('IDs') INTO FIDs;
CLOSE Result;
EXECUTE IMMEDIATE
 'UPDATE Test SET Done = True WHERE ID IN ('+FIDs+')';

Adam

Roy,

> To save me carrying out my own tests - what happens. Is the result the one I'd expect if I did the same using Delphi? What I'd expect is the loop to terminate with c50% of the rows not set to DONE = True

Right.

I completety misunderstood the question - so just ignore my post please.

Should read more slowly and carefully.

Uli

Uli

>Should read more slowly and carefully.

Been there, got the T-Shirt

Roy