☰ Cursussen/Courses Codesnippets     Top 
☰ ☰
B4A-database - Database class


1. Initialize
The database class needs 2 standard libraries: XUI and SQL (in B4A) or jSQL (in B4J).
The Initialize method contains compiler directives for B4J so this class can be used in B4J.
Sub Class_Globals
	Private xui As XUI
	Public sqllib As SQL
End Sub

Public Sub Initialize(folder As String,dbname As String, make As Boolean, reset As Boolean)
	#If B4J
		xui.SetDataFolder(folder)
	#End If
	If reset = True Then
		File.Delete(xui.DefaultFolder,dbname)		' remove db for testing
	End If
	If File.Exists(xui.DefaultFolder,dbname) = False Then
		#If B4J
			sqllib.InitializeSQLite(xui.DefaultFolder,dbname,make)
		#Else
		sqllib.Initialize(xui.DefaultFolder,dbname,make)
		#End If
	Else
		#If B4J
			sqllib.InitializeSQLite(xui.DefaultFolder,dbname,make)
		#Else
		sqllib.Initialize(xui.DefaultFolder,dbname,make)
		#End If
	End If
	Return
End Sub


2. SQL methods
The select_query method takes a SQL statement and returns a Resultset (a list of records).
The select_query_prepared method takes a SQL statement and an array of values to return a Resultset. The values are inserted in place of the question marks in the SQL statement.
The action_query method takes a SQL statement and a list of values. A transaction is started and the non query is executed. The question marks are replaced.
If the transaction was successful the transaction is ended.
If there was an error then the log contains the error message and the transaction is rolled back. No changes are made.
Public Sub select_query(sql As String) As ResultSet
	Private rs As ResultSet = sqllib.ExecQuery(sql)
	Return rs
End Sub

Public Sub select_query_prepared(sql As String, values() As String) As ResultSet
	Private rs As ResultSet = sqllib.ExecQuery2(sql,values)
	Return rs
End Sub

Public Sub action_query(sql As String, values As List)
	sqllib.BeginTransaction
	Try
		sqllib.ExecNonQuery2(sql,values)
		sqllib.TransactionSuccessful
	Catch
		Log(LastException.Message)
	End Try
	sqllib.EndTransaction
	Return
End Sub


3. Table methods
The get_last_rowid method selects the last rowid in the table and returns the value.
The get_num_rows method counts the number of records in the table and returns the value.
The table_exists method selects the tablename from the sqlite_master table. If the resultset count is greater than 0 then True is returned. If the resultset count is less than 1 then False is returned.
The get_tablenames method selects all the tablenames from the sqlite_master table and returns a list of names.
Note that depending on the SQLite version the master table can be called 'sqlite_schema'.
The get_fieldnames method uses the PRAGMA keyword and the table_info method to select all the fieldnames in the table. It returns a list of names.
The get_fieldtypes method gets the fieldtype names from the table_info. It returns a list of names.
The get_structure method selects all the table_info information and returns a resultset.
Public Sub get_last_rowid(table As String) As Int
	Private rowid As Int = sqllib.ExecQuerySingleResult("SELECT max(rowid) FROM " & table)
	Return rowid
End Sub

Public Sub get_num_rows(table As String) As Int
	Private numrows As Int = sqllib.ExecQuerySingleResult("SELECT count(*) FROM " & table)
	Return numrows
End Sub

Public Sub table_exists(table As String) As Boolean
	Private rs As ResultSet = sqllib.ExecQuery2("SELECT name FROM sqlite_master WHERE type='table' AND name=?",Array As String(table))
	If rs.RowCount > 0 Then
		Return True
	Else
		Return False
	End If
End Sub

public Sub get_tablenames As List
	' get all tables (sqlite_schema or sqlite_master)
	'Select name from sqlite_schema
	'where type ='table' AND name NOT LIKE 'sqlite_%';
	Dim lst As List
	lst.Initialize
	Private rs As ResultSet = sqllib.ExecQuery("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'")
	Do While rs.NextRow
		lst.Add(rs.GetString("name"))
	Loop
	Return lst
End Sub

public Sub get_fieldnames(table As String) As List
	Dim lst As List
	lst.Initialize
	lst.Add("rowid")
	Private rs As ResultSet = sqllib.ExecQuery("PRAGMA table_info(" & table & ")")
	Do While rs.NextRow
		' some fieldnames may contain non alphabetic charaters so enclose them in []
		lst.Add("[" & rs.GetString("name") & "]")
		'Log(rs.GetString("cid") & " " & rs.GetString("name") & " " & rs.GetString("type"))
	Loop
	'Log(lst)
	Return lst
End Sub

public Sub get_fieldtypes(table As String) As List
	Dim lst As List
	lst.Initialize
	lst.Add("rowid")
	Private rs As ResultSet = sqllib.ExecQuery("PRAGMA table_info(" & table & ")")
	Do While rs.NextRow
		lst.Add(rs.GetString("type"))
		'Log(rs.GetString("cid") & " " & rs.GetString("name") & " " & rs.GetString("type"))
	Loop
	Return lst
