Android Question sqlite import/export script system

hub73

Active Member
Licensed User
Longtime User
Hi !
i use sqlite access inside my application.
i've the database 'my_base.db'... forms, queries, ...

1)
To export data from my application to another device, i put queries inside 'my_script.txt' file

Like this :

B4X: 
insert into t_something (Pk_key,Title, other) values (1,'k,jjkljl','fsdfsdfsdf')
insert into t_other (Pk_key,Title, other) values (1,'k,jjkljl','fsdfsdfsdf')
insert into t_big (Pk_key,data, ...., ....) value (1, 'jkljklj', 'jhkkkkkkkkkkkkkkkkk', 'jhkkkkkkkkkkkkkkkkk''jhkkkkkkkkkkkkkkkkk''jhkkkkkkkkkkkkkkkkk''jhkkkkkkkkkkkkkkkkk''jhkkkkkkkkkkkkkkkkk''jhkkkkkkkkkkkkkkkkk')

2) Always from my application but from the other device. i want import the data.

Have you an idea ? SQLIte Command from shell ? consider the database already exist. Difficult to parse the file an create queries with SQL1.ExecQuery(Query) inside b4a...


Need advices !

Thanks !
 
Sort by date Sort by votes

hub73

Active Member
Licensed User
Longtime User
Pehaps i could put delimiters for each written query like !##insert into ...... ##!, next parse the file with a regex to retreive each query and use ExecQuery...
Could you help me to code this ?
 
Upvote 0

hub73

Active Member
Licensed User
Longtime User
pseudo code,
how to determine with regex the text between !## and ##! ?

B4X: 
Dim Query_array() as String
Dim Query as String
Dim Data as String
Data = File.ReadString(File.DirRootExternal, "test.txt")
Query_array = Regex.Split("<query>(.*?)</query>", Data)
Dim l As List
l.Initialize2(Query_array)
For i = 0 To l.Size - 1
    Query = l.Get(i)
    SQL1.ExecQuery(Query)
Next
 
Last edited:
Upvote 0

hub73

Active Member
Licensed User
Longtime User
in my mind <tag> was interesting in case of several lines for a query or crlf characters stored into a query string.

file.readlist seems to be interersting too.

Many thanks for your help DonManfred.
 
Upvote 0

hub73

Active Member
Licensed User
Longtime User
B4X: 
Sub Importer (Nom_dossier As String, Nom_fichier As String)

    Dim Tableau_requete() As String
    Dim Requete As String
    Dim Donnees As String

    Dim Reponse As Int
   
    Reponse = Msgbox2("Voulez vous ajouter des enregistrements dans cette base de données ?","Confirmation","oui","annuler","non", Null)
       
    If Reponse = DialogResponse.POSITIVE Then
   
        If File.Exists (Nom_dossier, Nom_fichier) Then
       
            Donnees = File.ReadString(Nom_dossier, Nom_fichier)
           
            edt_transfert_texte.text = Donnees
           
            Try
               
                Dim matcher1 As Matcher
                matcher1 = Regex.Matcher("<REQUETE>(.+?)<\/REQUETE>", Donnees)
                Dim i As Int
                i = 0
   
                ProgressDialogShow ("Veuillez patienter traitement en cours...")
                Do While matcher1.Find = True
                    Requete = matcher1.Match
                    Requete = Requete.Replace ("<REQUETE>","")
                    Requete = Requete.Replace ("</REQUETE>","")
                    SQL1.ExecNonQuery (Requete)
                    i = i + 1
                    DoEvents
                Loop   
                ProgressDialogHide       
   
                ToastMessageShow ("Fichier importé. (" & i & ") requêtes effectuées.", False)
               
            Catch
               
                Msgbox ("Erreur lors de l'importation ! " & CRLF & LastException.Message,"Erreur")
           
            End Try

        Else
            Msgbox ("Fichier d'importation '" & Nom_fichier & "' non trouvé !","Erreur")
        End If
       
    End If

End Sub

With this code i've a problem with the regex. If i've some 'CRLF' inside the text field value of a query, <REQUETE> ... </REQUETE> is not detected. Somebody have i idea to update the regex ?

For example with this :

B4X: 
<REQUETE> .... value ('
This is a text
with some
line returns',
...
</REQUETE>
 
Upvote 0

hub73

Active Member
Licensed User
Longtime User
B4X: 
Sub Executer_requetes ()

    Dim Requete As String
    Dim Donnees As String

    Dim Reponse As Int
   
    Reponse = Msgbox2("Voulez vous ajouter des enregistrements dans cette base de données ?","Confirmation","oui","annuler","non", Null)
       
    If Reponse = DialogResponse.POSITIVE Then
           
        Try

            Donnees = edt_transfert_texte.Text
            Donnees = Donnees.Replace (CRLF, "<RET>")
           
            If Donnees <> "" Then
           
                Dim matcher1 As Matcher
                matcher1 = Regex.Matcher("<REQUETE>(.+?)<\/REQUETE>", Donnees)
                Dim i As Int
                i = 0

                ProgressDialogShow ("Veuillez patienter traitement en cours...")
                Do While matcher1.Find = True
                    Requete = matcher1.Match
                    Requete = Requete.Replace ("<REQUETE>","")
                    Requete = Requete.Replace ("</REQUETE>","")
                    Requete = Requete.Replace ("<RET>",CRLF)
                    SQL1.ExecNonQuery (Requete)
                    i = i + 1
                    DoEvents
                Loop   
                ProgressDialogHide   
               
            End If   

            ToastMessageShow ("requête effectuées (" & i & ").", False)
           
        Catch
           
            ProgressDialogHide
            Msgbox ("Erreur lors de l'exécution des requêtes  ! " & CRLF & LastException.Message,"Erreur")
       
        End Try
       
    End If

   

End Sub
Finally i replace CRLF by <RET> before the regex. The opposite before the query.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…