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
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.
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
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
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
1 person found this helpful
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
pDB = xtra("arca").new()
Result = pDB.openDB(the moviepath & "ST_data")
if Result.errorMsg then
alert("Error Opening Database" & return & pDB.explainError(Result.errorMsg))
pTimeoutTime = 10000
pPingTime = 250
RunningQuery = false
pAlertBox = Sprite(1000) -- for example
on cleanup me
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.
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.")
pTimeOb.period = pPingTime
pAlertBox.setProgress((pPingCount * pPingTime / pTimeoutTime) * 100 )
if pPingCount * pPingTime = pTimeoutTime then -- timed out
alert("Query Timed out.")
exit -- try again in pPingTime time.
else -- there is some sort of database error
alert("Database Error: " & return & pDB.explainError(Result.errorMsg))
else -- no query errors
call(pCurOperation.CallbackMethod, pCurOperation.CallbackOb, Result)
-- if the code makes it this far then we are done and need to clean things up
if pTimeOb. objectP then
pTimeOb = void
RunningQuery = false
on setTimeOutTime me, MilliSecs
pTimeoutTime = MilliSecs
on setPingTime me, MilliSecs
pPingTime = MilliSecs
You then create an instance of this script on preparemovie.
-- Movie script
gDB = script("Asynchronus SQLite").new()
if gDB.voidP then halt -- can not connect to the database
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
on mouseUp me
Query = "select * from users"
gDB.executeSQL(Query, me, #setQueryResult) -- string, callback object, callback handler name
on setQueryResult me, Result -- this is the callback handler/method
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.
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.
am having abit of trouble plugging this in , is there any chance I can send you the files to have a look at?
or if there is a simpler solution to just loop until the DB has unlocked that would be really helpful too.
If you can not get what I posted to work, then you will have to come up with something on your own.
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