Devart Forums

viniciusfbb

I've tried several different ways, it really seems to be a MyDAC error/limitation. Please investigate this as soon as possible.

viniciusfbb

Hello, in the last few years I started to use many stored procedures and it always works well when it returns results using output parameters or internally using SELECT without INTO.
However, there are cases where I feel the need to return both at the same time, that is, return a list of results (SELECT without INTO) and return output parameters at the same time (this is perfectly possible, even dbForge works perfectly , displaying the output parameters and the resulting select at the same time). However, in this case I can't capture the output parameter via MyDAC, I get the message that it doesn't exist. Maybe I'm not doing it the right way, so I bring my code just below:

Stored procedure:

Delphi code:

Code: Select all

function CreateStoredProc(const AStoredProcedureName: string): TMyStoredProc;
begin
  Result := TMyStoredProc.Create(nil);
  try
    Result.Options.FieldsAsString := False;
    Result.Options.EnableBoolean := False;
    Result.Options.BinaryAsString := False;
    Result.Connection := Connection;
    Result.StoredProcName := AStoredProcedureName;
    Result.PrepareSQL;
  except
    FreeAndNil(Result);
    raise;
  end;
end;

procedure GetAccountsSummarized(const ASearchText: string; const ALimitOffset, ALimitRowCount: Cardinal; out ATotalRows: Cardinal; out ASelectResults: TArray<TipGetAccountsSummarizedResult>);
var
  LSelectResults: TList<TipGetAccountsSummarizedResult>;
  LSelectResultsItem: TipGetAccountsSummarizedResult;
  LGetAccountsSummarizedStoredProc: TMyStoredProc;
begin
  LGetAccountsSummarizedStoredProc := CreateStoredProc('get_accounts_summarized');
  LSelectResults := TList<TipGetAccountsSummarizedResult>.Create;
  try
    LGetAccountsSummarizedStoredProc.ParamByName('in_search_text').AsString := ASearchText;
    LGetAccountsSummarizedStoredProc.ParamByName('in_limit_offset').AsLongWord := ALimitOffset;
    LGetAccountsSummarizedStoredProc.ParamByName('in_limit_row_count').AsLongWord := ALimitRowCount;
    LGetAccountsSummarizedStoredProc.Execute;
    try
      ATotalRows := LGetAccountsSummarizedStoredProc.FieldByName('@out_total_rows').AsLongWord;
      while not LGetAccountsSummarizedStoredProc.Eof do
      begin
        LSelectResultsItem.AccountId := LGetAccountsSummarizedStoredProc.FieldByName('account_id').AsLongWord;
        LSelectResultsItem.AccountName := LGetAccountsSummarizedStoredProc.FieldByName('account_name').AsString;
        LSelectResultsItem.AccountEmail := LGetAccountsSummarizedStoredProc.FieldByName('account_email').AsString;
        LSelectResultsItem.AccountDocument := LGetAccountsSummarizedStoredProc.FieldByName('account_document').AsString;
        LSelectResults.Add(LSelectResultsItem);
        LGetAccountsSummarizedStoredProc.Next;
      end;
    finally
      LGetAccountsSummarizedStoredProc.Close;
    end;
    ASelectResults := LSelectResults.ToArray;
  finally
    LSelectResults.Free;
  end;
end;

The error message: Exception class EDatabaseError with message 'Field '@out_total_rows' not found'.

See that in dbForge it works as expected:

viniciusfbb

Sorry, I completely forgot that sqlite doesn't work with fixed sizes. The correct is BLOB and not BLOB(16).
Problem solved.

viniciusfbb

Additional information: My field is a BLOB(16), when I change it to BLOB everything works. How could I make the field BLOB(16) considered as a blob and not as a ftWideString?

viniciusfbb

I am using the LiteDAC 4.3.2 and Delphi 10.4.1 and I have some blob fields in my sqlite3 that I store binary data and I am having problems with these fields.

To write I use:

Code: Select all

ADataSet.ParamByName('xxx').AsBytes

To read I use:

Code: Select all

ADataSet.FieldByName('xxx').AsBytes

The write is apparently working correctly, but the read is not and from what I noticed internally LiteDAC is considering the BLOB field as a string (In TField, the FDataType is ftWideString), and that's a problem.

This happened to me on MyDAC, but there were options that I set to work around this problem:

Code: Select all

     TCustomMyDataSet(ADataSet).Options.FieldsAsString := False;
     TCustomMyDataSet(ADataSet).Options.BinaryAsString := False;

But in LiteDAC I didn't find that option. How can I work around this?

(Sorry for my bad english)

viniciusfbb

Sorry, I found the real problem and is in my code. --"

viniciusfbb

It appears to be related to ParamByName('xxxx').AsBytes. When I change to ParamByName('xxxx').AsBlob, the assertion stop.

So I have to ask when I should use AsBytes and when I should use AsBlob? And the the same apply to Fields?

