B4A Library Database table access skeleton class

Here is the the skeleton class I use for loading, saving and using sqlite tables

Note: The Date class can be found here (add this to your project)

'Class module

'Created by Nicholas John Joseph Taylor

'*>> ClsXXXLogicalNameXXX.bas <<*

' **Usage Instructions**
' 1) Create a new Class module based on the logical Name, following the convention ClsXXXLogicalNameXXX. Copy and Paste this code into it (Safer to then close this document incase it gets overwritten by mistake)
' 2) Perform a find&replace on XXXLogicalNameXXX, XXXPrimaryKeyColumnNameXXX and XXXTableNameXXX, replacing each respectively with the logical name for the class, the name of the primary key column and the name of the table in the database.
' 3) Replace Example columns with your own (Minus primary key field as it was setup up in step 1), using the examples to guide you. There are five Column Replacement Sections to overwrite
' 4) (optional) It is advised that any shared SubProcedures be created in a code module named following the convention XXXLogicalNameXXX

Sub Class_Globals
   Dim IntPrimaryKey As Int
   
   ' *>> Column replacement section #1 <<*
   'ExampleIntegerColumn
   Dim IntColumnExampleIntegerColumn As Int
   'ExampleTextColumn
   Dim StrColumnExampleTextColumn As String
   'ExampleDateColumn
   Dim DteColumnExampleDateColumn As Date
   'ExampleRealColumn
   Dim DblColumnExampleRealColumn As Double
   'ExampleBooleanColumn
   Dim BlnColumnExampleBooleanColumn As Boolean
   
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize(PrimaryKey As Int)
   IntPrimaryKey = PrimaryKey
   
   DatabaseLoad(PrimaryKey)
   
End Sub

#Region Database Operations

Sub DatabaseLoad(PrimaryKey As Int)
   Dim StrSQLQuery As String = ""
   StrSQLQuery = StrSQLQuery & " SELECT "
   
   ' *>> Column replacement section #2 <<* Note: Remove the comma from the first. If you don't know what I'm talking about then you'll understand when you get an error and see two commas when there should only be one.
   'ExampleIntegerColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleIntegerColumn"
   'ExampleTextColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleTextColumn"
   'ExampleDateColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleDateColumn"
   'ExampleRealColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleRealColumn"
   'ExampleBooleanColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleBooleanColumn"
   
   StrSQLQuery = StrSQLQuery & " FROM "
   StrSQLQuery = StrSQLQuery & "    XXXTableNameXXX"
   
   StrSQLQuery = StrSQLQuery & " WHERE "
   StrSQLQuery = StrSQLQuery & "    XXXPrimaryKeyColumnNameXXX = " & PrimaryKey

   Dim Cursor As Cursor
    Cursor = Main.SQLite.ExecQuery(StrSQLQuery)
   
   If Cursor.RowCount = 1 Then   
      IntPrimaryKey = PrimaryKey
         
      Cursor.Position = 0
      
      ' *>> Column replacement section #3 <<*
      'ExampleIntegerColumn
      IntColumnExampleIntegerColumn = Cursor.GetInt("ExampleIntegerColumn")
      'ExampleTextColumn
      StrColumnExampleTextColumn = Cursor.GetString("ExampleTextColumn")
      'ExampleDateColumn
      If Not(DteColumnExampleDateColumn.IsInitialized) Then
         DteColumnExampleDateColumn.Initialize
      End If
      DteColumnExampleDateColumn.SetDate(Cursor.GetString("ExampleDateColumn"))
      'ExampleRealColumn
      DblColumnExampleRealColumn = Cursor.GetDouble("ExampleRealColumn")
      'ExampleBooleanColumn
      BlnColumnExampleBooleanColumn = Cursor.GetInt("ExampleBooleanColumn") = 1
      
   End If

   LogColor("Loaded XXXLogicalNameXXX with query: " & StrSQLQuery,Colors.Green)

End Sub

