Include Prebuilt SQLITE 3 DB

[Nakia]

Hi,

I have been using a tool I found online to build up a SQLITE DB but I have ran into an issue when I try and use it with LC.
First oddity:
The Tools name is "SQL Lite Databrowser 2.0" and it doesn't save the file with a file extension? Through finder in MAC my system just sees it as a "Document" and through terminal is also shows no file extension.

So,
Noting this I have used the Copy files pane in the Stand alone settings to add the file and LC shows it as a SQL Database as it should.
I am using the following to make sure the file is there before connecting but it always returns that there is no file...

Code: Select all

   -- First make sure the DB is there
   put specialFolderPath("Engine")&"/SCOREPADDB" into tDatabasePath
   if there is not a file tDatabasePath then
      answer "Can't located the Database. Please download again from Appstore!" titled "Error"
      quit
   end if

When this didn't work I used terminal to place an extension onto the file (.sqlite) then subsequently reloaded the file through the copy files pane
and modified the code and it still cant find it.
Note: after I did this the tool I used to build the DB states 'this is not a valid SQL Lite 3 File' when I try to open it...

Code: Select all

   -- First make sure the DB is there
   put specialFolderPath("Engine")&"/SCOREPADDB.sqlite" into tDatabasePath
   if there is not a file tDatabasePath then
      answer "Can't located the Database. Please download again from Appstore!" titled "Error"
      quit
   end if

Can anyone see what sort of stupid mistake I have made here?

[endernafi]

There are times that I've seen that there is a file statement doesn't get along with the engine folder.
I would try the below options to pinpoint what's causing the problem,
preferably all possible combinations of them until you nail it:

• Copy your database file into the cache folder manually, check its existence via there is a file.
• Bypass the file check and directly interact with the database, since there is a file can result a false - positive.
  Try this instead:

  Code: Select all

  put revOpenDatabase("sqlite",tFilepath, , , ) into tResult
  if tResult is a number then
        put tResult into tDatabaseID
  else 
        answer tFilepath & cr & "Connection Error:" & cr & tResult
  end if
  

  Beware that revOpenDatabase creates an empty database if it couldn't find a present file.
• Try using SQLite Manager for Firefox, it's battlefield tested

Best,

~ Ender

[Nakia]

Thanks for the tips!

I tried as you suggested, moving the DB to the cache directory on openStack and its appears to have solved the issue of the flakey 'if there is a file' statement as it now recognises
some file there.

Code: Select all

on openStack
   wait 5 milliseconds with messages -- Just so the engine isn't over loaded
   put URL ("binfile:"& specialFolderPath("engine")&"/mydatabase.sql") into URL ("binfile:"& specialFolderPath("cache")&"/mydatabase.sql")
end openStack

However, I think that LC is still creating a new DB when I connect as there are no tables in the DB when I query it (and I know there are as I can see them using the Fire Fox add-on)..
Below is the DB Transaction.

This is killing me, never had so much trouble with a simple task before!!!!!!!

Code: Select all

local sDatabaseID
function execDatabaseTransaction
   ## This handler is reponsible for executing all the database transactions
   -- First make sure the DB is there
   put specialFolderPath("cache")&"/mydatabase.sql" into tDatabasePath
   if there is not a file tDatabasePath then
      answer "Can't located the Database. Please download again from Appstore!" titled "Error"
      quit
   end if
   -- Make connection to DB, place ID in script local var
   if revOpenDatabases() is empty or revOpenDatabases() = "revdberr" then
      put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
      -- Check connection to ensure it's an integer
      if tDatabaseID is not an integer then
         answer "Can't connect the Database. Please restart the Application" titled "Error"
         quit
      end if
      -- Store the connection ID
      put tDatabaseID into sDatabaseID
   end if
         -- QUERY/ACTION HANDLER
         return revDatabaseTableNames(sDatabaseID)
end execDatabaseTransaction

[Nakia]

For some reason LC just doesn't like the DB I am building with either the Tool I mentioned or the Mozilla Add-on.

I gave up and done it by script and it works as expected..

Code: Select all

