Using Sqlite - Revdberr, invalid database type

[Fanfanix]

All,

I am having problem connecting to a sqlite database. I can connect to it in standalone environment but I got the error "'Revdberr, Invalid database type" when I tested the app on the simulator. I am using LC 7.1, Commercial version, xCode 6.4. Here is a snippet of the code:

Code: Select all

   
   if the environment is "mobile" then
      answer " Mobile Environment is -->  " & the environment
      put specialFolderPath("engine") & "/MyAppDB.sqlite"        into engineFilePath
      put specialFolderPath("documents") & "/MyAppDB.sqlite"  into tDatabasePath
      if there is a file engineFilePath then
         if there is a file tDatabasePath then
            // do nothing, db already exists in documents folder
            answer information " Nothing to do. All is setup ... "
         else
            put URL ("binfile:" & engineFilePath)  into URL("binfile:" & tDatabasePath )
         end if
      end if
      put specialFolderPath("documents") & "/MyAppDB.sqlite"  into gMyAppDBPath
   else --- not mobile
      put   "/users/frantzcharles/documents/All About LiveCode/MyApp/MyAppDB.sqlite"  \ 
            into gMyAppDBPath
   end if
   
   answer " I am here ..."
   answer "  Path is ...  " & gMyAppDBPath
   
   try
      put revOpenDatabase("sqlite", gMyAppDBPath ) into g_ConID
      put result() into tResult
      answer "Open db result is ..." & tResult
   catch theError
      answer info "Problem Opening  database:  " & gMyAppDBPath & " - " & theError
      put 0 into g_ConID
   end try
   
   if g_ConID is not an integer then
      answer "DB Connection failure  " & g_ConID
   else
      answer "Connection details are ..." & result() & " - " & gMyAppDBPath & " - " & g_ConID
   end if

Please take a look and tell me where the error comes from. Thanks for your help.

Fanfan

[SChamblee]

On android the database needs to be in the documents folder, I'm not 100% sure if it has to be in the document folder on ios devices, but it does work in the documents folder.

I used the following which works on PC, Macs, android and ios. It will be the database is the same folder as the livecode program on desktop machine and in the document folder on mobile devices
command databaseConnect
local tDatabasePath

## The database must be in a writeable location
if the environment is "mobile" then
## mobile path
put specialFolderPath("documents") & "/mydatabase.sqlite" into tDatabasePath
else
##desktop and development path
put specialFolderPath("resources") & "/mydatabase.sqlite" into tDatabasePath
end if

if there is not a file tDatabaseFile then

put revOpenDatabase("sqlite", tDatabasePath, "binary" ) into tDatabaseID
databaseCreateTables
else
put revOpenDatabase("sqlite", tDatabasePath, "binary" ) into tDatabaseID

end if


end databaseConnect

[Fanfanix]

All,

