How do I insert the data from the SQL db into a table view?

[keram108]

Hi,
 
I have about 700 text lines in a SQL database and want to display them in a scrollable table view. I managed to get the SQL database working but do not know how to properly include the code for the table view.

The code for the databse is:

[lua]

require(“sqlite3”)

local path = system.pathForFile(“mq.sqlite”, system.DocumentsDirectory )
file = io.open( path, “r” )
   if( file == nil )then           
                         
       pathSource     = system.pathForFile( “mq.sqlite”, system.ResourceDirectory )  
       fileSource = io.open( pathSource, “rb” )
       contentsSource = fileSource:read( “*a” )                                  
                                  
        pathDest = system.pathForFile( “mq.sqlite”, system.DocumentsDirectory )                 
        fileDest = io.open( pathDest, “wb” )                 
        fileDest:write( contentsSource )                 
                
        io.close( fileSource )        
        io.close( fileDest )         
   end   
       
db = sqlite3.open( path )

local function onSystemEvent( event )
    if( event.type == “applicationExit”) then
        db:close()
    end
end

print ("version "…sqlite3.version())
print ("db path "…path)

local count =0
local sql = “SELECT * FROM allmq LIMIT 700”
for row in db:nrows(sql) do
    count = count +1
    local text = row.quote
    local q = display.newText(text, 20, 20 +(20 * count), native.systemFont, 18)
    q.anchorX = 0
    q.anchorY = 0
    q:setFillColor(255,255,255)
end

Runtime:addEventListener (“system”, onSystemEvent)
[/lua]

How do I combine the above code with the code for the table view so that the text is wrapping and scrollable?

keram

[keram108]

OK, so I added the following code to what I posted above:

[lua]-- Insert 640 rows
for i = 1, 640 do

    local isCategory = false
    local rowHeight = 36
    local rowColor = { default={ 1, 1, 1 }, over={ 1, 0.5, 0, 0.2 } }
    local lineColor = { 0.5, 0.5, 0.5 }

    – Insert a row into the tableView
    tableView:insertRow(
        {
            isCategory = isCategory,
            rowHeight = rowHeight,
            rowColor = rowColor,
            lineColor = lineColor,
            params = {}  – Include custom data in the row
        }
    )
end

local widget = require( “widget” )

– Create the widget
local tableView = widget.newTableView
{
    left = 10,
    top = 20,
    height = 430,
    width = 320,
    onRowRender = onRowRender,
    onRowTouch = onRowTouch,
    listener = scrollListener
}
 

[/lua]

but I’m getting an error for this line:   tableView:insertRow

and what should I put into this line:             params = {}  – Include custom data in the row  ??

I’ll appreciate any help with this.

Thanks.

keram

[rob]

You have a “scope” problem.  When you try to access tableView on line 8, tableView doesn’t exist.  move that whole loop below where you create the tableView.

Rob

[ksan]

Also row painting needs to go into an onRowRender() function as specified in tableView constructor. Best suggestion is to look at Corona. Labs sample apps. For example Widget Demo . Look at the tab with tableView and see how the row data is loaded.

[keram108]

Thanks, Rob and ksan,

I corrected the “scope” problem and now at least something is visible on the simulator screen - the table is there but the text lines are below and underneath it.

@ksan

Thanks for that tip - that’s the best way to learn, looking at the existing code. I assume that in the example the data is loaded with these sections:

[lua]

        – Insert the row into the tableView
        tableView:insertRow
        {
            isCategory = isCategory,
            rowHeight = rowHeight,
            rowColor = rowColor,
            lineColor = lineColor,
        }
    end

[/lua]

and

[lua]

    – Handle row rendering
    local function onRowRender( event )
        local phase = event.phase
        local row = event.row
        – in graphics 2.0, the group contentWidth / contentHeight are initially 0, and expand once elements are inserted into the group.
        – in order to use contentHeight properly, we cache the variable before inserting objects into the group

        local groupContentHeight = row.contentHeight

        local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

        – in Graphics 2.0, the row.x is the center of the row, no longer the top left.
        rowTitle.x = LEFT_PADDING

        – we also set the anchorX of the text to 0, so the object is x-anchored at the left
        rowTitle.anchorX = 0

        print(rowTitle.x)
        rowTitle.y = groupContentHeight * 0.5
        rowTitle:setFillColor( 0, 0, 0 )
    end
    
    – Handle row updates
    local function onRowUpdate( event )
        local phase = event.phase
        local row = event.row
        
        – print( row.index, “: is now onscreen” )
    end

[/lua]

which I added to my code now.

so my whole code is now:

[lua]

local widget = require( “widget” )
require(“sqlite3”)

local path = system.pathForFile(“mq.sqlite”, system.DocumentsDirectory )
file = io.open( path, “r” )
   if( file == nil )then           
                         
       pathSource     = system.pathForFile( “mq.sqlite”, system.ResourceDirectory )  
       fileSource = io.open( pathSource, “rb” )
       contentsSource = fileSource:read( “*a” )                                  
                                  
        pathDest = system.pathForFile( “mq.sqlite”, system.DocumentsDirectory )                 
        fileDest = io.open( pathDest, “wb” )                 
        fileDest:write( contentsSource )                 
                   
        io.close( fileSource )        
        io.close( fileDest )         
   end   
       
db = sqlite3.open( path )

local function onSystemEvent( event )
    if( event.type == “applicationExit”) then
        db:close()
    end
end

print ("version "…sqlite3.version())
print ("db path "…path)

local count =0
local sql = “SELECT * FROM allmq LIMIT 700”
for row in db:nrows(sql) do
    count = count +1
    local text = row.quote
    local q = display.newText(text, 40, 40 +(20 * count), native.systemFont, 18)
    q.anchorX = 0
    q.anchorY = 0
    q:setFillColor(255/255,120/255,50/255)
end

Runtime:addEventListener (“system”, onSystemEvent)

local widget = require( “widget” )

– Create the widget
local tableView = widget.newTableView
{
    left = 10,
    top = 20,
    height = 430,
    width = 320,
    onRowRender = onRowRender,
    onRowTouch = onRowTouch,
    listener = scrollListener
}

– Insert 640 rows
for i = 1, 640 do

    local isCategory = false
    local rowHeight = 36
    local rowColor = { default={ 1, 1, 1 }, over={ 1, 0.5, 0, 0.2 } }
    local lineColor = { 0.5, 0.5, 0.5 }

   – Insert a row into the tableView
    tableView:insertRow(
        {
            isCategory = isCategory,
            rowHeight = rowHeight,
            rowColor = rowColor,
            lineColor = lineColor,
        }
)
end

    – Handle row rendering
    local function onRowRender( event )
        local phase = event.phase
        local row = event.row
        – in graphics 2.0, the group contentWidth / contentHeight are initially 0, and expand once elements are inserted into the group.
        – in order to use contentHeight properly, we cache the variable before inserting objects into the group

        local groupContentHeight = row.contentHeight

        local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

        – in Graphics 2.0, the row.x is the center of the row, no longer the top left.
        rowTitle.x = LEFT_PADDING

        – we also set the anchorX of the text to 0, so the object is x-anchored at the left
        rowTitle.anchorX = 0

        print(rowTitle.x)
        rowTitle.y = groupContentHeight * 0.5
        rowTitle:setFillColor( 0, 0, 0 )
    end
    
    – Handle row updates
    local function onRowUpdate( event )
        local phase = event.phase
        local row = event.row
        
        – print( row.index, “: is now onscreen” )
    end
[/lua]

But still I’m getting only what is in the image above.

I don’t know what has to be changed in this line though:    local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

What am I missing?

keram

[rob]

What’s the code for your onRowRender() function?

[keram108]

@rob

What’s the code for your onRowRender() function?

I added it above (probably after you have posted your reply). See lines 66-88 above.

It must be something that picks up the text lines from the SQL database but I don’t know how to code it, could not find any example using SQL database…

It’s probably this line: local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

Any ideas how to do it? Thanks.

keram

[ksan]

Try this. I cleaned up your code a little and moved some into functions to ease reading. Main thing is how you need to load data into a lua table from SQLite (see loadData function) and then refer to records in that table using index. Didn’t run the code below as I didn’t have your db but it should be more or less in the right place. Please fix & tweak as needed. Hope this helps. 

