Soji

Thanks @Anders Melander

I did exactly same. I saw that even though it executes in the batch, server side it was inserting slowly. 

So I used transactions, it inserted super fast. 

Hi Brain,

Thanks for your suggestion. But those OLEDB drivers are either not maintained or deprecated.

Also FireDAC forces you to use ODBC driver. FireDAC, New OLE DB Driver, and SQL Server

I figured out that if I use begin transaction and commit transaction, then the performance increases dramatically. 15 minutes to 1 minute!!!

So I am satisfied with this solution and testing now.

Kind regards,

Soji.

Hi,

I am trying to rewrite a piece of legacy code which inserts a number of records in a bulk to SQL server tables(Total number of lines is around 200000 across 15 tables). Currently it uses ADO query to insert multiple records one by one. If the number of records are too high then this process takes a lot of time.

I was trying to make it faster and came across FireDAC array DML and according to the documentation, inserting 100000 records can be done less than a second!

So I implemented it and in my development machine  (where application and database resides) it improved a lot. From 8 minutes to less than a minute.

But when I tested it on a test environment where application and database resides on different machines connected by a network, performance is worse. (It was taking 10 minutes with ADO and with array DML it takes 15 minutes)
I can't figure it out why array DML insert is taking more time than normal record by record insert. Any ideas?

Delphi version:  Delphi 10.4 

SQL Server: 2017

SQL Driver: ODBC DRIVER 17 FOR SQL SERVER

Kind regards,

Soji.

On 9/21/2021 at 8:20 PM, Remy Lebeau said:

<ARG!> I just now noticed that you are connecting to Office 365 on port 993.  That is an IMPLICIT TLS port.  But, you are not using any TLS settings on your TIdIMAP4 at all!  Rather than assigning a TIdIOHandlerStack component to the TIdIMAP4.IOHandler property, you need to assign a TIdSSLIOHandlerSocketBase-derived component instead, like TIdSSLIOHandlerSocketOpenSSL (TIdSSLIOHandlerSocketBase derives from TIdIOHandlerStack and thus also has the TransparentProxy property).  And then set the TIdIMAP4.UseTLS property to utUseImplicitTLS.

@Remy Lebeau: Thanks that worked like a charm... You were really helpful. Thanks for taking time to help me with this.

On 9/14/2021 at 6:03 PM, Remy Lebeau said:

Without seeing a trace log of what's going on over the wire, I honestly could not tell you what is going on.  Can you get a Wireshark capture of the connect attempt?

Thanks @Remy Lebeau for your time.

I received a network log from the production server. They said that it is actually connecting to outlook through proxy! See the image below:

But I get the "Connection closed Gracefully" exception when I call IdIMAP.Connect(false). 

@Remy Lebeau: Thanks for your suggestion. I checked it but we use HTTP. So I used TIdConnectThroughHttpProxy and I investigated a bit more and adjusted code like this:
 

  p := TIdConnectThroughHttpProxy.Create(IdIMAP);
  io := TIdIOHandlerStack.Create(IdIMAP);
  p.Host :='x.x.x.x'; // Proxy server ip
  p.port :=1234; // Proxy server port
  p.Enabled := true;
  io.TransparentProxy:=p;

  IdIMAP.IOHandler := io;
  IdIMAP.Host := 'outlook.office365.com';
  IdIMap.Port := 993;
  IdIMAP.Password := 'Pass123';

Now I get the "Connection closed Gracefully" exception when I call IdIMAP.Connect(false). 
Thanks for your time,
Soji.

No. The proxy doesn't require it's own authentication. 

Hi,
I am trying to figure out how to connect to office 365 through a proxy server. My application is working well if I connect without a proxy. But now the requirement changed and direct connection to internet is not allowed and I have to use a proxy. I can connect to office 365 from the production machine through proxy using a browser. So the connection setup is good. Now I need to change my application to use a proxy. So I looked around and found that I have to use TIdIOHandlerStack and IdConnectThroughHttpProxy1 to achieve it. But the help document is not that great. So looking for suggestions/help.

My code looks like this.