End Sub

Public Sub get_structure(table As String) As ResultSet
	Dim lst As List
	lst.Initialize
	Private rs As ResultSet = sqllib.ExecQuery("PRAGMA table_info(" & table & ")")
	Return rs
End Sub


4. DML methods
The add_record method inserts a record in the table. It uses a prepared SQL statement. The question marks are replaced with the values from the list argument.
The change_record method updates a record in the table. All the fields are updated with the values from the value list argument. The rowid is used in the WHERE clause to select the correct record.
The change_field_content method updates one field from a record in the table. The second argument contains the value and the fourth argument contains the fieldname.
The remove_record method deletes a record from the table. The select_all_records method selects all the records from the table and returns a resultset. The resultset will be ordered if the orderfield argument is given.
The find_record method selects 1 record from the table with the given rowid and returns a resultset.
Public Sub add_record(tablename As String, valuelst As List)
	Dim fldlst As List = get_fieldnames(tablename)
	Private sql As String = "INSERT INTO " & tablename & "("
	For i = 1 To fldlst.Size-1				' fldlst.get(0) = rowid
		If i < fldlst.Size-1 Then
			sql = sql & fldlst.Get(i) & ","
		Else
			sql = sql & fldlst.Get(i) & ")"
		End If
	Next
	sql = sql & " VALUES ("
	For i = 1 To fldlst.Size-1
		If i < fldlst.Size-1 Then
			sql = sql & "?,"
		Else
			sql = sql & "?)"

		End If
	Next
	action_query(sql,valuelst)
End Sub

Public Sub change_record(tablename As String, valuelst As List,rowid As Int)
	Dim fldlst As List = get_fieldnames(tablename)
	Private sql As String = "UPDATE " & tablename & " SET "
	For i = 1 To fldlst.Size-1				' fldlst.get(0) = rowid
		If i < fldlst.Size-1 Then
			sql = sql & fldlst.Get(i) & " = ?, "
		Else
			sql = sql & fldlst.Get(i) & " = ? WHERE rowid = " & rowid
		End If
	Next
	action_query(sql,valuelst)
End Sub

Public Sub change_field_content(tablename As String, valuelst As List,rowid As Int,fldname As String)
	Private sql As String = "UPDATE " & tablename & " SET "
	sql = sql & fldname & " = ? WHERE rowid = " & rowid
	action_query(sql,valuelst)
End Sub

Public Sub remove_record(tablename As String,rowid As Int)
	Private sql As String = "DELETE FROM " & tablename & " WHERE rowid = ?"
	Private lst As List = Array As String(rowid)
	action_query(sql,lst)
End Sub

Public Sub select_all_records(tablename As String, orderfld As String) As ResultSet
	'Private sql As String = "SELECT rowid, name, reference FROM category ORDER BY name"
	Dim fldlst As List = get_fieldnames(tablename)
	Private sql As String = "SELECT "
	For i = 0 To fldlst.Size-1				' fldlst.get(0) = rowid
		'Log(fldlst.Get(i))
		If i < fldlst.Size-1 Then
			sql = sql & fldlst.Get(i) & ","
		Else
			sql = sql & fldlst.Get(i) & ""
		End If
	Next
	sql = sql & " FROM " & tablename
	If orderfld <> "" Then
		sql = sql & " ORDER BY " & orderfld
	End If
	Private rs1 As ResultSet = select_query(sql)
	Return rs1
End Sub

Public Sub find_record(tablename As String, rowid As Int) As ResultSet
	Dim fldlst As List = get_fieldnames(tablename)
	Private sql As String = "SELECT "
	For i = 0 To fldlst.Size-1				' fldlst.get(0) = rowid
		If i < fldlst.Size-1 Then
			sql = sql & fldlst.Get(i) & ","
		Else
			sql = sql & fldlst.Get(i) & " FROM " & tablename & " WHERE rowid = ?"
		End If
	Next
	Private fldarr() As String = Array As String(rowid)
	Private rs1  As ResultSet = select_query_prepared(sql,fldarr)
	Return rs1
End Sub


5. Database methods
The export_database method copies the database file (from the File.DirInternal folder) to the export folder.
The export folder must be writable (using RuntimePermissions library). An initialization must be set in the B4XPage_Created subroutine. 
	' folders
	exportfolder = rtpermissions.GetSafeDirDefaultExternal("Export")
	importfolder = rtpermissions.GetSafeDirDefaultExternal("Import")
A copy is also placed in the import folder.
The database can be given a new name to make a new (backup) version of it.
The import_database method copies a database file into the importfolder.
In the code you can call this method just before you initialize the database.
Public Sub export_database(dbname As String, exportfolder As String, importfolder As String,newname As String)
	'sqllib.Close
	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,importfolder 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