Sharing a SQLite3Connection between my main thread (GUI) and my workerthread lazarus / 16-Databases ---------------------------------------------------------------------- swierzbicki · 2013-08-09 12:58 Hello, I'm working on a WinCE (Motorola Mc3910) project.  I have to scan product and store data into multiple databases. Scanned data is put into a TObjectList. My TObjectList is consumed by a worker thread that basically :  - insert the data into a SQLite table as "Data not sent"  - Send the all "Data not sent" record into a remote firebird server database  - Update the data from the SQLite table as "Data sent" All of this is well working, smooth and my application is not freezing when remote IP connection is lost for example. I've been told to add a grid that will display scanned product. I have one question, is it safe to share one SQLite3Connection / SQLTransaction created in my datamodule ? I'll give the object ref. when creating my workerthread ? Or should I create a separate SQLite3Connection / SQLTransaction for my main thread ? Thank you for your advices, Best regards, Stéphane All of this is working fine. I've been asked now to display scanned data within a grid. ---------------------------------------------------------------------- swierzbicki · 2013-08-09 13:59 Sorry but I misplaced this post ! Should belong to the Database one ! Can an admin push it ? ---------------------------------------------------------------------- sin_dragan · 2013-08-09 18:47 Quote from: swierzbicki on August 09, 2013, 02:58:14 pm 1) All of this is well working, smooth and my application is not freezing when remote IP connection is lost for example. 2) All of this is working fine. I've been asked now to display scanned data within a grid. Hello, about the first point I need to ask: Which networking library did you choose? And from your expirience, how is Firebird handling insert into db when multiple scaners start to send data? I m currentlly in the process of finding one tcp/ip library that is stable on WinCe. I ve installed Lnet (and it's working pretty stable in WinCE device) and now I m testing Indy 10.2.0.3 for wince, for now it's working okay. About point two (data within a grid) I found on Wireless Demo orders examples that StringGrid looks nice on WinCe and also found you can use Rx DB controls, you can see this from germes orders example. ---------------------------------------------------------------------- ludob · 2013-08-10 05:08 Quote from: swierzbicki on August 09, 2013, 02:58:14 pmis it safe to share one SQLite3Connection / SQLTransaction created in my datamodule ? I'll give the object ref. when creating my workerthread ? Or should I create a separate SQLite3Connection / SQLTransaction for my main thread ? Sharing the connection will depend on your sqlite library: http://www.sqlite.org/threadsafe.html. Sharing a transaction is almost never possible. TSQLTransacion is not thread safe and sqldb does everything inside a transaction, even a simple select. Sharing database connections between threads is always tricky and should be avoided as much as possible. You should really have good reasons for doing that and it requires a careful design right from the start. In many cases the sharing of connections over threads is bolted on in a later stadium of the project as a simple way to avoid to make a second connection. That is frequently causing severe, hard to reproduce and hard to debug problems later on. ---------------------------------------------------------------------- swierzbicki · 2013-08-12 07:04 @sin_dragon : I'm currently using the synapse library with the nxnetwork library (mainly for my tcp socket client / server communication). I've slightly modded  the nxnetwork library to make it compatible with Delphi. Both libraries are working well. I've tried to set up the indy library under windows Ce but failed.... Would you mind to share your working library ? My remote DB is firebird. So far so good. I first started my project with Postgresql but I've got severe networking issue with the wince library : the postgresql library lost connection to remote server when my device was cradled and was no more able to reconnect when my device was uncradled again.... I'll check the rxGridqs. Thank you for the tip. @lulob : hummm, that's bad news ! I feel a little bit stuck now since I don't know if my SQLite library was compiled with the threadsafe option (used SQLite compiled DLL from zaher : http://forum.lazarus.freepascal.org/index.php/topic,10301.0.html ). I've also tried to use the WAL journal mode (I'm trying to open my queries for READ-ONLY purpose) ...but still get "Database is locked" error message. Code: [Select]SqliteConnection.ExecuteDirect('PRAGMA journal_mode=WAL; ---------------------------------------------------------------------- sin_dragan · 2013-08-17 09:04 I'm currently working on a similar project, I use Indy 10 (version 10.2.0.3) and it's working okay, on server side I use TidTCPCmdServer and on client (WinCE) TidTcpClient components. So far I m doing on MS Sql express server but ultimately plan to switch on Firebird DB. At the moment I m testing with one handheld device. Can you tell me how FB is handling multiple connections from clients? (select, insert and so on) My idea is to assign a connect and query procedure to FB for every connection from threads (Indy connections), so I m interested is this good approach or should I use some thread-safe list and then communicate with DB from single thread/connection. Although this is harder I think that is better for embeded DB systems. Thx in advance! ---------------------------------------------------------------------- swierzbicki · 2013-08-20 16:01 @sin_dragan : if I understand, you are sending data to an TCP server (TidTcpClient  => TidTCPCmdServer). The TCP server is than relaying the data to an MS SQL Express server. Is there any aknowledge when the data is posted ? What about if this is failing ? As said before, I'm using SQLite databases for my local storage. This is really neat and smooth ! All data is sent very 500ms to a remote SQL Server (Firebird in my case). As my data is locally stored, I can face network failure without any issue... Everything will be sent when network will be back. I don't think it to be the best approach but this is well working. The Firebird server is behaving correctly (as far as transaction are commited or rollbacked) but bear in mind that only two devices are currently connected : no lock and no freeze (and it doesn't really matters because all this stuff is done via a worker thread, so my main GUI is never impacted with slow downs and errors messages). ---------------------------------------------------------------------- sin_dragan · 2013-08-21 00:09 Quote from: swierzbicki on August 20, 2013, 06:01:37 pm@sin_dragan : if I understand, you are sending data to an TCP server (TidTcpClient  => TidTCPCmdServer). The TCP server is than relaying the data to an MS SQL Express server. That's correct. Indy creates thread for every connection on server, I ve set up a thread pool of 20 so the connections would be established faster, that way I can communicate with each scanner based on the request. Quote from: swierzbickiIs there any aknowledge when the data is posted ? What about if this is failing ? When client connects I relay the query statement directly from scanner, if I get result from DB server that AffectedRows is > 0 (1 in my case) I send reply to connection (201) if it's okay or else some other code error that the scanner will display to user. If connection fails, it displays the user message (if server unavailable, or if connection was lost during data transfer, but only if a full cycle is complete than the user can continue. Quote from: swierzbickiAs said before, I'm using SQLite databases for my local storage. This is really neat and smooth ! All data is sent very 500ms to a remote SQL Server (Firebird in my case). As my data is locally stored, I can face network failure without any issue... Everything will be sent when network will be back. I don't think it to be the best approach but this is well working. IMHO, I think that is a good approach, because the scanner can be out of range, the WiFi network could be down or something similar. SQLite is a solid DB, esspecialy for WinCe, I would use the same approach for offline data storing. I'm currently storing all the data on server, that way I don't need to setup clients, but eventually I will use the same techique. Quote from: swierzbickiThe Firebird server is behaving correctly (as far as transaction are commited or rollbacked) but bear in mind that only two devices are currently connected : no lock and no freeze (and it doesn't really matters because all this stuff is done via a worker thread, so my main GUI is never impacted with slow downs and errors messages).  Ok, thank you. I plan to use Zeos and FB, currently I m testing with Unidac and MsSql server.