Devart Forums

azyk

Hi, Hector

Please provide the following additional information:

- exact version of the DBMS you are using;
- SQL script for creation of the _UniDacDemo table.

azyk

We are currently updating UniDAC with the support for the new features of MongoDB 4.0.0. Please specify the new features of MongoDB 4.0.0 you need the most: https://docs.mongodb.com/manual/release-notes/4.0/

azyk

To fix the issue, we need to investigate it. We cannot reproduce the issue in our test environment. Please provide the following additional information:

- Compose a small sample Delphi project for the issue reproduction.
- Include the script of test tables creation.
- Step-By-Step instructions on how to set up the Apache module.

azyk

We fixed the issue and the fix was included to UniDAC 7.4.12.

Note that the TMongoDBUniProvider behavior was changed in UniDAC 7.4.12. Database Commands were removed from the Trial and Professional Editions, and the SQL-92 syntax was added. I.e. the collections should be requested as follows:

Code: Select all

select * from restaurants

Database Commands remain only in the Standard Edition. I.e. the collections should be requested as follows:

Code: Select all

{"find":"restaurants", "filter":{}}

azyk

We replied to your email.

azyk

We cannot reproduce the issues. Please send us the result of executing Case_sensitive_Linux console application. The code is below. Open it in Delphi, set the credentials and run.

Code: Select all

program Case_sensitive_Linux;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  System.SysUtils,
  PgAccess,
  PgClasses,
  MemData,
  Data.DB;

var
  fConnection: TPgConnection;
  thisQuery: TpGQuery;
begin
  try
    fConnection := TPgConnection.Create(nil);
    try

      fConnection.Pooling := false;
      fConnection.LoginPrompt := false;
      fConnection.Options.ApplicationName := ExtractFileName(paramstr(0));
      fConnection.Options.UseUnicode := true;
      fConnection.Options.EnableComposites := true;
      fConnection.Options.DefaultSortType := stCaseInsensitive;
      // SET UP CREDENTIALS
      fConnection.Server := 'Server';
      fConnection.Database := 'Database';
      fConnection.Username := 'Username';
      fConnection.Password := 'Password';
      fConnection.Port := 5432;

      thisQuery := TpGQuery.Create(nil);
      try
        thisQuery.Connection := fConnection;
        thisQuery.readonly := true;
        thisQuery.cachedUpdates := true;
        thisQuery.FetchAll := true;
        thisQuery.Options.LocalMasterDetail := true;
        thisQuery.Options.StrictUpdate := false;

        // Issue 1
        thisQuery.SQL.text := 'select job from emp where job= :PARAMname';
        thisQuery.ParamByName('paramNAME').asString := 'MANAGER';
        // Under Linux - Param Name is now case sensitive
        thisQuery.open;
        if thisQuery.RecordCount <> 3 then
          Writeln('Issue 1. BUG.')
        else
          Writeln('Issue 1. Not reproduced');

        // Issue 2
        thisQuery.SQL.text := 'select job from emp';
        thisQuery.open;

        if thisQuery.locate('job', 'MaNaGeR', [loCaseInsensitive]) then
        // loCaseInsenstive ignored
          Writeln('Issue 2. Not reproduced')
        else
          Writeln('Issue 2. BUG.');
      finally
        thisQuery.Free;
      end;
    finally
      fConnection.Free;
    end;

  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Writeln('Press ENTER to exit.');
  Readln;

end.

Script for create emp table is in the InstallDemoObjects.sql file. The file located where PgDAC demos were installed.

azyk

Please compose a small test project. In it, demonstrate exactly how you use SQL.Add . In the project, include a script to create a test table. Use the contact form at our website https://www.devart.com/company/contactform.html and send us this project. Also specify the exact version of the DBMS you are using.

azyk

In order for TPgDump to back up SEQUENCE in the database, set the doSequences property to True. For example:

Code: Select all

PgDump.ObjectTypes := PgDump.ObjectTypes + [doSequences];

More about TPgDump.ObjectTypes: https://www.devart.com/pgdac/docs/devar ... types.htm

azyk

We replied to your email.

azyk

We cannot reproduce this issue. Please compose a small test project for its demonstration. In the project, include all files of the mytable table (mytable.dbf + mytable.* memo and index files). Use the contact form at our website http://www.devart.com/company/contactform.html and send us the project.

azyk

In the latest release of UniDAC, we added a possibility to map fields to DateTime for TDBFUniprovider.
In order for the newduedate field from the specified SQL query to be mapped to DateTime, add the following mapping rule:

Code: Select all

  UniQuery.DataTypeMap.AddFieldNameRule('newduedate', ftDatetime);
  UniQuery.Open;

More details about Data Type Mapping in our online documentation: https://www.devart.com/unidac/docs/data ... apping.htm .

azyk

When the specific option 'DBFFormat' = 'dfAuto', UniDAC takes metadata from headers. In the sent file, the header says that data is stored in dBaseIII format. UniDAC fetches data according to dBaseIII specification: https://www.dbase.com/Knowledgebase/INT ... le_fmt.htm . When the specific option 'DBFFormat' = 'dfVisualFoxPro', UniDAC will fetch data using VisualFoxPro format. Although there is another format in the header.

azyk

Ivan_Carpio89 wrote: ↑Thu 20 Dec 2018 17:59 2.- The duration increased, going from 1 minute to 7 minutes. This has its explanation since now it consults the fields of the table for every 25 rows.

SmartFetch mode does not give duration advantages when fetching data sequentially. SmartFetch mode provides advantages when editing tables with a large number of records.