local widget = require( "widget" ) require("sqlite3") local path = system.pathForFile("mq.sqlite", system.DocumentsDirectory ) local myData = {} -- a table to contain the data loaded from SQLite local function setupDB() local function copyDBtoDocDir() file = io.open( path, "r" ) if( file == nil )then pathSource = system.pathForFile( "mq.sqlite", system.ResourceDirectory ) fileSource = io.open( pathSource, "rb" ) contentsSource = fileSource:read( "\*a" ) pathDest = system.pathForFile( "mq.sqlite", system.DocumentsDirectory ) fileDest = io.open( pathDest, "wb" ) fileDest:write( contentsSource ) io.close( fileSource ) io.close( fileDest ) end end copyDBtoDocDir() db = sqlite3.open( path ) local function onSystemEvent( event ) if( event.type == "applicationExit") then db:close() end end print ("version "..sqlite3.version()) print ("db path "..path) Runtime:addEventListener ("system", onSystemEvent) end local function loadData() local count = 0 local sql = "SELECT \* FROM allmq LIMIT 700" local row for row in db:nrows(sql) do count = count + 1 myData[count] = row.quote print(myData[count]) -- lets see if the data is coming in from SQLite end end setupDB() loadData() -- Handle row rendering local function onRowRender( event ) local phase = event.phase local row = event.row -- in graphics 2.0, the group contentWidth / contentHeight are initially 0, and expand once elements are inserted into the group. -- in order to use contentHeight properly, we cache the variable before inserting objects into the group local groupContentHeight = row.contentHeight local rowTitle = display.newText( row, myData[row.index], 0, 0, nil, 14 ) -- in Graphics 2.0, the row.x is the center of the row, no longer the top left. rowTitle.x = 10 -- we also set the anchorX of the text to 0, so the object is x-anchored at the left rowTitle.anchorX = 0 print(rowTitle.x) rowTitle.y = groupContentHeight \* 0.5 rowTitle:setFillColor(255/255,120/255,50/255) end -- Create the widget local tableView = widget.newTableView { left = 10, top = 20, height = 430, width = 320, onRowRender = onRowRender, --onRowTouch = onRowTouch, --listener = scrollListener } -- Insert rows for i = 1, #myData do local isCategory = false local rowHeight = 36 local rowColor = { default={ 1, 1, 1 }, over={ 1, 0.5, 0, 0.2 } } local lineColor = { 0.5, 0.5, 0.5 } -- Insert a row into the tableView tableView:insertRow( { isCategory = isCategory, rowHeight = rowHeight, rowColor = rowColor, lineColor = lineColor, } ) end

[keram108]

Thanks a lot, ksan!

It works Ok now :)  and it loads fast.

@ksan

… Main thing is how you need to load data into a lua table from SQLite (see loadData function) and then refer to records in that table using index. Didn’t run the code below as I didn’t have your db…

I atached the db… can be useful for others to see and test.

Yes, I need to fix and tweak little bit to get the text lines wrapping etc. Will try to do it myself. If I don’t manage I’ll come back with questions.

[ksan]

Great to hear. Good luck with your app! 

[keram108]

Hello ksan,

I had a closer look at the code above that you cleaned up and corrected and would like to get some clarification.

In the tableView widget you commented out these lines:

–onRowTouch = onRowTouch,
–listener = scrollListener

and still the scrolling is working OK.

I had a look at the Corona Labs\Corona SDK\Sample Code\Interface\ScrollView example and in it there is a whole section to create a scroller which includes the listener:

[lua]

– Create a ScrollView
local scrollView = widget.newScrollView
{
    left = 0,
    top = 0,
    width = display.contentWidth,
    height = display.contentHeight,
    bottomPadding = 50,
    id = “onBottom”,
    horizontalScrollDisabled = true,
    verticalScrollDisabled = false,
    listener = scrollListener,
}

[/lua]

Does that mean that the scrolling is already included in the tableView widget and there is no need for any extra code to create scrolling?

Trying to learn as much as possible…

Thanks.

keram

[ksan]

Both tableView and scrollView know how to scroll on their own. You do not need additional code for scrolling. The reason there is an optional listener for scrolling is to cater for cases where you may need to do additional things to support your app design. Please read the API page for tableView. The onRowTouch and listener are marked Optional with a good explanation of what you use them for. 

The onRowTouch for example is demonstrated in the Widget Demo sample code. You use it to detect a touch in one of your rows and then branch off to a detail view screen etc. If you don’t need to do anything when your rows are touched then you don’t need to include the listener for it. 

Hope this helps.

[keram108]

OK, so I added the following code to what I posted above:

[lua]-- Insert 640 rows
for i = 1, 640 do

    local isCategory = false
    local rowHeight = 36
    local rowColor = { default={ 1, 1, 1 }, over={ 1, 0.5, 0, 0.2 } }
    local lineColor = { 0.5, 0.5, 0.5 }

    – Insert a row into the tableView
    tableView:insertRow(
        {
            isCategory = isCategory,
            rowHeight = rowHeight,
            rowColor = rowColor,
            lineColor = lineColor,
            params = {}  – Include custom data in the row
        }
    )
end

local widget = require( “widget” )

