Deleted records not retaining their deleted status in cursor

[captsnappy]

I'm trying to load a cursor from a table and have the deleted records be flagged as deleted in the cursor. Not working so far. Here's my code:

select * from orders where &mfiltstr into cursor crsRptListOrders NOFILTER readwrite

(&mfiltstr can include "and deleted()" or "and not deleted()" )

The select statement is working in that if the filter is "and deleted()" it only selects deleted records, but they don't show up as deleted in the cursor.

What obvious, simple thing am I missing?

Thank you, all VFP gods on the forum here.

 

[MrDataGuy]

Interesting ( on one level) I have never noticed the issue before. Here is my feedback:

1)In general be very careful of deleted records. I can understand that a record may need to be deleted (I think inactive is a better choice) but with the possible exception of doing a RECALL leave deleted records alone. I knew of some programmers (and I use that term loosely in this case ) that would deleted records as a way to filter information and then recall the records at the end of the code. IMO a very bad idea!

2)I can confirm the issue you are seeing. The trigger is the use of the readwrite clause. If the code does not need to modify the cursor information then do not use the readwrite and you should be ok.

3)Below is a possible solution/kludge/work around, using the Delete_Filter ( or whatever you want to call the field )

SET DELETED off
SET SAFETY off
CREATE TABLE c:\Temp\Junk (Field1 c(10), Field2 I ) 


FOR I = 1 TO 20
	APPEND BLANK
	replace Field1 WITH STR( I )
	replace field2 WITH I
	IF I > 10 
		DELETE 
	ENDIF
	
ENDFOR

Select *, DELETED() as Filter_Deleted;
  from c:\Temp\Junk;
  into cursor qJunk Readwrite

DELETE ALL FOR Filter_Deleted

BROWSE normal

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[MrDataGuy]

Also one other comment. I see the NoFilter clause, is that really needed? Are there Set Filter commands in the code? IMO they are a bad idea (Other/many/most? VFPers may not share that view but is mine.) I have not used one since my FoxBase+ Days, not even in Fox Window, and I am 100% not in VFP 5.0 or beyond. There is so many better ways (IMO) to work with data in VFP then set filter. e.g. a SQL Select.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[jimstarr]

The NOFILTER keyword specifies that the result set will be a new, separate cursor rather than just a filtered version of the input table. I can't find any documentation of this, but it seems that in no circumstance will VFP create a cursor containing deleted records. So in all cases, using NOFILTER will lose the deleted status.

Jim

 

[MrDataGuy]

Jim:
NOFILTER "new, separate cursor"
You are right, forgot about that. Was getting it confused with the SET FILTER command. I forget the NOFILTER and fall back on the old trick of adding an extra 'junk' field to the select which forces the creation of a new separate cursor also. Bottom line the NOFILTER is the better way of doing it.

But the ReadWrite clause will also do that in this situation.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Dan Freeman]

Your mileage may vary here:

SELECT *, Deleted() as IsDeleted FROM .....

might add a column showing deletion status depending on how the WHERE is constructed, which may alter when/where VFP calls the Deleted() function.

The problem is that deleted() is an attribute that does not have any parallel in the SQL language. It just doesn't convey. The absolute best you can hope to do is create an attribute column that carries the deleted flag.

If records are deleted, you probably shouldn't be working with 'em. They're not supposed to exist and VFP is the only database where they're allowed to exist. In most databases, deleted means exactly that. If you're using deleted() to mean something else, it might be time for some re-engineering.

 

[Olaf Doschke]

I second jimstar: "it seems that in no circumstance will VFP create a cursor containing deleted records."

At least not via SQL. From the specs of SQL Select * means all fields, but the deletion mark is no field. You can't adress it as alias.deleted, can you?



That said you can use COPY to include the deletion mark: COPY TO newfile.dbf FOR deleted() will do that and as long as mfiltstr can also be used as a for clause this would also work as COPY TO newfile.dbf FOR &mfiltstr

Bye, Olaf.

 

[MrDataGuy]

captsnappy,
have we been any help to you? If not please give us some feedback on what is missing from our answers, so might be able to help you more.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Mrall]

Just a thought. If you have a use for deleted records, rather than deleting them why not create a logical field called selected.
Anything with the locical field selected = .t. You can do whatever to whenever you want encluding delete them perminatly.

Thanks

 

[captsnappy]

My apologies to all. I was pulled away onto another project that has totally consumed me since posting this topic.

Thank you everyone for your replies.

I was creating the cursor as the record source for a report and wanted to show deleted records with strike-thru font. I am not sure why I was using a cursor for this...maybe just a mind-f4rt from using cursors for grids...although sometimes I do create cursors for reporting b/c I'll do a lot of data manipulation to make the report design more straigh forward.

