Android Question B4XTable why 1 to 2 out of 0?

RB Smissaert · Feb 27, 2019

Doing some testing with B4XTable and run this code (altered from the posted example):

B4X:

Sub Activity_Create(FirstTime As Boolean)
 
 Dim i As Int
 Dim cursor1 As Cursor
 Dim data As List
 
 IME.Initialize("ime")
 IME.AddHeightChangedEvent
 Activity.LoadLayout("1")
 
 'create the columns
 B4XTable1.AddColumn("US County", B4XTable1.COLUMN_TYPE_NUMBERS)
 B4XTable1.AddColumn("Name", B4XTable1.COLUMN_TYPE_TEXT)
 B4XTable1.AddColumn("State", B4XTable1.COLUMN_TYPE_TEXT)
 B4XTable1.AddColumn("Interesting Number", B4XTable1.COLUMN_TYPE_NUMBERS)
 
 'create an empty B4XTable
 data.Initialize
 B4XTable1.SetData(data)
 
 B4XTable1.sql1.ExecNonQuery("create table test(id integer, name text, surname text, id2 integer)")
 B4XTable1.sql1.ExecNonQuery("insert into test values(2, 'c', 'd', 0.234)")

 B4XTable1.sql1.ExecNonQuery("insert into data values(1, 'a', 'b', 0.123)")
 B4XTable1.sql1.ExecNonQuery("insert into data select * from test")
 B4XTable1.sql1.ExecNonQuery("update data set c1 = 'abc' where rowid = 1")
 B4XTable1.CreateDataView("1")
 
 cursor1 = B4XTable1.sql1.ExecQuery("select sql from sqlite_master")
 
 'CREATE TABLE data (c0 INTEGER,c1 TEXT,c2 TEXT,c3 INTEGER)
 '---------------------------------------------------------
 For i = 0 To cursor1.RowCount - 1
  cursor1.Position = i
  Log(cursor1.GetString2(0))
 Next
 
 B4XTable1.Refresh

End Sub

It all runs fine but below the search text box I get 1 to 2 out of 0.
Why not 1 to 2 out of 2?
How to fix this?

RBS

Erel · Feb 27, 2019

What are you trying to do? This is not the expected way to work with B4XTable.

RB Smissaert · Feb 27, 2019

I am just testing the workings/capabilities of B4XTable and possibly change from the flexible table class to B4XTable.
What is the bit in my code that you think is not expected to happen?

RBS

Mahares · Feb 27, 2019

This is the simplest way I use it with an existing database as an example:.

B4X:

Dim Cursor1 As Cursor   
    Cursor1=Starter.SQL1.ExecQuery2(MyQuery,Array As String .......)
    If Cursor1.RowCount>0 Then
        Dim Data As List
        Data.Initialize
        Dim MyColumnCount As Int = Cursor1.ColumnCount
        Dim Col_Name(MyColumnCount) As String
        For i=0 To MyColumnCount-1
            Col_Name(i)=Cursor1.GetColumnName(i)
        Next
        dim NameColumn(MyColumnCount) as B4XTableColumn
        For i=0 To MyColumnCount -1 
            'you can create DATE and NUMBERS for the table def.
            NameColumn(i) = B4XTable1.AddColumn(Col_Name(i), B4XTable1.COLUMN_TYPE_TEXT)
        Next

        For i=0 To Cursor1.RowCount-1
            Cursor1.Position=i
            Dim row(MyColumnCount) As Object
            For j=0 To MyColumnCount -1
                row(j)= Cursor1.getstring(Col_Name(j))
            Next
            Data.Add(row)
        Next
        Cursor1.Close
        B4XTable1.SetData(Data)
    Else
        ToastMessageShow("No records for ...",False)
        Activity.Finish       
    End If
    Cursor1.Close

Erel · Feb 27, 2019

RB Smissaert said:
What is the bit in my code that you think is not expected to happen?

You need to create a List with the records and set it with a call to SetData.

RB Smissaert · Feb 27, 2019

Mahares said:

This is the simplest way I use it with an existing database as an example:.

B4X:

Dim Cursor1 As Cursor  
    Cursor1=Starter.SQL1.ExecQuery2(MyQuery,Array As String .......)
    If Cursor1.RowCount>0 Then
        Dim Data As List
        Data.Initialize
        Dim MyColumnCount As Int = Cursor1.ColumnCount
        Dim Col_Name(MyColumnCount) As String
        For i=0 To MyColumnCount-1
            Col_Name(i)=Cursor1.GetColumnName(i)
        Next
        dim NameColumn(MyColumnCount) as B4XTableColumn
        For i=0 To MyColumnCount -1
            'you can create DATE and NUMBERS for the table def.
            NameColumn(i) = B4XTable1.AddColumn(Col_Name(i), B4XTable1.COLUMN_TYPE_TEXT)
        Next

        For i=0 To Cursor1.RowCount-1
            Cursor1.Position=i
            Dim row(MyColumnCount) As Object
            For j=0 To MyColumnCount -1
                row(j)= Cursor1.getstring(Col_Name(j))
            Next
            Data.Add(row)
        Next
        Cursor1.Close
        B4XTable1.SetData(Data)
    Else
        ToastMessageShow("No records for ...",False)
        Activity.Finish      
    End If
    Cursor1.Close

