Fox Pro remote views with new connection

[SunnyFara]

I have a database with remote views. The remote views use ODBC to connect to my SQL db. (The name of the odbc is stored in the view property, "CONNECTNAME", which is read-only.)
I made a copy of the VFPdb (and the SQL db) in another location as another dept in my company wants a clone of this application.

Now I need all the remote views to point to a different sql server, but "CONNECTNAME" is read-only so I can't change it to point to the new server. Any suggestions on how to change it? Or does anyone know how to script those remote views so that I can recreate them easily?
Thank you.

 

[JRB-Bldr]

I am not sure if you have the VFPDB Connection names the same as the MySQLDB Connection names.
If so, the challenges to keep things differentiated might be greater.

Regardless, in general things are set up like this...

* Remote Views are built to use a DBC Connection Name
* DBC Connection Names are defined to use one of the Windows ODBC DSN
* ODBC DSN uses a remote server/database referenced by IP or Server Name

So one option is to merely change the 'pointer' that the Windows ODBC DSN uses to now point to another source location.

Or change the DSN associated with the DBC Connection Name

Or create a new DBC Connection name and point it to the new source DSN and change the desired Remote View's connection parameter by re-building the Views.

Worst case (not particularly recommended) if you had a LOT of view connections to change, you could edit the DBC's table
USE MyDBC.dbc
BROWSE
to change the Connection name for the specific remote views

Good Luck,
JRB-Bldr

 

[Mike Lewis]

I would just create a new DSN for those clients who want to read the "other" database. Assuming no client needs access to both databases, the new DSN can have the same name as the old, and nothing else will need changing.

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndyKr]

Just change teh DSN which your remote views are using - you don't need to change anything else, since presumably the data structures and names are the same in the new server as onl the old.

----
Andy Kramek
Visual FoxPro MVP

 

[SunnyFara]

The DSn change would work, except that several people need access to both databases, so I need to give them different names.

 

[SunnyFara]

For the suggestion to rebuild the views to change the remote connection, I thought of that, but I have about 200 views with all different update properties. Do you know of any way to script the existing views?

 

[SunnyFara]

Also, I tried editing the property field on the database dbc table directly, as suggested by jrbbldr, but then when I try to open the view it says "property list corrupted."

I really appreciate everyone's help.

 

[JRB-Bldr]

Like I suggested and others have agreed, the easiest way would be to change the DSN's so that it would affect all associated DBC Connections and thereby all associated Remote Views.

I just now made the following test....
1. I first created a new DSN which pointed where I wanted it to go.
2. I did a MODIFY DATABASE on an existing DBC with various Remote Views
3. I created a new Connection in the DBC which used the New DSN.
4. I closed the database.
5. I then opened the DBC as a table and verified that the new Connection existed as a table entry.
6. I then went to the record defining one of the Views
7. I went into the Properties Memo field, and manually replaced the old Connection name with the New Connection name.
8. I closed everything up.
9. I then did an OPEN DATABASE MyDBC
10. USE <RemoteViewName>

The existing remote view opened just fine using the new Connection which was utilizing the new ODBC DSN which was pointing to the new data source.

Everything worked fine.

Will this work for multiple Views by using a

REPLACE MyDBC.Property with ;
  STRTRAN(Property,"OldConnName","NewConnName");
  FOR ALLTRIM(Objecttype) = "View" ;
  AND ALLTRIM(Objectname) $ "view1,view2,view3)

I'm not sure.
I'd guess yes, but try it on a test DBC.

Try it.

Good Luck,
JRB-Bldr

 

[SunnyFara]

Thanks so much for the ideas, but unfortunately, none of these actually work. It still says that the field is corrupted.

Looks like I will need to recreate every single view. Unbelievable.
Does anyone know how to script Foxpro views?
Thank you.

 

[Mike Lewis]

Does anyone know how to script Foxpro views?

You can create views programmatically using CREATE SQL VIEW.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SunnyFara]

Thanks, I know about CREATE SQL VIEW.

What I am trying to do is, I have about 150 views in a VFP database. I need to recreate the database. How can I generate a script of all the existing views so that I don't have to write out all the code or manaully create them one by one?

 

[TamarGranor]

Check out GENDBC.PRG in the Tools\GenDBC directory.

Tamar

 

[Mike Lewis]

How can I generate a script of all the existing views so that I don't have to write out all the code or manaully create them one by one?

What I said still holds.

You could write a program that loops through all the views in the database (using perhaps ADBOBJECTS()). For each view, extract the SQL and any other relevant properties (using DBGETPROP()). Then use CREATE SQL VIEW and DBSETPROP() to create a new view, incorporating whatever property changes you need.

Should be completely straightforward.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SunnyFara]

Tamar,
THANK YOU SO MUCH!

GENDBC.PRG was exactly what I was looking for.

I really appreciate your help. You have saved me a ton of work.

Have a good one.