>Hi all,
>
>I am now going through and summarizing all the key points from the last thread...my head starts to spin with the number of comments made (and some of them went WAY off topic, which is normal I know). I am going to post some new threads with follow up questions that are specific so a bit easier to follow.
>
>First one: would it be possible to start changing their app by moving some of the more "static" information to SQL while keeping most of the data in the VFP tables? They have a few tables that are relatively static (e.g. life company tables that contain an address etc). Why in the world would I do this you might ask?
>
>- to allow me to learn SQL (MS or PostgreSQL) and the limitations
>
>- I will have to reengineer some forms to just pull down way less data (currently their company table has approx 7000 records and the form allows them to search a browsable list) and this is not a big deal. I understand though that I will most likely need to change something like this to bring down a subset of records; it would give me the possibility to present design concepts to them on a lesser used form to approve and they could see the performance
>
>- rewriting an entire large app myself over a couple of years and then migrating to it in one jump is full of pitfalls - we all know how many bugs can be found even with the best of testing; I could develop my own framework and perfect it before rolling it out to all the screens.
>
>So say I change my app to use the cursoradaptor class - does this allow some calls to be made to the SQL backend and others to just access the local/server VFP tables?
>
>Albert

What I do is almost a copy what Dmitry does. The same application runs against VFP or MS SQL Server and both ways it use CA. I wrote my own CA classes based on the base CA, and some supporting SQL* routines, so at times I bypass CA and do things directly. ie:

SQLQueryInto( "select * from myTable where someField = ?", "crsResult", theParameterToSearchFor)

lnRowsMatchingSomeCriteria = SQLExecuteScalar("select count(*) from myTable where myField = ?", myParameter)

SQLInsert("MyTable", "field1, field2, field3m field4", 1, "Some char data", "Another one", DateTime())

SQLNonQuery("Update myTable set myField=? where id=?", m.ValueToset, m.Id) 

What they are doing is self explanatory I guess. These type of operations don't need to setup a CA or view and these SQL* series functions handle them nicely.

Also, if you have a table in DE (pointing to a VFP table as expected normally), at runtime it is replaced by a CA that points to either VFP or SQL and all this is based on an app wide setting (oApp.DataMode and oApp.DataConnectionString namely).

Also I have a GetSQLText() method, that sanitizes the SQL before sent to backend based on what that is (SQL dialects and functions are not the same in all backends). ie:

local lcSQL
text to m.lcSQL textmerge noshow
select *, 
   cast(
      <SQL>case shippedOn is null then 'Pending' else 'Shipped' end</SQL>
      <VFP>iif(isnul(shippedOn), 'Pending', 'Shipped')</VFP> 
   as varchar(10) ) as Status
from Customer cu
inner join Orders ord on cu.CustomerId = ord.CustomerId
where orderDate >
<SQL>dateadd(month, -1, getdate())</SQL>
<VFP>gomonth(DateTime(), -1)</VFP>
end text

SQLQueryInto(m.lcSQL, 'crsResult')

It suggests that you should convert most of your data related codes to SQL (SQL-Select, Insert, Update, Delete) and yes it is, but also have xbase support like:

* this is like 
* Use myTable 
* where mytable is only alive in this block and buffered
* You can use it like a regular VFP table  
* with indexes

with SQLOpenTable("myTable").  
* Do something

* TableUpdate(2, .T., 'myTable') && it is buffered

endwith

Or the same way instead of with block it could be assigned to a variable:

loTable = SQLOpenTable('myTable')

then it lives as long as the loTable is in scope (if it were a Form property it would be alive until form closes, or if it were public or _screen property then would be alive until released explicitly).

To keep long story short, using the same thing against both backends (chosen at launch - might change anytime if preferred). Downside, you need to be sure you are writing code mostly to support VFP tables (if you can't be radical enough to use only SQL or have bigger < SQL >...< /SQL >, < VFP > < /VFP > blocks) where it means you are missing many cool features in MS SQL compared to VFP's limited SQL. Sometimes SQL might get too complex enough to be separated into 2 prgs like SomeProcSQL, SomeProcVFP.

Anyways, once you start using MS SQL Server (or postgreSQL, ... ) after some time you start to think how could you live without it. (ie: Like I do, wouldn't it be nice you are in Turkey and the database you are connecting to for some testing is 5000+ miles away, that is one small thing I like about).