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

  1. ‹ Older
  2. 10 months ago

    Tim S

    27 Dec 2018 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    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

  3. Jay M

    27 Dec 2018 Pre-Release Testers, Xojo Pro NC, USA
    Edited 10 months ago

    @Tim S 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

    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.

  4. Tim S

    27 Dec 2018 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

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

  5. Jay M

    27 Dec 2018 Pre-Release Testers, Xojo Pro NC, USA

    @Tim S Have to open the port permissions so it is accessible from outside right?

    Yes

  6. Bob K

    27 Dec 2018 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    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.

  7. Tim S

    28 Dec 2018 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    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

  8. Tim S

    29 Dec 2018 Canterbury, UK

    @Tim S 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....

    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.

  9. Tim S

    29 Dec 2018 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

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

    Thanks,
    Tim

  10. Ivan T

    29 Dec 2018 Pre-Release Testers

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

    Thanks,
    Tim

    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

  11. Tim H

    29 Dec 2018 Pre-Release Testers Portland, OR USA

    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.

  12. Tim S

    30 Dec 2018 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

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

    Tim

  13. Tim S

    30 Dec 2018 Canterbury, UK

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

    To which Tim are you responding?
    You should probably look here before investing too much time.

  14. Tim S

    30 Dec 2018 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    All Tims!... Tim Streater!

    Tim

  15. Oliver O

    30 Dec 2018 Pre-Release Testers, Xojo Pro https://udemy.seminar.pro

    The clean way would be to establish a middle tier on the raspi, that is a webservice which can read from and write to the sqlite database.

    Much faster to implement would be to install Cubesql server on the raspi.

    If you operate it with raspbian, a debian based linux system, then you can handle it like any Ubuntu server.

    The cubesql installation instructions for linux can be applied to the raspi as well.

    Cubesql of sqlabs.com is the multiuser version of sqlite. In your code you mainly have to change the connection type and code, otherwise it is probably the same (don’t know your code...)

  16. Richard D

    31 Dec 2018 Pre-Release Testers, Xojo Pro Europe (UK, London)

    @Oliver O Much faster to implement would be to install Cubesql server on the raspi.

    why do you need to install the CubeSQL on the raspi???
    i thought you install CUbeSQL on a machine that is going to be share with the different raspi

  17. Oliver O

    31 Dec 2018 Pre-Release Testers, Xojo Pro https://udemy.seminar.pro

    @Richard D 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.

    Tim Seyfarth wrote: “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.”

    But yes, I would set up a database server on a more powerful pc in the network and access it from all other devices.

    But if the requirements are to keep everything on the raspi, then cubesql would be the easiest way to go, imho.

  18. Richard D

    31 Dec 2018 Pre-Release Testers, Xojo Pro Europe (UK, London)

    can other raspi be able access the CubeSQL from the main raspi??

  19. Paul L

    31 Dec 2018 Xojo Inc, Third Party Store http://docs.xojo.com

    Like others, I suggest a standalone web app running a web service on the Pi. You can call the web service and have it grab the data from SQLite, package it as JSON and send it back.

    The EddiesWebService shows one way you could do this:

    Also these blog posts might be helpful:

  20. Oliver O

    31 Dec 2018 Pre-Release Testers, Xojo Pro https://udemy.seminar.pro

    @Richard D can other raspi be able access the CubeSQL from the main raspi??

    yes of course

  21. 9 months ago

    Sergio C

    4 Jan 2019 Pre-Release Testers, Xojo Pro

    You can also use SQLite Valentina Server.

or Sign Up to reply!