SQL set nick

[Memory]

Hello, please, help me add "nick" to Points Table. I can't set name of player to "nick", have error - Database query failed: no such column "nick".

function onResStart () 
executeSQLQuery ("CREATE TABLE IF NOT EXISTS Points ( serial TEXT, nick TEXT, points INTEGER DEFAULT 0 )" ); 
end 
addEventHandler ( "onResourceStart", getResourceRootElement ( getThisResource ( ) ), onResStart ) 
-------------------------------- 
function onJoin ( ) 
    local serial = getPlayerSerial ( source ); 
    local getPoints = executeSQLQuery ( "SELECT points FROM Points WHERE serial=?", serial ); 
    if type ( getPoints ) == "table" and #getPoints > 0 and getPoints[1] and getPoints[1].points then 
        setElementData ( source, "Points", tonumber ( getPoints[1].points ) ); 
    else 
        executeSQLQuery ( "INSERT INTO Points VALUES ( ?, ? )", serial, 0 ); 
        setElementData ( source, "Points", 0 ); 
    end 
end 
addEventHandler ( "onPlayerJoin", root, onJoin) 
---------------------------------------- 
function onPlayerQuit ( ) 
    local getActualPoints = tonumber ( getElementData ( source, "Points" ) ); 
    local getNickPlayer = getPlayerName(source); 
    local serial = getPlayerSerial ( source ); 
    local sql = executeSQLQuery ( "SELECT points FROM Points WHERE serial=?", serial ); 
    executeSQLQuery ( "UPDATE Points SET points=? WHERE serial=?", getActualPoints, serial ); 
    local sqlnick = executeSQLQuery ( "SELECT nick FROM Points WHERE serial=?", serial ); 
    executeSQLQuery ( "UPDATE Points SET nick=? WHERE serial=?", getNickPlayer, serial ); 
end 
addEventHandler("onPlayerQuit", root, onPlayerQuit) 

[Kenix]

Execute this query:

ALTER TABLE `Points` ADD `nick` TEXT NOT NULL

[Memory]

Thank you, but it don't work

ERROR: [gameplay]\points\points.lua:19: Database query failed: Cannot add a NOT NULL column with default value NULL 

Can be you mean FULL, but it too don't work

ERROR: [gameplay]\points\points.lua:19: Database query failed: near "FULL": syntax error 

[Renkon]

ALTER TABLE Points ADD nick VARCHAR(22)

[Kenix]

Well for mysql it's works perfectly.

[Kenix]

Well i check this out and you don't need use NOT NULL in query. In mysql you need write NOT NULL but in SQL no.

[Memory]

I have anyway any problem. Fix, please.

Create table and save.

function onResStart ()

executeSQLQuery ( "CREATE TABLE IF NOT EXISTS Points ( serial TEXT, points INTEGER DEFAULT 0 )" );

executeSQLQuery ( "ALTER TABLE Points ADD nick VARCHAR(22)" );

end

addEventHandler ( "onResourceStart", getResourceRootElement ( getThisResource ( ) ), onResStart )

--------------------------------

function onJoin ( )

local serial = getPlayerSerial ( source );

local getPoints = executeSQLQuery ( "SELECT points FROM Points WHERE serial=?", serial );

if type ( getPoints ) == "table" and #getPoints > 0 and getPoints[1] and getPoints[1].points then

setElementData ( source, "Points", tonumber ( getPoints[1].points ) or 0);

else

executeSQLQuery ( "INSERT INTO Points VALUES ( ?, ? )", serial, 0 );

setElementData ( source, "Points", 0 );

end

end

addEventHandler ( "onPlayerJoin", root, onJoin)

----------------------------------------

function onPlayerQuit ( )

local getActualPoints = tonumber ( getElementData ( source, "Points" ) );

local getNickPlayer = getPlayerName(source);

local serial = getPlayerSerial ( source );

local sql = executeSQLQuery ( "SELECT points FROM Points WHERE serial=?", serial );

executeSQLQuery ( "UPDATE Points SET points=? WHERE serial=?", getActualPoints, serial );

local sqlnick = executeSQLQuery ( "SELECT nick FROM Points WHERE serial=?", serial );

executeSQLQuery ( "UPDATE Points SET nick=? WHERE serial=?", getNickPlayer, serial );

end

addEventHandler("onPlayerQuit", root, onPlayerQuit)

I would like add "nick" for top of players.

addCommandHandler( 'top', 
    function( pPlayer ) 
        outputChatBox( 'TOP 10 PLAYERS', pPlayer ) 
        local aQuery = executeSQLQuery( 'SELECT points, nick FROM `Points` ORDER BY `points` DESC LIMIT 10' ) 
        if type( aQuery ) == 'table' and #aQuery > 0 then 
            for _, v in ipairs( aQuery ) do 
                outputChatBox( 'Player:' .. v[ 'nick' ] .. ', points:' .. v[ 'points' ], pPlayer ) 
            end 
        end 
    end 
) 

[Castillo]

And the problem is...?

[Memory]

Ah, sorry. Problem is that Top can't get "nick". Database query failed: no such column "nick". I would like set name of player to "nick" and refresh his when player quit or login.

[Memory]

Thank you, Kenix, work

[Kenix]

No problem.