Batch Insert with .DataType := ftFMTBcd

[raller09]

Hi,

i'm using Delphi 10.3, Unidac 8.1.2, Firebird 3.0;

How do i use ftFMTBCD fields with batch insert?

1 Ds will work (with a direkt "insert" statement with " bigint(*, -6)"?)
With more then 1 DS i get a Sql error.
Precision and Size are both "0",

Code: Select all

UniConnection1.ConnectString := 'Provider Name=InterBase;Data Source=localhost;Database=D:\Datenbank\BATCHFMTBCDWRITE.FDB;Port=3052;User ID=SYSDBA;Password=MASTERKE;Client Library=D:\Datenbank\_Firebird-3.0.4.33054-0_Win32\fbclient.dll;Use Unicode=True';
UniConnection1.EnableBCD := True;
UniConnection1.EnableFMTBCD := True;
UniConnection1.SpecificOptions := ...;InterBase.SimpleNumericMap=True;...
no Data Type Mapping

Code: Select all

procedure TForm1.Populate( const xRowCount: Integer);
var
  lQuery: TUniQuery;
  i: Integer;
  lValue: Double;
const
  cSqlStKompLagerWirkstoffe = 'Insert into BCDTEST ' +
                                  '( TESTFMTBCD, TESTBCD) ' +
                              'values ( :TESTFMTBCD, :TESTBCD);';
begin
  if ( xRowCount > 0) then
  begin
    Randomize;

    lQuery        := nil;
    try
      lQuery := TUniQuery.Create( nil);
      lQuery.Connection := UniConnection1;

      lQuery.Close;
      lQuery.SQL.Text := cSqlStKompLagerWirkstoffe;

      lQuery.Params[ 0].DataType  := ftFMTBcd;
      lQuery.Params[ 0].Precision := 18;
      lQuery.Params[ 0].Size      :=  6;

      lQuery.Params[ 1].DataType := ftFloat;

      lQuery.Params.ValueCount := xRowCount;

      lValue := Pi;

      for i := 0 to xRowCount - 1 do
      begin

        lQuery.Params[  0][ i].AsFmtBCD  := lValue;
        lQuery.Params[  1][ i].AsFloat   := lValue;
      end;

      lQuery.Execute( lQuery.Params.ValueCount);
    finally
      FreeAndNil( lQuery);
    end;
  end;
end;

Code: Select all

procedure TForm1.btn_Populate1DSClick( Sender: TObject);
begin
  Populate( 1);
end;

works:

Code: Select all

2020-01-07T13:55:30.2410 (4804:0000000003AD0040) EXECUTE_STATEMENT_START
    D:\DATENBANK\BATCHFMTBCDWRITE.FDB (ATT_18, SYSDBA:NONE, UNICODE_FSS, TCPv6:::1/56346)
    D:\DelphiProjekte\Testprogramme\UniDAC\Support\BatchFmtBCDWrite\Debug\Win32\BatchFmtBCDWrite.exe:10736
        (TRA_62, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Statement 31:
-------------------------------------------------------------------------------
Insert into BCDTEST ( TESTFMTBCD, TESTBCD) values ( ?, ?)

param0 = bigint(*, -6), "3.141593"
param1 = bigint(*, -4), "3.1416"

Code: Select all

procedure TForm1.btn_Populate2DSClick( Sender: TObject);
begin
  Populate( 2);
end;

don't work:

Code: Select all

Dynamic SQL Error
SQL error code = -842
Precision must be from 1 to 18.

->

2020-01-07T13:55:43.8950 (4804:0000000003AD0040) FAILED PREPARE_STATEMENT
    D:\DATENBANK\BATCHFMTBCDWRITE.FDB (ATT_18, SYSDBA:NONE, UNICODE_FSS, TCPv6:::1/56346)
    D:\DelphiProjekte\Testprogramme\UniDAC\Support\BatchFmtBCDWrite\Debug\Win32\BatchFmtBCDWrite.exe:10736
        (TRA_63, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

-------------------------------------------------------------------------------
EXECUTE BLOCK (p0 NUMERIC(0,0) = ?,p1 DOUBLE PRECISION = ?,p2 NUMERIC(0,0) = ?,p3 DOUBLE PRECISION = ?) AS BEGIN Insert into BCDTEST ( TESTFMTBCD, TESTBCD) values ( :p0, :p1);Insert into BCDTEST ( TESTFMTBCD, TESTBCD) values ( :p2, :p3); END
      0 ms

with database:

Code: Select all

SET SQL DIALECT 3;

SET NAMES UTF8;

SET CLIENTLIB 'D:\Datenbank\_Firebird-3.0.4.33054-0_Win32\fbclient.dll';

CREATE DATABASE 'LOCALHOST/3052:D:\Datenbank\BatchFmtBCDWrite.fdb'
USER 'SYSDBA' PASSWORD 'MASTERKE'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;



/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR BCDTEST_GEN START WITH 0 INCREMENT BY 1;
SET GENERATOR BCDTEST_GEN TO 0;


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE BCDTEST (
    BCDID                 INTEGER NOT NULL,
    TESTFMTBCD            DECIMAL(18, 6),
    TestBCD               Decimal(18, 4)
);

COMMIT WORK;



/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE BCDTEST ADD CONSTRAINT BCDTEST_PK PRIMARY KEY (BCDID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: BCDTEST_TBI0 */
CREATE OR ALTER TRIGGER BCDTEST_TBI0 FOR BCDTEST
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if ( new.BCDID is NULL) then
    new.BCDID = Gen_Id( BCDTEST_gen, 
                        1);
end
^

SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/


/* Privileges of users */
GRANT ALL ON BCDTEST TO PUBLIC;
GRANT USAGE ON SEQUENCE BCDTEST_GEN TO PUBLIC;

What do I have to do?

Thank you.

[ViktorV]

Thank you for the information. We have reproduced the issue and investigation is in progress. We will inform you when we have any results.

[ViktorV]

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