Mutli-threaded server with FireDAC

Hi Dmitry,

I'm building an HTTP server with SQLite/FireDAC and RealThinClient 
(RTC). RTC is multi-threaded. It creates a threadpool and calls event 
handlers from threads when HTTP requests are received. My event handlers 
create a TFDConnection and a TFDQuery for each HTTP request, as shown below.

I just read about Pooled connections and TFDManager. I see that using a 
pooled connection would be better. Do I need to use TFDManager if each 
TFDConnection (or pooled connection) exists only for 1 event, and is 
used by only one thread?

Joe

void __fastcall 
THttpDataModule::RTEVENTDataProviderDataReceived(TRtcConnection *Sender)
{
   TRtcDataServer *DS = (TRtcDataServer*)Sender;
   if (DS->Request->Complete) {
     // create a new TFDConnection with settings identical to Connection
     TFDConnection *TempConnection = new TFDConnection( this );
     TempConnection->ConnectionString = Connection->ConnectionString;
     TempConnection->UpdateOptions->LockWait = true;
     // create a new TFDQuery, apply settings, assign SQL, execute, 
FetchAll()
     TFDQuery *FDQuery = new TFDQuery( this );
     FDQuery->Connection = TempConnection;
     FDQuery->ResourceOptions->CmdExecMode = amNonBlocking;
     FDQuery->SQL->Assign( RTEventQuerySQL );
     FDQuery->Active = true;
     FDQuery->FetchAll();
     // use TRtcArray to get "flat" JSON compatible with D3
     TRtcArray *RtcArray = new TRtcArray;
     DelphiDataSetToRtcArray( FDQuery, RtcArray );
     DS->Write( RtcArray->toJSON() );
     delete( RtcArray );
     // destroy temporary TFDQuery and TFDConnection
     FDQuery->Active = false;
     FDQuery->SQL->Clear();
     delete( FDQuery );
     TempConnection->Connected = false;
     delete( TempConnection );
   }
}
0
Joe
3/31/2014 10:08:10 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

8 Replies
3534 Views

Similar Articles

[PageSpeed] 14

> FDQuery->ResourceOptions->CmdExecMode = amNonBlocking;

This is not what you needed. Just leave default value - amBlocking.

>      // destroy temporary TFDQuery and TFDConnection
>      FDQuery->Active = false;
>      FDQuery->SQL->Clear();
>      delete( FDQuery );
>      TempConnection->Connected = false;
>      delete( TempConnection );

Just use:
{code}      delete( FDQuery );
      delete( TempConnection );{code}

> Do I need to use TFDManager if each 
> TFDConnection (or pooled connection) exists only for 1 event, and is 
> used by only one thread?
 
You need TFDManager just to simplify specification of different TFDManager properties 
at design time. If you not explicitly created TFDManager, then FireDAC will implicitly
create TFDManager instance. In any case you can get access to TFDManager by calling
FDManager singleton function.


-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
3/31/2014 10:29:22 PM
On 3/31/2014 3:29 PM, Dmitry Arefiev wrote:
>> FDQuery->ResourceOptions->CmdExecMode = amNonBlocking;
>
> This is not what you needed. Just leave default value - amBlocking.
>
>>       // destroy temporary TFDQuery and TFDConnection
>>       FDQuery->Active = false;
>>       FDQuery->SQL->Clear();
>>       delete( FDQuery );
>>       TempConnection->Connected = false;
>>       delete( TempConnection );
>
> Just use:
> {code}      delete( FDQuery );
>        delete( TempConnection );{code}
>
>> Do I need to use TFDManager if each
>> TFDConnection (or pooled connection) exists only for 1 event, and is
>> used by only one thread?
>
> You need TFDManager just to simplify specification of different TFDManager properties
> at design time. If you not explicitly created TFDManager, then FireDAC will implicitly
> create TFDManager instance. In any case you can get access to TFDManager by calling
> FDManager singleton function.
>
>


Thank you, Dmitry. Is the CmdExecMode "relative"? What I mean is that 
with amBlocking, only the calling thread will be blocked? This is not 
the GUI thread, so the GUI thread will not be blocked?

