☰ ☰

B4A-database - Database files

1. Introduction

This tutorial describes how you can export or import a SQLite database from or into an app.
The SQLite database usually consists of 1 file and that file is placed in the File.DirInternal folder or xui.DefaultFolder (unless otherwise specified).
The extension of that file can be left blank, can be set to .db or .db3.
Here are some examples of file extensions: budget_db, chinook.db, dbdemos.db3
You can't copy the database file with a file manager app on your smartphone (unless that smartphone is rooted).

2. Export database

When you want to export the database that is used in one of your apps then you need to add some code lines to your app.
In the example the export method from a class called 'dbfiles' is used.
In the B4XMainPage you have to insert the following lines of code:
In the Class_Globals subroutine:

	' dbfiles class
	Private datafiles As dbfiles

In the B4XPage_Created:

	datafiles.Initialize

In the B4XPage_Disappear subroutine:

Private Sub B4XPage_Disappear
	' dbfiles class
	datafiles.export_database("budget_db","budget_db")
End Sub

To use the dbfiles class you have to add a class module to the project in the IDE (Project / Add New Module / Class Module / Standard Class). And you add the module to the parent folder.
You also add a reference to the RuntimePermissions library in the Libraries Manager panel.
And then you replace the generated code in the dbfiles tab from the IDE with the following code:

Sub Class_Globals
	' add runtime permissions library
	Private rtpermissions As RuntimePermissions
	Public exportfolder As String
	Public importfolder As String
End Sub
Public Sub Initialize
	'  2 folders in the Files folder of the app
	exportfolder = rtpermissions.GetSafeDirDefaultExternal("Export")
	importfolder = rtpermissions.GetSafeDirDefaultExternal("Import")
End Sub
Public Sub export_database(dbname As String,newname As String)
	If File.Exists(File.DirInternal, dbname) = True Then
		Dim inpstr As InputStream = File.OpenInput(File.DirInternal, dbname)
		Dim outstr As OutputStream = File.OpenOutput(exportfolder, newname,False)
		File.Copy2(inpstr,outstr)
		outstr.Close
		' copy to import folder to preserve the changes
		Dim inpstr As InputStream = File.OpenInput(File.DirInternal, dbname)
		Dim outstr2 As OutputStream = File.OpenOutput(importfolder, newname,False)
		File.Copy2(inpstr,outstr2)
		outstr2.Close
	End If
End Sub
Public Sub import_database(dbname As String)
	Dim inpstr As InputStream = File.OpenInput(importfolder, dbname)
	Dim outstr As OutputStream = File.OpenOutput(File.DirInternal, dbname,False)
	File.Copy2(inpstr,outstr)
	outstr.Close
End Sub

3. Import database

The dbfiles class module also contains an import_database method.
With this method you can import an existing (exported) database into your app (if the database structure is correct of course).
As you might have noticed the export_database method also copies the database file to the import folder.
To use the file in the import folder you have to add this line of code in the B4XPage_Created subroutine:

	datafiles.import_database("budget_db")

You have to place this line above the line that contains the database initialize call like this:

	' dbfiles class import before db.initialize!
	datafiles.Initialize
	datafiles.import_database("budget_db")
	'
	db.Initialize("mybudget","budget_db",True,False)

So now you can replace the database file in the import folder and the changes will be taken into the app.
You can change the exported file with a SQLite tool like SQLiteDatabaseBrowserPortable.exe or SQLite Expert Personal and then copy that file to the import folder of your app to use.
OR there is an other way... check out the next chapter called Database Manager.

Pages

Paragraphs