10 Replies Latest reply on Mar 5, 2008 10:03 AM by Tickster

    How to access SQLITE db on Local Network

    Tickster Level 1
      I have an AIR app that I would like to be able to have all users in the office read from the same SQLITE db located on our local network or local server. Is this possible?
        • 1. Re: How to access SQLITE db on Local Network
          Funkystrat Level 1
          Developed in Flash or Flex?
          • 2. Re: How to access SQLITE db on Local Network
            Tickster Level 1
            Hi Funkystrat,
            I'm developing this in html/javascript. I'm not at all oppose to developig this in Flash if it's just not possible using html/javascript.

            Thanks.
            • 3. Re: How to access SQLITE db on Local Network
              quote:

              Originally posted by: Tickster
              I have an AIR app that I would like to be able to have all users in the office read from the same SQLITE db located on our local network or local server. Is this possible?

              Certainly possible, but not advisable.
              Read section 6 of this:
              http://www.sqlite.org/lockingv3.html

              I guess if you were only ever reading (not writing) it wouldnt be a problem though.


              • 4. Re: How to access SQLITE db on Local Network
                Tickster Level 1
                Only the admin will modify the db and that may be once in a blue moon... everyone else will only read the db. I forgot to mention, the APP is in html/javascript.
                • 6. Re: How to access SQLITE db on Local Network
                  anirudhs Level 2
                  Hi,

                  This is definitely possible in HTML / javascript. Just keep your database file (sqlite db file) in a shared folder or network mapped drive and everyone can read from it.

                  Updates will use the operating system specific file locking mechanism, so this may not be the best thing to do if there are multiple concurrent updates.

                  But in your case, it should be fine.
                  • 7. Re: How to access SQLITE db on Local Network
                    Stonehippo2 Level 1
                    First: This is a pretty brittle architecture. SQLite can handle concurrent reads but it's reliant upon the OS to handle locking which means that you could get database corruption in some circumstances. In addition to corruption, you may get flat-out failures.

                    For example, in the demo app I created for you the application code that creates the database failed when I used a mounted remote drive on my Mac. This is because OS X doesn't implement the Windows FileLock() method (as described in the SQLite docs). You may also run afoul of permissions on the database file on the network share.

                    If you still want to do this, I've put together code for a demo app that creates a new database file on whatever path you specify in the input field, including a network share mapped to the system. For example, if you had drive F: mapped to \\some_server\some_share, you could use this to create a new database on the share by entering F:\\databasename.db in the input field and clicking the create database button. Note that if the database already exists the "create" code will actually open the database, not create a new one.

                    This demo is short on error handling but it shows how to work with the remote database and it should be taken as nothing more than a demo. It's free to use in any way you see fit, but there's no warranty; use at your own peril. :)

                    **** remotesql.html ****
                    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
                    " http://www.w3.org/TR/html4/strict.dtd">
                    <html>
                    <head>
                    <script type="text/javascript" src="AIRAliases.js"></script>
                    <script type="text/javascript" src="AirSourceViewer.js"></script>
                    <script type="text/javascript" src="code.js"></script>
                    </head>
                    <body onload="init();">
                    <button id="viewSource">View Source</button>
                    <hr>
                    <input type="text" id="dbPath" name="dbPath" value="">
                    <button id="createDb">Create Database</button>
                    <br>
                    <textarea id="console" cols="50" rows="10"></textarea>
                    </body>
                    </html>

                    **** code.js ****
                    /* A trivial logging method */
                    function log(message) {
                    // write the log to a control on the page
                    var c = document.getElementById("console");
                    c.value += message + "\n";
                    // dupe log messages to the command line for ADL
                    air.trace(message);
                    }

                    /* Asynchronously create or open a database */
                    function createDatabase() {
                    var dbPath = document.getElementById("dbPath").value;
                    if (dbPath) {
                    var dbFile = new air.File(dbPath); // if dbPath refers to a network share or mounted drive, that's where the DB will go
                    var conn = new air.SQLConnection();
                    conn.addEventListener(air.SQLEvent.OPEN, dbOpenHandler);
                    conn.addEventListener(air.SQLErrorEvent.ERROR, dbErrorHandler);
                    conn.openAsync(dbFile); // this will open the database if it exist or create it if it does not (assuming the dbPath is ok)
                    }
                    }

                    function dbOpenHandler(event) {
                    log("Created database");
                    }

                    function dbErrorHandler(event) {
                    log("Error message:" + event.error.message);
                    log("Details:" + event.error.details);
                    }

                    /* Utility function to attach event handlers to controls in the page
                    function attachEventHandler(element, event, handler) {
                    var e = document.getElementById(element);
                    if (e) {
                    e.addEventListener(event, handler);
                    }
                    }

                    /* Set up the application code */
                    function init() {
                    attachEventHandler("viewSource", "click", air.SourceViewer.getDefault().viewSource);
                    attachEventHandler("createDb","click", createDatabase);
                    }

                    • 8. How to access SQLITE db on Local Network
                      Tickster Level 1
                      Thanks for the detailed example Stonehippo2 and attention to my question guys.

                      I feel a little stupid for even asking this question in the first place since all I had to do was change the path to the path of the db on the network. The reason why it was not able to locate the db on the network was due to the fact that I'm developing this on a MAC and most of the end users are on PCs, hence the network paths to the db would be different between the two OS.



                      This script was all I needed all along... thanks for the time guys, much appreicated.

                      dbPath = "";
                      dbPathMac = "/Volumes/server-name/myDb.db";
                      dbPathWin=" \\some-volume\server-name\myDb.db";

                      if (navigator.appVersion.indexOf("Win")!=-1) dbPath=dbPathWin;
                      if (navigator.appVersion.indexOf("Mac")!=-1) dbPath="dbPathMac";

                      • 9. Re: How to access SQLITE db on Local Network
                        Stonehippo2 Level 1
                        Keep in mind what I mentioned about file locking. The mounted volume on the Mac will not honor the Windows FileLock() call. This could lead to some issues if you're writing from the Mac (or if you attempt to create the DB from within AIR on the Mac). It should not present too much of an issue for your read-only clients, but again, it's something to watch out for.

                        Anyhow, good luck with your app! :)
                        • 10. How to access SQLITE db on Local Network
                          Tickster Level 1
                          Bah!!

                          Stonehippo, you're right, MAC has that fileLock issue. :(

                          I get the "Database file is currently locked." error.

                          Even if just to read. I must be doing something wrong still... the search continues.