I do not yet understand TFDManager, or whether my existing handler is 
okay. RTC is multi-threaded, so I could have multiple clients, and 
multiple threads and event handlers running at the "same" time. Does 
this mean I MUST use TFDManager, even though my handler creates a 
TFDConnection and TFDQuery for its own use?

I don't think this is a FireDAC question, but I have tested with 12 
clients (browsers), all polling about 50 times/sec, and there is no 
problem if all clients are on the same PC as the server. 10 million 
requests with no errors. However, if the clients are on another PC, 
connecting over wireless, I very quickly get access violations. They are 
reported to be in VCL function CompareText(). Does this mean anything to 
you?

Joe
0
Joe
4/1/2014 12:13:32 AM
> What I mean is that with amBlocking, only the calling thread will be blocked?

Yes, it is.

> Does this mean I MUST use TFDManager, even though my handler creates a 
> TFDConnection and TFDQuery for its own use?

No, you does not need.

> However, if the clients are on another PC, 
> connecting over wireless, I very quickly get access violations. They are 
> reported to be in VCL function CompareText(). Does this mean anything to 
> you?

Looks like some memory issues.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
4/1/2014 12:31:08 AM
On 3/31/2014 5:31 PM, Dmitry Arefiev wrote:
>> What I mean is that with amBlocking, only the calling thread will be blocked?
>
> Yes, it is.
>
>> Does this mean I MUST use TFDManager, even though my handler creates a
>> TFDConnection and TFDQuery for its own use?
>
> No, you does not need.
>
>> However, if the clients are on another PC,
>> connecting over wireless, I very quickly get access violations. They are
>> reported to be in VCL function CompareText(). Does this mean anything to
>> you?
>
> Looks like some memory issues.
>
Okay. Thanks. I want to learn to use FDManager and Pooled connections, 
to avoid creating a TFDConnection on each request.

Do you mean the memory use for the server could be different for a 
client on a remote PC versus a client on the same PC?
0
Joe
4/1/2014 12:47:19 AM
> Okay. Thanks. I want to learn to use FDManager and Pooled connections, 
> to avoid creating a TFDConnection on each request.

http://docwiki.embarcadero.com/RADStudio/XE5/en/Defining_Connection_(FireDAC)
http://docwiki.embarcadero.com/RADStudio/XE5/en/Multithreading_(FireDAC)#Connection_Pooling

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
4/1/2014 12:59:20 AM
Dmitry,

I did more tests, and the AV was definitely related to the database access in the event handler, even with creating and destroying TFDConnection and TFDQuery for each event. After many different tests, and reading threads on EDN about problems with VCL global name space, I changed the new() statements to pass Owner=NULL instead of Owner=this, and the problems went away. Do you understand why?

// Owner=NULL is okay. Owner=this causes AV.
TFDConnection *Connection = new TFDConnection( NULL );
TFDQuery *Query = new TFDQuery( NULL );

I am very confused about the Pooled connection. If I have a TDataModule, and I create a Private connection in the constructor, with Pooled=True, how do I get a temporary connection from the pool within an event handler? 

Joe
0
Joe
4/2/2014 7:56:31 PM
> I changed the new() statements to pass Owner=NULL instead of Owner=this, and the problems went away. Do you understand why?

If an instance pointed by "this" is shared across several threads, then AV is expectable,
because VCL is not thread-safe.

> I am very confused about the Pooled connection. If I have a TDataModule, and I create a Private connection in the constructor, with Pooled=True, how do I get a temporary connection from the pool within an event handler? 

Just set TFDConnection.ConnectionDefName to the name of pooled connection definition.
All other is the same as with non-pooled connection definitions.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
4/3/2014 6:22:39 PM
On 4/3/2014 11:22 AM, Dmitry Arefiev wrote:
>> I changed the new() statements to pass Owner=NULL instead of Owner=this, and the problems went away. Do you understand why?
>
> If an instance pointed by "this" is shared across several threads, then AV is expectable,
> because VCL is not thread-safe.
>
>> I am very confused about the Pooled connection. If I have a TDataModule, and I create a Private connection in the constructor, with Pooled=True, how do I get a temporary connection from the pool within an event handler?
>
> Just set TFDConnection.ConnectionDefName to the name of pooled connection definition.
> All other is the same as with non-pooled connection definitions.
>


