Access sqlite from a remote server

问题:

I am wondering how i can access an sqlite database that is sitting a remote server.I have read threads discouraging it but i need to do it if its possible.

     /*
QUrlOperator xc("http://example.com");
xc.get("testdatabase.db");
*/

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

  db.setDatabaseName(xc.get("testdatabase.db"));
  //idea

  if( !db.open() )
  {
    qDebug() << db.lastError();
    qFatal( "Failed to connect." );
  }

回答1:

SQLite isn't a network database, so it doesn't have any network connection ability built into it.

You would need to either:

• Make the SQLite DB available on a network-accessible shared drive OR
• Write/find an existing network server/web service for it

A web application is essentially a web service. If you happen to be running a web application on top of this DB, just expose certain levels of DB access to admins-only.

It's not recommended you do this because multiple threads/clients/etc. accessing a SQLite DB simultaneously may lead to concurrency problems very quickly.

回答2:

Navicat can connect via SSH to a remote sqlite database.

For smaller project im using phpliteadmin

回答3:

sqlite is a document database meaning that its pretty much just a flat file store of your data with only the most minimal database engine on top, that is why it is like 300kb total. What you can do as a solution is to copy the db from your remote location to your location via ftp or access it by assigning a network share location to it. Be warned though that only 1 user can write to sqlite at a time.

回答4:

You cannot directly access an sqlite database remotely like that. If you need to share it, you will have to develop a web service and talk to the web service rather than the database from your Qt Application.

There are some tools which act as a web service for stuff like that, e.g. this site has some links.

回答5:

SQLite is file-based only. There is no way to talk to it over TCP/IP.

Like an Access database file, you have to have the database file in a network shared folder. The path is usually going to be a UNC path, like "\server\sharename\folderpath\databasefile".

The problem is the connection string you built isn't a connection string. It's a partial string. You have this:

DataSource = @"\\\\" + txtipaddress.Text + @"\qscanDBAttacheds\test.s3db;Version=3;New=False;Compress=True;"

The "DataSource" part has to be part of the string you build. You don't have that. You need this:

string connString = @"Data Source=\\" + txtipaddress.Text + @"\qscanDBAttacheds\test.s3db;Version=3;New=False;Compress=True;"

回答6:

You could use php on the server to run whatever sql commands you need and just navigate to the php page from the device.

回答7:

you can consider switching to PostgreSQL instead of SQLite, the database manipulation is very similar. The little differences in handling primary keys etc you can google. For example in the INSERT statement you'd pass a NULL in SQLite, as a primary key and in Postgre you'd pass it as DEFAULT (given you want the database to create the key for you).

In Python3 for example, the code difference I know of is that PostgreSQL allows remote connection and that avoiding SQL injection syntax is slightly different, as SQLite3 way would be with ? and PostgreSQL9.5 would be with %s

Switching to Postgre would provide you a remote connection possibility and it is a definite step up from using a local database (which is only designed for small to medium bandwidth applications).