B4J Question SQL error reading from MySql time column

Discussion in 'B4J Questions' started by rbirago, Apr 12, 2019.

  1. rbirago

    rbirago Member Licensed User

    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
  2. OliverA

    OliverA Expert Licensed User

    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)?
  3. rbirago

    rbirago Member Licensed User

    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")
            Sql1.Initialize2(driverClass, JdbcUrl, dbuser, dbpassword)
    Log("errore connessione DB")
    End Try
        MainForm = Form1
    "Layout1"'Load the layout file.
    End Sub

    'Return true to allow the default exceptions handler to handle the uncaught exception.
    Sub Application_Error (Error As Exception, StackTrace As StringAs 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
    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)
    End Sub
    and the screenshots of data, table structure and log error.
    The error is triggered reading the column Time1

    Attached Files:

  4. DonManfred

    DonManfred Expert Licensed User

    Try to format the date coming from the database

    José J. Aguilar and OliverA like this.
  5. OliverA

    OliverA Expert Licensed User

    José J. Aguilar likes this.
  6. rbirago

    rbirago Member Licensed User

    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?
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice