Change DBF

[sashaDG]

Hi everyone.
I solve the problem: there is a dirn dbf file (without indexes, not connected to the database), this file needs to be edited (add records from other files, change records, delete, save).

I want to do it this way: I create SELECT * FROM dirnn INTO CURSOR Tmpdir NOFILTER. Then I plan to use cursorsetprop (buffering) and TABLEUPDATE. Am I moving in the right direction?

This application should be shared, Thank you!

 

[Mike Lewis]

Hello SashaDG and welcome to the forum.

Frist, [tt]CURSORSETPROP()[/tt] and [tt]TABLEUPDATE()[/tt] are valid things to do in connection with updating a table, and are relevant to your question. In summary, you use [tt]CURSORSETPROP()[/tt] to establish buffering; you then edit the data, either programmatically or via user interaction; and finally you either commit the changes, using [tt]TABLEUPDATE()[/tt], or you cancel them, using [tt]TABLEREVERT()[/tt].

If you are not familiar with those functions, you can read up on them in the Help.

[tt]SELECT ... INTO CURSOR[/tt] isn't really relevant here. All that that does is to create a temporary copy of your original table. It's not clear why you want to do that.

You said you want to add records from other files. I assume you mean from other tables (DBFs). If so, the usual way to do that is either [tt]APPEND FROM ...[/tt] or [tt]INSERT INTO ... SELECT ... FROM[/tt]. Again, the Help will give you more information.

Finally, the fact that your table is "without indexes, not connected to the database" is not relevant in this context: it does not affect the method of updating it.

Does all this answer your question? If not, perhaps you could clarify exactly what you want to know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gerrit Broekhuis]

Hi Sascha,

If you do need the cursor (not sure) and want to make changes there, you should add the READWRITE clause.

Regards, Gerrit

 

[Chris Miller]

Major question is: Are you working inside Foxpro or in another programming language via ODBC or OLEDB Provider to VFP data?

Because it would drastically change what to recommend to use for working on VFP data.

Also, and again this drastically changes what to use: Is that DBF one of the types VFP supports natively? Because DBF isn't DBF, if it's Clipper or a more modern version of dBase or Harbor or anything not mentioned in this topic about the DBF file formats VFP supports in the Table Header Record Structure for byte offset 0.

If it's not in there, i.e. if the first byte in your DBF is neither of the values mentioned there, then it becomes remote data access to VFP and you would not be able to use VFP SQL but do SQLEXEC through a relevant ODBC driver or use that driver in your own language.

And to make it clear, the help topic there is only about the header structure of DBFs VFP supports, it does not describe all the other DBF types there may exist including extensions made by Sybase for Advantage Database or VFPA.

So, to summarize:
1. What is your programming environment/development stack
2. What type of DBF do you have judged by its first byte.

To see the first byte would just need a hex editor or a little programming of FOPEN and reading in 1 byte, I'm sure you'll get that whatever you program in.

Chriss

 

[Chris Miller]

Looking at it from the perspective of the usability of VFPs TABLEUPDATE:

TABLEUPDATE works on the buffer of a VFP workarea of a VFP data session that has buffered changes.

And that workarea can be:
1. USE of a DBF simply opened by USE and set in buffermode by CURSORSETPROP("Buffering",buffermode)
2. USE of a Local or Remote View that's made updatable, typically all done by the view designer and all applied to the workarea by usage of the view.
3. populating by a cursoradapter, which can act quite like a local or remote view.
4. populated by SQEXEC(), which is always using ODBC and thus is access of remote data only. Needs CURSORSETPROPS additional to buffering, that tell TABLEUPDATE what to target.

And actually not VFP SQL INTO cursor. You make me wonder if you could apply all the CURSORSETPROP setting you'd also do for a workarea populated by SQLEXEC, but I don't think that works. Well, see it as a questionable use of the TABLEUPDATE SQL query combination, as for native use of TABLEUPDATE you'd rather USE the dbf and not query it. Usage of the whole topic of buffered shared data access on DBFs would come to either local views or cursoradapters, not direct SQL INTO CURSOR, if you ask from the usage of TABLEUPDATE within VFP for DBFs.

Chriss

 

[sashaDG]

