How to access database tables in a different directory

[namax]

Hi all,

I am currently creating a database and have 2 folders. A production folder and real data storage folder and would like to switch between these folders to access tables to test my project and also store real data. I have created a file called config.cfg in notepad and stored that in the production folder and would like to edit the path of either of the folders in this config file when switching between them so that when I open a data entry form, the application should access tables from the path specified in the config.cfg file and store data in the specified path.

What I have done so far, I have only copy of the database in the "real data storage folder" which I named as malcon_slidedatabase in the path "C:\malcon_slidedatabase". And all the forms,codes,reports,etc..and also the copy of the database tables in the folder which I named malcon_microscopy_db in the path "C:\Users\Administrator.Malconpc-PC\Documents\Malcon_microscopy_db".

I know there is a way out but really stuck here.Any start of would greatly be appreciated.

regards,

 

[NasibKalsi]

You can create myconfig.dbf with at least 2 fields. MyStatus and MyPath. Add two blank records. Enter 1 to say Active and 0 to say inActive in your MyStatus, and enter the path for each record. At start up Loop through and pickup the MyPath where it says Active (1).

Wait, you might get some better solutions from other experts.

Nasib

 

[Mike Lewis]

This is a familiar problem. Many of us have to deal with a similar situation in our applications.

You are correct that you need some form of storage mechanism to tell your application where to find its data. You use a text file named Config.CFG, which is fine. I use an INI file. Others you a DBF, or an entry in the Windows registry.

Given that you use a text file, you should place it in the same folder as your executable program. That way, you are guaranteed to be able to find it. In your main program, read the text file, and decide which directory contains the data. Then add that directory to your VFP search path. Do NOT hard-code any paths within your program (the text file itself contains the paths).

To summarise, in your main program, do something like this:

* First, set the default directory to that of the executable program
SET DEFAULT TO JUSTPATH(SYS(16))

* Read the text file
lcFile = FILETOSTR("Config.CFG")

* Decide which directory to use, based on the contents of the file
lcPath = <parse the text file to extract the directory path>

* lcPath contains the path to the required directory. Add
* it to the search path
SET PATH TO &lcPath ADDITIVE

* From now on, do NOT include the path when opening tables, etc.

This is just one possible approach. It's the method I have always used. The big advantage is that you can point the application to any directory simply by editing the text file.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

Please have a look at

http://www.jamesbooth.com/remotedata.htm

hth

MarK

 

[mjcmkrsr]

...

or write this code in the BeforeOpenTables method of the form/class

* [URL unfurl="true"]http://support.microsoft.com/kb/128996/en-us[/URL]
LOCAL lcDataPath, lnStartPos, i, lcObjClass, lcDataName, lcNewDataPath, lcObjName
LOCAL loCursor as Cursor

lcDataPath = "c:\YourDataFolder"

