B4J Question DBUTILS ERROR ADDING RECORD

[Peter Lewis]

Hi ALL

I am trying to get data from a remote MySQL database and update / insert to a SQLite database. My initial method worked but was very slow. I am busy changing it to DBUTILs and it seems to go a bit faster. The Update Proc is working but at about 1 record a second but the Insert is crashing with the following error.

UpdateRecord: UPDATE [Customer] SET [CustNo]=?,[Name]=?,[Province]=? WHERE [CustNo] = ?
59
UpdateRecord: UPDATE [Customer] SET [CustNo]=?,[Name]=?,[Province]=? WHERE [CustNo] = ?
58
UpdateRecord: UPDATE [Customer] SET [CustNo]=?,[Name]=?,[Province]=? WHERE [CustNo] = ?
57
Error occurred on line: 35 (REMOTEDB)
java.lang.ClassCastException: anywheresoftware.b4a.objects.collections.Map$MyMap cannot be cast to java.util.List
at b4j.example.remotedb._customerremoteget(remotedb.java:102)
at b4j.example.main._listcustomers(main.java:110)
at b4j.example.main._menubar1_action(main.java:144)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:226)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:79)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:92)
at anywheresoftware.b4a.BA$1.run(BA.java:188)
at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)



Here is the code of the Sub

Sub CustomerRemoteGet
If Sql2.IsInitialized = False Then    'in case the connection closes
        Sql2.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://162.215.252.76:3306/himelcoz_diamonds", "himelcoz_testing", "ZXNb^iVIP1_=")
Log("init database")
    End If
    Dim rz As ResultSet '
    rz = Sql2.ExecQuery("SELECT CustNo, Name, Province FROM Customer WHERE Updated <> 0")'
   
        Do While rz.NextRow
        Dim rem  As Map
        rem.Initialize
        rem.Put("CustNo", rz.GetString("CustNo"))  
        rem.Put("Name", rz.GetString("Name"))       
        rem.Put("Province", rz.GetString("Province"))
        Log(rz.GetString("CustNo"))
    Dim filt As Map
         filt.Initialize
        filt.Put("CustNo",rz.GetString("CustNo"))
       
        If DB.sql1.IsInitialized = False Then
            DB.sql1.InitializeSQLite(File.DirApp, "data/diamonds.db", True)
        End If
       
    If DB.sql1.ExecQuerySingleResult("SELECT count(*) FROM Customer WHERE CustNo = "&rz.GetString("CustNo")) <> 0 Then
            DBUTILS.UpdateRecord2(DB.sql1, "Customer", rem, filt)

    Else

    DBUTILS.InsertMaps(DB.sql1,"Customer",rem) 'LINE 35

    End If
   
        Sql2.execNonQuery2("UPDATE Customer SET Updated = ? WHERE `CustNo` LIKE ?",Array As String(1,(rz.GetString("CustNo"))))
       
    Loop
    rz.Close
    End Sub

I have read about Connection Pool but all the examples I found give a pool (error) and I believe I should be using ASYNC but when I change to ASYNC then I get the error that it is not Initialised.

Please can you point me in the right direction.

I am slowly getting the hang of SQL and it is great

 

[Erel]

Note that DBUtils is only 100% compatible with SQLite. You might encounter issues with MySQL.

The error in this case is that you need to pass a list of maps to DBUtils.InsertMaps. You can do it with:

DBUTILS.InsertMaps(DB.sql1,"Customer",Array(rem))

 

[EnriqueGonzalez]

Dbutils is good but will not help you increasing performance.

And connection pool is a safer method of getting info from your database but not a faster solution (if you want to do it, its pretty easy to configure the only thing is that the jdbc URL is a bit more strict than the normal connection URL)

To really improve performance while inserting information into a db use transactions

SQL.begintransaction 
' a thousand update insert calls 
SQL.transactionsucessful

'And if something fails use a try catch
SQL.rollback

This line works when you have several nonqueries to the database, not when is only one.

 

[Erel]

Transactions are indeed most important when inserting data to a database.

Note that DBUtils.InsertMaps does create a transaction. However for it to be useful you need to create a list with all the data and only then call InsertMaps.