SQL Server data processing with VFP 8 1

[bharons]

good morning experts...
Please correct my code below, because only one line of data appears:

m.tanggal = thisform.txtTanggal.value
lnResult = SQLExec(Thisform.nHandle, "select top (20) * from dbo.detail where tanggal = ?m.tanggal", "detailku")
lnResult = SQLExec(Thisform.nHandle, "select top (20) * from dbo.debitur where norekening = ?detailku.norekening", "debiturku")
lnResult = SQLExec(Thisform.nHandle, "select top (20) * from dbo.nasabah where cif = ?debiturku.cif", "nasabahku")
lnResult = SQLExec(Thisform.nHandle, "select top (20) * from dbo.jaminan where norekening = ?detailku.norekening", "jaminanku")

select    debiturku.norekening,detailku.tanggal,nasabahku.nama,jaminanku.keterangan ;
from     detailku ;
join     debiturku on detailku.norekening = debiturku.norekening ;
join     nasabahku on debiturku.cif = nasabahku.cif ;
join     jaminanku on detailku.norekening = jaminanku.norekening ;
where     detailku.sandi = 3 group by 1,2,3,4 order by 1 ;
into cursor templunas readwrite

and if I browse detailku it shows a lot of data as shown in the image below

 

[Chris Miller]

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.

 

[bharons]

in second query i use this :

lnResult = SQLExec(Thisform.nHandle, "select top (20) * from dbo.debitur join detailku on norekening = detailku.norekening", "debiturku")

it is give me massage box that need to enter value for debiturku.cif (view parameter)..

 

[bharons]

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.

well i try this code :

lnResult =  SQLExec(Thisform.nHandle, "select top (20) detail.norekening, detail.tanggal, jaminan.keterangan from dbo.detail join dbo.debitur on detail.norekening = debitur.norekening join dbo.jaminan on detail.norekening = jaminan.norekening where detail.tanggal = ?m.tanggal", "debiturku")

it is give me the record, while i create join with dbo.jaminan. i got massage that "command contains unrecognized phrase / keyword."

lnResult =  SQLExec(Thisform.nHandle, "select top (20) detail.norekening, nasabah.nama, detail.tanggal, jaminan.keterangan from dbo.detail join dbo.debitur on detail.norekening = debitur.norekening join dbo.nasabah on debitur.cif = nasabah.cif join dbo.jaminan on detail.norekening = jaminan.norekening where detail.tanggal = ?m.tanggal", "debiturku")

can you tell me which one missing on my code..

 

[Chris Miller]

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.

 

[Chris Miller]

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:

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.

 

[Chris Miller]

To add to the sql script example:

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'

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.

 

[Chris Miller]

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

Select * From orders where id=?orderid;

Select * From orderitems where orderid=?orderid;

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

Select products.* from orderitems left join products on orderitems.productid = products.id
where orderitems.orderid = ?orderid

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.

 

[bharons]

dear Chriss my great lecturer..
this is my sampling sql server database in format excel..

I use the coding below, to produce data that has the maximum value based on the criteria of the norekening column, the selisih column (number of days)..
The question is whether the coding I used is wrong...

TEXT TO m.lcSQL noshow
select n.cif, d.norekening, n.nama, d.nomor,
d4.tanggal, d4.ke, d4.tanggal_lunas, d4.ke_lunas, d4.jenis,
d4.agunan, d4.kunci, d4.sandi, d4.selisih
from dbo.debitur d inner join
(select d3.norekening, d3.tanggal, d3.ke, j1.tanggal_lunas,
j1.ke_lunas, j1.jenis, j1.agunan, j1.kunci, j1.sandi,
datediff(day,tgl_jt, tanggal) as selisih
from dbo.detail d3 inner join
(select j.norekening, d2.tanggal as tanggal_lunas,
d2.ke as ke_lunas, j.jenis, j.keterangan as agunan,
iif(j.jenis = 1, j.nomesin, j.shm_no) as kunci, d2.sandi
from dbo.jaminan j inner join
(select norekening, tanggal, ke, sandi
from dbo.detail where tanggal = ?m.tanggal and sandi = 3) d2
on j.norekening = d2.norekening) j1
on j1.norekening = d3.norekening) d4
on d.norekening = d4.norekening inner join dbo.nasabah n
on d.cif = n.cif
group by n.cif, d.norekening, n.nama, d.nomor,
d4.tanggal, d4.ke, d4.tanggal_lunas, d4.ke_lunas, d4.jenis,
d4.agunan, d4.kunci, d4.sandi, d4.selisih
having max(d4.selisih) = d4.selisih
ENDTEXT

m.tanggal = thisform.txtTanggal.value
lnResult = SQLExec(Thisform.nHandle, m.lcSQL, "templunas")   
    
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
select templunas
browse
endif

Thank you before and after for your understanding, attention and enlightenment..