Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

Problems with sqlite after build
Started by justin24 Jun 23 2019 10:00 PM

6 replies to this topic
sqlite
[TOPIC CONTROLS]
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

justin24

[GLOBAL: userInfoPane.html]
justin24
  • Observer

  • 2 posts
  • Corona SDK

Hello

 

This is probably me being stupid but.....

I have written a simple 1 page app that grabs data from a local sqlite DB. This runs correctly in the simulator and I have made sure I have an updated copy in both the file location and sandbox location but when I build it for Android (or HTML5 and even Windows) it comes up with a runtime error saying the table does not exist.

 

I have tried to put the DB in a folder and on the root folder but still get the same error.

 

local sqlite3 = require( "sqlite3" )
local path = system.pathForFile( "duniversity.db", system.DocumentsDirectory )
local db = sqlite3.open( path )

 

above is how I am calling my DB.

Is there a limitation to the free version or do I have to purchase a plugin/license to compile with sqlite DB?

 

(While posting this I noticed system.DocumentsDirectory in the path location, does this mean I need to create a new folder?

 

Thanks in advance



[TOPIC: post.html]
#2

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,867 posts
  • Corona SDK

There's no limitations to using SQLite, or any requirement to create a folder. system.DocumentsDirectory is a folder automatically created for you called 'Documents' in the app sandbox, along with the 'tmp' and 'Caches' folders.

 

When you build for Android (or any other OS), the copy that was in the sandbox location for the simulator is not going to be there on the device, unless you copy it there yourself in code. When you call sqlite.open it will see the database does not exist and create it, but of course it won't have your tables.

 

When my app runs, I check whether it is the first install or if the version number has changed, and if so copy all my databases across from ResourcesDirectory to the CachesDirectory. I used to use DocumentsDirectory but iOS can and will clear this folder out without warning if it needs the space.



[TOPIC: post.html]
#3

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,867 posts
  • Corona SDK

This is my file copy function, which works on all platforms - you can run into trouble on Windows if using other settings. 

 

 
local copyFile = function (fni, fno)
 
  local path = system.pathForFile(fni)
  local fileHandle = io.open( path, "rb" )
  local txt = fileHandle:read( "*a" )
 
  local path = system.pathForFile( fno, system.CachesDirectory )
 
  local file, errorString = io.open( path, "wb" )
 
  if not file then
    -- Error occurred; output the cause
    print( "File error: " .. errorString )
  else
    -- Write data to file
    file:write( txt )
    -- Close the file handle
    io.close( file )
  end
 
  file = nil
  txt = nil
  io.close(fileHandle)
  fileHandle = nil
  path = nil
 
end
 
copyFile("mySourceDatabase.db", "myCopiedDatabase.db")
 


[TOPIC: post.html]
#4

justin24

[GLOBAL: userInfoPane.html]
justin24
  • Observer

  • 2 posts
  • Corona SDK

There's no limitations to using SQLite, or any requirement to create a folder. system.DocumentsDirectory is a folder automatically created for you called 'Documents' in the app sandbox, along with the 'tmp' and 'Caches' folders.

 

When you build for Android (or any other OS), the copy that was in the sandbox location for the simulator is not going to be there on the device, unless you copy it there yourself in code. When you call sqlite.open it will see the database does not exist and create it, but of course it won't have your tables.

 

When my app runs, I check whether it is the first install or if the version number has changed, and if so copy all my databases across from ResourcesDirectory to the CachesDirectory. I used to use DocumentsDirectory but iOS can and will clear this folder out without warning if it needs the space.

 

Thanks for the reply. I have checked my database before build and it is located in the Documents directory in the sandbox and the app folder that I am compiling from. I did manually copy it to and from the sandbox documents directory too (as well as checking all versions with DB Browser). Still getting the error. I am sure it is something really simple that I am missing.



[TOPIC: post.html]
#5

XeduR @Spyric

[GLOBAL: userInfoPane.html]
XeduR @Spyric
  • Corona Geek

  • 1,021 posts
  • Corona SDK

@justin24, you seem to be misunderstanding what Nick is telling you.
 

When you build for Android (or any other OS), the copy that was in the sandbox location for the simulator is not going to be there on the device, unless you copy it there yourself in code. When you call sqlite.open it will see the database does not exist and create it, but of course it won't have your tables.

 

When you build an app, only the files inside of system.ResourceDirectory are added into the build. Whatever you may or may not have in your system.DocumentsDirectory will NOT be transferred over when you build the app.

 

In order to work with writable and readable storage, you must first create the file in (or copy an existing file into) the documents folder AFTER the game has been installed by the user. For how to copy a file, just look at Nick's code.



[TOPIC: post.html]
#6

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,867 posts
  • Corona SDK

It doesn't matter that it is in the Documents directory in your simulator sandbox. This is not copied across when you build for a device, or even if you run the program on a different machine in the Corona Simulator. You are not meant to manually put files there unless just testing a concept in the simulator. 

 

You will need to copy the file from the system.ResourcesDirectory to system.DocumentDirectory, in code, as I explained above. 

 

EDIT: XeduR quicker on the draw, but the point remains :)



[TOPIC: post.html]
#7

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 26,393 posts
  • Enterprise

This might be a useful read:

 

https://coronalabs.com/blog/2015/05/19/tutorial-initializing-a-writable-sqlite-database-from-a-read-only-database/

 

Rob




[topic_controls]
[/topic_controls]

Also tagged with one or more of these keywords: sqlite