Android Question Cursor in cursor loop

[fasilosman]

what are the different between these two when handling large Sqlite database

01.

    Cursor1 = SQL1.ExecQuery("Select * from Invoice" )

    For i = 0 To Cursor1.RowCount - 1
    Cursor1.Position = i

    Cursor2.SQL1.ExecQuery("Select * from Product where ProductID= " & Cursor1.GetString("ProductID"))
    Cursor2.Position = 0
   
    Cursor3 = SQL1.ExecQuery("Select * from PDARetailer where RetID = " & Cursor1.GetString("RetailerID") )
    Cursor3.Position = 0
 
    AddRow(Array As String(Cursor1.GetString("InvoiceID") , Cursor2.GetString("ProductName"),  Cursor2.GetString("RetailerName")))
 
    Next
    End If

    Cursor1.Close
    Cursor2.Close
    Cursor3.Close

02.

    Cursor1 = SQL1.ExecQuery("Select * from Invoice" )

    For i = 0 To Cursor1.RowCount - 1
    Cursor1.Position = i

    Cursor2.SQL1.ExecQuery("Select * from Product where ProductID= " & Cursor1.GetString("ProductID"))
    Cursor2.Position = 0
   
    Cursor3 = SQL1.ExecQuery("Select * from PDARetailer where RetID = " & Cursor1.GetString("RetailerID") )
    Cursor3.Position = 0
 
    AddRow(Array As String(Cursor1.GetString("InvoiceID") , Cursor2.GetString("ProductName"),  Cursor2.GetString("RetailerName")))
 
    Cursor2.Close
    Cursor3.Close

    Next
    End If

    Cursor1.Close

Can any body help me to improve my code

 

[sorex]

never use *

your stuff could be simplified to

Cursor1 = SQL1.ExecQuery("Select i.InvoiceID,p.ProductName from Invoice as i join Product as p on i.ProductID=p.ProductID join PDARetailer as pda on i.RetailerID=pda.RetID" )

For i = 0 To Cursor1.RowCount - 1
AddRow(Array As String(Cursor1.GetString("InvoiceID") , Cursor2.GetString("ProductName"),
Next

not sure why you need the pda table tho, you don't seem to use any value of it.

 

[fasilosman]

never use *

your stuff could be simplified to

Cursor1 = SQL1.ExecQuery("Select i.InvoiceID,p.ProductName from Invoice as i join Product as p on i.ProductID=p.ProductID join PDARetailer as pda on i.RetailerID=pda.RetID" )

For i = 0 To Cursor1.RowCount - 1
AddRow(Array As String(Cursor1.GetString("InvoiceID") , Cursor2.GetString("ProductName"),
Next

not sure why you need the pda table tho, you don't seem to use any value of it.

Thanks for the reply. actually I was mistaken ,the code should be like this to use the PDARetailer

AddRow(Array As String(Cursor1.GetString("InvoiceID") , Cursor2.GetString("ProductName"),  Cursor3.GetString("RetailerName")))

Actually I want to know what is the use of Cursor.close and when can i use it

 

[sorex]

I think it is just the position in the returned recordset, close closes the recordset connection (I think).

A joined query is faster than nested lookups so you only need 1 cursor and 1 close when everything is added to your listview.

 

[fasilosman]

Thank you very much