10 Replies Latest reply on Feb 14, 2011 1:54 AM by whitestar7

    ARCA Xtra  - multiple user access to SQLite DB

    whitestar7 Level 1

      Hi Guys,

       

      I have having trouble with multiple users accessing a SQLite DB file from a projector. They can all open and browse the data through the projector fine, but if USER1 makes a change and clicks save, and then USER2 tries access the DB it causes USER2 to get a script error.

       

      Is it possible to write a script which tells the user that the DB is being modified so please wait, rather than throwing them out of the projector with a script error

       

      Thankyou

        • 1. Re: ARCA Xtra  - multiple user access to SQLite DB
          whitestar7 Level 1

          hi folks,

           

          so thankfully through ARCA's excellent customer support I have learnt that I can determine that an error has occurred, but not sure how I can do the rest.

           

          res = gDB.executeSQL('select * from users') -- selects all entries in the DB
          if res.errorMsg = 5 then        --error code 5: "The database file is locked"

          alert ("Database already in use please click here to retry after 10 seconds")

           

          if the timer >10000 then

           

          --it is here i get even more stuck, I want to tell it to retry the last task it was trying to do

           

          please help if you can

          • 2. Re: ARCA Xtra  - multiple user access to SQLite DB
            Sean_Wilson Adobe Community Professional

            I'm not familiar with Arca, but is there some sort of DB.close() command? Perhaps you need to void your database reference/s in order to release the file on disk.

            • 3. Re: ARCA Xtra  - multiple user access to SQLite DB
              whitestar7 Level 1

              I can't close the connection because of the way the data is pulled from the DB file.

               

              All I need to do is

               

              1- if an error is found then display an alert

               

              2- Display the alert until the error code is no longer found

               

              3-Continue where the user was before the alert

               

              If I can get a rough idea of how I could do this, I should be able to adapt the code to suit the Db commands

              • 4. Re: ARCA Xtra  - multiple user access to SQLite DB
                whitestar7 Level 1

                I have made abit of a break through with the following code

                 

                  res = gDB.openDB(the moviepath & "ST_data") -- this opens the database file
                  if res.errorMsg <> 0 then -- checks to see if there is any error
                    errorAnswer = baMsgBox( "Database is already in use", "Do you want to quit or retry" , "RetryCancel", "Question" , 2) ---alert box which prompts the user
                    if errorAnswer = "Retry" then _movie.go(1) --- this is suppose to start the whole movie again or at least the first line of the current script
                    else if errorAnswer = "Cancel" then quit -- this quits the projector
                  end if

                 

                 

                 

                The problem I am having is that it doesnt go back to the beginning each time, it goes to frame 1 on the first click but after that it just runs through the rest of the code in the script

                • 5. Re: ARCA Xtra  - multiple user access to SQLite DB
                  Production Monkey Level 3

                  If you want multiple users to access a database then you would probably be better off using one designed for that purpose like MySQL.

                   

                  But, if you are set on SQLite and if you can have multiple simultaneous connections which you seem to imply you can, then the best solution I can think of is to create a Class/Script that handles all the details of polling the database for availability and handles other issues like a progress dialogue box.

                   

                  You have to treat the database as an asynchronous action - meaning you send it a query and at some latter time it sends a result back via a callback. Doing it this way greatly simplifies any database queries you want to make. As long as your queries are fairly simple like SELECT, INSERT, DELETE, etc. then the code is fairly straight forward.

                   

                  Off the top of my head I wrote something to get you started. I have not tested this code but it does compile. The following is a Parent script that you would use to interface to your database in an asynchronous manner.

                   

                  -- Asynchronus SQLite

                   

                  property  pDB  -- instance of Arca xtra
                  property  pTimeoutTime  -- how long in milliseconds to ping database
                  property  pPingTime  -- time between database pings.
                  property  RunningQuery  -- Boolean. is a query running? true/false
                  property  pTimeOb  -- timout object for polling the database
                  property  pCurOperation  -- set of data for current query
                  property  pPingCount  -- how many times the current query has pinged the database
                  property  pAlertBox  -- a MIAW, LDM, or a Sprite that informs the user as to the progress of the query.

                   


                  on new me
                    arcaregister([0000,000,0000])
                    pDB = xtra("arca").new()
                   
                    Result = pDB.openDB(the moviepath & "ST_data")
                   
                    if Result.errorMsg then
                      alert("Error Opening Database" & return & pDB.explainError(Result.errorMsg))
                      return void
                    end if
                   
                    pTimeoutTime  = 10000 
                    pPingTime  = 250
                    RunningQuery = false
                    pAlertBox = Sprite(1000)  -- for example
                   
                    return me
                  end new

                   


                  on cleanup me
                    pDB.closeDB()
                  end cleanup

                   


                  on executeSQL me, Query, CallbackOb, CallbackMethod, OptionalParameters
                    if RunningQuery then exit -- only allow one query at a time
                   
                    RunningQuery = True
                    pCurOperation = [#Query:Query, #OptionalParameters:OptionalParameters, #CallbackOb:CallbackOb, #CallbackMethod:CallbackMethod]
                    pPingCount = 0
                    pTimeOb = timeout().new("QueryProcessor_"&me, 1, me, #processQuery)  -- creating the timeout object here breaks the call stack, which is good.
                  end executeSQL

                   


                  on processQuery me, TimeOb
                    Result = pDB.executeSQL(pCurOperation.Query, pCurOperation.OptionalParameters)
                   
                    if Result.errorMsg then
                      if Result.errorMsg = 5 then -- database is currently locked
                        pPingCount = pPingCount + 1
                        if pPingCount = 1 then  -- then inform user there will be a delay.
                          pAlertBox.setMessage("Waiting for database response.")
                          pAlertBox.setProgress(0)
                          pAlertBox.show()
                          pTimeOb.period = pPingTime
                          exit
                        end if
                       
                        pAlertBox.setProgress((pPingCount * pPingTime / pTimeoutTime) * 100 ) 
                       
                        if pPingCount * pPingTime = pTimeoutTime then -- timed out
                          alert("Query Timed out.")
                        else
                          exit  -- try again in pPingTime time.
                        end if
                      else  -- there is some sort of database error
                        alert("Database Error: " & return & pDB.explainError(Result.errorMsg))
                      end if
                    else  -- no query errors
                      call(pCurOperation.CallbackMethod, pCurOperation.CallbackOb, Result)
                    end if
                   
                    -- if the code makes it this far then we are done and need to clean things up
                    if pTimeOb. objectP then
                      pTimeOb.forget()
                      pTimeOb = void
                    end if
                   
                    pAlertBox.hide()
                    RunningQuery = false
                  end processQuery

                   


                  on setTimeOutTime me, MilliSecs
                    pTimeoutTime = MilliSecs
                  end setTimeOutTime

                   


                  on setPingTime me, MilliSecs
                    pPingTime  = MilliSecs
                  end setPingTime

                   

                  ----------------------------------------------------------------------------

                   

                  You then create an instance of this script on preparemovie.

                   

                  -- Movie script

                   

                  global gDB

                   

                  on prepareMovie
                    gDB = script("Asynchronus SQLite").new()
                    if gDB.voidP then halt -- can not connect to the database
                  end

                   

                  on stopMovie
                    gDB.cleanup()
                  end

                  --------------------------------------------------------

                   

                   

                  Then it is simply a matter of sending your queries to the gDB object and it will send the results back to the callback handler and object that you specify. Here's a behavior that shows how simple this should be:

                   

                  -- Sample DB Behavior

                   

                  global gDB

                   

                  on mouseUp me
                    Query = "select * from users"
                    gDB.executeSQL(Query, me, #setQueryResult) -- string, callback object, callback handler name
                  end

                   


                  on setQueryResult me, Result  -- this is the callback handler/method
                    put Result
                  end

                  ----------------------------------------------------------

                   

                   

                  I also suggest using a MIAW or a LDM or a set of sprites as a way to inform the user of any delays in processing a query. Check the code for pAlertBox to see how I use this idea to update a progress bar. Of course you will have to create the implementation.

                  1 person found this helpful
                  • 6. Re: ARCA Xtra  - multiple user access to SQLite DB
                    whitestar7 Level 1

                    Hi PM,

                     

                    Thanks for the detailed reply, I will give this a go and let you know how I get on.

                     

                    Thanks again for the excellent help.

                    • 7. Re: ARCA Xtra  - multiple user access to SQLite DB
                      whitestar7 Level 1

                      hiya,

                       

                      am having abit of trouble plugging this in , is there any chance I can send you the files to have a look at?

                      • 8. Re: ARCA Xtra  - multiple user access to SQLite DB
                        whitestar7 Level 1

                        or if there is a simpler solution to just loop until the DB has unlocked that would be really helpful too.

                         

                        Thanks again

                        • 9. Re: ARCA Xtra  - multiple user access to SQLite DB
                          Production Monkey Level 3

                          If you can not get what I posted to work, then you will have to come up with something on your own.

                          • 10. Re: ARCA Xtra  - multiple user access to SQLite DB
                            whitestar7 Level 1

                            Hi PM, the problem I can see is that the Director file accesses about 20 fields, of which some are images, and the code you mentioned is struggling to read the fields from the DB.

                             

                            Thanks for your help, am gonna keep plugging away