In my case, the parameter of the stored procedure is

IN in_account_uuid binary(16)

viniciusfbb

Hi, I am using the last version of MyDac in delphi Sydney and sometimes when I execute a stored procedure I'm getting the assertion above

Assertion failure (D:\Projects\Delphi\Dac\MySql\Source\MyClasses.pas, line 1780).

But I don't have the license of the source to investigate it. What is this assertion?

viniciusfbb

Problem solved.

viniciusfbb

ViktorV, Yes, I read about it, and each thread have it own connection.

My version of MyDAC don't have source, otherwise I could better investigate what happens in the destroy of the class "TMyStoredProc" and its classes relatives.

This problem always occurs in the destroy of it. Here is the debugger view (without mydac source, the callstack of the madExcept is better):

Sorry for my bad english.

viniciusfbb

Hello,

I'm making a high-performance multithread server where there can be up to 100,000 concurrent connections. And, during my performance tests, I sometimes got the same exception, by destroying the TCustomMyDataSet class.
This happens with a certain rarity, more or less every 100,000 request-response, so I came to believe that it could be a multithreading problem of the TCustomMyDataSet classes (TMyStoredProc base class, among others).

The following code is the only function I use during my tests, which is the call of a database stored procedure, which returns the data of an address:

Code: Select all

procedure TipServerDatabase.GetPostalCodeData(ACountryCode, APostalCode: TipNullable<string>; out APostalCodeAddress, APostalCodeDependentLocality, APostalCodeLocalityDistrict, APostalCodeLocality, APostalCodeAdministrativeArea: TipNullable<string>);
var
  LConnection: TMyConnection;
  LStoredProc: TipMyStoredProc;
  LStopwatch: TipStopwatchLite;
begin
  LConnection := GetConnection;
  try
    LStoredProc := TipMyStoredProc.Create(LConnection);
    try
      LStoredProc.Connection := LConnection;
      LStoredProc.StoredProcName := 'get_postal_code_data';
      LStoredProc.PrepareSQL;
      try
        if ACountryCode.IsNull then
          LStoredProc.ParamByName('in_country_code').Clear
        else
          LStoredProc.ParamByName('in_country_code').AsString := ACountryCode.Value;
        if APostalCode.IsNull then
          LStoredProc.ParamByName('in_postal_code').Clear
        else
          LStoredProc.ParamByName('in_postal_code').AsString := APostalCode.Value;
        LStopwatch := TipStopwatchLite.StartNew;
        LStoredProc.Execute;
        try
          DoCallFetched(LStoredProc.StoredProcName, LStopwatch.Miliseconds / 1000);
          if LStoredProc.FieldByName('@out_postal_code_address').IsNull then
            APostalCodeAddress.IsNull := True
          else
            APostalCodeAddress.Value := string(LStoredProc.FieldByName('@out_postal_code_address').AsString);
          if LStoredProc.FieldByName('@out_postal_code_dependent_locality').IsNull then
            APostalCodeDependentLocality.IsNull := True
          else
            APostalCodeDependentLocality.Value := string(LStoredProc.FieldByName('@out_postal_code_dependent_locality').AsString);
          if LStoredProc.FieldByName('@out_postal_code_locality_district').IsNull then
            APostalCodeLocalityDistrict.IsNull := True
          else
            APostalCodeLocalityDistrict.Value := string(LStoredProc.FieldByName('@out_postal_code_locality_district').AsString);
          if LStoredProc.FieldByName('@out_postal_code_locality').IsNull then
            APostalCodeLocality.IsNull := True
          else
            APostalCodeLocality.Value := string(LStoredProc.FieldByName('@out_postal_code_locality').AsString);
          if LStoredProc.FieldByName('@out_postal_code_administrative_area').IsNull then
            APostalCodeAdministrativeArea.IsNull := True
          else
            APostalCodeAdministrativeArea.Value := string(LStoredProc.FieldByName('@out_postal_code_administrative_area').AsString);
        finally
          LStoredProc.Close;
        end;
      except
        on E: EDatabaseError do
          raise EipDatabase.CreateFmt(CALL_ERROR, [E.Message, LStoredProc.StoredProcName]);
      else
        raise;
      end;
    finally
      LStoredProc.Free;
    end;
  finally
    if MultiConnection then
      LConnection.Free;
  end;
end;

And above the madExcept

I am using the last version of the MyDac and the delphi Tokyo 10.2.3

Devart Forums

Search found 11 matches

Re: Stored procedure returning select and output parameter

Stored procedure returning select and output parameter

Re: Binary data in blob field

Re: Binary data in blob field

Binary data in blob field

Re: Stored procedure assertion

Re: Stored procedure assertion

Stored procedure assertion

Re: Is there any chance of the TCustomMyDataSet class doesn't be threadsafe?

Re: Is there any chance of the TCustomMyDataSet class doesn't be threadsafe?

Is there any chance of the TCustomMyDataSet class doesn't be threadsafe?