SQL Update() does not update

[Koen Piller]

Hi,

I have this code which does not update

Update PERMISSION ;
SET ;
PERMISSION.Permissionnmbr = curPermiss5a.Permissionnmbr , ;
PERMISSION.Permissionfrom = curPermiss5a.Permissionfrom , ;
PERMISSION.Permissiontill = curPermiss5a.Permissiontill , ;
PERMISSION.Permissiondate = curPermiss5a.Permissiondate ;
FROM curPermiss5a ;
WHERE PERMISSION.ID = curPermiss5a.ID
lnRec = _Tally
la =  AERROR(aPermis)
If lnRec = 0
	lnError = 8
Else
	m.llSuccess = Tableupdate(.T.)
Endif
If llSuccess = .F.
	lnError = 16
Endif

Select VERZEKNR

[ul]
[li]The cursor curPermiss5a all fields have values.
The values of the both ID's are equal
aPermis does not show any update errors
My Errorcatcher is not activated
lnRec = 1 [/li][/ul]
and still the fields of the table to be updated, PERMISSION, remains blank
Any clue how to find what is the cause of this unwanted behavior?

I keep on puzzling.

Koen

 

[Olaf Doschke]

What is the buffering/locking state of curPermiss5a? And how is it created? By a CA or updatable view on permission.dbf?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
Buffering of cursor curPermiss5a was 5, I changed to 1: no effect.
Cursor was created with a SQL Select from 3 dbf's

Select ;
T1.Id, ;
T1.Permissionfrom,	;
T1.Permissiontill,	;
T1.Permissionnmbr,	;
T1.Permissiondate, ;
T1.Permissionrqst, ;
T1.iVerzeknr, ;
T2.Permissionrecvd, ;
T2.ID As VerzeknrID, ;
T3.chemoenz, ;
T3.nierdialyse, ;
T3.ID As NAWID ;
FROM PERMISSION T1 With (Buffering = .T.) ;
JOIN VERZEKNR T2 With (Buffering = .T.) On T2.ID = T1.iVerzeknr ;
JOIN NAW T3 With (Buffering = .T.) On T3.ID = T2.iNAW ;
INTO Cursor curPermiss5a Readwrite NOFILTER

Regards,
Koen

 

[IlyaRabyy]

Koen,
Did you try FLUSH FORCE command?

Regards,

Ilya

 

[Olaf Doschke]

A readwrite cursor is writable, but does not update underlying tables.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

It would only by chance, if you do a single table query and get a filter cursor.

An updatable cursor needs to be a view or CA, because you already get an error 1468, if you try to CURSORSETPROP("SendUpdates", .T.).

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,

It is not the cursor curPermiss5a which needs to be updated it is the DBF Permission.DBF which needs to be updated.
This technique to update a dbf with the content from a cursor is used for 'ages'.

@Ilya,
The lowlevel 'Flush Force' was not issued, I will give it a try.

Regards,
Koen

 

[Olaf Doschke]

Koen, fantastic, then it should be easy to set this up as it's necessary and you'll get this yourself.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,

sorry I think I donot follow. What is fantastic?

Regards,
Koen

 

[TamarGranor]

What do you see when you use a query with the same conditions?

SELECT * FROM PERMISSION ;
 JOIN curPermiss5a ;
   ON PERMISSION.ID = curPermiss5a.ID

Tamar

 

[Koen Piller]

Tamar,
I get a query with one record, correct according to the code, with all fields from PERMISSION.DBF and curPermiss5a with data from their relevant sources.
Koen

 

[TamarGranor]

Okay, so what do you see if you check the table right after the UPDATE command?

Tamar

 

[Koen Piller]

Tamer,
I notice that the fields are not updated, they remain blank as initially.
You know I lost lust to find out what is going on here. So, since it is after all only an update of a few fields, I changed my code from

SQL..Update

to a

REPLACE Permissionnmbr WITH curPermiss5a.Permissionnmbr,;

etc. works fine and I can go on.
Thanks for your advise and sorry for bothering.
Koen

 

[Nigel Gomm]

how are you checking the table after the update?

Could this be SET SQLBUFFERING ?

hth

n

 

[Koen Piller]

Nigel,

You can check the Buffering with ln= Cursorgetprop('Buffering', cTableAlias)
if ln = 5 Optimistic Table Buffering is on

Regards,

Koen