[andyvincent]

Gm_MySQL v7 - OO edition

I was surprised when I came back to Facepunch & Garry's Mod (after nearly a year off) that people were still using my old MySql bindings. Having fresh inspiration, I set about re-writing it and coming up with a nicer way of doing things.

Crashing/Problems?
[list][*]Please give me as much information as possible:
[list][*]Test scripts / Example code[*]MDMP files[/list][*]Make sure you're running the most up-to-date version[/list]
Changes
[list][*]v7
[list][*]Added calls to mysql_library_init/mysql_library_end[*](Hopefully) fixed crash that could occur randomly[*]Updated to version 6.0.2 of libmysql[*]Fixed problem with on Linux that would stop queries from running[/list][*]v6
[list][*]Fixed a potential problem with the Linux DLLs[*]Fixed a problem with the Garbage Collection (thanks haza55)[/list][*]v5.1
[list][*]Linux binaries now available[/list][*]v5
[list][*]Finally tracked down the crash (it was due to libmysql.dll, so downgraded to 5.0). Thanks to everyone who helped.[*]Added version code (mysqloo.VERSION, mysqloo.MYSQL_VERSION, mysqloo.MYSQL_INFO)[*]Added server version (Database:serverVersion, Database:serverInfo, Database:hostInfo)[/list][*]v4
[list][*]Attempt to fix the crash problems that people are having...[/list][*]v3
[list][*]Added function: Database:wait() - blocks until connection has been completed.[*]Added function: Query:wait() - blocks until query has been completed (and all data read back).[/list][*]v2
[list][*]Bug fix - objects would be garbage collected whilst still running threads. This would show up more often if you were attempting to connect to a server that did not exist.[/list][*]v1
[list][*]Initial release[/list][/list]
Example

require("mysqloo")

local DATABASE_HOST = "localhost"
local DATABASE_PORT = 3308
local DATABASE_NAME = "test"
local DATABASE_USERNAME = "root"
local DATABASE_PASSWORD = "1234"

function printQuery(query)
  PrintTable(query:getData())
end

function afterConnected(database)
  local query1 = database:query("SELECT ID, Name, Cost FROM test WHERE Cost > 0.50")
  query1.onData = function(Q,D) print("Q1") PrintTable(D) end
  query1.onSuccess = printQuery
  query1.onError = function(Q,E) print("Q1") print(E) end
  query1:start()

  local query2 = database:query("SELECT ID, Name, Cost FROM test")
  query2.onData = function(Q,D) print("Q2") PrintTable(D) end
  query2.onError = function(Q,E) print("Q1") print(E) end
  query2:start()
end

function connectToDatabase()
  local databaseObject = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
  databaseObject.onConnected = afterConnected
  databaseObject:connect()
end

connectToDatabase()

