Concurrent SQLSelect to a readonly database in a Web application

  1. last week

    Maurizio R

    May 18 Pre-Release Testers, Xojo Pro

    I'm willing to use an in memory SQLiteDataBase in a web application using only SQLSelects i.e. with no data modification.
    I'm planning to use only one database Connect at the application level and execute multiple and concurrent SQLSelect at the session level.

    Is this the right way to use a "readonly" database in this scenario?

    Thanks in advance for your suggestions.

    @Maurizio R Ok Norman, this was my concern.
    How can I have a single in memory database where multiple sessions are connected to?

    Pretty sure you cant - I cant think of any way to
    But how are you going to populate it ?
    If its "read data from disk" then why not just use one on disk with many connections ?

  2. Dave S

    May 18 San Diego, California USA

    how can you use an "in-memory" database?
    Which session is going to populate it? (at which time it is no long a read-only)

  3. Norman P

    May 18 Xojo Inc

    In memory or not you should not have one connection for ALL queries
    This is a recipe for problems
    Each session should have a connection - which isn't a problem

  4. Maurizio R

    May 18 Pre-Release Testers, Xojo Pro

    Ok Norman, this was my concern.
    How can I have a single in memory database where multiple sessions are connected to?

  5. Maurizio R

    May 18 Pre-Release Testers, Xojo Pro
    Edited last week by Maurizio R

    Hi Dave,
    the database will be an app property loaded in memory, using the backup method, during the app.open event.

  6. Norman P

    May 18 Xojo Inc Answer

    @Maurizio R Ok Norman, this was my concern.
    How can I have a single in memory database where multiple sessions are connected to?

    Pretty sure you cant - I cant think of any way to
    But how are you going to populate it ?
    If its "read data from disk" then why not just use one on disk with many connections ?

  7. Maurizio R

    May 18 Pre-Release Testers, Xojo Pro

    Why in memory:

    1. it's not so big (few Mbytes)
    2. I don't want the file be locked in the filesystem

    Point 2 is the more important: it seems that the file is locked when a Connect is done and some other utilities complains about this.

  8. Maurizio R

    May 18 Pre-Release Testers, Xojo Pro

    Just to be clear, I wasn't sure if the SQLSelect method can be called from different threads on a single Connect or must be called from different threads where each thread have a private Connect.

    Thank you Norman for your reply.

  9. Norman P

    May 18 Xojo Inc

    1) many connections to in memory db's will require that each basically has its own copy so this will increase the memory your app requires. A single shared read only db on disk will require less.

    2) I'm curious why #2 matters ?
    You cant replace the file on disk when its open (and I'd suggest this is a bad thing to do)

  10. Maurizio R

    May 18 Pre-Release Testers, Xojo Pro

    The db will be in memory as a property of the app so as a single copy.
    The select statements will be serialized using a critical section: performances are still good.

    #2 matters only because the current utility used to backup, not to overwrite, the db complains about the locked state.
    This will be adjusted/changed in the future.

or Sign Up to reply!