B4J Question Problem with Float field in db

Giacomo

Giacomo

Active Member
Licensed User
Longtime User
Ciao
I have to read a sqlite db table and insert it into a tableview.
B4X: 
Sub Table_01_SelectedRowChanged(Index As Int, Row() As Object)
    Row= Table_01.SelectedRowValues
    Log(Row(3))
    Dim Querry As String
    
    Querry= "Select    [main].[WS_HEADER].[WS], "
    Querry=Querry & "[main].[WS_HEADER].[CUSTOMER_CODE], "
    Querry=Querry & "[main].[CUSTOMERS].[CUSTOMER_NAME_1], "
    Querry=Querry & "[main].[CUSTOMERS].[CUSTOMER_NAME_2], "
    Querry=Querry & "[main].[CUSTOMERS].[CITY],"
    
    Querry=Querry & "[main].[WS_HEADER].[AGENT_CODE], "
    Querry=Querry & "[main].[WS_HEADER].[AGENT_DESCR] "
    Querry=Querry & "FROM   [main].[WS_HEADER] "
    Querry=Querry & "INNER JOIN [main].[CUSTOMERS] ON [main].[WS_HEADER].[CUSTOMER_CODE] = [main].[CUSTOMERS].[CUSTOMER_CODE] "
    Querry=Querry & "WHERE  [main].[WS_HEADER].[WS_YEAR] = 2019 "
    Querry=Querry & "And [main].[WS_HEADER].[AGENT_CODE] = '"&Row(3)&"';"
    
    DBUtils.ExecuteTableView(SQL1,Querry,Null,0,Table_02)
            
    Log(Table_02.Items.Size)
End Sub
the problem is in reading the [WS] field( is Type FLOAT ), the value entered in the DB has no decimal
Sqlite.jpg

Displayed on tableView, 1 unwanted decimal appears
My_result.jpg


Unfortunately I can't change the structure of the db.
Can you help me ?
 
Sort by date Sort by votes
R

roerGarcia

Active Member
Licensed User
Longtime User
dbutils: 
Public Sub ExecuteTableView(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, _
    TableView1 As TableView)
    TableView1.Items.Clear
    Dim cur As ResultSet
    If StringArgs = Null Then
        Dim StringArgs(0) As String
    End If
    cur = SQL.ExecQuery2(Query, StringArgs)
    Dim cols As List
    cols.Initialize
    For i = 0 To cur.ColumnCount - 1
        cols.Add(cur.GetColumnName(i))
    Next
    TableView1.SetColumns(cols)
    Do While cur.NextRow
        Dim values(cur.ColumnCount) As String
        For col = 0 To cur.ColumnCount - 1
            values(col) = cur.GetString2(col)
        Next
        TableView1.Items.Add(values)
        If Limit > 0 AND TableView1.Items.Size >= Limit Then Exit
    Loop
    cur.Close
End Sub

dbutil json: 
Public Sub ExecuteJSON (SQL As SQL, Query As String, StringArgs() As String, Limit As Int, DBTypes As List) As Map
    Dim table As List
    Dim cur As Cursor
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteJSON: " & Query)
    Dim table As List
    table.Initialize
    If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
    For row = 0 To Limit - 1
        cur.Position = row
        Dim m As Map
        m.Initialize
        For i = 0 To cur.ColumnCount - 1
            Select DBTypes.Get(i)
                Case DB_TEXT
                    m.Put(cur.GetColumnName(i), cur.GetString2(i))
                Case DB_INTEGER
                    m.Put(cur.GetColumnName(i), cur.GetLong2(i))
                Case DB_REAL
                    m.Put(cur.GetColumnName(i), cur.GetDouble2(i))
                Case Else
                    Log("Invalid type: " & DBTypes.Get(i))
            End Select
        Next

May be help you to view this.

I added an array of desired data types when calling dbutils .

Another try:

sql cast: 
Select CAST(MyColumn AS Varchar(10)) AS MyColumn
FROM MyTable
 
Last edited:
Upvote 0
Giacomo

Giacomo

