B4X Booklets
B4X SQLite Database
Copyright: © 2022 Anywhere Software Edition 2.4
Last update: 2022.08.15
Table of content 2 B4X SQLite Database
1 B4X platforms .............................................................................................................................. 5
2 SQLite Database........................................................................................................................... 6
2.1 General information ............................................................................................................. 6
2.2 SQLite Database basics ........................................................................................................ 8
2.2.1 Database initialization SQL1.Initialize / SQL1.InitializeSQLite .................................... 8
2.2.2 Table creation CREATE TABLE .................................................................................. 9
2.2.3 INTEGER PRIMARY KEY rowid ................................................................................ 10
2.2.4 Adding data INSERT INTO.......................................................................................... 11
2.2.5 Updating data UPDATE ............................................................................................... 11
2.2.6 Reading data SELECT .................................................................................................. 11
2.2.7 Filtering WHERE ......................................................................................................... 13
2.2.8 Sorting ORDER BY ..................................................................................................... 14
2.2.9 Date / Time functions ..................................................................................................... 15
2.2.10 Other functions ........................................................................................................... 17
2.2.10.1 Get the data type of columns typeof() .............................................................. 17
2.2.10.2 Get the max length of the data in a column length() ......................................... 17
2.2.10.3 Get a sub string substr() .................................................................................... 17
2.2.10.4 Replace parts of a string replace() .................................................................... 18
2.2.10.5 Find a substring in a string instr() ..................................................................... 18
2.2.10.6 Round a number round() ................................................................................. 18
2.2.10.7 Get the total number of rows count() ................................................................ 18
2.2.10.8 Get the tables in the database sqlite_master ...................................................... 18
2.2.10.9 Get the column names of a table TableName .................................................... 18
2.2.10.10 Get the number of database rows that were changed changes() .................... 19
2.2.10.11 Get the PRIMARY KEYs from a table rowid .................................................. 19
2.2.10.12 Get the last insert rowid from a table last_insert_rowid .................................. 19
2.2.11 ResultSet GetInt, GetInt2 etc B4A, B4i, B4J ........................................................... 20
2.2.12 Cursor GetInt, GetInt2 etc B4A only .................................................................... 21
2.2.13 Get Table information PRAGMA ............................................................................. 22
2.2.14 Deleting data DELETE FROM ............................................................................... 23
2.2.15 Rename a table ALTER TABLE Name RENAME TO........................................... 23
2.2.16 Add a column ALTER TABLE Name ADD COLUMN ......................................... 23
2.2.16.1 Update the database after having added a column ............................................. 23
2.2.17 Delete a table DROP TABLE .................................................................................. 23
2.2.18 Insert an image ........................................................................................................... 24
2.2.18.1 From a file .......................................................................................................... 24
2.2.18.2 From a Bitmap ................................................................................................... 24
2.2.19 Read an image ............................................................................................................ 25
2.2.20 ExecQuery vs ExecQuery2 / ExecNonQuery vs ExecNonQuery2 .......................... 26
2.2.21 Insert many rows SQL.BeginTransaction / SQL.EndTransaction ............................ 27
2.2.22 Asynchronous queries ................................................................................................ 28
2.2.23 Batch inserts AddNonQueryToBatch / ExecNonQueryBatch .................................. 29
2.3 Multiple tables .................................................................................................................... 30
2.4 Transaction speed ............................................................................................................... 32
2.5 First steps ........................................................................................................................... 33
2.5.1 Reference the SQLite library ......................................................................................... 33
2.5.2 Declare the SQLite library ............................................................................................. 33
2.5.3 Initialize the SQLite library and the variables ............................................................... 34
2.6 SQLite Database first simple example program SQLiteLight1 ........................................ 37
2.6.1 Source code .................................................................................................................... 38
2.6.1.1 Program initialization ............................................................................................. 38
2.6.2 Database handling .......................................................................................................... 40
2.6.2.1 Create database....................................................................................................... 40
Table of content 3 B4X SQLite Database
2.6.2.2 ReadDataBase ........................................................................................................ 40
2.6.2.3 ShowEntry .............................................................................................................. 41
2.6.2.4 AddEntry ................................................................................................................ 42
2.6.2.5 DeleteEntry ............................................................................................................ 43
2.6.2.6 UpdateEntry ........................................................................................................... 43
2.7 SQLite Database second simple example program SQLiteLight2 ................................... 44
2.7.1 Main module source code parts...................................................................................... 46
2.7.1.1 Declaration of the Process global variables ........................................................... 46
2.7.1.2 Show table .............................................................................................................. 47
2.7.1.3 ReadDatabaseRowIDs............................................................................................ 48
2.7.1.4 UpdateSelectedEntryDisplay ................................................................................. 48
2.7.1.5 tblData_CellClicked event ..................................................................................... 49
2.7.2 Edit Module source code parts ....................................................................................... 50
2.7.3 Filter Module source code parts ..................................................................................... 50
2.7.3.1 Update Filters ......................................................................................................... 50
2.7.3.2 GetFilterQuery ....................................................................................................... 51
2.8 SQLite Database third simple example program SQLiteLight3 ...................................... 52
2.9 SQLite Database fourth example program SQLiteLight4 ................................................ 53
2.10 SQLite Viewer ................................................................................................................... 55
3 DBUtils version 2 ....................................................................................................................... 56
3.1 DBUtil functions ................................................................................................................ 57
3.1.1 CopyDBFormAssets B4A, B4i .................................................................................... 58
3.1.2 CopyDBFormAssets B4J ............................................................................................. 58
3.1.3 CreateTable B4A, B4i, B4J .......................................................................................... 58
3.1.4 DeleteRecord B4A, B4i, B4J ....................................................................................... 58
3.1.5 DropTable B4A, B4i, B4J ............................................................................................ 59
3.1.6 ExecuteHtml B4A, B4i, B4J ........................................................................................ 59
3.1.7 ExecuteJSON B4A, B4i, B4J ....................................................................................... 59
3.1.8 ExecuteList B4A, B4i, B4J ........................................................................................... 59
3.1.9 ExecuteListView B4A ................................................................................................. 60
3.1.10 ExecuteMap B4A, B4i, B4J ..................................................................................... 60
3.1.11 ExecuteMemoryTable B4A, B4i, B4J ..................................................................... 60
3.1.12 ExecuteTableView B4J ............................................................................................ 60
3.1.13 ExecuteSpinner B4A ................................................................................................ 60
3.1.14 GetDBFolder B4A, B4i, B4J ................................................................................... 61
3.1.15 GetDBVersion B4A, B4i, B4J .................................................................................. 61
3.1.16 GetFieldInfo B4A, B4i, B4J ..................................................................................... 61
3.1.17 GetTables B4A, B4i, B4J .......................................................................................... 61
3.1.18 InsertMaps B4A, B4i, B4J ....................................................................................... 61
3.1.19 SetDBVersion B4A, B4i, B4J .................................................................................. 61
3.1.20 TableExists B4A, B4i, B4J ...................................................................................... 61
3.1.21 UpdateRecord B4A, B4i, B4J .................................................................................. 62
3.1.22 UpdateRecord2 B4A, B4i, B4J ................................................................................ 62
3.2 Examples ............................................................................................................................ 62
3.3 DBUtilsDemo example B4XPages project ....................................................................... 63
3.3.1 Code differences............................................................................................................. 65
Table of content 4 B4X SQLite Database
Main contributors: Klaus Christl (klaus), Erel Uziel (Erel)
To search for a given word or sentence use the Search function in the Edit menu.
All the source code and files needed (layouts, images etc.) of the example projects in this guide are
included in the SQLiteDatabase_SourceCode folder.
Updated for following versions:
B4A version 11.80
B4i version 8.80
B4J version 9.80
B4X Booklets:
B4X Getting Started
B4X Language
B4X IDE Integrated Development Environment
B4X Visual Designer
B4X Help tools
B4XPages Cross-platform projects
B4X CustomViews
B4X Graphics
B4X XUI B4X User Interface
B4X SQLite Database
B4X JavaObject NativeObject
B4R Example Projects
You can consult these booklets online in this link [B4X] Documentation Booklets.
Be aware that external links don’t work in the online display.
1 General information 5 B4X SQLite Database
1 B4X platforms
B4X is a suite of programming languages for different platforms.
B4X suite supports more platforms than any other tool
ANDROID | IOS | WINDOWS | MAC | LINUX | ARDUINO | RASPBERRY PI | ESP8266 | AND
MORE...
B4A Android
B4A is a 100% free development tool for Android applications, it includes all the features
needed to quickly develop any type of Android app.
B4i iOS
B4i is a development tool for native iOS applications.
B4i follows the same concepts as B4A, allowing you to reuse most of the code and build
apps for both Android and iOS.
B4J Java / Windows / Mac / Linux / Raspberry PI
B4J is a 100% free development tool for desktop, server and IoT solutions.
With B4J you can easily create desktop applications (UI), console programs (non-UI) and
server solutions.
The compiled apps can run on Windows, Mac, Linux and ARM boards (such as Raspberry
Pi).
B4R Arduino / ESP8266
B4R is a 100% free development tool for native Arduino and ESP8266 programs.
B4R follows the same concepts of the other B4X tools, providing a simple and powerful
development tool.
B4R, B4A, B4J and B4i together make the best development solution for the Internet of
Things (IoT).
B4XPages
B4XPages is an internal library for B4A, B4i and B4J allowing to develop easily cross-
platform programs.
B4XPages is explained in detail in the B4XPages Cross-platform projects booklet.
Even, if you want to develop only in one platform it is interesting to use the B4XPages
library it makes the program flow simpler especially for B4A.
2 SQLite Database 6 B4X SQLite database
2 SQLite Database
2.1 General information
This guide covers the use of SQLite Databases in the B4X languages (B4A, B4i, B4J).
All the source code and files needed (layouts, images etc.) of the example projects in this guide are
included in the SQLiteDatabase_SourceCode folder.
There are three folders for each project, one for each platform B4A, B4i and B4J.
What is a database (source Wikipedia Database):
A database is an organized collection of data for one or more purposes, usually in digital form. The
data are typically organized to model relevant aspects of reality (for example, the availability of
rooms in hotels), in a way that supports processes requiring this information (for example, finding a
hotel with vacancies). The term "database" refers both to the way its users view it, and to the logical
and physical materialization of its data, content, in files, computer memory, and computer data
storage. This definition is very general and is independent of the technology used. However, not
every collection of data is a database; the term database implies that the data is managed to some
level of quality (measured in terms of accuracy, availability, usability, and resilience) and this in
turn often implies the use of a general-purpose Database management system (DBMS). A general-
purpose DBMS is typically a complex software system that meets many usage requirements, and
the databases that it maintains are often large and complex.
The interface between your program and the database is the SQL language.
The data is stored in tables, each table has a certain number of columns and rows.
Each row contains a data set and the different data of a given set are stored in the columns.
Simple example programs are included in the SourceCode\SQL folder.