Android Question MySql to Sqlite

Kiran Raotole

Active 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
 

victormedranop

Well-Known Member
Licensed User
try with web services to connect to remote mysql server if is a public server.
if stuck let me know.
victor
 

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.

B4X:
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
 

Attachments

Last edited:

Alex Guerrero

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

B4X:
'--- 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
 

Sandman

Well-Known 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
 

Kiran Raotole

Active 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 **
 

Mahares

Well Known Member
Licensed User
sql1.Initialize(File.DirAssets,"temp1.db",False)
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:
B4X:
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)
 

Mahares

Well Known Member
Licensed User
Whats different between DirInternal and DirDefaultExernal
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.
 

Kiran Raotole

Active 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)
 

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.
 
Top