Martin,
I call Stored Procedures at the Server side from my CA's
Here is how I call the SP form a CA for Insert/Update/Delete
SPA_DEL_BANCOS, SPA_INS_BANCOS, SPA_UPD_BANCOS are the Stored Procedures in SQL Server.
I made it with Mark McCasland CA builder and changed it to create the code for each SP call in each CA.
All my SP at SQL Server start with SPA_
A sample of code in a CA (Init event)
TEXT TO This.DeleteCmd NOSHOW
EXECUTE SPA_DEL_BANCOS ?ca_Bancos.Codigo_banco
ENDTEXT
TEXT TO This.InsertCmd NOSHOW
EXECUTE SPA_INS_BANCOS ?ca_Bancos.NOME_BANCO, ?ca_Bancos.NUMERO_BANCO
ENDTEXT
TEXT TO This.UpdateCmd NOSHOW
EXECUTE SPA_UPD_BANCOS ?ca_Bancos.CODIGO_BANCO, ?ca_Bancos.NOME_BANCO, ?ca_Bancos.NUMERO_BANCO
ENDTEXT
ca_bancos is the CursorAdapter
Sample of the SP for CA_INS_BANCOS in SQL Server:
CREATE PROCEDURE SPA_INS_Bancos
@NOME_BANCO CHAR(35)
,@NUMERO_BANCO CHAR(3)
AS
BEGIN TRANSACTION
INSERT INTO Bancos(
NOME_BANCO
,NUMERO_BANCO
)
VALUES (@NOME_BANCO
,@NUMERO_BANCO
)
IF (@@ERROR <> 0) -- Houve ERRO na inserção DO registro
BEGIN
ROLLBACK TRANSACTION
RAISERROR 65006 'Erro na inclusão do registro'
END
ELSE
BEGIN
COMMIT TRANSACTION
END
GO
Just do the same for the SELECT if you wish in the Init event for each CA.
[ ],s
Peter
>Hi, all.
>
>I was doing some training last week at a customer. They are using SQL Server 2000 and they has a corporate policy to do all DB access trough Store Procedures. I helped them in the past to build a data access component when they where using VFP 7, and we sat for a round of test-driven development to refactor it using CursorAdapter. It was more an exercise than something very critical, but anyway...
>
>I couldn't find a good way to make the CursorAdapter use the Select/Insert/Update/Delete SPs.
>
>We try to fiddle with the *Cmd methods, but to no avail. The problem I couldn't figure out was how to pass parameters to the SP. I tried adding them to the SP command string in the Before* (BeforeInsert, for example), but my changes are not applied. I managed to force the parameters before calling TableUpdate, but this only works in single-record cases.
>
>Any hint or example? I guess it should be some easy way I'm totally overlooking.
>
>Thanks in advance,