Dragan,

I've create an SPT wrapper class that allows to create an updatable SPT cursor with one line of code.
Let me know if you're interested (the code is too big to post here).

Here's a list of features:

- handles login to SQL Server (DSN-less)
- allows you to create an updatable SPT cursor with one line of code (SQL SELECT)
e.g. o.sqlSelect("SELECT * FROM customers",, 5) && 5 = optimistic table buffering
- automatically figures out the PK for the table
- handles table names and field names that contain characters that are not allowed in VFP (e.g [my long field name], [Order Details])
- handles field names that are different in SQL Server and in VFP (e.g. SELECT MySqlField AS MyVfpField)
- support of parameterized views
- support of REQUERY() of SPT cursor
- keeps track of multiple SPT cursors
- error handling (could be improved though)

Here's some sample code:

o = NEWOBJECT("sqlPass", "sqlPass.prg")

?o.sqlConnect("N11416GE", "northwind", "sa", "") && don't use blank passwords! ;-)
?o.sqlSelect("SELECT * FROM customers",, 5)  && 5 = optimistic table buffering

*-- at this point, we have an SPT cursor that is updatable and that behaves 
*-- almost like a regular VFP local view.
*-- The only exception is REQUERY() which doesn't work with SPT cursors. 
*-- The wrapper takes care of that (see below).

*-- change some data
REPLACE ALL companyName WITH "x" + ALLTRIM(companyName)
?TABLEUPDATE(.t.)
?o.Requery()
BROWSE
*-- revert the changes
REPLACE ALL companyName WITH SUBSTR(companyName, 2)
?TABLEUPDATE(.t.)
?o.Requery()
BROWSE

*-- the following sample shows how to use parameterized views
lcCompanyId = "ALFKI"
?o.sqlSelect("SELECT * FROM customers WHERE customerId=?lcCustomerId",, 5)  && 5 = optimistic table buffering
BROWSE
lcCompanyId = "ANTON"
?o.Requery()
BROWSE

*-- the wrapper keeps track of all the SPT cursors. This allows you to open other views.
?o.sqlSelect("SELECT * FROM orders WHERE customerid=?lcCustomerId",, 5)
BROWSE
lcCustomerId="ALFKI"
?o.Requery()
BROWSE

SELECT customers
?o.Requery()
BROWSE

Dragan,

I've create an SPT wrapper class that allows to create an updatable SPT cursor with one line of code.
Let me know if you're interested (the code is too big to post here).

Here's a list of features:

- handles login to SQL Server (DSN-less)
- allows you to create an updatable SPT cursor with one line of code (SQL SELECT)
e.g. o.sqlSelect("SELECT * FROM customers",, 5) && 5 = optimistic table buffering
- automatically figures out the PK for the table
- handles table names and field names that contain characters that are not allowed in VFP (e.g [my long field name], [Order Details])
- handles field names that are different in SQL Server and in VFP (e.g. SELECT MySqlField AS MyVfpField)
- support of parameterized views
- support of REQUERY() of SPT cursor
- keeps track of multiple SPT cursors
- error handling (could be improved though)

Here's some sample code:

o = NEWOBJECT("sqlPass", "sqlPass.prg")

?o.sqlConnect("N11416GE", "northwind", "sa", "") && don't use blank passwords! ;-)
?o.sqlSelect("SELECT * FROM customers",, 5)  && 5 = optimistic table buffering

*-- at this point, we have an SPT cursor that is updatable and that behaves 
*-- almost like a regular VFP local view.
*-- The only exception is REQUERY() which doesn't work with SPT cursors. 
*-- The wrapper takes care of that (see below).

*-- change some data
REPLACE ALL companyName WITH "x" + ALLTRIM(companyName)
?TABLEUPDATE(.t.)
?o.Requery()
BROWSE
*-- revert the changes
REPLACE ALL companyName WITH SUBSTR(companyName, 2)
?TABLEUPDATE(.t.)
?o.Requery()
BROWSE

*-- the following sample shows how to use parameterized views
lcCompanyId = "ALFKI"
?o.sqlSelect("SELECT * FROM customers WHERE customerId=?lcCustomerId",, 5)  && 5 = optimistic table buffering
BROWSE
lcCompanyId = "ANTON"
?o.Requery()
BROWSE

*-- the wrapper keeps track of all the SPT cursors. This allows you to open other views.
?o.sqlSelect("SELECT * FROM orders WHERE customerid=?lcCustomerId",, 5)
BROWSE
lcCustomerId="ALFKI"
?o.Requery()
BROWSE

SELECT customers
?o.Requery()
BROWSE

HTH
>>Hi!
>>
>>I did updating of memo fields through SPT some time ago, but I do not remember a single problem. Check what is "SQL WHERE" option in your SPT cursor - send only key fields in SQL Where. If you cannot solve the problem, simple SQLEXEC(nn,"UPDATE MyTable Set MyMemo=?MyVFPCursor.MyMemo WHERE ID=?MyVFPCursor.ID") is not that complex.
>
>I know, but then I'd rather give up the updatable cursor and build a complete "Update .. Set" SQL command. Or, maybe not - if there's a 8K limit per command, and I need to do multiple records, file buffering on... doing just memos separately may do the trick. Maybe I should set these fields to something else, not Text? Uchitsya, uchitsya, uchitsya...
>
>Nice routine, BTW. I'd probably write something like that once I found which things I need to automate. I'm still in the phase of finding what works and what doesn't.