### About

Quick Steps on how to use a SQLite database.
Reference
http://www.b4x.com/b4j/help/jsql.html
B4X: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/
For this example, the database is called test.db and stored in the same folder as the project.jar
(accessed by File.DirApp).
Add SQLite driver to Project Attributes
#Region Project Attributes
'SQLite driver (sqlite-jdbc-3.7.2.jar) located in the B4J Core Libraries folder
#AdditionalJar: sqlite-jdbc-3.7.2
'FOR RASPBERRY PI ONLY
#AdditionalJar: sqlite-raspberry
#End Region
Notes
If adding the AdditionalJar attribute is forgotten, following error during compile:
java.lang.RuntimeException: Class not found: org.sqlite.JDBC
When updating the SQLite driver, ensure to use the correct driver version (like 3.7.2)
Download the driver https://bitbucket.org/xerial/sqlite-jdbc/downloads
(Note: there are also other sites to download)
The SQLite driver must be copied to the B4J Additional Libraries folder.
Select the jSQL library in the B4J IDE Library Manager
The library is added to project.b4j file under NumberOfLibraries=NUMBER as LibraryNUMBERX=jsql
Define var as sqlObj in Process_Globals
`Sub Process_Globals
...
Private sqlObj As SQL
Private sqlFile As String
...
End Sub`
Init SQLite
Open the sqlite database located in the same folder as the Jar by using the
SQLite Helper class InitializeSQLite.
Try
sqlFile = "test.db"
' Note that the flag CreateIfNecessary is set to true
sqlObj.InitializeSQLite(File.DirApp, SQLFile, True)
Catch
Log("ERROR: " & LastException.Message)
End Try
Note on InitializeSQLite Boolean Flag CreateIfNecessary
If True, then the database will be created in case not exists.
If False and the database does not exists, following error message:
ERROR: java.io.FileNotFoundException: C:\Daten\b4j\samples\SQLITE~2\Objects\testX.db
To ensure opening an existing database file, check if the database file exists by using
If File.Exists(File.DirApp, SQLFile) = True Then ...
Select Data and store in a StringBuilder
Executing a SQL Select statement requires a ResultSet, to store the data:
Dim cursor As ResultSet
Execute the SQL Select statement and retrieve the data.
Example using StringBuilder to store the content of each record:
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
Try
cursor = sqlObj.ExecQuery ("SELECT * FROM TABLE")
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
Select Data using DBUtils and store in a TableView
DBUtils module to be stored in same folder as project.b4j. Ensure to add to the project.
Dim tvwTable As TableView
DBUtils.ExecuteTableView(sqlObj, "SELECT * FROM TABLE", Null, 0, tvwTable)
Select using parameterized queries
Always use parameterized queries as characters are escaped and SQL injection is ahndled properly.
Examples
DBUtils.ExecuteMap(Main.sql_user,"SELECT * FROM user WHERE name= ?" ,Array As String(nUser))
instead "SELECT * FROM user WHERE name='"&nUser&"'"
Dim UserName as String "user*"
Dim RecordCount as Int = sqlObj.ExecQuerySingleResult2("SELECT count(*) FROM user WHERE name= ?", Array As String(UserName))
Create a new table
Example creating a table with two columns.
Tablename = mynotes.
Column1 = Title TEXT
Column2 = Content TEXT
Try
'Remove the table mynotes if exists
sqlObj.ExecNonQuery("DROP TABLE IF EXISTS mynotes")
'Create the table if not exists
sqlObj.ExecNonQuery("CREATE TABLE IF NOT EXISTS mynotes (Title TEXT , Content TEXT)")
'Insert some test data
sqlObj.ExecNonQuery ("INSERT INTO mynotes VALUES ('Title Note 1', 'Content Note 1')")
sqlObj.ExecNonQuery ("INSERT INTO mynotes VALUES ('Title Note 2', 'Content Note 2')")
sqlObj.ExecNonQuery ("INSERT INTO mynotes VALUES ('Title Note 3', 'Content Note 3')")
Catch
Log("ERROR: " & LastException.Message)
End Try
Auto Increment Field
To create an auto increment field, use as field type INTEGER PRIMARY KEY.
When inserting, set the value of that field to Null which automatically increments by one when inserting.
If null is omitted, the field will not have any value and shown as empty.
Delete a single entry from a table
Example deleting from table Images using ID field.
Try
sqlObj.ExecNonQuery2("DELETE FROM mynotes WHERE ID = ?", Array As String(id))
Catch
Log(LastException.Message)
End Try
Delete all entries from a table
Example deleting all entries from table mynotes.
Try
sqlObj.ExecNonQuery("DELETE FROM mynotes")
Catch
Log(LastException.Message)
End Try
Update Entries in a Table
Try
sqlObj.ExecNonQuery2("UPDATE mynotes SET Content = ? WHERE Title = ?", Array("New Content", "MyTitle"))
Catch
Log(LastException.Message)
End Try
SQLite FAQ
Lookup from time-to-time the SQLite FAQs http://www.sqlite.org/faq.html
Some Hints
Define the tablename in queries without quotation marks.
Multiple processes can have the same database open at the same time.
Multiple processes can be doing a SELECT at the same time.
But only one process can be making changes to the database at any moment in time, however see https://sqlite.org/faq.html#q5

Changed: , Category: SQLite, File: T6C254M1814.htm, ID: 1814