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

 

[incendio]

Thanks for this library.

I think, this code on manual is wrong

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]

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.

 

[incendio]

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]

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.