Sqlite database reference to text box

[MOULTC15A]

Hi,
An app I am designing right now focuses on financial management. As this involves inputting numbers, a database had to be used (in which I chose sqlite). However, I am new to this method of storing information and am struggling with inputting a value into the database. Currently, a text box which numericField is in the ‘income’ scene in which numbers can be typed in. Under this, there is a ‘done’ button. My aim is to get the number written in the text box to get put into the database when the done button is pressed. This is the code for my income scene currently:

local composer = require( "composer" )

local scene = composer.newScene()

-- -----------------------------------------------------------------------------------
-- Code outside of the scene event functions below will only be executed ONCE unless
-- the scene is removed entirely (not recycled) via "composer.removeScene()"
-- -----------------------------------------------------------------------------------

--Set up the SQLite library (the database for storing information)
local sqlite3 = require( "sqlite3" )

--Create a file path for the database file "data.db"
local path = system.pathForFile( "data.db", system.DocumentsDirectory )

--Open the database for access
local db = sqlite3.open( path )

--Creates the table for income, 'income' is the table name, 'IncomeValue' is the column
local incomeTableSetup = [[CREATE TABLE IF NOT EXISTS income ( IncomeValue INTEGER PRIMARY KEY autoincrement );]]
db:exec( incomeTableSetup )

--When the value is written in the text box, then the done button is pressed, this function runs
local function insertIncome()
	local insertQuery = [[INSERT INTO income VALUES ( NULL, "valuefromtextbox");]]
	db:exec( insertQuery )
end



--If the menu button is pressed
local function gotoMenu()
	composer.gotoScene( "menu" )
end




-------------------------
-- Scene event functions

-- create()
function scene:create(event)

	local sceneGroup = self.view


--Displaying objects/scene
display.setDefault( scenegroup, "background", 1, 1, 1)-- Making background white

local banner = display.newRect( sceneGroup, 0, 0, 400, 80 )--Setting a blue banner at the top
	banner:setFillColor(0, 0, 1)
	banner.x = display.contentCenterX
	banner.y = -80

local menuBackground = display.newRect( sceneGroup, 0, 0, 140, 40 )
	menuBackground:setFillColor( 0, 1, 0 )
	menuBackground.x = 100
	menuBackground.y = 550

local menuText = display.newText( sceneGroup, "Menu", 100, 550, native.systemFont, 28)
	menuText:setFillColor( 0, 0, 0 )

local incomeBanner = display.newText( sceneGroup, "Income", display.contentCenterX, -60, native.systemFont, 28)

local amountText = display.newText( sceneGroup, "Add Amount:", display.contentCenterX, 70, native.systemFont, 28 )
	amountText:setFillColor( 0, 0, 0 )

local doneBackground = display.newRect( sceneGroup, 0, 0, 100, 40)
	doneBackground:setFillColor( 0, 1, 0 )
	doneBackground.x = display.contentCenterX
	doneBackground.y = 220

local doneText = display.newText( sceneGroup, "Done", display.contentCenterX, 220, native.systemFont, 20 )
	doneText:setFillColor( 0, 0, 0 )

local numericField = native.newTextField( display.contentCenterX, 150, 220, 36, sceneGroup )
numericField.inputType = "number"

	menuBackground:addEventListener( "tap", gotoMenu )
	doneBackground:addEventListener( "tap", insertIncome )
end

--Listeners for buttons


--show()
function scene:show(event)

	local sceneGroup = self.view
	local phase = event.phase

	if (phase == "will") then

	elseif (phase == "did") then

	end
end


--hide()
function scene:hide(event)

	local sceneGroup = self.view
	local phase = event.phase

	if (phase == "will") then


	elseif( phase == "did") then
		composer.removeScene( "income")
	end
end


scene:addEventListener( "create", scene)
scene:addEventListener( "show", scene)
scene:addEventListener( "hide", scene)
scene:addEventListener( "destroy", scene)


return scene

Any help would be much appreciated, thanks!

[vb66r55]

Hi,

I followed the instructions in the article to rewrite the code for you, but I am not sure if this is what you want.

local function insertIncome(valuefromtextbox)
	local path = system.pathForFile( "data.db", system.DocumentsDirectory ) 
	local db = sqlite3.open( path )

	local tablesetup = [[CREATE TABLE IF NOT EXISTS income ( IncomeValue INTEGER PRIMARY KEY,autoincrement);]]
	db:exec( tablesetup )

	local insertQuery = [[INSERT INTO income VALUES (NULL, ']]..valuefromtextbox..[['); ]]
	db:exec( insertQuery )
		
	if ( db and db:isopen() ) then
		db:close()
		print("Close")
	end
	return true
end

I would suggest you to do this and wrap them in a function so that you can open or close the file correctly every time.

Please note that you must remember to pass the value of valuefromtextbox each time it is executed, and this function will add a row to the database each time it is executed.

If you want to change the information instead of adding it, let me know.

By the way, you should write an additional tap function in which insertIncome() is executed so that you can control whether the database is written.

[MOULTC15A]

Thanks for the assistance,
The code you have rewritten makes sense, the only issue I am having is with the execution of the value in the text box.
I re-named the text box as a variable “valuefromtextbox”, however when the function is executed an error appears saying “attempt to concentrate local ‘valuefromtextbox’ (a table value)”. I’m not sure if this is because it doesn’t know that the valuefromtextbox has a numerical value that is written?

[vb66r55]

Yes.

In db, each row stores a value or multiple values instead of tables.

Unless you can determine how many objects are in the table, you can write it like this ↓

valuefromtextbox[1]…[[’,’]]…valuefromtextbox[2]…[[’,’]]…valuefromtextbox[3]

Otherwise, when the quantity in the table cannot be determined, I would suggest you to set a loop to store the next value in the db.

[vb66r55]

By the way, it takes time to write db, and it is best not to add too many rows at once.

The best way is to limit the number in the valuefromtextbox table, for example 10, so you can pre-write 10 data in the way I did above.

Then because you cannot determine the quantity, just pre set all the data to a specific value, so you can ensure that each value in each table has data, but if you are storing numbers, use English characters a.b.c.d for the preset value to avoid confusion.

If you use the above method, remember
local tablesetup = [[CREATE TABLE IF NOT EXISTS income (IncomeValue INTEGER PRIMARY KEY,autoincrement);]]
Change to ↓
local tablesetup = [[CREATE TABLE IF NOT EXISTS income (IncomeValue INTEGER PRIMARY KEY,autoincrement1,autoincrement2,autoincrement3);]]
This format.

[MOULTC15A]

I have tried writing the insertQuery line with valuefromtextbox[1]…[[’,’]] etc. however the program still does not seem to understand what the valuefromtextbox is. Instead of saying “cannot concentrate local ‘valuefromtextbox’”, it says 'cannot concentrate field ‘?’.

I also changed the tablesetup to autoincrement 2 and 3 as well.

Sorry, due to my lack of knowledge on this area of lua I am struggling to grasp why the errors are occurring. I will continue to try to figure out a solution in the meantime.

[MOULTC15A]

If it makes it easier to understand, the basic solution (1st iteration) I am trying to achieve is a value written in the text box, then the done button is pressed. This should then insert the value in the text box into the db. I tried getting the program to print the value in the text box as well, but it seems that will not work until it understands what the valuefromtextbox is.

[vb66r55]

@MOULTC15A

Ok…
I don’t quite understand the narrative. It seems that you only save 1 value at a time, so why use Table.
But don’t worry, this is the test code I just wrote, and it has been run success. This should help you understand the operation of SQL.

local function TestSearchData()
		local path = system.pathForFile( "data.db", system.DocumentsDirectory ) 
		local db = sqlite3.open( path )
		
		local tablesetup = [[CREATE TABLE IF NOT EXISTS Income (IncomeValue INTEGER PRIMARY KEY,valuefromtextbox1,valuefromtextbox2);]]
		db:exec( tablesetup )
		
		for row in db:nrows("SELECT * FROM Income") do
			print(row.valuefromtextbox1)
			print(row.valuefromtextbox2)
		end
		
		if ( db and db:isopen() ) then
			db:close()
		end
	end
	local function insertIncome(valuefromtextbox)
		local path = system.pathForFile( "data.db", system.DocumentsDirectory ) 
		local db = sqlite3.open( path )

		local tablesetup = [[CREATE TABLE IF NOT EXISTS Income (IncomeValue INTEGER PRIMARY KEY,valuefromtextbox1,valuefromtextbox2);]]
		db:exec( tablesetup )
		
		local tablefill = [[INSERT INTO Income VALUES (NULL, ']]..valuefromtextbox[1]..[[',']]..valuefromtextbox[2]..[['); ]]
		db:exec( tablefill )	
			
		if ( db and db:isopen() ) then
			db:close()
			TestSearchData()
		end
		return true
	end
	
	local TextField = native.newTextField( 960, 540, 220, 22 )
	TextField.font = native.newFont( native.systemFontBold, 16 )
	TextField.text = ""
	TextField.placeholder = "Test。"
	TextField.hasBackground = false
	

	local function onTestText( event )
		if ( "began" == event.phase ) then
		elseif ( "submitted" == event.phase ) then
			native.setKeyboardFocus( nil )
			local TestTable = {"a","a"}
			TestTable[1] = event.target.text
			insertIncome(TestTable)
		end
	end
	TextField:addEventListener( "userInput", onTestText )

