Bob:

I rolling my own. I'm about half way through this application and the concept appears to be working very well. I'm going to incorporate this into my cursor classes. Here are a couple of small samples of how it works.

*- VFP, get a new case
*- oCMIS is the application object
= SQLEXEC(oCMIS.ConHandle, 'Execute spGetCases' + STR(oCMIS.CaseID), 'cCases')

*- SQL Server Stored prodcedure
*- 'convert' strips off time portion of datetime field
CREATE procedure spGetCases(
@caseid int)
as
select pk_cases, fk_fldacts, fk_casetypes, caseno, sumry, rsubj,
convert(char(10), cse_open, 101) as cse_open, convert(char(10), cse_close, 101) as cse_close,
convert(char(10), nigrevdate, 101) as nigrevdate, openby,
convert(char(10), navrev, 101) as navrev, reopen,
convert(char(10), orgclosedate, 101) as orgclosedate, reopenreason
from cases where pk_cases = @caseid


*- VFP, update a case
*- If more than one record, nest in a scan endscan loop
xSQL = pk_cases + [, ] + ALLTR(STR(fk_fldactiv)) + [, ] + ALLTR(STR(fk_casetype)) + [, ] + ['] + ALLTR(caseno) + [', ] + ;
['] + sumry + [', ] + ['] + rsubj + [', ] + ['] + DTOC(cse_open) + [', ] + ;
['] + DTOC(cse_close) + [', ] + ['] + DTOC(nigrevdate) + [', ] + ['] + openby + [', ] + ;
['] + DTOC(navrev) + [', ] + IIF(reopen, '1', '0') + [, ] + ['] + DTOC(orgclosedate) + [', ] + ;
['] + ALLTR(reopenreason) + [']

= SQLEXEC(oCMIS.ConHandle, 'Execute spUpdateCases ' + xSQL)

*- SQL Server Stored prodcedure
CREATE procedure spUpdateCases(
@caseid int,
@fk_fldactiv int,
@fk_casetype int,
@caseno char(11),
@sumry text,
@rsubj text,
@cse_open datetime,
@cse_close datetime,
@nigrevdate datetime,
@openby char(3),
@navrev datetime,
@reopen bit,
@orgclosedate datetime,
@reopenreason char(25))
as
update cases set
fk_fldactiv = @fk_fldactiv,
fk_casetype = @fk_casetype,
caseno = @caseno,
sumry = @sumry,
rsubj = @rsubj,
cse_open = @cse_open,
cse_close = @cse_close,
nigrevdate = @nigrevdate,
openby = @openby,
navrev = @navrev,
reopen = @reopen,
orgclosedate = @orgclosedate,
reopenreason = @reopenreason
where pk_cases = @caseid

You can combine updates and inserts in one procedure by keying on the value of the primary key. I plan to do it this way.

You can also recieve parameters from a stored procedure; like if you wanted to return a message stating whether the update was succesful. You can also return a cursor at the same time.

outputparm = space(10)
=sqlexec(handle,"{call{someprocedure(parms, ?@outputparm)}")

Hope this gives you some ideas.

Regards,
Charlie