B4X Store

Source

jdashboardUI.b4xlib · Public API only

Store language: en

Tables & Data · jdashboardUI · B4J / SQLite

JDataset is a data-access and data-binding component. Its primary purpose is not to display a table: it binds UI Views to SQLite fields, loads the current record into those controls and writes the controls' values back to the database.

Highlights

Data binding between SQLite fields and Views.
Individual binding with AddLinkView.
Group binding with AddGroupView, using each control's Tag as the field name.
Record navigation: first, previous, next, last and direct position.
CRUD operations that read and write the currently bound controls.
Standard and parameterized queries with Query and Query2.
Supports TextField, JEditText, Label, ComboBox, CheckBox, jCheckbox, ImageView and JImageView.
Images and files stored as BLOB data.
Record exports to Map and List.
SQLite table creation.
Database backup/copy support.

Core concept: bind Views to fields

Data.AddLinkView(txtName, "name")
Data.AddLinkView(txtPrice, "price")
Data.AddLinkView(chkActive, "active")

Each View is associated with one field. When JDataset loads a record, the control receives the field value. When UpdateChange or AddRecord is called, JDataset reads the current value from the same control.

This makes it possible to build a B4J record editor with very little glue code.

Initialize

Private Data As JDataset
Data.Initialize(File.DirApp, "shop.db", True)

Signature:

Initialize(Path As String, FileName As String, CreateIfNecessary As Boolean)

JDataset is a data class. It has no Designer Properties and no DesignerCreateView.

AddLinkView

Data.AddLinkView(txtName, "name")
Data.AddLinkView(txtPrice, "price")
Data.AddLinkView(cmbCategory, "category")
Data.AddLinkView(chkActive, "active")
Data.AddLinkView(imgPhoto, "photo")

Supported View types in the current implementation:

TextField
JEditText
Label
ComboBox
CheckBox
jCheckbox
ImageView
JImageView

Images are read from and written to BLOB fields.

AddGroupView

You can bind a complete group by setting each control's Tag to the database field name.

txtName.Tag      = name
txtPrice.Tag     = price
cmbCategory.Tag  = category
chkActive.Tag    = active
imgPhoto.Tag     = photo

Then:

Dim Result As String = Data.AddGroupView(pnlEditor)
Log(Result)

JDataset walks the controls recursively and binds each non-empty Tag.

Query and automatic loading

Data.Query("SELECT * FROM products ORDER BY id", "id")

The second argument is the primary-key field.

JDataset executes the query, builds an ID list, selects the first record and loads its fields into the bound Views.

Parameterized query:

Dim Args() As String = Array As String("active")
Data.Query2( _
    "SELECT * FROM products WHERE status = ?", _
    Args, _
    "id")

Data.FirstRecord
Data.PreviousRecord
Data.NextRecord
Data.LastRecord

Data.Position = 3
Log(Data.Position)
Log(Data.Size)

Moving the current position automatically loads the new record into the bound controls.

Public properties:

Property	Access	Type
SQL	read	SQL
CurrentTable	read	String
Position	read / write	Int
Size	read	Int

Complete bound-form example

Sub Class_Globals
    Private Root As B4XView
    Private Data As JDataset

    Private txtName As TextField
    Private txtPrice As TextField
    Private cmbCategory As ComboBox
    Private chkActive As CheckBox
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("ProductEditor")

    Data.Initialize(File.DirApp, "shop.db", True)

    Data.AddLinkView(txtName, "name")
    Data.AddLinkView(txtPrice, "price")
    Data.AddLinkView(cmbCategory, "category")
    Data.AddLinkView(chkActive, "active")

    Data.Query("SELECT * FROM products ORDER BY id", "id")
End Sub

Private Sub btnNext_Click
    Data.NextRecord
End Sub

Private Sub btnPrevious_Click
    Data.PreviousRecord
End Sub

Private Sub btnSave_Click
    Data.UpdateChange
End Sub

Private Sub btnNew_Click
    Data.AddRecord
End Sub

Private Sub btnDelete_Click
    Data.DeleteRecord
End Sub

CRUD from bound Views

AddRecord reads the bound controls and inserts a new row.

Data.AddRecord

UpdateChange writes current control values to the selected record, excluding the primary key.

Data.UpdateChange

DeleteRecord deletes the current record and adjusts navigation.

Data.DeleteRecord

RefreshField reloads the current record into the Views.

Direct field reads

These lowercase get... methods take a field parameter, so they remain methods rather than B4X properties.

Dim Name As String = Data.getString("name")
Dim Quantity As Int = Data.getInt("quantity")
Dim Total As Long = Data.getLong("total")
Dim Price As Double = Data.getDouble("price")
Dim Photo() As Byte = Data.getBlob("photo")

Search

Dim Found As Boolean = Data.Search("code", "A-100")
Data.Find("products", "id", "category", "Keyboard")

BLOBs, images and files

Data.StoreFile("manual", File.Combine(File.DirApp, "manual.pdf"))

Dim Ok As Boolean = Data.RetrieveFile( _
    "manual", _
    File.Combine(File.DirApp, "manual-copy.pdf"))

Log(Data.GetFileSize("manual"))
Log(Data.IsFileFieldValid("manual"))

Image conversion helpers:

ImageToBytes
BytesToBitmap
ImageToBase64
Base64ToImage
IcoToBitmap

The library uses image4j-0.7 for ICO support.

Fields and schema

Dim Fields As List = Data.GetTableFields

If Data.FieldExists("price") Then
    Data.UpdateFieldValue("price", 19.95)
End If

Create a table:

Dim Columns As Map
Columns.Initialize
Columns.Put("id", "INTEGER PRIMARY KEY")
Columns.Put("name", "TEXT")
Columns.Put("price", "REAL")
Columns.Put("active", "INTEGER")

Data.CreateTable("products", Columns)

Export

Public export APIs include:

ExportRecordToMap
ExportAllRecordsToList
ExportQueryToList
ExportAllFilesFromColumn
ExportAllFilesFromColumn2

Use the dedicated BLOB/file APIs for binary data.

View management

Data.SetViewsEnabled(False)

If Data.IsViewLinked("price") Then
    Log("price is linked")
End If

Data.ClearLinkView

Database access

Dim DB As SQL = Data.SQL
Dim Copied As Boolean = Data.CopyDatabase(File.DirApp, "shop-backup.db")
Data.CloseDB

Public API

Initialization/query: Initialize, Query, Query2.

Binding: AddLinkView, AddGroupView, ClearLinkView, SetViewsEnabled, IsViewLinked.

CRUD/navigation: AddRecord, DeleteRecord, UpdateChange, NextRecord, PreviousRecord, FirstRecord, LastRecord, RefreshField.

Direct reads: getString, getInt, getLong, getDouble, getBlob, GetBitmap.

Search: Search, Find.

Images/files: ImageToBytes, BytesToBitmap, ImageToBase64, Base64ToImage, StoreFile, RetrieveFile, GetFileSize, IsFileFieldValid, IcoToBitmap.

Schema/export: GetTableFields, FieldExists, CreateTable, ExportRecordToMap, ExportAllRecordsToList, ExportQueryToList, ExportAllFilesFromColumn, ExportAllFilesFromColumn2, UpdateFieldValue.

Database: CloseDB, CopyDatabase.

Notes

For CRUD operations, simple single-table queries are the safest fit because JDataset derives the active table name from the query text.

Table and field identifiers should come from an application-controlled schema.

JTableview: advanced visual table.
TableViewUtils: static helpers for visual table-cell content.

← JTableview

TableViewUtils →

JDataset