type
  TForm1 = class(TForm)
    Button1: TButton;
    IdIMAP: TIdIMAP4;
    IdConnectThroughHttpProxy1: TIdConnectThroughHttpProxy;
    IdIOHandlerStack1: TIdIOHandlerStack;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  IdIMAP.Username := 'test@test.com'; // outlook user name
  IdIMAP.Password := 'xxxxx'; // outlook user password
  IdIMAP.Port := 993; // outlook server port
  IdIMAP.Host := 'outlook.office365.com'; // outlook server
  IdIMAP.AuthType := iatUserPass;

  IdConnectThroughHttpProxy1.Host := 'x.x.x.x'; // Proxy server
  IdConnectThroughHttpProxy1.Port := 1234; // Proxy server port

  IdIOHandlerStack1.TransparentProxy := IdConnectThroughHttpProxy1;

  IdIMAP.IOHandler := IdIOHandlerStack1;

  try
    Memo1.Lines.Clear;
    Memo1.Lines.Add('Trying to connect to server...');
    if not IdIMAP.Connected then
    begin
      if IdIMAP.Connect(false) then
        Memo1.Lines.Add('Connected to server')
      else
        Memo1.Lines.Add('NOT Connected to server')
    end;

    Memo1.Lines.Add('Trying to login to server...');
    if not(IdIMAP.ConnectionState in [csAuthenticated, csSelected]) then
      IdIMAP.Login;
    Memo1.Lines.Add('Logged in to server');
  except
    on E: Exception do
    begin
      Memo1.Lines.Add('Error while connecting: ' + E.Message);
    end;
  end;
end;

When I try this, I get "Error while connecting: 403 forbidden error"

Thanks for your time,

Kind regards,

Soji.

13 hours ago, emailx45 said:

Then, you can create yourself a tool to "find / change" it...

Thanks @emailx45. I started exactly with same idea sometime back. Then I found some issue when we upgraded Delphi version. The *.dproj file changed and my search and replace failed or did not work with different build configurations. I created a tool to search and replace build version at that time. Thanks for your detailed answer. Much appreciated.

15 hours ago, Anders Melander said:

Don't use the IDE managed version resource. Use an external RC file with a version resource instead and update it with whatever suits your build pipeline best. For example a simple bat or cmd file.

I'm sure there are lots of homegrown utilities that can update the version in the dproj, an RC or RES file but I like to keep the number of dependencies down. A utility would just be yet another piece of software to install, maintain, etc.

Thanks @Anders Melander. I was also not looking for any utilities. Try to find a best solution within IDE/projects itself.  

Hi,

I am looking for an easy option to set  "version information" of multiple projects with in a project group at the same time. Is there any option already available wit the Delphi IDE?

Going through each and every project and updating the version information for a particular build configuration is error prone and cumbersome. I have almost 75 projects in a project group. Don't want to click through each and every project and enter the version information for every release.

Any best practices or suggestions to handle this situation?

I use configuration manager to select a configuration to build all the projects in my development machine. (For release build I use a build server and MSBUILD) 

Kind regards,

Soji.

Thanks for all the inputs.

CryptoLib4Pascal  worked for me.

4 minutes ago, Kas Ob. said:

no IV or block modes

@Kas Ob.: I spotted the same. Thanks for pointing it out.

5 minutes ago, Kas Ob. said:

that package is missing a file HlpIhash

Yes. You have to use another package HashLib4Pascal from same authors. I use that package for PKDF2.

3 minutes ago, Uwe Raabe said:

You are referencing a unit named "Utils" in your project

Yes. That is right. I already "fixed" it. 

But it is really strange to say that I have to add "getit" right?

I got an interesting message when I tried to upgrade my project from Delphi 10.3 to Delphi 10.4.1. It says I need to add "getit" package!. This is really a strange message.

(There is no package named getit in my project group and I am sure I don't have any dependency to a package named getit.) 

50 minutes ago, Vandrovnik said:

Soji wrote "legacy application", I think it is not a mobile app, but a desktop one, which connects directly to a database.

It is a desktop application.  

Just now, David Heffernan said:

So you don't mind if the security is easily circumvented? 

No.

6 minutes ago, Tntman said:

You are storing information to connect to remote database fore example MySql ? It is not good to store any password related stuff in any application, have you consider making API and perform DB request over it ?

It is a legacy application. Sustain mode. So looking for a quick solution.

9 minutes ago, Angus Robertson said:

harder is how you protect the key used for the encryption.

Yes. I was thinking same.

We use an config file to store the database connection string. We are thinking about encrypting the connection string.

Is there any best practices to keep the connection string information safe?

Just now, Fr0sT.Brutal said:

Effectively not the same 😛

Yes. I was about to type the same. It is not same.

Just now, aehimself said:

Live with it; it's only visual, not critical.

I tried that comment. Did not work out well though 😒

Thanks @Attila Kovacs.

This is interesting. I will give a try.

If I can make it work, then it avoids going through loop by loop and adding the peekmessage.

Thanks for your response. 

Application.ProcessMessages is not a solution I am fond of. I wanted to avoid that and came into the peekmessage option.

45 minutes ago, aehimself said:

There is no such thing as a method is "not possible to put in a thread".

Yes. It is possible to move to thread. But it is a big overhaul and in sustain mode, it is not allowed to do that overhaul.