Hi Dmitry,

The instance is not shared. It is created and destroyed within the event 
handler. It is used only by the thread that executes the handler, and is 
never referenced externally.

I think what's happening is that when the Owner is not NULL, VCL does a 
non-thread-safe add/remove to the Components[] of the Owner. With 
Owner=NULL, that add/remove is avoided, so no AV. I think this means 
that run-time creation of TFDConnection and TFDQuery is thread-safe only 
if it is done with Owner=NULL. Do you agree?

Regarding the Pooled connection, thank you, I have it working.

Joe
0
Joe
4/7/2014 10:58:03 PM
Reply:

Similar Artilces:

FireDAC and Delphi Pro XE4... firedac tab not visible?
I am moving from a very old Borland product (2006) to Delphi XE4 Professional with FireDAC addon. I purchased both from Embarcadero, and have downloaded both. The documentation says the FireDAC components should automagically appear in the tabset for Delphi. They do not. I don't want to fool around too much as my environment is strictly controlled by IT, and they are very slow to do anything. Is there something that I should have done, but did not do? I installed XE4 first, registered it, then used the link in my email from Embarcadero to download the FireDAC components. Then I ra...

Delphi XE5 Enterprise FireDAC SQL Server driver not in list
Just installed Delphi XE5 Studio Enterprise. Playing with FireDAC components. When I drop a TFDConnection on my form, I am looking for the driver for Microsoft SQL Server. I do not see it in the list. I see ADS, ASA, FB, IB, MSAcc, MySQL, ODBC, PG, SQLLite. Is there a separate install? Wayne This is a bug. The workaround is to drop TFDPhysMSSQLDriverLink on a form. Then MSSQL will be accessible in the drivers list. -- With best regards, Dmitry Arefiev / FireDAC Architect FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, DB2, SQL Anywhere, Access, Informi...

Basic setup for remote/virtual server with FireDAC/Delphi etc.
Hi,I waswondering if anyone can point me in the right direction with some advice. In the past I have produced an application with Delphi 2007, IB Objects and Firebird 2.0 with Dev Express grids which worked really well. I now want to produce a completely new version taking advantage if possible of a hosted (cloud based) server. My idea is rent a windows 2008 R2 server off Hetzner, install Firebird 2.5 on it with my large databases. I should point out all my databases are used read only no editing. I would then like to produce a new windows app (and ios/android app later on) with my new XE4 a...

FireDac
Hello, I am using Delphi XE4 and facing a problem with FireDac (8.0.5) and MySQL. I'm trying to get the last 100 records from a large table (20 million rows). When I run the following SQL: {code}select * from largetable order by FId limit 100{code} in MySQL Workbench it returns in 0.2 seconds. When I run the above SQL in Delphi through an ADQuery it never returns (Delphi freezes). Any Ideas ? Thank you ...

FireDac Delphi Xe2
Hello, I have FireDac components installed to Delphi Xe2, My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. It doesn't work. Maybe someone know what I have to change in FiredAc source ro remove this issue. I suggest to the FireDAC newgroup. You'll probably get a better response there. Sebastian Kumor wrote: > Hello, > I have FireDac components installed to Delphi Xe2, > My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. > It doesn't work. Mayb...

FireDAC for Delphi 2007 ?
Hello, I would like to know if FireDAC Client/Server Pack can be installed on Delphi 2007 pro. Thanks, Regards, Emmanuel Lion FireDAC Client/Server Pack for XE5 - No FireDAC Client/Server Pack for XE4/XE3, see: http://support.embarcadero.com/article/42970 M. E.L. wrote: > Hello, > > I would like to know if FireDAC Client/Server Pack > can be installed on Delphi 2007 pro. > > Thanks, > Regards, > > Emmanuel Lion > On 02.10.2013 18:17, quinn wildman wrote: > FireDAC Client/Server Pack for XE5 - No > FireDAC Client/Server Pack ...

