C++ 调用SQLSERVER 数据库存储过程示例
1.数据库名为5C_DB,数据库登陆账户名:sa,密码为:duan00
2.存储过程名为“[dbo].[GetAllPicPath]”,
输入一个整数,CheckRecordID int,返回(五个图片路径+一个整数表示不同的情况)
内容如下:
(如果第一次创建存储过程[dbo].[GetAllPicPath] ,应该用
CREATE PROCEDURE [dbo].[GetAllPicPath] ,如果已经存在该存储过车,就用
ALTER PROCEDURE [dbo].[GetAllPicPath]
)
USE [5C_DB]
GO
/****** Object: StoredProcedure [dbo].[GetAllPicPath] Script Date: 03/27/2017 13:54:12
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAllPicPath]
-- Add the parameters for the stored procedure here
@CheckRecordID int, --输入参数
@strArc1Path nvarchar(255) out, --输出参数:受电弓1路径
@strArc1ThumbnailPath nvarchar(255) out, ---输出参数:受电弓1缩略图路径
@strArc2Path nvarchar(255) out, --输出参数:受电弓2路径
@strArc2ThumbnailPath nvarchar(255) out, ---输出参数:受电弓2缩略图路径
@strNumPath nvarchar(255) out, ---输出参数:号牌图片路径
@returnID int out ---输出参数:返回类型
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- 我们平时查询的时候在消息栏里不是会有,(1行受影响)之类的文字吗,加上set nocount
on后就不会有了,这就避免了这些没有的信息了
SET NOCOUNT ON;
-- Insert statements for procedure here
--根据入参@CheckRecordID从CheckRecord表中获取图片基本路径 和 设备名
--声明返回值@intReturn, 返回值说明如下
--0,表示拍摄异常,包括未拍摄到弓 和 拍摄到大于2个弓
--1,表示只拍摄到一个弓,并且1号弓为升弓
--2,表示只拍摄到一个弓,并且2号弓为升弓
--3,表示只拍摄到一个弓,并且1号弓为降弓
--4,表示只拍摄到一个弓,并且2号弓为降弓
--5,表示拍摄到了两个弓,并且都为升弓
--6,表示拍摄到了两个弓,并且都为降弓
--7,表示拍摄到了两个弓,且1号弓为升弓,2号弓为降弓
--8,表示拍摄到了两个弓,且2号弓为升弓,1号弓为降弓
DECLARE @intReturn int
select @intReturn=0
DECLARE @strPicPath nvarchar(Max)
DECLARE @DeviceID nchar(10)
SELECT @strPicPath=ImaAddr, @DeviceID=DeviceID FROM CheckRecord WHERE
ChekID=@CheckRecordID
--print '检索到:' + cast(@@rowcount as char) + '行'
--print '中间基本录为:' + @strPicPath
--print '设备ID为:' + @DeviceID
--根据设备ID(DeviceID) 获取设备名
DECLARE @strDeviceName nvarchar(10)
select @strDeviceName=DeviceName from DeviceInfo where DeviceID=@DeviceID
--print '检索到:' + cast(@@rowcount as char) + '行'
--print '设备名为:' + @strDeviceName + '11'
select @strDeviceName= RTRIM(@strDeviceName)
--print '设备名为:' + @strDeviceName + '11'
--根据checkRecordID 在ArcCheckRecord表中 获取行数
DECLARE @ArcNum int
DECLARE @ArcRiseCount int, @ArcDownCount int, @ArcRiseNum int,@ArcDownNum int
set @ArcRiseCount=0
set @ArcDownCount=0
set @ArcRiseNum=0
set @ArcDownNum=0
DECLARE Arc_Cursor SCROLL CURSOR --创建游标
for --并给游标赋值
select ArcNo, ArcIsRise from ArcCheckRecord where checkrecordid=@CheckRecordID
--print '检索到:' + cast(@@rowcount as char) + '行'
set @ArcNum=@@rowcount
OPEN Arc_Cursor --打开游标
DECLARE @ArcNo int, @ArcIsRise int
FETCH NEXT from Arc_Cursor INTO @ArcNo, @ArcIsRise
--检查check @@FETCH_STATUS变量.查看FETCH命令是否成功执行
WHILE @@FETCH_STATUS = 0
BEGIN
if @ArcIsRise=1
BEGIN
select @ArcRiseCount = @ArcRiseCount + 1
select @ArcRiseNum = @ArcNo
END
else
BEGIN
select @ArcDownCount = @ArcDownCount + 1
set @ArcDownNum=@ArcNo
END
FETCH NEXT from Arc_Cursor INTO @ArcNo, @ArcIsRise
END
--print '升弓数量:' + cast(@ArcRiseCount as char)
--print '降弓数量:' + cast(@ArcDownCount as char)
--print cast(@ArcRiseNum as char) + '号弓升弓'
close Arc_Cursor --关闭游标
DEALLOCATE Arc_Cursor --释放游标
--
select @ArcNum=@ArcRiseCount + @ArcDownCount
if @ArcNum = 1 --如果只拍摄到一个弓
begin
if @ArcRiseNum=1 --如果只拍摄到一个弓,并且1号弓为升弓
begin
print '只拍摄到一个弓,并且1号弓为升弓'
select @strArc1Path = @strDeviceName + '\' + @strPicPath +
'\1\0\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath = @strDeviceName + '\' + @strPicPath +
'\1\0\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = ''
print @strArc2Path
SELECT @strArc2ThumbnailPath = ''
print @strArc2ThumbnailPath
select @intReturn=1
end
else --如果只拍摄到一个弓,并且2号弓为升弓
begin
if @ArcRiseNum=2
begin
print '只拍摄到一个弓,并且2号弓为升弓'
select @strArc1Path = ''
print @strArc1Path
select @strArc1ThumbnailPath = ''
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\' + @strPicPath +
'\1\1\PantographResult1.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath = @strDeviceName + '\' +
@strPicPath + '\1\1\PantographLessImg1.jpg'
print @strArc2ThumbnailPath
select @intReturn=2
end
else
begin
if @ArcDownNum=1
begin
print '只拍摄到一个弓,并且1号弓为降弓'
select @strArc1Path = @strDeviceName + '\' +
@strPicPath + '\2\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath = @strDeviceName + '\'
+ @strPicPath + '\2\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\' +
@strPicPath + '\1\1\PantographResult1.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath = @strDeviceName +
'\' + @strPicPath + '\1\1\PantographLessImg1.jpg'
print @strArc2ThumbnailPath
select @intReturn=3
end
else
begin
print '只拍摄到一个弓,并且2号弓为降弓'
select @strArc1Path = @strDeviceName + '\' +
@strPicPath + '\1\0\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath = @strDeviceName + '\'
+ @strPicPath + '\1\0\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\' +
@strPicPath + '\2\PantographResult2.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath = @strDeviceName +
'\' + @strPicPath + '\2\PantographLessImg2.jpg'
print @strArc2ThumbnailPath
select @intReturn=4
end
end
end
end
ELSE --如果拍摄的不止一个弓
BEGIN
if @ArcNum = 2 --如果拍摄到两个弓
BEGIN
IF @ArcRiseCount = 2 --如果拍摄到两个弓,并且都为升弓
BEGIN
print '拍摄到两个弓,并且都为升弓'
select @strArc1Path = @strDeviceName + '\' + @strPicPath +
'\1\0\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath = @strDeviceName + '\' +
@strPicPath + '\1\0\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\' + @strPicPath +
'\1\1\PantographResult1.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath = @strDeviceName + '\' +
@strPicPath + '\1\1\PantographLessImg1.jpg'
print @strArc2ThumbnailPath
select @intReturn=5
END
ELSE --如果拍摄到两个弓,并且不是都为升弓
BEGIN
IF @ArcDownCount = 2 --如果拍摄到两个弓,并且都为降弓
BEGIN
print '拍摄到两个弓,并且都为降弓'
select @strArc1Path = @strDeviceName + '\' +
@strPicPath + '\2\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath = @strDeviceName + '\'
+ @strPicPath + '\2\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\' +
@strPicPath + '\2\PantographResult2.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath = @strDeviceName +
'\' + @strPicPath + '\2\PantographLessImg2.jpg'
print @strArc2ThumbnailPath
select @intReturn=6
END
ELSE --如果拍摄到两个弓,并且为一个降弓/一个升弓
begin
if @ArcRiseNum=1 --如果拍摄到两个弓,并且为1号弓升弓
,2号弓为降弓
begin
print '拍摄到两个弓,并且为1号弓升弓,2号弓为降
弓'
select @strArc1Path = @strDeviceName + '\'
+ @strPicPath + '\1\0\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath =
@strDeviceName + '\' + @strPicPath + '\1\0\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\'
+ @strPicPath + '\2\PantographResult2.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath =
@strDeviceName + '\' + @strPicPath + '\2\PantographLessImg2.jpg'
print @strArc2ThumbnailPath
select @intReturn=7
end
else --如果拍摄到两个弓,并且为2号弓升弓,1号弓为降
弓
begin
print '拍摄到两个弓,并且为2号弓升弓,1号弓
为降弓'
select @strArc1Path = @strDeviceName + '\'
+ @strPicPath + '\1\1\PantographResult1.jpg'
print @strArc1Path
select @strArc1ThumbnailPath =
@strDeviceName + '\' + @strPicPath + '\1\1\PantographLessImg1.jpg'
print @strArc1ThumbnailPath
SELECT @strArc2Path = @strDeviceName + '\'
+ @strPicPath + '\2\PantographResult1.jpg'
print @strArc2Path
SELECT @strArc2ThumbnailPath =
@strDeviceName + '\' + @strPicPath + '\2\PantographLessImg1.jpg'
print @strArc2ThumbnailPath
select @intReturn=8
end
end
END
END
ELSE --如果拍摄到大于2个弓,判断其出现异常
BEGIN
print '拍摄到大于2个弓,或没拍摄到弓,出现异常!!!!!'
select @strArc1Path = ''
print @strArc1Path
select @strArc1ThumbnailPath = ''
print @strArc1ThumbnailPath
SELECT @strArc2Path = ''
print @strArc2Path
SELECT @strArc2ThumbnailPath = ''
print @strArc2ThumbnailPath
select @intReturn=0
END
END
select @strNumPath = @strDeviceName + '\' + @strPicPath + '\3\WagonResult.jpg' ---
输出参数:号牌图片路径
select @returnID = @intReturn
return @intReturn
END
3.调用存储过程代码
(备注代码中1433,表示SQLSERVER所在电脑要打开TCP/IP端口号,具体设置请按照http://www.2cto.com/database/201306/217563.html 所示(SQL server 2008 1433端口开启解决方案))
int iSize = strlen("Provider=SQLOLEDB; Server=127.0.0.1,1433;Database=5C_DB; uid=sa; pwd=duan00;");
其中server = 127.0.0.1,1433表示链接本机(127.0.0.1)1433端口;
Database=5C_DB,表示使用5C_DB这个数据库;
uid=sa; pwd=duan00;表示数据库登录名为sa,密码为duan00的用户
#include <stdio.h>
#include <string> #include <iostream> using namespace std; #import "msado15.dll" \ no_namespace \ rename ("EOF", "adoEOF") int main(void) { // @return_value = [dbo].[GetAllPicPath] // @CheckRecordID = 127, // @strArc1Path = @strArc1Path OUTPUT, // @strArc1ThumbnailPath = @strArc1ThumbnailPath OUTPUT, // @strArc2Path = @strArc2Path OUTPUT, // @strArc2ThumbnailPath = @strArc2ThumbnailPath OUTPUT, // @strNumPath = @strNumPath OUTPUT _CommandPtr mCommandPtr; _RecordsetPtr mRecordsetPtr; _ConnectionPtr mConnectionPtr; char sqlString[256] = {0}; int iSize = strlen("Provider=SQLOLEDB; Server=127.0.0.1,1433;Database=5C_DB; uid=sa; pwd=duan00;");memcpy(sqlString,"Provider=SQLOLEDB; Server=127.0.0.1,1433;Database=5C_DB; uid=sa; pwd=duan00;",iSize); CoInitialize(NULL); //初始化COM组件 try { mConnectionPtr.CreateInstance(__uuidof(Connection));// 创建Connection对象 } catch (_com_error e) { CoUninitialize(); } try { mConnectionPtr->Open(sqlString,"", "", adModeUnknown); } catch (_com_error e) { string errorInfo = e.Description(); CoUninitialize(); } CoUninitialize(); try { mCommandPtr.CreateInstance(__uuidof(Command)); mCommandPtr->ActiveConnection=mConnectionPtr; //绑定_ConnectionPtr mCommandPtr->CommandText=_bstr_t("GetAllPicPath"); //存储过程名 mCommandPtr->CommandType=adCmdStoredProc; //命令类型为存储过程或事务等 //添加参数CheckRecordID _ParameterPtr pParam1; pParam1.CreateInstance(__uuidof(Parameter)); pParam1=mCommandPtr->CreateParameter(_bstr_t("@CheckRecordID"),DataTypeEnum::adInteger,adParamInput,sizeof(int)); pParam1->Value=_variant_t(1); mCommandPtr->Parameters->Append(pParam1); _ParameterPtr pParam2; pParam2.CreateInstance(__uuidof(Parameter)); pParam2=mCommandPtr->CreateParameter(_bstr_t("@strArc1Path"),DataTypeEnum::adVarChar,adParamOutput,256); mCommandPtr->Parameters->Append(pParam2); _ParameterPtr pParam3; pParam3.CreateInstance(__uuidof(Parameter)); pParam3=mCommandPtr->CreateParameter(_bstr_t("@strArc1ThumbnailPath"),DataTypeEnum::adVarChar,adParamOutput,256); mCommandPtr->Parameters->Append(pParam3); _ParameterPtr pParam4; pParam4.CreateInstance(__uuidof(Parameter)); pParam4=mCommandPtr->CreateParameter(_bstr_t("@strArc2Path"),DataTypeEnum::adVarChar,adParamOutput,256); mCommandPtr->Parameters->Append(pParam4); _ParameterPtr pParam5; pParam5.CreateInstance(__uuidof(Parameter)); pParam5=mCommandPtr->CreateParameter(_bstr_t("@strArc2ThumbnailPath"),DataTypeEnum::adVarChar,adParamOutput,256); mCommandPtr->Parameters->Append(pParam5); _ParameterPtr pParam6; pParam6.CreateInstance(TEXT("ADODB.Parameter")); pParam6=mCommandPtr->CreateParameter(TEXT("@strNumPath"), DataTypeEnum::adVarChar,adParamOutput,256); mCommandPtr->Parameters->Append(pParam6); // //绑定返回值参数 _ParameterPtr pParam7; pParam7.CreateInstance(__uuidof(Parameter)); pParam7=mCommandPtr->CreateParameter(_bstr_t("@returnID"), DataTypeEnum::adInteger, adParamOutput,sizeof(int)); mCommandPtr->Parameters->Append( pParam7); BOOL bInsert=FALSE; VARIANT vRecordsAffected; //设置执行影响数据库表中的行数 vRecordsAffected.vt=VT_I4; vRecordsAffected.lVal=0; //运行存储过程,返回结果集, 并在参数vRecordsAffected 中 返回存储过程运行的返回值 mRecordsetPtr = mCommandPtr->Execute(&vRecordsAffected,NULL,adCmdStoredProc); string strArc1Path((LPSTR)(LPCSTR)_bstr_t(pParam2->Value)); cout << strArc1Path << endl; string strArc1ThumbnailPath((LPSTR)(LPCSTR)_bstr_t(pParam3->Value)); cout <<strArc1ThumbnailPath << endl; string strArc2Path((LPSTR)(LPCSTR)_bstr_t(pParam4->Value)); cout <<strArc2Path << endl; string strArc2ThumbnailPath((LPSTR)(LPCSTR)_bstr_t(pParam5->Value)); cout <<strArc2ThumbnailPath << endl; string strNumPath((LPSTR)(LPCSTR)_bstr_t(pParam6->Value)); cout <<strNumPath << endl; cout << (int)pParam7->Value<< endl; } catch (_com_error e) { string errorInfo = e.Description(); cout << e.Description() << endl; cout << e.ErrorMessage() << endl; CoUninitialize(); } mCommandPtr.Release(); mRecordsetPtr->Release(); mConnectionPtr->Close(); mConnectionPtr.Release(); CoUninitialize(); system("pause"); return 1; }