Android Question JdbcSQL - Insert error

[fasilosman]

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.

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

 

[Erel]

If it is a remote server then you should only use the async methods.

 

[fasilosman]

If it is a remote server then you should only use the async methods.

Yes. My connection code below and I use "DisableStrictMode".

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().

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'

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.

 

[Erel]

Also I tested "ExecQueryAsync" as below. But it says

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?

 

[fasilosman]

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