Converting Remote Views to DSNLess

[stanlyn]

Hi,

I currently have a solution working that requires me to pre-build the remote views and connection. I would rather create this connection in code which I have done already. My bad experience with CursorAdapters caused me to try a remote view process just to see if it presented to me that same issues I've fought with using cursor adapters. It actually works quite well, except for having to pre-build everything. Particularly, I like the ease it offers in making sql table changes and then refactoring the VFP side, as all I have to do is
1. make the MSSQL schema changes and save,
2. open the remote view and delete its referenced sql table (in the top pane), and add it back, then add all the fields back (in the bottom pane) and check the update box,
3. then open the form's data env and delete the view and re-add it.
4. lastly, refactor the form's controls to reflect the sql changes.

This has worked everytime...

My experience working with CursorAdapters was so much more un-forgiving. I do see their power, however if I cannot make it easy to change something without blowing the CursorAdapter away and starting over on simple changes, then its a waste...

Like I've already said, I probably don't know what I'm doing and have failed to find the right white papers or articles that describes in detail how changes are made without recreating a new CursorAdapter, or getting past a cursor adapter load in the form designer. I have Doug's articles, all FoxRockX & FoxTalk articles, all of Henzenwerke VFP books, and threads I've started here in Tek-Tips, which mostly goes into the details of creating and using. Some of the Tek-Tip threads have helped a lot with its troubleshooting, however in the past, I generally reverted back to the time consuming "pass thru" commands after fighting with it. I'd like to change that here, today...

What I've gotten frustrated at is when making a small change to the underlying schema, the cursor adapters gets broken and sometimes to a point that if the form does load in the designer, the broken cursor adapter references are gone and therefore I have to recreate it. At that point, I curse it and quit...

I really don't want to use pre-defined remote views and connections. Instead I want to use a DSNLess connection with params stored in a local table and create the database connection in the program's main prg and close it when closing the app. I have lots of experience using this approach and SQL pass thru commands, however the "pass thru" stuff is really time consuming, but with full granular control.

I'd really like to learn RAD (rapid development) techniques as mentioned in CursorAdapters.

Thanks,
Stanley

 

[Olaf Doschke]

With your bad experience about cursor adapters, I see you put it away, then this doesn't seems to be the time to pick it up again.

Remote views use a connection in the dbc or a DSN you setup in ODBC manager, that's the way they work. You can't instead use them with whatever connection handle number you gott from SQLStringconnect, that's only the way cursoradapters and sqlpassthrough work.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

With your bad experience about cursor adapters, I see you put it away, then this doesn't seems to be the time to pick it up again.

I did put it away,
I would like to revisit and hopefully learn how to get past the issues that caused me to "put it away" in the past.
Can you suggest a better time than now when I should revisit this?

Your last paragraph said a lot...
1. I cannot mix pass thru with remote views... thanks you,
2. You explained how DSN and connections work with remote views... thank you,
3. Yow explained that remote views are totally different from CursorAdapters (that you love and use), and Sql PassThru (that I currently use with success)... thank you

Based on your answers, looks like my experiment with remote views is over...

Now, wonder what I need to do or learn in order to achieve near the success level that you have experienced?

Thanks,
Stanley

 

[Olaf Doschke]

I'd say you should now go forward with remote views, as you get them going. Live with the fact your DBC needs a connection, it's not much of a hurdle, is it?

You can make it the norm to create connections on the fly via CREATE CONNECTION. Also do that in a separate local DBC you also create on the fly. Just a database for views.

In a multi-user environment, it's better every user has it's own DBC for views anyway and views are things you can throw away, they are just queries, not the data.

You shouldn't decide just on one single pro or con, as you're having so much success in using remote views, why not getting over the difficulties. Even with a predefined connection, what's so bad about it? It will not need to change, just because tables change.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

views are things you can throw away, they are just queries, not the data.

How are remote views any different from CursorAdapters or passthru? I thought they too are the same, just results from queries and not the actual data. Am I wrong?

So, I could create a throwaway dbc on each iteration of the application with its remote connection and remote views created via code in the startup program, then delete it on application close.

Thanks,
Stanley

 

[Olaf Doschke]

I didn't want to stress a difference of views to cursoradapters or passthrough. But each of the other ones has another reason for not being dispensible: cursoradapters are classes, you keep them as they are rather part of code. SQL passtrough is mainly code using SQLyz functions, so very obvious there's nothing to throw away.

