Android Question JdbcSQL - Insert error

fasilosman

Active Member
Licensed User
Hi all,

I used sample code to connect JdbcSQL from the link JdbcSQL forum link

The ExecQueryAsync works find. But when I use ExecNonQuery it returns and error.

Code and error message as follows.

B4X:
Sub ListAnimals As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Log("conn suces")
        Try
             mysql.ExecNonQuery("Insert Into Sector (SecID, SecName) Values (50,'Test') " )
            Log("NonQuery: " & Success)

        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

java.sql.SQLException: Could not retrieve transation read-only status server

I checked the server setting, Inser and Update command are allowed and I checked with VB.net program.

Can any one help me in this

Thanks
 

fasilosman

Active Member
Licensed User
If it is a remote server then you should only use the async methods.


Yes. My connection code below and I use "DisableStrictMode".
B4X:
Sub Connect As ResumableSub
    mysql.InitializeAsync("mysql", driver, jdbcUrl, Username, Password)
    Wait For MySQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

Also I tested "ExecQueryAsync" as below. But it says
(SQLException) java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
B4X:
Sub ListAnimals As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Log("conn suces")
        Try
            Dim sf As Object = mysql.ExecQueryAsync("mysql", "Insert Into Sector (SecID, SecName) Values (?,?) ",Array(10,"New sectore") )
            Wait For (sf) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            If Success Then
                Log("Query: " & Success)
            Else
                Log(LastException)
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

Please note that Also I tried "AddNonQueryToBatch". It also failed. It says
(SQLException) java.sql.SQLException: Unknown system variable 'tx_read_only'
B4X:
Sub ListAnimals As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Log("conn suces")
        Try
    'Dim sf As Object = mysql.ExecQueryAsync("mysql", "Insert Into Sector (SecID, SecName) Values (?,?) ",Array(10,"New sectore") )
    mysql.AddNonQueryToBatch("Insert Into Sector (SecID, SecName) Values (?,?) ",Array(10,"New sectore"))
    Dim SenderFilter As Object = mysql.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
   
    If Success = True Then
        Log("Success")
        Else
            Log(LastException)
    End If
   
    Catch
            Success = False
            Log(LastException)
    End Try
        CloseConnection
    End If
End Sub

Do you have any idea to solve this please.
 
Last edited:
Upvote 0

fasilosman

Active Member
Licensed User
That's expected.

Check this: https://stackoverflow.com/questions/16515249/mysql-unknown-system-variable-tx-read-only
Which database are you using and which Jdbc driver?

Thank you for the lead. Now it is working.

Server Details (I think it doesn't matter) - MySQL version 8.0.1 Running in Percona server.
Jdbc driver/connector Used (This matters) = AdditionalJar: mysql-connector-java-5.1.34-bin
They say that It has a bug of " 'tx_read_only' and they provide an update of "mysql-connector-java-5.1.44-bin"
Checked the reference here https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-44.html
So I used updated mysql connector. Then it works.

The "mysql-connector-java-5.1.44-bin" connector jar file is https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.44.tar.gz
Hope this will help others too.

Thank you
 
Upvote 0
Top