SQL Select & Inserts on Only DBFs

[stanlyn]

Hi,

Using VFP9sp2x I'm replacing my xbase commands with sql like commands and have read that some of this is not as straight forward as we would like to see. I'm doing this using the SQL commands built into VFP9sp2 and only on dbf local tables. There is NO SQL database in this mix. There is confusion with what works and what doesn't when using vfp's sql, mssql, mysql, and/or mixing them, so I'll give each question a number and listen to your advice...

Once again, NO SQL databases here, only VFP databases and tables...

Using VFP9sp2...
1. Combining insert and union commands within a select statement when result cursors are used. - All references I've read says it DOES NOT WORK. It looks as if the vfp sql statement can create the cursor, it should be able to use in it unions and inserts, right????

2. Vfp cursors cannot be referenced by the builtin VFP SQL engine. I can see why real SQL engines would have a problem with referencing the VfP cursor as they are totally local with no outside references. Is that still true with vfp's builtin sql engine?

3. What else can or cannot be done with vfp's sql engine in the vfp environment that cannot be done in a mssql or mysql environment?

Any thoughts and explanations greatly appreciated...
Stanley

 

[Olaf Doschke]

One more answer:

>The whole nature of my questions is regarding this "SQL inside VFP towards DBFs" scenario.
I understand that and you pointed it out very clearly.

But a first you were including the server topic by your Question No. 3 and also I was referring to you saying "I'm replacing my xbase commands with sql like commands"

While you do that with VFP SQL towards DBFs, the goal of such a step must be to be ready to move to a server backend, and the warning still is, to not overdo this. Parts of your code are not about the interface of your application logic or user interface towards the database, but are internal. If you convert these parts from xBase to sql and later move to another UI or programming language for application logic on top of moving to mysql or mssql you'd redo this too, as VFP is the only language capable of doing SQL as part of the native language in all of the main OOP layers from database stored procs to application business logic to user interface logic.

So, while you pointed out you only wanted to know about SQL capabilities of VFP you questioned directly AND indirectly about SQL Server and therefore got that much verbiage about these, too. Also you should not only get your answer, if I see you making steps towards servers, you should make the right steps at the appropriate parts of your application, not blindly move away from xBase to sql in all places, even inside the VFP/DBF world.

There is this simple saying nothing is faster finding some record in an index as SEEK. Even doing a fully rushmore optimizable query the rushmore algorithm first has to identify which index to use before seeking. But - and this is a big but - rushmore can make use of many indexes and does many things automatically. Of course in xBase code you can also switch indexes, uses one index per table at the same time, etc. But in the end your development time is longer and you have to think on a much finer detail level than on the sql query level. Also the simple query about one table is not the one you need to fear of getting too slow with sql, and in more complex queries it's superior to xbase relations and scans and seeks with all the program logic one has to understand. From that perspective you may move from any xBase code towards SQL even inside a VFP only application, but then why redo stuff you will need to redo again? The only reason would be, you want better performance than what you have now, but not just for sake of doing something with a single programming paradigm.

We may revisit question 3, if you perhaps rephrase your question to focus on what you really want to know. In general T-SQL or MySQL has a richer SQL language, VFP makes up for this in that you can embed it's SQL inside a fully blown programming language, which does not only include variables, control flow (if, loops or such) and functions, but even OOP. SQL Server allows to switch on COM usage or .NET language inside stored procs or functions, but you need administrative privileges to do so and there are security concerns, if you do so. The same goes for using OS functions. SQL Server is a goal for hackers, DBFs are not.

So, that added some other thoughts on your third question. Finally about the performance: My experience was MS SQL Server speeded things up. That may just be me, my environment is 500 users on the backend, not a single user application, so that experience mostly comes from that prerequisite, there are dedicated solutions for small user numbers in SQL Server, too. Not just the express version, which was binary the same os standard or enterprise, but LocalDB. That may be something to look out for, if your situation rather is a single user desktop application.

Then on the other side of the scale in massivle multiplayer - um - user scenarios there is multi core usage, server clusters and replication which DBFs are not capable without add on development of data synching with many sites. If you then move data into the internet, be it a classical single server or the cloud, you don't put DBFs there. Even if you do, you can't USE them anymore. All your concepts in your VFP code have to change to a decoupled service, web service, soap or REST api.

Code DBF/VFP only usage is good for single user desktop applications only, if you ask me.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

Thank you so much for hanging in there with me. It DOES make sense, and I do get what you have explained. One of the problems I was struggling with was this seperation of vfp and mssql. A lot of what you said I already knew (in fragments), as I had never mentally processed the whole trip from end to end (vfp-odb-sql). Thanks for that understanding and your addons above!

Thanks again,
Stanley

 

[Olaf Doschke]

Many of the things I said are just conclusions, which you can deduct from working with a few languages using eg the same driver. Some is pure logic if you just think it through. And in some aspects I also don't care for the whole truth, you wouldn't be able to develop something if you first deconstruct what you're using to the atomic level, but it helps to be curious. And it helps to gain basic levels in other related or unrelated stuff, not only the technical stuff. That also applies to soft skills, where I still have to learn a lot more.

Bye, Olaf.