Problems with sqlite after build

[justin24]

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.

[lua]local sqlite3 = require( “sqlite3” )
local path = system.pathForFile( “duniversity.db”, system.DocumentsDirectory )
local db = sqlite3.open( path )[/lua]

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

[nick_sherman]

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.

[nick_sherman]

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

[lua]

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”)

[/lua]

[justin24]

@nick\_sherman

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.

[XeduR]

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

@nick\_sherman

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.

[nick_sherman]

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

[rob]

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

[nick_sherman]

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.

[nick_sherman]

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

[lua]

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”)

[/lua]

[justin24]

@nick\_sherman

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.

[XeduR]

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

@nick\_sherman

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.

[nick_sherman]

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

[rob]

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