Usage
Basic functionality:
[list][*]mysqloo.connect - Create a new database connection
Parameters: (hostname, username, password, database, port,
unix socket, client flag)
Returns: Database object[/list]Database object:
[list][*]Database:connect - Start connecting to the database
Parameters: ()
Returns: Nothing[*]Database:escape - Make text safe for queries
Parameters: (text)
Returns: Escaped string[*]Database:abortAllQueries - Aborts all running queries
Parameters: ()
Returns: Nothing[*]Database:status - Check database status
Parameters: ()
Returns: One of:
[list][*]mysqloo.DATABASE_CONNECTED - (0) - Connected to the database.[*]mysqloo.DATABASE_CONNECTING - (1) - Current is attempting to connect[*]mysqloo.DATABASE_NOT_CONNECTED - (2) - Connection was not successful or has failed[*]mysqloo.DATABASE_INTERNAL_ERROR - (3) - Some internal error occured[/list][*]Database:query - Query the database
Parameters: (query)
Returns: Query object[*]Database:onConnected - Callback when connected successfully
Parameters: ()
Returns: Nothing[*]Database:onConnectionFailed - Callback when connection could not be established.
Parameters: (error_message)
Returns: Nothing[/list]Query object:
[list][*]Query:start - Start a query running
Parameters: ()
Returns: Nothing[*]Query:abort - Abort a query running
Parameters: ()
Returns: Nothing[*]Query:getData - Get all data from a query
Parameters: ()
Returns: Table containing all the data that has been read so far[*]Query:setOption - Change query options
Parameters: (Flag, set)
Flag is one of:
[list][*]mysqloo.OPTION_NUMERIC_FIELDS - (1) - Use numeric fields when presenting data [default:off][*]mysqloo.OPTION_NAMED_FIELDS - (2) - Use named fields when presenting data [default:on][*]mysqloo.OPTION_INTERPRET_DATA - (4) - Auto-convert int/float fields to int/float representation [default:on][*]mysqloo.OPTION_CACHE - (8) - Cache the data after reading it [default:on][/list]Returns: Nothing[*]Query:status - Check query status
Parameters: ()
Returns: One of:
[list][*]mysqloo.QUERY_NOT_RUNNING - (0) - Query has not been started yet.[*]mysqloo.QUERY_RUNNING - (1) - The query is executing on the database.[*]mysqloo.QUERY_READING_DATA - (2) - The query has been run, and is now reading the data back.[*]mysqloo.QUERY_COMPLETE - (3) - The query has completed (successfully or with an error).[*]mysqloo.QUERY_ABORTED - (4) - The query has been aborted.[/list][*]Query:onData - Callback when a single row has been received
Parameters: (data_row)
Returns: Nothing[*]Query:onSuccess - Callback when all data has been received
Parameters: ()
Returns: Nothing[*]Query:onFailure - Callback when an error occured
Parameters: (error_message)
Returns: Nothing[*]Query:onAborted - Callback when a query was aborted
Parameters: ()
Returns: Nothing[/list]
Installation
[list][*]Extract the zip file to your garrysmod\addons directory (ie: c:\steam\steamapps\username\garrysmod\garrysmod\ad dons)[*]Take the libmySQL.dll, and put it in the same directory as HL2.EXE (ie: c:\steam\steamapps\username\garrysmod)[*]Move the files gmsv_mysqloo.dll and gmcl_mysqloo.dll to the garrysmod\lua\includes\modules folder.[/list]
Downloads
[list][*]Binaries
Windows
Linux[*]Source Code[/list]

[maurits150]

I'm sure a lot of people would love this. Thanks a ton.

[MakeR]

Awesome stuff.

[Jake1305]

Awesome.

Linux binary's? Because of the GMod Lin Bins today?

[Handsome Matt]

Ah metatables! Good work!

[Trivkz]

Awesome. Gonna update my logs with this!

[andyvincent]

Jake1305 posted:

Awesome.

Linux binary's? Because of the GMod Lin Bins today?

I don't have any means of compiling on Linux at the moment... Plus some code needs to be written to handle the Linux side of things with regard to threading. If anyone wants to contribute, let me know - I suppose you can via GitHub anyway.

[Jake1305]

andyvincent posted:

I don't have any means of compiling on Linux at the moment... Plus some code needs to be written to handle the Linux side of things with regard to threading. If anyone wants to contribute, let me know.

Alright', glad to see you back and things back on track.

Not sure if I'll switch from tmysql yet, I'll look into it, I'll post back later if I run into problems with it.

[Mistur Man]

Yay! Thank's alot!

[Prefan]

There seems to be a bug. Every time I try to connect to a database that is down, Garry's Mod crashes.

[andyvincent]

Prefan posted:

There seems to be a bug. Every time I try to connect to a database that is down, Garry's Mod crashes.

Thanks. Updated with version 2, please see the first post!

[Prefan]

Awesome, thanks.

Edited:

Crashed again, after doing some testing while inserting 200 entries into a sql table.

mdmp if it helps, http://dl.dropbox.com/u/717734/mysqlOO.mdmp.

[Prefan]

Also, if this isn't much, I have a request.

Would it be possible to create a non-threaded version of this module? I know I might sound crazy, but I designed my code around the non-threaded version of the old module, and it is impossible to move towards a threaded version. I think I'm going to stick with the old module for now, but eventually it would be nice to switch to this.

[andyvincent]

Prefan posted:

Also, if this isn't much, I have a request.

