Could you please help me solve this problem:

-
Could you please help me solve this problem:

// FireDAC + Array DML:

          FDQuery.SQL.Text := 'INSERT INTO ' + LoopTXTDoc.Key + '(BaseNum) VALUES (:BN)';
          FDQuery.Params[0].DataType := ftInteger;
          FDQuery.Params.ArraySize := LoopTXTDoc.Value.Count;
          i := 0;
          for LoopBaseNum in LoopTXTDoc.Value do
          begin
            FDQuery.Params[0].AsStrings[i] := LoopBaseNum;
            inc(i);
          end;
          FDQuery.Execute(FDQuery.Params.ArraySize);

where:
ArrayDMLSize = 450 (because SQLite limit = 500)
LoopTXTDoc.Value.Count = FDQuery.Params.ArraySize = 7123

But this code don't fill the Params array. It's length = 1 and I get List index out of bounds (1). What's the problem?


UPD: example: http://www46.zippyshare.com/v/WAA50dDs/file.html
it's changed demo from here http://cc.embarcadero.com/item/29832

Comments

  1. N. PravdinAugust 15, 2015 at 6:53 AM

    Also:
    I use Delphi XE8 update 1 + SQLite.
    SQL table: AFDConn.ExecSQL('CREATE TABLE ' + LoopTXTDoc.Key + ' (BaseNum INTEGER UNIQUE);');
    Options: FDQuery.Params.BindMode := pbByNumber;

    ReplyDelete
  2. N. PravdinAugust 15, 2015 at 6:57 AM

    And this code works pretty good:

    for LoopBaseNum in LoopTXTDoc.Value do
    begin
      AFDConn.ExecSQL('INSERT INTO ' + LoopTXTDoc.Key + '(BaseNum) VALUES (' +  LoopBaseNum + ');');
    end;

    ReplyDelete
  3. Dany MarmurAugust 15, 2015 at 9:55 AM

    I don't use FD, but it seems strange to have a param of type int and use it as an array. The resulting SQL would be strange. The argument inside the brackets should be one value per field. Not per insert.

    ReplyDelete
  4. N. PravdinAugust 15, 2015 at 10:00 AM

    Dany Marmur Hmm... This is official documentation: http://docwiki.embarcadero.com/RADStudio/XE8/en/Array_DML_%28FireDAC%29#Command_execution

    And this is my sample code:

      FDQuery1.Connection.StartTransaction;
      try
        FDQuery1.Params.BindMode := pbByNumber;
        FDQuery1.ResourceOptions.ArrayDMLSize := 150;
        FDQuery1.Connection.ResourceOptions.ArrayDMLSize := 150;

        FDQuery1.Connection.ExecSQL('DROP TABLE IF EXISTS tbl;');
        FDQuery1.Connection.ExecSQL('CREATE TABLE tbl(BaseNum INTEGER UNIQUE);');

        FDQuery1.SQL.Text := 'INSERT INTO tbl (BaseNum) VALUES (:BN)';
        FDQuery1.Params[0].DataType := ftInteger;
        FDQuery1.Params.ArraySize := NUM_INSERTS;
        for i := 0 to NUM_INSERTS - 1 do
        begin
          FDQuery1.Params[0].AsIntegers[i] := i;
        end;
        FDQuery1.Execute(NUM_INSERTS, 0);

        FDQuery1.Connection.Commit;
      except
        FDQuery1.Connection.Rollback;
        raise;
      end;

    I get: Exception class EListError with message 'List index out of bounds (1)'.

    ReplyDelete
  5. Dany MarmurAugust 15, 2015 at 12:13 PM

    "FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
    // here FDQuery1.Params collection is filled by 3 parameters
    FDQuery1.Params.ArraySize := 100;
    for i := 0 to 100-1 do begin
      FDQuery1.Params[0].AsIntegers[i] := i;
      FDQuery1.Params[1].AsStrings[i] := 'qwe';
      FDQuery1.Params[2].Clear(i);
    end;"

    OK, there's definitely lots of automagic at work here :) . So, i'm not the guy to comment on this. Apologies, apoplectic. But it says you have to define the array size first. I'm not at a keyboard so i can not check details and i can not browse your last code in a satisfactory way.

    Good luck

    ReplyDelete
  6. Dany MarmurAugust 16, 2015 at 12:42 AM

    Do you get the error in the loop or at the execute command?

    Just reading the docs i can not find a mistake in your code.

    If you can, compile with debug info and debug units. Tell the debugger to stop on the exception and look at the call stack. Then you'll know where to put breakpoints in order to hunt the problem down.

    ReplyDelete
  7. N. PravdinAugust 18, 2015 at 8:54 PM

    Dany Marmur
    This is a bug in FireDAC SQLite driver. There are two workarounds:
    * do not use pbByNumber, but that will disable native array DML support;
    * do not set ResourceOptions.ArrayDMLSize, but you will need to keep eyes open when you will have large array size.
    (c)  Dmitry Arefiev     https://forums.embarcadero.com/message.jspa?messageID=705781#705781

    ReplyDelete

Post a Comment