How to modify tables when remote views used?

[opticalman]

I am using FVP8 as front end
SQL Express 2005 is back end.
I have about 60 remote views.

It is time to modify the Tables via SQL Express. In the past, if I change the tables, the remote views will no longer work, nor can I access the views to modify them via the IDE.

Any suggestions on a good way to approach this?

One idea I am considering... I used GENDBC to make a progam to make the dbc. If I modify the backend tables, then run the "make dbc program" from GENDBC, should it work??

Or is it time to break free from remote views???

 

[Olaf Doschke]

It would be quite the same effort, if you had stored procs or views in the remote database or code using spt, changing tables means changing sql commands/code in general.

That's why you typically don't do major changes to the database scheme in general. The problem does not get smaller when moving away from views/remote views.

Even with DBC/DBF local views and table changes you need some tool like SDT to let table alterations be reflected in view definition changes. And that also has it's limitations.

Bye, Olaf.

 

[opticalman]

Olaf said,"you need some tool like SDT "

Is that Stonefield Data Tool???

 

[TamarGranor]

One thing you can do this time to reduce the trouble the next time is make sure your views don't use SELECT *. That's one of the main reasons views break--when you change the list of fields in the table, they don't match up right any more.

Tamar

 

[Olaf Doschke]

SDT means Stonefield Database Toolkit, right. But it won't help for remote data, it only can update local views to foxpro data when foxpro databases change, that have SDT dbc event code propagating that table alterations to views. I only mentioned that as an example, that in princpile such automatic changes are possible. But it's limited.

Besides that I think Tamar is right in that remote views have the same problem as local views when using *, because what applies to local views also applies to remote views: A new field already breaks the view, as it does not only have the SELECT * FROM ... sql code in it, but properties for each field that existed while the view was created - eg the field type that should be used on the foxpro side.

If * now selects more fields there are no such properties for the new fields in the view definition and therefore the view fails.

If defining the view with the full list of fields instead of * the view does not break, but it continues to only return these fields and the new field needs to be added. So this also just helps half the way. The view and the app does not break, but it does not support the new field either.

As you want the new field for some purpose in your application and not just in the database in most cases, you need to change the view anyway. We're back to my statement from above: Changing the databse means changing the app and code anyway. There is no general way to do that automatic, the more complex the database change is, the less correct will automatic view changes be. Even the renaming of an existing field can already be a

You've hit the topic of side effects, changes to one component effecting other objects. When working with a remote database, even if that offers triggers or events happening, that could propagate alterations of field names, and new fields to existing code working on that field, how should that be done, as application code can be in any language an typically even exist offline from the database server, especally if it's not inhouse development.

You would need an IDE to know all the dependencies to sync changes to all other components that could be effected. That's also a reason SDT can't offer the same kind of updates for remote views.

Bye, Olaf.

 

[opticalman]

Thank you Olaf and Tamar

I now have a better understanding of WHY the past mods have been such a pain.

I now understand the pitfalls of "SELECT *" in views. I think I can handle the field additions and modifications. I just hate it when I can no longer open the view for modification from the IDE and have to rebuild the view from scratch.

I think I know what to do

I consider my question answered..... but will gladly accept any other suggestions.

 

[Olaf Doschke]

you should always be able to open a view for modification. There'll be a prompt, but sill after OK you get to the view designer and can change from * or table.* to all fields.

Bye, Olaf.

 

[opticalman]

Olaf

Your comment made me investigate further.

I may have been working under the wrong assupmtions. I would have declared that changes to the back end tables made it impossible for me to access the remote view.

But I was able to increase a field length and use the remote view editor. Then I added a field. I recieved an error message that the fields did not match and key fields were reset to default, but I was able to acces the view editor, just as you said.

The main changes I want to make is to increase field size. It may not be as bad as I thought. I should be able to make the mods in an afternoon.

I will add, somewhere in these "add a field" trials, I was able to crash my system due to "fields do not match", so I will tread with caution. All is OK right now.

Thanks to all. I am satisfied.