Guys, you gave a lot of information so quickly, I appreciate it, thanks. But it's getting hard for me where to start

 

[Chris Miller]

sashaDG,

you get so many answers covering so many cases, as your situation is vague. So perhaps start with my main question: Are you asking for DBF access out of another programming language or are you working, perhaps starting with VFP?

Chriss

 

[sashaDG]

SELECT ... INTO CURSOR isn't really relevant here. All that that does is to create a temporary copy of your original table. It's not clear why you want to do that.

why not relevant? Am I not making a temporary copy? what better to do

If you do need the cursor (not sure) and want to make changes there, you should add the READWRITE clause.

NOFILTER not the same?

Major question is: Are you working inside Foxpro or in another programming language via ODBC or OLEDB Provider to VFP data?
1. What is your programming environment/development stack
2. What type of DBF do you have judged by its first byte.

I write only on VFP.I don't know the answer to the second question and I don't understand what it is for

1. USE of a DBF simply opened by USE and set in buffermode by CURSORSETPROP("Buffering",buffermode)

I plan to use this.

The question arises, in the future, how can I create a multiplayer mode (I plan to use buffering 2 or 5, I don’t know which is better). The case when a row is deleted n -> the table is updated (should shift n + 1 rows and remove the void) is it possible to do this without a primary key? I don't seem to be looking...

 

[Chris Miller]

SELECT ... INTO CURSOR isn't really relevant here.

why not relevant? Am I not making a temporary copy? what better to do

It's not wrong thinking, but the concept of a query that creates an updatable workarea is an updatable local view. You're reinventing the wheel if you do a query into cursor and then all the necessary CURSORSETPROPS, that's all part of a view and done when you make use of that.

In fact you can't make a query result cursor update the DBF it came from, because several of the CURSORSETPROPS you see in the code myearwood points out will not work for a query cursor.
Even just trying CursorSetProp("Tables","dirn") fails with the error "Property is invalid for table cursors". CursorGetProp("SourceType") is 3, which says the workareas data source is a table itself. In fact the result of a query is a table, no matter if you pick into table or into cursor.

The concept of using n tier architecture in VFP is with views, local for DBFs, remote for backends like MSSQL, or cursoradapter, which can be native (=local) or remote. You're trying to mix two things that don't mix.

You can simply USE a table, then there is only one relevant CURSORSETPROP, that is the buffering, and then all you do on that workarea goes into its buffer and TABLEUPDATE commits it to the DBF. With the disadvantage you can't do queries to only pick some fields, to only get some records, to join other data, etc. But that's all doable with views or cursoradapter. So if you want to use the query concept there are you possibilities.

Chriss

 

[sashaDG]

The dirn file is located on the local network.
1. We talked about remote access above, I don’t understand what it is about.
2. I don't use the SQL server (I don't know how to use it)

Maybe I don’t need to use G, but when updating, delete empty constructions (if it’s possible in VFP)

You can simply USE a table, then there is only one relevant CURSORSETPROP, that is the buffering, and then all you do on that workarea goes into its buffer and TABLEUPDATE commits it to the DBF. With the disadvantage you can't do queries to only pick some fields, to only get some records, to join other data, etc. But that's all doable with views or cursoradapter. So if you want to use the query concept there are you possibilities.

It turns out the cursor adapter is better, but what difficulties does it have?

 

[sashaDG]

i think i need to look at the functions: SCATTER, GATHER..

 

[Mike Lewis]

You've been given a lot of information. Perhaps too much information. I won't add to that, except to answer just one of your questions.

You asked:

[tt]SELECT ... INTO CURSOR ....[/tt] why not relevant? Am I not making a temporary copy? what better to do

Yes, that command creates a temporary table (which is what a cursor is). It will contain a copy of your original table.

The problem is that if you update the cursor, those updates will be lost when the cursor is closed. You will need to take extra action to write the updates back to the original table. That's why I said it wasn't relevant.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

i think i need to look at the functions: SCATTER, GATHER..

Well yes and no, but mostly no.

Buffering has outdated the use of scattering data to variables to bind to. It's the oldest and most outdated way to do data editing by SCATTER, edit variables, to finally commit by GATHER or revert by not doing the GATHER. It seems easier, but it lacks doing what TABLEUPDATE or TABLEREVERT can do for the whole buffer of multiple records. and the buffer also doesn't pollute the variable namespace.