[MOULTC15A]

Thanks,
Yes at the current time i only plan for 1 value to be inputted at a time, I just needed the program to store the values entered as I will later require them to be extracted in a ‘summary’ menu which labels week, month and year income. Furthermore I will also have an expenses menu which will require a separate database (i’m assuming?) which will also be used to label day, month and year expenses as well as an overall profit amount.

Anyway, I have run the test code you wrote, and the console seems to be printing ‘nil’ every time a value is entered. I am assuming this was supposed to print the numbers/letters written in the text box?

[vb66r55]

It’s really strange, why does Nil appear?

I performed “English”, “Number”, and “UTF-8 string”, all of which can be stored in db, and can be found by checking in the sandbox.

Maybe you can post an error message.

Normally, you should see something similar to this picture on the console.

[MOULTC15A]

No actual error seems to pop up, however I know it is storing values in the console as every time another value is inputted, all the other values pop up as well, it’s just that they all say nil.

[MOULTC15A]

I’m not sure if it has something to do with my scenes, as I put your code into a separate console and it worked fine?

[vb66r55]

My code can be executed in main.lua or other places.
In fact, you can see so many nil in print, which means that the DB is running normally.

Therefore, I think you are giving nil as the value of insertIncome(valuefromtextbox).
You can try to do this.

local function insertIncome(valuefromtextbox)
print(valuefromtextbox[1],valuefromtextbox[2])
local path = system.pathForFile( “data.db”, system.DocumentsDirectory)

First try to check the value passed in, because what I wrote is only for testing, so it is good to define the passed value outside instead of in the function.

[MOULTC15A]

The nil value still seems to come up.
So printing the valuefromtextbox[1] and [2] before the rest of the function should print the value passed in?

Also, in the onTestText function, a line states setkeyboardfocus to nil, i’m assuming that means to just make the text box empty? This wouldn’t have anything to do with the issue?

[MOULTC15A]

Sorry, after a bit of messing around I think this has worked (kind of?), It seems now the values previously in the DB are still stated as ‘nil’, however, I tried inputting ‘test’ and it seems to have put this into the DB correctly, as this picture shows. How should I delete the values already in the DB?

[vb66r55]

Yes, you cannot pass nil, so db will only store nil.

local q = [[DELETE FROM Income WHERE valuefromtextbox1=]]…"’" … (You want to Delete String) … “’”…[[;]]
db:exec( q )

local q = [[DELETE FROM Income WHERE valuefromtextbox2=]]…"’" … (You want to Delete String) … “’”…[[;]]
db:exec( q )

This is the deletion method under normal circumstances. If you cannot delete nil, it is recommended that you delete the wrong file first, do the test again, and avoid the occurrence of nil again.

http://luasqlite.luaforge.net/lsqlite3.html

This is the official Lua guide, which should help you.

[MOULTC15A]

Yep, so I deleted all the values, and when I input ‘test’ it comes up in the console, however two ‘nil’ statements still appear under it. I didn’t think that 3 rows of values were printed when one was entered into the console anyway?

By the way, if there is an easier way of storing values that you think is more suited for my project, please tell me. I chose Sqlite because I knew i needed to store these values then extract them later on (add them together for example) and thought this wasn’t possible on a simpler db like JSON

[vb66r55]

Hi, on this point, since I can’t see your overall code, you can only check the value of print step by step and check why the value passed in is nil, which will help you.

SQL is very fast when extracting large amounts of multi-person data, and one can store multiple people’s information, but if you just need to store one person for each, then JS can do it.

[MOULTC15A]

Yeah I think SQL is better suited for my program as I plan on making further developments and will need to manipulate the information in the db.

This is how my overall code looks at the moment (just the income scene), if it helps, I can upload the whole file with all the scenes, but I think the problem is located somewhere in the income scene.

-----------------------------------------------------------------------------------------
--
-- Income.lua (Financial Manager App)
--
-----------------------------------------------------------------------------------------

local composer = require( "composer" )

local scene = composer.newScene()

-- -----------------------------------------------------------------------------------
-- Code outside of the scene event functions below will only be executed ONCE unless
-- the scene is removed entirely (not recycled) via "composer.removeScene()"
-- -----------------------------------------------------------------------------------

