• gm_MySQL Lua DLL Module - v1.8
    314 replies, posted
  • Avatar of andyvincent
  • [highlight][u]Gm_MySQL v1.8[/u][/highlight] Ok, here's an extension for the developers! This provides 3 functions to connect and query MySQL databases: [highlight][u]Usage[/u][/highlight] [b][u]mysql.connect(host, username, password, database, [port], [unix_socket], [client_flag])[/u][/b] [b]Description:[/b] Connect to a database at a specified address. This also selects the database for you. You can have up to 255 con-current database connections at any one time. The parameters in []'s are optional. [b]Returns:[/b] Database & error message. If database is 0, then there was a problem connecting to the database (error will be filled in). [b]Example:[/b] [lua]local db, error = mysql.connect("127.0.0.1", "andy", "mypass", "crapbase"); if (db == 0) then Msg(error .. "\n") end[/lua] [b][u]mysql.disconnect(database)[/u][/b] [b]Description:[/b] Disconnect from a database. [b]Returns:[/b] success & error message. [b]Example:[/b] [lua]local succ, error = mysql.disconnect(db); if (not succ) then Msg(error .. "\n") end[/lua] [b][u]mysql.query(database, sql_string, [result_type])[/u][/b] [b]Description:[/b] Query a database. [b]Returns:[/b] Table, success, & error message. Table can be non-nil even if the success flag is false. This would indicate that when retrieving the data, an error occured, but some data was successfully retrieved. The parameters in []'s are optional. result_type should be one of the following: mysql.QUERY_NUMERIC, mysql.QUERY_FIELDS, or mysql.QUERY_BOTH. This parameter decides on how the result's keys should be formatted. If omitted then this defaults to mysql.QUERY_NUMERIC. [b]Example:[/b] [lua]local tab, succ, error = mysql.query(db, "SELECT * FROM my_table"); if (not succ) then Msg(error .. "\n") end if (tab) then PrintTable(tab) end [/lua] [b][u]mysql.escape(database, string)[/u][/b] [b]Description:[/b] Escapes a string (cleans it). [b]Returns:[/b] string and error message. If string is false, the function failed (for whatever reason). [b]Example:[/b] [lua]local test,error = mysql.query(db, "\r\n\""); if (not test) then Msg(error .. "\n") else then Msg(test) end [/lua] [b][u]mysql.last_insert_id(database)[/u][/b] [b]Description:[/b] Retrieves the last ID used by an AUTO_INCREMENT_FIELD in an INSERT statement. [b]Returns:[/b] number and error message. If number is false, the function failed (for whatever reason). [b]Example:[/b] [lua]local test,error = mysql.last_insert_id(db); if (not test) then Msg(error .. "\n") else then Msg( "Last id = " .. tostring(test) ) end [/lua] [highlight][u]Example Usage[/u][/highlight] [lua]require( "mysql" ) local db, error = mysql.connect("127.0.0.1", "username", "password", "database") if (db == 0) then print(tostring(error) .. "\n") return end print("connection opened - " .. db .. "!\n"); test, isok, error = mysql.query(db, "SELECT * FROM names"); if (test) then PrintTable(test) end if (!isok) then print(tostring(error) .. "\n"); end test, isok, error = mysql.query(db, "SELECT 1 + 1"); if (test) then PrintTable(test) end if (!isok) then print(tostring(error) .. "\n"); end test, isok, error = mysql.query(db, "INSERT INTO names (SteamId,Name) VALUES('fuck me!','it worked')"); if (test) then PrintTable(test) end if (!isok) then print(tostring(error) .. "\n"); end local succ, error = mysql.disconnect(db) if (not succ) then print( error ); end print("connection closed!\n"); [/lua] [highlight][u]Installation[/u][/highlight] [list] [*]Extract the zip file to your garrysmod\addons directory (ie: c:\program files\valve\steam\steamapps\[b]username[/b]\garrysmod\garrysmod\addons) [*]Take the libmySQL.dll, and put it in the same directory as HL2.EXE (ie: c:\program files\valve\steam\steamapps\[b]username[/b]\garrysmod) [*]Move the files gmsv_mysql.dll and gmcl_mysql.dll to the garrysmod\lua\includes\modules folder. [/list] [highlight][u]Changes[/u][/highlight] [list] [*]1.8 - 8th January 2008 [list][*]Added mysql.last_insert_id() to get the last ID used by an AUTO_INCREMENT field. [/list] [*]1.7 - 2nd January 2008 [list][*]Fixed a bug handling NULL entries within the database. [/list] [*]1.6 - 3rd August 2007 [list][*]Added thread support (see [url=http://forums.facepunchstudios.com/showpost.php?p=6238603&postcount=123]this post[/url][/list] [*]1.5 - 25th January 2007 [list][*]Added extra optional parameter to mysql.query which can determine weather to use the field names each row's attribute.[*]Revised folder structure to fit with Gmod's new specification.[/list] [*]1.4 - 18th January 2007 [list][*]Updated to latest interface version.[*]Autocloses any open connections when Gmod unloads the library[*]Tidied up code a little, now using GetNewTable instead of NewTable and GetObject.[/list] [*]1.3 - 26th December 2006 [list][*]Queries no longer use mysql_real_escape_string (they wouldn't anyway due to a bug).[*]Added mysql.escape to clean strings.[/list] [*]1.2 - 20th December 2006 [list][*]Updated to latest interface version (003) [*]Queries now use mysql_real_escape_string. [Requested by Anders and morpheous] [*]Uses numbers to index the results (instead of numbers-as-strings). [/list] [*]1.1 - 2nd December 2006 [list][*]Bug fix to mysql.connect where the parameters were shifted by one (password would be used for the database name) etc. [Report by KmartSquirrel][/list] [*]1.0 - 1st December 2006 [list][*]Initial Release[/list] [/list] [highlight][u]Downloads[/u][/highlight] [list] [*][url=http://www.brownlittlehandfootman.com/files/gmod/10/gm_mysql/1.8/gm_mysql-1.8.zip]Zip file[/url] [*][url=http://www.brownlittlehandfootman.com/files/gmod/10/gm_mysql/1.8/gm_mysql-1.8-source.zip]Source code (for the paranoid)[/url] [*][url=http://www.brownlittlehandfootman.com/files/gmod/10/gm_mysql/]Archive[/url] [/list]
  • Avatar of Polarity
  • We need a C++ king rating. Or an "I have a new religion" rating. Thanks Andy! :D
  • Avatar of Anders1
  • Cool, you should write a socket library next. I wanted to do that, but I've never done any socket programming in C/C++ so you might just be the right person to do it. I'll work on a gd library instead :)
  • [QUOTE=Polarity]We need a C++ king rating. Or an "I have a new religion" rating. Thanks Andy! :D[/QUOTE] Polarity, if you incorporate this into your RP mod, you could easily store jobs, amount of money, job access flags, ect.
  • Avatar of Polarity
  • Don't worry, I'm already on it. :D I asked Andy for this in IRC for that EXACT purpose.
  • Avatar of fackelkind
  • Nice, but what about result handling / data fetching? How I can select rows and parse them in my scripts? Is the something like: while (mysql.fetch_row (result))? [b]Edit:[/b] Omg nvm, I really should start reading the first post. So it will always return in an assosiative table, right?
  • Avatar of andyvincent
  • [QUOTE=fackelkind]Nice, but what about result handling / data fetching? How I can select rows and parse them in my scripts? Is the something like: while (mysql.fetch_row (result))? [b]Edit:[/b] Omg nvm, I really should start reading the first post. So it will always return in an assosiative table, right?[/QUOTE] It'll return a 2 dimensional table. Something like this: [lua]mysql.query(db, "SELECT name, value FROM table")[/lua] Will produce a table like this: [code] t[row][col] = name t[row][col] = value [/code]
  • Avatar of Galliko
  • HOW can SO MANY afford to buy a C++ compiler, WHEN I CAN'T?! PLEASE TELL ME THE SECRET! WAREZ! :v:
  • Avatar of Polarity
  • If you don't have a MySQL server, try [url]http://freesql.org[/url]. It's slow, but it works.
  • Avatar of Absolute
  • Here is something that should stop the asking of "How do I setup a MySQL server?" Just follow the install guides and there you go. [url="http://www.easyphp.org/"]Free MySQL,Php,Webserver[/url] To Andy: You got my very first LUA King, good work.
  • Avatar of Cheeslick!
  • If anyone wants to use my dual-cpu SQL machine they're more than welcome - just PM me for info.
  • Avatar of Anders1
  • [QUOTE=Galliko]HOW can SO MANY afford to buy a C++ compiler, WHEN I CAN'T?! PLEASE TELL ME THE SECRET! WAREZ! :v:[/QUOTE] Visual Studio C++ Express is free
  • Avatar of Absolute
  • Thank you so much Anders, I have been lookin for this stuff, Now I can finaly learn.
  • Avatar of jlulian38
  • [QUOTE=Anders1]Cool, you should write a socket library next. I wanted to do that, but I've never done any socket programming in C/C++ so you might just be the right person to do it. I'll work on a gd library instead :)[/QUOTE] You could just port the LuaSocket code to work with Garry's special init sequence thing. Probably can't be too hard.
  • Avatar of AUSSIE69
  • Sweet, I wanted to go into your server but I had to download 177 files and it was really slow.
  • Avatar of Anders1
  • [QUOTE=jlulian38]You could just port the LuaSocket code to work with Garry's special init sequence thing. Probably can't be too hard.[/QUOTE] Wrong LuaSocket is huge and I'd have to modify every function to make it work with the GModInterface.
  • Avatar of andyvincent
  • [QUOTE=tylerm89]I'm assuming this doesn't support Microsoft SQL Server 2000 or 2005?[/QUOTE] That's correct. MySQL isn't the same as MSSQL :(. [highlight]Updated - v1.1 - See first post![/highlight]
  • Avatar of Seiteki
  • The installer always tells me that I don't have Gmod10 installed. Why now?