Other B4XTable : Load data from cursor

[incendio]

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

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

Private Cur As Cursor
Private S as SQL
Cur = S.ExecQuery("select * from COUNTY")
B4XTable1.SetDataFromCursor(Cur)

 

[Erel]

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).

 

[incendio]

What mistakes?

Never use DBUtils cause it is slow when handle large data.

 

[LucaMs]

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.

 

[Erel]

Never use DBUtils cause it is slow when handle large data.

You are making an unfounded claim. Please start a new thread and provide more information...

What mistakes?

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.

 

[Erel]

Example: https://www.b4x.com/android/forum/threads/b4x-b4xtable-load-data-from-sql-database.102520/

 

[incendio]

1. B4XTable is a cross platform library. Your code will only work in B4A.

I forgot about that, but i think it could modify to work for all platform

4. The original code is asynchronous. Why make it synchronous? It will cause the UI to freeze.

There are situations that users needs to see whole data before making actions.

5. You are not checking the columns types. You will lose information if you treat all values as strings.

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.

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.

That's true, SQL table should supply the correct order to B4XTable, for ex

select field1, field2 from table

or

select field2, field1 from table

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

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