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

    OliverA Well-Known Member 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
    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 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
            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
     

    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 Well-Known Member 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?
     
Loading...
  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