Can't add into DA tables

Data Abstract
1

Hi,
I have SP that like as below and it has the update statement at the beginning of SP. I can’t add it into DA tables that return the error. I need to add to DA Command. How to get the result from DA Command?

select @ReturnPrefix + right(replicate(‘0’,4) + cast(@MaxNbr as varchar(4)),4) as FrmNbr

daerror

My Coding as below.
lp := aParam.Add; lp.Name := ‘Company’;lp.Value := ‘YY’;
lp := aParam.Add; lp.Name := ‘TxnType’;lp.Value := ‘PTAR’;
lp := aParam.Add; lp.Name := ‘IncNbr’;lp.Value := edt_IncNbr.Value;
ClientDataModule.RemoteCommand.Execute(‘sp_GetMaxRangeNum’,aParam);

2

Hi,

What should return this command - value or recordset?
Will it work , if you execute it in SQL Server Management Studio and with pure SDAC (w/o DA)?

3

Hi,
SP will return the value. It works in SDAC’s TMSStoredProc without problem.

4

Hi,

as a workaround - you can specify parameters for this command manually.

Can you provide a DDL script for this procedure, pls?
We want to reproduce this error locally.
you can drop email to support@ for keeping privacy.

5

Hi,

Here is the SP content.

Thanks & regards,

Timothy

(Attachment sp_GetMaxRangeNum.sql is missing)

6

Hi,

Here is the SP content.

Thanks & regards,

Timothy

sp_GetMaxRangeNum.zip (561 Bytes)

7

Hi,

if you drop [dbo].[sp_GetMaxRangeNum] to Commands it should create command w/o any issues.
also you can declare FrmNbr & ToNbr as output parameters of SP.
see more at CREATE PROCEDURE (Transact-SQL) - SQL Server | Microsoft Docs

8

Hi,
Yes, I done it before. But don’t know how to get the return values from “Commands”.

9

Hi,

if parameters are declared as output ones, you can get them w/o any issues:

 //client-side call
 DARemoteCommand1.Execute('myCommand', inputParams, OutputParams);

 //server-side call
 DALocalCommand1.Execute('myCommand', inputParams, OutputParams);
10

Hi,
I tried the following code that doesn’t update the record. “edt_IncNbr” is integer and it should return the number range. But it only append the blank record with 0 number. Am I need to create the parameter name manually in “Commands”?

    lp := aParam.Add; lp.Name := 'Company';lp.Value := 'YY';
    lp := aParam.Add; lp.Name := 'TxnType';lp.Value := 'PTAR';
    lp := aParam.Add; lp.Name := 'IncNbr';lp.Value := edt_IncNbr.EditingValue;
    ClientDataModule.RemoteCommand.Execute('sp_GetMaxRangeNum',aParam, outParam);
    edt_frmnbr.Text := outParam.Items[0].Value;
    edt_tonbr.Text := outParam.Items[1].Value;
11

Hi,

try to specify correct type for that parameter like

lp.DataType := datInteger;
lp.AsInteger := edt_IncNbr.EditingValue;
12

Hi,
I use the following code that report the error of “Undeclared identifier: 'DataType”

lp := aParam.Add; lp.Name := ‘IncNbr’;lp.DataType := datInteger; lp.AsInteger := edt_IncNbr.EditingValue;

13

Hi,

You are right. I’ve mixed TDAParam and DataParameter types.
Can you check values in OnBeforeExecuteCommand event on server-side?

14

Hi,
Sorry that I don’t know how to check the values in OnBeforeExecuteCommand event on server-side. At now, it hasn’t any code in there.

15

Hi,

something like

procedure TDataService.DataAbstractServiceBeforeExecuteCommand(aSender: TObject;
  const aCommand: IDASQLCommand);
begin
  yyy := aCommand.ParamByName('xxx').Value;
end;
16

Hi,
I debug it returns the correct parameter value.

17

Hi,

try to use SQL Profiler and see what is wrong with your SP.

18

Hi,
It sent the correct command to SQL as below.

exec dbo.sp_GetMaxRangeNum ‘YY’,‘PTAR’,3

sql
sql747×99 12.9 KB

19

Hi,

can you check values of output parameters in the OnAfterExecuteCommand event on server-side?

20

Hi,
I created the out parameters that has the following error. The out parameters will not create by itself after I drop the SP into “Commands”.

output
output1
output2