LUA - mysql_direct_query / get_table_postfix / mysql_escape_string

[Metin2 Download 596]

M2 Download Center

This is the hidden content, please

• Sign In
• or
• Sign Up

( Internal )

You could use this code to create the relative functions in lua: mysql_direct_query, get_table_postfix, mysql_escape_string.

The mysql_direct_query returns two values: the first one contains the count of how many rows had been affected (works fine for select, insert, update queries, and so on) and the second one a table containing all the information retrieved by a select query (empty if not).
 

The field type will be auto-detected, which means:

• A numeric field will be pushed as lua number
• A BLOB one will be pushed as table byte per byte
• A NULL field will be pushed as nil (not displayed in iteration)
• The other ones will be pushed as strings (be aware of this)

 

Example

Example 1

Spoiler

-- local res1, res2 = mysql_direct_query("select player.name, player.level from player.player limit 2;")
local res1, res2 = mysql_direct_query("select * from player.player limit 1;")
-- local res1, res2 = mysql_direct_query("select name, skill_level from player.player limit 1;")
syschat(res2[1].name)
-- syschat(string.format("count(%d)", res1))
-- for i1=1, res1 do
	-- syschat(string.format("\tindex(%d)", i1))
	-- syschat(string.format("\t\tname(%s), level(%d)", res2[i1].name, res2[i1].level))
-- end
-- local res1, res2 = mysql_direct_query("select * from player.guild_comment;")
-- local res1, res2 = mysql_direct_query("INSERT INTO `guild_comment` (`id`) VALUES ('1');")
-- local res1, res2 = mysql_direct_query("INSERT INTO `guild_comment` (`guild_id`, `name`, `notice`, `content`, `time`) VALUES ('1', 'martytest', '0', 'blabla', NOW());")
syschat(string.format("count(%d)", res1))
for num1, str1 in ipairs(res2) do
	syschat(string.format("\tindex(%d)", num1))
	for key1, val1 in pairs(str1) do
		-- syschat(string.format("\t\tkey(%s), value(%s)", key1, val1))
		-- syschat(string.format("\t\tkey(%s), value(%s) type(%s)", key1, val1, type(val1)))
		if (type(val1)=="table") then
			syschat(string.format("\t\tkey(%s), size(%d), type(%s)", key1, table.getn(val1), type(val1)))
			-- syschat(string.format("\t\tkey(%s), type(%s)", key1, type(val1)))
			-- for num2, str2 in ipairs(val1) do
				-- syschat(string.format("\t\t\tidx(%s), value(%s), type(%s)", num2, str2, type(num2)))
			-- end
			-- syschat(string.format("\t\tkey(%s), value(%s), type(%s)", key1, table.concat(val1, ", "), type(val1))) --client will crash
		else
			syschat(string.format("\t\tkey(%s), value(%s), type(%s)", key1, val1, type(val1)))
		end
	end
end

-- syschat(mysql_escape_string("abyy"))
-- syschat(mysql_escape_string("'schure'")) --\'schure\'
syschat(mysql_escape_string("'\"lewd'\"")) --\'\"lewd\'\"
-- syschat(mysql_escape_string("`\n``\t`"))
syschat(mysql_escape_string([["aww'omg"<?'3]])) --\"aww\'omg\"<?\'3

 

Example 2

Spoiler

local query=[[
SELECT player.player.name as m_name, player.player.level as m_level, player.player.exp as m_exp
FROM player.player, account.account
WHERE
	player.player.name not like '[%]%' AND
	date_sub(now(), interval 1 day) < player.player.last_play AND
	player.player.account_id=account.account.id AND
	account.account.status='OK'
ORDER BY player.player.level DESC, player.player.exp DESC, player.player.name ASC
LIMIT 10;
]]
local res1, res2 = mysql_direct_query(query)
say_title("Top "..res1.." players.")
for num1, str1 in ipairs(res2) do
	say_reward(num1.."\t"..str1.m_name.."\t"..str1.m_level.."\t"..str1.m_exp)
end

 

Example 3

Spoiler

if get_time() > pc.getqf("antiflood") then
	local antiflood_time=30 -- 30 seconds
	pc.setqf("antiflood", get_time()+antiflood_time)
	-- do stuff
else
	local antiflood_diff=pc.getqf("antiflood")-get_time()
	say_reward("You are not allowed to view the rank yet[ENTER]Wait... "..antiflood_diff.." second/s")
end

 

 

How To

questlua_global.cpp

#include "db.h"

 

	int _get_table_postfix(lua_State* L)
	{
		lua_pushstring(L, get_table_postfix());
		return 1;
	}

#ifdef _MSC_VER
#define INFINITY (DBL_MAX+DBL_MAX)
#define NAN (INFINITY-INFINITY)
#endif
	int _mysql_direct_query(lua_State* L)
	{
		if (!lua_isstring(L, 1))
			return 0;

		int i=0, m=1;
		MYSQL_ROW row;
		MYSQL_FIELD * field;
		MYSQL_RES * result;

		std::auto_ptr<SQLMsg> pMsg(DBManager::instance().DirectQuery("%s", lua_tostring(L, 1)));
		if (pMsg.get())
		{
			// ret1 (number of affected rows)
			lua_pushnumber(L, pMsg->Get()->uiAffectedRows);
			//-1 if error such as duplicate occurs (-2147483648 via lua)
			//   if wrong syntax error occurs (4294967295 via lua)
			// ret2 (table of affected rows)
			lua_newtable(L);
			if ((result = pMsg->Get()->pSQLResult) &&
					!(pMsg->Get()->uiAffectedRows == 0 || pMsg->Get()->uiAffectedRows == (uint32_t)-1))
			{

				while((row = mysql_fetch_row(result)))
				{
					lua_pushnumber(L, m);
					lua_newtable(L);
					while((field = mysql_fetch_field(result)))
					{
						lua_pushstring(L, field->name);
						if (!(field->flags & NOT_NULL_FLAG) && (row[i]==NULL))
						{
							// lua_pushstring(L, "NULL");
							lua_pushnil(L);
						}
						else if (IS_NUM(field->type))
						{
							double val = NAN;
							lua_pushnumber(L, (sscanf(row[i],"%lf",&val)==1)?val:NAN);
						}
						else if (field->type == MYSQL_TYPE_BLOB)
						{
							lua_newtable(L);
							for (DWORD iBlob=0; iBlob < field->max_length; iBlob++)
							{
								lua_pushnumber(L, row[i][iBlob]);
								lua_rawseti(L, -2, iBlob+1);
							}
						}
						else
							lua_pushstring(L, row[i]);

						lua_rawset(L, -3);
						i++;
					}
					mysql_field_seek(result, 0);
					i=0;

					lua_rawset(L, -3);
					m++;
				}
			}
		}
		else {lua_pushnumber(L, 0); lua_newtable(L);}

		return 2;
	}

	int _mysql_escape_string(lua_State* L)
	{
		char szQuery[1024] = {0};

		if (!lua_isstring(L, 1))
			return 0;

		DBManager::instance().EscapeString(szQuery, sizeof(szQuery), lua_tostring(L, 1), strlen(lua_tostring(L, 1)));
		lua_pushstring(L, szQuery);
		return 1;
	}

 

			{	"get_table_postfix",			_get_table_postfix				},
			{	"mysql_direct_query",			_mysql_direct_query				},
			{	"mysql_escape_string",			_mysql_escape_string			},

Author: Marty