– Create the widget
local tableView = widget.newTableView
{
    left = 10,
    top = 20,
    height = 430,
    width = 320,
    onRowRender = onRowRender,
    onRowTouch = onRowTouch,
    listener = scrollListener
}
 

[/lua]

but I’m getting an error for this line:   tableView:insertRow

and what should I put into this line:             params = {}  – Include custom data in the row  ??

I’ll appreciate any help with this.

Thanks.

keram

[rob]

You have a “scope” problem.  When you try to access tableView on line 8, tableView doesn’t exist.  move that whole loop below where you create the tableView.

Rob

[ksan]

Also row painting needs to go into an onRowRender() function as specified in tableView constructor. Best suggestion is to look at Corona. Labs sample apps. For example Widget Demo . Look at the tab with tableView and see how the row data is loaded.

[keram108]

Thanks, Rob and ksan,

I corrected the “scope” problem and now at least something is visible on the simulator screen - the table is there but the text lines are below and underneath it.

@ksan

Thanks for that tip - that’s the best way to learn, looking at the existing code. I assume that in the example the data is loaded with these sections:

[lua]

        – Insert the row into the tableView
        tableView:insertRow
        {
            isCategory = isCategory,
            rowHeight = rowHeight,
            rowColor = rowColor,
            lineColor = lineColor,
        }
    end

[/lua]

and

[lua]

    – Handle row rendering
    local function onRowRender( event )
        local phase = event.phase
        local row = event.row
        – in graphics 2.0, the group contentWidth / contentHeight are initially 0, and expand once elements are inserted into the group.
        – in order to use contentHeight properly, we cache the variable before inserting objects into the group

        local groupContentHeight = row.contentHeight

        local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

        – in Graphics 2.0, the row.x is the center of the row, no longer the top left.
        rowTitle.x = LEFT_PADDING

        – we also set the anchorX of the text to 0, so the object is x-anchored at the left
        rowTitle.anchorX = 0

        print(rowTitle.x)
        rowTitle.y = groupContentHeight * 0.5
        rowTitle:setFillColor( 0, 0, 0 )
    end
    
    – Handle row updates
    local function onRowUpdate( event )
        local phase = event.phase
        local row = event.row
        
        – print( row.index, “: is now onscreen” )
    end

[/lua]

which I added to my code now.

so my whole code is now:

[lua]

local widget = require( “widget” )
require(“sqlite3”)

local path = system.pathForFile(“mq.sqlite”, system.DocumentsDirectory )
file = io.open( path, “r” )
   if( file == nil )then           
                         
       pathSource     = system.pathForFile( “mq.sqlite”, system.ResourceDirectory )  
       fileSource = io.open( pathSource, “rb” )
       contentsSource = fileSource:read( “*a” )                                  
                                  
        pathDest = system.pathForFile( “mq.sqlite”, system.DocumentsDirectory )                 
        fileDest = io.open( pathDest, “wb” )                 
        fileDest:write( contentsSource )                 
                   
        io.close( fileSource )        
        io.close( fileDest )         
   end   
       
db = sqlite3.open( path )

local function onSystemEvent( event )
    if( event.type == “applicationExit”) then
        db:close()
    end
end

print ("version "…sqlite3.version())
print ("db path "…path)

local count =0
local sql = “SELECT * FROM allmq LIMIT 700”
for row in db:nrows(sql) do
    count = count +1
    local text = row.quote
    local q = display.newText(text, 40, 40 +(20 * count), native.systemFont, 18)
    q.anchorX = 0
    q.anchorY = 0
    q:setFillColor(255/255,120/255,50/255)
end

Runtime:addEventListener (“system”, onSystemEvent)

local widget = require( “widget” )

– Create the widget
local tableView = widget.newTableView
{
    left = 10,
    top = 20,
    height = 430,
    width = 320,
    onRowRender = onRowRender,
    onRowTouch = onRowTouch,
    listener = scrollListener
}

– Insert 640 rows
for i = 1, 640 do

    local isCategory = false
    local rowHeight = 36
    local rowColor = { default={ 1, 1, 1 }, over={ 1, 0.5, 0, 0.2 } }
    local lineColor = { 0.5, 0.5, 0.5 }

   – Insert a row into the tableView
    tableView:insertRow(
        {
            isCategory = isCategory,
            rowHeight = rowHeight,
            rowColor = rowColor,
            lineColor = lineColor,
        }
)
end

    – Handle row rendering
    local function onRowRender( event )
        local phase = event.phase
        local row = event.row
        – in graphics 2.0, the group contentWidth / contentHeight are initially 0, and expand once elements are inserted into the group.
        – in order to use contentHeight properly, we cache the variable before inserting objects into the group

        local groupContentHeight = row.contentHeight

        local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

        – in Graphics 2.0, the row.x is the center of the row, no longer the top left.
        rowTitle.x = LEFT_PADDING

        – we also set the anchorX of the text to 0, so the object is x-anchored at the left
        rowTitle.anchorX = 0

        print(rowTitle.x)
        rowTitle.y = groupContentHeight * 0.5
        rowTitle:setFillColor( 0, 0, 0 )
    end
    
    – Handle row updates
    local function onRowUpdate( event )
        local phase = event.phase
        local row = event.row
        
        – print( row.index, “: is now onscreen” )
    end
