SQLite HELP

Solar2D General Questions/Discussion
#1

Hello:
So, I am trying to set-up a database that has four tables. I need to get the row IDs that connects the tables together. I can create the tables and insert values them as followed:

[lua]-- Create tables
local path = system.pathForFile(“myPlayBook.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

local tablesetup = [[CREATE TABLE IF NOT EXISTS Play (ID INTEGER PRIMARY KEY AUTOINCREMENT, playName TEXT, Category TEXT);]]
print(tablesetup)
db:exec( tablesetup )

local tablesetup = [[CREATE TABLE IF NOT EXISTS Player (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Team TEXT, X INTEGER, Y INTEGER, Routed BOOLEAN, PlayID INTEGER);]]
print(tablesetup)
db:exec( tablesetup )

local tablesetup = [[CREATE TABLE IF NOT EXISTS Route (ID INTEGER PRIMARY KEY AUTOINCREMENT, PlayerID INTEGER, lineType TEXT, routeTip TEXT, tipX INTEGER, tipY INTEGER, tipA INTEGER, PlayID INTEGER);]]
print(tablesetup)
db:exec( tablesetup )

local tablesetup = [[CREATE TABLE IF NOT EXISTS routePoints (ID INTEGER PRIMARY KEY AUTOINCREMENT, pointNum INTEGER, X INTEGER, Y INTEGER, RouteID INTEGER, PlayID INTEGER);]]
print(tablesetup)
db:exec( tablesetup )
– Inserted Values

db:exec([[INSERT INTO Play VALUES (NULL, ‘]]…“34-Defense1”…[[’,’]]… categorySeletion… [[’);’]])

print(db:exec(“SELECT MAX(ID) FROM Play”)) – RETURNS 0 :frowning: WHY???

for i=1, playersGroup.numChildren do
local iPlayer = playersGroup[i]
if iPlayer.routed == true then
db:exec([[INSERT INTO Player VALUES (NULL, ‘]] … iPlayer.player …[[’,’]]… iPlayer.team …[[’,’]]…iPlayer.x …[[’,’]]… iPlayer.y …[[’,’]]…‘true’…[[’,’]]…db:exec(“SELECT MAX(ID) FROM Play”)…[[’);’]])
db:exec([[INSERT INTO Route VALUES (NULL, ‘]] … db:exec(“SELECT LAST(Player) AS LastID FROM Player”) …[[’,’]]… iPlayer.lineType …[[’,’]]… iPlayer.tip …[[’,’]]… iPlayer.tipX …[[’,’]]… iPlayer.tipY …[[’,’]]… iPlayer.tipA …[[’,’]]…db:exec(“SELECT MAX(ID) FROM Play”)…[[’);’]])
for w = 1, #iPlayer.points do
db:exec([[INSERT INTO routePoints VALUES (NULL, ‘]] … w …[[’,’]]… iPlayer.points[w].x …[[’,’]]… iPlayer.points[w].y …[[’,’]]… db:exec(“SELECT LAST(Route) AS LastID FROM Route”) …[[’,’]]… db:exec(“SELECT MAX(ID) FROM Play”)…[[’);’]])
end
else
db:exec([[INSERT INTO Player VALUES (NULL, ‘]] … iPlayer.player …[[’,’]]… iPlayer.team …[[’,’]]… iPlayer.x …[[’,’]]… iPlayer.y …[[’,’]]… ‘false’ …[[’,’]]… db:exec(“SELECT MAX(ID) FROM Play”)…[[’);’]])
end
end[/lua]

My problem is inserting the reference to the ID of Play to Player in PlayID , ID of Player and ID of Play to Route, etc…

I am able get the values of everything else by:

[lua] --debug only
for row in db:nrows(“SELECT * FROM Play ORDER BY ID DESC LIMIT 0”) do
print( row.ID, row.playName, row.Category)
end
print(db:exec(“SELECT * FROM Play ORDER BY ID DESC LIMIT 0”))

–print(db:last_insert_rowid(“SELECT * FROM Play”))
–[[
print(db:exec(“SELECT * FROM Play ORDER BY ID DESC LIMIT 0”))

print(db:exec(“SELECT LAST(Play) AS ID FROM Play”))

for row in db:nrows(“SELECT * FROM Player”) do
– I can get row.whatever
–print(row.Routed, row.PlayID)
end

for row in db:nrows(“SELECT * FROM Route”) do
– I can get row.whatever
–print(row.PlayerID, row.lineType)
end

for row in db:nrows(“SELECT * FROM routePoints”) do
– I can get row.whatever
–print(row.RouteID, row.PlayID)
end
–]][/lua]

I’ve tried every function I can think of the get the max Row ID to no avail.

Please Help.

Thanks,

Willy J.

[import]uid: 66859 topic_id: 12954 reply_id: 312954[/import]

#2 
Select Max(ID) As MaxID From Play

That will return the Maximum value of ID from the Play table, which you could then reference as:

row.MaxID [import]uid: 26769 topic_id: 12954 reply_id: 47556[/import]

#3

Ah, sorry. I’ve just seen that you’ve already done that. I got a bit confuddled because your formatting went wonky! [import]uid: 26769 topic_id: 12954 reply_id: 47557[/import]

#4

Yea,

I changed what I wrote

[lua]print(db:exec(“SELECT MAX(ID) FROM Play”)) [/lua]

to what you wrote and it still returned the value of O :frowning:

Is there a better way of doing this?

Thanks,

Willy J.
[import]uid: 66859 topic_id: 12954 reply_id: 47563[/import]

#5

How many records do you have in the play table? If you only have one, its possible its id could be zero.

oh you could also do:

SELECT id FROM Play ORDER BY id DESC LIMIT 1

I’m not sure that SQL lite supports all of that, but that works in most SQL DB’s. [import]uid: 19626 topic_id: 12954 reply_id: 47569[/import]

#6

Right now I have 5 in there there. I’ll always have at least one record in the Play table before it adds values to the others.

I also tried what you have written to no avail :frowning: [import]uid: 66859 topic_id: 12954 reply_id: 47572[/import]

#7

Okay, I must be doing something wrong or is this is the only way you supposed to do this… I was able to get the max or the last ID from the Play table by doing this:

[lua]for row in db:nrows(“SELECT ID FROM Play ORDER BY ID DESC LIMIT 1”) do
print(row.ID)
end

for row in db:nrows(“SELECT MAX(ID) FROM Play”) do
print(row)
end[/lua]

Those two will output the correct answer. BUT, this will not:

[lua]print (db:exec(“SELECT MAX(ID) FROM Play”))[/lua]

Why Not? Please help me!

Willy J.

[import]uid: 66859 topic_id: 12954 reply_id: 47576[/import]

#8

I think it’s because db:exec returns a SQLite code. In this case it’s returning 0 which means OK or success, i.e. it’s run the query and is telling you it ran it OK.

What you need to do is something like:

local maxID = 0  
  
for row in db:nrows("Select Max(ID) As MaxID From Play") do  
 maxID = row.MaxID   
end  
  
-- The variable maxID will now hold the maximum value of ID from Play, and you can use it in your other SQL statements.

Edit: Posted this before I’d seen your post above. It’s definitely because db:exec returns 0, which is the OK code from SQLite. [import]uid: 26769 topic_id: 12954 reply_id: 47577[/import]

#9

HAHA… Thats too funny! Thanks! [import]uid: 66859 topic_id: 12954 reply_id: 47580[/import]