Android Question Cursor in cursor loop

fasilosman · Jul 24, 2014

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

01.

B4X:

    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.

B4X:

    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 · Jul 24, 2014

never use *

your stuff could be simplified to

B4X:

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 · Jul 24, 2014

sorex said:
never use *

your stuff could be simplified to

B4X:

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

B4X:

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 · Jul 24, 2014

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 · Jul 25, 2014

Thank you very much

Android Question Cursor in cursor loop

fasilosman

Active Member

sorex

Expert

fasilosman

Active Member

sorex

Expert

fasilosman

Active Member

Similar Threads

Android Question Cursor in cursor loop

fasilosman

Active Member

sorex

Expert

fasilosman

Active Member

sorex

Expert

fasilosman

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency