Android Question Remote Database Connect - Stored Procedure execution

mrumball

Member
Licensed User
Longtime User
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:-
B4X:
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:-
B4X:
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
B4X:
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
B4X:
java.sql.SQLException: Incorrect syntax near '@P0'.

Any ideas on what is going on?

Regards
 

mrumball

Member
Licensed User
Longtime User
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

B4X:
 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:-
B4X:
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:-

B4X:
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
 
Upvote 0
Top