Sub DatabaseSave
   Dim StrSQLQuery As String = ""
   StrSQLQuery = StrSQLQuery & "INSERT OR REPLACE INTO "   
   StrSQLQuery = StrSQLQuery & "XXXTableNameXXX"
   
   StrSQLQuery = StrSQLQuery & "("
   If IntPrimaryKey>0 Then
      StrSQLQuery = StrSQLQuery & "XXXPrimaryKeyColumnNameXXX,"
      
   End If
   
   ' *>> Column replacement section #4 <<* 
   ' Hint: This will be identical to *>> Column replacement section #2 <<* Copy&Paste that section here
   'ExampleIntegerColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleIntegerColumn"
   'ExampleTextColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleTextColumn"
   'ExampleDateColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleDateColumn"
   'ExampleRealColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleRealColumn"
   'ExampleBooleanColumn
   StrSQLQuery = StrSQLQuery & "   , ExampleBooleanColumn"
   
   StrSQLQuery = StrSQLQuery & ")"
   StrSQLQuery = StrSQLQuery & " VALUES "
   StrSQLQuery = StrSQLQuery & "("   
   
   If IntPrimaryKey>0 Then
      StrSQLQuery = StrSQLQuery & IntPrimaryKey & ","
      
   End If
   
   ' *>> Column replacement section #5 <<*
   'ExampleIntegerColumn
   StrSQLQuery = StrSQLQuery & "," & IntColumnExampleIntegerColumn       
   'ExampleTextColumn
   StrSQLQuery = StrSQLQuery & "," & "'" & StrColumnExampleTextColumn & "'"      
   'ExampleDateColumn
   StrSQLQuery = StrSQLQuery & "," & "'" & DteColumnExampleDateColumn.GetISO8601Date & "'"      
   'ExampleRealColumn
   StrSQLQuery = StrSQLQuery & "," & DblColumnExampleRealColumn    
   'ExampleBooleanColumn
   If BlnColumnExampleBooleanColumn Then
      StrSQLQuery = StrSQLQuery & ",1"   
   Else
      StrSQLQuery = StrSQLQuery & ",0"   
   End If
   
   StrSQLQuery = StrSQLQuery & ")"

   Main.SQLite.ExecNonQuery(StrSQLQuery)
               
   If IntColumnPrimaryKey < 1 Then
      IntColumnPrimaryKey = CdDBUtils.GetIntFromDB("XXXPrimaryKeyColumnNameXXX","SELECT last_insert_rowid() AS XXXPrimaryKeyColumnNameXXX",-1)
                     
      If IntColumnPrimaryKey = -1 Then
         LogColor("Error getting XXXPrimaryKeyColumnNameXXX after XXXLogicalNameXXX saved to database" & StrSQLQuery, Colors.Red)
                              
      End If
                     
   End If
               
   LogColor("Saved XXXLogicalNameXXX with query: " & StrSQLQuery,Colors.Green)

End Sub

#End Region

'Below is the code for the accompanying code module

''Code module
'
''*>> CdXXXLogicalNameXXX.bas <<*
'
''Subs in this code module will be accessible from all modules.
'Sub Process_Globals
'   'These global variables will be declared once when the application starts.
'   'These variables can be accessed from all modules.
'
'End Sub
'
'#Region Get Object
'
'   Sub New As ClsXXXLogicalNameXXX    
'      Dim Result As ClsXXXLogicalNameXXX
'      Try
'         Dim XXXLogicalNameXXX As ClsXXXLogicalNameXXX
'         XXXLogicalNameXXX.Initialize
'         
'         XXXLogicalNameXXX.DatabaseNew
'         
'         If XXXLogicalNameXXX.IntColumnPrimaryKey > 0 Then
'            Result = XXXLogicalNameXXX
'         
'         End If
'      
'      Catch
'         LogColor(LastException.Message,Colors.Magenta)
'      End Try
'      Return Result
'   End Sub
'   
'   Sub Get(IntPrimaryKey As Int) As ClsXXXLogicalNameXXX
'      Dim Result As ClsXXXLogicalNameXXX
'      Try
'         If IntPrimaryKey > 0 Then
'            Dim XXXLogicalNameXXX As ClsXXXLogicalNameXXX
'            XXXLogicalNameXXX.Initialize
'            
'            XXXLogicalNameXXX.DatabaseLoad(IntPrimaryKey)
'            
'            If XXXLogicalNameXXX.IntColumnPrimaryKey > 0 Then
'               Result = XXXLogicalNameXXX
'            
'            End If
'            
'         End If
'         
'      Catch
'         LogColor(LastException.Message,Colors.Magenta)
'      End Try
'      Return Result
'   End Sub
'   
'#End Region