Would it be possible to create a non-threaded version of this module? I know I might sound crazy, but I designed my code around the non-threaded version of the old module, and it is impossible to move towards a threaded version. I think I'm going to stick with the old module for now, but eventually it would be nice to switch to this.

Would it be possible to get a copy of your test code that causes the crash? This would help a lot. Thanks.

I'm not sure about a non-threaded version, I would like to take a look at your code and see how you would adapt it be thread-capable.

[infinitywrai]

Interesting take on callbacks that I'm sure a lot of people would like, very similar to Azu's gm_tmysql2 that I use.

[MegaDeath]

Have a wrench good sir!

[SchumacherAlt]

Good to see you back, Andy, wonderful release.

[MegaDeath]

Weird, I set a variable to the DBObject in the connection function, but whenever i go to access it later it has become null? I think the reference counting is glitched...

Edited:

I'm gunna switch to the older API till this thing is better tested... Having everything asyncronous is also a bit of a pain when something must be done in order.

[andyvincent]

MegaDeath posted:

Weird, I set a variable to the DBObject in the connection function, but whenever i go to access it later it has become null? I think the reference counting is glitched...

Your test code would be helpful!

[NewBee]

hl2.exe keeps crashing when i try to connect to the mysql database.
This is the code i am using

-- core/sv_mysql.lua

rp.mysql = {}

local host = "localhost"
local port = 3306
local name = "rp"
local user = "root"
local pass = ""

function rp.mysql.connect()

	databaseObject = mysqloo.connect(host, user, pass, name, port)
	   databaseObject:connect()                                                                                                                      
	

end 

[andyvincent]

Are you using the latest version? Is the database setup?

Edited:

Version 3 released, see first post!

[Prefan]

Integrating into all my scripts now, thanks a ton for the wait functions.

Edited:

Crashed using the wait functions.

http://dl.dropbox.com/u/717734/hl2_4...1_31_24C0.mdmp

[NewBee]

andyvincent posted:

Are you using the latest version? Is the database setup?

Yes, I am using the latest version, it only crashes when the database is up and running (so the crash is probrably caused by connecting.).

[Lexic]

Oooh, nice looking module. I might switch to this later.

[nicatronTg]

I can only imagine the look on your face when you saw the 2006 bindings still being used.

Thanks a ton, I'll be getting around to MySQL a bit this weekend, I never actually fully integrated the old module.

[Skuch]

My GMod crashes aswell when it connects to the database.

I don't know whether it's a error in the connection part of the module, or the port handling part. It seems to crash when the port number isn't defined, or it's just plain numbers. But when embedded into a string it borkz out.

function NS.ConnectSQL()  

	local conn = mysqloo.connect( "127.0.0.1", "root", "", "coke" );
	conn:connect();

end

NS.ConnectSQL();

[andyvincent]

Does it actually crash ("HL2.exe has crashed" message), or lock up?

Edited:

Whatever I do I cannot make it crash (or lockup)... Please can you verify that you are using the latest version (3) - adding print(mysqloo.VERSION) to your script will tell you the version code (if it prints nil then you are using v1 or v2).

How is your script started (autorun-client, server or both?)
Are you using the libmysql.dll that was provided with this download or something else?
Anything that you can tell me will help :)

[NewBee]

andyvincent posted:

Does it actually crash ("HL2.exe has crashed" message), or lock up?

Edited:

Whatever I do I cannot make it crash (or lockup)... Please can you verify that you are using the latest version (3) - adding print(mysqloo.VERSION) to your script will tell you the version code (if it prints nil then you are using v1 or v2).

How is your script started (autorun-client, server or both?)
Are you using the libmysql.dll that was provided with this download or something else?
Anything that you can tell me will help :)

My hl2.exe when I start a listen server,there are also crashes when using the v3, but to be shure that i have v3 I redownloaded it. libmysql is where it shoud be (steam/steamapps/username/garrysmod/).

The script is in my gamemode and its loaded serverside in its own file.

I am not using any other mysql modules.

[andyvincent]

Can you re-create the crash with the example code (modify it for your tables and database settings)?

[NewBee]

andyvincent posted:

Can you re-create the crash with the example code (modify it for your tables and database settings)?

yes i can

[andyvincent]

It should be producing MDMP files in the directory that contains HL2.EXE. Could you send me the one it produces? Thanks.

[Nemesis036]

thank you

how could i have it so that on player first joining the server, it stores their name/steamid in a table?

[Kopimi]

Hehe, thanks a lot for this, I was just about to download your old gm_tMySQL and I noticed this. Time to go learn SQL lol

Edit:

I'm crashing when connecting to the server as well. I printed my version and it is 3, heres the code I'm using:

require("mysqloo")

print(mysqloo.VERSION)

local server = mysqloo.connect("MYDATABASE.COM", "USER", "PASS")

function connectSQL(server)
	server.onConnected = function()
		print("Connected to SQL host successfully!")
	end
	server:connect()
end

connectSQL(server)

I'm running my script using lua_openscript on a listen server.

[andyvincent]

Updated the DLLs to v4 (see the first post). I've not really added anything, but have down-graded to older header files, so this might fix the crashing. If it doesn't then I'll upload some debug DLLs or something.

[Knallex]

If it works now(gonna test tommorow or something), I'll freaking love you. I've hoped for some updates to this module, finally they've arrived :D

[Kopimi]

V4 doesnt work, I still crash on connecting. I cleaned my old install, reinstalled the new one, checked my version (printed 4) and ran the same code posted above, crashed.

[Saint Devil]

Mine's working fine. But the only thing I dislike (though not a big deal), is the fact that I can only connect to one database in a query. For example, if I were to connect to one database and query that, I couldn't query a second database in that same query on the onSuccess function.

[andyvincent]

Saint Devil posted:

Mine's working fine. But the only thing I dislike (though not a big deal), is the fact that I can only connect to one database in a query. For example, if I were to connect to one database and query that, I couldn't query a second database in that same query on the onSuccess function.

I don't think I've added any code to stop you querying other databases... I've not tried it though... This should work:

local DATABASE1_HOST = "localhost"  
local DATABASE1_PORT = 3308  
local DATABASE1_NAME = "test"  
local DATABASE1_USERNAME = "root"  
local DATABASE1_PASSWORD = "1234"  
  
local DATABASE2_HOST = "localhost"  
local DATABASE2_PORT = 3309
local DATABASE2_NAME = "test"  
local DATABASE2_USERNAME = "root"  
local DATABASE2_PASSWORD = "5678"  

function printQuery(query)  
  PrintTable(query:getData())  
end  
  
database1 = nil
database2 = nil
connected = 0

function runQuery2(query)
  local query2 = database2:query("SELECT ID, Name, Cost FROM test WHERE Cost > 0.50")  
  query2.onData = function(Q,D) print("Q2") PrintTable(D) end  
  query2.onSuccess = printQuery
  query2.onError = function(Q,E) print("Q2") print(E) end  
  query2:start()  
end

function afterConnected(database)  
  connected = connected + 1
  if (connected == 2) then
    local query1 = database1:query("SELECT ID, Name, Cost FROM test WHERE Cost > 0.50")  
    query1.onData = function(Q,D) print("Q1") PrintTable(D) end  
    query1.onSuccess = runQuery2  
    query1.onError = function(Q,E) print("Q1") print(E) end  
    query1:start()  
  end 
end  

function connectToDatabase()  
  database1 = mysqloo.connect(DATABASE1_HOST, DATABASE1_USERNAME, DATABASE1_PASSWORD, DATABASE1_NAME, DATABASE1_PORT)  
  database1.onConnected = afterConnected  
  database1:connect()  

  database2 = mysqloo.connect(DATABASE2_HOST, DATABASE2_USERNAME, DATABASE2_PASSWORD, DATABASE2_NAME, DATABASE2_PORT)  
  database2.onConnected = afterConnected  
  database2:connect()  
end  
  
connectToDatabase()

In theory you should be able to chain as many database-callbacks as you like.

[andyvincent]

Updated: Crash bug fixed, See first post for more information.

Download
http://github.com/downloads/andyvinc...MySqlOO-v5.zip

[Knallex]

Thanks, andy!