I cannot connect my database - sqlite to my andriod phone

[lemodizon]

Hello Everyone,

I already created an SQLite database unfortunately I can't access/connect my database from my android device. I already checked the SQLite database from the inclusion under the standalone application settings and add the file of my database still I can't connect to my database. I tried these codes below it works on my pc but on android it doesn't work. My old android phone doesn't have an external sd card. and I want to store my database in the internal storage of the is this possible? I was trying to create an app on my old android phone where I can add, edit, and delete. can you teach me how can I connect my database to my android phone? Thanks

Code: Select all

put specialfolderpath("documents") & "/DB_ePMMA.sqilte" into lDatabaseFile
    
  if there is no file lDatabaseFile then
    
    Answer Error "No Database Found!" 
   
  else
  
   put revOpenDatabase("Sqlite", lDatabaseFile) into gDatabaseID
   
  end if

[SparkOut]

Firstly you are not using the same filename (extension) in the code as the file copied in the standalone builder.

Secondly, adding the files in the Copy Files will place them in the specialFolderPath("resources") location. This is non-writable on Android. Even opening the sqlite database will require writable privileges whether you want to update and save changes or not. So on opening the app you will need to check for existence of the file in the specialFolderPath("documents") location, and if not already there, then copy it from "resources" to "documents". (Hint: use the "binfile" format to copy the binary sqlite database file, "file" will corrupt the database with munged up line ending conversions.)

Thirdly what errors are you getting? Saying it "doesn't work" does not make it very easy to help. Was the error to do with the file not being found, or what errors were reported if it can't be opened?

[lemodizon]

Firstly you are not using the same filename (extension) in the code as the file copied in the standalone builder.

Secondly, adding the files in the Copy Files will place them in the specialFolderPath("resources") location. This is non-writable on Android. Even opening the sqlite database will require writable privileges whether you want to update and save changes or not. So on opening the app you will need to check for existence of the file in the specialFolderPath("documents") location, and if not already there, then copy it from "resources" to "documents". (Hint: use the "binfile" format to copy the binary sqlite database file, "file" will corrupt the database with munged up line ending conversions.)

Thirdly what errors are you getting? Saying it "doesn't work" does not make it very easy to help. Was the error to do with the file not being found, or what errors were reported if it can't be opened?

Hi Sparkout,

Can you teach me how to do the copy process from "resources" to "documents". the error I got from the android app No database found since I'm not using the right filename (extension) in my code.

I will not add my database under the copy files since this is not writable on Andriod right?

how about in the inclusion part do I have to check the SQLite database?

Thanks for the response to my post.

[Klaus]

Hi lemodizon,

I will not add my database under the copy files since this is not writable on Andriod right?

WRONG!
You have to add your db file there, or it will not be put into the final app package!

how about in the inclusion part do I have to check the SQLite database?

Sure, if you want to connect and use your db!

Here is what you have to do:
Check on openstack if you have already copied the db file form specialfolderpath("resources") to specialfolderpath("documents") before:

Code: Select all

...
## Here you will find all files and folders that you added to your app via "Copy files"
put specialfolderpath("resources") & "/yourdb.db" into tSourceDB

## Only here we have write permission!
## And even opening a database is considered "writing"
put specialfolderpath("documents") & "/yourdb.db" into tTargetDB

## APP opened for the very first time?
if there is not a file tTargetDB then
   put url("binfile:" & tSourceDB) into url("binfile:" & tTargetDB)
end if
## NOW you can open and access your db from here: 
## specialfolderpath("documents") & "/yourdb.db"
...

Best

Klaus

[lemodizon]

Hi Klaus,

I don't know if I got your code correctly. I just want to have full access to my database where I can update and delete the data inside of it. Thanks, Klaus

Code: Select all

 local lDatabaseFile
  global gDatabaseID
  
  put specialfolderpath("resources") & "/DB_ePMMA.db" into tSourceDB
  put specialfolderpath("documents") & "/DB_ePMMA.db" into tTargetDB
  
  if there is not a  file tTargetDB then
    put url("binfile:" & lDatabaseFile) into url("binfile:" & tTargetDB)
  end if
  
  put specialFolderPath("Documents") & "/DB_ePMMA.db" into lDatabaseFile
  
  if there is no file lDatabaseFile then
    Beep
    Answer Error "No Database Found!" 
  else
    put revOpenDatabase("Sqlite", lDatabaseFile) into gDatabaseID
    answer specialfolderpath("documents")
  end if
  

I don't know if the picture below if I got it or not. coz I tried to look for the folder can't find it.

[Klaus]

I don't know if I got your code correctly.