Feel free to get in touch if anyone has any problems


I intend to add CreateTable fuctionality (Added to the Save sub-procedure), plus create a tool that generates these classes for me. If anyone else fancies doing this work for me then please share the code here. It will save me a job.

Kind regards,
Nick

 

[nicholas.jj.taylor]

Here is an example of a class made with the above skeleton class:

'Class module

'*>> ClsPayment.bas <<*

Sub Class_Globals
   Dim IntPrimaryKey As Int
   
   'CollectorID
   Dim IntColumnCollectorID As Int
   
   'TableRecID
   Dim IntColumnTableRecID As Int
   
   'TicketNum
   Dim IntColumnTicketNum As Int
   
   'PaymentDate
   Dim DteColumnPaymentDate As Date
   
   'Amount
   Dim DblColumnAmount As Double
   
   'PaymentTypeID
   Dim IntColumnPaymentTypeID As Int
   
   'Longitude
   Dim DblColumnLongitude As Double
   
   'Latitude
   Dim DblColumnLatitude As Double
   
   'PostedDate
   Dim DteColumnPostedDate As Date
   
   'TicketID
   Dim IntColumnTicketID As Int
   
   'SendAckdByServer
   Dim DteColumnSendAckdByServer As Date
   
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize(PrimaryKey As Int)
   IntPrimaryKey = PrimaryKey
   
   Load(PrimaryKey)
   
End Sub

#Region Database Operations

Sub DatabaseLoad(PrimaryKey As Int)
   Dim StrSQLQuery As String = ""
   StrSQLQuery = StrSQLQuery & " SELECT "
   
   'CollectorID
   StrSQLQuery = StrSQLQuery & "     CollectorID"
   
   'TableRecID
   StrSQLQuery = StrSQLQuery & "   , TableRecID"
   
   'TicketNum
   StrSQLQuery = StrSQLQuery & "   , TicketNum"
   
   'PaymentDate
   StrSQLQuery = StrSQLQuery & "   , PaymentDate"
   
   'Amount
   StrSQLQuery = StrSQLQuery & "   , Amount"
   
   'PaymentTypeID
   StrSQLQuery = StrSQLQuery & "   , PaymentTypeID"
   
   'Longitude
   StrSQLQuery = StrSQLQuery & "   , Longitude"
   
   'Latitude
   StrSQLQuery = StrSQLQuery & "   , Latitude"
   
   'PostedDate
   StrSQLQuery = StrSQLQuery & "   , PostedDate"
   
   'TicketID
   StrSQLQuery = StrSQLQuery & "   , TicketID"
   
   'SendAckdByServer
   StrSQLQuery = StrSQLQuery & "   , SendAckdByServer"
   
   
   StrSQLQuery = StrSQLQuery & " FROM "
   StrSQLQuery = StrSQLQuery & "    TabletCLPayments"
   
   StrSQLQuery = StrSQLQuery & " WHERE "
   StrSQLQuery = StrSQLQuery & "    ID = " & PrimaryKey

   Dim Cursor As Cursor
    Cursor = Main.SQLite.ExecQuery(StrSQLQuery)
   
   If Cursor.RowCount = 1 Then   
      IntPrimaryKey = PrimaryKey
         
      Cursor.Position = 0
      
      'CollectorID
      IntColumnCollectorID = Cursor.GetInt("CollectorID")
      
      'TableRecID
      IntColumnTableRecID = Cursor.GetInt("TableRecID")
      
      'TicketNum
      IntColumnTicketNum = Cursor.GetInt("TicketNum")
      
      'PaymentDate
      If Not(DteColumnPaymentDate.IsInitialized) Then
         DteColumnPaymentDate.Initialize
      End If
      DteColumnPaymentDate.SetDate(Cursor.GetString("PaymentDate"))
      
      'Amount
      DblColumnAmount = Cursor.GetDouble("Amount")
      
      'PaymentTypeID
      IntColumnPaymentTypeID = Cursor.GetInt("PaymentTypeID")
      
      'Longitude
      DblColumnLongitude = Cursor.GetDouble("Longitude")
      
      'Latitude
      DblColumnLatitude = Cursor.GetDouble("Latitude")
      
      'PostedDate
      If Not(DteColumnPostedDate.IsInitialized) Then
         DteColumnPostedDate.Initialize
      End If
      DteColumnPostedDate.SetDate(Cursor.GetString("PostedDate"))
      
      'TicketID
      IntColumnTicketID = Cursor.GetInt("TicketID")
      
      'SendAckdByServer
      If Not(DteColumnSendAckdByServer.IsInitialized) Then
         DteColumnSendAckdByServer.Initialize
      End If
      DteColumnSendAckdByServer.SetDate(Cursor.GetString("SendAckdByServer"))
      
   End If

   LogColor("Loaded Payment with query: " & StrSQLQuery,Colors.Green)

