Table Processing

[ongoma]

Hi Friends

(VFP6 Win98)

If I wished to process a table using fox's native way of processing, I would do something like:

Select stocks
go top

Do While !Eof() 
   Replace qty with qty + 3
   Skip
EndDo

How will I accomplish this if am using Sql pass through
against say Access DB/MSSQL etc. How do I move from record 1 to next processing until all records are done.

Something like this:

= SQLEXEC(nConnection,"Select * from stocks","stocks")

Select Stocks
.. blah

.. is not advisable in cases where records may be many in the table.

Please help

 

[bborissov]

TEXT TO lcUpdate NOSHOW
     UPDATE Stocks SET Qty = Qty + 3
ENDTEXT
IF SQLEXEC(nConnection,lcUpdate) < 0
   AERROR(laError)
   MessageBox([Update is not successful ] + laError[1,2])
ENDIF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Ongoma,

Borislav has given you the code. Let me give you a word of explanation.

In most back end databases, there is no concept of a record number, and no equivalent to VFP's SCAN loop (or DO WHILE, as per your code). In general, you have to look for a way of achieving your goals using pure SQL.

In this case, it's quite simple. The UPDATE command is equivalent to VFP's REPLACE. Other cases are more complex, but they are usually do-able.

By the way, in your VFP version, you can greatly simplify your code. Instead of this:

Select stocks
go top

Do While !Eof()
   Replace qty with qty + 3
   Skip
EndDo

you could have done this:

SELECT Stocks
REPLACE ALL Qty WITH Qty + 3

I know that's not directly related to your query, but I thought it might be useful to know it.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ongoma]

Mike

Thanks alot. May be my example was quite simplistic.
This is really what I want to achieve:

(Am using SPT throughout)

I have an employees table residing on a sqlserver. These table has employee static info. There is also another table
that has the employees payments/benefits/loans etc.as raw data. And another table that will hold monthly procesed transactions for future references.

Now, I need to go through the employees table to determine
details like dates of employement/termination/suspension then if this particular employee qualifies to be on this month's payroll I go through this other transaction table calculating all payments/deductions/interest on outstand loans/benefits/tax which are then posted into the history table with a period tag. eg [200701] for Jan 2007.

Asuming the employees table has 200,000 staff It wouldnot be wise to fetch all the 200k recs across the wire into a local cursor for DoWhile!Eof() processing.

What would be best approach to these scenario?


Thanks alot

 

[Mike Lewis]

Ongoma,

You don't say which back end you are using. If it is one that supports stored procedures, then that would be the best way to go.

Essentially, you write a procedure that resides in the database. The procedure would be written in SQL, using whatever syntax and language extensions the back end supports.

You can then execute the procedure within VFP, via SQL pass-through. Instead of sending a SELECT or UPDATE command to the back end, you send an EXEC command.

This is by far the most efficient way of dealing with the sort of problem that you describe -- but, as I said, only if your back end supports it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

It's a myth that a stored procedure is always faster than a straight sql command.

As always, I acknowledge your greater expertise.

However, wouldn't that be specific to the back end database? Isn't it possible that on some back ends, SPs are inherently faster than straight SQL? Or not?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[opticalman]

ongoma

You may be able to do your updates with a WHERE clause added to the update command. I first check to see if I can write a WHERE command that selects my group like:

SELECT * FROM MyTable WHERE HireDate = '01/30/2005'

If I get the right group, via SQLEXEC, I then send

UPDATE MyTable SET MyField = MyField + 1 WHERE HireDate = '01/30/2005'

Those were just examples. If you are working with DATE values, I have found success with a built in SQL 2005 FUNCTION called DATEPART() Examples of use:
DATEPART( YEAR , MyDate ) = '2006'
DATEPART( MONTH , MyDate ) = '01'

I am assuming you are using some version of MS SQL SERVER. If you are using ACCESS DB, the DATEPART() function would not aply.

Jim Rumbaugh

 

[Imaginecorp]

“SPs are inherently faster than straight SQL? Or not ?”

No: For a simple query like what Ongoma is doing, its not worth the effort. A straight forward query will suffice.

Yes: if massive amounts of data are being massaged at the back end, (calculations, summaries etc) to return a small dataset, SP’s (IMO) will beat the pants out of straight queries sent from the desktop either via a SQLEXECUTE() or Remote Views.

The biggest joke is VFP is faster at doing this, the more complicated - the faster (2GB and/or multiple tables joins) than SQL Server or Oracle.

 

[Mike Lewis]

Imaginecorp and Mike Y,

I accept the valid points you made.

I think my mistake was saying that SPs are more "efficient". That might or might not be true, and I won't argue the point.

My usual reason for using SPs is not performance, but convenience. I often find it easier to write a procedure as a series of SQL statements (including error checking, and often wrapped in a transaction), than to bring all the data into VFP and process it there.

Of course, there are other issues to consider when deciding to use SPs -- not least of which is whether the back end supports them, or whether the DBAs allow developers to create them.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk