SQLite HELP

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:

-- 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:

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:

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!

views:1696 update：2011/9/28 8:56:32


Label: corona android iphone ipad building error inmobi problem display screen View image tableView failed object physics touch button game lua config event listener