Change Connection for a View

[hjohnson]

Is there anyway to temporarily change the Connection of a view? The problem that I have is that there are 2 exact SQL-Server databases that we have to send data to. Depending on certain criteria, data either goes to one or the other. Rather than creating multiple views, I was hopeing that I could create 2 connections and someone change the connection the view is using before opening it so the data would be routed appropriately.

Any suggestions?

Thanks
Hal

 

[Mike Lewis]

Hal,

No, you can't change the connection object within the view, but you can change the database name within the connection object.

Assuming your connection object resides in a DBC:

OPEN DATABASE Whatever && DBC hosting the connection and view
DBSETPROP("MyConnection","Connection","Database","RemoteDatabaseName")

You would have to close the connection and re-open it, then re-open the view, in order for this to work. Also, I assume that the two databases are on the same server and have the same login details.

Hope this helps.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[hjohnson]

Thanks,

Your assumption is correct, the connection object does reside in the DBC.

For now, the two databases are on the same server and have the same login detail- but I don't control over that, so it's possible that it may change.

One question, would this affect other users who my be using the DBC/Connection/View information or is that just changed for the particular instance?

Thanks
Hal

 

[Olaf Doschke]

Hi,

a DBSETPROP changes a value within the DBC, so that will change it for everyone using the same DBC. For this and other reasons (stability of tables) I'd recommend using a seperate View-DBC on every client. Create and/or validate it with every start of the application. I don't just talk about validate database but some kind of checksum test to see if it's manipulated (or got corrupt).

Well, the simplest would be to throw an existing DBC away and recreate it from scratch. This could be done by copying a DBC you included and compiled into the EXE.

Somebody has shown a short while ago, how you could do that.

Bye, Olaf.

 

[Mike Lewis]

Hal,

would this affect other users who my be using the DBC/Connection/View information or is that just changed for the particular instance?

I see that Olaf has already answered that point. As he said, DBSETPROP() affects the information stored in the physical DBC on disk.

You can use SQLSETPROP() to change the properties of an active connection in memory. To do that, first use CURSORGETPROP() to get the connection handle for the view (which must be open at the time). Then pass that handle to SQLSETPROP(). You use SQLSETPROP() to change the data source rather than the database (the data source is the ODBC DSN), which means that you can use this technique to switch to databases with different login requirements or even on different back ends.

I hope this all makes sense.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[tatin]

Mike Lewis,

Could that not be achieved creating a connection with the SQLSTRINGCONNECT() each time a set of records is required or an operation has to be performed on the server? I am contemplating using this method as I am moving to MySql.

 

[hjohnson]

The solution that I came up with is to use a "template" DBC. When users start the app, this template dbc is copied to a local folder, then customizing some of the settings used in apps, such as connections or views. This lets setting to be adjust without affecting all users.

Hal

 

[Mike Lewis]

Tatin,

Could that not be achieved creating a connection with the SQLSTRINGCONNECT() each time a set of records is required or an operation has to be performed on the server?

Yes, that sounds feasible. However, you don't want to constantly call SQLSTRINCONNECT() every time you send a command to the server. That would impose a high overhead. Call it once and keep the connection open until you ned to refer to a different database.

Hal,

The solution that I came up with is to use a "template" DBC.

That also sounds like a good approach.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[tatin]

But Mike,

I am using forms with Private Datasessions and have been having each instance to open and close its own server connection, would you consider that to be too much overhead? Just asking, I am new to this.

 

[Mike Lewis]

Tatin,

I am using forms with Private Datasessions and have been having each instance to open and close its own server connection, would you consider that to be too much overhead?

No, that wouldn't be too high an overhead, but it's not really necessary. The fact that each form has its own data session doesn't affect remote connections. They can all easily use the same connection handle, provided it is in scope for all the forms (for example, if it is held in a public variable).

That said, you might prefer to leave things as they are, since that would tend to make each form more self-contained.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)