The latest SCATTER/GATHER improvement with NAME and ADDITIVE allows to do a few more useful things, but regarding data binding and editing buffering is the intended tool and better option. Use of scattering to an object makes a whole record one parameter you can pass to a form in another private datasession or even to COM.

I don't know what kind of programmer you are. If you're very familiar with SQL then why not go the route of using views? You define and reuse your SQL there, can do parameterized views, query from multiple tables, etc., etc. in short leverage all your knowledge about SQL.

Or you are like most and prefer the simplest way of editing by USE (to open), bind to controls or programmatically APPEND/SEEK/LOCATE/REPLACE/DELETE and then USE (to close) and be done with it even without any buffering and tableupdate or revert.

VFP always offers multiple ways, not only but also because of backwards compatibility and there's a whole chapter about shared data access that's explaining shared access and buffering:

https://www.vfphelp.com/vfp9/_5wn12p7iu.htm

There are always ways to reason differently, for a single user application it makes less sense to work with shared access and maybe also skip the usage of buffering, unless you're putting weight on only committing a full change instead of a set of changes and not let the user quasi edit the files themselves even into a half baked intermediate state. Also see the topic of transactions, I mentioned them lately a few times already.

But let me stop here. I already began comparing what VFP offers with MSSQL isolation levels. I don't think this will be helpful here, but if you're an MSSQL guy and know isolation levels in and out, the comparison might help. If you want, I could post something about that.

Chriss

 

[Chris Miller]

Mike Yearwood,

thanks for pointing out, I now reread the whole thread and let's focus on one of the latest decisions made by Sasha:

It turns out the cursor adapter is better, but what difficulties does it have?

That's also supported by you saying...

I'd definitely use the CA if I could.

So, Sasha, if you want to go for cursoradapter (=CA), then I'd recommend using a builder, perhaps the one improved from what VFP itself offers by Bernard Bout. Unfortunately his foxite web blog is gone.
One starting point, but that was from the time CAs were new in VFP8, is this:

https://www.codemag.com/Article/0301062/Introducing-the-CursorAdapter-Class

Take a look, play with it and see, whether it is approachable to you.

Chriss

 

[Chris Miller]

Perhaps a better entry point, as it's a later publishing date 2004/2005. I've not fully read it, but looks decent with all the aspects it covers:

http://www.redware.com/handbooks/vfpcursoradaptor/index.html

Chriss

 

[sashaDG]

Thank you for your responsiveness.

VFP always offers multiple ways, not only but also because of backwards compatibility and there's a whole chapter about shared data access that's explaining shared access and buffering:

https://www.vfphelp.com/vfp9/_5wn12p7iu.htm

Thanks for the site, I'll take a look

And also

http://www.redware.com/handbooks/vfpcursoradaptor/index.html

We should show him a piece of code to set his table to tablebuffering. Then let the UI update the contents of multiple rows. Then in the save, begin a transaction, tableupdate the rows with getnextmodified and if all goes well, commit. If anything fails, rollback.

That would be great

I think I need to read CA(link above), then 'Programming for Shared Access'(above).
And where i can read about 'views'?

We talked about remote access above, I don’t understand what it is about

if I read about CA, I will answer my own question?

 

[Chris Miller]

if I read about CA, I will answer my own question?

I am aware there are still open questions, but why the haste. If this question is so pressing for you, remote means quite what the natural meaning of the word is, remote, far away, not regarding VFP, access to data of other databases. Views, for case of DBF access through views, refer to

https://www.vfphelp.com/help/_5wn12p6xd.htm

btw, this online help causes headaches to me whenever I follow links in it. So while I give links to the help online at vfphelp.com, you better look for the exact same topic in your local help file, where you can navigate normal, for example to topics in the section "See Also".

Chriss

 

[sashaDG]

I need to use datasourcetype = Native in remote view and CA ?

 

[Chris Miller]

In CA you need to use datasourcetype native, that's right.

