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; }