Android Question MySql to Sqlite

Discussion in 'Android Questions' started by Kiran Raotole, Jul 11, 2018.

  1. Kiran Raotole

    Kiran Raotole Member Licensed User

    Hii Expert

    I want to make app that download data from mysql and insert it into android db file.
    Plz give me example or sample code.

    I am new in B4A
     
  2. Pooya1

    Pooya1 Active Member Licensed User

  3. Peter Simpson

    Peter Simpson Expert Licensed User

  4. victormedranop

    victormedranop Active Member Licensed User

    try with web services to connect to remote mysql server if is a public server.
    if stuck let me know.
    victor
     
  5. Alex Guerrero

    Alex Guerrero Member Licensed User

    Hello, I append an example where data of a table in MySQL is read through PHP and inserted in SQLite.

    You must configure the file datos.php, assign the execution permissions and copy in the respective folder of the server (http://200.234.54.12/mysql/), it is recommended to change the name to the file.

    Code:
    Dim ssql As SQL
    Dim hc As OkHttpClient
    Dim req As OkHttpRequest
    Dim Query As String


    '-----------------------
    hc.InitializeAcceptAll("hc")


    '-----------------------
    Query="SELECT idLot, nlote FROM lotes"
    req.InitializePost2(
    "http://200.234.54.12/mysql/datos.php", Query.GetBytes("UTF8"))
    hc.Execute(req, 
    1'--> tarea = 1

    '-----------------------
    Sub hc_ResponseSuccess (Response As OkHttpResponse, tarea As Int) 'TaskId
        Dim res As String
        
    Dim cant, idact As Int  ', tarea
       
        out.InitializeToBytesArray(
    0)
        
    'Log("tarea:" & tarea)
        'res = Response.GetString("UTF8")
        Response.GetAsynchronously("res", out, False, tarea)
       
        res = 
    BytesToString(out.ToBytesArray, 0, out.ToBytesArray.Length, "utf8")

        
    Log(res)
       
        
    If tarea = 1 Then
            
    Try
                
    Dim jsonp As JSONParser
                jsonp.Initialize(res)
       
                
    Dim l As List
                l.Initialize
                l = jsonp.NextArray

                
    If l.Size > 0 Then
                    
    Dim m As Map
                    
    Dim k As Int
                    
    Dim t As String
                    
    For i = 0 To l.Size - 1
                        m = l.Get(i)
                        k = i+
    1
                        t = m.Get(
    "idLot") &" - "&  m.Get("nlote")
                        ssql.ExecNonQuery(
    "INSERT OR IGNORE INTO Combos (key,id,value) VALUES ("& k &",'LOTE','"& t &"')")
                    
    Next
                    
    Msgbox("Lotes actualizados, reinicie...""Atención")
                
    Else
                    
    Msgbox("Se presento un error, repita el proceso...""Error")
                
    End If
               
            
    Catch
                
    Msgbox("Se presento un error, repita el proceso...""Error")
            
    End Try
        
    End If

        Response.Release
    End Sub
     

    Attached Files:

    Last edited: Jul 11, 2018
  6. Alex Guerrero

    Alex Guerrero Member Licensed User

    A better way, code not tested with Job.Download2, use OkHttpUtils2.

    Code:
    '--- HttpUtils2 and HttpJob based on OkHttp
     
    Public Sub mysql_to_sqlite
        
    Dim Job As HttpJob
        Job.Initialize(
    "Job", Me)

        Query=
    "SELECT idLot, nlote FROM lotes"
        Job.Download2(
    "http://200.234.54.12/mysql/datos.php", Query.GetBytes("UTF8"))

        
    Wait For (Job) JobDone(Job As HttpJob)
        
    If Job.Success Then
            
    Try
                
    Dim jsonp As JSONParser
                jsonp.Initialize(Job.GetString)

                
    Dim l As List
                l.Initialize
                l = jsonp.NextArray

                
    If l.Size > 0 Then
                    
    Dim m As Map
                    
    Dim k As Int
                    
    Dim t As String
                    
    For i = 0 To l.Size - 1
                        m = l.Get(i)
                        k = i+
    1
                        t = m.Get(
    "idLot") &" - "&  m.Get("nlote")
                        ssql.ExecNonQuery(
    "INSERT OR IGNORE INTO Combos (key,id,value) VALUES ("& k &",'LOTE','"& t &"')")
                    
    Next
                    
    Msgbox("Lotes actualizados, reinicie...""Atención")
                
    Else
                    
    Msgbox("Se presento un error, repita el proceso...""Error")
                
    End If
             
            
    Catch
                
    Msgbox("Se presento un error, repita el proceso...""Error")
            
    End Try
           
        
    End If
        Job.Release
    End Sub
     
  7. Sandman

    Sandman Active Member Licensed User

    Assuming you're in control of the server running MySQL, there's potentially a simpler solution: Just convert the MySQL db to a SQLite db on the server and simply return that file for the device to use.

    Here's an answer on Stack Overflow that probably could help you quite a bit: Convert MySQL to SQlite
     
  8. Erel

    Erel Administrator Staff Member Licensed User

  9. Kiran Raotole

    Kiran Raotole Member Licensed User

    thanks everybody.

    Now, I store temp1.db file in asset folder & i want to open copy to default folder

    sql1.Initialize(File.DirAssets,"temp1.db",False)

    I'm getting error log

    Logger connected to: Xiaomi Mi A1
    --------- beginning of main
    *** Service (starter) Create ***
    ** Service (starter) Start **
    ** Activity (main) Create, isFirst = true **
    ** Activity (main) Resume **
    ** Activity (main) Pause, UserClosed = false **
    ** Activity (working) Create, isFirst = true **
    Error occurred on line: 32 (working)
    android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:808)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:793)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:696)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:671)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at b4a.example.working._activity_create(working.java:388)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:735)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:360)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:260)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at b4a.example.working.afterFirstLayout(working.java:104)
    at b4a.example.working.access$000(working.java:17)
    at b4a.example.working$WaitForLayout.run(working.java:82)
    at android.os.Handler.handleCallback(Handler.java:789)
    at android.os.Handler.dispatchMessage(Handler.java:98)
    at android.os.Looper.loop(Looper.java:172)
    at android.app.ActivityThread.main(ActivityThread.java:6637)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)
    ** Activity (working) Resume **
    *** Service (starter) Create ***
    ** Service (starter) Start **
    ** Activity (main) Create, isFirst = true **
    ** Activity (main) Resume **
    ** Activity (main) Resume **
    ** Activity (main) Pause, UserClosed = false **
    ** Activity (working) Create, isFirst = true **
    Error occurred on line: 32 (working)
    android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:808)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:793)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:696)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:671)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:780)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:363)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:260)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at b4a.example.working.afterFirstLayout(working.java:104)
    at b4a.example.working.access$000(working.java:17)
    at b4a.example.working$WaitForLayout.run(working.java:82)
    at android.os.Handler.handleCallback(Handler.java:789)
    at android.os.Handler.dispatchMessage(Handler.java:98)
    at android.os.Looper.loop(Looper.java:172)
    at android.app.ActivityThread.main(ActivityThread.java:6637)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)
    ** Activity (working) Resume **
    *** Service (starter) Create ***
    ** Service (starter) Start **
    ** Activity (main) Create, isFirst = true **
    ** Activity (main) Resume **
    ** Activity (main) Pause, UserClosed = false **
    ** Activity (working) Create, isFirst = true **
    Error occurred on line: 32 (working)
    android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:808)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:793)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:696)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:671)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at b4a.example.working._activity_create(working.java:388)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:735)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:360)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:260)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at b4a.example.working.afterFirstLayout(working.java:104)
    at b4a.example.working.access$000(working.java:17)
    at b4a.example.working$WaitForLayout.run(working.java:82)
    at android.os.Handler.handleCallback(Handler.java:789)
    at android.os.Handler.dispatchMessage(Handler.java:98)
    at android.os.Looper.loop(Looper.java:172)
    at android.app.ActivityThread.main(ActivityThread.java:6637)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)
    ** Activity (working) Resume **
     
  10. Mahares

    Mahares Well Known Member Licensed User

    You cannot initialize a database in Assets folder. You need to copy it to internal or default or root external first, because Assets is read only. Something like this:
    Code:
    Private filename, DBFilePath As String
            DBFilePath = 
    File.DirInternal  'you can use the default external or root external instead or equival with runtime permissions
            filename="temp1.db"
            
    File.Copy(File.DirAssets,filename,DBFilePath,filename)
            SQL1.Initialize(DBFilePath,filename,
    True)
     
  11. Kiran Raotole

    Kiran Raotole Member Licensed User

    thanks Mahares its work
    Whats different between DirInternal and DirDefaultExernal
     
  12. Mahares

    Mahares Well Known Member Licensed User

    Files in DirInternal are private to the application and can only be accessed by it. Files in the DirDefaultExernal can be accessed by other applications. There are are few other differences listed in the forum.
     
  13. Kiran Raotole

    Kiran Raotole Member Licensed User

    Okay. If there is any problem if I used DirDefaultExternal.

    I try it
    Log :
    java.io.FileNotFoundException: /storage/emulated/0/Android/data/b4a.example/files/temp1.db (No such file or directory)
     
  14. Mahares

    Mahares Well Known Member Licensed User

    if your target SDK in the manifest is 23 and higher, you need to use runtime permissions. Need runtimepermissions lib: rp.GetSafeDirDefaultExternal("") instead of File.DirDefaultExternal
    https://www.b4x.com/android/forum/threads/runtime-permissions-android-6-0-permissions.67689/
    There are many other links that explain how to use it. If you want to use File. DirDefaultExternal, then change the target to 19 without worrying about runtime permissions for now. You will be ok.
     
    Peter Simpson likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice