Result pane doesn't update

I’m having an issue at the moment where when stepping through a script the result pane doesn’t update.

I can view variables, but the result window just says “No Result” at every step.

I’ve hit Best, Source and AEPrint, but no change.

This is happening in multiple scripts at the moment. I think I’ve seen similar behavior before.

Would a sample help?

A sample nearly always helps.

So this is interesting. I quit everything and restarted the mac.

I started working on DB-E/sqlLite2 scripts, and before I had gotten very far at all, the result window had stopped working.

This wasn’t after numerous trials and errors, it was literally after a single error in SQL Lite. Now on this script the result window is stuck on the last result before the error, so I can even tell you which line the result stopped updating.

In the EstablishSQLDatabase handler the first line is the last result showing

set updateString to "create table standings (" & fieldNames & ")"
	set sqlDbFilePath to POSIX path of fileLocation

About this script, I had a version of this that works, that was specific to a DBEvents database. I’m tweaking it to make it apply to any DBEvents database. (The specific database was the academy awards, and some of the variables and messages are still geared toward that)

The good news is I have now cloned a big Oacars database to SQLLite (10k+records), and now I just need to learn how to do all the queries I need to do. (And hope it’s better and faster than DB Events)

use script "Myriad Tables Lib" version "1.0.9"
use script "SQLite Lib2" version "1.0.0"
use script "FileManagerLib" version "2.3.3"
use scripting additions
property sqlDb : ""
property sqlDbFile : ""
set startTime to current date
set {databaseFile, fileName, fileLocation} to PickDBEVFile()
--set {databaseFile, fileName, fileLocation} to {"/Users/stocklys/Documents/Databases/Oscar Database.dbev", ¬
--   "Oscar Database", ¬
--   "Macintosh HD:Users:stocklys:Documents:Databases:"}
set sqlLCloneName to fileName & "-sqlL"
tell application "Database Events"
  launch
  tell database databaseFile
     open
     properties
  end tell
  tell its database fileName
     set recordCount to count of records
     set fieldNames to name of every field of record 1
  end tell
  --or set field names to {"name1", "name2"}--... making sure you get all the field names
end tell
EstablishSQLDatabase(sqlLCloneName, fileLocation, fieldNames)
set moviesFoundCount to 0
set movieCount to 0
set foundMovies to {}
set oscarMovies to {}
set progress completed steps to moviesFoundCount
set progress description to ("Processing " & moviesFoundCount as text) & " Oscars"
set progress additional description to "Oscars found: 0"
set progress total steps to recordCount

repeat
  set moviesFoundCount to moviesFoundCount + 1
  set moviesRemaning to recordCount - moviesFoundCount
  if moviesRemaning = 0 then exit repeat
  
  tell application "Database Events"
     tell its database "Oscar Database-1"
        tell record moviesFoundCount
           set recordName to name
           set fieldNames to the name of every field
           set fieldValues to the value of every field
           my AddRecordToSQL(recordName, fieldNames, fieldValues)
        end tell
     end tell
  end tell
  
  if moviesFoundCount mod 10 = 0 then
     
     set timeNow to current date
     set elapsedTime to timeNow - startTime
     
     set {averageExecutions, secondsRemaining, remainingExecutions} to CalculateTimeRemaining(recordCount, moviesFoundCount, elapsedTime)
     
     set elapsedTimeString to SecondsToDaysHoursMinsSeconds(elapsedTime)
     set remainingTimeString to SecondsToDaysHoursMinsSeconds(secondsRemaining)
     set averageTimeString to SecondsToDaysHoursMinsSeconds(averageExecutions)
     {elapsedTimeString, remainingTimeString, averageTimeString}
     set AppleScript's text item delimiters to {" "}
     set addDescString to {moviesFoundCount as text, "done of", recordCount as text, "so far:", elapsedTimeString} as text
     set descString to {remainingExecutions as text, "remaining (est.)", remainingTimeString, "(" & averageExecutions & ")"} as text
     
     set progress total steps to recordCount
     set progress completed steps to moviesFoundCount
     set progress description to descString
     set progress additional description to addDescString
     
  end if
  
end repeat

on AddRecordToSQL(recordName, fieldNames, fieldValues)
  set the beginning of fieldValues to recordName
  
  set sqlDb to open db in file sqlDbFile with can create
  try
     
     begin transaction db sqlDb
     update db sqlDb sql string "insert into standings values (?, ?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?)" with arguments fieldValues
     commit db sqlDb
     close db sqlDb
  on error errMsg number errNum
     close db sqlDb
     display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
     
  end try
  
end AddRecordToSQL

on EstablishSQLDatabase(databaseName, fileLocation, fieldNames)
  set saveTID to AppleScript's text item delimiters
  set AppleScript's text item delimiters to {", "}
  set fieldNames to fieldNames as text
  set updateString to "create table standings (" & fieldNames & ")"
  
  set sqlDbFilePath to POSIX path of fileLocation
  set sqlDbFile to sqlDbFilePath & "/" & databaseName & ".db"
  try
     set sqlDb to open db in file sqlDbFile with can create
     
     update db sqlDb sql string "drop table if exists standings"
     update db sqlDb sql string updateString
     close db sqlDb
  on error errMsg number errNum
     close db sqlDb
     display dialog ("Error Number: " & errNum as text) & return & return & "Error Message: " & return & return & "\"" & errMsg & "\""
     
  end try
  set AppleScript's text item delimiters to saveTID
  
