Re: http://expert.csdn.net/Expert/topic/1206/1206102.xml?temp=4.985988E-03如果要在存储过程中存取数据库,则要调用ntwdblib.dll提供的函数,关于这个库的Delphi
wrapper,也可以到www.delphi-jedi.org的API Labrary或者www.zeoslib.org中找到,用法
还可参考一下MsSqlServer的Onlinebook,和SqlServer目录下的Tools\DevTools\Samples\dblib\c的代码,
在zeoslib的ZeosDBO也是该库的极好的用例。不过从SqlServer7开始,MS就声明不再升级
ntwdblib.dll了,所以有部分Sql7,8的特性ntwdblib是不支持的,例如sql8的bigint数据类
型,我尝试过用ntwdblib.dll存取过该种bigint类型的数据,Select,Update, Delete都没
问题,就是存储过程的output参数解决不了。下面是一个简单的SQLServer扩展存储过程的例子,作用如Delphi的EncodeDateTime:
{ **************************************************************************** }
library DWXpLib;uses
SysUtils,
Classes,
DateUtils,
MSSqlODS; //the wrapper of ntwdblib.dll{$R *.res}const
XP_NOERROR = 0;
XP_ERROR = 1; MAX_SERVER_ERROR = 20000;
XPERR_UNKNOWN = MAX_SERVER_ERROR + 1;
XPERR_INVALID_PARAM_COUNT = MAX_SERVER_ERROR + 2;
XPERR_INVALID_DATA_TYPE = MAX_SERVER_ERROR + 3;
XPERR_ODS_FUNC_FAIL = MAX_SERVER_ERROR + 4;
XPERR_INVALID_PARAM_TYPE = MAX_SERVER_ERROR + 5;
XPERR_INVALID_PARAM = MAX_SERVER_ERROR + 6;
XPERR_DATA_CONVERT = MAX_SERVER_ERROR + 7;
type
PDBDATETIME4 = ^DBDATETIME4;
PDBDATETIME = ^DBDATETIME;function __GetXpVersion: ULONG; stdcall;
begin
Result := ODS_VERSION;
end;procedure SendError(pSrvProc: PSRV_PROC; aErrCode: Integer; szErrorMsg: string);
begin
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, aErrCode, 16, 1,
nil, 0, 1, PChar(szErrorMsg), Length(szErrorMsg)); srv_senddone(pSrvProc, SRV_DONE_ERROR or SRV_DONE_MORE, 0, 0);
end;
wrapper,也可以到www.delphi-jedi.org的API Labrary或者www.zeoslib.org中找到,用法
还可参考一下MsSqlServer的Onlinebook,和SqlServer目录下的Tools\DevTools\Samples\dblib\c的代码,
在zeoslib的ZeosDBO也是该库的极好的用例。不过从SqlServer7开始,MS就声明不再升级
ntwdblib.dll了,所以有部分Sql7,8的特性ntwdblib是不支持的,例如sql8的bigint数据类
型,我尝试过用ntwdblib.dll存取过该种bigint类型的数据,Select,Update, Delete都没
问题,就是存储过程的output参数解决不了。下面是一个简单的SQLServer扩展存储过程的例子,作用如Delphi的EncodeDateTime:
{ **************************************************************************** }
library DWXpLib;uses
SysUtils,
Classes,
DateUtils,
MSSqlODS; //the wrapper of ntwdblib.dll{$R *.res}const
XP_NOERROR = 0;
XP_ERROR = 1; MAX_SERVER_ERROR = 20000;
XPERR_UNKNOWN = MAX_SERVER_ERROR + 1;
XPERR_INVALID_PARAM_COUNT = MAX_SERVER_ERROR + 2;
XPERR_INVALID_DATA_TYPE = MAX_SERVER_ERROR + 3;
XPERR_ODS_FUNC_FAIL = MAX_SERVER_ERROR + 4;
XPERR_INVALID_PARAM_TYPE = MAX_SERVER_ERROR + 5;
XPERR_INVALID_PARAM = MAX_SERVER_ERROR + 6;
XPERR_DATA_CONVERT = MAX_SERVER_ERROR + 7;
type
PDBDATETIME4 = ^DBDATETIME4;
PDBDATETIME = ^DBDATETIME;function __GetXpVersion: ULONG; stdcall;
begin
Result := ODS_VERSION;
end;procedure SendError(pSrvProc: PSRV_PROC; aErrCode: Integer; szErrorMsg: string);
begin
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, aErrCode, 16, 1,
nil, 0, 1, PChar(szErrorMsg), Length(szErrorMsg)); srv_senddone(pSrvProc, SRV_DONE_ERROR or SRV_DONE_MORE, 0, 0);
end;
解决方案 »
- 关于SakEmail邮件接收后如何判断是附件还是邮件内容?
- 请问如何FASTREPORT3.0中动态的在主数据区中动态创建MEMO?
- 请教一个有关于自定义FORM的初级问题
- Acess 加上密码后为什么ADO里输入密码后连接不上老提示出错?急急急!!!!
- delphi在安装Office WEb Component的时候出现问题!
- 帮个大忙!!!给大分哦!
- 怎么取文件中冒号后的值(关于文件的读取)?
- 这样的数组常量该如何写啊
- 问个四舍五入问题
- 首次搞开发应该注意的问题!
- 三层高手请-->中间层提示"Interface not support!",如何解决
- 寻:用Delphi写SQLServer扩展存储过程的例子
{
execute dwxp_encode_datetime
@year, --1 integer
@month, --2 integer
@day, --3 integer
@hour, --4 integer
@minute, --5 integer
@second, --6 integer
@milliseconds, --7 integer
@datetime output --8 datetime
}
var
I: Integer;
bType, bType2: Byte;
cbMaxLen, cbActualLen: ULONG;
dt4: DBDATETIME4;
dt: DBDATETIME;
fNull: Boolean;
vByte: Byte;
vWord: Word;
vLongWord: LongWord;
vParams1_7: array[1..7] of LongWord;
vPointer: Pointer;
vDateTime: TDateTime;
vDateTimeStr: string;
begin
Result := XP_ERROR; if srv_rpcparams(pSrvProc) <> 8 then
begin
SendError(pSrvProc, XPERR_INVALID_PARAM_COUNT,
'dwxp_encode_datetime: 参数的个数不符合要求。');
Exit;
end; if srv_paraminfo(pSrvProc, 1, @bType, @cbMaxLen, @cbActualLen, nil, @fNull) = FAIL then
begin
SendError(pSrvProc, XPERR_ODS_FUNC_FAIL,
'dwxp_encode_datetime: 调用srv_paraminfo时失败。');
Exit;
end; if (srv_paramstatus(pSrvProc, 8) and SRV_PARAMRETURN) = 0 then
begin
SendError(pSrvProc, XPERR_INVALID_PARAM_TYPE,
'dwxp_encode_datetime: @datetime参数必须声明为OUTPUT。');
Exit;
end; for I := 1 to 7 do
begin
if srv_paraminfo(pSrvProc, I, @bType, @cbMaxLen, @cbActualLen, nil, @fNull) = FAIL then
begin
SendError(pSrvProc, XPERR_ODS_FUNC_FAIL,
'dwxp_encode_datetime: 调用srv_paraminfo时失败。');
Exit;
end; if fNull then
begin
SendError(pSrvProc, XPERR_INVALID_PARAM,
Format('dwxp_encode_datetime: 参数%d不能为NULL。', [I]));
Exit;
end; case bType of
SRVINT1:
vPointer := @vByte;
SRVINT2:
vPointer := @vWord;
SRVINT4, SRVINTN:
vPointer := @vLongWord;
else
SendError(pSrvProc, XPERR_INVALID_DATA_TYPE,
Format('dwxp_encode_datetime: 参数%d的数据类型不符合要求。%d',
[I, bType]));
Exit;
end; if srv_paraminfo(pSrvProc, I, @bType, @cbMaxLen, @cbActualLen, vPointer, @fNull) = FAIL then
begin
SendError(pSrvProc, XPERR_ODS_FUNC_FAIL,
'dwxp_encode_datetime: 调用srv_paraminfo时失败。');
Exit;
end; case bType of
SRVINT1: vParams1_7[I] := vByte;
SRVINT2: vParams1_7[I] := vWord;
SRVINT4, SRVINTN: vParams1_7[I] := vLongWord;
end;
end; if srv_paraminfo(pSrvProc, 8, @bType, @cbMaxLen, @cbActualLen, nil, @fNull) = FAIL then
begin
SendError(pSrvProc, XPERR_ODS_FUNC_FAIL,
'dwxp_encode_datetime: 调用srv_paraminfo时失败。');
Exit;
end; vDateTime := EncodeDateTime(vParams1_7[1],
vParams1_7[2], vParams1_7[3], vParams1_7[4], vParams1_7[5],
vParams1_7[6], vParams1_7[7]);
vDateTimeStr := DateTimeToStr(vDateTime); if bType = SRVDATETIMN then
begin
if cbMaxLen = SizeOf(DBDATETIME4) then
bType2 := SRVDATETIM4
else
bType2 := SRVDATETIME;
end
else
bType2 := bType;
case bType2 of
SRVDATETIM4:
begin
if srv_convert(pSrvProc, SRVVARCHAR, PChar(vDateTimeStr),
Length(vDateTimeStr),
SRVDATETIM4, @dt4, SizeOf(DBDATETIME4)) = -1 then
begin
SendError(pSrvProc, XPERR_DATA_CONVERT,
'dwxp_encode_datetime: 转换数据时出错。');
Exit;
end; srv_paramsetoutput(pSrvProc, 8, @dt4, SizeOf(DBDATETIME4), False);
end; SRVDATETIME:
begin
if srv_convert(pSrvProc, SRVVARCHAR, PChar(vDateTimeStr),
Length(vDateTimeStr),
SRVDATETIME, @dt, SizeOf(DBDATETIME)) = -1 then
begin
SendError(pSrvProc, XPERR_DATA_CONVERT,
'dwxp_encode_datetime: 转换数据时出错。');
Exit;
end;
srv_paramsetoutput(pSrvProc, 8, @dt, SizeOf(DBDATETIME), False);
end; else
SendError(pSrvProc, XPERR_INVALID_DATA_TYPE,
'dwxp_encode_datetim: @datetime参数的数据类型不符合要求。');
Exit;
end; Result := XP_NOERROR;
end;
exports
__GetXpVersion,
dwxp_encode_datetime
;begin
end.
use master
goif (not exists(select * from master.dbo.sysobjects where (xtype = N'X')
and (name = 'dwxp_encode_datetime')))
execute sp_addextendedproc 'dwxp_encode_datetime', 'DWXpLib'
go
http://expert.csdn.net/Expert/topic/1221/1221349.xml?temp=9.107608E-02
十分感谢你