IF !EMPTY(lcDataPath)
   = AMEMBERS( laCursors, THISFORM.DATAENVIRONMENT, 1)
   = ASORT( laCursors, 2)

   ** nStartPos = ASUBSCRIPT( laCursors, ASCAN( laCursors, "Object"), 1)
   ** 2010-02-03, corrected by Igor Nikiforov
   lnStartPos = ASCAN( laCursors, "Object", -1, -1, 2, 15)
   IF NOT EMPTY(lnStartPos)
      FOR i = lnStartPos TO ALEN(laCursors, 1)
         IF laCursors[ i, 2] = "Object"
            lcObjClass = "ThisForm.DataEnvironment." + laCursors[ i, 1] + ".class"
            IF EVAL( lcObjClass) = "Cursor"
               lcObjName = "ThisForm.DataEnvironment." + laCursors[ i, 1] + ".Database"
               lcDataName = EVAL(lcObjName)
               lcNewDataPath = ALLTRIM( lcDataPath) + ALLTRIM( SUBSTR( lcDataName, RAT( "\", lcDataName) + 1))
               loCursor = EVAL( "ThisForm.DataEnvironment." + laCursors[ i, 1])
               IF NOT EMPTY(loCursor.Database)
                  loCursor.DATABASE = lcNewDataPath
               ELSE
                  loCursor.CursorSource = ADDBS(ALLTRIM(lcDataPath)) + JUSTFNAME(loCursor.CursorSource)
               ENDIF
            ENDIF
         ELSE
            EXIT
         ENDIF
      ENDFOR
   ENDIF
ENDIF
RETURN

hth

MarK

 

[mjcmkrsr]

...

or have a look at

http://support.microsoft.com/kb/128996/en-us

hth

MarK

 

[Olaf Doschke]

Depends on how you open data.

I'd think you're opening tables by using data environments. Then no matter if you set a different path or not, the members in the dataenvironment point to the database you used during development. A solution then is to use the code by mjcmkrsr to change all dataenvironment members - AMEMBERS( laCursors, THISFORM.DATAENVIRONMENT, 1) - and let them point to your configured path.

That shows you one bad side of the dataenvironment. Even if you didn't intended, you're having hardcoded paths by using it. You're on the right track using a config file to be able to switch to other folders. There's a mechanism of VFP for finding data, if it's not in the place stored in the dataenvironment members: VFP searches along all paths given by SET PATH, additional to the current directory set by CD or SET DEFAULT. Mike has addressed using that. Still your dataenvironment members may find the path encoded in them first, and won't switch to other data.

The solution is to have a third place for the data during development. A copy of the data only local on your computer. Then you may rename that main folder, eg preceding a ~ or _, if you don't want the dataenvironment tables to find their original place and want to switch away from development data to test or prod data. Of course you may also unmap a drive of dev data or switch to a different windows account not having access to dev database or even sitch computers.

The best solution anyway, would be to open data programmatically, not using the dataenvironment. You have OPEN DATABASE, where you can specify precisely the database you want. And after that all USE databasename!tablename will open tables from that database. You can even open two databases in different paths with same name, but the addressing with databasename!tablename then obviously is ambiguous. There's a solution to that, too: SET DATABASE ("full path to a dbc here") sets a specific database active, all USE tablename (without a dbf extension) after that use tables from that database.

Generally just switch between TEST and PROD at application start, perhaps even display the current data scope, especially if the scope is TEST, so no user later accidentally works in the test database with their work time wasted, as it's not going into production data, is based on most probably older data etc.

My recommendation:

Set up three databases/locations:
PROD: Available to end users, maybe even disallow access from your windows account (if you develop as internal employee of the company), have a separate windows account,
so you can't accidentally work in production, while you only wanted to test.
TEST: Available to you and a set of users willing to beta test software.
DEV: Available only to you, maybe local, not available to any beta testers or end users. You can test your newest development here. Make whatever manual changes, but leave the test database as is, until you have a new release. The test database then can even be used for testing database updates. Of course you can repeat tests after copying PROD to TEST, only applying updates to PROD, when the update of TEST succeeds. Write scripts or use tools generating scripts from dbc differences.

Set up appropriate windows account groups and users can only find the data they are allowed to use anyway. You only need to be cautious to beta testers using TEST data when testing and PROD data otherwise. It's easiest, if you only specify what data to use at application start, don't switch forth and back.

Bye, Olaf.

Sample setup:

DEV data in C:\dev\appname\databasename.dbc
TEST data in \\shared\test\appname\databasename.dbc
PROD data in \\shared\prod\appname\databasename.dbc

Add tables from C:\dev\appname\databasename.dbc to your dataenvironemnts, then only your usage of the application will use DEV data. For all other users the initial attempt of the dataenvironment always fails and VFP will search along SET PATH, so at start do what Mike suggested. I'd put it this way, only slightly simpliifed:

* First, set the default directory to that of the executable program
SET DEFAULT TO JUSTPATH(SYS(16))

* Read the text file
lcPath = FILETOSTR("Config.CFG")

IF NOT ADIR(laDummyArray,AddBS(lcPath)+"yourdatabase.dbc")=1
   Messagebox("The configuration is wrong, no database found")
ENDIF

* lcPath contains the path to the required database (without dbc file name). 
* Add it to the search path
SET PATH TO (lcPath) ADDITIVE

CONFIG.CFG now needs to be in the EXE folder having either \\shared\test\appname\ or \\shared\prod\appname\ or C:\dev\appname\ as it's text

Now the only problem is, even if YOU as the developer put in \\shared\test\appname\ into your config, your forms dataenvironments still open data from C:\dev\appname\, as it's hardcoded in the dataenvironment. So either you temporarily rename your dev database path to C:\dev\~appname\ or you switchto an account not having access to c:\dev folder, or change computer, or ... many thinkable solutions.

Using the code of mjcmkrsr to change paths of all dataenvironemnt members may even be faster, as there is no timeout in not finding a folder, but it's more complicated to get going with your config, then simply SET PATH.

Reasoning for this complexity is, the dataenvironments are meant for easy and fast development, VFP is more fit for single user development anyway, though it has a comprehensive help chapter on developing for multiple users and shared data access. Application Frameworks based on VFP often have their own classes for data access not based on the forms dataenvironment. You also don't have the dataenvironment in form classes of VCXes, it only exists in SCX forms.

Bye, Olaf.

 

[Olaf Doschke]

One more thing, namax,

I don't know, if you're familiar with network shares and UNC notation. \\shared\test or \\shared\prod in my sample is short for network shares you need to set up anyway. You may also map a drive letter, but of course you'll need network shares every user can access from their client. The UNC path typically has two components: \\server\sharename, so in this case it's two shares for prod and test data. You may also have one share of \\server\data and have prod and test as subfolders, but maybe you'll want to provide the test files from another server to get most performance for prod data without also serving for tests.

Bye, Olaf.

 

[namax]

Thanks all for your support. With the tip from all of you I managed to have it done and all is working fine.this is the code that i used.
In a separate command I created a procedure that would be used after wards in my main program.This the code:

PROCEDURE getconfig
gcDatabaseDir = ""
nFile = FOPEN("microscopy.cfg")
IF nFile == -1 THEN
MESSAGEBOX("Could not open configuration file: microscopy.cfg",16)
ELSE
DO WHILE !FEOF(nFile)
cLine = FGETS(nFile)
IF SUBSTR(cLine,1,14) == "[DATABASEDIR]="
gcDatabaseDir = SUBSTR(cLine,15)
ENDIF
ENDDO
=FCLOSE(nFile)
ENDIF
ENDPROC

The main program is this:

SET PROCEDURE TO microproc.prg
PUBLIC gcDatabaseDir, gcDatabase
_screen.Visible = .f.
DO getconfig
IF !EMPTY(gcDatabaseDir) THEN
gcDatabase = gcDatabaseDir + "\MICROSCOPY1.DBC"
OPEN DATABASE (gcDatabase) SHARED
SET DATABASE TO MICROSCOPY1

DO FORM main.scx
READ EVENTS
ENDIF

CLOSE DATABASES ALL
CLOSE ALL
RELEASE gcDatabaseDir, gcDatabase
RELEASE ALL

And in all of my forms' dataenvironment init() event, I included this code:

SET DATE BRITISH
SET DELETED ON

FOR i = 1 TO THIS.Objects.count
IF UPPER(THIS.Objects(i).Class) == "CURSOR" THEN
THIS.Objects(i).Database = gcDatabase
ENDIF
ENDFOR

Once again, thankyou all for the tips.

regards,

Yanx

 

[Mike Lewis]

Glad you managed to get it working.

Just a couple of observations:

1. You don't really need to do all that low-level file access (FOPEN, etc). As we showed you earlier, you can just use FILETOSTR() to get the text file into a variable. After that, you can use AT() to find [tt][DATABASEDIR]=[/tt], then proceeed as per your code.

2. It's good practice to avoid public variables. Instead of storing the database directory in gcDatabaseDir, your GetConfig routine should just return it to the main program (via a RETURN statement).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Another point is, if you change the database property of dataenvironment tables on the fly (set THIS.Objects(i).Database = gcDatabase) and a form uses the default data session and tables with same name/alias of another database are already open, then that won't close them and use tables of the specified database. Haven't tried, but I think VFP is not very precise about that, if an alias already is there, that's already fine and VFP will take that as the table which should be opened.

It's nothing to really worry about, as you set gcDatabaseDir once at program start anyway. Also this won't matter, if all your forms have private data sessions, which is better for many reasons anyway, unless you want to share the same data session between some forms. You can share data anyway, even in private datasessions, as you can reopen the same table for each form, but each form will have it's own record pointer, then. Both an advantage or disadvantage, sometimes.

Bye, Olaf.