SDcard SQL saving


#1

I just was wondering to store the sql database on the SD card.

DB = SqlOpenDatabase(“sdcard/test.db”,“1.0”,“My Customer Database”)
DB = SqlOpenDatabase(“storage/sdcard/test.db”,“1.0”,“My Customer Database”)

Do i need to add a string or command on the config.xml to give the program autorisation to write on the SD card.


#2

You’ll need to use a PhoneGap plugin for this, and run as a native app. Here’s one - I have not looked at whether it can write to a card:


#3

I tried, but the problem is the same.

the file must be located somewhere?

fileName = "test.db"
DB = SqlOpenDatabase(fileName,“1.0”,“My Customer Database”)

the sql which is integrated works fine, the only problem is the location find back of the file.


#4

I think with
DB = SqlOpenDatabase(fileName,“1.0”,“My Customer Database”)
you have to define fileName somwhere on your device using the PhoneGap FileSystem Plugin. You can’t just get an “outside” (of the webapp) file on your phone or SD card without that plugin.

In Project Properties, open PhoneGap configxml. Change the line for cordova-file-plugin so that it has the version number for the library:
<gap:plugin name="cordova-plugin-file" source="npm" version="3.0.0"/>

(See PhoneGapFileSystem example: Read/Write txt/mp3 to/from public folder)

And then you can define
fileName = cordova.file.externalRootDirectory & “mydatabase.db”

Therefore the db file must be in the main directory of your phone.

I didn’t try that with databases but this works fine with other files:
Audio1.src = cordova.file.externalRootDirectory & "door.mp3"
Audio1.play()

Please see Location of files listed in the manifest file - add ressource files at runtime for reference and let me know if that works.


#5

hmmm… Because it would be nice for my project to read from a db on the external storage I tried what I wrote above. But it didn’t work. It seems that SqlOpenDatabase can not read from external storage by using the PhoneGapFileSystem plugin.

Another idea would be to use https://github.com/litehelpers/cordova-sqlite-ext or https://github.com/an-rahulpandey/cordova-plugin-dbcopy and copy the db into the webapp folder but I don’t know how to include a phonegap plugin in NSBasic. I’d really like to know how that works.


#6

You might want to ask this question on the PhoneGap forums - someone might know more there.


#7

I guess we need phonegap plugin ‘cordova-sqlite-ext’

On https://www.npmjs.com/package/cordova-sqlite-storage it says:

The following features are available in litehelpers / cordova-sqlite-ext:

  • Pre-populated database (Android/iOS/macOS/Windows)

So I guess that is what we are looking for. I tried to implement that plugin in the config.xml

<gap:plugin name=“cordova-sqlite-ext” source=“npm” />

but with that phonegap will not create an apk but gives error. What’s wrong?!?


#8

cordova-sqlite-evcore-extbuild-free is what we are looking for!
It supports external storage directory.

window.resolveLocalFileSystemURL(cordova.file.externalDataDirectory, function(externalDataDirectoryEntry) {
  var db = window.sqlitePlugin.openDatabase({name: 'external.db', androidDatabaseLocation: externalDataDirectoryEntry.toURL()});

  db.transaction(function(tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS MyTable (data)');

for reference see:

But my Javascript skills are too less to implement this plugin in NSBasic :frowning:


#9

I’ve used this control in projects myself. You simply need to add this line to PhoneGap configxml:

<plugin name="cordova-sqlite-evcore-extbuild-free" source="npm" />

I have not experimented with SD cards. We don’t have any devices here with such a slot.

Why do you need to put it on an SD Card?


#10

alright. phonegap compiles apk with
<gap:plugin name=“cordova-sqlite-evcore-extbuild-free” source=“npm” />

but not with
<gap:plugin name=“cordova-sqlite-ext” source=“npm” />

Anyway. the first one is what I need. The next days I’ll do trial & error until I’ll get this plugin working. My problem is that I know very little javascript and it’s always a hazzle to transfer javascript tutorials into NSBasic.

I don’t need to read necessarily from SD card. But I need to read from public accessible phone storage as the databases the user uses differ from each other and are just to big. so each user installs his apk and put the database(es) into a specific folder on the phone.


#11

YES!!! I got it! I guess I was first too pessimistic. I went to bed to give the “cordova-sqlite-evcore-extbuild-free” a try tomorrow. But I couldn’t sleep. I really wanted to give it a try first. And what to say… it works!!! I can read from an sqlite file on the main directory (root directory - do net get confused with rooting of android devices [my device is also not rooted]).

Just add
<gap:plugin name=“cordova-plugin-file” source=“npm” version=“3.0.0”/>
<gap:plugin name=“cordova-sqlite-evcore-extbuild-free” source=“npm” />
to the config.xml

and put the file ‘extern.db’ into the main directory of your android phone
and and add the file ‘intern.db’ to the manifest - just to see the difference on how to read from inside and how to read from outside the webapp.

And this is the code:

'###### Read prepopulated internal DB
Function btReadInternalDB_onclick()
  Dim DB=SqlOpenDatabase("intern.db")
  s=Array(["Select * from user WHERE user_level='administrator';", LoadDB_dataHandler])
  Sql(DB,s)
End Function


'###### Read prepopulated external DB
Function btReadExternalDB_onclick()
window.resolveLocalFileSystemURL(cordova.file.externalRootDirectory, gotExtDB, failExtDB)
End Sub
  Function failExtDB(error)
    Call AddLog("can not open db")
  End Function
  Function gotExtDB(externalDataDirectoryEntry)
    Dim DB = window.sqlitePlugin.openDatabase({name: "extern.db", androidDatabaseLocation: externalDataDirectoryEntry.toURL()})
    s=Array(["Select * from user WHERE user_level='administrator';", LoadDB_dataHandler])
    Sql(DB,s)
  End Function

'###### display records
Sub LoadDB_dataHandler(transaction, result)
  Dim DBrecords
  DBrecords=result
  
  For i=0 To DBrecords.rows.length-1
    record=DBrecords.rows.item(i)
    Call AddLog(record["username"])
  Next
End Function

'###### log
Sub AddLog(txt)
  TextArea1.text = txt & vbCRLF & TextArea1.text
End Sub

Here is the whole project:
SQLite_extern.zip (7.4 KB)


#12

Good work - I bet you slept well after that!


#13

Very good work! nice and working sample. Thx forwarding :slight_smile: