gm_MySQL Lua DLL Module - v1.8


Gm_MySQL v1.8

Ok, here’s an extension for the developers! This provides 3 functions to connect and query MySQL databases:


Usage

mysql.connect(host, username, password, database, [port], [unix_socket], [client_flag])
Description: 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.
Returns: Database & error message. If database is 0, then there was a problem connecting to the database (error will be filled in).
Example:
[lua]local db, error = mysql.connect(“127.0.0.1”, “andy”, “mypass”, “crapbase”);
if (db == 0) then Msg(error … "
") end[/lua]

mysql.disconnect(database)
Description: Disconnect from a database.
Returns: success & error message.
Example:
[lua]local succ, error = mysql.disconnect(db);
if (not succ) then Msg(error … "
") end[/lua]

mysql.query(database, sql_string, [result_type])
Description: Query a database.
Returns: 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.
Example:
[lua]local tab, succ, error = mysql.query(db, “SELECT * FROM my_table”);
if (not succ) then Msg(error … "
") end
if (tab) then PrintTable(tab) end
[/lua]

mysql.escape(database, string)
Description: Escapes a string (cleans it).
Returns: string and error message. If string is false, the function failed (for whatever reason).
Example:
[lua]local test,error = mysql.query(db, "
“”);
if (not test) then Msg(error … "
")
else then Msg(test) end
[/lua]

mysql.last_insert_id(database)
Description: Retrieves the last ID used by an AUTO_INCREMENT_FIELD in an INSERT statement.
Returns: number and error message. If number is false, the function failed (for whatever reason).
Example:
[lua]local test,error = mysql.last_insert_id(db);
if (not test) then Msg(error … "
")
else then Msg( "Last id = " … tostring(test) ) end
[/lua]


Example Usage

[lua]require( “mysql” )

local db, error = mysql.connect(“127.0.0.1”, “username”, “password”, “database”)
if (db == 0) then print(tostring(error) … "
") return end

print("connection opened - " … db … "!
");

test, isok, error = mysql.query(db, “SELECT * FROM names”);
if (test) then
PrintTable(test)
end
if (!isok) then
print(tostring(error) … "
");
end

test, isok, error = mysql.query(db, “SELECT 1 + 1”);
if (test) then
PrintTable(test)
end
if (!isok) then
print(tostring(error) … "
");
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) … "
");
end

local succ, error = mysql.disconnect(db)
if (not succ) then
print( error );
end
print("connection closed!
");
[/lua]


Installation

[ul]
[li]Extract the zip file to your garrysmod\addons directory (ie: c:\program files\valve\steam\steamapps*username*\garrysmod\garrysmod\addons)
[/li][li]Take the libmySQL.dll, and put it in the same directory as HL2.EXE (ie: c:\program files\valve\steam\steamapps*username*\garrysmod)
[/li][li]Move the files gmsv_mysql.dll and gmcl_mysql.dll to the garrysmod\lua\includes\modules folder.
[/li][/ul]


Changes

[ul]
[li]1.8 - 8th January 2008
[/li][list][li]Added mysql.last_insert_id() to get the last ID used by an AUTO_INCREMENT field.
[/li][/ul]
[li]1.7 - 2nd January 2008
[/li][ul][li]Fixed a bug handling NULL entries within the database.
[/li][/ul]
[li]1.6 - 3rd August 2007
[/li][ul][li]Added thread support (see this post[/ul]
[/li][li]1.5 - 25th January 2007
[/li][ul][li]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.[/ul]
[/li][li]1.4 - 18th January 2007
[/li][ul][li]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.[/ul]
[/li][li]1.3 - 26th December 2006
[/li][ul][li]Queries no longer use mysql_real_escape_string (they wouldn’t anyway due to a bug).[
]Added mysql.escape to clean strings.[/ul]
[/li][li]1.2 - 20th December 2006
[/li][ul][li]Updated to latest interface version (003)
[/li][li]Queries now use mysql_real_escape_string. [Requested by Anders and morpheous]
[/li][li]Uses numbers to index the results (instead of numbers-as-strings).
[/li][/ul]
[li]1.1 - 2nd December 2006
[/li][ul][li]Bug fix to mysql.connect where the parameters were shifted by one (password would be used for the database name) etc. [Report by KmartSquirrel][/ul]
[/li][li]1.0 - 1st December 2006
[/li][ul][li]Initial Release[/ul]
[/li][/list]


Downloads

[ul]
[li]Zip file
[/li][li]Source code (for the paranoid)
[/li][li]Archive
[/li][/ul]

Oh you rock so much andy

We need a C++ king rating. Or an “I have a new religion” rating. Thanks Andy! :smiley:

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 :slight_smile:

Oh fucking shit… I really need one of these!

Andy have I ever told you that I love you?

Might come in handy…

Well done! Time to say “fuck off file databases” :slight_smile:

HOT

Thank you!

Lua God!!! Thanks for this.

Polarity, if you incorporate this into your RP mod, you could easily store jobs, amount of money, job access flags, ect.

Don’t worry, I’m already on it. :smiley:

I asked Andy for this in IRC for that EXACT purpose.

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))?

Edit:

Omg nvm, I really should start reading the first post. So it will always return in an assosiative table, right?

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:



t[row][col] = name
t[row][col] = value


So I have to buy a server for this?

Nice :smiley: I’m doing some lua now, this might come handy :smiley:

HOW can SO MANY afford to buy a C++ compiler, WHEN I CAN’T?! PLEASE TELL ME THE SECRET! WAREZ! :v:

If you don’t have a MySQL server, try http://freesql.org.

It’s slow, but it works.

Horray Andy, mySQL can always be used.

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.

Free MySQL,Php,Webserver

To Andy:
You got my very first LUA King, good work.