Other B4XTable : Load data from cursor

incendio

incendio

Well-Known Member
Licensed User
Longtime User
From this thread :
https://b4x.com/android/forum/threa...custom-navigation-buttons.102362/#post-642555

I add Sub to load data from cursor.

These are the steps :
1) Open file B4XTable.b4xlib with 7Zip (or Winrar/Winzip)
2) Modify file B4XTable.Bas, add this code
B4X: 
Public Sub SetDataFromCursor (Data As Cursor)    
    If sql1.IsInitialized Then sql1.Close
    #if B4J
    sql1.InitializeSQLite("", ":memory:", True)
    #Else If B4A OR B4I
    sql1.Initialize("", ":memory:", True)
    #End If
    
    CreateTable
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("INSERT INTO data VALUES (")
    For i = 0 To Columns.Size - 1
        sb.Append("?,")
    Next
    sb.Remove(sb.Length - 1, sb.Length)
    sb.Append(")")
    
    Private ColVal As List
    
    sql1.BeginTransaction
    For x = 0 To Data.RowCount - 1
        Data.position = x
        ColVal.Initialize
        
        For i = 0 To Columns.Size - 1
            ColVal.Add(Data.GetString2(i))
        Next
        
        sql1.ExecNonQuery2(sb.ToString, ColVal)
    Next
    sql1.TransactionSuccessful
    
    CountAll = Data.RowCount
    Refresh2 (True)
End Sub

Sample of use
B4X: 
Private Cur As Cursor
Private S as SQL
Cur = S.ExecQuery("select * from COUNTY")
B4XTable1.SetDataFromCursor(Cur)
 
Sort by date Sort by votes
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
Moved to the questions forum.

There are several mistakes in this code. There is also no need to modify the library code. You should instead read the data and set it with SetData. You can use DBUtils.ExecuteMemoryTable to read the data (though it doesn't preserve the data types so it is better to read the data yourself).
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Developing a class that can read a DB table and convert it to the format required by b4xTable, list or arrays (and vice versa, when there will be the possibility to edit the b4xTable cells), would be the correct thing to do.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
incendio said:
Never use DBUtils cause it is slow when handle large data.
Click to expand...
You are making an unfounded claim. Please start a new thread and provide more information...

incendio said:
What mistakes?
Click to expand...
1. B4XTable is a cross platform library. Your code will only work in B4A.
2. It is a mistake to suggest to edit the code unless there isn't any other possible solution. In this case there is a very simple solution. The edit will need to be done every time that the library will be updated.
3. It is a mistake to duplicate existing code.
4. The original code is asynchronous. Why make it synchronous? It will cause the UI to freeze.
5. You are not checking the columns types. You will lose information if you treat all values as strings.
6. There is a hidden assumption here that the column order in the SQL table is the same as the order of columns added to B4XTable.

I will post a small example that shows how to read data from a SQL table and add it to a B4XTable.
 
Upvote 0

incendio

Well-Known Member
Licensed User
Longtime User
Erel said:
1. B4XTable is a cross platform library. Your code will only work in B4A.
Click to expand...
I forgot about that, but i think it could modify to work for all platform

Erel said:
4. The original code is asynchronous. Why make it synchronous? It will cause the UI to freeze.
Click to expand...
There are situations that users needs to see whole data before making actions.

Erel said:
5. You are not checking the columns types. You will lose information if you treat all values as strings.
Click to expand...
Table's need columns type, for formatting, alignment, etc, but developers (at least for me), don't need it. Return value as a string from a column is enough, It can be converted to desired type if needed.

Erel said:
6. There is a hidden assumption here that the column order in the SQL table is the same as the order of columns added to B4XTable.
Click to expand...
That's true, SQL table should supply the correct order to B4XTable, for ex
B4X: 
select field1, field2 from table
or
B4X: 
select field2, field1 from table

Erel said:
I will post a small example that shows how to read data from a SQL table and add it to a B4XTable.
Click to expand...
I read your example, it is good to have another option, but my application usually contains about 20-30 tables.
Using your example, I have to write a lines of codes to converts sql result into a list for every table.
I think, i prefer my solution, only 1 line code needed for every table, I am a lazy writter
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
B4A Library [B4X] B4XTableSelections - extended selection modes for B4XTable
2
Replies
21
Views
17K
aeric
  • Locked
  • Article
Android Tutorial [B4X] Cross platform Editable B4XTable + Form Example
Replies
9
Views
21K
Erel
  • Article
Android Tutorial [B4X] B4XTable - Load data from SQL database
Replies
4
Views
24K
Erel
  • Article
Android Tutorial [B4X] B4XTable - Multiple rows selection
Replies
0
Views
11K
Erel
  • Locked
  • Article
Android Tutorial [B4X] B4XTable with custom cells layout
Replies
4
Views
24K
William Lancee