I have an application from an outside vendor that is written in VFP. I would like to develop a separate system that would be used to do quick look ups that are specifically written for the users and develop some reports that they could run. What is the easiest/best way to access that database ensuring that I don't change any data inadvertently? I have already linked to it and did a quick form and it seemed to work okay. I did manage to change the current data for a few records that I had to manually fix because of a programming glitch. I know that I can set the tables in the data environment to be read-only but I'm afraid that I will forget and cause a problem with the 'production' database. Is there a foolproof system that this fool could use?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Inquire only on Database
- Thread starter KarenJB
- Start date
Mike Lewis
Programmer
Hi Karen,
Setting the tables (or more precisely the cursors) in the data environment to read-only won't have any effect outside your application. This setting does not affect the database in any way. It is purely local to yourself.
Another option would be not to use the data environment, but instead to explicitly open the tables with USE ... NOUPDATE. This will have the same effect as the read-only setting.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Setting the tables (or more precisely the cursors) in the data environment to read-only won't have any effect outside your application. This setting does not affect the database in any way. It is purely local to yourself.
Another option would be not to use the data environment, but instead to explicitly open the tables with USE ... NOUPDATE. This will have the same effect as the read-only setting.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
- Thread starter
- #3
Mike Lewis
Programmer
Yes, either method will work.
If you already have the tables in the data environment of the form, and if that method is working for you, then leave things as they are. Just change the ReadOnly property (of the cursor within the data environment) to .T.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
If you already have the tables in the data environment of the form, and if that method is working for you, then leave things as they are. Just change the ReadOnly property (of the cursor within the data environment) to .T.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Olaf Doschke
Programmer
Really only a copy of the database files can ensure, you don't change anything in the production data. When you tested your software on a databse copy and when you feel more comfortable, you may change to working on the prodcution dbc.
Still there are several options still unmentioned:
From the GUI side, THISFORM.Setall("Readonly",.T.) woul d help.
SET MULTILOCKS ON + CURSORSETPROP("Buffering",5,0) will set table buffering on for all tables that will be opened from then on in the current datasession. This needs to be done repeadedly for every form with a private datasession, in the load event of forms. Then, changes will at least be buffered, only Tableupdate() but unfortunately also simply closing the tables will write tese changes back to DBFs. If you keep everything opened until the form unloads, you may simply do Tablerevert()s on any open alias before releaseing the form.
Do SQL in code: SQL SELECT...INTO CURSOR NOFILTER or READWRITE. Even though READWRITE sounds wrong, it's not linking the SQL result to the DBF(s) the data came from.
Both NOFILTER and READWRITE assure you get a cursor, which is it's own temp file, with no backlink to the DBFs, READWRITE enables you to change values in the result cursor, but those are not written back to the DBFs, this can still be handy to fill in calcualted fields, which you first select blank, eg select ...0 as somevalue,...
You can double ceck, what file an alias is with ? DBF("cursorname"), this should give you some .tmp file, not the DBF file you selected from.
Omitting both NOFILTER and READWRITE can lead to results just being a filtered view of the DBF, and then changes CAN affect the DBF. That is in situations of simple SQL (Selects from a single table) with a fully optimizable where clause.
For example soething like SELECT * FROM table INTO CURSOR curResult can give you a cursor, that in fact simply is the DBF itself. DBF("curResult") then would return the fullpath to the DBF file.
One risk remains: Even though with the NOFILTER or READWRITE clause SQL-Select does create seperate files, it also opens DBFs in side workareas, so you have the DBFs opened writable. At this point I would advice you to make yourself comfortable with the term and concept of workareas and aliases.
Either you scan through all used workareas (AUSED) and close all workareas which give JUSTEXT(DBF(ALIAS()))=="DBF", or you open the DBFs yourself NOUPDATE/.Readonly=.T. in advance to any SQL, that would then retrieve data from those readonly aliases.
Avoid using REPLACE, DELETE APPEND, ZAP, PACK without using their IN clause, which explicitly determines the alias you want to work on, which then should be a cursor, or you might change something not only in the current workarea. Use naming conventions and name your cursor results with a prefix "cur" (VFP has a chapter about naming conventions that in fact recommends this for cursor objects (in the DE), not for alias names, but still "cur" or "crs" are often used prefixes for cursor aliases in forums and newsgroups).
Unfortunately there is no simple setting preventing any changes, like SET READONLY ON. Even OPEN DATABASE NOUPDATE does not prevent changes to any database table, only the the database (DBC/DCT/DCX) files themselve, so it prevents ALTER TABLE and such. Setting files readonly obviously wouldn't work for the production system.
But I hope this gives you some options. Surely only workin on a copy of the data is the safest.
Bye, Olaf.
Still there are several options still unmentioned:
From the GUI side, THISFORM.Setall("Readonly",.T.) woul d help.
SET MULTILOCKS ON + CURSORSETPROP("Buffering",5,0) will set table buffering on for all tables that will be opened from then on in the current datasession. This needs to be done repeadedly for every form with a private datasession, in the load event of forms. Then, changes will at least be buffered, only Tableupdate() but unfortunately also simply closing the tables will write tese changes back to DBFs. If you keep everything opened until the form unloads, you may simply do Tablerevert()s on any open alias before releaseing the form.
Do SQL in code: SQL SELECT...INTO CURSOR NOFILTER or READWRITE. Even though READWRITE sounds wrong, it's not linking the SQL result to the DBF(s) the data came from.
Both NOFILTER and READWRITE assure you get a cursor, which is it's own temp file, with no backlink to the DBFs, READWRITE enables you to change values in the result cursor, but those are not written back to the DBFs, this can still be handy to fill in calcualted fields, which you first select blank, eg select ...0 as somevalue,...
You can double ceck, what file an alias is with ? DBF("cursorname"), this should give you some .tmp file, not the DBF file you selected from.
Omitting both NOFILTER and READWRITE can lead to results just being a filtered view of the DBF, and then changes CAN affect the DBF. That is in situations of simple SQL (Selects from a single table) with a fully optimizable where clause.
For example soething like SELECT * FROM table INTO CURSOR curResult can give you a cursor, that in fact simply is the DBF itself. DBF("curResult") then would return the fullpath to the DBF file.
One risk remains: Even though with the NOFILTER or READWRITE clause SQL-Select does create seperate files, it also opens DBFs in side workareas, so you have the DBFs opened writable. At this point I would advice you to make yourself comfortable with the term and concept of workareas and aliases.
Either you scan through all used workareas (AUSED) and close all workareas which give JUSTEXT(DBF(ALIAS()))=="DBF", or you open the DBFs yourself NOUPDATE/.Readonly=.T. in advance to any SQL, that would then retrieve data from those readonly aliases.
Avoid using REPLACE, DELETE APPEND, ZAP, PACK without using their IN clause, which explicitly determines the alias you want to work on, which then should be a cursor, or you might change something not only in the current workarea. Use naming conventions and name your cursor results with a prefix "cur" (VFP has a chapter about naming conventions that in fact recommends this for cursor objects (in the DE), not for alias names, but still "cur" or "crs" are often used prefixes for cursor aliases in forums and newsgroups).
Unfortunately there is no simple setting preventing any changes, like SET READONLY ON. Even OPEN DATABASE NOUPDATE does not prevent changes to any database table, only the the database (DBC/DCT/DCX) files themselve, so it prevents ALTER TABLE and such. Setting files readonly obviously wouldn't work for the production system.
But I hope this gives you some options. Surely only workin on a copy of the data is the safest.
Bye, Olaf.
Olaf Doschke
Programmer
One further idea: You could retrieve data with Remote views to the DBC, non updatable views of course. Creating a secondary DBC with the remote views. Even though local views would be sufficient, via remote views you's not open up the dbf's in side workareas, you only get back the results.
The downside is, you are limited to VFP6 features, eg if the database has newer field types, this won't work.
Another but even more complicated solution is, your secondary reporting/searching/datamining application impersonates a system user you set up, which only has read access to the network share.
Bye, Olaf.
The downside is, you are limited to VFP6 features, eg if the database has newer field types, this won't work.
Another but even more complicated solution is, your secondary reporting/searching/datamining application impersonates a system user you set up, which only has read access to the network share.
Bye, Olaf.
- Status
Similar threads