gmsv_mysqloo - Updated OO MySQL Module (multiple statements/stored procs!)

[Kingof🅱east]

[b]MySQLOO v8.1 - OO MySQL Module[/b]
Originally by AndyVincent, updated to GM13 by Drakehawke

Link to previous thread: [url]http://facepunch.com/showthread.php?t=1220537[/url]

[b]- - - New in 8.1 - - -[/b]

REVISION 001:
Added linux compatibility code to LuaOO/source/CLASS_LuaObjectBase.cpp (snprintf)
Added CLIENT_INTERACTIVE flag to mysqloo global table

REVISION 000:
Supports multiple statements in-query - CLIENT_MULTI_STATEMENTS flag must be passed
Supports stored procedures - No longer receive those pesky out-of-sync errors!
Halfway supports multiple resultsets - No more out-of-sync errors, however only the first resultset is handled properly, and the rest are thrown out. This is going to be fixed completely eventually.

[b]Documentation[/b] - Thanks Drakehawke!

-- mysqloo table

mysqloo.connect( host, username, password [, database, port, socket, flags] )
-- returns [Database]
-- Initializes the database object, note that this does not actually connect to the database.

mysqloo.VERSION -- [Number] Current MySQLOO version
mysqloo.MYSQL_VERSION -- [Number] Current MySQL version
mysqloo.MYSQL_INFO -- [String] Fancy version string

mysqloo.DATABASE_CONNECTED -- [Number] 0 - Database is connected
mysqloo.DATABASE_CONNECTING -- [Number] 1 - Datbase is connecting
mysqloo.DATABASE_NOT_CONNECTED -- [Number] 2 - Database is not connected
mysqloo.DATABASE_INTERNAL_ERROR -- [Number] 3 - Internal error

mysqloo.QUERY_NOT_RUNNING -- [Number] 0 - Query not running
mysqloo.QUERY_RUNNING -- [Number] 1 - Query running
mysqloo.QUERY_READING_DATA -- [Number] 2 - Query is reading data
mysqloo.QUERY_COMPLETE -- [Number] 3 - Query is complete
mysqloo.QUERY_ABORTED -- [Number] 4 - Query was aborted

mysqloo.OPTION_NUMERIC_FIELDS -- [Number] 1 - ?
mysqloo.OPTION_NAMED_FIELDS -- [Number] 2 - ?
mysqloo.OPTION_INTERPRET_DATA -- [Number] 4 - ?
mysqloo.OPTION_CACHE -- [Number] 8 - ?

mysqloo.CLIENT_MULTI_STATEMENTS -- [Number] 65536 - Allows multiple statements within one query
mysqloo.CLIENT_MULTI_RESULTS -- [Number] 131072 - Allows multiple result sets to be returned by the server
mysqloo.CLIENT_INTERACTIVE -- [Number] 1024 - Uses interactive_timeout rather than wait_timeout for the connection

-- Database object

-- Functions
Database:connect()
-- Returns nothing
-- Connects to the database

Database:query( sql )
-- Returns [Query]
-- Initializes a query to the database, [String] sql is the SQL query to run.

Database:escape( str )
-- Returns [String]
-- Escapes [String] str so that it is safe to use in a query.

Database:abortAllQueries()
-- Returns nothing
-- Aborts all running queries to the database

Database:status()
-- Returns [Number] (mysqloo.DATABASE_* enums)
-- Checks the connection to the database
-- USE THIS FUNCTION CONSERVATIVELY - it forces the main thread to wait for all running queries to finish
-- If you call this before each query it will cause lag on your server!
-- Instead you should wait for a query to timeout, THEN check the connection.

Database:wait()
-- Returns nothing
-- Forces the server to wait for the connection to finish.

Database:serverVersion()
-- Returns [Number]
-- Gets the MySQL servers version

Database:serverInfo()
-- Returns [String]
-- Fancy string of the MySQL servers version

Database:hostInfo()
-- Returns [String]
-- Gets information about the connection.

-- Callbacks
Database.onConnected( db )
-- Called when the connection to the MySQL server is successful

Database.onConnectionFailed( db, err )
-- Called when the connection to the MySQL server fails, [String] err is why.

-- Query object

-- Functions
Query:start()
-- Returns nothing
-- Starts the query.

Query:isRunning()
-- Returns [Boolean]
-- True if the query is running, false if it isn't.

Query:getData()
-- Returns [Table]
-- Gets the data the query returned from the server
-- Format: { row1, row2, row3, ... }
-- Row format: { field_name = field_value }

Query:abort()
-- Returns nothing
-- Cancels the running query.

Query:lastInsert()
-- Returns [Number]
-- Gets the autoincrement index of the last inserted row.

Query:status()
-- Returns [Number] (mysqloo.QUERY_* enums)
-- Gets the status of the query.

Query:affectedRows()
-- Returns [Number]
-- Gets the number of rows the query has affected.

Query:setOption( option )
-- Returns nothing
-- Changes how the query runs/returns data (mysqloo.OPTION_* enums).

Query:wait()
-- Returns nothing
-- Forces the server to wait for the query to finish.

Query:error()
-- Returns [String]
-- Gets the error caused by the query (if any).

-- Callbacks
Query.onAborted( q )
-- Called when the query is aborted.

Query.onError( q, err, sql )
-- Called when the query errors, [String] err is the error and [String] sql is the SQL query that caused it.

Query.onSuccess( q, data )
-- Called when the query is successful, [Table] data is the data the query returned.

Query.onData( q, data )
-- Called when the query retrieves a row of data, [Table] data is the row.

[b]Example[/b] - Thanks Drakehawke!

require( "mysqloo" )

local db = mysqloo.connect( "123.456.789.0", "drake", "abc123", "database_name", 3306 )

function db:onConnected()

    print( "Database has connected!" )

    local q = self:query( "SELECT 5+5;" )
    function q:onSuccess( data )

        print( "Query successful!" )
        PrintTable( data )

    end

    function q:onError( err, sql )

        print( "Query errored!" )
        print( "Query:", sql )
        print( "Error:", err )

    end

    q:start()

end

function db:onConnectionFailed( err )

    print( "Connection to database failed!" )
    print( "Error:", err )

end

db:connect()

[b]This module relies on the server's think hook. If there are no players online, think doesn't run, and that is why the module isn't working for you.[/b]

For other general information, check the other thread.

[b]Download & Installation[/b]
Binaries and source: [url=https://github.com/KingofBeast/gmsv_mysqloo]GitHub[/url]

[url=https://github.com/KingofBeast/gmsv_mysqloo/blob/master/RELEASE/gmsv_mysqloo_win32.dll?raw=true]Windows DLL[/url] + [url=https://github.com/KingofBeast/gmsv_mysqloo/blob/master/RELEASE/libmysql_win32.zip?raw=true]libmysql[/url]
[url=https://github.com/KingofBeast/gmsv_mysqloo/blob/master/RELEASE/gmsv_mysqloo_linux.dll?raw=true]Linux DLL[/url] + [url=https://github.com/KingofBeast/gmsv_mysqloo/blob/master/RELEASE/libmysql_linux.tar.gz?raw=true]libmysqlclient[/url]

[b]gmsv_mysqloo[/b] must go into [b]path/to/server/garrysmod/lua/bin[/b]. If the folder doesn't exist, create it.
[b]libmysql/libmysqlclient[/b] must go into [b]path/to/server[/b]. This is the same folder that srcds.exe/srcds_run are in.

[EvacX]

Thank you <3

[brandonj4]

Well done!

[MIKEYMASTALSKi]

Do you know if your build has the fix for the lua memory objects never being cleaned up?

The issue is outlined here by Maurits [url]https://facepunch.com/showthread.php?t=1220537&p=41989340&viewfull=1#post41989340[/url]

It seems to be a bug happening for me but only on my Linux (Debian 7) machine.

When using DebugR, MySQLOO's Think hook causes my server to lag horrifically (2 ticks/2fps).

While I'm not sure if the cause is from what Maurit's outlined, it's definitely an issue and I'm not sure if it's something with my code or not.

[Kingof🅱east]

I myself haven't ever been plagued by this issue so it may be something to test, but I will look into it. I do better writing my own code so right now I'm in the process of remaking the module entirely so at that point the issue may not exist.

May help to share your code so I can see it. May help tracking stuff down.

[BomBom]

Gonna use this when Linux builds come in.
Any ideas when?

[Kingof🅱east]

Anyone is free to compile the source under Linux. It shouldn't give any issues. I'll add a download link to the OP if somebody does. I don't have any Linux installs or else I would do it myself

[DrunkSquirrel]

Currently using this but still having issues with returning queries and the like. I can use require("mysqloo") with no errors. But when putting the example code in a test lua file, and running it or any query, I get no type of print call back, or error.

[lua]
require( "mysqloo" )

local DATABASE_HOST = "doolaloo.com" // database host (can be an ip)
local DATABASE_PORT = 3306 // port to the database, you probably wont need to change this unless you get told to
local DATABASE_NAME = "leplaya" // name of the database
local DATABASE_USERNAME = "obvuser" // username which you use to access it
local DATABASE_PASSWORD = "test91210" // password of the username

local db = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)

function db:onConnected()

print( "Database has connected!" )

local q = self:query( "SELECT 5+5;" )
function q:onSuccess( data )

print( "Query successful!" )
PrintTable( data )

end

function q:onError( err, sqlz )

print( "Query errored!" )
print( "Query:", sqlz )
print( "Error:", err )

end

q:start()

end

function db:onConnectionFailed( err )

print( "Connection to database failed!" )
print( "Error:", err )

end

db:connect()
[/lua]

When running, the code outputs this:
[lua]
] lua_openscript mysql.lua
Running script mysql.lua...
[/lua]

But prints out nothing. Now when I run it in single player, or on a local server it works fine and correctly, printing this instead:
[lua]
] lua_openscript mysql.lua
Running script mysql.lua...
Database has connected!
Query successful!
1:
5+5 = 10
[/lua]