End Sub

Sub DatabaseSave
   Dim StrSQLQuery As String = ""
   StrSQLQuery = StrSQLQuery & "INSERT OR REPLACE INTO "   
   StrSQLQuery = StrSQLQuery & "TabletCLPayments"
   
   StrSQLQuery = StrSQLQuery & "("
   If IntPrimaryKey>0 Then
      StrSQLQuery = StrSQLQuery & "ID,"
      
   End If
   
   'CollectorID
   StrSQLQuery = StrSQLQuery & "     CollectorID"
   
   'TableRecID
   StrSQLQuery = StrSQLQuery & "   , TableRecID"
   
   'TicketNum
   StrSQLQuery = StrSQLQuery & "   , TicketNum"
   
   'PaymentDate
   StrSQLQuery = StrSQLQuery & "   , PaymentDate"
   
   'Amount
   StrSQLQuery = StrSQLQuery & "   , Amount"
   
   'PaymentTypeID
   StrSQLQuery = StrSQLQuery & "   , PaymentTypeID"
   
   'Longitude
   StrSQLQuery = StrSQLQuery & "   , Longitude"
   
   'Latitude
   StrSQLQuery = StrSQLQuery & "   , Latitude"
   
   'PostedDate
   StrSQLQuery = StrSQLQuery & "   , PostedDate"
   
   'TicketID
   StrSQLQuery = StrSQLQuery & "   , TicketID"
   
   'SendAckdByServer
   StrSQLQuery = StrSQLQuery & "   , SendAckdByServer"
   
   StrSQLQuery = StrSQLQuery & ")"
   StrSQLQuery = StrSQLQuery & " VALUES "
   StrSQLQuery = StrSQLQuery & "("   
   
   If IntPrimaryKey>0 Then
      StrSQLQuery = StrSQLQuery & IntPrimaryKey & ","
      
   End If
   
   'CollectorID
   StrSQLQuery = StrSQLQuery & "," & IntColumnCollectorID    
   
   'TableRecID
   StrSQLQuery = StrSQLQuery & "," & IntColumnTableRecID    
   
   'TicketNum
   StrSQLQuery = StrSQLQuery & "," & IntColumnTicketNum    
   
   'PaymentDate
   StrSQLQuery = StrSQLQuery & "," & "'" & DteColumnPaymentDate.GetISO8601Date & "'"      
   
   'Amount
   StrSQLQuery = StrSQLQuery & "," & DblColumnAmount 
   
   'PaymentTypeID
   StrSQLQuery = StrSQLQuery & "," & IntColumnPaymentTypeID    
   
   'Longitude
   StrSQLQuery = StrSQLQuery & "," & DblColumnLongitude 
   
   'Longitude
   StrSQLQuery = StrSQLQuery & "," & DblColumnLongitude    
   
   'PostedDate
   StrSQLQuery = StrSQLQuery & "," & "'" & DteColumnPostedDate.GetISO8601Date & "'"      
   
   'TicketID
   StrSQLQuery = StrSQLQuery & "," & IntColumnTicketID    

   'SendAckdByServer
   StrSQLQuery = StrSQLQuery & "," & "'" & DteColumnSendAckdByServer.GetISO8601Date & "'"      

   StrSQLQuery = StrSQLQuery & ")"

   Main.SQLite.ExecNonQuery(StrSQLQuery)

   LogColor("Saved Payment with query: " & StrSQLQuery,Colors.Green)

End Sub

#End Region

 

[aklisiewicz]

Nice job!
would be nice to see some CRUD demo with this class used
Art