B4A Library TDDBUtils Functions to handle SQLite and SQLCipher

Library: TDDBUtils.b4xlib
Language: B4A
Version 1.8
Manual: Version 1.5.2 (did some checks and improvements, added SQLCipher tutorial)
Status: Work in Process
(C) This library is free for non commercial use other use see manual.

New to 1.8:
  • optimized code
  • fixed some errors
  • Added grouping and sorting of records to select1
  • Added new function 'findRecord' to return cursor position by select condition
  • Updated lib attached

This library supports the development of database driven Application (SQLite3/SQLCipher 4) with a growing (Work in Process) list of handy functions to make developers life easier. All the functions are explained in the attached manual. The lib code is well documented. To examin or modify the code rename TDDBUtils.b4xlib to TDDBUtils.zip uncompress it and get a readable .bas file.

Please report any error or whish or proposal via a reply to this post or direct to techdoc@gbecker.de.

See also brand new library extended Nativ Views working well together with this library in case of development of database driven apps.

List of Functions:

Manage Database
  • OpenDB
  • CloseDB
  • startTransaction
  • commitTransaction
  • setDBcompactMode
  • compactDB
  • gettableNames
  • getColumnsInfo
  • findColumnInfo
  • catch and identify SQLError
Manage records
  • insert
  • update
  • delete
  • save
  • select1, simple select with grouping and sorting
  • select2, select spread 2 joined tables
  • select3, select based on a date
  • select4, report Max/Min,Avg,Sum,Count values
  • dateDifference, select between 2 dates
  • find a record
  • checkDBNull
  • checkExists
Manage individual encryption for column values
  • EncryptText
  • DecryptText
Manage data bound Form views:
supported views: EditText, Label, RadioButton, CheckBox, ImageView
  • clearViews
  • Views2Columns
  • Columns2Views
 

Attachments

  • TDDBUtils.pdf
    499.1 KB · Views: 66
  • TDDButils.b4xlib
    6.5 KB · Views: 19
Last edited:

incendio

Well-Known Member
Licensed User
Longtime User
Thanks for this library.

I think, this code on manual is wrong
B4X:
select1(TabName As String, Columns As String, Condition As String) As ResultSet

The select functions is not enough if only able to join from 2 tables, perhaps could add function to run sql statement directly.
 

Guenter Becker

Active Member
Licensed User
The manual says that select1 is for a simple select over 1 table! if you like to do a select over two tables than please use select2. I looked into the manual and it says exact this.
Did an small update to the manual to have a better explanation of select2 by adding an example.

I also added an tutorial to show how to convert simple SQLite Datbase to an encrypted SQLite Datbase that can be handeled with the SQLCipher Lib.
 
Last edited:

incendio

Well-Known Member
Licensed User
Longtime User
1. Take a look at select 1 command, Columns as String?

2. I know, select 2 is to select over two tables, but sometimes there are a need to select over many tables, not only two tables!
 

Guenter Becker

Active Member
Licensed User
In select1
if you leave columns empty than all columns are adressed. To adress only one ore a few one please enter the column names separated by a comma like Columns = "ProdNo, Price, Description"

You are right sometimes you need to span over more than 2 tables, if you like to do so you have to enhance the function select or right a new select 4a function and add it. In the current version select2 is limited for only 2 tables. And for the experts it is using an innerjoin.
 
Top