Accessing Connection within DataBase Contaiiner

[MdotButler]

I have a need to change the connection information stored in a database containing a bunch of remote views (VFP 7). For example, if doing training I would change the connection to open the "Training" database, if testing I would open the "Testing" database... All databases would be SQL databases stored in different servers. This is not the actual reason for needing this but will suffice as an example.

This needs to be done the first time the database is openned. I see that there are events that are fired upon doing the "open database", specifically the "OpenData". My question to the group is how to drill down to the connection and modify the DSN or ODBC name?

Has anyone done this or is there a better way that I am not thinking of?

TIA
Mark

 

[JRB-Bldr]

You could do this within the VFP database in a couple of ways, but both would involve having 2 separate Connections defined.

Create a VFP Database Connection to "Training" and another one to "Testing".

It sounds like you are using Remote Views into your remote database tables. With that in mind from here you have a few choices depending on how you are connecting to the remote database:
1. Create separate Remote Views into each separate table in each separate database. Then have your code 'recognize' which environment you were in and use the appropriate Remote View.
2. Have your VFP application dynamically CREATE SQL VIEW (a.k.a. your Remote View) based on the environment you were in using the REMOTE CONNECTION which was appropriate.
3. Open the VFP database, but don't use Remote Views. Instead use SQL Pass-through. With your VFP Database OPEN you can utilize the previously defined Connections (whichever one was appropriate) to set up the SQL Pass-through.

There are most likely other means which others might suggest, but those are the ones that come to mind at this time.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Hi Mark,

In addition to the good suggestions from JRB-Bldr, here's what I think is an easier method.

First, make sure the database (DBC) is open, but not any of the remote views. Then use DBSETPROP() to change the Datasource property of the connection object.

For example, if the connection object (within the DBC) is called MyConnection, and the DBC is called MyDatabase, and the back-end data sources are called Training and Testing respectively, you would do something like this:

* Swicth to Training database
OPEN DATABASE MyDatabase
DBSETPROP("MyConnection", "CONNECTION", "DataSource", "Training")

Alternatively, if you are more comfortable using connection strings, change the ConnectString property instead.

The advantage of this method is that it is independent of the remote views -- it will still work even if you change the views or the back-end data.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Very good advice, Mike!

And this shows another reason, why you should have DBCs with (remote) views local to each user, because only then you can change the connection within the DBC this way at the start, when no views are in use.

Bye, Olaf.

 

[Mike Lewis]

Thanks, Olaf. Yes, I agree with what you say about keeping the DBC local to the user. I generally do that for performance reasons, but it's also useful for keeping changes local.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[MdotButler]

Thanx Mike and Olaf for the ideas.

I did hook this into the "OpenData" method of the database. I kept the code external to the database so it could be included in the execute and the database excluded from the project. At least that is the idea for now. Here is the test program for anyone reviewing this thread.

PROCEDURE dbc_OpenData(cDatabaseName, lExclusive, lNoupdate, lValidate)
*Immediately after DBC is opened.
SET DEBUG ON
LOCAL lnMod
lnMod = MOD(SEC(DATETIME()),2)
DO CASE
CASE lnMod=0
   pcMyDSN = "DRIVER=SQL Server;SERVER=MXW2\SERVER1;UID=UserTest;PWD=PW2;DATABASE=CMSTEST"
CASE lnMod=1
   pcMyDSN = "DRIVER=SQL Server;SERVER=MXW2\INSTANCE2;UID=User1;PWD=PW1;DATABASE=EDI"
ENDCASE
*
DO CASE
CASE TYPE("pcMyDSN")="U"
   RETURN
CASE TYPE("pcMyDSN_Set")="C"
OTHERWISE
   PUBLIC pcMyDSN_Set
   pcMyDSN_Set = TTOC(DATETIME())
   WAIT WINDOW pcMyDSN
   SET DATABASE TO (JUSTSTEM(cDatabaseName))
   DBSETPROP("TestConnection", "CONNECTION", "CONNECTSTRING", pcMyDSN)
ENDCASE
SET DATABASE TO (JUSTSTEM(cDatabaseName))
WAIT WINDOW DBGETPROP('TestConnection',"CONNECTION","CONNECTSTRING")
ENDPROC

Again thanx for the ideas.
Mark

 

[Mike Lewis]

Mark,

Yes, that looks like it should work. I don't see why you need to hook into DBC_OpenData; it looks like an unnecessary complication, although you probably have a good reason.

By the way, you mentioned excluding the database from the project. Whether it's in the project or not doesn't make a scrap of difference. The point is you need to exclude it from the executable. The fact that you're doing a DBSETPROP() requires the DBC to be external to the executable, as it would be read-only otherwise.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

I assume you have some other factor determining what database to use, than MOD(SEC(DATETIME()),2), which merely is for a demonstration purpose, isn't it.

As Mike already said you could do that code even before opening the database. It's of course okay, even a good idea to keep that logic as close to the database, if it's rather a decision per user account, then per application for example. Because any application working with the same DBC then would be routed to the corrct remote database.

The downside is, that database events will prevent any usage of that database via ODBC.

If you ever need that, you better put that logic into your application, not into the database. As another solution external processes needing database access can still do so via the OLEDB Provider, when dbc events are turned on.

This reason may be of low impact though, as the DBC in itself is for remote database access with it's connection and if non VFP apps would need access to that data they perhaps won't use a DBC with remote views anyway, but adress that remote data more direct.

Bye, Olaf.

 

[MdotButler]

Mike
- Putting the code into the "opendata" method insures that each time the database is openned the code will be executed before any remote view can be accessed.
- Yes I did mean the database is excludeed from the execute but the program executed as part of the "opendata" would be part of the execute.

Olaf
- The code was just used as a proof of concept and has test code in it just to excercise the routine.
- I am trying to purposely get away from using ODBC as any other application could then use it as well.
- I plan to use that same connection string within the application where I have code using SQLSTRINGCONNECT().

Thanx again for your input...
Mark