How to access SQLite from Raspi on a Raspi or from Windows on a Raspi - Read Only

Databases
1

Hello all.

I need to access a SQLite database running on a Raspberry pi from either or another Raspi or a windows machine. This is a read only connection, so no worry about latency or file locking (I think).

Thanks in advance!
Tim

2

SQLite is NOT for shared enviroments.

  • If it is read only, just copy the same file to all the devices.

  • Create a simple service in the device with the database and send the responses in JSON

  • Use a real dbms

3

Hello Pedro.

Cannot copy to all, unless it was to be done frequently which makes little sense. Just want to read certain tables.

How to connect from RPI to RPI over network? Just use an IP + path or what?

BTW, even with PostgreSQL, which is my alternative, I’d still have the same question - is connecting different?

Thanks,
Tim

4

I don’t think PostgreSQL server works on a Raspberry pi, which is what you’ll need to have a PostgreSQL database.

SQLite must have direct access to the database file, so you’ll need a file sharing connection to the pi that has the database.

5

Connecting to a database server from different machines is easy. There is no easy to do what you’re asking for since it’s a SQLite database.

However, using a TCP socket on the Pi you can have any computer on your network connect to it via a TCPSocket and query for what data is desired. The drawback is that you do all the work of packaging the query and result messages. Take a look at EasyTCPSocket as it will simplify the process.

6

Thanks again for the responses everyone.
Postgre can run on the pi - here’s a nice little how to
https://opensource.com/article/17/10/set-postgres-database-your-raspberry-pi

Bob, how do you connect to a POSTgreSQL server from a different machine? Sounds like that is what I will need to do…
Thanks again all!
Tim

7

Just like you connect to ANY Postgres DB. I don’t understand the question since Postgres is a DB server it’s designed to be connected to by other computers. At that point you don’t need Postgres on the Pi, just on a computer on the network. AFAIK Pi apps can connect to Postgres (never tried myself).

8

Hi Bob,

The Pi is the master, doing all of the work. The other is to display certain info. When using Postgre, or Pervasive, I’ve only ever connected on the same computer, never over a network, internet etc…

Tim

9

[quote=419494:@Tim Seyfarth]
Postgre can run on the pi - here’s a nice little how to
https://opensource.com/article/17/10/set-postgres-database-your-raspberry-pi[/quote]
Wow, didn’t know that!

To connect to PostgreSQL on another machine, supply the hostname, which can be a network name or simply a IP address, and of course port, username and password.

10

Have to open the port permissions so it is accessible from outside right?
Thanks again!
Tim

11

Yes

12

Still seems like overkill. If the Pi is connected to a network there’s no reason why it can’t just connect to a db server.

13

Hi Bob,
The issue is, all of the data is collected and maintained on the pi. So if a server is required, it must be on the pi.

I thought I could just connect to the SQlite db to do a read, and maybe I can; especially since there is no time sensitivity with the reading. The other PI or PC will be displaying a graphical representation which can be many seconds off from reality…

Thanks again!

Tim

14

[quote=419597:@Tim Seyfarth]I thought I could just connect to the SQlite db to do a read, and maybe I can; especially since there is no time sensitivity with the reading. The other PI or PC will be displaying a graphical representation which can be many seconds off from reality…
[/quote]
Well if all you’re doing is reading then you probably could connect to it to do a read, although this sort of thing is not recommended (particularly if you were writing to the remote SQLite db). The problem: networked file systems tend to lie about whether they’ve actually written data to disk. You’re reading in this case so it probably won’t matter.

Make sure you set a timeout for your connection to avoid getting SQLITE_BUSY on your actions.

15

So to connect, do I use an IP + an internal path to the SQLite database?

Thanks,
Tim

16

[quote=419642:@Tim Seyfarth]So to connect, do I use an IP + an internal path to the SQLite database?

Thanks,
Tim[/quote]
lol

It is better if you make a web service to share the fata with a json. The server can connect to the local database

17

Tim, You cannot access an SQLite database via IP. That’s for database services such as Postgres. SQLite is strictly a file based system designed to be used on the local drive. AFAIK, you cannot open an SQLite database read-only from Xojo. And you shouldn’t attempt to access it via a mapped drive. SQLite cannot handle multiuser file locking. The “MultiUser” property is misleading. It allows concurrent access from multiple processes on the same machine. Not true multiuser access.

18

Hi Tim.
Happy new year to you!
So it’s just a simple path, will try it out and see how it goes.

Tim

19

[quote=419736:@Tim Seyfarth]Hi Tim.
Happy new year to you!
So it’s just a simple path, will try it out and see how it goes.[/quote]
To which Tim are you responding?
You should probably look here before investing too much time.

20

All Tims!.. Tim Streater!

Tim