SQL Server data processing with VFP 8

Simple reason...

After the first query you have a bunch of records in the VFP cursor detailku.
Your second query has the where clause "where norekening = ?detailku.norekening". That will only put in the value of detailku.norekening in the first row of detailku.

SQL Server has no access to the VFP cursors. Even if it would, you don't join detailku in your second query, so you only get the data for one detailku.norekening value.

Why are you getting all the cursors, if you finally want the result of your VFP query, do that on the level of the SQL Server with one query in SQL Server and you'd have your final result in one go.

in second query i use this :
it is give me massage box that need to enter value for debiturku.cif (view parameter)..

Chris Miller said:

Simple reason...

After the first query you have a bunch of records in the VFP cursor detailku.
Your second query has the where clause "where norekening = ?detailku.norekening". That will only put in the value of detailku.norekening in the first row of detailku.

SQL Server has no access to the VFP cursors. Even if it would, you don't join detailku in your second query, so you only get the data for one detailku.norekening value.

Why are you getting all the cursors, if you finally want the result of your VFP query, do that on the level of the SQL Server with one query in SQL Server and you'd have your final result in one go.

Click to expand...

well i try this code :
it is give me the record, while i create join with dbo.jaminan. i got massage that "command contains unrecognized phrase / keyword."
can you tell me which one missing on my code..

You're trying to ride a dead horse. No matter how you shift it around, a parameter within SQLExecs cSQLCommand parameter is only a single value, it can't be a whole table/workarea/cursor nor a whole column of a table, it's always a single value.

Your idea is not working.

Instead of getting several cursors you finally join within VFP, do the SQL that's necessary overall to get the final result within SQL Server.

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:
"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.

To add to the sql script example:

Not only could 'C' be a parameter, you could use declaration of SQL variables, in MS SQL Server done with DECLARE @varname as datatype, which includes using table valued variables - kind of temp tables or cursors, but within variable space, not in the temp database. Which, on the other side, you can also use. You can do a lot of things, it just requires to dive deeper into the SQL language of the Server, like T-SQL is for MSSQL. It pays to do that.

Another even simpler example for the sql script batch that's less theoretical:

Here, due to the flatter hierarchy, you don't need Select orderitems.* from orders left join orderitems on orderitems.orderid=orders.id where orders.orderid=?orderid. As the orderitems table has the orderid itself, so you don't need to join up to orders as was necessary for a where clause like driveletter='C' which is a condition you can only test when reading the drive record.

The more important point is now: Altough that's the major subset of the data of an order, for a form to modify an order you likely don't just want to be able to delete order items or change their quantity, you might want to add an orderitem or change one to another specific product, but for that you also need the full products table. No problem to add in a SELECT * FROM products, but initially you only need

That would only give you the products within that one order, not any other.

Now it's up to you. But in the sense of not fetching all data of any table ever, except perhaps for very short tables, you should change the design of an application that previously simply USE Products for that feature and have a view or query that can fetch products starting with (at minimum) the first 3 letters of the product name or something like that. The product choice may also be changed to a two step process of first selecting a product category and then only fetch products of that category. The major idea is still the same: Have queries with a high selectivity. That means the opposite of what it sounds like, high selectivity means a simple filter condition already is very selective and only addresses a small subset of the data. It always boild down to keep the amount of data managable, that goes through the network bottleneck. An orderid is such a thing, as each order - even a big order with 100s of items - is a small subset of all orders and orderitems in the database.

In a web application the bottleneck differs to that of a desktop application, by the way, as a web application usually will have data local to the web server or at least in a network neighborhood with high bandwidth to it, while what's transferred to the end user interface is only a small subset of that. You can still do that wrong and have a HTML list of products with all products, that also blows up the usage of the small bottleneck between web server and client browser.