Active Member
Licensed User
Longtime User
roerGarcia said:
dbutils: 
Public Sub ExecuteTableView(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, _
    TableView1 As TableView)
    TableView1.Items.Clear
    Dim cur As ResultSet
    If StringArgs = Null Then
        Dim StringArgs(0) As String
    End If
    cur = SQL.ExecQuery2(Query, StringArgs)
    Dim cols As List
    cols.Initialize
    For i = 0 To cur.ColumnCount - 1
        cols.Add(cur.GetColumnName(i))
    Next
    TableView1.SetColumns(cols)
    Do While cur.NextRow
        Dim values(cur.ColumnCount) As String
        For col = 0 To cur.ColumnCount - 1
            values(col) = cur.GetString2(col)
        Next
        TableView1.Items.Add(values)
        If Limit > 0 AND TableView1.Items.Size >= Limit Then Exit
    Loop
    cur.Close
End Sub

dbutil json: 
Public Sub ExecuteJSON (SQL As SQL, Query As String, StringArgs() As String, Limit As Int, DBTypes As List) As Map
    Dim table As List
    Dim cur As Cursor
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteJSON: " & Query)
    Dim table As List
    table.Initialize
    If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
    For row = 0 To Limit - 1
        cur.Position = row
        Dim m As Map
        m.Initialize
        For i = 0 To cur.ColumnCount - 1
            Select DBTypes.Get(i)
                Case DB_TEXT
                    m.Put(cur.GetColumnName(i), cur.GetString2(i))
                Case DB_INTEGER
                    m.Put(cur.GetColumnName(i), cur.GetLong2(i))
                Case DB_REAL
                    m.Put(cur.GetColumnName(i), cur.GetDouble2(i))
                Case Else
                    Log("Invalid type: " & DBTypes.Get(i))
            End Select
        Next

May be help you to view this.

I added an array of desired data types when calling dbutils .

Another try:

sql cast: 
Select CAST(MyColumn AS Varchar(10)) AS MyColumn
FROM MyTable
Click to expand...
Ok with JSon
but
how I fill the DBType list if I don't know the structure of the DB before ?

sorry for my English :confused:
 
Upvote 0
R

roerGarcia

Active Member
Licensed User
Longtime User
è possibile aggiungere un array e modificare il codice di "dbutil" per restituire il tipo di dati richiesto o convertirlo da "dbutil"
 
Upvote 0
drgottjr

drgottjr

Expert
Licensed User
Longtime User
per sapere la struttura della db, fai:
B4X: 
ExecQuery("SELECT sql FROM sqlite_master WHERE name = 'nome_della_tabella'")

il risultato è una stringa (imagine in allegato)
 

Attachments

  • db.png
    db.png
    9.4 KB · Views: 185
Upvote 0
R

roerGarcia

Active Member
Licensed User
Longtime User
Questo non aiuterà. Ciò di cui hai bisogno è cambiare il tipo di dati restituiti in "dbutil" o modificare il valore per eccezione nel codice "dbutil" in modo che sia intero
 
Upvote 0
Giacomo

Giacomo

