TUniQuery with very large string param causes an Access Violation

[elraj71]

Hi!

I use UniDAC vers. 7.1.4 (trial) for RAD Studio XE2, Delphi XE2 and Sql Server 2014 to write application.

In my application, I delete a record by TUniQuery and then I insert same record by TUniQuery.
This record has a string field (jsonparams) that it could be very large (size > 7000).
If param 'jsonparams' has very large value, command ExecSql causes an Access Violation.


A sample code that I used is:

Code: Select all

var
  qryDel, qryIns: TUniQuery;  
  jsonParams: TStringList;
begin
 jsonParams := TStringList.Create;
  try
    qryDel := TUniQuery.Create(nil);
    qryDel.Connection := SqlConnMssql;

    qryDel.SQL.Text := 'DELETE FROM zTMPJOBSRESULTS61';
    qryDel.ExecSql;

    //

    jsonParams.LoadFromFile(edtNomeFile.Text);

    qryIns := TUniQuery.Create(nil);
    qryIns.Connection := SqlConnMssql;

    qryIns.SQL.Text :=
      'INSERT INTO zTMPJOBSRESULTS61' +
      '(idutente, idjob, autore, datainizio, stato, dipendenti, giorni, attivita, jsonparams)     ' +
      '   VALUES (:idutente, :idjob, :autore, :datainizio, :stato, :dipendenti, :giorni, :attivita, :jsonparams) ';

    qryIns.ParamByName('idutente').AsInteger := 61;
    qryIns.ParamByName('idjob').AsInteger := 1;
    qryIns.ParamByName('autore').AsString := 'SWB';
    qryIns.ParamByName('datainizio').AsDateTime := Now;
    qryIns.ParamByName('stato').AsString := 'F';
    qryIns.ParamByName('dipendenti').AsString := '41,29,24,11,45,2,51,52,15,42,43,50,48,39,37,32,8,38,9,26,4,16,5,18,33,6,3,34,46,35,36,49,23,22,47,28,21,1,30,44,27,25,40,31,10,7';
    qryIns.ParamByName('giorni').AsString := '010917-300917';
    qryIns.ParamByName('attivita').AsString := 'ExpPaghe';
    qryIns.ParamByName('jsonparams').AsString := jsonParams.Strings[0];

    qryIns.ExecSql;

  finally
    jsonParams.Free;
    qryDel.Free;
    qryIns.Free;
  end;

why? Can I solve this problem?

Thanks in advance,
Federica

[azyk]

Please provide the script to create the zTMPJOBSRESULTS61 table.

[elraj71]

Please provide the script to create the zTMPJOBSRESULTS61 table.

CREATE TABLE zTMPJOBSRESULTS61 (
idutente INT NOT NULL,
idjob INT NOT NULL,
autore VARCHAR(3) NOT NULL,
risultato INT NULL,
datainizio DATETIME NOT NULL,
datafine DATETIME NULL,
stato VARCHAR(1) NULL,
dipendenti VARCHAR(4000) NULL,
giorni VARCHAR(4000) NULL,
attivita VARCHAR(20) NULL,
jsonparams VARCHAR(MAX) NULL,
CONSTRAINT PK_JOBSRESULTS_61 PRIMARY KEY CLUSTERED (IDUTENTE, IDJOB))

[azyk]

We tried to reproduce the specified issue according to your recommendations, however it was not reproduced. Please compose a small test sample, on which the issue is reproduced and send it using the contact form at our site:http://www.devart.com/company/contactform.html. In the sample, include the file with data to fill the jsonparams field.

[elraj71]

We tried to reproduce the specified issue according to your recommendations, however it was not reproduced. Please compose a small test sample, on which the issue is reproduced and send it using the contact form at our site:http://www.devart.com/company/contactform.html. In the sample, include the file with data to fill the jsonparams field.

I uploaded a sample.

[Stellar]

To specify a large text value in the query parameter, you can use the following statement:

qryIns.ParamByName('jsonparams').AsMemo := jsonParams.Text;

We reproduced the issue with Access violation, as soon as we get a result, we will let you know.