Error processing batch operations with manual parameters (ParamCheck = False)

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
stivalet
Posts: 4
Joined: Sat 26 Jan 2019 17:19

Error processing batch operations with manual parameters (ParamCheck = False)

Post by stivalet » Sat 26 Jan 2019 18:25

Hi my name is Hector, in our company (an ERP in Mexico) we are migrating from another DAC to UniDAC v7.4.

We have a solution with the previous DAC since 2012 and we are trying to make a seamless migration to UniDAC.

While I was doing my testings on batch operations, I found an error executing a batch using manual parameters:

Our UniQuery objects have the property ParamCheck always set to false, this is because we always create the query parameters manually. So executing the next code (based on the help topic Bacth Operations) returns an access violation:

Code: Select all

procedure TFTest_DataAccessComponents.Button1Click(Sender: TObject);
var
   i, smallint: Integer;
   UniQueryTest: TUniQuery;
begin
   StartTest(UniConnection1);
   UniConnection1.StartTransaction;
   UniQueryTest:= nil;
   try
      // creates a UniQuery object and defines it's properties
      UniQueryTest := TUniQuery.Create(Self);
      with UniQueryTest do begin
         Name := 'UniQueryTest';
         Connection := UniConnection1;
         CachedUpdates := True; // Important setting in our project. This values can't be changed
         ParamCheck:= False; // Important setting in our project. This values can't be changed. We always define the params manually. Note: When this values is true, the batch insertion works
      end;
      // describe the SQL query
      UniQueryTest.Close;
      UniQueryTest.Params.Clear;
      UniQueryTest.SQL.Clear;
      // The SQL text is generated the same way by a function that can't be changed
      UniQueryTest.SQL.Add('INSERT INTO _UniDacDemo ("ID", "F_INTEGER", "F_FLOAT", "F_STRING", "F_DATETIME", "F_SMALLINT", "F_MEMO", "F_CURRENCY", "F_DATE", "F_BOOLEAN")');
      UniQueryTest.SQL.Add('VALUES (:F1, :F2, :F3, :F4, :F5, :F6, :F7, :F8, :F9, :F10)');

      // definition of the parameter types passed to the query :
      {UniQuery1.Params.CreateParam(ftInteger, 'ID', ptInput);}
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params.Add;
      UniQueryTest.Params[0].DataType := ftInteger;
      UniQueryTest.Params[1].DataType := ftInteger;
      UniQueryTest.Params[2].DataType := ftFloat;
      UniQueryTest.Params[3].DataType := ftString;
      UniQueryTest.Params[4].DataType := ftDateTime;
      UniQueryTest.Params[5].DataType := ftSmallint;
      UniQueryTest.Params[6].DataType := ftMemo;
      UniQueryTest.Params[7].DataType := ftCurrency;
      UniQueryTest.Params[8].DataType := ftDate;
      UniQueryTest.Params[9].DataType := ftBoolean;

      // specifies the array dimension:
      UniQueryTest.Params.ValueCount := 1000;

      smallint := 1000;
      // populates the array with parameter values:
      for i:= 0 to UniQueryTest.Params.ValueCount - 1 do begin
         smallint := smallint + 1000 + 10;
         if smallint > 32767 then
            smallint:= 1;
         UniQueryTest.Params[0][i].AsInteger := i + 1;
         UniQueryTest.Params[1][i].AsInteger := i + 2000 + 1;
         UniQueryTest.Params[2][i].AsFloat := (i + 1) / 12;
         UniQueryTest.Params[3][i].AsString := 'Values ' + IntToStr(i + 1);
         UniQueryTest.Params[4][i].AsDateTime := Now;
         UniQueryTest.Params[5][i].AsSmallInt := smallint;
         UniQueryTest.Params[6][i].AsMemo := 'Memo ' + IntToStr(i + 1);
         UniQueryTest.Params[7][i].AsCurrency := (i + 100) / 11;
         UniQueryTest.Params[8][i].AsDate := Date;
         UniQueryTest.Params[9][i].AsBoolean := (i mod 2 = 0);
      end;//for

      // insert 1000 rows into the BATCH_TEST table
      UniQueryTest.Execute(1000);

      UniConnection1.Commit;
   except on E: Exception do begin
         if UniConnection1.InTransaction then
            UniConnection1.Rollback;
         ShowMessage(e.Message);
      end;//Exception
   end;//try-except
   if Assigned(UniQueryTest) then
      FreeAndNil(UniQueryTest);
