dbPoll failed

[.:HyPeX:.]

Hey guys, i do not understand this error: (cresult)

dbPoll failed; near "~": syntax error.

  
function SaveDownOnResourceStop(resource) 
if resource ~= getThisResource() then return end 
outputChatBox("#6969C9*AS: Saving "..table.size(Punished).." records from sqlite.", root, 255,255,255,true) 
    for i, v in pairs(Punished) do 
    local exec = dbQuery(Database, "DROP TABLE IF EXISTS "..v[2]) 
        local aresult = dbPoll(exec, -1) 
        local query = dbQuery(Database, "CREATE TABLE "..v[2].." (Serial varchar, Punishes int, StartTime int, EndTime int, Duration int, PlayerName varchar, ResponsibleName varchar, Reason varchar );") 
        local bresult = dbPoll(query, -1) 
            for i,v in pairs(result) do 
            outputChatBox(type(v)) 
            end 
        local dataSaving = dbQuery(Database, "INSERT INTO "..v[2].." (Serial, Punishes, StartTime, EndTime, Duration, PlayerName, ResponsibleName, Reason) VALUES ("..v[2]..","..v[1]..","..v[3]..","..v[4]..","..v[5]..","..v[6]..","..v[7]..","..v[8]..")") 
        local cresult = dbPoll(dataSaving, -1) 
    end 
end 
addEventHandler("onResourceStop", resourceRoot, SaveDownOnResourceStop) 

EDIT: ERROR Return: dbPoll failed. Error code: 1 Error message: near "~": syntax error

EDIT2: Went even more into the trouble, and outputed some values:

  
~HyPeX~: a 
string number number number number string string string 
F27CCCFAB293B848763C115C76C09442 0 0 0 0 ~HyPeX~ AntiSpam None 

from:

outputChatBox(type(v[2]).." "..type(v[1]).." "..type(v[3]).." "..type(v[4]).." "..type(v[5]).." "..type(v[6]).." "..type(v[7]).." "..type(v[8])) 
outputChatBox((v[2]).." "..(v[1]).." "..(v[3]).." "..(v[4]).." "..(v[5]).." "..(v[6]).." "..(v[7]).." "..(v[8])) 

[.:HyPeX:.]

Bump i need help, this is driving me mad, i dont get why it does not work.

output:

  
~HyPeX~: a 
restart: Resource restarting... 
*AS: Saving 1 records from sqlite. 
table 
0 
0 
table 
0 
string number number number number string string string 
F27CCCFAB293B848763C115C76C09442 0 0 0 0 ~HyPeX~ AntiSpam None 
dbPoll failed. Error code: 1  Error message: near "~": syntax error 

Code:

  
unction SaveDownOnResourceStop(resource) 
if resource ~= getThisResource() then return end 
outputChatBox("#6969C9*AS: Saving "..table.size(Punished).." records from sqlite.", root, 255,255,255,true) 
    for i, v in pairs(Punished) do 
    local exec = dbQuery(Database, "DROP TABLE IF EXISTS "..v[2]) 
        local aresult, num_affected_rows, last_insert_id = dbPoll(exec, -1) 
        outputChatBox(type(aresult)) 
        outputChatBox(table.size(aresult)) 
        local query = dbQuery(Database, "CREATE TABLE "..v[2].." (Serial varchar, Punishes int, StartTime int, EndTime int, Duration int, PlayerName varchar, ResponsibleName varchar, Reason varchar );") 
        local bresult, num_affected_rows, last_insert_id = dbPoll(query, -1) 
        outputChatBox(table.size(bresult)) 
        outputChatBox(type(bresult)) 
        outputChatBox(num_affected_rows) 
        for i,v in pairs(bresult) do 
        outputChatBox(type(v)) 
        end 
            outputChatBox(type(v[2]).." "..type(v[1]).." "..type(v[3]).." "..type(v[4]).." "..type(v[5]).." "..type(v[6]).." "..type(v[7]).." "..type(v[8])) 
            outputChatBox((v[2]).." "..(v[1]).." "..(v[3]).." "..(v[4]).." "..(v[5]).." "..(v[6]).." "..(v[7]).." "..(v[8])) 
        local dataSaving = dbQuery(Database, "INSERT INTO "..v[2].." (Serial, Punishes, StartTime, EndTime, Duration, PlayerName, ResponsibleName, Reason) VALUES ("..v[2]..","..v[1]..","..v[3]..","..v[4]..","..v[5]..","..v[6]..","..v[7]..","..v[8]..")") 
        local cresult, num_affected_rows, last_insert_id = dbPoll(dataSaving, -1) 
        local error_code,error_msg = num_affected_rows,last_insert_id 
    outputConsole( "dbPoll failed. Error code: " .. tostring(error_code) .. "  Error message: " .. tostring(error_msg) ) 
    end 
