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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | -- 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 :-( 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 |
1 | Select Max(ID) As MaxID From Play |
Ah, sorry. I've just seen that you've already done that. I got a bit confuddled because your formatting went wonky!
Yea,
I changed what I wrote
1 | print(db:exec("SELECT MAX(ID) FROM Play")) |
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.
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 :-(
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:
1 2 3 4 5 6 7 | 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 |
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:
1 2 3 4 5 6 7 | 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. |
HAHA... Thats too funny! Thanks!
send message and call to a person
Forum: Report Spam + PM
[INSTALL_PARSE_FAILED_NO_CERTIFICATES]
[SOLVED] Director ERROR: Failed to execute new( params ) function on 'screen1'
Too many (200) local variables
Corona SVG Level Builder released. Drag and drop physics level editor with Inkscape and SVG.
Small bug in Collectible Items example
Pivot Joints and apparent elasticity
Can't make removeSelf() delayed