on buildDB
   ## This handler is reponsible for building the SQL Lite DB and creating all the Tables
   -- Make connection to DB, place ID is script local var
   put specialFolderPath("cache")&"/mydatabase.sqlite" into tDatabasePath
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   -- Check connection to ensure it's an integer
   if tDatabaseID is not an integer then
      answer "Can't connect the Database. Please restart the Application" titled "Error"
      quit
   end if
   -- Store the connection ID
   put tDatabaseID into sDatabaseID
   -- Build all the tables, first the Flights Table
   put "CREATE TABLE Flights (Contest_ID NUMERIC, Id INTEGER PRIMARY KEY, Edited_Timestamp NUMERIC, Created_Timestamp NUMERIC, Pilot_Secondary_Org_Value TEXT, Pilot_Primary_Org_Value TEXT, Sequence_Type TEXT, Round_Value TEXT, Sequence_Value NUMERIC, Judge_Value NUMERIC, Missing_Pilot_Panel_Penalty BOOL, Figure_Index NUMERIC, Figure_Raw_Score NUMERIC, Figure_Break_Penalty BOOL, Figure_Comment TEXT, Figure_Box_Penalty BOOL)" into tSQL
   revExecuteSQL sDatabaseID, tSQL
   -- Next the Pilots Table
   put "CREATE TABLE Pilots (Contest_ID NUMERIC, Id INTEGER PRIMARY KEY, Frequency TEXT, Spread_Spectrum BOOL, Active BOOL, Comments TEXT, Missing_Pilot_Panel BOOL, Airplane TEXT, Address_2 TEXT, Address_1 TEXT, Name TEXT, Class TEXT, Pilot_Primary_Org_Value TEXT, Pilot_Secondary_Org_Value NUMERIC)" into tSQL
   revExecuteSQL sDatabaseID, tSQL
   -- Next the Sequences Table
   put "CREATE TABLE Sequences (Contest_ID NUMERIC, Id INTEGER PRIMARY KEY, Sequence_Type TEXT, Sequence_Class TEXT, Sequence_Min_Round NUMERIC, Sequence_Max_Round NUMERIC, Figure_Index NUMERIC, Figure_K_Factor NUMERIC, Figure_Description TEXT, Figure_Scoring_Incrament TEXT)" into tSQL
   revExecuteSQL sDatabaseID, tSQL
   -- Lastly the Preferences Table
   put "CREATE TABLE Preferences (Contest_ID INTEGER PRIMARY KEY, Freestyle_Required BOOL, Secondary_Org_Name TEXT, Primary_Org_Name TEXT, Contest_Region TEXT, Include_Box_Penalties BOOL, Include_MPP_Penalties BOOL, Include_Break_Penalties BOOL, Custom_Class_Name_0 TEXT, Custom_Class_Name_1 TEXT, Include_Freestyle BOOL, Include_Unknown BOOL, Include_Known BOOL, Contest_Name TEXT)" into tSQL
   revExecuteSQL sDatabaseID, tSQL
   -- Close the DB
   revCloseDatabase sDatabaseID
end buildDB

[LCNeil]

Hi Nakia,

I use the following SQL utility to create/edit SQLite databases for use in any LiveCode based apps (ios/Android etc)

http://sourceforge.net/projects/sqlitebrowser/

I hope this helps

Kind Regards,

Neil Roger
--
RunRev Support Team ~ http://www.runrev.com
—

[Nakia]

Hi Neil,

This is the exact tool I was referring to that I was using (before using the Firefox Add-on)..
No matter what I do LC just wont see any DB I build with this tool as valid!

As above,
I have resorted to building the tables in LC for which I am using exactly the SQL Statements taken from the tool you mention Neil.

I'm simply confused as to why it won't work...

[endernafi]

I'm simply confused as to why it won't work...

Nakia,

Probably you tried with SQLite Manager to update or fill the records of the existed database which you've created with the SqliteBrowser.
Clearly SqliteBrowser produced a faulty file, SQLite Manager may not fix it.
You should create an new database with SQLite Manager {firefox add-on} from scratch.
But nevermind, I did it for you

nakiaTestDatabase.zip

(1.02 KiB) Downloaded 240 times

I tested it in the simulator with this script:

Code: Select all

ask "Enter table name to investigate:"
put it into tTable

put revOpenDatabase("sqlite",tFilename, , , ) into tDatabaseID
put "pragma table_info(" & tTable & ")" into tQuery
put revDataFromQuery(comma, cr, tDatabaseID, tQuery) into tData
revCloseDatabase tDatabaseID

answer tDatabaseID & cr & tQuery & cr & tData

It worked fine here; like I said, SQLite Manager is battlefield-tested
Please try and let me know if it works for you.


Best,

~ Ender

[Nakia]

I will give this DB a shot in my program tonight and let you know the result...

[Nakia]

Hmmm, well I get the same error using the DB you have created so it's something I am doing.
Any chance I can email you the stack to take a peek at ?

[Nakia]

For what its worth all I am doing is below;
(Which I am calling from a button mouseUp)

the DB you loaded in copied through the copy files pane in stand alone settings.

.....

Still getting the same error that LC cant find the file.
(Even if I skip the file check its creating a new DB as the SQL returns no tables)

Code: Select all

on execDatabaseTransaction 
   ## This handler is responsible for executing all the database transactions
   -- First make sure the DB is there
   put specialFolderPath("engine")&"/nakiaTest.sqlite" into tDatabasePath
   if there is not a file tDatabasePath then
      answer "Can't located the Database. Please download again from Appstore!" titled "Error"
      quit
   end if
   -- Make connection to DB, place ID is script local var
   if revOpenDatabases() is empty or revOpenDatabases() = "revdberr" then
      put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
      -- Check connection to ensure it's an integer
      if tDatabaseID is not an integer then
         answer "Can't connect the Database. Please restart the Application" titled "Error"
         quit
      end if
      -- Store the connection ID
      put tDatabaseID into sDatabaseID
   end if
   put revDatabaseTableNames(sDatabaseID) into tData
   answer tData
end execDatabaseTransaction

[endernafi]

Any chance I can email you the stack to take a peek at ?

Sure, of course.
I sent an e-mail to your user list address.
You can reach me from that one.

Best,

~ Ender

[Jellicle]

I use a little app I bought on the Mac app store to make and edit sqlite files. It's called Base. Works well!

Gerry

[Klaus]

Hi all,

there is a free "Valentina Studio" in the (Mac) Appstore, which produces LC friendly SQLite database files


Best

Klaus

[Nakia]

Turns out all the Tools I were using to build the DB were fine!

It was simply a silly mistake on my behalf regarding a combination of having folders on my system with 'White space' characters in the name and me not knowing LC creates the folder your copied file exists in as well.
Little embarrassing but you learn from your mistakes!