11 Replies Latest reply: Oct 8, 2010 12:15 PM by rschepis RSS

    CCA Stored Procedure

    736634
      Hi,

      My DBA created a SQL stored procedure for me to process payments over the IVR, when I gather customer's data and then send to stored procedure I get an error, has some one done this with CCA?

      I have this info
      exec utility.dbo.ProcessCreditCardPaymentV2EnteredBy 43042, '4111111111111111', 2, 13, '378', 'John', 'Smith', 1, '94606', False, True

      where utility.dbo.ProcessCreditCardPaymentV2EnteredBy is the location of the Stored procedure, and all others are the values sent to it. I also tried it using the CCA format shown in a documentation oracle has on their support page, but I get a Fail Status either way I try it.

      EXEC {utility.dbo.ProcessCreditCardPaymentV2EnteredBy (43042, '4111111111111111', 2, 13, '378',  'John', 'Smith', 1, '94606', False, True)}

      Thank you!
        • 1. Re: CCA Stored Procedure
          rschepis
          EXEC NameOfYourProc ?InputParameter1, ?InputParameter2, $ReturnVariable1, $ReturnVariable2

          Note: Your Sproc will need to accept all 4 parameters and ignore the last 2 as they are only place holders for the return variables
          • 2. Re: CCA Stored Procedure
            736634
            Thanks a lot, I will use this and let you know of the outcome.
            • 3. Re: CCA Stored Procedure
              736634
              Thanks for the info, I tested with provided code
              EXEC NameOfYourProc ?InputParameter1, ?InputParameter2, $ReturnVariable1, $ReturnVariable2

              and it looks like my SP isn't accepting the return variables, in your ecperience is thi somethign we have to modify at the DB level or CCA?
              • 4. Re: CCA Stored Procedure
                736634
                Hi again,

                based on the way the stored procedure is designed it looks like I'll have to declare varaibles, execute SP and select variable results.

                Does any one have an idea of how to translate this to CCA sql acceptable syntax?

                DECLARE @WasSuccess bit, @Message nvarchar(200), @ConfirmationNumber int
                exec spocName ?CustAccount, ?CreditCardNbr, ?ExoMonth,?ExpYear,?CSC,?etc,@WasSuccess = @WasSuccess OUTPUT, @Message = @Message OUTPUT, @ConfirmationNumber = @ConfirmationNumber OUTPUT Select @WasSuccess, @Message, @ConfirmationNumber
                • 5. Re: CCA Stored Procedure
                  rschepis
                  List out the following...


                  Input parameter list SPROC is expecting to be passed in:




                  Output parameters SPROC returns when complete:
                  • 6. Re: CCA Stored Procedure
                    736634
                    Input parameter list SPROC is expecting to be passed in:

                    ?AccNbr, ?CC, ?CCMM, ?CCYY, ?CSC, ?FstNm, ?LstNam, ?Amnt, ?Zip, ?Recur, ?Test



                    Output parameters SPROC returns when complete:

                    @WasSuccess, @Message, @ConfirmationNumber
                    • 7. Re: CCA Stored Procedure
                      rschepis
                      Ensure your DB guy has added 3 dummy, fake, input placeholders parameters to the Sproc. CCA requires that you send in the placeholders for output.

                      The Sproc should completely ignore and take no action on those 3 extra input parameters.

                      Then use this syntax exactly in CCA:

                      EXEC ProcessCreditCardPaymentV2EnteredBy ?AccNbr, ?CC, ?CCMM, ?CCYY, ?CSC, ?FstNm, ?LstNam, ?Amnt, ?Zip, ?Recur, ?Test, $WasSuccess, $Message, $ConfirmationNumber


                      As with any CCA to DB action, you should always build in a short delay e.g. play a 1 second silence WAV prompt (or better build a loop to check the SQLStatus variable) after sending it.

                      Then play back your variables which will be:

                      $WasSuccess, $Message and $ConfirmationNumber
                      • 8. Re: CCA Stored Procedure
                        736634
                        That is exactly how I ran it based on the info you provided on first reply, does this mean we need to have our DB guys change something in theri end? I should've mentioned this before, but the DB we have is not an oracle DB, it is MS Server 2005.
                        • 9. Re: CCA Stored Procedure
                          rschepis
                          That is correct.

                          Change the sproc.

                          For them to work with CCA, the Sproc needs to be looking for x number of dummy parameters.

                          Where x = the number of output parameters.

                          Have the Sproc completely ignore those extra input parameters...CCA sends them but you have to live with it or not use Sprocs with CCA.

                          Yes SQL2005, correct that is what you need to change.

                          Then it should work happy with CCA.
                          • 10. Re: CCA Stored Procedure
                            736634
                            Hi again, would you by any chance have some information/documentation on how the spoc should be set up? My DBA guys have been assisting me on this and they created a procedure that requires me to declare the return variables in CCA not only as $Returned variable, but as DECLARE @ReturnedVar1, etc..

                            CCA doesn't like that and fails even when the code works just fine in MS SQL 2005 server. They've changed the spoc a few times now but seem to not be able to simply ignore $returnvars and only populate them on return of execution of spoc.

                            Thanks a lot!
                            • 11. Re: CCA Stored Procedure
                              rschepis
                              Run this script in your DB:

                              /****** Put in your DB name here ******/
                              USE [CC813]
                              GO


                              SET ANSI_NULLS ON
                              GO

                              SET QUOTED_IDENTIFIER ON
                              GO


                              CREATE PROCEDURE [dbo].[usp_GetUser_Info_By_Username]
                              (     @UserName varchar(50),
                                   @FirstName varchar(50),
                                   @LastName varchar(50),
                                   @PhoneNumber varchar(50)
                              )
                              /****** FirstName , LastName and PhoneNumber are just place holder from CCA
                                   CCA will ALWAYS pass in placeholder parameters for the return variables
                                   the SPROC should just ignore these as this sample does. ******/
                              AS

                              BEGIN

                                   SET NOCOUNT ON;

                                   DECLARE @UserID Varchar(25)

                                   Select FirstName, LastName, Comm as PhoneNumber from users where username = @UserName and companyid = 1 and isdeleted = 0
                                   
                              END


                              GO




                              /*

                              Then in CCA Campaign call it like this:

                              EXEC usp_GetUser_Info_By_Username ?userName, $FirstName, $LastName, $PhoneNumber


                              --> NOTE in this sample the only thing the SPROC cares about is userName.
                              --> NOTE 2 be sure the username exists for company 1 or change the companyid
                              --> NOTE 3 be sure you have garneted execute rights to the SPROC for the ODBC DSN user you have defined.



                              */