Android Question sqlite import/export script system

[hub73]

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 :

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 !

 

[hub73]

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 ?

 

[DonManfred]

i want import the data

read the file with file.readlist
iterate trough the list and create a sql-command for each line. Make sure to use transactions

 

[hub73]

pseudo code,
how to determine with regex the text between !## and ##! ?

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

 

[DonManfred]

how to determine with regex the text between !## and ##! ?

based on the code you write to the textfile. why are you not using file.readlist like i suggested???

 

[hub73]

i fact i've posted the code before your reply ;-)

 

[hub73]

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.

 

[DonManfred]

i fact i've posted the code before your reply ;-)

You posted code without ### in post #1
And that´s the post i was answering giving the right infos to read that file.

 

[hub73]

Very happy for your help DonManfred.

 

[hub73]

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 :

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

 

[hub73]

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.