Android Question Remote Database Connect - Stored Procedure execution

[mrumball]

Hi,
I am using the RDC to connect to MS SQL Server 2008, I have created a stored procedure with an input value and an output value.

Procedure is detailed below:-

ALTER PROCEDURE dbo.sp_Increment_CustomSerialNumber (
    @OwnerCode varchar(10),
    @Number int OUTPUT
)
as
BEGIN
--insert procedure body here
set @Number = (SELECT Serial from TblCustomerCustomSerialNumbers WHERE OwnerCode = @OwnerCode)

--inc the value
set @Number +=1

Update TblCustomerCustomSerialNumbers SET Serial = @Number WHERE OwnerCode = @OwnerCode
END

The corresponding entry in the commands section of the config file is below:-

sql.kitting_getCustomDigits=DECLARE @return_value int,@Number int ;EXEC    @return_value = sp_Increment_CustomSerialNumber    @OwnerCode = N?,@Number = @Number OUTPUT; SELECT @Number as N'Number'

The stored procedure runs fine when executed in sql server manager

DECLARE    @return_value int,@Number int ;EXEC    @return_value = sp_Increment_CustomSerialNumber    @OwnerCode = N'ABB',@Number = @Number OUTPUT; SELECT    @Number as N'Number'

but i get the following error message in the B4a log when executing

java.sql.SQLException: Incorrect syntax near '@P0'.

Any ideas on what is going on?

Regards

 

[Erel]

The problem is probably because of the multiple statements. Try to break it to single statements and then call ExecuteBatch.

 

[mrumball]

The problem is probably because of the multiple statements. Try to break it to single statements and then call ExecuteBatch.

Hi Erel,

I have tried to run the commands as 3 separate statements in a batch as follows

 For Each Serialnumber In CodeMod1.SerialNumberList
          Dim CommandsList As List
            CommandsList.Initialize
   
    Dim cmd As DBCommand
            cmd.Initialize
            cmd.Name = "kitting_getCustomDigits1"
    'Dim test1 As String = CodeMod1.OrderArray(15)
            cmd.Parameters = Array As Object(Null)
            CommandsList.Add(cmd)
           
           
          Dim cmd As DBCommand
            cmd.Initialize
            cmd.Name = "kitting_getCustomDigits2"
    Dim test1 As String = CodeMod1.OrderArray(15)
            cmd.Parameters = Array As Object(CodeMod1.OrderArray(15))
            CommandsList.Add(cmd)
           
           
            Dim cmd As DBCommand
            cmd.Initialize
            cmd.Name = "kitting_getCustomDigits3"
    'Dim test1 As String = CodeMod1.OrderArray(15)
            cmd.Parameters = Array As Object(Null)
            CommandsList.Add(cmd)
           
           
         
         
          Next
          reqManager.ExecuteBatch(CommandsList, "GCD")

the entries in the config file are as follows:-

sql.kitting_getCustomDigits1=DECLARE @return_value int,@Number int
sql.kitting_getCustomDigits2=EXEC @return_value = sp_Increment_CustomSerialNumber @OwnerCode = N?,@Number = @Number OUTPUT
sql.kitting_getCustomDigits3=SELECT @Number as N'Number'

Unfortunately is does not seem to recognise the 1st statement 'Declare' ... the error message i am getting is:-

java.sql.SQLException: Must declare the scalar variable "@return_value".

I get the same error if i omit the 1st statement in sql manager when testing


much appreciate your help

Regards

Martin

 

[Erel]

Sorry but I don't know if it is possible to run such procedure. You can try to combine the multiple commands to a single command.