Lua Function mysqlQuery in otserv!

[AzLaN]

Yeah since I needed a lua function that queries stuff to the sql server I made this function. It doesn't have any kind of error checking. I'd be really happy if someone fixed that for me.

First add database.h to your includes!

in: luascript.cpp add:

int LuaScriptInterface::luaMysqlQuery(lua_State *L)
{
    //mysqlQuery(query, data/info)
    DBQuery query;
    std::string data = popString(L);
    query << popString(L);
    Database* db = Database::instance();
    DBResult* result;

    if(!(result = db->storeQuery(query.str()))){
        return false;
    }
    if(data != "set")
    {
        std::vector<std::string> dataexp = explodeString(data, ", ");
        lua_createtable(L, dataexp.size(), 0);

        for (int i=0; i<(int)dataexp.size(); ++i)
        {
            setField(L, dataexp[i], result->getDataString(dataexp[i]));
        }
    }
    query.str("");
    return 1;
}

after

int LuaScriptInterface::luaGetPlayerIp(lua_State *L)
{
    return internalGetPlayerInfo(L, PlayerInfoIp);
}

and then add:

void LuaScriptInterface::setField(lua_State *L, std::string index, const std::string& val)
{
    lua_pushstring(L, index.c_str());
    lua_pushstring(L, val.c_str());
    lua_settable(L, -3);
}

after

void LuaScriptInterface::setField(lua_State *L, const char* index, int32_t val)
{
    lua_pushstring(L, index);
    lua_pushnumber(L, (double)val);
    lua_settable(L, -3);
}

in: luascript.h add:

static int luaMysqlQuery(lua_State *L);

before

static int luaGetConfigValue(lua_State *L);

and then add:

static void setField(lua_State *L, std::string index, const std::string& val);

after:

static void setField(lua_State *L, const char* index, int32_t val);

DONE .


I hope I didn't forget anything and here is a example usage:

function onSay(cid, words, param)
    s = mysqlQuery("SELECT `online`, `force` FROM `players` WHERE `players`.`name` = '" .. getPlayerName(cid) .. "'", "force, online")
    broadcastMessage(s.force)
    mysqlQuery("INSERT INTO `accounts` (`name`, `password`) VALUES (5, 5)", "set")
end

I hope you can see how it works.

 

[classicnoobwar]

Help I downloaded the newest Avesta from https://code.google.com/p/avesta74/. Yet I don't have any map called Sources.

 

[222222]

Amazing! Thank you a bunch!
Also, if you want to add it as a TalkAction command for UPDATE/INSERT/DELETE, you can do this:

data\talkactions\talkactions.xml

<talkaction words="/sql" access="3" filter="first word" separator=" " script="sql.lua"/>

data\talkactions\scripts\sql.lua

function onSay(cid, words, param)
    local query = string.gsub(param, "'", "''")
    if (query ~= "") then
        mysqlQuery(query, "set")
    end
   
    print(":: Executed SQL Query by [" .. getCreatureName(cid) .. "]: " .. query);
    return 1
end

Usage in the game:

/sql update players set level = 123 where name = 'Steve'
/sql delete from bans where id = 5
/sql insert into TABLE_NAME (x,y,z) VALUES (x,y,z)

So now you can run SQL queries from the game as a gamemaster.
The script will also take into consideration with character names that includes the ' character so it won't mess up the queries.