I'm saying you build up a local DBC, not in each step of your application. You can build it up at the start of your EXE. So that stage becomes pure code and though you have a connection object in the DBC you build, that connection is made by code, and can use meata data to build up the connection string used in CREATE CONNECTION. there you have what you want, DNSless connections based on meta data.

Oh, and what you keep about your remote views this way is the code generating them in a local throwaway DBC. There's a tool called eview, which turns existing views into code generating them, this could be very helpful to you. It was a download at universalthread before it got renamed. It's still available as zip download from the foxpro wikis -

http://fox.wikis.com/wc.dll?wiki~eView

- scroll down to the bottom.

Last not least, I'd say you're ready to pick up cursoradapter again, after you have conquered remote views.

Bye, Olaf.

 

[TamarGranor]

It sounds like one problem that you ran into each way is that you're using SELECT * rather than listing the fields of interest. That'll always call problems, no matter which approach you use.

Tamar

 

[stanlyn]

Hi Tamar,

is that you're using SELECT * rather than listing the fields of interest. That'll always call problems, no matter which approach you use

Yes, I'm using the "select *" when selecting the fields from the view's 1st page. I'm even selecting the pk field as I need to see it in a disabled reference only textbox. The pk field is NOT updateable under any circumstances, and the table will never grow past 1000 records, therefore I do not see the problems you are suggesting. All fields are needed (select *) as any one or multiple fields may be updated on an existing record, or all fields will need to be updated on a new record entry. If I knew what field was going to be updated, then sure, I'd do a "select minimal fields" for the update to keep the net traffic down and response time fast. Problem is I don't what field(s) will be updated, so I have to get them all. So please explain why it such a bad idea, and whatever else I may be missing here (which is probably a lot)...

Thanks,
Stanley

 

[Olaf Doschke]

Select * has no problem of not working in itself, but views have several properties for each field, so adding fields to a DBF renders views defined with * invalid, as field descriptions are missing. Defining a view with the concrete list of fields your view definition is stable and continues working, albeit not automatically including new fields.

That is a disadvantage of views in general, but as it is that way, you need to change views when changing your database anyway, so you better opt for the concrete list of fields. That gives you a chance an old app version still works with an extended DBF, just not setting/maintaining the new field. But the stress level of synchronizing DB version and application version is lower that way.

From what you described already you often had that view definition problem, it hasn't worked as an eye-opener for you, that this is caused by the bad habit of lazy view definitions.

Another problematic thing about both views and cursor adapters is autoinc fields or whatever same type of integer sequence fields in remote databases, eg INT IDENTITY in MSSQL or INT AUTO_INCREMENT in MySQL.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

From what you described already you often had that view definition problem, it hasn't worked as an eye-opener for you, that this is caused by the bad habit of lazy view definitions.

Actually I haven't written a single character for the views that I got working. I did it all via the designer. Later this week I hope to try the all via code approach as already mentioned. And yes in code, everything can be defined and updated when a table change occurs and all without the fear of a table or property change causing the form to load without the view, as I experienced with cursor adapters.

Thanks,
Stanley

 

[Olaf Doschke]

You misunderstand, but it will take a bit to explain. Even if you create views visually with the view designer you have a choice about picking * or table.* or all the single fields. No matter what you choose your view will be stored with specifications for each single field, but this definition breaks with changed DBF if choosing the "lazy" * or table.* and does NOT break when choosing all single fields.

Execution or use of a view executes the stored SQL, if that retrieves more fields than know from the view definition this breaks the view and causes errors. A query only querying all fields by all their names as known at view definition stays stable, unless you delete fields. Of course it also does only fetch the field list as it is and stays what it is.

In a way you might think it's clever to pick * or table.* to get all fields no matter how the DBF changes, but that's not the case. The "watch dog" of VFP runtime cares about knowing details/specifications for each single field the query it executes comes back with and can't work with the extra fields it doesn't know details of. Details of this knowledge are whterh or not to update the field. MS could have opted for defaults about such new fields, eg define them as updatable,instead they decided to alarm and error.

That means you can't be lazy about view definitions, that's not just addressing coding habits and keyboard laziness. It's about being too loose about definitions. If you didn't recognize yet, databases are very bureaucratic.

Bye, Olaf.