I've installed it exactly the same on 2 of my servers, and neither of them are responding to the connection or any queries ran.

[Kingof🅱east]

Are you running it on a server that has no connected players?

MySQLOO relies on the think hook, which doesn't run if there are no players.

[Linda]

[QUOTE=KingofBeast;43873019]Are you running it on a server that has no connected players?

MySQLOO relies on the think hook, which doesn't run if there are no players.[/QUOTE]
You should add that to the OP. That's important to know.

[BomBom]

[QUOTE=KingofBeast;43871645]Anyone is free to compile the source under Linux. It shouldn't give any issues. I'll add a download link to the OP if somebody does. I don't have any Linux installs or else I would do it myself[/QUOTE]

Been trying to compile it on my local Debian 7 x64 vps.
So far only getting this.

==== Building ThreadOO (release) ====
Creating obj/Release/ThreadOO
CLASS_Mutex.cpp
CLASS_Event.cpp
CLASS_Thread.cpp
../ThreadOO/source/CLASS_Thread.cpp: In static member function ‘static void* Thread::threadProc(void*)’:
../ThreadOO/source/CLASS_Thread.cpp:58:16: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast]
CLASS_MutexLocker.cpp
Linking ThreadOO
==== Building LuaOO (release) ====
Creating obj/Release/LuaOO
CLASS_LuaOO.cpp
CLASS_LuaClassInfo.cpp
CLASS_LuaObjectBase.cpp
../LuaOO/source/CLASS_LuaObjectBase.cpp: In member function ‘virtual int LuaObjectBase::toString()’:
../LuaOO/source/CLASS_LuaObjectBase.cpp:41:59: error: ‘sprintf_s’ was not declared in this scope
make[1]: *** [obj/Release/LuaOO/CLASS_LuaObjectBase.o] Error 1
make: *** [LuaOO] Error 2

[Kingof🅱east]

See if it works when you include stdio.h

[&gt;&gt;oubliet]

Out of interest, why does it require a think hook to process queries? Can't it send off queries as soon as they're created?

[DrunkSquirrel]

Yes I tried running queries on and and off both servers. I also wanted to know if my server provider play a role in this? Using NFOServers as well.

[Kingof🅱east]

[QUOTE=>>oubliette<<;43873547]Out of interest, why does it require a think hook to process queries? Can't it send off queries as soon as they're created?[/QUOTE]

The query objects do get handled and can finish without the think hook running, they just wouldn't get a chance to let the script know of that. The polling function comes in as a convenient way for the query objects to talk back to the Lua state when it's ready. Otherwise I'd imagine it'd cause some pretty unexpected results.

[QUOTE=DrunkSquirrel;43873599]Yes I tried running queries on and and off both servers. I also wanted to know if my server provider play a role in this? Using NFOServers as well.[/QUOTE]

