Stored Procedure in BatchExecute crashes

[OlliWW]

Hi,

we are using SDAC 10.1.1 at the moment. We already discoverd the Bug in 9.4.3. If you use a stored procedure in a BatchExec with an insert statement, it crashes with:

inorrect Syntax near ")"

update statements are working fine. Also only crashes if rowcount > 1. With 1 row it works, too.

Could you assist me, to resolve this issue?

Greetings from Germany


Code:

Code: Select all

unit SDAC;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBAccess, SdacVcl, Data.DB, MSAccess;

type
  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
  private
    MSConnection1: TMSConnection;
    MSConnectDialog1: TMSConnectDialog;
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const
  cTempTableName = '#BatchTemp';

procedure TForm1.FormCreate(Sender: TObject);
var
  qryExec: TMSQuery;
  objParam: TMSParam;
begin
  MSConnection1.Connect;

  // Create Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text :=
      'create table ' + cTempTableName + ' ( ' +
        '_GEN INT IDENTITY(1, 1) primary key, ' +
        '_COL_STRING varchar(max), ' +
        '_COL_DATE datetime ' +
      ')';
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;


  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    //##### Query only crashes by using insert (update works)
    qryExec.SQL.Text := 'insert into ' + cTempTableName + ' (_COL_DATE, _COL_STRING) values (getDate(), :STRING) ';

    qryExec.Params.ValueCount := 1;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[0].Value := 'Hello';

    qryExec.Params.ValueCount := 2;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[1].Value := 'World';

    qryExec.Params.ValueCount := 3;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[2].Value := 'Foobar';

    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;


  // Drop Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text := 'drop table ' + cTempTableName;
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;
end;

end.

[pavelpd]

Hi

Thank you for the info provided!
We have reproduced the issue and we are currently investigating its origin.
We will inform you about the results shortly.

[pavelpd]

Hi!

We've reproduced the issue and fixed it. The fix will be included in the next build of our product.
As a workaround, we can send you a nightly UniDAC build including the required changes.
Please specify your license number, IDE version and send us via this contact form: https://www.devart.com/company/contactform.html