[6.2.8 / SQLite] Can not access a DML array as a single value

[swierzbicki]

Hello,

I'm not able to use batch insert with SQLite.
I'm getting this error " Can not access a DML array as a single value" when executing the insert query.

Code: Select all

function TCTMArticles.GetArticles: Boolean;
var
  str: string;
  ArticleList: TArticleList;
  Article: TArticle;
  ArticleDetail: TArticleDetail;
  i: Integer;
  InternalConnection: TUniConnection;
  cptArt, cptArtDet: Integer;
  qryArticles, qryDetails: TUniSQL;


procedure ArticleToParams(var AUniQuery: TUniSQL;
  const Article: TArticle; Index:Integer);
begin
  AUniQuery.Params[0][Index].AsString := Article.lib_art;
  AUniQuery.Params[1][Index].AsString := Article.gencod_art;
  AUniQuery.Params[2][Index].AsString := Article.code_art;
  AUniQuery.Params[3][Index].AsInteger := Article.colisage;
  AUniQuery.Params[4][Index].asfloat := Article.stock;
end;

procedure ArticleDetailToParams(var AUniQuery: TUniSQL;
  const Article: TArticle; const ArticleDetail: TArticleDetail; Index:Integer);
begin
  AUniQuery.Params[0][Index].AsString := Article.gencod_art;
  AUniQuery.Params[1][Index].AsString := Article.code_art;
  AUniQuery.Params[2][Index].AsString := ArticleDetail.gencod;
  AUniQuery.Params[3][Index].AsString := ArticleDetail.couleur;
  AUniQuery.Params[4][Index].AsString := ArticleDetail.taille;
  AUniQuery.Params[5][Index].AsInteger := ArticleDetail.nb_pcs;
end;

begin
  result := False;
  if not WebServiceAlive(FWebServiceHostName, FWebServicePort) then
    exit;

  ArticleList := TArticleList.Create;
  InternalConnection := TUniConnection.Create(nil);
  qryArticles := TUniSQL.Create(nil);
  qryDetails := TUniSQL.Create(nil);

  try
    str := GetCTMWebService(False, FWebServiceURL)
      .transfert(jsonStockArticlesGet, jsonStockArticles);
    ArticleList := TArticleList.FromJsonString(str);

    if (ArticleList.infodef.retnumbers = 0) or
      (ArticleList.infodef.errcode <> '') then
      exit;

    SetDefaultConnection(connection, InternalConnection);
    qryArticles.connection := InternalConnection;
    qryArticles.SQL.Text := sqlStockArticlesInsert;
    qryArticles.Params[0].DataType := ftString;
    qryArticles.Params[1].DataType := ftString;
    qryArticles.Params[2].DataType := ftString;
    qryArticles.Params[3].DataType := ftInteger;
    qryArticles.Params[4].DataType := ftFloat;
    qryArticles.Params.ValueCount := Length(ArticleList.articles);

    cptArtDet := 0;
    for Article in ArticleList.articles do
      cptArtDet := cptArtDet + Length(Article.artDetail);

    qryDetails.connection := InternalConnection;
    qryDetails.SQL.Text := sqlStockArticlesDetailInsert;
    qryDetails.Params[0].DataType := ftString;
    qryDetails.Params[1].DataType := ftString;
    qryDetails.Params[2].DataType := ftString;
    qryDetails.Params[3].DataType := ftString;
    qryDetails.Params[4].DataType := ftString;
    qryDetails.Params[5].DataType := ftInteger;
    qryDetails.Params.ValueCount := cptArtDet;

    cptArt:=0;
    cptArtDet := 0;

     for Article in ArticleList.articles do
      begin
        ArticleToParams(qryArticles,Article,cptArt);
        cptArt := cptArt +1;
        for ArticleDetail in Article.artDetail do
        begin
          ArticleDetailToParams(qryDetails,Article, ArticleDetail, cptArtDet);
          cptArtDet := cptArtDet + 1;
        end;
      end;


    InternalConnection.StartTransaction;
    try
      InternalConnection.ExecSQL(sqlStockArticleDelete);
      qryArticles.Execute(cptArt-1);
      qryDetails.Execute(cptArtDet-1);
      InternalConnection.Commit;
      result := True;
    except
      InternalConnection.Rollback;
    end;

  finally
    ArticleList.free;
    qryArticles.free;
    qryDetails.free;
    InternalConnection.free;
  end;