Yes, I know I could move the data via a cursor, but why would I do that if I could do the same
via direct SQL? B4XTable has .sql1.Exec... so why not use it?
It would be nice if B4XTable could be used as a class (similar to the flexible table) so that we could
see what is going on and edit if needed.

RBS

Erel · Feb 27, 2019

RB Smissaert said:
Yes, I know I could move the data via a cursor, but why would I do that if I could do the same
via direct SQL? B4XTable has .sql1.Exec... so why not use it?

You are of course free to do whatever you like. However don't assume that everything will just work. Changing the internal data structure is possible but requires YOU to first understand how it works.

RB Smissaert said:
It would be nice if B4XTable could be used as a class (similar to the flexible table) so that we could
see what is going on and edit if needed.

This is already the case: https://www.b4x.com/android/forum/threads/100383/#content

Tip: never use Cursor. Only ResultSet. Not more complicated and cross platform.

RB Smissaert · Feb 27, 2019

Erel said:
You are of course free to do whatever you like. However don't assume that everything will just work. Changing the internal data structure is possible but requires YOU to first understand how it works.

This is already the case: https://www.b4x.com/android/forum/threads/100383/#content

Tip: never use Cursor. Only ResultSet. Not more complicated and cross platform.

Thanks, will have a look at the class.
Yes, I am aware of ResultSet and intend to move to that.

RBS

RB Smissaert · Feb 27, 2019

Erel said:
You are of course free to do whatever you like. However don't assume that everything will just work. Changing the internal data structure is possible but requires YOU to first understand how it works.

This is already the case: https://www.b4x.com/android/forum/threads/100383/#content

Tip: never use Cursor. Only ResultSet. Not more complicated and cross platform.

> This is already the case: https://www.b4x.com/android/forum/threads/100383/#content

Couldn't find any B4XTable class in that thread.

RBS

DonManfred · Feb 27, 2019

RB Smissaert said:
Couldn't find any B4XTable class in that thread.

No. But the info that a b4xlib DOES contains the Source.
In your case the class of the b4xtable is inside the b4xtable.b4xlib (rename it to b4xtable.b4xlib.zip and ectract the class if you want)

RB Smissaert · Feb 27, 2019

DonManfred said:
No. But the info that a b4xlib DOES contains the Source.
In your case the class of the b4xtable is inside the b4xtable.b4xlib (rename it to b4xtable.b4xlib.zip and ectract the class if you want)

OK, thanks, overlooked that and will extract the class.

RBS

RB Smissaert · Feb 27, 2019

Erel said:
You are of course free to do whatever you like. However don't assume that everything will just work. Changing the internal data structure is possible but requires YOU to first understand how it works.

This is already the case: https://www.b4x.com/android/forum/threads/100383/#content

Tip: never use Cursor. Only ResultSet. Not more complicated and cross platform.

With only 2 small changes to the class this works all fine now:

B4X:

'Filters the data. Call ClearDataView to remove the filter. WhereClause can be an empty string now.
Public Sub CreateDataView (WhereClause As String)
 sql1.ExecNonQuery("DROP VIEW IF EXISTS TempView")
 If WhereClause.Length > 0 Then
  sql1.ExecNonQuery("CREATE VIEW TempView AS SELECT *, rowid FROM data WHERE " & WhereClause)
 Else
  sql1.ExecNonQuery("CREATE VIEW TempView AS SELECT *, rowid FROM data") '<<<<< added
 End If
 CountAll = sql1.ExecQuerySingleResult("SELECT count(*) FROM TempView")
 Log("CreateDataView, CountAll: " & CountAll & ", WhereClause: " & WhereClause)
 mFirstRowIndex = 0
 SQLTableName = "TempView"
 UpdateData
End Sub

Public Sub SetData (Data As List)
 If sql1.IsInitialized Then sql1.Close
 #if B4J
 sql1.InitializeSQLite("", ":memory:", True)
 #Else If B4A OR B4I
 sql1.Initialize("", ":memory:", True)
 #End If
 CreateTable 'creates the table called data
 If Data.Size = 0 Then Return '<<<< added
 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(")")
 For Each row() As Object In Data
  sql1.AddNonQueryToBatch(sb.ToString, row)
 Next
 Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL1")
' Dim n As Long = DateTime.Now
 Wait For (SenderFilter) SQL1_NonQueryComplete (Success As Boolean)
' Log($"Set data time: ${DateTime.Now - n}ms"$)
 CountAll = Data.Size
 Log("SetData, CountAll: " & CountAll)
 Refresh2 (True)
End Sub

One further thing I will add is an option to alter the data when it is moved from the view to the display.
This is useful for example for dates, which I currently have as Excel dates. So the view will have integer values
(nice for sorting) but the display will have for example dd/mm/yyyy. Not sure now if the can be done with
B4XFormatter.

RBS

Android Question B4XTable why 1 to 2 out of 0?

RB Smissaert

Well-Known Member

Erel

B4X founder

RB Smissaert

Well-Known Member

Mahares

Expert

Erel

B4X founder

RB Smissaert

Well-Known Member

Erel

B4X founder

RB Smissaert

Well-Known Member

RB Smissaert

Well-Known Member

DonManfred

Expert

RB Smissaert

Well-Known Member

RB Smissaert

Well-Known Member

Similar Threads