Changing Data in Original Table

[FoxEgg]

Hi Folks

I am hoping to exploit the season's good will, because I fear my question is a bit basic and I probably deserve to be hauled over the coals for not knowing how to do this....
I have searched and tried to find the action I want to do.... without avail.

OK so (and no criticism yet please) .... I am converting a much loved FPDos program to run on VFP8... I AM NOT rewriting everything yet... (just replacing FORMS with print outs that I wrote in HP PCL5 escape codes... and trying to replace browse commands with Grids etc)

SO...
In my original prg, one command brings up a BROWSE with some fields editable, some not... It works... It is S-L-O-W but it works... OK so no problems so far.

To upgrade, I thought I would use SQL Select either into a CURSOR or into a BROWSE screen... I can do this... but these are READ ONLY temporary files.

So here is the question:

1. How do I change the data in the READ ONLY screens ( [READWRITE] didn't seem to do anything ) to make it editable.

and

2. If I do manage to change the data in the CURSOR or BROWSE screens... how do I then convey that data change back to and alter the original table ...


My original FPD program line was

BROW fields ENTRY_NUM :2 :H= '#', PRINT :P='!' :2 :H= 'Pr', TRANSFER :4 :P='!!!' :H= 'T/F' , percent_fe :3 :H= '%', FILE_NUM :R :18 :P='!!!!!!!!!!!!!!!!!!' :H= 'File Number',  LAST_NAME :R :12 :H= 'Last Name', item_numb :R :6 :H= 'Item #', idx_sequen :2, SERVICE :19 :R  TIMEOUT(15) NOMENU WINDOW MARY for file_num = fnum && 250299 removed -->> service :10

It still works in VFP8 but in both FPD and VFP8 it is slow....

My latest attempt is

SELECT entry_num, print, transfer, percent_fe, file_num, last_name, item_numb, idx_sequen, service from 1 ;
WHERE file_num = fnum;
INTO cursor brwser2 readwrite

I do know how to use GRIDS ... (but the calibre of my excellence is better described as 'trial and error')

OK so be nice... Its my birthday soon, so don't carve me up.

Fox Egg

 

[Dan Freeman]

You're looking for an updatable local view.

It's a query, but it can be set to update the underlying data when you issue TableUpdate().

You'll need a DBC, even if there's nothing it but the view. (Many of us actually use temporary DBC files created on the fly just for this purpose.) Then CREATE VIEW myView and you'll see the View Designer, which looks an awful lot like the query designer.

Check out the help topics on Buffering, Tableupdate(), Tablerevert(), and Buffermode. And then come back when you have questions. You will.

 

[FoxEgg]

Thank you.. will do...
'I'll be baack ! ' (spoken with heavy Austrian-American accent)

JF

Sydney, Australia

 

[Dan Freeman]

We'll be here!

The thing to remember here is that it's more difficult to explain than is is to *do*.

It may all seem new, but once you get through it once you'll be all set.

 

[FoxEgg]

Well, I see what you mean... about "difficult to explain".. I have put in several hours


I created the DBC
Put the table inside
Ran the view designer (updatable local)

I selected (checked) the two upgradeable fields... (The rest I didn't want to change)
I checked the "Send SQL update" box (in the Upgrade Criteria section)
Ran the view .. It worked

Changed a few of the values in the upgradable allowed fields ... AND later changed the values in fields where I wasn't supposed to change the values. (or rather they were not supposed to upgrade.

That is when it got funky... both got changed///

Don't reply just yet,,, it is late here.. I have been at this all day and will give it a fresh start tomorrow


Quick Question: Do I ABSOLUTELY have to have a PRIMARY KEY in my dbf for the view to work ? I am thinking that might be one of my problems. (Besides general dullard-ness)

JF

Sydney, Australia

 

[Mike Lewis]

Hi JF,

I fear my question is a bit basic and I probably deserve to be hauled over the coals for not knowing how to do this....

Come now. When have we ever hauled you over the coals? We're not about to start now.

OK so be nice... Its my birthday soon, so don't carve me up.

ditto

Don't reply just yet,,, it is late here.. I have been at this all day and will give it a fresh start tomorrow

It's tomorrow here now, so I'll go ahead and answer.

Quick Question: Do I ABSOLUTELY have to have a PRIMARY KEY in my dbf for the view to work ?

Quick answer: No, you don't need a primary key for the view to work, but you do need a primary key to make the view updatable (if you didn't, VFP would have no way of knowing which record to update). But note that the key doesn't have to be a single field; it could be any combination of fields that has a unique value.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Also in regard to fields you don't want to change: Don't check them as being updatable. In the Update Criteria tab uncheck the fields you don't want to be written back into the DBFS.

Here's also where you can check multiple fields to be the primary key. The primary key should not be changed during the editing of course, otherwise the record to update can't be found, as you make this identifier a moving target. If you don't have such fields, it's not hard to add a field.

Besides that, there is no need to make any DBF part of the DBC, only the view itself needs the DBC to be stored in there, there are no free views as separate files, but you can click on "other tables" and choose free table files or tables of any other DBC, it doesn't matter, this is fully stored into the view definition. Adding tables to the DBC doesn't hurt in general, if you work on them with a VFP5-9 compiled code, but it does change the header and the DOS app may then reject to open it, so only do that if you fully move to VFP, it shouldn't be a first step. That's what dan implicitly said by "You'll need a DBC, even if there's nothing it but the view", you only need to define the view with the DBC open, you don't need to touch anything in all your other database files.

Bye, Olaf.

 

[Dan Freeman]

I see Mike and Olaf have addressed the highlights.

* No need to add the *table* to the DBC
* You need a primary key for the update

Next step: make that view parameterized.

WHERE file_num = [b]?[/b]fnum

Then, you can rerun the query just by changing the variable fnum.

Olaf mentions that you can use tables from another DBC in your view. That's true, but don't. It creates a maintenance nightmare.

I'm working on a dozen or more apps where the original developer wrote views that used tables from all over the enterprise. The table/dbc locations not only span multiple folders, they also span multiple servers. The only way to get one of them to open is to first USE thedbc, locate the view's record, and manually examine the SQL. Then I can go make sure all the necessary dbcs or tables are open before opening the view. It's a nightmare.

 

[Dan Freeman]

Oops.

Then, you can rerun the query just by changing the variable fnum.

Then, you can rerun the query just by changing the variable fnum and calling Requery().

 

[FoxEgg]

Hi,

I have spent a while on this... as previously
Dan I now appreciate that this has a steep learning curve

So I
> created the DBC
> put the table inside
> ran the view designer (updatable local)

I have tried HEAPS of options...

I selected all fields...
I checked the "Send SQL update" box (in the Upgrade Criteria section)
Ran the view .. It 'seemed' to work

Changed a few of the values in the updateable allowed fields ... AND later changed the values in fields where I wasn't supposed to change the values. (or rather they were not
supposed to upgrade.

But it was funky... in some runs it changed the whole column of values ...

Got the error message "The key defined by the keyField property for table foxegg2015/1 is not unique."

I cannot seem to find the view1 file anywhere on the computer...
? How do I run a view programmatically ?
I copied the SQL code into the program and if didnt like the first line which was

DBSetProp(ThisView,"View","SendUpdates",.T.) .. error said something about ThisView

It is all becoming a bit too difficult...
Sorely tempted to go back to my old BROW Command,, slow BUT always WORKS

Fact is that I MUST NEED A PRIMARY KEY if I am to make it UPDATEABLE (which was the purpose of the whole exercise in the first place.

So do I add just another field to the original table.... and fill each with a unique random number and call it PRIMARY.. seems like a wasteful exercise for the purpose of having a PRIMARY KEY so the View will work

JF

PS Mike thanks for your much appreciated comments...

Sydney, Australia

 

[Olaf Doschke]

>Got the error message "The key defined by the keyField property for table foxegg2015/1 is not unique."

At least you understand you need a primary key:
>So do I add just another field to the original table.... and fill each with a unique random number and call it PRIMARY.. seems like a wasteful exercise for the purpose of having a PRIMARY KEY so the View will work
This is not wasteful, it's the normal way to define a primary key!

You have a column only there to be the key. It doesn't need to be random, it can be a integer autoinc field, though in conjunction with views, especially in a schema with parent/child it makes it harder to create foreign keys for child tables, because you only know primary keys after a tableupdate but would need them beforehand, if you create parent and child records at the same go buffered.

Mike has told you, you can also specify a group of fields, two or more, that are unique. And I said you need to take care these values don't change, because otherwise VFP can't find the records to update.

So besides realizing you need a primary key you already have advice on how to decide for one or add one.

>I cannot seem to find the view1 file anywhere on the computer...
What did I wrote?

there is no need to make any DBF part of the DBC, only the view itself needs the DBC to be stored in there, there are no free views as separate file

There are no view files. Views are stored in the DBC/DCT/DCX files, the DBC is a table, that stores meta data about the tables and also the views themselves. Views are only meta data. There is no view files, because in the end a view is just meta data, it's mainly the query you define and some settings about primary key fields, updatable fields, etc.

Yes, it's hard to get the first view to run, but you really need some interactive help, as it seems. You don't need to execute the code you find when you switch to the SQL code is nothing you need and not written beginner friendly, eg ThisView would be a variable you need to define and set to the view name. This would later be helpful, if you'd need to define views with more complex queries the query designer can't let you design visually. But forget about these commands, they are there to define the view and this is what you already do by using the visual designer. Nobody told you to go there and need this, you only need to choose table, fields, primary key, updatable fields. You only can define updatable fields after having selected primary key field or fields.

To use the view you first have to open the database with OPEN DATABASE and then USE viewname. Or in the verbose version you USE database!viewname. This is the same syntax as USE database!tablename, you're simply not used to this as you rather USE c:\mydata\mytables\tabl1.dbf, but that's not needed and not possible with views, as views are not tables, they don't have files, so they don't have a path. The database knows them and so you need the database open and only need the viewname. Views don't need a separate file for themselves, as they don't store data in themselves, they always query tables and store changes back in these tables. Views are rather code than data and that's why dan mentions he rather creates the DBC and views at runtime. That's not what you need to begin with, it's advanced stuff. It makes view updates easier, as you don't need to get exclusive access to the shared central DBC, it makes view changes as easy as code changes.

And again, just because you don't seem to thoroughly read, you shouldn't add your tables to the new dbc, you only need the dbc to define and later use the view. It's the home of the view/views, doen't need to be the new home of your data, you only will risc code not working as adding free dbfs to a dbc changes their headers. Legacy code will error on opening non free tables.

Bye, Olaf.

 

[FoxEgg]

Thanks Olaf,

It was a long day in the office (with a couple of other unwanted non VFP upsets).... However home now and with Sauv Blanc in hand, and inspired by your comments, I shall venture forth tomorrow with rejuvenated vigour.

I really do appreciate the detailed response... Tomorrow I shall swing the Tek-Tips sword and slay the savage VFP VIEW beast.

Excuse the flamboyant language... its my birthday tomorrow; poetic license is allowed.

JF


Sydney, Australia

 

[Olaf Doschke]

>Excuse the flamboyant language
No need for excuses, to conquer views is a heroic thing and such language is totally suitable.

Just one thing to excuse from my side: As I said "Nobody told you to go there" (there being the sql code the view designer offers) that was not meant as rebuke of you going there, nobody explicitly forbid to look at that. You're eager to learn everything, but you overburden yourself with doing more than you need for a start and that as always with a totally new larger topic raises more questions as it answers. It seems to turn out harder than we thought of, to define a primary key for you as one of the problems, but instead of trying to find ways to circumvent it, as you seemed to do, just bow to the inevitable creation of a primary key and everything will follow.

I just tested to simply add an integer (autoinc) field to a free table. That type is available even for free tables, but will also render the dbfs useless for legacy code, the table remains free, but still will become a vfp8/9 type of free dbf. It also won't automatically create a number sequence for existing records, they all get a value of 0, only new records will be numbered 1,2,3,.... So for the moment I'd opt for a choice of multiple already existing fields remaining unique.

If all your table fields could change, you would need to add a new field for the primary key. Without default values it's hard to create an automatic value. So either you have to give up the dbf type of a free legacy table or you need to change code inserting new records to compute something unique for the new id primary key field.

And of course one more thing: Even latest version free dbfs don't offer the primary index type, but that's also no reason to make the dbfs dbc tables, free dbfs offer candidate indexes, which are usable as primary index replacement and also will suffice the view need for a guaranteeing a unique value. I would guess Mike and Dan would opt for the change towards DBC tables, as it makes things easier, and a simple reason for that pragmatic solution is, you already did add the dbfs to the dbc. If excluding legacy access to the dbfs is of no importance the easiest primary key is the autoinc integer, despite of the problems with it foreign keys. As you designed your DBFs without primary keys that will likely not be a problem for you anyway. So if you decide for autoinc, just start by adding a normal int field, then UPDATE thetable SET id=RECNO() to get existing records numbered and then modify the int field to autoinc, where you need to specify next value to be max id + 1. Then create a primary index on that.

If you want to take my advice and route you'd restart with a backup of your dbfs or at least free them with FREE TABLE (test if legacy code then can use the tables again, I have a doubt) and then would rather use a char(36) field for a GUID (aka uniqueidentifier) value you can create via some Windows API functions. Besides not being able to create it as default value of the id field it's the easiest to create a unique value, even in disconnected situations, as GUIDs are unique globally, even if not created by the database. But I'll not show how to create GUID values, as long as you don't decide for that, because this post already has got longer already and you only need the code eventually.

Bye, Olaf.

 

[Mike Lewis]

Tomorrow I shall swing the Tek-Tips sword and slay the savage VFP VIEW beast.

Don't think of it as a savage beast, but rather as a friendly kitten. Instead of slaying it, play along with it and learn its ways. It will give you a lot of fun, and you may even come to love it. (Hmm, maybe I'm stretching the metaphor a bit here.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[FoxEgg]

Metaphor stretched... and understood
Sauv Blanc finished

GUID field was created and populated (Thanks to a Professor Gagnon program from the past for his GUID program) ((Where is he anyway ?))

With the Key field created.. The VIEW worked as it should.


Now, onward and upward to learn the Freeman suggestion

Next step: make that view parameterized.

WHERE file_num = ?fnum

Then, you can rerun the query just by changing the variable fnum.

Thanks for the encouragement guys

JF


Sydney, Australia

 

[FoxEgg]

Credit where credit is due:

Create a GUID with two lines of code
faq184-2486
Posted: 21 Sep 02

Requires WSH 5.6


[B]oGUID = CreateObject("scriptlet.typelib")
cGUID = substr( oGUID.GUID, 2, 36 ) [/B]

Mike Gagnon

Except I limited it to 12, not 36.

J

Sydney, Australia

 

[FoxEgg]

Hi and thanks again

As I mentioned, I created a Key field (I went back and made it 36 not 12) Then the VIEW worked as it should...
Got what looked like a Grid... when run from the VIEW DESIGNER Menu
I also added the ?fnum parametric and it also worked

Now putting it into the program, I followed the plan below

To use the view you first have to open the database with OPEN DATABASE and then USE viewname. Or in the verbose version you USE database!viewname.

and I used

set console on
USE foxegg2015!view1

But nothing appeared on screen

Did the same from the COMMAND Window... nothing on screen.

I am sure that there is a simple explanation.


JF



Sydney, Australia

 

[Dan Freeman]

When you issue

Use (table)

Nothing appears on screen. This is normal behavior.

You need to do something to put it on screen, like Browse or DO FORM.

 

[Olaf Doschke]

In regard to your latest visualisation problem, as dan says. And one more question: Did you save data? When using an updatable view instead of a table you can add data to the view and as it's an updatable view it can store that new or changed data to the table, but it doen't do this fully automatic, you have to call TABLEUPDATE(). Then restarting the app and reusing the view also will display that saved data. Otherwise your edits and new records will simply be lost.

And more important thing. Even if the first 12 chars of a guid are quite random and unique, only the whole value is guaranteed to be unique in the long run, so don't cut it off.

Bye, Olaf.

 

[FoxEgg]

Thanks Olaf...

I did mention that I took your advice and used the full 36 random GUID...

Thanks for the TABLEUPDATE() info. I had not done that.

[For some reason (at this moment) all of my REPORT FORMS are printing BLANK... Grrrr...]

JF




Sydney, Australia