To go to a particular record, it is not necessary to sequentially read all the records before/after it. You can use the TUniTable.MoveBy method. For example, if you want to go to the 500000th record, use the following code:

Code: Select all

  UniTable.Active:= True;
  UniTable.MoveBy(500000);

For more information about MoveBy in the Embarcadero documentation: http://docwiki.embarcadero.com/Librarie ... Set.MoveBy

Ivan_Carpio89 wrote: ↑Thu 20 Dec 2018 17:59 1.- If the table is in constant change, that is, is being affected by INSERT's, UPDATE's and DELETE's, have you considered that the WHERE was as follows ?:

SmartFetch mode uses a universal mechanism that suits all Uni providers. Therefore, SmartFetch mode does not use the specific features of various DBMSs. Besides, it does not use SQL Server OFFSET-FETCH Clause.

Ivan_Carpio89 wrote: ↑Thu 20 Dec 2018 17:59 1.- Get all the rows of the table, only by consulting the fields of the primary key.
2.- In each Next the fields of the first 25 rows are obtained, and so on for every 25 rows traveled.

This is a specificity implementation of the SmartFetch mode. It allows to use the SmartFetch mode for all Uni providers.

TUniTable fetches data by blocks. The number of records in the block is taken from the FetchRows property: https://www.devart.com/unidac/docs/deva ... chrows.htm . For example, if you set FetchRows=42, then the data will be fetched by 42 records.

azyk

In the header of the file, it is specified that data is stored in the dBaseIII format. Therefore, when autodetecting the format, the some fields are fetched incorrectly. In fact, the data is stored in VisualFoxPro format. Therefore, to solve the issue, change the file header into VisualFoxPro. Or set the specific connection option 'DBFFormat' to 'dfVisualFoxPro'.

azyk

Ivan_Carpio89 wrote: ↑Wed 19 Dec 2018 14:44 Okay. It is complicated for me, because I have tables with more than 1 million records, and to be activating TUniTable's is slow because the SQL sentences take time. Also the memory of my application grows to 1.5 GB only by activating TUniTable, this is why all the records in the table are brought to memory.

Any suggestions with the use of the TUniTable, or any recommendation of another equivalent component in UniDAC?

Please set the credentials in the UniDAC_SmartFetch console application and run it. Tell us about the results.

Code: Select all

program UniDAC_SmartFetch;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  UniProvider,
  SQLServerUniProvider,
  Uni,
  ActiveX,
  psAPI,
  Winapi.Windows;

var
  Con: TUniConnection;
  UniTable: TUniTable;

function CurrentProcessMemoryMB: Cardinal;
var
  MemCounters: TProcessMemoryCounters;
begin
  MemCounters.cb := SizeOf(MemCounters);
  if GetProcessMemoryInfo(GetCurrentProcess,
      @MemCounters,
      SizeOf(MemCounters)) then
    Result := MemCounters.WorkingSetSize div (1024*1024)
  else
    RaiseLastOSError;
end;

begin
  CoInitialize(nil);
  try
    Con := TUniConnection.Create(nil);
    try

      Con.ProviderName := 'SQL Server';
//  Set up credentials here
      Con.Server := 'XXXXX';
      Con.Database := 'XXXXX';
      Con.Username := 'XXXXX';
      Con.Password := 'XXXXX';
      Con.Connect;

      UniTable := TUniTable.Create(nil);
      try
        UniTable.Connection := Con;

        UniTable.SmartFetch.LiveBlock := False;
        UniTable.SmartFetch.Enabled := False;
        UniTable.SpecificOptions.Values['SQL Server.FetchAll'] := 'True';
        UniTable.FetchRows := 25;

        UniTable.SQL.Text := 'select * from MyTable';
        UniTable.Active:= True;
        while not UniTable.eof do begin
          UniTable.Next;
          UniTable.Fields[0].Value;
        end;

        Writeln(Format('SmartFetch=False. Memory usage=%d Mb', [CurrentProcessMemoryMB]));
      finally
        UniTable.Free;
      end;

      UniTable := TUniTable.Create(nil);
      try
        UniTable.Connection := Con;

        UniTable.SmartFetch.LiveBlock := True;
        UniTable.SmartFetch.Enabled := True;
        UniTable.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
        UniTable.FetchRows := 25;

        UniTable.SQL.Text := 'select * from MyTable';
        UniTable.Active:= True;
        while not UniTable.eof do begin
          UniTable.Next;
          UniTable.Fields[0].Value;
        end;

        Writeln(Format('SmartFetch=True.  Memory usage=%d Mb', [CurrentProcessMemoryMB]));
      finally
        UniTable.Free;
      end;

    finally
      Con.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Writeln('Press ENTER to exit.');

  Readln;
end.

Devart Forums

Search found 1119 matches

Re: Error processing batch operations with manual parameters (ParamCheck = False)

Re: MongoDB. TUniQuery error: Invalid namespace specified '.Session'

Re: Case sensitive issues under 10.3+Linux

Re: TUniQuery Invalid value Error CodeGear 2007 C++

Re: Provider DBF Direct how to use the CDX indices?

Re: Case sensitive issues under 10.3+Linux

Re: Bad Performance on Query.SQL.Add

Re: TPGDump generate error on Postgresql 10.2

Re: DBF Direct Connect

Re: Impossible to cast a Date into Datetime (DBF Uniprovider)

Re: Impossible to cast a Date into Datetime (DBF Uniprovider)

Re: DBF Direct Connect

Re: "Live Data Windows Mode" in UniDAC

Re: DBF Direct Connect

Re: "Live Data Windows Mode" in UniDAC