Android Question Saving Resultset values to remote MySQL

Setlodi · Aug 8, 2024

Hi there

I hope someone can help me. I have an android app that is used by remote workers using mobile PDAs. They collect data in the field and this is saved on the local SQLite DB on the devices. At the end of the day, the workers hand in the devices and I need them to click a button to upload data from the local SQLite DB to a remote MySQL DB.

I know how to:

run a SQLite query to get results on the devices
Get values from the local database
configure jRDC, run DBCommand, configure B4J server, config file etc.

What I want to do is get todays values from local SQLite and add them values to the remote MySQL.

Can this be run in a loop after getting resultset?

B4X:

Dim cmd As DBCommand = CreateCommand("Insert_Entry", Array(GetString(Column_A), GetString(Column_B)))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For (j) JobDone(j As HttpJob)
    
    If j.Success Then
        Log("Inserted successfully!!!!")
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release

Any ideas?

aeric · Aug 8, 2024

Setlodi said:
CreateRequest.ExecuteBatch(Array(cmd)

As the name suggests, this method executes a batch of commands.

Setlodi · Aug 8, 2024

aeric said:
As the name suggests, this method executes a batch of commands.

Thanks for your response aeric. So if my SQLite query returns 10 records, how would I insert all 10 records into my remote db?

aeric · Aug 9, 2024

~~Not~~ tested.

B4X:

Dim cmdList As List
cmdList.Initialize
Dim rs As ResultSet = sql.ExecQuery("SELECT Column_A, Column_B FROM table1")
Do While rs.NextRow
    Dim cmd As DBCommand = CreateCommand("Insert_Entry", Array(rs.GetString("Column_A"), rs.GetString("Column_B")))
    cmdList.Add(cmd)
Loop
rs.Close

Dim j As HttpJob = CreateRequest.ExecuteBatch(cmdList, Null)

Setlodi · Aug 9, 2024

Thank you once more aeric. I'm now getting the following error:

B4X:

ResponseError. Reason: Server Error, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/>
<title>Error 500 org.eclipse.jetty.io.EofException: Closed</title>
</head>
<body><h2>HTTP ERROR 500 org.eclipse.jetty.io.EofException: Closed</h2>
<table>
<tr><th>URI:</th><td>/rdc</td></tr>
<tr><th>STATUS:</th><td>500</td></tr>
<tr><th>MESSAGE:</th><td>org.eclipse.jetty.io.EofException: Closed</td></tr>
<tr><th>SERVLET:</th><td>anywheresoftware.b4j.object.JServlet-4e08711f</td></tr>
</table>
<hr/><a href="https://eclipse.org/jetty">Powered by Jetty:// 11.0.9</a><hr/>
</body>
</html>

I can confirm that there are values in the Test_Table

B4X:

        Log("StopSeq = " & rs.GetString("StopSeq"))
        Log("FirstName = " & rs.GetString("FirstName"))
        Log("Surname = " & rs.GetString("Surname"))

When I log the list:

B4X:

Log("cmdList = " & cmdList)

I get

B4X:

cmdList = (ArrayList) [[IsInitialized=true, Name=Insert_Check, Parameters=[Ljava.lang.Object;@10cdeaa
], [IsInitialized=true, Name=Insert_Check, Parameters=[Ljava.lang.Object;@fceca9b
], [IsInitialized=true, Name=Insert_Check, Parameters=[Ljava.lang.Object;@ba9ae38
], [IsInitialized=true, Name=Insert_Check, Parameters=[Ljava.lang.Object;@1a46e11
], [IsInitialized=true, Name=Insert_Check, Parameters=[Ljava.lang.Object;@eb23b76
]]

Is cmdlist not supposed to have ResultSet values?

Here's my code:

B4X:

    Dim cmdList As List
    cmdList.Initialize
    Dim rs As ResultSet = SQL.ExecQuery("SELECT StopSeq, FirstName,  Surname FROM Test_Table")
    Do While rs.NextRow
        
        Log("StopSeq = " & rs.GetString("StopSeq"))
        Log("FirstName = " & rs.GetString("FirstName"))
        Log("Surname = " & rs.GetString("Surname"))
        
        Dim cmd As DBCommand = CreateCommand("Insert_Check", Array(rs.GetString("StopSeq"), rs.GetString("FirstName"), rs.GetString("Surname")))
        cmdList.Add(cmd)
        
        Log("cmdList = " & cmdList)
        
    Loop
    rs.Close

    Dim j As HttpJob = CreateRequest.ExecuteBatch(cmdList, Null)

My config.properties file:

B4X:

sql.Insert_Check=INSERT INTO StopCheck_Table (StopSeq, FirstName, Surname) VALUES (?,?,?);

aeric · Aug 9, 2024

What is the logs in jRDC2 server?

Setlodi · Aug 9, 2024

B4X:

2024-08-09 10:08:52.564:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2024-08-09 10:08:53.693:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2024-08-09 10:08:54.069:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 14.0.1+7
2024-08-09 10:08:54.277:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
2024-08-09 10:08:54.378:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@3f6f6701{/,file:///C:/Users/JUSTIC~1.SET/DOCUME~1/DIGITR~1/%23ECITA~2/B4JSER~1/DIGIPO~1/Objects/www,AVAILABLE}
2024-08-09 10:08:54.414:INFO :oejs.RequestLogWriter:main: Opened C:\Users\Justice.Setlodi\Documents\DigiTraffic Development\# eCitations Development\B4J Server Development\digiPolice_jRDC_Server\Objects\logs\b4j-2024_08_09.request.log
2024-08-09 10:08:54.501:INFO :oejs.AbstractConnector:main: Started ServerConnector@1ea9f6af{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2024-08-09 10:08:54.507:INFO :oejs.Server:main: Started Server@2cd76f31{STARTING}[11.0.9,sto=0] @2413ms
jRDC is running (version = 2.23)

When I run it without the resultset it works i.e. saving 1 record

B4X:

    Dim cmd As DBCommand = CreateCommand("Insert_Check", Array(StopSeq, FirstName, Surname))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For (j) JobDone(j As HttpJob)
  
    If j.Success Then
        Log("Inserted successfully!!!!")
    Else
        Log("ERROR: " & j.ErrorMessage)

    End If
    j.Release
End Sub

Nohup:

B4X:

2024-08-09 10:19:20.531:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2024-08-09 10:19:20.954:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2024-08-09 10:19:21.324:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 11.0.17+8-post-Ubuntu-1ubuntu222.04
2024-08-09 10:19:21.764:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
2024-08-09 10:19:21.804:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@359df09a{/,file:///root/www,AVAILABLE}
2024-08-09 10:19:21.934:INFO :oejs.RequestLogWriter:main: Opened /root/logs/b4j-2024_08_09.request.log
2024-08-09 10:19:21.959:INFO :oejs.AbstractConnector:main: Started ServerConnector@3c19aaa5{HTTP/1.1, (http/1.1)}{0.0.0.0:17178}
2024-08-09 10:19:21.985:INFO :oejs.Server:main: Started Server@175b9425{STARTING}[11.0.9,sto=0] @2168ms
jRDC is running (version = 2.23)
2024-08-09 10:33:54.128:INFO :cmvci.AbstractPoolBackedDataSource:qtp1617791695-15: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 18ivkzwb5f5vt8byxq0oa|13a5fe33, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 18ivkzwb5f5vt8byxq0oa|13a5fe33, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://160.119.254.12:3306/Citations_Database_2, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Fri Aug 09 10:33:54 UTC 2024 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Fri Aug 09 10:33:54 UTC 2024 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Fri Aug 09 10:33:54 UTC 2024 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Command: batch (size=1), took: 50ms, client=105.209.231.27
(MySQLIntegrityConstraintViolationException) com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '36903ed1580f04a23.446198297E12' for key 'StopCheck_Table.PRIMARY'
(EofException) org.eclipse.jetty.io.EofException: Closed
Command: , took: 43ms, client=105.209.231.27

aeric · Aug 10, 2024

Setlodi said:
(MySQLIntegrityConstraintViolationException) com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '36903ed1580f04a23.446198297E12' for key 'StopCheck_Table.PRIMARY'

This seems you are trying to insert a new row that conflict with existing primary key.

Setlodi · Aug 10, 2024

Sorted, Thanks aeric!!

Android Question Saving Resultset values to remote MySQL

Setlodi

Member

aeric

aeric

Expert

Setlodi

Member

aeric

Expert

Setlodi

Member

aeric

Expert

Setlodi

Member

aeric

Expert

Setlodi

Member

Similar Threads