Page 1 of 2

SqLite in network

Posted: Thu Oct 20, 2011 12:44 pm
by Rimantas
Hi,
I begin project with FireBird , but I like SqLite . I found SqLitenning ( http://www.sqlitening.com/support/index.php ) as solution to work in network , but couldn't to force to work that . Maybe any of us have experience with SqLite in network enviroment ? I know that official opinnion is that SqLite can't work in network , but ... I hope that exist solution for that ... :-) .

With best regards !

Re: SqLite in network

Posted: Thu Oct 20, 2011 1:04 pm
by IBTC
Maybe using Harbour netio will be a solution. Using at server NETIO server app with access to SQLite and on the workstations a NETIO client app.

Re: SqLite in network

Posted: Thu Oct 20, 2011 3:34 pm
by Rimantas
IBTC wrote:Maybe using Harbour netio will be a solution. Using at server NETIO server app with access to SQLite and on the workstations a NETIO client app.
Very , very interesting ... :shock: :D . It worst to try , I think . Many thanks to you !

Re: SqLite in network

Posted: Thu Oct 20, 2011 4:23 pm
by Rimantas
IBTC wrote:Maybe using Harbour netio will be a solution. Using at server NETIO server app with access to SQLite and on the workstations a NETIO client app.
In sources of harbour I found , that hbnetio can work as Windows service , but can't to find info how to run in this mode . Can you help to me ?

With best regards !

Re: SqLite in network

Posted: Thu Oct 20, 2011 4:57 pm
by IBTC
Rimantas wrote:Can you help to me ?
Sorry, I don't know how hbnetio can work as Windows service. :( At the moment I don't use hbnetio yet. Maybe ask at Harbour user's mailing list.

Re: SqLite in network

Posted: Thu Oct 20, 2011 5:30 pm
by James Bott
I have not looked at SQLite in years, but the last I knew, it uses file locking to update a record (instead of record locking). It was really designed for a single user. But, perhaps this has changed.

Regards,
James

Re: SqLite in network

Posted: Thu Oct 20, 2011 6:01 pm
by Rimantas
James Bott wrote:I have not looked at SQLite in years, but the last I knew, it uses file locking to update a record (instead of record locking). It was really designed for a single user. But, perhaps this has changed.
James , nothing changed . It's all the same . Exist some technics which allows to work with SqLite in network , here they are : http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork . But ... it's very fine to work with SqLite with own tools - (x) Harbour + FwH . And Netio it seems can do the same as technologies in mentioned link . The NetIo idea is that all PC in network are working with SqLite database , but through Netio server . It can solve the problem of file locking for new records or update . I'll try that .

Regards !

Re: SqLite in network

Posted: Thu Oct 20, 2011 6:45 pm
by James Bott
Rimantas,

I don't understand how that works, but I will be interested to hear about your progress.

James

Re: SqLite in network

Posted: Fri Oct 28, 2011 2:32 pm
by Marco Turco
Hello Rimantes,
I'm using Sqlite and xHarbour since late 2010 in all my products and in network environments without problems.
Generally speaking, you don't need a client-server structure to operate on a network with Sqlite but you should cumulate the write calls inside a Begin Immediate->Commit sequence in order to minimize the file locktime.

To be clear, if you make

INSERT INTO TABLE 1 ....
INSERT INTO TABLE 2 ...
INSERT INTO TABLE 3 ...

you will have 3 lock-unlock loops

Instead, if you make

Begin immediata
INSERT INTO TABLE 1 ....
INSERT INTO TABLE 2 ...
INSERT INTO TABLE 3 ...
Commit

You have just a 1 lock-unlock loop

We have customers with also 25-28 client that operate without problem this way.
Are you using Sqlite3 with odbc or with a wrapper ?

Re: SqLite in network

Posted: Fri Oct 28, 2011 2:40 pm
by James Bott
Marco,

28 users on a network--that is good news. Thanks for the info.

James

Re: SqLite in network

Posted: Fri Oct 28, 2011 3:12 pm
by Marco Turco
Yes, and this is not all !! Using the "WAL" mode Sqlite3 also support concurrent write calls in multitasking on the same server.

To make a sample, I have a franchising company as customer with 7-8 associated companies in different locations.
They installed my app on a remote dedicated server and any company has 2-3 client connected via remote desktop to the remote server.
I have found Sqlite3 really a good dbf replacement: just a single db file, SQL92 syntax and ACID features.

Re: SqLite in network

Posted: Fri Oct 28, 2011 4:02 pm
by James Bott
Marco,

I looked at SQLite a number of years ago--maybe 5. If I remember correctly I had to get a third party C program to work with FWH and interface with SQLite. Is that still how it is done? Or is there a DLL or ActiveX module or something?

James

Re: SqLite in network

Posted: Fri Oct 28, 2011 4:11 pm
by Marco Turco
James,
there is an official wrapper for harbour but for xharbour - as I know - there is only an unofficial wrapper + fwh class to access to the Sqlite3 API developed by SSBBS a chinese FWH user. This is the access mode I'm using.

It's available on http://www2.zzz.com.tw/phpbb2/viewforum ... 99265acf02
It's for this reason I'm interested to know which wrapper is using Rimantes at this moment.

Re: SqLite in network

Posted: Fri Oct 28, 2011 5:31 pm
by Rimantas
Marco Turco wrote:Hello Rimantes,
I'm using Sqlite and xHarbour since late 2010 in all my products and in network environments without problems.
Generally speaking, you don't need a client-server structure to operate on a network with Sqlite but you should cumulate the write calls inside a Begin Immediate->Commit sequence in order to minimize the file locktime.

To be clear, if you make
INSERT INTO TABLE 1 ....
INSERT INTO TABLE 2 ...
INSERT INTO TABLE 3 ...

you will have 3 lock-unlock loops

Instead, if you make
Begin immediata
INSERT INTO TABLE 1 ....
INSERT INTO TABLE 2 ...
INSERT INTO TABLE 3 ...
Commit

You have just a 1 lock-unlock loop
We have customers with also 25-28 client that operate without problem this way.
Are you using Sqlite3 with odbc or with a wrapper ?
Marco , you provide so PERFECT VALUABLE INFORMATION !!! :-) How fine that is , many thanks to you !

At first , reading info about SqLite and network problems with them I didn't begin a seriuos working ( production ) project with Sqlite . Now I have a new project , small enterpise of production , ~10-12 PC in network . So SqLite for such project is ideal - no server , minimal requirements and administration and etc.

About locking - I'm working ONLY in such manier , as you described - begin ... insert/update one or some tables ... commit . In mine main workplace I'm working with Oracle , inserting/updating I'm doing only in this way some years alreday .

Because I was afraid to begin with SqLite database in network , I begin with fireBird . But now I'll return to SqLite . With FireBird I'm working with hbfbird from harbour\contrib ( I'm working with harbour ) . I was working with hbsqlt3 from harbour\contrib . Also I'll review solution from Ssbbs .

Many thanks to you , Marco !

With best regards !

Re: SqLite in network

Posted: Sat Oct 29, 2011 6:26 am
by RAMESHBABU
Mr.Marco,

You have given very good info regarding Sqlite.

May please know that:

How strong it is in security aspects ?

Can we protect databases with passwords like other flavours of SQL.

I found that SqliteManager is easily opening the Databases without any
security.

Regards,

- Ramesh Babu