Hi everybody,
i've a problem calling a sql sp from my c++ component, if i
voluntarily crash the sp (for example try to insert a null value where
is not allow) return value are all blank and isn't possible for me to
understand correctly the problem
here you are sp:
CREATE PROCEDURE SP_TEST_RETURN_VALUE
(
@.ErrNum INT OUTPUT,
@.ErrMsg varchar(256) OUTPUT
)
AS
BEGIN
DECLARE @.PatientCode int
SET @.ErrNum = 999
SET @.ErrMsg = 'Empty'
BEGIN TRANSACTION
INSERT INTO TabellaTest( [INDEX], CampoTest)
VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
value!!
SET @.ErrNum = @.@.ERROR
IF @.ErrNum > 0 BEGIN
SET @.ErrMsg = 'ERROR (pos 2)'
goto abort_transaction
END
COMMIT TRANSACTION
RETURN 0
abort_transaction:
ROLLBACK TRANSACTION
RETURN -1
END
GO
here you are the call from c++:
.............
try
{
olehr = accessor.Open(dataSource.GetSession(),
_T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
// if fail value is 0 not -1!!
result = accessor.m_RETURNVALUE;
// IF FAIL the 2 memeber below contain wrong value
//accessor.m_ErrNum;
//accessor.m_ErrMsg;
if (FAILED(olehr())) _com_issue_error(olehr());
} // end try
catch (_com_error &e)
{
char * descr = (char*) e.Description();
char * msg = (char*) e.ErrorMessage();
accessor.Close();
}
it seems that the sp fail make the rollback but not se any values into
in/out parameters.
Please help me......Put SET NOCOUNT ON as the first statement in the sp.
CREATE PROCEDURE SP_TEST_RETURN_VALUE
(
@.ErrNum INT OUTPUT,
@.ErrMsg varchar(256) OUTPUT
)
AS
set nocount on
DECLARE @.PatientCode int
SET @.ErrNum = 999
SET @.ErrMsg = 'Empty'
BEGIN TRANSACTION
INSERT INTO TabellaTest( [INDEX], CampoTest)
VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
value!!
SET @.ErrNum = @.@.ERROR
IF @.ErrNum > 0 BEGIN
SET @.ErrMsg = 'ERROR (pos 2)'
goto abort_transaction
END
COMMIT TRANSACTION
RETURN 0
abort_transaction:
ROLLBACK TRANSACTION
RETURN -1
GO
AMB
"Matteo" wrote:
> Hi everybody,
> i've a problem calling a sql sp from my c++ component, if i
> voluntarily crash the sp (for example try to insert a null value where
> is not allow) return value are all blank and isn't possible for me to
> understand correctly the problem
> here you are sp:
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> BEGIN
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> END
> GO
> here you are the call from c++:
> ..............
> try
> {
> olehr = accessor.Open(dataSource.GetSession(),
> _T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
> // if fail value is 0 not -1!!
> result = accessor.m_RETURNVALUE;
> // IF FAIL the 2 memeber below contain wrong value
> //accessor.m_ErrNum;
> //accessor.m_ErrMsg;
> if (FAILED(olehr())) _com_issue_error(olehr());
> } // end try
> catch (_com_error &e)
> {
> char * descr = (char*) e.Description();
> char * msg = (char*) e.ErrorMessage();
> accessor.Close();
> }
>
> it seems that the sp fail make the rollback but not se any values into
> in/out parameters.
> Please help me......
>|||Wow, thanks a lot it seems to be working fine...... I've another
question (if you have another bit time for me!)
As you can see there is a try-catch block, every time the sp crash the
e.Description return "IDispatch Error #3092", i expect that in this
member is store the error description related to e.Error member (which
contain the error code), but indeed there is always the message above,
do you know something about it? Thanks.
Matteo
"examnotes" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge news:<B4E1BBA4-BB85-4D27-8BB0-16A1D4618CA3@.microsoft.com>...
> Put SET NOCOUNT ON as the first statement in the sp.
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> set nocount on
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> GO
>
> AMB
>
> "Matteo" wrote:
>|||sorry, in my previous mail i described a new problem, but the member
that contain "IDispatch Error #3092" is nor e.Desciption, but
e.ErrorMessage!!! I'm sorry...
mbellardi@.ferraniait.com (Matteo) wrote in message news:<3c42a11c.0504220511.4152e939@.posti
ng.google.com>...
> Hi everybody,
> i've a problem calling a sql sp from my c++ component, if i
> voluntarily crash the sp (for example try to insert a null value where
> is not allow) return value are all blank and isn't possible for me to
> understand correctly the problem
> here you are sp:
> CREATE PROCEDURE SP_TEST_RETURN_VALUE
> (
> @.ErrNum INT OUTPUT,
> @.ErrMsg varchar(256) OUTPUT
> )
> AS
> BEGIN
> DECLARE @.PatientCode int
> SET @.ErrNum = 999
> SET @.ErrMsg = 'Empty'
> BEGIN TRANSACTION
> INSERT INTO TabellaTest( [INDEX], CampoTest)
> VALUES ( @.PatientCode, NULL) -- Not CampoTest not allow a null
> value!!
> SET @.ErrNum = @.@.ERROR
> IF @.ErrNum > 0 BEGIN
> SET @.ErrMsg = 'ERROR (pos 2)'
> goto abort_transaction
> END
> COMMIT TRANSACTION
> RETURN 0
> abort_transaction:
> ROLLBACK TRANSACTION
> RETURN -1
> END
> GO
> here you are the call from c++:
> .............
> try
> {
> olehr = accessor.Open(dataSource.GetSession(),
> _T("{? = call dbo.SP_TEST_RETURN_VALUE;1 (?,?) }") );
> // if fail value is 0 not -1!!
> result = accessor.m_RETURNVALUE;
> // IF FAIL the 2 memeber below contain wrong value
> //accessor.m_ErrNum;
> //accessor.m_ErrMsg;
> if (FAILED(olehr())) _com_issue_error(olehr());
> } // end try
> catch (_com_error &e)
> {
> char * descr = (char*) e.Description();
> char * msg = (char*) e.ErrorMessage();
> accessor.Close();
> }
>
> it seems that the sp fail make the rollback but not se any values into
> in/out parameters.
> Please help me......
