• gm_MySQL Lua DLL Module - v1.8
    314 replies, posted
  • Avatar of tomato3017
  • [QUOTE=mrflippy]Just for documentation in the thread, here are the values for the client_flag parameter for the connect function (At least I think so) :) : [code]#define CLIENT_LONG_PASSWORD 1 /* new more secure passwords */ #define CLIENT_FOUND_ROWS 2 /* Found instead of affected rows */ #define CLIENT_LONG_FLAG 4 /* Get all column flags */ #define CLIENT_CONNECT_WITH_DB 8 /* One can specify db on connect */ #define CLIENT_NO_SCHEMA 16 /* Don't allow database.table.column */ #define CLIENT_COMPRESS 32 /* Can use compression protocol */ #define CLIENT_ODBC 64 /* Odbc client */ #define CLIENT_LOCAL_FILES 128 /* Can use LOAD DATA LOCAL */ #define CLIENT_IGNORE_SPACE 256 /* Ignore spaces before '(' */ #define CLIENT_PROTOCOL_41 512 /* New 4.1 protocol */ #define CLIENT_INTERACTIVE 1024 /* This is an interactive client */ #define CLIENT_SSL 2048 /* Switch to SSL after handshake */ #define CLIENT_IGNORE_SIGPIPE 4096 /* IGNORE sigpipes */ #define CLIENT_TRANSACTIONS 8192 /* Client knows about transactions */ #define CLIENT_RESERVED 16384 /* Old flag for 4.1 protocol */ #define CLIENT_SECURE_CONNECTION 32768 /* New 4.1 authentication */ #define CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */ #define CLIENT_MULTI_RESULTS 131072 /* Enable/disable multi-results */ #define CLIENT_REMEMBER_OPTIONS (((ulong) 1) << 31)[/code] I had to use CLIENT_MULTI_RESULTS to return a result set back from a stored procedure. I have no idea whether any of the rest of the values will work with the module or not.[/QUOTE] Wow, very useful flippy! I have been looking for something like this.
  • Avatar of mrflippy
  • [QUOTE=tomato3017]Wow, very useful flippy! I have been looking for something like this.[/QUOTE] It's actually for our project :)
  • Avatar of andyvincent
  • I've got a threaded version that you guys can test - [url=http://www.andyvincent.co.uk/files/gmod/10/gm_mysql/1.6/gm_mysql-1.6.zip]Download[/url] ([url=http://www.andyvincent.co.uk/files/gmod/10/gm_mysql/1.6/gm_mysql-1.6-source.zip]Source[/url]) Example usage: [lua] print("------------------------------------------------------------------\n"); print("Threaded test!") thread, isok, error = mysql.query(db, "SELECT * FROM names", mysql.QUERY_NOBLOCK); if (thread) then print("thread=" .. thread .. " -> started\n") function printResults(threadid) if (mysql.query_complete(threadid)) then print("thread=" .. threadid .. " -> finished!") test, isok, error = mysql.query_read(threadid) if (test) then PrintTable(test) end if (!isok) then print( tostring(error) ); end mysql.query_cleanup(threadid) else print("thread=" .. threadid .. " -> still not finished") timer.Simple(1, printResults, threadid) end end timer.Simple(0, printResults, thread) end [/lua]
  • [lua] local db, error = mysql.connect("*******", "*******", "******", "********"); if (db == 0) then print(tostring(error) .. "\n") return end print("connection opened - " .. db .. "!\n"); steam, isok, error = mysql.query(db, "SELECT steam FROM users"); local succ, error = mysql.disconnect(db) if (not succ) then print( error ); end print("connection closed!\n"); if ([ply: steamID()] == steam ) then print("Worked!\n"); end [/lua] Can i have some help with this
  • Avatar of mrflippy
  • [QUOTE=buildaholic][lua]if ([ply: steamID()] == steam ) then[/lua][/QUOTE] I'm not quite sure what you're doing here. I'm not familiar with the bracket notation, and there shouldn't be a space between the colon and steamID(). (and it's actually SteamID() with a capital 'S') If you want to check for whether a player's steam id exists in the database, you probably want a sql query like [lua]local query = "select count(*) from player where steam = '"..ply:SteamID().."'"[/lua] This should return the number of records in the player table that have a steam id matching the player's. Or, if you want to check every player in the game, you could use the query that you have there now, but you would also need to loop through each of the players and loop through each of the steam IDs returned. (The sql queries should return a table as far as I know.)
  • To be honest i'm not that great with lua. I can do the mysql/php part easily all i need is: it to read my mysql database and get table users then find out is the player that is currently on the server has his steamid in the database. i can do [lua] if ([ply:SteamID()] == steam ) then //DO MY STUFF HERE end [/lua] just above not so sure abote this, here is so far [lua] local db, error = mysql.connect("crossstar.co.uk", "ben_bbservers", "****", "ben_BBservers"); if (db == 0) then print(tostring(error) .. "\n") return end print("connection opened - " .. db .. "!\n"); local steam = "select count(*) from player where steam = '"..ply:SteamID().."'" local succ, error = mysql.disconnect(db) if (not succ) then print( error ); end print("connection closed!\n"); if ([ply:SteamID()] == steam ) then //do my stuff here if there id is in the database end [/lua]
  • Avatar of UberMensch
  • MySQL queries return a 2d table as far as I know, so you should do: [lua] tostring(steam[1]["rowname"]) [/lua] When you compare something with another string.
  • Avatar of Guardian9978
  • Sorry to raise this old thread but I'm trying to get this to work. I was playing around with this and was wondering I would go about having it work with player spawn... For logging purposes. I tried to integrate this into the sandbox game mode which some of you may find not very smart but I didn't know how to do it any other way... since it has to be on player spawn and whenever I try to spawn the console throws this error: [CODE]attempted to index global 'ply' (a nil value)[/code] and the lua code i sued for that line its arguing about is this: [code]local query = "select count(*) from player where steam = '"..ply:SteamID().."'"[/code] anyone know how to do any of this? Also how would I log time that they spawned?
  • ply is nil... Simple as that, you didn't define it. You probably named the argument pl or something like that.
  • Avatar of UberMensch
  • [QUOTE=Guardian9978]Sorry to raise this old thread but I'm trying to get this to work. I was playing around with this and was wondering I would go about having it work with player spawn... For logging purposes. I tried to integrate this into the sandbox game mode which some of you may find not very smart but I didn't know how to do it any other way... since it has to be on player spawn and whenever I try to spawn the console throws this error: [CODE]attempted to index global 'ply' (a nil value)[/code] and the lua code i sued for that line its arguing about is this: [code]local query = "select count(*) from player where steam = '"..ply:SteamID().."'"[/code] anyone know how to do any of this? Also how would I log time that they spawned?[/QUOTE] Make another gamemode and derive sandbox into it. Also, take a look at [url=http://garrysmod.com/wiki/?title=Os.time]this[/url] for time things.
  • [QUOTE=UberMensch]Make another gamemode and derive sandbox into it.[/QUOTE] It's got nothing to do with that, he just need to define ply.
  • Avatar of Guardian9978
  • Look in the post above my last 1 I just removed the data for my SQL servers protection but one is specified. Since when I tested the insert it worked just fine. Just the Select line isn't working.
  • Avatar of maurits150
  • [QUOTE=Guardian9978]OK I Partially have it working this is what I have for the player initial spawn: [lua]/*--------------------------------------------*/ print("\n======================================================================\n"); print("======================================================================\n"); print("======================================================================\n"); local db, error = mysql.connect("*****", "*****", "*****", "*****") if (db == 0) then print(tostring(error) .. "\n") return end local query22 = mysql.query("select count(*) from server1 where ID2 = '"..ply:SteamID().."'"); if(query22 == 0) then steamid = ply:SteamID() name = ply:Nick() // joined1 = os.date() print("\nAttempting to insert into SQL\n\n"); // result, isok, error = mysql.query(db, "INSERT INTO server1 (ID2,Name,J1) VALUES('" .. steamid .. "','" .. name .. "','" .. joined1 .. "');"); result, isok, error = mysql.query(db, "INSERT INTO server1 (ID2,Name,J1) VALUES('" .. steamid .. "','" .. name .. "','0');"); if (!isok) then print("Failed to add player.\n\n"); end print("Added Player:\n\nJoined: " .. os.date() .. "\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end local succ2, error = mysql.disconnect(db) if (not succ2) then Msg( error .. "\n" ) end print("Done!!!"); print("======================================================================\n"); print("======================================================================\n"); print("======================================================================\n"); /*--------------------------------------------*/[/lua] and this is the message I am getting from it: [code]SERVER: Insufficient parameters![/code][/QUOTE] There's nothing wrong with the MySQL script but you forgot to write the os.date format flags fix this [lua]print("Added Player:\n\nJoined: " .. os.date() .. "\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end[/lua] to this [lua]print("Added Player:\n\nJoined: " .. os.date(%I:%M %p) .. "\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end -- --[/lua] [b]Edit:[/b] Damn why do the lua tags always give errors :(
  • Avatar of Guardian9978
  • Ok I figured out my mistake this line: [lua]local query22 = mysql.query("select count(*) from server1 where ID2 = '"..ply:SteamID().."'");[/lua] should be: [lua]local query22 = mysql.query(db, "select count(*) from server1 where ID2 = '"..ply:SteamID().."';");[/lua] Since I fixed that I tried to print out the result of query22 and all i get from it is: [code]table: 02638920[/code] (The number changes depending on what I change in the table data.) (Doesn't matter whats in the column even if its empty it shows the same thing.) ------------------------------------------------------------------------------------------------------------------------------------------- now about the time part... when i have this: [lua]result, isok, error3 = mysql.query(db, "INSERT INTO server1 (steam_id,name,j1) VALUES('" .. steamid .. "','" .. name .. "','" .. os.date(%D) .. "');");[/lua] it says: [code]Unexpected symbol near '%'[/code]
  • Avatar of tomato3017
  • I have figured out if you make threaded queries too fast that the database will go away. I found this out by simulating a heavy traffic to see if it would handle the load. Not to mention this crashes the server sometimes. This is a fresh install of the server I use to test my addons on. Here's my test code [lua] require( "mysql" ) local xdb, isok, error = mysql.connect("192.168.0.10", "****", "*****", "testdatabase") local threadtable = {} function querytest1() local thread, isok, error = mysql.query(xdb, "SELECT 1 + 1 FROM servers", mysql.QUERY_NOBLOCK); if(isok) then table.insert(threadtable,thread) print("Thread:" .. thread .. " made") else mysql.query_cleanup(thread) end timer.Simple(1,querytest1) end function querytest2() local thread, isok, error = mysql.query(xdb, "SELECT 1 + 1 FROM servers", mysql.QUERY_NOBLOCK); if(isok) then table.insert(threadtable,thread) print("Thread:" .. thread .. " made") else mysql.query_cleanup(thread) end timer.Simple(1,querytest2) end function querytest3() local thread, isok, error = mysql.query(xdb, "SELECT 1 + 1 FROM servers", mysql.QUERY_NOBLOCK); if(isok) then table.insert(threadtable,thread) print("Thread:" .. thread .. " made") else mysql.query_cleanup(thread) end timer.Simple(1,querytest3) end function querycallback(query) local count = 0 local atable = {} atable = table.Copy(threadtable) for i,v in pairs(atable) do count = count + 1 if(mysql.query_complete(v)) then local result, isok, error = mysql.query_read(v) mysql.query_cleanup(v) if !isok then print("THREAD FAILED") print(error) else print("THREAD #" .. v .. " FINISHED") print(result) end table.remove(threadtable,i) else print("THREAD NOT DONE") end end PrintTable(threadtable) timer.Simple(5, querycallback) end timer.Simple(1,querytest1) timer.Simple(1,querytest2) timer.Simple(1,querytest3) timer.Simple(1,querytest1) timer.Simple(1,querytest2) timer.Simple(5, querycallback) [/lua]
  • Avatar of tomato3017
  • [QUOTE=Guardian9978]How can my script be accessing the sql database too fast? I'm in my test server and all my websites and anything else using my sql is disabled. I'm the only 1 connecting to it.. and i try to connect when the server finishes loading and its told to print query22... All I get is table: ######### but the query insert into table works just fine with out a problem...[/QUOTE] I wasn't talking to you. Sorry if I made you confused.
  • Avatar of tomato3017
  • [QUOTE=Guardian9978]Ok I figured out my mistake this line: [lua]local query22 = mysql.query("select count(*) from server1 where ID2 = '"..ply:SteamID().."'");[/lua] should be: [lua]local query22 = mysql.query(db, "select count(*) from server1 where ID2 = '"..ply:SteamID().."';");[/lua] Since I fixed that I tried to print out the result of query22 and all i get from it is: [code]table: 02638920[/code] [/QUOTE] That's the table your COUNT is in. Try print(query22[1]) That should work.
  • Avatar of Guardian9978
  • Thank you Ill try that. EDIT: I tried that method and it gave me the same Table: ######## message. This is what I have currently: [lua]/*--------------------------------------------*/ print("\n======================================================================\n"); print("======================================================================\n"); print("======================================================================\n"); local db, error = mysql.connect("server", "username", "password", "database") if (db == 0) then print(tostring(error) .. "\n") return end query22 = mysql.query(db, "SELECT count(*) FROM iota WHERE ID2 = '" .. ply:SteamID() .. "';"); print("\nQuery22: "); print(query22[1]) print("\n\n"); if(query22[1] == 0) then steamid = ply:SteamID() name = ply:Nick() print("\nAttempting to insert into SQL\n\n"); query33 = mysql.query(db, "INSERT INTO iota (ID2,name,j1) VALUES('" .. steamid .. "','" .. name .. "','0');"); if (!query33) then print("Failed to add player.\n\n"); end print("Added Player:\n\nnick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end local succ2, error = mysql.disconnect(db) if (not succ2) then Msg( error .. "\n" ) end print("Done!!!"); print("======================================================================\n"); print("======================================================================\n"); print("======================================================================\n"); /*--------------------------------------------*/[/lua]
  • Avatar of tomato3017
  • [QUOTE=Guardian9978]-snip-[/QUOTE] It's returning a 2d table then, use [1][1] instead of just [1] then. Basically its returning this: {{result}} Also, os.date("%D") is the correct way per the definition on the garrys mod wiki which I suggest you read. [url]http://www.garrysmod.com/wiki/?title=Lua[/url]
  • Avatar of Guardian9978
  • That worked like a charm thanks... Now to figure out why my if statement isn't working... [lua]if(query22[1][1] == 0) then[/lua]
  • Avatar of tomato3017
  • [QUOTE=Guardian9978]That worked like a charm thanks... Now to figure out why my if statement isn't working... [lua]if(query22[1][1] == 0) then[/lua][/QUOTE] Try and see if this works. [lua]if(tonumber(query22[1][1]) == 0) then[/lua]
  • Avatar of Guardian9978
  • Never mind it worked I just made the edit to the wrong file since I was trying to test it on my game so I didn't have to keep closing and opening my dedicated server. Thank you. EDIT: This is what I got and it works thanks to tomato3017. This script saves the following information: SteamID Nickname(ingame name) Date they FIRST spawned in the server Date they Last joined the the server. (Updates every initial spawn or first spawn doesn't update if they're respawning from a death.) This script also sets everyone as disconnected after the dedicated starts up so if the server crashes when you start it up again everyone is marked as offline. (also works with listen servers.) Only lines you will need to modify are the following: Lines 5 and 14 - Connection info Lines 7,17,24, and 23 - change the table name iota to whatever the table name you want to use. [lua] function MySQLload() Msg("////////////////////////////////////////////////////\n") Msg("/// MySQL Script Loaded ///\n") Msg("////////////////////////////////////////////////////\n") local db, error = mysql.connect("SERVER", "USERNAME", "PASSWORD", "DATABASE") if (db == 0) then print(tostring(error) .. "\n") return end mysqlstarta = mysql.query(db, "UPDATE iota SET ONLINE = 0;") local succ2, error = mysql.disconnect(db) if (not succ2) then Msg( error .. "\n" ) end Msg("/// All players marked as offline. ///\n") Msg("////////////////////////////////////////////////////\n") end function MySQLSpawn(ply) local db, error = mysql.connect("SERVER", "USERNAME", "PASSWORD", "DATABASE") if (db == 0) then print(tostring(error) .. "\n") return end query22 = mysql.query(db, "SELECT count(*) FROM iota WHERE ID2 = '" .. ply:SteamID() .. "';"); if(tonumber(query22[1][1]) == 0) then steamid = ply:SteamID() name = ply:Nick() joined1 = os.date("%m/%d/%y") print("\nAttempting to insert into SQL\n\n"); query33 = mysql.query(db, "INSERT INTO iota (ID2,name,j1,lastlogin1) VALUES('" .. steamid .. "','" .. name .. "','" .. joined1 .. "','" .. joined1 .. "');"); print("Added Player:\n\nDate: " .. joined1 .. "\nNick: ".. name .. "\nSteamID: " .. steamid .. "\n\nTo the database.\n\n"); end if(tonumber(query22[1][1]) == 1) then steamid = ply:SteamID() name = ply:Nick() joined1 = os.date("%m/%d/%y") print("Updating " .. name .. "'s Profile."); query33 = mysql.query(db, "UPDATE iota SET lastlogin1 = '" .. joined1 .. "' WHERE ID2 = '" .. ply:SteamID() .. "';"); end local succ2, error = mysql.disconnect(db) if (not succ2) then Msg( error .. "\n" ) end end hook.Add( "PlayerInitialSpawn", "MySQLSpawn", MySQLSpawn ) hook.Add( "InitPostEntity", "MySQLload", MySQLload )[/lua]
  • [lua] steamid = pl:SteamID() test, isok, error = mysql.query(db, "INSERT INTO names (SteamId,Name) VALUES('" .. steamid .. "','it worked')");[/lua] I tried putting this in, and when ran it out puts in .. how do I run lua inside the mysql queries?
  • Avatar of Guardian9978
  • What exactly does it put out? Because from your example with the steamid that's the correct way.