You need to test it while players are on the server, or else it definitely won't work.

[BomBom]

[QUOTE=KingofBeast;43873524]See if it works when you include stdio.h[/QUOTE]

I included both stdio.h and stdlib.h, didn't seem to make any difference.
The server does have build-essentials and gcc installed.

[Kingof🅱east]

Okay. Found out that sprintf_s isn't standard. Try replacing sprintf_s with snprintf.

[DrunkSquirrel]

[QUOTE=KingofBeast;43873943]You need to test it while players are on the server, or else it definitely won't work.[/QUOTE]

I have tested it with players on the server, and off, and as well as myself on the server and off. I run the script above and the onsuccess/onconnectionfailure hooks are not being called at all. Nothing is printed back to me on my server console, but in singleplayer/local hosted servers, it is.

For example:
[lua]
function TestQuery()
print("Making Query Test...")
id = "STEAM_0:1:27452596"
local query1 = db:query("SELECT * FROM Player WHERE ID = '" .. id .. "'") //Find their steamID
query1.onSuccess = function(q) print("OOOOKAY") end
query1.onError = function(q,e) print("something went wrong when checking") end
query1:start()
end
concommand.Add("test_query", TestQuery)
[/lua]

Returns nothing at all on my server, The odd thing is the database is connected and everything:
[lua]
function ReportServerInfo()
print("Current mysqloo status num: " .. db:status())
print("Server info: " .. db:serverInfo())
print("Host info: " .. db:hostInfo())
end
concommand.Add("reportserverinfo", ReportServerInfo)
[/lua]

The above code block returns this:
[lua]
Current mysqloo status num: 0
Server info: 5.5.32
Host info: drunkdeath.com via TCP/IP
[/lua]

The "0" means its connected, but when it connects, I'm not getting any console feedback, any prints, no returns, just blank spaces after running the script. However as stated above it all works for me on singleplayer, returning everything in console printed. So does anyone have any idea of whats going on / how I can fix this?

[disseminate]

Can you add the CLIENT_INTERACTIVE flag as a potential connection flag?

[Kingof🅱east]

Done. mysqloo.CLIENT_INTERACTIVE

Also added code to allow Linux to compile ([b]Linux compile runs properly in SrcDS now[/b]) and cleaned up BuildProjects a bit.

Git updated. Download link will give you the updated dll.

[Kingof🅱east]

Added linux compile to the OP and fixed the BuildProjects files so compiling under linux shouldn't be an issue.

[NiandraLades]

Hey, has anyone else experienced server crashing because of this module? We need it for one of our addons, but since doing so we've been having occasional crashes, which usually happen on map change or map vote.

[NaRyan]

The only addon that I used that would cause the server to crash was SourceBans addon.
(would loose connection to the database. retry in 60 seconds, 60 seconds later server would crash)

Using this module with PointShop and TTT Stats at the moment, and not had any crashes on map change or map vote.

[Liam MacKay]

It's running fine for me, and I'm using it for Pointshop and ULX MySQL

[[GG]Super]

Does this work on centos 6 by any chance?

[Teddi]

[QUOTE=NiandraLades;44137163]Hey, has anyone else experienced server crashing because of this module? We need it for one of our addons, but since doing so we've been having occasional crashes, which usually happen on map change or map vote.[/QUOTE]

Are you running any queries specifically in a ShutDown hook?

[NiandraLades]

[QUOTE=Teddi Orange;44141456]Are you running any queries specifically in a ShutDown hook?[/QUOTE]

Only this:

hook.Add("ShutDown", "achievement_SaveAllPlayers", function()
	achievement:SaveAllPlayers()
end)

As you can probably guess, the addon's an achievement system. I think I've heard before that if sql drops, the server can crash. Could that be related to my problem?

[ARitz Cracker]

How long can the queries be? ... I might have to send one a that's around 32kb long.

[[GG]Super]

I have this on my little sandbox server and I've tried using this with pointshop and while I get confirmation from pointshop that the server has connected to the mysql server, nothing is written to the table on the mysql server.
This also happens with the mysql on an inventory mod as well as other things that use mysql. Does anyone have any idea what it could be?

[Revenge282]

[QUOTE=LegoGuy;44180960]How long can the queries be? ... I might have to send one a that's around 32kb long.[/QUOTE]

Just out of curiosity, what query could you possible construct that is that massive?