Cant Create Database Anymore

[Nii Darko Darkoh]

Hi DS,

How do I solve this database creation problem ?

I presently have the following commands which doesnt work.. 

var privatePath = app.GetPrivateFolder("cashin");

var db=app.OpenDatabase(privatePath+"/ntbcsh/ntbcsh.db");

I used to create databse with the following commands

var db=app.OpenDatabase("/sdcard/ntbcsh/ntbcsh.db");

But since the Android No-Direct-SDCard-Access policy, i switched   

commands to the following 

var privatePath = app.GetPrivateFolder("cashin");

var db=app.OpenDatabase(privatePath+"/ntbcsh/ntbcsh.db");

which cant create a database anymore.

Any help is much appreciated please? Thanks.

[Alan Hendry]

HI,

In Samples (touch rocket in top right) there's Database (Using local SQLite databases).

If you run it, do some adds, then close it and run again, the added rows should still exist.

It has db = app.OpenDatabase("Mydata"), seems to work for me (Android 11, DS 2.09)

I have a prototype that used "/sdcard/db/Multi" that worked in Oreo, doesn't seem to work in A11.

Changed to "Multi" and it seems OK (SPK). (Not sure where the db is stored).

Regards, ah

[Dave]

You can't easily create files on the root of the internal sdcard anymore in Android 10/11 because of scoped storage. You should however be able to do what you were trying to do with private folders instead.  Your problem is probably because you specified a sub-folder and did not create it first.

Try this - 

var privatePath = app.GetPrivateFolder("cashin");

app.CreateFolder( privatePath+"/ntbcsh" )

var db=app.OpenDatabase(privatePath+"/ntbcsh/ntbcsh.db");

That should hopefully work.  It's also worth noting that you can convert paths to useable paths on scoped storage devices by using the app.RealPath() method like this - 

app.CreateFolder( app.RealPath( "/sdcard/ntbcsh" ) )

var db=app.OpenDatabase( app.RealPath("/sdcard/ntbcsh/ntbcsh.db") )

This should work on older versions of Android as well as newer ones with scoped storage, it just means that on scoped storage devices your files will go into the /Android/data/com.myname.myapp/files/ntbcsh folder

Most of the DS methods already use app.RealPath() internally (such as app.ReadFile/WriteFile etc) however the app.OpenDatabase() method was probably forgotten because it is actually a wrapped Cordova plugin and lives in a different part of the codebase.

[Alan Hendry]

Hmm,

I had make folder, but it didn't didn't work under Android 11

app.MakeFolder("/sdcard/db") ; db =app.OpenDataBase("/sdcard/db/Multi")

But removing the path (and the makefolder) did work

 db =app.OpenDataBase("Multi" )

I think app.RealPath(...) is 2.11 (still in Beta) 

https://groups.google.com/g/droidscriptbeta/c/uwxXQdor9eg/m/4E5LLzrjCAAJ 

Might this apply to other objects/methods? 

(ZipUtil, Zip/Unzip, SendFile, ScanFile, Save Image, AudioRecorder SetFile).

Regards, ah

[Steve Garman]

// if only using database in your app

// use just a filename as path

// mydb.db

// if you want a file you can copy out of your

// app, use app.GetPrivateFolder(  )

// as below

// to see changes from sample

//  search for dbPath

function OnStart()

{

   dbPath = app.GetPrivateFolder("databases") + "/mydb.db"

   alert(dbPath)

      //Create a layout with objects vertically centered.  

   lay = app.CreateLayout("linear", "VCenter,FillXY")

   //Create an 'Add' button.  

   btnAdd = app.CreateButton("Add to Database", 0.6, 0.1)

   btnAdd.SetOnTouch(btnAdd_OnTouch)

   lay.AddChild(btnAdd)

   //Create a 'Remove' button.  

   btnRemove = app.CreateButton("Remove from Database", 0.6, 0.1)

   btnRemove.SetOnTouch(btnRemove_OnTouch)

   lay.AddChild(btnRemove)

   //Create a 'Delete' button.  

   btnDelete = app.CreateButton("Delete Database", 0.6, 0.1)

   btnDelete.SetOnTouch(btnDelete_OnTouch)

   lay.AddChild(btnDelete)

   //Create text box to show results.  

   txt = app.CreateText("", 0.9, 0.4, "multiline")

   txt.SetMargins(0, 0.1, 0, 0)

   txt.SetBackColor("#ff222222")

   txt.SetTextSize(18)

   lay.AddChild(txt)

   //Add layout to app.      

   app.AddLayout(lay)

   //Create or open a database called "MyData".  

   db = app.OpenDatabase(dbPath)

   //Create a table (if it does not exist already).  

   db.ExecuteSql("CREATE TABLE IF NOT EXISTS test_table " +

      "(id integer primary key, data text, data_num integer)")

   //Get all the table rows.      

   DisplayAllRows()

}

//Called when user touches our 'Add' button.  

function btnAdd_OnTouch()

{

   //Add some data (with error handler).  

   db.ExecuteSql("INSERT INTO test_table (data, data_num)" +

      " VALUES (?,?)", ["test", 100], null, OnError)

   //Get all the table rows.      

   DisplayAllRows()

}

//Called when user touches our 'Remove' button.  

function btnRemove_OnTouch()

{

   //Remove data.  

   db.ExecuteSql("DELETE FROM test_table WHERE id > 3")

   //Get all the table rows.      

   DisplayAllRows()

}

//Called when user touches our 'Delete' button.  

function btnDelete_OnTouch()

{

   //Delete this database.  

   db.Delete()

   //Get all the table rows.  

   DisplayAllRows()

}

//function to display all records 

function DisplayAllRows()

{

   txt.SetText("")

   //Get all the table rows.  

   db.ExecuteSql("select * from test_table;", [], OnResult)

}

//Callback to show query results in debug.  

function OnResult(results)

{

   var s = "";

   var len = results.rows.length;

   for(var i = 0; i < len; i++)

   {

      var item = results.rows.item(i)

      s += item.id + ", " + item.data + ", " + item.data_num +

         "\n";

   }

   txt.SetText(s)

}

//Callback to show errors.  

function OnError(msg)

{

   app.Alert("Error: " + msg)

   console.log("Error: " + msg)

}

[Nii Darko Darkoh]

Thanks guys.