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")

 

[DonManfred]

Popolate

PopOlate?
Didn´t you mean populate? Note that you did this mistake a few times.

 

[Star-Dust]

PopOlate?
Didn´t you mean populate? Note that you did this mistake a few times.

Sorry, my english is bad... ehm also my italian...

 

[Star-Dust]

PopOlate?
Didn´t you mean populate? Note that you did this mistake a few times.

I have updated the library, are you happy now?

 

[Claudio Oliveira]

Good job Star-Dust.
Very helpful indeed.
Well done!

I have updated the library, are you happy now?

 

[Star-Dust]

Thank's @Claudio Oliveira

 

[LucaMs]

Note that you did this mistake a few times.

As you did often the mistake to write "shoes" instead of "shows"

 

[MarcoRome]

Well Done Star

 

[Star-Dust]

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

This is the code

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

 

[Mashiane]

If you call...

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?

 

[Star-Dust]

If you call...

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]

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]

You could also associate database boolean fields to checkbox and/or button views.
Spare some time to think about it...

 

[Johan Hormaza]

And is there the possibility of doing the same in B4J?
Thank you

 

[KMatle]

As you did often the mistake to write "shoes" instead of "shows"

What's wrong with popolating shows?! Did you even try?

 

[Star-Dust]

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]

And is there the possibility of doing the same in B4J?
Thank you

Yes, but I need time. As soon as I finish for b4a

 

[Johan Hormaza]

Si, ma ho bisogno di tempo.
Appena avrò finito per b4a

ok Gracias!!

 

[Claudio Oliveira]

Yes

Yes.

Currently they are being tested

Brilliant!

 

[Mashiane]

Cool that it's coming for b4j. You might need to consider availing using map variables for your filters. I like what you are doing here..