I think that my problem comes from my lack of understanding in how the database file is included in the app bundle. For my case, the database file is here, on my hard drive: /users/myname/documents/All About Livecode/MyApp/MyAppDB.sqlite. I understand that I need to include the database file in the Standalone Setting under the tab "Copy Files". In that tab, if I select the "Add File.." button, the entry is like this: MyAppDB.sqlite. And if I select the "Add folder ...", the entry is like this: /Users/myname/documents/All About Livecode/MyApp/*. My first question is: Do I need both or which one is the correct entry.

Now here is my script

Code: Select all

    if the environment is "mobile" then
      answer " Mobile Environment is -->  " & the environment
      put specialFolderPath("engine") & "/MyAppDB.sqlite"        into engineFilePath
      put specialFolderPath("documents") & "/MyAppDB.sqlite"  into tDatabasePath
      --- See if database is already in the documents folder. If not, copy it from
      --- the engine folder
      if there is not a file tDatabasePath then
         put URL ("binfile:" & engineFilePath)  into URL("binfile:" & tDatabasePath )
         answer " File is copied from Engine into the Documents folder ..."
      else
         answer "File is already in the documents folder ... "
      end if
   end if
   
   if the environment is "mobile" then
      put specialFolderPath("documents") & "/MyAppDB.sqlite"  into gMyAppDBPath
   else
      put   "/users/myname/documents/All About Livecode/MyApp/MyAppDB.sqlite"  \ 
            into gMyAppDBPath
   end if
 
   answer "  Final db path is ...  " & gMyAppDBPath
   
   try
      put revOpenDatabase("sqlite", gMyAppDBPath, , , , ) into g_ConID
      put result() into tResult
      answer "Open db result is ..." & tResult
   catch theError
      answer info "Problem Opening  database:  " & gMyAppDBPath & " - " & theError
      put 0 into g_ConID
   end try

When I run the app on my Mac, all is fine, the db is accessed, no issue at all. When I run it in the Simulator, the DB is not located, I get the error "Revdberr, invalid database type".

I am wondering if my db file is really included in the app bundle in the engine folder. How can I verify that? What should be the full path of the db file in the Engine folder? Why the script says there is a file in Documents while the real file is not there? Please help me solve this imbroglio. Thanks.

[SChamblee]

I use specialfolderpath("documents") on mobile devices which works on android and ios. and specialfolderpath("resources") on everything else.

If the database does not exist then your code should create the database in that location. The code I posted is exactly what I use on all my devices (PC,Mac,Android,ios) with no problems.

In the start center there is a sample book database stack that was very useful in getting me started.

[Simon]

Hi Fanfanix,
I tried this answer once before with someone else and the response wasn't great but...
in the Standalone Android do you have sqlite ticked?

Simon

[Fanfanix]

Hi Simon,

My app is an iOS app, it is not an Android. Still, in the standalone setting, Sqlite is ticked and in the Script Librairies list, I select Database and Table . Can you tell me where is the database file supposed to be? I just copied it in /Users/myname/Documents because I see that is also my default folder. And still, it fails showing up in the Simulator. Also, do you see anything wrong in my enclosed codes?

Thanks

Fanfan

[Simon]

Hi Fanfan,
Your code seems correct > copy the db from engine to documents > open db in documents.
Which is why I thought it may be the sqlite tick.
Have you tried just revOpenDatabase with out copying the file? It should just create the database if it's not there. See if that's working. (do a create table n' stuff)
As far as Copy File in the standalone settings you just need MyAppDB.sqlite not the whole folder unless you have other stuff to copy.
Oh which version of liveCode are you using? You might try dropping to a lower version.

Simon

[Fanfanix]

Hi Simon,

My app is an iOS app. It is like the Jeopardy game. I created the game front-end and the database is created and populated with questions and answers by a different app. On the desktop, I can play the game, select the subject and answer the questions. On the mobile, I expect the already populated database to be copied and be accessible to retrieve the questions and verify the answers. The database needs not to be recreated on the mobile but to be moved on the mobile and to be accessible there.

I am doing it for fun, fun to learn Livecode. If you are available and want to help, I am open to pay you to take it over and help me finish it. Let me know and I will pass you my personal email.

Thanks a lot

Fanfan

[dave.kilroy]

Hi Fanfan - what version of 7.1 are you using? There was a bug in LC 7.1(dp1) that gave the same error you are reporting (http://quality.runrev.com/show_bug.cgi?id=15619)

I've just tried again with LC 7.1(rc1) and sqlite is being accessed fine

Good luck!

Dave

[Simon]

Hi FanFan,
Try this out;

FanFan.zip

liveCode 7.0.6

(2.09 KiB) Downloaded 519 times

Look at the card script, button script and standalone settings.
only tested in the iOS sim.

Simon

[Fanfanix]

Hi Dave,

By the time it is now in London, you are in deep sleep. I am here in New Jersey, celebrating because of you. My app is working perfectly well in the Simulator. Like you said, it was the LC version 7.1.0 (dp1), the culprit. I downloaded LC 7.1.0 (rc1) and no more "revdberr, invalid database type". Thanks very much for your contribution. Game on !

Thanks also to you, Simon. Your app example works well in LC 7.1.0 (rc1). Your free advice went very far. I did learn a lot about Livecode.

Like the French president, I give you both the Medal of Honor !!!! Merci beaucoup !!

Fanfan

[dave.kilroy]

Fanfan - glad it's working and thank you for my very first Medal of Honor (I bet Simon has cupboards full of them...)

[Quinton B.]

Got It to work Guys, Thanks for your help. Now how do we do an SQLite update?

Here's the code that works:

global gDatabaseID
on mouseUp
-- put the text of each field into a variable
put the text of field "field_Forgot_Password_UserName" into tForgotPasswordUserName
put the label of button "button_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestion
put the text of field "field_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestionAnswer
-- statement for searching a database
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion =" && "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tUserVar
-- executes the search command
put revDataFromQuery(tab,return,gDatabaseID,tUserVar) into tPasswordCheck //field "field_Forgot_Password_Password"
if tPasswordCheck is empty then
answer "Fields Are Not Correct. Please Make Sure They Are Correct, To Retrieve Your Password"
else
answer "Successfully Found Your Password"
put tPasswordCheck into field "field_Forgot_Password_Password"
put empty into field "field_Forgot_Password_UserName"
set the label of button "button_Forgot_Password_SecurityQuestion" to "Chose A Security Question"
put empty into field "field_Forgot_Password_SecurityQuestion"
end if
-- clears all fields
end mouseUp

[Klaus]

Hi Quinton,

maybe you wanted to answer in this thread?
http://forums.livecode.com/viewtopic.ph ... 65#p153865

Best

Klaus