Re: [sqlite] Multiple network connections
Am 25.12.2012 22:18, schrieb Çağlar Orhan: But every client has MS *.hta files and connecting to sqlite over ODBC driver with vbscript. What you rely on in this case, has nothing to do with the WebServer (IIS) ... meaning the WebServer is not used as a decoupling layer (over http). What you do instead is FileLevel-Access over a (Network-) Share (SMB/CIFS). In this mode, the client-machines are opening the DB separately - and the DBFiles Pages are transferred to *each* client independently: [.hta->vbscript->ADO(ODBC)->]...network...[<-SMB/CIFS] That means 5-10 or few larger client counts make connections to that unique sqlite file on the IIS server. As said, these connections have nothing to do with the IIS (as a Service on your ServerHost). Your ServerHost-*Machine* was simply set up, to provide (beside the IIS) also the SMB/CIFS-Service, to be able to act as a (Network-)FileServer (which then interacts with your clientside ADO/ODBC-Driver at the Network-FileLevel). In this mode, SQLite-concurrency is handled over (NetworkLayer-) FileLocks - and these FileLocks do make problems sometimes. The more stable mode would be, to use your IIS as a real decoupling layer, and access the DB-Files at the server-end *locally* (e.g. behind an *.asp-File, which can hold VBScript too - but delivers disconnected Rs to your Clients, after retrieving them over a local DB-Connection against a Server-local SQLite-File): [.hta->vbscript->XMLhttp-Obj]...network...[<-IIS<-.asp<-ADO(disc. Rs)] Your current (CIFS-)access-mode would also work, when you completely disable the IIS-WebService on your Server-Machine - and open your ADO(ODBC)-connections directly within VBScript (without any .hta). If you have very stable Network-Connections (no WLAN involved, very stable ClientMachines (no Notebooks, which may power down "on an open connection" due to longer inactivity), then the SMB/CIFS- Mode can work relatively reliable, especially when you really only have 5-15 clients "to serve" in that mode. But in case there's more clients - or when you have a certain potential for "client-machines, disconnecting unexpectedly" - then it's better to work over IIS/.asp instead of CIFS, to do "server-local" DBFile-Access over http. But also that "Server-local-mode" could cause certain concurrency- problems (but then more performance-related ones) ...not as many as when working with Network-Filelocks as the only possible "concurrency-signalling-mechanism" available to SQLite. Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple network connections
Çağlar Orhan Hi Larry, thank you for your explanatory answer. I have an idea now, but with my poor English am afraid i did understand wrong or incomplete :( I am going to try my network layout. We have an IIS as a server and clients have MS Win7 on their PCs. But usually we used IIS machine as a clasical static file storage. So i do. There is one mydb.sqlite file as DB on somewhere at IIS. IIS not to pay any atention on the file and not to run any application on itself. But every client has MS *.hta files and connecting to sqlite over ODBC driver with vbscript. That means 5-10 or few larger client counts make connections to that unique sqlite file on the IIS server. Every client (5-100) all over the network has their own application which takes aim at mydb.sqlite file as DB target source. Basically proceses are just read and write to table. And you wrote "What Jay and others warn about is that SQLite is not designed for concurrent DB operations. Also, have its file store be accessed over a network from SQLite code running remotely is a formula for database corruption. But it looks like you are not doing that." Is my situation comply with warning :( From your first post, I understood that the IIS server would be doing all the SQLite operations, (likely from multiple threads, although that could only be surmised). Now, it appears that you have clients on different machines accessing a SQLite file over the network via their own SQLite code. This is precisely what the warning advises against. The problem is this: Even though the SQLite design anticipates different processes operating on the same database file, it guards against concurrent access issues by using file locking and another file located "beside" the database file to control access and provide journalling. If file access over a network had the same semantics and predictability as machine-local file access, then there would no reason your usage would not work. Unfortunately, file access over a network has known weaknesses with respect to file locking. This means that such access involving running SQLite code and a remote database file is unreliable when multiple clients may perform nearly concurrent operations. Just as network file locking sort of works, your application(s) may sort of work, at least enough for some simple tests to seem successful. You would do well to avoid deploying such a solution. You might be interested in this "Code Project" article, and perhaps its code: http://www.codeproject.com/Articles/22416/A-SQLite-Server-Client-Implementation Thank you, You're welcome, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple network connections
Hi Larry, thank you for your explanatory answer. I have an idea now, but with my poor English am afraid i did understand wrong or incomplete :( I am going to try my network layout. We have an IIS as a server and clients have MS Win7 on their PCs. But usually we used IIS machine as a clasical static file storage. So i do. There is one mydb.sqlite file as DB on somewhere at IIS. IIS not to pay any atention on the file and not to run any application on itself. But every client has MS *.hta files and connecting to sqlite over ODBC driver with vbscript. That means 5-10 or few larger client counts make connections to that unique sqlite file on the IIS server. Every client (5-100) all over the network has their own application which takes aim at mydb.sqlite file as DB target source. Basically proceses are just read and write to table. And you wrote "What Jay and others warn about is that SQLite is not designed for concurrent DB operations. Also, have its file store be accessed over a network from SQLite code running remotely is a formula for database corruption. But it looks like you are not doing that." Is my situation comply with warning :( Thank you, Regards, Caglar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple network connections
Çağlar Orhan wrote: While i was reading "*Using SQLite*" from *Jay A. Kreibich*, i saw that sqlite doesn't designd for over network multiple client connections. I have a project that built on Microsoft .hta and vbscript. And I am using sqlite on the IIS for local storage but, 5 to 10 client applications connecting as a network database to that sqlite. SQLite file is in a shared folder. I made some test that 2 or few more clients inserting, updating and query same tables. I couldn't catch any inconsistency about table integrity. But on the book, because of there is no native support for providing access to multiple computers over a network, cause problems. Like database file corrupt etc. Is there a solution for network shared sqlite problematic? If your SQLite DB is being accessed locally by the webserver, then there is no reason to worry about consistency issues due to the webserver's network activity, provided that your application can handle the access delay issues that arise when multiple threads may attempt overlapping DB operations. If you are happy with the performance and the number of clients is not likely to grow much, SQLite could be a good solution. What Jay and others warn about is that SQLite is not designed for concurrent DB operations. Also, have its file store be accessed over a network from SQLite code running remotely is a formula for database corruption. But it looks like you are not doing that. Thank you You're welcome. -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple network connections
While i was reading "*Using SQLite*" from *Jay A. Kreibich*, i saw that sqlite doesn't designd for over network multiple client connections. I have a project that built on Microsoft .hta and vbscript. And I am using sqlite on the IIS for local storage but, 5 to 10 client applications connecting as a network database to that sqlite. SQLite file is in a shared folder. I made some test that 2 or few more clients inserting, updating and query same tables. I couldn't catch any inconsistency about table integrity. But on the book, because of there is no native support for providing access to multiple computers over a network, cause problems. Like database file corrupt etc. Is there a solution for network shared sqlite problematic? Thank you Caglar ORHAN ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