FireDac Delphi Xe2
Hello, I have FireDac components installed to Delphi Xe2, My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. It doesn't work. Maybe someone know what I have to change in FiredAc source to remove this issue. To set the Param on NULL value before executing the query I do: {code} with qryExample.ParamByName('Example_id') do Begin DataType:=ftInteger; Clear; // Bound := True; End; {code} > {quote:title=Robert Triest wrote:}{quote} > To set the Param on NULL value before executing the query I do: >...

FireDAC?
I'm starting a new development initiative and want to pick the tech that will last the longest and have the most complete feature set. Elsewhere someone noted that DBExpress has been deprecated in favour of FireDAC. I seem to have missed the announcement of FireDAC. So I'm looking for any info on what FireDAC is and why I should use it. Any links? I just discovered the FireDAC newsgroup and the first message there is an announcement with links to the product info and documentation. So my basic question (What is it?) is answered. Still don't know the future of DBExpr...

FireDac Delphi Xe2
Hello, I have FireDac components installed to Delphi Xe2, My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. It doesn't work. Maybe someone know what I have to change in FiredAc source to remove this issue. To set the Param on NULL value before executing the query I do: {code} with qryExample.ParamByName('Example_id') do Begin DataType:=ftInteger; Clear; // Bound := True; End; {code} > {quote:title=Robert Triest wrote:}{quote} > To set the Param on NULL value before executing the query I do: >...

Thread safety of Firedac
Hi, We want to use firedac in a multithreaded environment, but we are not sure how to interprete the multithreading documentation in http://docwiki.embarcadero.com/RADStudio/XE6/en/Multithreading_%28FireDAC%29 : Is it thread safe to use a TFDConnection over multiple threads as long as no threads use the connection simultaneously, or not? A similar question for a TFDQuery for example. To be specific, I am wondering if following cases are thread safe: 1) a thread creates a connection and uses its connection, but does not close&remove this connection. Another thread serves to find t...

FireDac Delphi Xe2
Hello, I have FireDac components installed to Delphi Xe2, My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. It doesn't work. Maybe someone know what I have to change in FiredAc source ro remove this issue. I suggest to the FireDAC newgroup. You'll probably get a better response there. Sebastian Kumor wrote: > Hello, > I have FireDac components installed to Delphi Xe2, > My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. > It doesn't work. Mayb...

It is FireDAC after all
Marco announces FireDAC: http://blog.marcocantu.com/blog/firedac_announced.html -- Nick The Buy Now button almost work ;-) > {quote:title=Nick Hodges wrote:}{quote} > Marco announces FireDAC: > > http://blog.marcocantu.com/blog/firedac_announced.html > That was fast :) delete this message > What's the price for XE3 Pro licensees? Introductory price is $399 -- With best regards, Dmitry Arefiev / FireDAC Architect http://www.embarcadero.com/products/rad-studio/firedac > All the links I found just go to a page that lets you purchase Delp...

FireDAC Client/Server for RAD Studio XE5 Professional MS SQL Server Driver
Hello, I have purchased the RAD Studio XE5 Professional Edition and the add-on : FireDAC Client/Server for RAD Studio XE5 Professional. I am attempting to create a simple project : File\New\FireMonkey Mobile Application - Delphi. Confirm the blank application template. Now for the FireDAC components ... I simply add a TFDConnection object to the blank form and the "FDConnection1" is created successfully. I right mouse click and choose "Connection Editor..." from the drop down list. I attempt to select the MS SQL Server driver ID, but it is not available ...

Firedac for Delphi XE Pro
Will the XE4 Client Server Pack for FireDac work properly with Delphi XE Professional? Will that provide the full Firedac functionality or is it limited? Is it dependent on functionality in the Enterprise product which is not in the Professional? And finally, will I have the license rights to market and sell the application I create? I understand it does not require dbexpress which has certain restrictions, correct? I would like to be able to evaluate it in an XE environment without upgrading everything just to try and work with it. Patrick Moloney wrote: > Will the XE4 Client Serve...

Web resources about - Mutli-threaded server with FireDAC - embarcadero.delphi.firedac

Resources last updated: 1/5/2016 10:47:43 PM