[/lua]

But still I’m getting only what is in the image above.

I don’t know what has to be changed in this line though:    local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

What am I missing?

keram

[rob]

What’s the code for your onRowRender() function?

[keram108]

@rob

What’s the code for your onRowRender() function?

I added it above (probably after you have posted your reply). See lines 66-88 above.

It must be something that picks up the text lines from the SQL database but I don’t know how to code it, could not find any example using SQL database…

It’s probably this line: local rowTitle = display.newText( row, "Row " … row.index, 0, 0, nil, 14 )

Any ideas how to do it? Thanks.

keram

[ksan]

Try this. I cleaned up your code a little and moved some into functions to ease reading. Main thing is how you need to load data into a lua table from SQLite (see loadData function) and then refer to records in that table using index. Didn’t run the code below as I didn’t have your db but it should be more or less in the right place. Please fix & tweak as needed. Hope this helps. 

local widget = require( "widget" ) require("sqlite3") local path = system.pathForFile("mq.sqlite", system.DocumentsDirectory ) local myData = {} -- a table to contain the data loaded from SQLite local function setupDB() local function copyDBtoDocDir() file = io.open( path, "r" ) if( file == nil )then pathSource = system.pathForFile( "mq.sqlite", system.ResourceDirectory ) fileSource = io.open( pathSource, "rb" ) contentsSource = fileSource:read( "\*a" ) pathDest = system.pathForFile( "mq.sqlite", system.DocumentsDirectory ) fileDest = io.open( pathDest, "wb" ) fileDest:write( contentsSource ) io.close( fileSource ) io.close( fileDest ) end end copyDBtoDocDir() db = sqlite3.open( path ) local function onSystemEvent( event ) if( event.type == "applicationExit") then db:close() end end print ("version "..sqlite3.version()) print ("db path "..path) Runtime:addEventListener ("system", onSystemEvent) end local function loadData() local count = 0 local sql = "SELECT \* FROM allmq LIMIT 700" local row for row in db:nrows(sql) do count = count + 1 myData[count] = row.quote print(myData[count]) -- lets see if the data is coming in from SQLite end end setupDB() loadData() -- Handle row rendering local function onRowRender( event ) local phase = event.phase local row = event.row -- in graphics 2.0, the group contentWidth / contentHeight are initially 0, and expand once elements are inserted into the group. -- in order to use contentHeight properly, we cache the variable before inserting objects into the group local groupContentHeight = row.contentHeight local rowTitle = display.newText( row, myData[row.index], 0, 0, nil, 14 ) -- in Graphics 2.0, the row.x is the center of the row, no longer the top left. rowTitle.x = 10 -- we also set the anchorX of the text to 0, so the object is x-anchored at the left rowTitle.anchorX = 0 print(rowTitle.x) rowTitle.y = groupContentHeight \* 0.5 rowTitle:setFillColor(255/255,120/255,50/255) end -- Create the widget local tableView = widget.newTableView { left = 10, top = 20, height = 430, width = 320, onRowRender = onRowRender, --onRowTouch = onRowTouch, --listener = scrollListener } -- Insert rows for i = 1, #myData do local isCategory = false local rowHeight = 36 local rowColor = { default={ 1, 1, 1 }, over={ 1, 0.5, 0, 0.2 } } local lineColor = { 0.5, 0.5, 0.5 } -- Insert a row into the tableView tableView:insertRow( { isCategory = isCategory, rowHeight = rowHeight, rowColor = rowColor, lineColor = lineColor, } ) end

[keram108]

Thanks a lot, ksan!

It works Ok now :)  and it loads fast.

@ksan

… Main thing is how you need to load data into a lua table from SQLite (see loadData function) and then refer to records in that table using index. Didn’t run the code below as I didn’t have your db…

I atached the db… can be useful for others to see and test.

Yes, I need to fix and tweak little bit to get the text lines wrapping etc. Will try to do it myself. If I don’t manage I’ll come back with questions.