B4A Library SD: SqliteExtra

This new library is used to add some functions to the already precious SQL library.
  1. Insertion, updating and reading of images in fields of type BLOB.
  2. Functions to add a table, a field. Update a field. Delete a row or table.
  3. List of tables, fields and typos of the fields contained in the database
  4. Commands to populate List and ListView also with images.

SD_SqliteExtra

Author:
Star-Dust
Version: 1.03
  • sqlite
    • Fields: (TypeBase)
      • TypeFieldBlob As String
      • TypeFieldInteger As String
      • TypeFieldNumeric As String
      • TypeFieldReal As String
      • TypeFieldText As String
    • Functions:
      • AddField (FileNameDB As String, NameTable As String, NameField As String, TypeField As String, defaultValue As String) As Boolean
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.AddField("mysql.db","mytable","myfield","")
        sq.AddField("mysql.db","mytable","myNumberfield","0")
      • AddTable (FileNameDB As String, NameTable As String, Fields As String, CreateIfNecessary As Boolean) As Boolean
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.AddTable("mysql.db","mytable","ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Number INTEGER default 0, Name TEXT default ''")
      • Class_Globals As String
      • DeleteRow (FileNameDB As String, NameTable As String, Condition As String) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.DeleteRow("mysql.db","mytable","ID=1")
      • DeleteTable (FileNameDB As String, NameTable As String) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.DeleteTable("mysql.db","tableOld")
      • Initialize As String
        Initializes the object. You can add parameters to this method if needed.
      • InsertBitmap (filenameDB As String, TableName As String, Field As String, Bm As Bitmap) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.InsertBitmap("mysql.db", "mytable", "imageField", LoadBitmap(File.DirInternal,"myimage.png"))
      • IsInitialized As Boolean
        Verifica se l'oggetto sia stato inizializzato.
      • getListTable(Path As String,FileName As String) As List
      • getListFieldName(Path As String,FileName As String, TableName As String) As List
      • getListFieldTypeBase(Path As String,FileName As String, TableName As String) As List
      • getListFieldTypeName(Path As String,FileName As String, TableName As String) As List
      • PopulateList (FileNameDB As String, NameTable As String, FieldName As String, Condition As String) As List
        Example:
        Dim sq as sqlite
        sq.initialize
        Dim L As list = sq.PopolateList("mysql.db","mytable","Name", "")
      • PopulateListTwoLine (FileNameDB As String, NameTable As String, FieldName As String, FieldSecondLine As String, FieldID As String, Condition As String, LV As ListView, CLearListView As Boolean) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.PopolateListTwoLine("mysql.db","mytable","City","State","ID", "", ListView1,True)
      • PopulateListTwoLineBitmap (FileNameDB As String, NameTable As String, FieldName As String, FieldSecondLine As String, FieldID As String, FieldBitmap As String, Condition As String, LV As ListView, CLearListView As Boolean) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.PopolateListTwoLine("mysql.db","mytable","City","State","banner","ID", "", ListView1,True)
      • PopulateListView (FileNameDB As String, NameTable As String, FieldName As String, FieldID As String, Condition As String, LV As ListView, CLearListView As Boolean) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.PopolateList("mysql.db","mytable","CityField","ID", "", ListView1,True)
      • readBitmap (filenameDB As String, TableName As String, Filter As String, Field As String) As Bitmap
        Example:
        Dim sq as sqlite
        sq.initialize
        Dim B as Bitmap = sq.readBitmap("mysql.db", "mytable","ID=1", "imageField")
      • UpdateBitmap (filenameDB As String, TableName As String, Filter As String, Field As String, Bm As Bitmap) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.UpdateBitmap("mysql.db", "mytable","ID=1", "imageField", LoadBitmap(File.DirInternal,"myimage.png"))
      • UpdateField (FileNameDB As String, NameTable As String, FieldName As String, Value As String, Condition As String) As String
        Example:
        Dim sq as sqlite
        sq.initialize
        sq.UpdateField("mysql.db","mytable","NumberField", "100", "ID=1")
        sq.UpdateField("mysql.db","mytable","StringField", "'StringValue'", "ID=1")

1.png
2.png


video1.gif
 

Attachments

  • example2.zip
    62.9 KB · Views: 596
  • example1.zip
    99.3 KB · Views: 608
  • SD_SqliteExtra 1.04.zip
    7.4 KB · Views: 631
Last edited:

Star-Dust

Expert
Licensed User
Longtime User
In the next update (In the BindingNavigator Library) I insert the Dataset
similar to what is on VB.NET

So you can associate a View with a field in a DB.
As the Cursor position is scrolled, the views of type Label, EditText or Imageview are automatically updated.
Probably there will be a new View Class that automatically creates a mask based on the fields in the table and will manage the scrolling and updating of the fields

upload_2018-3-19_23-12-48.png
4a.gif

This is the code
B4X:
Sub Globals
    Private ImageView1 As ImageView
    Private LabelName As Label
    Private EditTextDescription As EditText
 
    Dim DB As SqlNavigator
    Private LabelInfo As Label
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("main")
 
    File.Copy(File.DirAssets,"test.sql",File.DirRootExternal,"test.sql")
    DB.Initialize(File.DirRootExternal,"test.sql",False)
 
    DB.AddLinkView(ImageView1,"bitmap")
    DB.AddLinkView(LabelName,"name")
    DB.AddLinkView(EditTextDescription,"description")
 
    DB.Query("SELECT * FROM mytable")
    DB.Position=0
End Sub
 
Last edited:

Star-Dust

Expert
Licensed User
Longtime User
If you call...

B4X:
DB.Initialize(File.DirRootExternal,"test.sql",False)

to open test.sql, why do you need "filenameDB" string as per your first post, or can the same instance i.e. DB, used for different SQLite databases?
Right question, but the one in which you pass as a parameter the fileNameDB is the sqlite class, which populates the ListView, loads and saves the images, etc ...

While in the next update there will be two new classes SqlNavigator and SqlBarNavigator, as you can see in the declaration.

These new classes will handle updating the views attached to the class as the value of SqlNavigator.position changes.

SqlBarNavigator is a CustomView that contains a Navigator similar to the SeekBar that can be inserted in the Design as you see in the image, and will manage everything automatically as in VB.NET. You will only have to look at the views, as in the code shown
 

Claudio Oliveira

Active Member
Licensed User
Longtime User
In the next update I will insert the DataSetNavigator.
Similar to what is on VB.NET

So you can associate a View with a field in a DB.
As the Cursor position is scrolled, the views of type Label, EditText or Imageview are automatically updated
...

Hey Star!
Nice improvement!
Will this work the other way 'round?
I mean, view updates will also be applied to the associated database field, like VB "bound controls" do?
 

Claudio Oliveira

Active Member
Licensed User
Longtime User
You could also associate database boolean fields to checkbox and/or button views.
Spare some time to think about it... ;)
 

Star-Dust

Expert
Licensed User
Longtime User
Hey Star!
Nice improvement!
Will this work the other way 'round?
I mean, view updates will also be applied to the associated database field, like VB "bound controls" do?
Yes
You could also associate database boolean fields to checkbox and/or button views.
Spare some time to think about it... ;)
Yes.

Currently they are being tested
 

Star-Dust

Expert
Licensed User
Longtime User
Last edited:
Top