You need to use local views, not remote views. You don't have anything to do with remote data access, that's why I skipped any questions related to that. I just thought - even after you said and I overlooked it - that using SQL you are familiar with other databases already, and with SQL. Forget remote for a moment. Remember, your central question was about a DBF, and that can also be another system or you may ask in this forum about DBF access, not because you are using VFP. That wasn't naturally assumed, at least not by me.

To make it very clear, LOCAL is about DBFs, not about local drives. So you also don't need remote views just because the DBF is on a "remote" or other computer on a network share. That's still covered by local views.

Local views have no datasourcetype, they are stored in a VFP DBC database and usually query the tables in the same DBC, but can also address free DBFs, as you have one, or DBFs of other DBCs. But since CAs are more modern, perhaps only look into local views, because they are less complex than CA and teach you concepts necessary for specifying what TABLEUPDATE needs to know to work.

Chriss

 

[Chris Miller]

The case when a row is deleted n -> the table is updated (should shift n + 1 rows and remove the void)

It's more precise to talk of records, also in terms of how the VFP language refers to reccount, recno, etc.

But you can assume row and record are used as synonyms in the following, too:

That's not what you do. It's not a bad idea to reuse deleted rows for new rows, but there's a very strong reason DBFs were not designed to automatically shift rows up, when one is deleted. Assume a table has grown to MBs, perhaps even 100s of MBs. Deleting one row somewhere in the middle of the file, with the goal of keeping the records in their order, you actually need to copy the file part after the deleted row up recsize() bytes, which isn't a fast file operation. That's why there is a deletion flag in each record you just mark a record as deleted and don't remove it physically from the DBF file.

The other option is to fill the gap, but not in the fashion to move all further records one up, instead just move the last undeleted record to the gap record and delete it. In each further step, obviously look for the last undeleted record to swap with a deleted. To keep the chronological order, which isn't the physical order anymore, the only reliable thing is a datetime field. Nothing else.

You asked in detail:

Is it possible to do this without a primary key?

There is no need. I don't know why you thought it, maybe you only thought about an autoincrement integer. This actually makes it impossible, as the autoincrementing field becomes readonly, only written once when a new record is generated. If you thought about the id number keeping track of the chronoloigcal order, that's right, with a primary key index on a sequential integer column the records are in numerical order, even if you do swaps to remove gaps.

It's not the natural thing to do anyway, the common sense solution is to not be picky about it, and PACK your tables in a maintenance over night from time to time. There is SET DELETED ON, to suppress any record marked as deleted from anything, from SQL or non-SQL operations, so that's enabling to work with DBFs as if the deleted records in them actually are not part of the file. There are only very few things this affects, there is no way to alternative between two colors by just using odd/even of the recno. But once you use view or CA, which both query data of a DBF into a cursor, at start you always have no deleted records in them and you can use modulo 2 of the record number to alternate between 0 and 1, for example. Or as simpler aspect, you have reccount records numbered from 1 to reccount in the view. Which isn't the full table, usually, but just the section of data needed in a form, like one order to process order fulfillment of it, or a list of orders not fully processed - in contrast to all orders. There always is a way to only get the data you actually need instead of all.

Just take this away from all of it:

1. Your requirement isn't necessary
2. You work with SET DELETED ON - remember it means to turn ON the feature of taking the deletion flag into account to suppress access to deleted rows.
3. You PACK DBFs as maintenance.

And by the way, this isn't just a thing about DBFs, it's a common scheme to actually allow fragmentation of data files also in server databases, and to not keep the database size as compact as necessary. This wasn't even a concern back in the days dbase started andd hdd space was expensive. Performance had a bigger impact on the usability of data, and still has, than saving as much space as you could possibly do. You also don't shrink server databases just because you deleted a record in a table. It would be just a waste of time with no benefit.

By the way, even if ou do what's possible with moving deleted rows to the end of a file. The reorganization of index node recnos you trigger by this doen't make the tree structure of an index optimal, this happens when you reindex because you pack more optimal, so a goal of avoiding PACK is actually not desirable as you then don't reorganize indexes from time to time, but instead grow them into something thaat eventually becomes less effective.

Any concerrn you have with gaps is better addressed individually. Like alterating background coloring is easily done in a report when you use a report variable that counts and use modulo 2 of it instead of the recno of the report data. That's universally applicable to any report without any complicated stunt man actions to keep a DBF file "tidy".

Chriss