sql syntax mistakes

[IIYAMA]

Hi,

I can't figure out why my system isn't saving and loading.

Can somebody check my code? Thank you!

  
-- loading -- 
local query = dbQuery(DB_connection, "SELECT * FROM `world` WHERE `area` = ?", gridX .. "/" .. gridY) 
if query then 
    outputDebugString("query") 
    local result, rows = dbPoll(query, -1) 
    if rows ~= 0 and type(result) == "table" and #result ~= 0 then 
        worldPartTable = fromJSON(result[1]) 
        outputDebugString("Loaded from DB") 
    end 
end 
------------ 

-- storing -- 
local query = dbQuery(DB_connection, "SELECT * FROM `world` WHERE `area` = ?", gridX .. "/" .. gridY) 
if query then 
    local result, rows = dbPoll(query, -1) 
    if result then 
        if rows == 0 then 
            dbExec(DB_connection, "INSERT INTO `world` (area) VALUES (?)", gridX .. "/" .. gridY) 
        end 
                 
        local resultOfUpdate = dbExec(DB_connection, "UPDATE `world` SET `data` = ? WHERE `area` = ?",toJSON(worldPartTable),gridX .. "/" .. gridY) 
         
         
        outputChatBox("resultOfUpdate: " .. tostring(resultOfUpdate)) 
    else 
        dbFree( query ) 
    end 
end 
----------- 

-- onResourceStart -- 
DB_connection = dbConnect ( "sqlite", "file.db" ) 
if DB_connection then 
    outputChatBox("connected to db") 
    dbExec(DB_connection, "CREATE TABLE IF NOT EXISTS `world` ( area TEXT )")     
else 
    local cancelReason = "MTA_map_generator: db failed to load." 
    outputDebugString(cancelReason) 
    cancelEvent(true,cancelReason) 
end 
--------------------- 

[TAPL]

result table will looks like:

result = { 
    [1] = { 
        ["columnName"] = value, 
        ["columnName"] = value, 
        ["columnName"] = value 
    }, 
    [2] = { 
        ["columnName"] = value, 
        ["columnName"] = value, 
        ["columnName"] = value 
    }, 
    [3] = { 
        ["columnName"] = value, 
        ["columnName"] = value, 
        ["columnName"] = value 
    } 
} 

So defiantly result[1] won't be JSON string ( line 8 ).

Also you've used UPDATE for column 'data' but your sql table doesn't have column with such name.

[IIYAMA]

Well the structure I want is this:

result = { 
    [1] = { 
        ["columnName"] = value, 
        ["columnName"] = value, 
        ["columnName"] = value 
    } 
} 

I want 1 key and a value.

I don't want more rows.

But if that is incorrect, how should I put it?

"UPDATE `world` SET `data` = ? WHERE `area` = ?" 

Like this?

"UPDATE `world` SET `area` = ?" 

or this?

"UPDATE `world` SET `area` = ? WHERE `area` = ?" 

or must I change the inset into?

[0xCiBeR]

First alter your table to hold "data" column. Then use a statement like this:

UPDATE `world` SET `data` = ? WHERE `area` = ? 

[IIYAMA]

And how will this structure be look like?

This?

    result = { 
        [1] = { 
            ["area"] = value, 
            ["data"] = value 
        } 
    } 

I ended up with:

local query = dbQuery(DB_connection, "SELECT * FROM `world` WHERE `area` = ?", gridX .. "/" .. gridY) 
if query then 
    local result, rows = dbPoll(query, -1) 
    if result then 
        if rows ~= 0 and type(result) == "table" and #result ~= 0 then 
            dbExec(DB_connection, "INSERT INTO `world` VALUES(area,value)",gridX .. "/" .. gridY,toJSON(worldPartTable)) 
        else 
           dbExec(DB_connection, "UPDATE `world` SET `value` = ? WHERE `area` = ?",toJSON(worldPartTable),gridX .. "/" .. gridY) 
        end 
    else 
        dbFree( query ) 
    end 
end 

And this warning:

WARNING: mta_map_generator\server_worldgeneration:425: dbExec failed; (1) no such column: value

This sql request: "UPDATE `world` SET `value` = ? WHERE `area` = ?"

[Addlibs]

Self explanatory - your database table does not have a column named 'value'. Add that column into the table or correct it in the code, depending on whether it was meant to be 'value' or something else.

[Deepu]

ok IIyama I got a great idea.... You Don't have a column named value, therefore you have to add a column... value

either recreate the table or ALTER Table thats it..

Stay Awesome, Brofist

[IIYAMA]

Yea how, how...

[Deepu]

If already existing table name was Poops then.. and if you want to add another column "SHIT" then.. use

ALTER TABLE Poops ADD SHIT TEXT

In GENERAL:

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE

[Saml1er]

But why are you updating a column which doesn't even exist? In your first post you are updating 2 columns ( area and data )

This???

  
dbExec(DB_connection, "UPDATE `world` SET `data` = ? WHERE `area` = ?", area, data) 

Anyway if you want to add a column named "value" then simply use alter.

dbExec(DB_connection, "ALTER TABLE world ADD value TEXT") 

[KariiiM]

dbExec(DB_connection, "ALTER TABLE world ADD value TEXT") 

Add this line to your code then after restarting your script the new column will be added automaticly then you can remove this line from your code since Sqlite isn't editable like mysql

[IIYAMA]

I solved it. The column wasn't created by another problem in the resource.

Thank you all!