No you didn't!
Why not copy/paste my ready-to-use code and only replace the names with the names of your database?

Code: Select all

...
  ## local lDatabaseFile
  ## Not neccesary, see below...
  global gDatabaseID
  
  put specialfolderpath("resources") & "/DB_ePMMA.db" into tSourceDB
  put specialfolderpath("documents") & "/DB_ePMMA.db" into tTargetDB
  
  ## if there is not a  file tTargetDB then
  ##   put url("binfile:" & lDatabaseFile) into url("binfile:" & tTargetDB)
  ##end if
  ## ???
  ## Of course you need to copy hte SOURCEDB file!
  ## lDatabaseFile is EMPTY at this point!
 
 ## This does the trick:
 if there is not a  file tTargetDB then
    put url("binfile:" & tSourceDB) into url("binfile:" & tTargetDB)
 end if
  
  ## put specialFolderPath("Documents") & "/DB_ePMMA.db" into lDatabaseFile
  ## Not neccessary, this is already in tTargetDB
  
  ## NOW this file is present, so no need to check, just open it:
  ## if there is no file lDatabaseFile then
  ##  Beep
  #  Answer Error "No Database Found!" 
  ## else
    put revOpenDatabase("Sqlite", tTargetDB) into gDatabaseID
    answer specialfolderpath("documents")
  ## end if
  ...

[lemodizon]

Hi Klaus,

I tried your code still this is the output. Where I can find the folder that stored my database in my android phone via internal storage?

[SparkOut]

You can't see it using a 3rd party file explorer, as it is.
The specialFolderPath ("documents") folder is sandboxed on mobile device o/s and you would need root access to be able to read using any other tool but your own app.
The "answer" output in the script is just there to show you the database location was used successfully rather than an error from not being found.

Also note that on mobile, specialFolderPath takes case-sensitive arguments, so get used to specialFolderPath("documents") with no upper-case D. (The line that was commented out would not have worked.)

[AxWald]

Hi,

an easy way to see what's actually in your documents folder on mobile:

Code: Select all

on mouseUp
   put the defaultFolder into mySavedPath
   set the defaultFolder to specialFolderPath("documents")
   answer "Files in documents:" & CR & the files
   set the defaultFolder to mySavedPath
end mouseUp

Put this into a button.

Have fun!

[Klaus]

Since a couple of version we can have this even shorter:

Code: Select all

on mouseUp
   answer "Files in documents:" & CR & files(specialFolderPath("documents"))
end mouseUp

No more messing with the defaultfolder.

[lemodizon]

You can't see it using a 3rd party file explorer, as it is.
The specialFolderPath ("documents") folder is sandboxed on mobile device o/s and you would need root access to be able to read using any other tool but your own app.
The "answer" output in the script is just there to show you the database location was used successfully rather than an error from not being found.

Also note that on mobile, specialFolderPath takes case-sensitive arguments, so get used to specialFolderPath("documents") with no upper-case D. (The line that was commented out would not have worked.)

This is noted. Thank you

[lemodizon]

Since a couple of version we can have this even shorter:

Code: Select all

on mouseUp
   answer "Files in documents:" & CR & files(specialFolderPath("documents"))
end mouseUp

No more messing with the defaultfolder.

Hi Klaus and AxWald,

This is now working i was able to connect to my database. thank you very much . My question is in the part of SQL "INSERT" are they the same code in desktop and android coz my code below works in desktop app but when i tried in android it is not working.

Code: Select all

command ProductSaveData
  local tProdName, tProdDesc, tPrice
  local tProdID
  
  put fld "ProdNameFld" into tProdName
  put fld "ProdDescFld" into tProdDesc
  put fld "ProdPriceFld" into tPrice
  
  
  put fld "IDFld" into tProdID
  
  if tProdID is empty then
    AddProduct  tProdName, tProdDesc, tPrice
      end if
end ProductSaveData




///Add product info to the Database
command AddProduct pProdName, pProdDesc, pPrice, 
   
   local lSQLStatement
   global gDatabaseID
   
   put "INSERT into TBLPROD(ProdName,ProdDesc,ProdPrice)VALUES(" & quote & pProdName & quote & comma & quote & pProdDesc & quote & comma & quote & pPrice & quote &")" 
   into lSQLStatement
   revExecuteSQL gDatabaseID, lSQLStatement
   
   if the result is an integer then
      
      Answer Info "Product information successfully added!" 
      
   else
     
      Answer Error "Sorry, there was a problem adding Product information" 
  
   end if
end AddProduct

[lemodizon]

Hi everyone,

I already found my mistake in my code this now working. Thanks everyone Stay safe.