Active Member
Licensed User
Longtime User
For me english is very difficult (google translator ? I'm italian)
I think it is better if we write in English so that our considerations can serve others

I did so ... (on the advice of roerGarcia

)
I knew the first field was numeric so ...
B4X: 
    'Tipi di campi
    Tipo_campi.Initialize
    Tipo_campi.Clear
    Tipo_campi.Add(DBUtils_Aste.DB_INTEGER)
    For i =1 To 6
        Tipo_campi.Add(DBUtils_Aste.DB_TEXT)
    Next
    '#################
    'DBUtil EDIT
    '#################
    DBUtils_Aste.ExecuteTableView(SQL1,Querry,Null,0,Table_02,Tipo_campi)
DBUtil edited
B4X: 
Public Sub ExecuteTableView(SQL As SQL, Query As String, StringArgs() As String, Limit As Int, _
    TableView1 As TableView ,Campi As List)'EDIT
    TableView1.Items.Clear
    Dim cur As ResultSet
    If StringArgs = Null Then
        Dim StringArgs(0) As String
    End If
    cur = SQL.ExecQuery2(Query, StringArgs)
    Dim cols As List
    cols.Initialize
    For i = 0 To cur.ColumnCount - 1
        cols.Add(cur.GetColumnName(i))
    Next
    TableView1.SetColumns(cols)
    Do While cur.NextRow
        Dim values(cur.ColumnCount) As String
        For col = 0 To cur.ColumnCount - 1
            '#################
            'DBUtil EDIT
            '#################
            'EDIT made by me to insert field types
           
            Select Campi.Get(col)'EDIT
                Case DB_TEXT'EDIT
                    values(col) = cur.GetString2(col)
                Case DB_INTEGER'EDIT
                    values(col) = cur.GetLong2(col)'EDIT
                Case DB_REAL'EDIT
                    values(col) = cur.GetDouble2(col)'EDIT
                   
            End Select'EDIT      
        Next
        TableView1.Items.Add(values)
        If Limit > 0 And TableView1.Items.Size >= Limit Then Exit
    Loop
    cur.Close
End Sub

ExecQuery("SELECT sql FROM sqlite_master WHERE name = 'nome_della_tabella'")
Click to expand...
drgottjr

this is a great idea.
I still need to edit DBUtil to be able to send field types at Sub ExecuteTableView
 
Upvote 0
drgottjr

drgottjr

Expert
Licensed User
Longtime User
ha detto giacomo:
I knew the first field was numeric so ...
Click to expand...

were you able to change the first field? did it work? if yes, then you can change all the other fields in the same way. find the structure of the db and change the fields as needed.
 
Upvote 0
Giacomo

Giacomo

Active Member
Licensed User
Longtime User
drgottjr said:
ha detto giacomo:


were you able to change the first field? did it work? if yes, then you can change all the other fields in the same way. find the structure of the db and change the fields as needed.
Click to expand...
In reality the data comes from a querry that selects the data from 2 or more tables
only the first field is numeric
however it now works correctly ?
 
Upvote 0
Harris

Harris

Expert
Licensed User
Longtime User

Would you recommend SQLite?

Well, I intend to rewrite a small POS system for B4J. It will be used by small restaurants, so I don't expect a lot of competing readings and recordings, or a lot of data, and it will be used on a local network. My intention is to use SQLITE, to facilitate the installation, but I never used this...
www.b4x.com www.b4x.com

seems sqlite doesn't support numeric with decimal?
That seems odd since I use decimal on my devices all the time... with no issue.
 
Upvote 0
udg

udg

Expert
Licensed User
Longtime User
Rad here about Affinity .
"When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT. "
 
Upvote 0
Giacomo

Giacomo

Active Member
Licensed User
Longtime User
udg said:
Rad here about Affinity .
"When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT. "
Click to expand...
OK of course
my number is an integer (col 1, row 1, 314223)
in the DB the field type is 'Float'
because in the tableView it is transformed into 314223.0) ?
 
Upvote 0
udg

udg

Expert
Licensed User
Longtime User
Hi Giacomo,
help me understand it better . You have an integer value (314223) and save it in a type REAL data field on the SqLite DB; then reading it back to pass it to TableView you get it as a decimal number (314223.0)? Why this surprises you?
On the other hand, if the DB column is NUMERIC, the preceding post would suggest saving as Integer. But how is declared the column for the TableView?

Sorry if I missed any previous hint, but I'm travelling and just read your last post.
 
Upvote 0
Giacomo

Giacomo

Active Member
Licensed User
Longtime User
udg said:
But how is declared the column for the TableView?
Click to expand...
This is the point :)
is it possible to declare the type for each column in the tabeview?
How you do it ?
Do you have the patience to explain it to me with an example??
Sorry I'm a beginner
 
Upvote 0
Jorge M A

Jorge M A

Well-Known Member
Licensed User
Longtime User
Giacomo said:
1 unwanted decimal appears
Click to expand...

If this is just the problem, I don't see any reason to complicate the solution.
You know the field name [WS] and its type in the database [REAL], as well as the representation you want [INT], so why not just modify the first line of your query?

B4X: 
Querry= "Select   CAST( [main].[WS_HEADER].[WS] AS INT ) AS WS, " ...
 
Upvote 0
LucaMs

LucaMs

Expert
Licensed User
Longtime User
Jorge M A said:
If this is just the problem,
Click to expand...
Did anyone understand exactly the problem? ?

(first post)

1581868064142.png


I undestand so:
1) db field WS is declared as Float but contains only Integers
2) when he, using DBUtils, fills the TableView, one digit is shown!

Trying to help you... I "worked too much" testing B4XTable.
Take a look at the project I'm attaching; I fear it is not perfect (mainly the B4X column types / SQLite column types mapping).


[The project can be a good basis for managing a generic table whose contents are unknown.]
 

Attachments

  • DBDecimals.zip
    4.4 KB · Views: 187
Last edited:
Upvote 0
You must log in or register to reply here.
Top