Android Question Online Database Best Practices

jahswant

Well-Known Member
Licensed User
Longtime User
I have my old program who have been using a sync up and down to online database and doing operations on data.Now i want to move and do direct actions to online database. I've been using my classes like this.
B4X:
Sub Class_Globals
    Public Nom As String

    Public ID As Int

    Public abbrv As String

    Public ann As Int

    Public titre As String

    Public fil As Filiere

    Public nivo_etu As String
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize(id_ As Int, Nom_ As String, abbrv_ As String, id_fil As Int, titl As String, ann_ As Int, nivo_etu_ As String)
    ID = id_
    Nom = Nom_
    abbrv = abbrv_
    ann = ann_
    fil = GetFiliere(id_fil)
    titre = titl
    nivo_etu = nivo_etu_
End Sub

Public Sub Creer()
    Dim objArray As String =  "INSERT INTO niveau VALUES(Null, '" & Nom.Replace("'", "\'") & "', '" & abbrv.Replace("'", "\'") & "', '" & fil.ID & "', '" & titre & "', '" & ann & "', '" & nivo_etu & "')"
    DBAccess.Miseajour(objArray)
End Sub

Public  Sub GetFiliere(IDF As Int) As Filiere
    Dim str As String = ("SELECT * FROM filiere WHERE id_filiere='"& IDF &"'")
    Dim fill As Filiere
    Dim Rsa As ResultSet = DBAccess.SelectMoi(str)
    Do While Rsa.NextRow
        fill.Initialize(Rsa.GetInt("id_filiere"),Rsa.GetString("nom"),Rsa.GetString("abbrev"))
    Loop
    Rsa.Close
    Return fill
End Sub

Public Sub Modifier()
    Dim objArray As String =  "UPDATE niveau SET Nom='" & Nom.Replace("'", "\'") & "', abbrev='" & abbrv.Replace("'", "\'") & "', annee_etud='"& ann & "', filiere_id_filiere='" & fil.ID & "', titre='" & titre & "', nivo_etud='" & nivo_etu & "' WHERE id_niveau='" & ID & "'"
    DBAccess.Miseajour(objArray)
End Sub

Public Sub Supprimer()
    DBAccess.Miseajour("DELETE FROM niveau WHERE id_niveau='" & ID & "'")
End Sub

What are the best practicies to return values from subs since all actions are handled in jobdone event ?
 

jahswant

Well-Known Member
Licensed User
Longtime User
The best practice is to use jRDC2
It's tecnically difficult.
This is DBAccess
B4X:
'Code module
'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

Sub Miseajour(Query As String)
   
        Starter.SQL1.ExecNonQuery(Query)
End Sub

Sub SelectMoi (WithQuery  As String) As ResultSet   
    Dim Rs As ResultSet = Starter.SQL1.ExecQuery(WithQuery)   
    Return Rs   
End Sub
 
Upvote 0

Widget

Well-Known Member
Licensed User
Longtime User
I have my old program who have been using a sync up and down to online database and doing operations on data.Now i want to move and do direct actions to online database.
...
What are the best practicies to return values from subs since all actions are handled in jobdone event ?

Some additional advice:
Make sure you only use a paramaterized query to avoid SQL injection attacks.
To construct and execute the SQL statement, you may want to consider using DBUtils.bas since it uses parameters for its query. As an added bonus you will have all of the database operations in one module so if you ever have to change the syntax to accommodate another database, you swap out the DBUtils module to something else.
 
Upvote 0

jahswant

Well-Known Member
Licensed User
Longtime User
Some additional advice:
Make sure you only use a paramaterized query to avoid SQL injection attacks.
To construct and execute the SQL statement, you may want to consider using DBUtils.bas since it uses parameters for its query. As an added bonus you will have all of the database operations in one module so if you ever have to change the syntax to accommodate another database, you swap out the DBUtils module to something else.
Thanks but my problem is to use my classes and jRDC.
 
Upvote 0

jahswant

Well-Known Member
Licensed User
Longtime User
Now sql1 is a local database ( SQLite Connections ) . I want to shift MY database from local to direct server and execute All MY queries on it either while keeping the présent classes of MY application. Thé online database is MySQL.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
Upvote 0
Top