end EstablishSQLDatabase


on CalculateTimeRemaining(totalCount, completedCount, elapsedTime)
  local totalCount, completedCount, elapsedTime
  set averageExecution to elapsedTime / completedCount
  set remainingExecutions to totalCount - completedCount
  set secondsRemaining to round (remainingExecutions * averageExecution)
  --if secondsRemaining = 0 then
  --   remainingExecutions * averageExecution
  --end if
  return {averageExecution, secondsRemaining, remainingExecutions}
end CalculateTimeRemaining

on SecondsToDaysHoursMinsSeconds(secondsRemaining)
  set saveTID to AppleScript's text item delimiters
  set AppleScript's text item delimiters to {""}
  set mins to secondsRemaining div 60
  set secondsRemaining to secondsRemaining - mins * 60
  set hrs to mins div 60
  set minsRemaining to mins - hrs * 60
  set dys to hrs div 24
  set hrsRemaining to hrs - dys * 60
  set dhms to {}
  
  if dys > 0 then
     if dys > 1 then
        set the end of dhms to (dys as text) & " days"
     else
        set the end of dhms to "1 day"
     end if
  end if
  if hrsRemaining > 0 then
     if hrsRemaining > 1 then
        set the end of dhms to (hrsRemaining as text) & " hours"
     else
        set the end of dhms to "1 hour"
     end if
  end if
  if minsRemaining > 0 then
     if minsRemaining > 0 then
        set the end of dhms to (minsRemaining as text) & " minutes"
     else
        set the end of dhms to "1 minute"
     end if
  end if
  if secondsRemaining > 0 then
     if secondsRemaining > 1 then
        set the end of dhms to (secondsRemaining as text) & " seconds"
     else
        set the end of dhms to "1 second"
     end if
  end if
  if the (count of dhms) > 1 then
     set AppleScript's text item delimiters to {", "}
     set the item -1 of dhms to "and " & item -1 of dhms
     set dhms to dhms as text
     set AppleScript's text item delimiters to {", and "}
     set dhms to text items of dhms
     set AppleScript's text item delimiters to {" and "}
     
     
     set dhms to dhms as text
     
  end if
  return dhms as text
  set AppleScript's text item delimiters to saveTID
  
  
end SecondsToDaysHoursMinsSeconds

on PickDBEVFile()
  set saveTID to AppleScript's text item delimiters
  local fileInfo
  try
     set userPrompt to "Select a DataBase Events database file (.dbev)"
     
     repeat
        set databaseFile to choose file with prompt userPrompt ¬
           without multiple selections allowed
        set fileInfo to parse object databaseFile with HFS results
        set fileType to name_extension of fileInfo
        set fileNameRoot to name_stub of fileInfo
        set fileLocation to parent_folder_path of fileInfo
        set databaseFile to POSIX path of databaseFile
        if fileType is "dbev" then exit repeat
        set saveTID to AppleScript's text item delimiters
        set AppleScript's text item delimiters to {return & return}
        set wrongFile to full_name of fileInfo
        
        set userPrompt to {wrongFile & " is not a DataBase Events database file. ", ¬
           "It doesn't end in \".dbev\"", ¬
           "Select a DataBase Events database file (.dbev)"}
        set userPrompt to userPrompt as text
        set AppleScript's text item delimiters to saveTID
     end repeat
     set AppleScript's text item delimiters to saveTID
     
     return {databaseFile, fileNameRoot, fileLocation}
  on error errMsg number errNum
     set AppleScript's text item delimiters to saveTID
     error errMsg number errNum
  end try
end PickDBEVFile

I’ve moved this to beta testing. Should have been there to start.

I’m convinced that his is a bug. Something to do with either DB Events or SQLite or both.

After just a little poking around in the above script, not doing any heavy lifting at all, the result pane stops working. Also, if I double click on a variable name, that variable window doesn’t show the value. But I can see the variable values in the that pane.

Something magical has happened.

After the problem with the result pane not updating persisted through restarts and reboots, and while I was in the middle of pounding away at it for some hours, I noticed the issue had disappeared. Result pane updates properly.

Not only that, but the other frustrating issue I was having with SQLIte Lib2, that it would leak and I’d have to quit SD to work with a DB that was left open, also went away.

The thing is, I had added so many try/on errors and hadn’t been checking the result window, that I didn’t notice what I did or possibly could have done that “fixed” either issue. And it may be the same thing, because when I noticed the result pane working came after I stopped a script, and thought I’d have to restart, so I noticed both issues had resolved at the same time.

Also, the good news is that I can report that the same operation that took 45 minutes using DB Events takes under 10 seconds using SQLite2 lib.

Shane suggested that it may be an order of magnitude faster, and that would be fine, but it’s actually 2 orders of magnitude faster.

And it’s back. Seems that maybe if an error occurs in the wrong part of an SQLite script that trips it.

I’ll try to narrow it down.