Wiki Home

Better Integration Of VFP With SQL Server


Namespace: DotNet
AlejandroSosa (Panama) posted a message at UT (apr 5, 2001) to Ricardo Wenger (Microsoft) suggesting a better integration (via native VFP commands) with SQL Server. Ricardo found that to be an interesting idea, and said he would keep his eyes on the thread, to see what the VFP community thinks about this subject. Well, what do we think about? -- Fernando Alvares
Yeah, well, it's just what I want/need; coincidentally I earlier today asked that question in Basic Oracle For VFP.
IMO it would be very very slow due to the overhead, but for a commercial point of view it maybe super; having a rather large app, the ONLY thing which prevents us sometimes from selling, is that it is not SQL (DBMS) based. When all is (too) slow, we just wait for faster hardware. Well I will. -- Peter Stordiau
Nothing is really "SQL-based". VFP is as "SQL-based" as java, vb, delphi, c, or any language you choose to use. You connect to SQL back-ends as needed and you make you APP SQL-based or dbf-based or jet-based, etc. depending on your and your clients' needs. - Alex Wieder
I see that as an option, that means: you have it, you can use it if you want. All we know that many constructs in VFP can lead to very poor performance, so we try to avoid them, but I think that's not the case here. The case here IMO is that some commands or command sequences might be slow performing like GOTO, SCAN ... ENDSCAN, some people at UT said (sorry, I can't remember who) that VFP is record oriented while SQL Server (and others) are set oriented, so that construct (if supported by such implementation) would have a poor performance or even could not be implemented at all. Programmers should be aware of using only those well performing commands across different data management platforms (I can see not only SLQ Server, but Oracle, Informix etc) when their programs were targeted to many of them. -- Fernando Alvares
I don't understand why we need better integration with SQL Server. The following simple class does 99% of everything I ever do in SQL Server. With simple modification and extension -- the connection could be an object for example -- this class could do anything. We don't need better integration with SQL Server because SQL Server provides an ample programming interface, and is amply interoperative. I just don't get it. -- Steven Black
x= CREATE("Q")
x.q= "Select * FROM Customer"
x.x()
BROWSE
*-- [smb] I use this class all the time for scratch work.
********************************
DEFINE CLASS Q AS CUSTOM
********************************
H= 0
C="Temp"
Q=""
FUNCTION Init
  This.H=sqlconnect("SomeSystemDSNConnectionName","SomeUser","SomePassword")
FUNCTION Destroy
  SQLDISCONNECT(This.H)
FUNCTION X
  IF This.H> 0
    SQLExec(This.H, This.Q, This.C)
  ENDIF
ENDDEFINE


WOW! If that is all you need with what you do with SQL... it sounds like you haven't done a SQL ap. For example:

1. Connecting each time you access SQL is slow and multiple connections take resources.
2. How do you update the cursor you just made?
3. If you have a parent-child tables, and you are using identity fields for PK's, were in your class does it retrieve the identity field?
4. How do you deal with the date vs datetime issue and the fact that you can't store empty dates in SQL.

I am not saying it can't be done. I am just saying, you little class doesn't do everything that needs to be done.

Also, I think the ER is to make Remote Views work better with SQL, not VFP. As you say, you can write a class that will handle most of your needs.

I didn't say this little class does everything. I said, this 9-line class (for invocation in the command-window or in scratch programs) fulfills my 99% use case for SQL Server usage from within Fox. It's not perfect but it shows what you can do with 9-lines of code. To update a table, for example:
x.q="Update Foo SET xxx=yyy WHERE bar=zzz"
x.x()

To retrieve an identity field
x.c= "curIdentity"  && Store it where you like, Temp by default.
x.q="SELECT @@IDENTITY"
x.x()

Also note that the connection is scoped to the life of the object. Maybe you just didn't try it < s >?-- Steven Black
I like Steve's approach. For a somewhat more sophisticated version of the above class (same general idea but with more bells and whistles), see www.ml-consult.co.uk/foxst-25.htm. Mike Lewis
The reason why this is getting some attention lately (as of April 2001) is that it might be a good way to position VFP from a marketing perspective. OTOH, this might also mean that we're possibly talking about dis-integrating vfp from the dbf driver and thus make it entirely data-independent, and therefore [yet another] candidate for the clr. -- Alex Wieder
I agree with Steve. We have the ability to wrap anything we want to using our own custom data classes. If you have not started using data classes even for your standard cursor/VFP data access, you're not preparing for the future, IMHO. Unfortunately, legacy apps will need to be re-engineered for this to become reality, so, in that case, maybe the argument could be made for VFP DML to go against different back-ends. However, I see this as the similar thing as just compiling a FPDos app in VFP just so you can say your app is running in VFP. Or, relying on the "conversion" wizards. What does this gain you long term? -- Randy Jean

[2001.04.09 07:15:26 PM EST]
Ha ! here the discussion starts; Maybe I agree with you, and maybe not. Anyway, it may IMO a little too simple to state it like this. Knowing or feeling that in fact you are referring to my "What we have ..." below, there are some fellows amongst us - and having written apps which just have their value - who think differently and have another point of view. Of course, going by the book, you are right, but who ever started developing this large large app in OOP environment ? Now please note that I respect all of your standpoints, because you just have them, disagreeing or not. Ok ? but when we have this legacy app, developed in over 100,000 hrs ... please, it is too simple.

