Search code examples
c++sqlsql-serverstored-procedures

MSSQL server Stored procedure not returning output parameters when called from c++

I have a stored procedure which when i call from MSSQL Server GUI.It returns the results correctly.But when i call the same procedure from my C++ code it Executes correctly but doesn't give the output parameter results. I tried the stored procedure simply returning the output without it doing anything else it succeeded but the stored procedure given below fails.I think there is something wrong happening while the execution of stored procedure. Heres my stored procedure:

IF EXISTS (SELECT name FROM sysobjects 
     WHERE name = 'putFileinFTandJT' AND type = 'P')
DROP PROCEDURE putFileinFTandJT
GO
CREATE PROCEDURE putFileinFTandJT
/* Put incoming file in File Table & Job Table */

/* IN */ @input varchar(max), 
/* IN */ @flName varchar(max), 
/* IN */ @uid INT, 
/* IN */ @jbid INT,
/* OUT */@t INT  OUTPUT,
/* OUT */@t1 INT OUTPUT

AS
DECLARE @orgFileID INT = -1;
DECLARE @temp1 INT;
DECLARE @newFileID INT;
DECLARE @versionCnt INT = 0;

/* SET  autocommit = 0; */
EXEC getDirId @input,@temp1 OUTPUT ;
if @temp1 = -1 
    EXEC putDir @uid,@input,@temp1 OUTPUT;

select @orgFileID = FileID   from FileTable where DirID=@temp1 and FileName=@flName and VersionNumber = 0;
IF  @orgFileID = -1 
BEGIN
    set @orgFileID = 0;
    insert into FileTable(FileName,DirID,IsDirectory,UserID,VersionNumber,isduplicate) values(@flName,@temp1,0,@uid,0,0);
END 
ELSE
BEGIN
    select @versionCnt = count(*)   from VersionTable where FileID = @orgFileID;
    insert into FileTable(FileName,DirID,IsDirectory,UserID,VersionNumber,isduplicate) values(@flName,@temp1,0,@uid,@versionCnt + 1,0);
    SET @versionCnt = @versionCnt + 1;
END
select @newFileID = FileID   from FileTable where FileName = @flName and DirID = @temp1 and VersionNumber = @versionCnt;
insert into JobTable values(@jbid,@newFileID);
commit;
set @t = @newFileID;
set @t1 = @orgFileID;

GO

//Editing Here is the cpp code

HWND desktopHandle = GetDesktopWindow(); 
SQLCHAR buff[255]={0};
int intval1=0,intval2=0,intval3 = 0,intval4,intval5;
     retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
     retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,                     (SQLPOINTER*)SQL_OV_ODBC3,    0); 

     retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
      retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

     retcode = SQLDriverConnect(hdbc, desktopHandle, (SQLWCHAR*) L"Driver={SQL Server};Server=WIN-E9EO4VT0V8I;Database=connector;UID=sa;PWD=spsoft_123;Trusted_Connection=False;", SQL_NTS, NULL, 0, NULL, 0);
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);



   retcode = SQLPrepare(hstmt, (SQLWCHAR*)L"{call putFileinFTandJT(?,?,?,?,?,?,?)}" , SQL_NTS);


   retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 100, 0, szQuote, 0, &cbValue2);
    retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 50, 0, szQuote2, 0, &cbValue2);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &iuser , 0, &cbValue5);
retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_SLONG , SQL_INTEGER, 0, 0, &ibackup  , 0, &cbValue5);

retcode = SQLBindParameter(hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &intval1, 0, &cbValue6);
retcode = SQLBindParameter(hstmt, 6, SQL_PARAM_OUTPUT, SQL_C_SLONG , SQL_INTEGER, 0, 0, &intval2, 0, &cbValue7);
retcode = SQLBindParameter(hstmt, 7, SQL_PARAM_OUTPUT, SQL_C_SLONG , SQL_INTEGER, 0, 0, &intval3, 0, &cbValue8);
retcode = SQLExecute(hstmt);
if(retcode==SQL_ERROR)
{
    SQLWCHAR sqlstate[1024];
SQLWCHAR message[1024];
if(SQL_SUCCESS == SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, NULL, message, 1024, NULL))
    std::cout<<"Message: "<<(wchar_t*)message<<"\nSQLSTATE: "<<sqlstate<<std::endl;
}

Please help as i am not getting any ideas. Thanks

Solution

  • Are there additional result counts being returned in your results? I'm not sure exactly which API you're using, but to get output parameters from SP calls in my experience has often meant walking through the results returned skipping over result counts for the final RPC result. (You might also try putting set nocount on in the SP).