Is it possible to establish a read-only connection to an online .sqlite file?

Question

0.00/5 (No votes)

See more:

In my project, I am trying to use a online database which is hosted on my web server, and I want for users to be able to perform queries from their clients on my database.

Is there a way for me to allow them to issue and execute queries without having them download the entire database every time?

What I have tried:

Here is what I am working with: (C#)

C#

namespace Test
{
    class Program
    {
        public static SQLiteConnection m_dbConnection;

        static void Main(string[] args)
        {
            m_dbConnection = new SQLiteConnection(@"Data Source=https://dl.mydomain.com/Urls.sqlite");
            m_dbConnection.Open();
            test();
        }

        private static void test()
        {
            String query = "SELECT * FROM table";
            SQLiteCommand prepQuery = new SQLiteCommand(query, m_dbConnection);
            SQLiteDataReader reader = prepQuery.ExecuteReader();

            Console.WriteLine(reader["testcolumn"].ToString());
        }
    }
}

Posted 10-Mar-16 11:30am

Member 12383988

Updated 10-Mar-16 11:54am

Add a Solution

Comments

Maciej Los 10-Mar-16 17:51pm

Forget about SQLite. Use MySQL, PostgreSQL or any other free database server based engine. File based databases may fast get corrupted in LAN/WAN environment. SQLite is very good as one-in-time connection in local machine only.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2016-03-10T11:54:00

Solution 2

No, you can't. The clients cannot use a file-based database over HTTP.

Using SqlLite on the server, your only solution is to create a Web Service or WebApi the clients can call to do the database operations for the them.

Posted 10-Mar-16 11:54am

Dave Kreskowiak

Comments

Maciej Los 10-Mar-16 18:03pm

Short and to the point! +5!

Member 12383988 10-Mar-16 18:06pm

Alright. Thanks!

Maciej Los · Accepted Answer · 2016-03-10T11:54:00

I'd suggest to read SQLite documentation[^], which says exactly the same as i mentioned in the comment to the question.

Quote:

Server-side database

Systems designers report success using SQLite as a data store on server applications running in the datacenter, or in other words, using SQLite as the underlying storage engine for an application-specific database server.

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.

...

Situations Where A Client/Server RDBMS May Work Better

Client/Server Applications

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.