Being sure to use the SQL Server engine

Can someone confirm for me that the folowing is true

form.patient.sql = "SELECT * FROM patient WHERE year(DOB) < 1950"

reads the whole table off the server and then finds the records I want and 
makes them into the patient rowset whereas

sqlexec("SELECT * FROM patient WHERE year(DOB) < 1930",
oldies.dbf)

only fetches the records I want from the server but then sticks them into 
the local table oldies.dbf that I then have to "use" before I can look
at 
the data. If I edit one of those records I must then take the resulting 
record and explicitly update the corresponding record on the server.

This feels long-winded. Is there an automated way? Is there a paper 
somewhere about these things so I don't have to keep troubling you guys?

Thanks

Andy 

> Just so much: when you are at the beginning, take a chance and do it like 
> it is meant to be done, from the beginning.
>
> That means: retrieving data from the server for reading only. You don't 
> have to copy them into DBFs for that; just make the queries read-only, and

> cut the datalinks wherever possible (dBase GRID _IS_ a real showstopper 
> here...)
>
> send an explicit UPDATE command or INSERT command or DELETE command to the

> server whenever your user did something correspondingly to the local or 
> read-only data.

Got that. Thanks again

Andy 

Andy Ellis schrieb:
> Can someone confirm for me that the folowing is true
> form.patient.sql = "SELECT * FROM patient WHERE year(DOB) <
1950"
 > ...
> sqlexec("SELECT * FROM patient WHERE year(DOB) < 1930",
oldies.dbf)

quite true, but some other things need to be mentioned.

do preferrably NOT use the joker (*) to retrieve all fields from SQL 
servers. DBFs deliver all fields anyway, and you can locally filter out 
which ones you want and which not. SQL servers deliver all fields you 
request.
If you request all, they deliver all, which in most cases is NOT what 
you want, and what can make delivery take by far longer.


About the feelingly long winded way. You will find that it's by far the 
shorter one, soon enough.

That said, you can have your old way of selecting data from sql servers 
into live queries and have the BDE hold a memory for synchronizing the 
data with the server semi-automatically.

But trust me, you would not really want this.

It works, but it does not deliver the full power of SQL servers and in 
addition it bears some problems at runtime, because sometimes BDE just 
can not synchronize correctly with sql servers.

It would be too long to explain all this.
Just so much: when you are at the beginning, take a chance and do it 
like it is meant to be done, from the beginning.

That means: retrieving data from the server for reading only. You don't 
have to copy them into DBFs for that; just make the queries read-only, 
and cut the datalinks wherever possible (dBase GRID _IS_ a real 
showstopper here...)

send an explicit UPDATE command or INSERT command or DELETE command to 
the server whenever your user did something correspondingly to the local 

In article <tXdbypJbIHA.1236@news-server>, anvyll@gmail.com says...
> Can someone confirm for me that the folowing is true
> 
> form.patient.sql = "SELECT * FROM patient WHERE year(DOB) <
1950"
> 
> reads the whole table off the server and then finds the records I want and

> makes them into the patient rowset whereas
> 
> sqlexec("SELECT * FROM patient WHERE year(DOB) < 1930",
oldies.dbf)
> 
> only fetches the records I want from the server but then sticks them into 
> the local table oldies.dbf that I then have to "use" before I can
look at 
> the data. If I edit one of those records I must then take the resulting 
> record and explicitly update the corresponding record on the server.
> 
> This feels long-winded. Is there an automated way? Is there a paper 
> somewhere about these things so I don't have to keep troubling you guys?
> 
> Thanks
> 
> Andy 
> 
> 
> 
Andy,

Both techniques will work the same and both depend on what type of data 
you are querying. If it is a dBASE table then all rows are returned to 
your local BDE which then chooses the rows you want and returns those to 
your program. If the data are in, say, Firebird, (and you are setup to 
passthrough the SQL to Firebird - the defaul), then only the relevent 
rows are returned from the server. In a network environment and large 
tables, this is where Firebird (or other SQL servers) will have 
tremendous speed advantages over dBASE tables. Using André's suggestion 
to select only the specific fields (instead of SELECT *), speeds things 
up even further.

As well, the correct SQL would be more like this:

select * from patient where extract(year from DOB) < 1950

dBASE's year() function is not part of the SQL standard, so you have do 
things the SQL way in SQL even though you are doing it in a dBASE 
program.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.