end;
Changing the param check to true works well but we can not change this setting in this moment. The previous DAC operates well in both paramCheck cases.

Also this may be not concern of my expertise but I found that skipping the line 8 of this portion of code contained in the uni.pas unit allows to run the batch without errors with ParamCheck set to false:

Code: Select all

procedure TUniSQL.CheckUniMacros;
var
  UsedCon: TUniConnection;
begin
  if EnableUniSQL then begin
    UsedCon := TUniConnection(UsedConnection);
    // values here are UsedCon not null, FMacrosVersion = 0, UsedCon.FMacrosVersion = 0
    if (UsedCon <> nil) and (FMacrosVersion < UsedCon.FMacrosVersion) then begin
      // jumping the previous if statement while debugging executes the batch operation correctly
      if FICommand <> nil then begin
        SetICommandSQL;
        WriteParams;
      end;
      FMacrosVersion := UsedCon.FMacrosVersion;
    end;
  end;
end;
I hope you all can help us to find a solution to this problem. Thank you very much in advance.

Cheers.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Post by azyk » Tue 29 Jan 2019 11:55

Hi, Hector

Please provide the following additional information:

- exact version of the DBMS you are using;
- SQL script for creation of the _UniDacDemo table.

stivalet
Posts: 4
Joined: Sat 26 Jan 2019 17:19

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Post by stivalet » Tue 29 Jan 2019 23:31

Hello Azyk,

here is the information you had requested:

DBMS: Microsoft SQL Server Enterprise (64-bit) 2014
Version: 12.0.2000.8
OS: Microsoft Windows NT 6.1 (7601) 64 bit
Collation: Latin1_General_CI_AS

DB: SireDB
Collation: Latin1_General_CI_AS

Table Script:

Code: Select all

CREATE TABLE [dbo].[_UniDacDemo](
	[ID] [int] NULL,
	[F_INTEGER] [int] NULL,
	[F_FLOAT] [float] NULL,
	[F_STRING] [char](50) collate LATIN1_GENERAL_BIN NULL,
	[F_DATETIME] [datetime] NULL,
	[F_SMALLINT] [smallint] NULL,
	[F_MEMO] [varchar](max) collate LATIN1_GENERAL_BIN NULL,
	[F_CURRENCY] [money] NULL,
	[F_DATE] [date] NULL,
	[F_BOOLEAN] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
additional info:
Rad Studio version: XE7
UuniDac version: 7.4.11

thank you

stivalet
Posts: 4
Joined: Sat 26 Jan 2019 17:19

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Post by stivalet » Sat 02 Feb 2019 00:21

Hello guys. Any word on this?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Post by Stellar » Tue 05 Feb 2019 07:27

Thank you for the information. We have fixed the issue, and the fix will be included in the next UniDAC build.

stivalet
Posts: 4
Joined: Sat 26 Jan 2019 17:19

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Post by stivalet » Wed 06 Feb 2019 18:45

Thanks a lot Stellar. I'll wait for that update and let you know the results.

One more thing. Can I ask you the estimated release date?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Post by Stellar » Tue 12 Feb 2019 14:06

At the moment, we cannot announce the exact release date of the next UniDAC build. As a workaround, we can send you a night UniDAC build including the required changes. For this, using e-support form (https://www.devart.com the "Support"\"Request Support" menu) provide us with your license number and IDE version you are interested in.

Post Reply