Android Question Saving Resultset values to remote MySQL

S

Setlodi

Member
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?
 
Solution
Setlodi said:
(MySQLIntegrityConstraintViolationException) com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '36903ed1580f04a23.446198297E12' for key 'StopCheck_Table.PRIMARY'
Click to expand...
This seems you are trying to insert a new row that conflict with existing primary key.
Sort by date Sort by votes

aeric

Expert
Licensed User
Longtime User
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)
 
Last edited:
Upvote 1
S

Setlodi

Member
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 (?,?,?);
 
Upvote 0
S

Setlodi

Member
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
Android Question Resultset
Replies
11
Views
583
Guenter Becker
  • Article
B4J Library [B4X] [B4XLib] SD_SQL (direct access to MySQL, MariaDB, MS SQL, FireBird)
2 3 4
Replies
64
Views
22K
Star-Dust
  • Locked
  • Article
B4A Library JdbcSQL - Directly connect to remote databases
2
Replies
23
Views
64K
Erel
  • Locked
  • Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
138
Views
143K
Erel
  • Locked
  • Article
Android Tutorial Remote Database Connector (RDC) - Connect to any remote DB
20 21 22
Replies
423
Views
315K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…