I'll just set the filter on the table and the deleted records will show on the report.

Thanks again to all and sorry for the delay in replying.

 

[Mike Lewis]

There's nothing at all wrong with using a cursor as the data source for a report. I expect most of us do it all the time.

Personally, if I was faced with this problem, I would do what the others have suggested, that is, add a logical field to the cursor that records the "deleted" status, and use that to determine whether you want to format the fields with strike-through.

But if you're happy to do it direct from the table, that's fine as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

I second Mike in both using cursors as report source all the time instead of the report DE and in adding a seperate field striktrrough.

In the case you have with a single table you could do

Select *, deleted() as strikethrough from orders where &mfiltstr into cursor crsRptListOrders NOFILTER readwrite

It depends much on the data quantity and how data is scattered in the table, but in general selecting records is faster than SET FILTER and it's more flexible, if you need to select data from more than one table. On the other side, if the need to query data from more table arises, it's not working to select deleted(), as you can't specify of what table you want to select it's deleted() status.

Therefore in the long run it would be advisable to have an additional field in the table instead of using the deletion flag as a field.

I go further and say at any time you really continue to work with deleted data, be it just to display it striked through or red, you'd not delete it but add a boolean field.

Bye, Olaf.

 

[Dan Freeman]

On the other side, if the need to query data from more table arises, it's not working to select deleted(), as you can't specify of what table you want to select it's deleted() status.

The problem isn't that you can't specify which alias (SELECT Deleted("alias") as ....) -- it's that you can't control when VFP will call the function. The more complex a query, the more likely VFP will use intermediate "work" cursors to derive the final result set, and it may call the function for every row in one of those intermediate cursors instead of the original table.

When I mentioned this technique earlier in the thread, I did say your mileage may vary.

I certainly agree that if using the deleted flag for actual meaning, it should be an actual data element.

 

[Olaf Doschke]

dan,

I just simplyfied when saying "you can't specify an alias, as I want to keep that story short. That can be explained in detail if captsnappy asks back.

In the MSDN Foxpro General Forum I recently gave an example how the alias names can and will differ while running an SQL Select and why you can't use an alias in conjunction with deleted() within SQL.

If you or captsnappy care I can point you there, but I have to dig that out first.

Bye, Olaf.

 

[Dan Freeman]

I suspect we have a translation difference here, Olaf.

You're saying that you can't pass an alias to deleted() but I suspect you don't realize that's what you're saying (or what I'm hearing).

You can "specify" an alias, of course. It just doesn't mean anything (which is what we're both really saying).

 

[Olaf Doschke]

You can technically pass an alias, but it won't do what one could expect, so finally this really means you can't pass an alias. The reason simply is, internally fox SQL engine works with other alias names.

Bye, Olaf.

 

[Olaf Doschke]

Just for the record, here I explain and show what's happening behind the scenes in SQL and how that affects DELETED() and other alias related functions:

http://social.msdn.microsoft.com/Fo...l/thread/d3c5c167-b99f-41df-8708-82f93dfa920d

Quintessence is what dan and I agree on: Better make deleted() a real table field - or actual data element as he put it - when you want to query it.

Bye, Olaf.

 

[TamarGranor]

Olaf - The discussion you and Dan are having is definitely about the meaning of words. You agree on the behavior of VFP.

You said that you "can't" pass the alias. "Can't" is generally understood as "are unable to." A better phrasing for what you actually mean is "must not" or "should not." "Must not" is stronger than "should not," but both make it clear that it's the programmer's action in question, not the programming language rules.

Tamar

 

[Olaf Doschke]

Tamar, you're hitting the right spot and yet not. Can't really translates 1:1 into german "kannst nicht" in the meaning of "are unable to", and yet that is technically wrong I would also say so in german.

Besides, "must not" does not translate 1:1 to "mußt nicht", but would be "darfst nicht", which like "must not" means "aren't allowed to". That or should not is surely the more proper wording.

Why do I still stand to "cannot"? Well, of course you can, but you don't get what you want. Which in turn means - to me - that you can't do it. And that may be a thought and double meaning of cannot more common in German.

It doesn't matter that you technially can pass in an alias to the deleted() function, within the sql it loses it's function. It's more like you can't step on the gas while you're engaging the clutch (not sure if that is correct english). You can of course, but you won't accellerate. And you might rather say "you shouldn't step on the gas" in that situation.

Bye, Olaf.

 

[Olaf Doschke]

An idiom better fitting my usage of "cannot" is "You cannot be serious!" or "You cannot be in earnest!".

Both of these usages of "cannot" do not mean "aren unable to" be serious/in earnest, do they?

Bye, Olaf.