I don't know what you actually need sepcifically to your database, but as generalization, you might adapt a FoxPro application that was DBF based to SQL Server and are used to not just having a joined resultset, but the two, three or more tables with their separate records, i.e. not all order data joined into one result but 1 order record in one order cursor and all orderitems in an orderitems cursor. That to not talk about the situation you would usually have in a dbf based solution of simply opening orders in one workarea and orderitems in another, meaning having access to all orders and all orderitems of all orders, no filtering at all.
The last and very usual VFP scenario doesn't work well, it doesn't scale well at all, when using an MS SQL (or other server) backend, just doing SELECT * FROM SERVERTABLE instead of USING DBFTABLE will give you access to all records, too, but selecting all data from a server actually copies them over, USEing a DBF does not load all it's data, you can browse it and scroll down to the end and read all data that way, but the USE itself just positions you on record 1 and only perhaps for a caching strategy, VFP or the filesystem will fetch more than that first one record. Surely it will never fetch all data, though. That's also a misconception some Fox developers have. But take that aside, unimportant. As that does not scale well, it will just take a lot of time to read in a lot of data you don't need at all.
An adaption to a server backend will always require you to rethink data access and not just hammer it into a way all VFP code can continue to work as is, once the same workarea names are populated. When you work on an order, you only need the one order record and only the orderitems that elong to that order. You don't need the zillion other records you may have. So don't fetch them.
Now comes the advice on how to get there, even in one SQLEXEC. You cannot only execute a single query, when you have multiple queries in the cSQLCommand parameter, you get multiple results either in one go or with the help of SQLMORERESULTS() - look at it in the VFP help.
Here's a short breakdown. Depending on whether you SQL connection works in batchmode or not, you will be able to get all in one SQLEXEC or get one of the results with SQLMORERESULTS.
To give an example, assume a database with three tables "drives" (hard disk drives), "directories" and "files". Then this SQL Script will query these tables separately instead of joining them into one result:
Code:
h = SQLConnect("mssql") && however you connect. Here it's done by DSN.
Text To cSQL noshow
Select drives.* from drives
where driveletter='C'
Select directories.* from drives
left join directories on directories.driveid = drives.id
where driveletter='C'
Select files.* from drives
left join directories on directories.driveid = drives.id
left join files on files.directoryid = directories.id
where driveletter='C'
EndText
* in batch mode an sql batch with multiple results fetches all of them
SQLSetProp(h,"BatchMode",.T.)
nResult = SQLExec(h,cSQL,"overalldata",aDriveCursors)
* In this case you will get cursors "overalldata" (drives), "overalldata1" (directoriess) and "overalldata2" (files)
* Turning batch mode off, VFP will only fetch the first result of an sql batch with multiple results
SQLSetProp(h,"BatchMode",.F.)
* So now you need the SQLExec and two further SQLMoreResults calls:
nResult = SQLExec(h,cSQL,"drive",aDriveCursors)
nResult = SQLMoreResults(h,"directories")
nResult = SQLMoreResults(h,"files")
* one advantage of this is that you have control about the three cursor names.
Do While nResult<2
nResult = SQLMoreResults(h,"dummy")
EndDo
"Wait", you'll say, "didn't you say no joins?"
Look at the selected columns, the first query only selects drives columns (drives.*) the second only directories columns (directories.*) and the third only files columns (files.*), the joins are necessary to be able to use the general where clause
where driveletter='C'.
And the while loop at the end ensures VFP will know the batch terminated. While you know, in this case, that there are always exactly 3 results in the SQL script batch, there's no point fur further SQLMoreResults, VFP does not know. For VFP the batch only finishes when SQLMoreResults returns 2, and that will only be the case, when you ask it for one further result. It's important VFP realizes that, because you can only make the next SQLExec call, otherwise you get the error message "Invalid call issued while executing a SQLMORERESULTS() sequence."
So that's the downside of SQLMoreResults, the end of the batch does (not necessarily) come back when you fetch the last result, but when you ask for a further result when there isn't one more.
Anyway you do it (in Batchmode=.T. or .F.) - it's an example of how to get at the subsets of interest, which is very generally what you need to do when changing from a DBF backend to a server backend.
Remark: I choose a sample that's intuitively understoof of hdd drives with their directories and files. The structure obvóusly has a drives table as root/head, then a directories table and finally files (drives->directories->files).
All files of drive C:\ could be too much data to fetch, that's not making this a good example of reducing the data amount to something managable, but this was just to illustrate how to get multiple sql results either in one go in batchmode or by using SQLMoreResults. And for that I considered the intuitive understanding is more important than how realistic it is. I intentionally gave no actual data schema and data for this. This is not for copy&paste execution, this is for understanding the principles and then adapting it to whatever concrete data subset you'd be interested in from your specific database.