--Set up the SQLite library (the database for storing information)
local sqlite3 = require( "sqlite3" )

local path = system.pathForFile( "data.db", system.DocumentsDirectory )
local db = sqlite3.open( path )

local q = [[DELETE FROM Income;]]
db:exec( q )

local function TestSearchData()
	
	local tablesetup = [[CREATE TABLE IF NOT EXISTS Income (IncomeValue INTEGER PRIMARY KEY,valuefromtextbox1,valuefromtextbox2);]]
	db:exec( tablesetup )

	for row in db:nrows("SELECT * FROM Income") do
		print(row.valuefromtextbox1)
		print(row.valuefromtextbox2)
	end

	if ( db and db:isopen() ) then
		db:close()
	end
end


local function insertIncome(valuefromtextbox)
	print(valuefromtextbox[1],valuefromtextbox[2])

	local path = system.pathForFile( "data.db", system.DocumentsDirectory )
	local db = sqlite3.open( path )

	local tablesetup = [[CREATE TABLE IF NOT EXISTS Income (IncomeValue INTEGER PRIMARY KEY,valuefromtextbox1,valuefromtextbox2);]]
	db:exec( tablesetup )

	local tablefill = [[INSERT INTO Income VALUES (NULL, ']]..valuefromtextbox[1]..[[',']]..valuefromtextbox[2]..[['); ]]
	db:exec( tablefill )

	if ( db and db:isopen() ) then
		db:close()
		TestSearchData()
	end
	return true
end

local TextField = native.newTextField( display.contentCenterX, 180, 220, 22 )
TextField.font = native.newFont( native.systemFontBold, 14 )
TextField.text = ""
TextField.placeholder = "Test."



local function onTestText( event )
	if ( event.phase == "began" ) then
	elseif ( event.phase == "submitted" ) then
		native.setKeyboardFocus( nil )
		local TestTable = {"a", "a"}
		TestTable[1] = event.target.text
		insertIncome(TestTable)
	end
end

--If the menu button is pressed
local function gotoMenu()
	composer.gotoScene( "menu" )
end

-------------------------
-- Scene event functions

-- create()
function scene:create(event)

	local sceneGroup = self.view


--Displaying objects/scene
display.setDefault( scenegroup, "background", 1, 1, 1)-- Making background white

local banner = display.newRect( sceneGroup, 0, 0, 400, 80 )--Setting a blue banner at the top
	banner:setFillColor(0, 0, 1)
	banner.x = display.contentCenterX
	banner.y = -80

local menuBackground = display.newRect( sceneGroup, 0, 0, 140, 40 )
	menuBackground:setFillColor( 0, 1, 0 )
	menuBackground.x = 100
	menuBackground.y = 550

local menuText = display.newText( sceneGroup, "Menu", 100, 550, native.systemFont, 28)
	menuText:setFillColor( 0, 0, 0 )

local incomeBanner = display.newText( sceneGroup, "Income", display.contentCenterX, -60, native.systemFont, 28)

local amountText = display.newText( sceneGroup, "Add Amount:", display.contentCenterX, 70, native.systemFont, 28 )
	amountText:setFillColor( 0, 0, 0 )

local doneBackground = display.newRect( sceneGroup, 0, 0, 100, 40)
	doneBackground:setFillColor( 0, 1, 0 )
	doneBackground.x = display.contentCenterX
	doneBackground.y = 220

local doneText = display.newText( sceneGroup, "Done", display.contentCenterX, 220, native.systemFont, 20 )
	doneText:setFillColor( 0, 0, 0 )

	menuBackground:addEventListener( "tap", gotoMenu )
	TextField:addEventListener( "userInput", onTestText )
end




--show()
function scene:show(event)

	local sceneGroup = self.view
	local phase = event.phase

	if (phase == "will") then

	elseif (phase == "did") then

	end
end


--hide()
function scene:hide(event)

	local sceneGroup = self.view
	local phase = event.phase

	if (phase == "will") then


	elseif( phase == "did") then
		composer.removeScene( "income")
		display.remove( numericField )
	end
end


scene:addEventListener( "create", scene)
scene:addEventListener( "show", scene)
scene:addEventListener( "hide", scene)
scene:addEventListener( "destroy", scene)


return scene

currently it deletes all the information in the db by default, then it prints whatever is written in the text box followed by ‘nil’

[vb66r55]

Your code has only one problem,
You must move it to the function, otherwise the DB will not be closed properly.

local path = system.pathForFile( “data.db”, system.DocumentsDirectory)
local db = sqlite3.open( path)

You can try to record every step of the program error to learn bug fixing skills.