end 
addEventHandler("onResourceStop", resourceRoot, SaveDownOnResourceStop) 

[cheez3d]

Use ? instead of concatenation. The problem is your name which has a special character ~. You either use ? or add ' for the query to work, but I suggest ?. Read more on the wiki.

[.:HyPeX:.]

How can i do the "?" thing?

[Karuzo]

For example:

  
local x = 5  
dbQuery(cnt," UPDATE yourtable SET x=?",x) 
  

[.:HyPeX:.]

There's no value set yet, thats why i want to use insert into. How can i make this work with insert into??

[TAPL]

Note: It is usually good practice to surround table and column names with backticks (`) in case they contain spaces or SQL keywords (and therefore cause syntax errors). This is especially true when using variables for table and column names, as potential problems may not be apparent when the script is first written.

This example shows how to use backticks and ?? for parts of the query that are not column values:

dbExec( connection, "UPDATE `??` SET `??`=?", tableName, columnName, columnValue ) 

Example

local qh = dbQuery( connection, "INSERT INTO table_name VALUES (?,?,?)", "aaa", "bbb", 10 ) 

[.:HyPeX:.]

Ok, sorry for the long time, i was just bussy.

Now, i cant figure it out. It says no such column 'F23A4676' (Serial)

  
local dataSaving = dbQuery(Database, "INSERT INTO "..v[2].." (Serial, Punishes, StartTime, EndTime, Duration, PlayerName, ResponsibleName, Reason) VALUES (?,?,?,?,?,?,?,?)",v[2],v[1],v[3],v[4],v[5],v[6],v[7],v[8]) 

EDIT: Got it to work, now there's something strange, i cannot get the results back...

  
function LoadUpOnResourceStart ( resource ) 
    local dbqery = dbQuery ( Database, "SELECT name FROM sqlite_master WHERE type='table'" ) 
    local tables = dbPoll ( dbqery, -1 ) 
    outputChatBox ( "#6969C9*AS: Restoring ".. #tables .." records from sqlite.", root, 255, 255, 255, true ) 
    for index, tbl in ipairs ( tables ) do 
        loadUpTable [ tbl.name ] = {} 
        loadUpTable [ tbl.name ][ 1 ] = tbl.name 
        outputChatBox(tbl.name) 
    end 
  
    for a, v in pairs ( loadUpTable ) do 
    local Query = dbQuery ( Database, "SELECT * FROM "..v[1] ) 
        local result = dbPoll ( Query, -1 ) 
        outputChatBox ( table.size(result) ) 
        for i, v in pairs ( result ) do 
            outputChatBox ( #v ) 
            for col, val in ipairs ( v ) do 
                outputChatBox ( val ) 
            end 
        end 
    end 
end 
addEventHandler ( "onResourceStart", resourceRoot, LoadUpOnResourceStart ) 

Output:

  
*AS: Restoring 1 records from sqlite. 
F27CCCFAB293B848763C115C76C09442 
1 
0 

[.:HyPeX:.]

Bump, anyone?

[ixjf]

DON'T concatenate the values directly with the query string. That's a huge flaw and can easily be exploited. Use ? instead and pass the actual value as an argument.

As for your issue, replace ipairs with pairs in line 18.

And for optimal performance, replace pairs with ipairs in line 16 since it's an array.