Android Question Get number of records / rows

Declan

Well-Known Member
Licensed User
Longtime User
I am using the following code, which is taken from the RDC and DBUtils tutorial, to populate a local SQLite table:
B4X:
Sub JobDone(Job As HttpJob)
    If Job.Success = False Then
        Log("Error: " & Job.ErrorMessage)
    Else
        If Job.JobName = "DBRequest" Then
            Dim result As DBResult = reqManager.HandleJob(Job)
            reqManager.PrintTable(result)
        End If
' These are the records from remote MySQL database
        If Job.tag = "prodlisting" Then
            Dim result As DBResult = reqManager.HandleJob(Job)
            ProgressDialogHide
            reqManager.PrintTable(result)
Dim ListOfMaps As List
    ListOfMaps.Initialize
'    For i = 1 To 40
        Dim m As Map
        m.Initialize
        m.Put("Id", "id")
        m.Put("Description", "description")
        m.Put("Barcode", "barcode")
        m.Put("Category", "category")
        m.Put("Volume", "volume")
        m.Put("GetMass", "getmass")
        m.Put("Supplier", "supplier")
        ListOfMaps.Add(m)
'    Next
    DBUtils.InsertMaps(SQL, "prodlisting", ListOfMaps)
        End If
    End If
    Job.Release
End Sub

This runs and I do not get any errors.
BUT, the local SQLite table "prodlisting" has no records.
I suspect my problem is with the For / Next statement:
I need to replace "For i = 1 To 40" with For i = 1 To NumberOfRecords
How do I read the number of records obtained in (result)?

The log is:
B4X:
LogCat connected to: B4A-Bridge: alps M7L
--------- beginning of main
Installing file.
PackageAdded: package:b4a.example
** Service (starter) Create **
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
~w:1004,main,44
DropTable: DROP TABLE IF EXISTS [prodlisting]
CreateTable: CREATE TABLE IF NOT EXISTS [prodlisting] ([Description] TEXT, [Barcode] TEXT, [Category] TEXT, [Volume] TEXT, [GetMass] TEXT, [Supplier] TEXT)
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
HandleJob: 16
Tag: prodlisting, Columns: 7, Rows: 12
id    description    barcode    category    volume    getmass    supplier  
1    Bells Extra Special    5000387003019    Whisky    750    1    Brandhouse  
2    J & B Rare    5010103800303    Whisky    750    1    Brandhouse  
3    Johnnie Walker Red    5000267014005    Whisky    750    1    Brandhouse  
4    Johnnie Walker Black    5000267024011    Whisky    750    1    Brandhouse  
5    Bushmills Original    5010109317070    Whisky    750    1    Brandhouse  
6    Smirnoff 1818    6001398119545    Vodka    750    1    Brandhouse  
7    Gilbeys    6001398101021    Gin    750    1    Brandhouse  
8    Bertrams VO    6001398100970    Brandy    750    1    Brandhouse  
9    Jose Cuervo Gold    7501035042148    Tequila    750    1    Brandhouse  
10    Captain Morgan Black Lable    6001398122026    Rum    750    1    Brandhouse  
11    Smirnoff Spin     6001398622373    Ready-to-Drink    300    0    Brandhouse  
12    Windhoek Lager    6001760005506    Beer    330    0    Brandhouse  
InsertMaps (first query out of 1): INSERT INTO [prodlisting] ([Description], [Barcode], [Category], [Volume], [GetMass], [Supplier]) VALUES (?, ?, ?, ?, ?, ?)
ExecuteMap: SELECT description FROM prodlisting WHERE Barcode = ?
No records found.
** Activity (main) Pause, UserClosed = true **
** Service (starter) Destroy **
 

Declan

Well-Known Member
Licensed User
Longtime User
Cool, thanks Erel,
I have changed the code to:
B4X:
Sub JobDone(Job As HttpJob)
    If Job.Success = False Then
        Log("Error: " & Job.ErrorMessage)
    Else
'        If Job.JobName = "DBRequest" Then
'            Dim result As DBResult = reqManager.HandleJob(Job)
'            reqManager.PrintTable(result)
'        End If
        If Job.tag = "prodlisting" Then
            Dim result As DBResult = reqManager.HandleJob(Job)
            ProgressDialogHide
            reqManager.PrintTable(result)
            Dim ListOfMaps As List
                ListOfMaps.Initialize
            For Each row() As Object In result.Rows
            ListOfMaps.Add(CreateMap("description": row(0), "barcode": row(1), "category": row(2), "volume": row(3), "getmass": row(4), "supplier": row(5)))
            DBUtils.InsertMaps(SQL, "prodlisting", ListOfMaps)
            Next
        End If
    End If
    Job.Release
End Sub
and the log is:
B4X:
** Service (starter) Create **
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
DropTable: DROP TABLE IF EXISTS [prodlisting]
CreateTable: CREATE TABLE IF NOT EXISTS [prodlisting] ([Description] TEXT, [Barcode] TEXT, [Category] TEXT, [Volume] TEXT, [GetMass] TEXT, [Supplier] TEXT)
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
HandleJob: 19
Tag: prodlisting, Columns: 7, Rows: 12
id    description    barcode    category    volume    getmass    supplier   
1    Bells Extra Special    5000387003019    Whisky    750    1    Brandhouse   
2    J & B Rare    5010103800303    Whisky    750    1    Brandhouse   
3    Johnnie Walker Red    5000267014005    Whisky    750    1    Brandhouse   
4    Johnnie Walker Black    5000267024011    Whisky    750    1    Brandhouse   
5    Bushmills Original    5010109317070    Whisky    750    1    Brandhouse   
6    Smirnoff 1818    6001398119545    Vodka    750    1    Brandhouse   
7    Gilbeys    6001398101021    Gin    750    1    Brandhouse   
8    Bertrams VO    6001398100970    Brandy    750    1    Brandhouse   
9    Jose Cuervo Gold    7501035042148    Tequila    750    1    Brandhouse   
10    Captain Morgan Black Lable    6001398122026    Rum    750    1    Brandhouse   
11    Smirnoff Spin     6001398622373    Ready-to-Drink    300    0    Brandhouse   
12    Windhoek Lager    6001760005506    Beer    330    0    Brandhouse   
InsertMaps (first query out of 1): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 2): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 3): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 4): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 5): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 6): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 7): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 8): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 9): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 10): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 11): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 12): INSERT INTO [prodlisting] ([description], [barcode], [category], [volume], [getmass], [supplier]) VALUES (?, ?, ?, ?, ?, ?)
ExecuteMap: SELECT * FROM prodlisting WHERE Barcode = ?
No records found.
** Activity (main) Pause, UserClosed = true **
** Service (starter) Destroy **

Now I read the SQLite database with:
B4X:
    Dim QueryBarcode As String
    lblTakeStockbarcode.Text = "5000387003019"
    QueryBarcode = lblTakeStockbarcode.Text
    lblTakeStockTip.Text = "PLACE PRODUCT ON SCALE"
    GetProductInfo(QueryBarcode)
and:
B4X:
Sub GetProductInfo(Value As String)
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL,"SELECT * FROM prodlisting WHERE Barcode = ?", Array As String(Value))
    If m = Null Or m.IsInitialized = False Then
        lblTakeStockProduct.Text = ""
    Else
        lblTakeStockProduct.Text = m.Get("description")
    End If
End Sub
But, as can be seen from the log, I get:
ExecuteMap: SELECT * FROM prodlisting WHERE Barcode = ?
No records found.

I have checked and rechecked the syntax of the above, but cannot seem to get to grips with the problem.
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…