B4J Question SQL error reading from MySql time column

[rbirago]

I have to read a MySql table where there is a Time column. As usual I get data from cursor using GetString and then I translate for myself the fields in the proper type...but getting the time field there is an Sql error in the GetString statement (Sql Bad format for Time).
The Mysql server I connect is ver. 5.6.28 and the connector I use is mysql-connector-java-5.1.35-bin.
I Tried to use a newer connector (mysql-connector-java-8.0.11), but B4j refuses db connection.
Any suggestion?
thank you
Roberto

 

[OliverA]

1) Post some code
2) Post the actual error message
3) Post your table definition
4) Do all Time values in the DB produce an error message or only some (such as Null)?

 

[rbirago]

I've noticed that the error is when I ttry to read a time fields with decimals.
this is the code

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: mysql-connector-java-5.1.35-bin
'    #AdditionalJar: mysql-connector-java-8.0.11
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Public Sql1 As SQL
    Private RS As ResultSet
    Public settings As Map
    Private btnGetRk1 As Button
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    settings = File.ReadMap(File.DirApp, "settings.txt")
    Dim JdbcUrl As String = settings.Get("JdbcUrl")
    Dim driverClass As String = settings.Get("DriverClass")
    Dim dbuser As String = settings.Get("DBUser")
    Dim dbpassword As String = settings.Get("DBPassword")
    Try
        Sql1.Initialize2(driverClass, JdbcUrl, dbuser, dbpassword)
    Catch
        Log("errore connessione DB")
    End Try
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub btnGetRk1_Click
    Dim lstMap As List
    Dim cur As ResultSet
    cur = Sql1.ExecQuery("select * from timefields")
    Do While cur.nextrow
        Dim wMap As Map
        wMap.Initialize
        For i = 0 To cur.ColumnCount - 1
            Dim wColName As String = cur.GetColumnName(i)
            Dim wValue As String = cur.GetString2(i)
            wMap.Put(cur.GetColumnName(i).ToLowerCase, wValue)
        Next
        lstMap.Add(wMap)
    Loop
    cur.Close
End Sub

and the screenshots of data, table structure and log error.
The error is triggered reading the column Time1

 

[DonManfred]

Try to format the date coming from the database

SELECT *, DATE_FORMAT(date,'%d/%m/%Y') AS niceDate
FROM table

 

[OliverA]

The underlying Java Time class object cannot handle decimal seconds. Therefore, as per @DonManfred, format the value properly in SQL itself so JDBC can handle it. If you want to keep the fractional seconds, convert the information (via SQL) into something like a long/float
Links:
https://bugs.mysql.com/bug.php?id=70892
https://docs.oracle.com/javase/6/docs/api/java/sql/Time.html

 

[rbirago]

I see. I think that the better way is to change the MySql db field from Time(3) to DateTime(3). Don't you agree?