Where I may sound offended, I am just not, and in stead of that trying to figure how things in this world should be done. Having this 100,000+ hrs investment, you don't throw this away just because in the mean time some -indeed- nice OOP was invented. What I want to make clear is : there are other ways.
Looking at all the LARGE packages existing in the world (approx. 100) -if I'm well informed- only two "converted" to OOP, and all the others did nothing. Just an impossible job. Thus, I'm not saying it is impossible, I'm only "prooving" this by the understanding that just nobody did, where everybody knows it should be done. Believe me.
So here are these two who re-engineered (and I'm one of them), being a tough job, for me resulting in noting less than an "OS", by you implied as an FPDos app compiled in VFP. Again, no hard feelings, but please try to understand these persons (companies) who really need stuff like this (different back-ends), having the rest really under control. IMO just saying "we don't need this", is from the point of view that one just starts developing, or is able to start all over again. But mind you, there are other worlds !

When you (disagreeing persons) may think that a re-engineered app won't lead to any real OOP-app, I personally would agree 100 % ,"not knowing". In fact, anyone who claims this had been done, I won't believe... Not because I won't believe from a theoretical point of view, but because I was there. However, having this 100,000 hrs app, you have about 99,000 hrs to go, and now think of how creative you can be in this time. So out of 100, 2 decided to go this route, and for sure for us this worked out fine. But yes, now about the back-end ...

For any further interest :
We are already capable of replicating our database to any (SQL) DBMS, if it is for writing. So this is for our customers having (for instance) Oracle as the legacy DBMS. But of course it's fake, because all reading is done from the stupid DBF's, not matter how fast is is. So please let us offer some real back-end to the customers, even if we are "playing" with our VFP.
I hope to 've put the message through (call me stupid if you like).
-- Peter Stordiau
[Ref #1]One thing I would like to have is the ability to access Oracle, SQL Server, Informix etc using VFP's native DML syntax thru the use of (maybe 3rd party supplied) add-on "driver DLLs" that could do the "durty job" between the program itself and the access routines of the DB - much like ODBC, SPT etc already do (even the DBC/DBF/... access could be a component DLL, just to make it generic). I don't like the idea of a better integration to SQL Server only. MS could implement a way to have this ability in other DM platforms too (Delphy guys told me something about BDE, is that the case?). This IMO would easy the access to other DBS to VFP programmers and make simple writing programs to them. I think this was the idea AlejandroSosa suggested. -- Fernando Alvares
What we have, is just the app as a fact, full of Seek's, Skip's, Go Top's etc.;
where it is theoretically possible to convert our programs to use the SQL-commands in stead of Seek etc., in practice this would already not be possible due to the number of source-lines wich will be added (but all can be done in small functions -> making it even more slow), but we also would have to deal with the result-sets. Thus, the logic of the programs will change too much in order to automatically convert things (manually no way, app is far to large).
Now mind you, the larger customers do have their standards for DBMS, and though your (to be sold) app is completely new to them, it should fit into their standard. This indeed leads to any SQL-DBMS and for sure not SQLServer only.
Please think of the possibilities Citrix brought us, where before PC-based apps like VFP would lose from Unix etc.; not anymore now. So, DBMS-independency is as strategical important, but once your app was not setup like that ... -- Peter Stordiau
Better integration in terms of what? The only thing that comes to mind might be taking advantage of some of VFP's UI tools, for example in writing stored procedures. VFP's debugger and Intellisense would be pretty handy. -- Dan LeClair
This reminds me of replaceable data drivers in Clipper. Where is that legacy tool at now? I'm not saying it wouldn't be useful. It could really help in some large legacy app migrations. My fear is that we then have a watered down development tool that will continue to be categorized as "legacy" itself. -- Randy Jean

Isn't ODBC a replaceable data driver in some sense, that you have to connect to it? -- Fernando Alvares

Yes, but it's not part of the development tool either. Adding more native data access abilities to the development tool goes back to monolithic system design which I am trying to move away from "from a marketing perspective". For instance, I have adapted my data classes to work with a socket DLL and and HP3000 backend. The programmatic interface of this data class is almost identical in every way to how I work with views and cursors. I need data, I call oCursor.Requery(). I need to save, I call oCursor.Save(). No difference.

I fully agree with you in that, no doubt. I don't want it to be part of the development tool, and I don't like the monolithic system design idea, too. Perhaps I couldn't make me understand well. What I really whould like to have was an open DMS access architecture in VFP (even to access the DBF), but using native VFP DML syntax (not exclusivelly, but keeping the other ways too, of course). Please check [Ref #1] above, to see that I'm not willing better integration with SQL Server only, I would like to have better integration with all of them.

This feature could be an option: if you like it you can use it, if you don't like it use the other options. There are things in VFP that I don't like and that I don't use, but I see many folks using it, and that's ok for me. The so called integration (I'd rather call it DMS independency throught native DML access) could be implemented in a way not to burden VFP so, as many features/options we have, the better, IMO -- Fernando Alvares
From the sound of your statements above, it appears that you have not explored Remote Views in VFP. You define a remote view against a back-end database via ODBC. From then on, you treat the remote view as a VFP table. USE...Browse...EDIT...SET FILTER...SCAN...REPLACE...etc. Voila! You are using back-end database data with vfp DML syntax. And it works with any ODBC compliant data source. Oracle, SQL SERVER, Informix, etc.
Ray Kirk
The VFP7 ConnString clause for USE means you don't even need DSNs to connect RVs to remote backends. This means you can include RVs in a "views only" dbc in your exe. This removes 2 of the most persistent criticisms of RVs- scalability and security. -- John Ryan
Category Questions Category VFP Future Category VFP Marketing Category Data
( Topic last updated: 2006.01.22 12:57:46 PM )