MySQLOO Problems.

Hey guys. I’m trying to get mysqloo working with this gamemode, but I can’t find a way to make these queries actually…well…query. I know mysqloo is working because I made a function in init.lua that created the required tables and columns, but the gamemode just won’t save/load player data. How would I set up mysqloo to query from multiple functions?? I’m new to SQL stuff so I’m trying my hardest.

mysqloo code to connect:


include("options.lua")
include("obj_player_extend.lua")
include("admin_shd.lua")
include("admin_sv.lua")
include("commands.lua")
include("dice.lua")
include("options_sv.lua")
include("purchases_shd.lua")
include("purchases_sv.lua")

require ("mysqloo")

local db = mysqloo.connect(MYSQL_DATABASE_HOST, MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD, MYSQL_DATABASE_NAME, MYSQL_DATABASE_PORT)
db:Connect()

function db:onConnected()
	print("[CCGM] Database has been connected")
end


What I’m trying to get working. All these functions that have sql queries. How to make connection without connecting to server in each function??


function GetSQLPid(self)
		local q = db.Query("SELECT player_id FROM zs_players WHERE steam_id='"..self:SteamID().."'")
		if not q then return end
		local pid
		for id,row in pairs(q) do
			pid = tonumber(row['player_id'])
		end
		
		return pid
	end

	function CCGM_PlayerDisconnected(user)
		local q = db:query("SELECT player_id FROM zs_players WHERE steam_id='"..user:SteamID().."'")
		if q then
			for id,row in pairs(q) do
				local pid = tonumber(row['player_id'])
				
		
				db.onConnected = function()
					local q = db:query("UPDATE zs_players SET last_connect='"..os.date("%Y-%m-%d %H:%M:%S").."' WHERE player_id='"..pid.."'")
					q:start()
				end
			end
		end
	end
	hook.Add("PlayerDisconnected", "CCGM Player Disconnect", CCGM_PlayerDisconnected)

	function HasLevelInformation(user)
	--return file.Exists("ccgm/zombiesurvival/info/"..string.gsub(user:SteamID(),":","_")..".txt")
		local query = db:query("SELECT steam_id FROM zs_players WHERE steam_id='"..user:SteamID().."'")
		
		if query then
			for id,row in pairs(query) do
				if row['steam_id']==user:SteamID() then
					return true
				end
			end
		end
		return false	
	end


Edit: The Code to connect to the DB in the example code is in options_sv.lua as you see it is linked.

The q object returned from db:Query doesnt contain data, but a object which calls a function when theres data ready for you. If you read the docs ( http://forum.facepunch.com/showthread.php?t=1220537 ), you will need to do something like



function GetSQLPid(self)
	local q = db.Query("SELECT player_id FROM zs_players WHERE steam_id='"..self:SteamID().."'")
	function q:onSuccess(data)
		local pid
		for id,row in pairs(data) do
			pid = tonumber(row['player_id'])
		end
		
		return pid
	end
	q:start()
end


Shit okay. That makes sense. Thanks. Just needed a second mind on that one!

Never try to return a value when dealing with MySQL. Always detach your thinking from function call > return, continue… to query > callback whenever it’s done, continue with other code not worrying about when the callback is called… on callback, continue what the query was doing…

Now, with a lot of your data, you may want to load or create it when the map starts. The same goes with player data, you may want to load / create it when the player connects and then load it when they’re fully in game. When you save data, just save it… OnFail have something happen. OnSuccess, you can ignore.

A few examples… When a player connects to my server ( using player_connect game-event, or CheckPassword Hook ), I create the account if it doesn’t exist, or increment connects. This prepares me for when the player fully connects… When they fully connect… I show them the usual and query character information. When character query callback returns data, I update the player and open the character menu. When a player chooses a character, or creates a new one, it notifies the server and the server deals with what needs to be done, ie if selecting a char, then selects and spawn and if creating then it confirms data and inserts then spawn or reports issue and the panel updates notifying the player of the issues…

I’m not saying my way is the absolute best way and you need to do it this way, but it makes things a lot easier ( and if you do force the server to halt all execution until the query returns, well… lets just say you will end up with more and more issues as more players join )…

Database, connecting:
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_mysqloo.lua.html
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_tmysql.lua.html

With OO you’ll need to do a few things on query ( helps to make a helper function / wrapper )… Check to see if the db is still connected, if not reconnect then query or no need to reconnect so just query. TMySQL supports persistent connections so as long as your db does you don’t need to reconnect…

When your player connects, it may be a good idea to pre-query and grab account id, or whatever else and store it in a data table so that when the other queries need to happen, you already have certain info ( although, you could easily use a JOIN to query all data associated with a steamid / accountid at once or each time ) but, just remember to use callbacks instead of trying to return data…

As Herover pointed out, onConnected is a different callback. You will also want to define onConnected / on Failed, etc… prior to calling the connect so that they exist when it happens ( although since they’re in their own thread, chances are the code you wrote will be executed by the time it connects )…

Hopefully this helps…

Nice, I tend to end up in callback-hell. Pointshop got some good snippets too…