后台sql server2000
数据提供 adoquery+datasetProvider
连接方式 socketConnection
客户端 Clientdataset取得数据是通过dataRequest取得,更新数据通过在相应datasetProvider的beforeUpdateRecord中处理。这些取得数据和数据更新的处理都是用adoquery执行相应的存储过程实现的。下面是取得数据和更新数据的存储过程:
CREATE PROCEDURE GetUser
@userType VARCHAR(6)='ALL',
@userId INTEGER=0,
@currUserId INTEGER=0,
@currUserMode INTEGER=1
AS
BEGIN
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
SET NOCOUNT ON
DECLARE
@ErrorMsg VARCHAR(400)
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*特定编号用户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/IF @userType='ONE'
BEGIN
IF (dbo.UserIdCheck(@userId))=1000
BEGIN
SET @ErrorMsg=' **当前操作的资料在数据库中不存在,可能已被删除**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END
ELSE
BEGINSELECT @userID AS uservID,
tu.userName AS uservName,
tu.userPWD AS uservPWD,
tu.userDepartment AS uservDepartment,
tu.userComments AS uservComments,
tu.userLevel AS uservLevel,
tu.userLastTime AS uservLastTime
FROM dbo.tblUSER tu
WHERE tu.userId=@userID
END
END/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*选择用户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/ELSE IF @userType='SELECT' BEGIN
SELECT tu.userId AS uservID,
tu.userName AS uservName
FROM dbo.tblUSERO tu /*优化*/END/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*HOME ALL*/
/*所有客户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/ELSE
BEGIN
SELECT tu.userID AS uservID,
tu.userName AS uservName,
tu.userPWD AS uservPWD,
tu.userDepartment AS uservDepartment,
tu.userComments AS uservComments,
tu.userLevel AS uservLevel,
tu.userLastTime AS uservLastTime
FROM dbo.tblUSER tu
ORDER BY uservId
END
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
SET NOCOUNT OFF
END
GO
数据提供 adoquery+datasetProvider
连接方式 socketConnection
客户端 Clientdataset取得数据是通过dataRequest取得,更新数据通过在相应datasetProvider的beforeUpdateRecord中处理。这些取得数据和数据更新的处理都是用adoquery执行相应的存储过程实现的。下面是取得数据和更新数据的存储过程:
CREATE PROCEDURE GetUser
@userType VARCHAR(6)='ALL',
@userId INTEGER=0,
@currUserId INTEGER=0,
@currUserMode INTEGER=1
AS
BEGIN
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
SET NOCOUNT ON
DECLARE
@ErrorMsg VARCHAR(400)
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*特定编号用户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/IF @userType='ONE'
BEGIN
IF (dbo.UserIdCheck(@userId))=1000
BEGIN
SET @ErrorMsg=' **当前操作的资料在数据库中不存在,可能已被删除**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END
ELSE
BEGINSELECT @userID AS uservID,
tu.userName AS uservName,
tu.userPWD AS uservPWD,
tu.userDepartment AS uservDepartment,
tu.userComments AS uservComments,
tu.userLevel AS uservLevel,
tu.userLastTime AS uservLastTime
FROM dbo.tblUSER tu
WHERE tu.userId=@userID
END
END/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*选择用户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/ELSE IF @userType='SELECT' BEGIN
SELECT tu.userId AS uservID,
tu.userName AS uservName
FROM dbo.tblUSERO tu /*优化*/END/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*HOME ALL*/
/*所有客户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/ELSE
BEGIN
SELECT tu.userID AS uservID,
tu.userName AS uservName,
tu.userPWD AS uservPWD,
tu.userDepartment AS uservDepartment,
tu.userComments AS uservComments,
tu.userLevel AS uservLevel,
tu.userLastTime AS uservLastTime
FROM dbo.tblUSER tu
ORDER BY uservId
END
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
SET NOCOUNT OFF
END
GO
@userName VARCHAR(10),
@userPWD VARCHAR(10),
@userDepartment VARCHAR(20),
@userLevel INTEGER=1,
@userComments VARCHAR(50) = NULL,
@userLastTime DATETIME = NULL,
@currUserId INTEGER=0
AS
BEGIN
SET NOCOUNT ON
DECLARE @varUserLevel INTEGER,
@theCount INTEGER,
@ErrorMsg VARCHAR(400)SET @varUserLevel=dbo.UserIdCheck(@currUserId)
IF @varUserLevel=1000
BEGIN
SET @ErrorMsg= '**您的用户ID在数据库中不存在,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
ENDIF @varUserLevel=1
BEGIN
SET @ErrorMsg= '**您是低权限用户,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END/*判断用户名是否重复*/
SELECT @theCount=COUNT(userId) FROM dbo.tblUSER WHERE userName=@userName
IF @theCount=1
BEGIN
SET @ErrorMsg= '**用户名称重复,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
ENDSET @userLastTime = ISNULL(@userLastTime,GETDATE())INSERT INTO dbo.tblUSER (userName,userPWD,userDepartment,userLevel,userComments,userLastTime)
VALUES (@userName,@userPWD,@userDepartment,@userLevel,@userComments,@userLastTime)
SET NOCOUNT OFF
END
GO
取得数据是这样做的
clientdataset1.data:=clientdataset1.dataRequest('exec GetUser @userType='+#39+'all'+#39);
如果符合raiserror的条件,在客户端也能够捕捉到相应的错误。可是数据更新就捕捉不到。在beforeUpdateRecord事件中,通过adoquery执行的 UserInsert存储过程。用重新触发错误也不行。try
.....
except
raise;//重新触发错误
end;客户端就是捕捉不到任何错误,执行也不成功。请高手执教。
//如果权限不够(varUserLevel=1000),执行到
//spAccountExec.ExecProc;就出错了(没有提示,跟踪调试不动了)
//我检查了相关的存储过程,raiserror后都有返回语句 return
//权限判断
CREATE FUNCTION UserIdCheck (@userId integer)
RETURNS integer AS
BEGIN DECLARE
@theCount INTEGER,
@userLevel INTEGER,
@theResult INTEGERSET @theCount=0SELECT @theCount=tu.userId ,@userLevel=tu.userLevel FROM dbo.tblUSERO tu
WHERE tu.userId=@userIdIF @theCount=0 /*无此用户*/
BEGIN
SET @theResult=1000
END
/*有此用户返回用户权限值*/
ELSE
BEGIN
SET @theResult= @userLevel
ENDRETURN @theResultEND
//应用程序服务器的删除客户资料的代码if UpdateKind=ukDelete then
begin
accountvId:=vartostr(deltaDS.fieldByName('accountvId').OldValue);
try
spAccountExec.Close;
spAccountExec.ProcedureName:='AccountDelete';
spAccountExec.Parameters.Clear;
spAccountExec.Parameters.CreateParameter('@accId',ftInteger,pdInput,0,strToInt(accountvId));
spAccountExec.Parameters.CreateParameter('@currUserId',ftInteger,pdInput,0,currUserId);
spAccountExec.Prepared;
spAccountExec.ExecProc;//到这里出错,但捕捉不到错误信息 //**************************************
applied:=true; //very important
//**************************************
except
on e:exception do
begin
applied:=false;
raise;
end;
end;
end;
//删除客户资料的存储过程CREATE PROCEDURE AccountDelete
@accId INTEGER,
@currUserId INTEGER=0
AS
BEGIN
SET NOCOUNT ON
DECLARE
@varUserLevel INTEGER,
@ErrorMsg VARCHAR(400)IF (dbo.AccountIdCheck(@accId))=0
BEGIN
SET @ErrorMsg= '**当前操作的资料在数据库中不存在,可能已被删除**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
ENDSET @varUserLevel=dbo.UserIdCheck(@currUserId)
IF @varUserLevel=1000
BEGIN
SET @ErrorMsg= '**您的用户ID在数据库中不存在,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
ENDIF @varUserLevel=1
BEGIN
SET @ErrorMsg= '**您是低权限用户,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END
DELETE FROM dbo.tblACCOUNT
WHERE accId=@accIdSET NOCOUNT OFF
END
GO
//如果权限不够(varUserLevel=1),执行到
//spAccountExec.ExecProc;就出错了(没有提示,跟踪调试不动了)
//我检查了相关的存储过程,raiserror后都有返回语句 return
不好意思,更正一下
请高手们提提意见,你们做在数据库后台进行安全控制是如何捕捉到错误的?RAISERROR?
我添加了一个Reconcile Error Dialog ,在clientdataset的onReconcileError中,Action:=HandleReconcileError(dataset,UpdateKind, E);能捕捉到错误,而且出现的错误完全符合我的设计。但是程序中的try..except捕捉不到。
ADOSP.ProcedureName:='sp_codenamedel;1'; ADOSP.Parameters.Clear; //清空参数列表
with ADOSP.Parameters.AddParameter do //追加新参数
begin
Name:='@Code';
DataType:=ftString;
Direction := pdInput;
Size:=4;
end;
with ADOSP.Parameters.AddParameter do
begin
Name:='@TableName';
DataType:=ftString;
Direction := pdInput;
Size:=40;
end;
with ADOSP.Parameters.AddParameter do
begin
Name:='@State';
DataType:=ftInteger;
Direction := pdInputOutput;
end;
if not ADOSP.Prepared then ADOSP.Prepared:=True; ADOSP.Parameters.ParamByName('@Code').Value:=ACode;
ADOSP.Parameters.ParamByName('@TableName').Value:=TableName;
ADOSP.ExecProc;
Result:=ADOSP.Parameters.ParamByName('@State').Value;注意,创建参数的顺序要与存储过程的参数顺序一致!
else if UpdateKind=ukDelete then
begin
accountvId:=vartostr(deltaDS.fieldByName('accountvId').OldValue);
spAccountExec.Close;
spAccountExec.ProcedureName:='AccountDelete';
spAccountExec.Parameters.Clear;
spAccountExec.Parameters.CreateParameter('@accId',ftInteger,pdInput,0,strToint(accountvId)));
spAccountExec.Parameters.CreateParameter('@currUserId',ftInteger,pdInput,0,currUserId);
spAccountExec.Prepared:=true;
spAccountExec.ExecProc; //停在这里,F8也没有执行下一条语句,但是客户端并没有捕获
//到错误,仍旧可以正常使用客户端 applied:=true; end;
@accId INTEGER,
@currUserId INTEGER=0
AS
BEGINSET NOCOUNT ON
DECLARE
@varUserLevel INTEGER,
@ErrorMsg VARCHAR(400)IF (dbo.AccountIdCheck(@accId))=0
BEGIN
SET @ErrorMsg= '**当前操作的资料在数据库中不存在,可能已被删除**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
ENDSET @varUserLevel=dbo.UserIdCheck(@currUserId)
IF @varUserLevel=1000
BEGIN
SET @ErrorMsg= '**您的用户ID在数据库中不存在,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
ENDIF @varUserLevel=1
BEGIN
SET @ErrorMsg= '**您是低权限用户,不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问,请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END
DELETE FROM dbo.tblACCOUNT
WHERE accId=@accIdSET NOCOUNT OFF
END
GO
RETURNS integer AS
BEGIN
DECLARE @theCount integer
SELECT @theCount=count(ta.accId) FROM dbo.tblACCOUNT ta
WHERE ta.accId=@accId
RETURN @theCount
ENDCREATE FUNCTION UserIdCheck (@userId integer)
RETURNS integer AS
BEGIN DECLARE
@theCount INTEGER,
@userLevel INTEGER,
@theResult INTEGERSET @theCount=0SELECT @theCount=tu.userId ,@userLevel=tu.userLevel FROM dbo.tblUSER tu
WHERE tu.userId=@userIdIF @theCount=0 /*无此用户*/
BEGIN
SET @theResult=1000
END
/*有此用户返回用户权限值*/
ELSE
BEGIN
SET @theResult= @userLevel
ENDRETURN @theResultEND
你是在中间层的代码里捕捉存储过程的异常?
如果是这样,我想不可能。数据库的异常怎么会被VCL捕捉到,除非是Borland自己的数据库。
不知SQL Server支不支持传出参数?
如果支持,那很好办,可以在存储过程中捕捉异常,把错误码作为参数传出来。
然后在代码里处理,这种方式我采用过,不过是Delphi+BDE+Oracle.
var
strSuccess:string;
begin
strSuccess:='';
with spProfCal do
begin
ParamByName('xCOMPID').AsString:=xCOMPID;
ParamByName('CURDATE').AsString:=DateToString(xCURDAT);
ExecProc;
//注意SUCCESS是传出参数,赋值是在存储过程内部进行的。
strSuccess:=ParamByName('SUCCESS').AsString;
if trim(strSuccess) = 'Y' then
Result:=True
else
Result:=False;
end;
end;
再另外说一句,如果你确定是ADO的问题,可以改成BDE试试,先不要改架构。这个时候你需要冷静处理。不客气的讲,而且我觉得你开发方式也有点问题,写了这么多代码,才来测试这种架构可不可行,风险被放大了。希望你不要介意我这么说。^-^