Sqlite database reference to text box

Solar2D General Questions/Discussion
#41

You have valid dates?
For debugging purposes I’d suggest you create the string first i.e.

local query = '[[SELECT SUM(incomeValue) FROM income WHERE timeStamp > "]]..seven_days_ago..[[" AND timeStamp < "]]..now..[["]]'
print(query)
db:exec(query, getData)
print(weekIncome)

If unable to resolve upload your .db file and I’ll have a look.

#42

Yep, the dates i have set are valid. In the income scene I set it to insert dates like this:
local insertQuery = [[INSERT INTO income VALUES ( NULL, ']] .. integer .. [[',']].. os.date("!%Y-%m-%d") ..[[' );]]

I also created the string first, nil still prints though.

This is the file for the database, I only have income currently because I haven’t yet changed the expenses scene. However i’ll get to that when this is worked out. The database has two values in it currently.
data6.db (12 KB)

#44

Are you sure that you have used the correct database path/name in your open statement ?
A wrong path/name would cause an empty database to be created which could explain your problem.

I suggest you add the following statements before db:exec to see your database contents

for row in db:nrows( "SELECT * FROM income"  )
do
	print("row:",row.PK,row.incomeValue,row.timeStamp)		
end
#45

Thanks for the advice, I just realized the path to the database was wrong, I changed this and it’s working now!
Capture

Although With my expenses, the console doesn’t seem to understand something in my line of code. The expenses table is a separate table to the income, with rows “PK2”, “expenseValue” and “timeStamp2”.
This is what the code looks like:

local function getTodayExpenseData(udata, cols, todayvalues, names)
	todayExpense = todayvalues[1]
	return 0
end

db:exec([[SELECT SUM(expenseValue) FROM expense WHERE timeStamp2 > "]]..to_date..[[" AND timeStamp < "]]..now..[["]], getTodayExpenseData)
print(todayExpense)

And this is what the database shows:
Capture

#46

Did you run this test on the 23rd of September 2020 ?

.. WHERE timeStamp2 > "]]..to_date..[[" AND timeStamp < "]]..now..[["]] ...

The reason for asking is because you are testing for a timestamp less then now, meaning yesterday or before and the field “timeStamp2” contains todays date only.

But that is not the problem, take careful look at the SQL statement and ask yourself where you have defined the field “timeStamp”

#47

Ah yes, I see it now,
I wrote timeStamp instead of timeStamp2.
And sorry I forgot to mention to_date defines the time since 1970