end;

[AlexP]

Hello,

We have already fixed this issue. Currently, to resolve the issue, you should disable UniSQLMonitor.

[swierzbicki]

This is not working, I've already tried to disable the Monitor.
I'm trying to insert 90000 + 20000 records. Could it be a problem ?

SQL statements are simple :

sqlStockArticlesInsert = 'INSERT INTO articles ' +
'(Id_Article, Libelle_Article ,Gencod_Article, Code_Article, Colisage, Stock) '
+ 'VALUES ' +
'(:Id_Article, :Libelle_Article, :Gencod_Article, :Code_Article, :Colisage, :Stock) ';

sqlStockArticlesDetailInsert = 'INSERT INTO articles_details ' +
' (Id_Article_detail, Gencod_Article, Code_Article, Gencod_Piece, Couleur, Taille, Nombre_Pieces) '
+ 'VALUES ' +
' (:Id_Article_detail, :Gencod_Article, :Code_Article, :Gencod_Piece, :Couleur, :Taille, :Nombre_Pieces) ';

[AlexP]

We can't reproduce the issue. Please edit the sample provide below, so that the issue is reproduced, and send it to us.

Code: Select all

var
  i: Integer;
begin
  UniSQLMonitor1.Active := False;
  UniConnection1.ExecSQL('CREATE TABLE DEPT (DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO))');
  UniConnection1.ExecSQL('CREATE TABLE EMP (EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, HIREDATE TIMESTAMP, SAL REAL, COMM REAL, DEPTNO INT REFERENCES DEPT, PRIMARY KEY (EMPNO))');
  UniQuery1.SQL.Text := 'INSERT INTO DEPT VALUES (:DEPTNO, :DNAME, :LOC)';
  UniQuery1.Params.ValueCount := 9000;
  UniQuery1.Params[0].ParamType := ptInput;
  UniQuery1.Params[0].DataType := ftInteger;
  UniQuery1.Params[1].ParamType := ptInput;
  UniQuery1.Params[1].DataType := ftString;
  UniQuery1.Params[2].ParamType := ptInput;
  UniQuery1.Params[2].DataType := ftString;

  for i:= 0 to 8999 do begin
    UniQuery1.Params[0][i].AsInteger := i;
    UniQuery1.Params[1][i].AsString := IntToStr(i);
    UniQuery1.Params[2][i].AsString := IntToStr(i);
  end;
  UniQuery1.Execute(9000);

  UniQuery1.SQL.Text := 'INSERT INTO EMP VALUES (:EMPNO, :ENAME, :JOB, :MGR, :HIREDATE, :SAL, :COMM, :DEPTNO)';
  UniQuery1.Params.ValueCount := 9000;
  UniQuery1.Params[0].ParamType := ptInput;
  UniQuery1.Params[0].DataType := ftInteger;
  UniQuery1.Params[1].ParamType := ptInput;
  UniQuery1.Params[1].DataType := ftString;
  UniQuery1.Params[2].ParamType := ptInput;
  UniQuery1.Params[2].DataType := ftString;
  UniQuery1.Params[3].ParamType := ptInput;
  UniQuery1.Params[3].DataType := ftInteger;
  UniQuery1.Params[4].ParamType := ptInput;
  UniQuery1.Params[4].DataType := ftDateTime;
  UniQuery1.Params[5].ParamType := ptInput;
  UniQuery1.Params[5].DataType := ftFloat;
  UniQuery1.Params[6].ParamType := ptInput;
  UniQuery1.Params[6].DataType := ftFloat;
  UniQuery1.Params[7].ParamType := ptInput;
  UniQuery1.Params[7].DataType := ftInteger;

  for i:= 0 to 1999 do begin
    UniQuery1.Params[0][i].AsInteger := i;
    UniQuery1.Params[1][i].AsString := IntToStr(i);
    UniQuery1.Params[2][i].AsString := IntToStr(i);
    UniQuery1.Params[3][i].AsInteger := i;
    UniQuery1.Params[4][i].AsDateTime := now;
    UniQuery1.Params[5][i].AsFloat := i;
    UniQuery1.Params[6][i].AsFloat := i;
    UniQuery1.Params[7][i].AsInteger := i;
  end;
  UniQuery1.Execute(2000);
end;