Jump to content
Xeon

LuaSQL (DLL)

Recommended Posts

Just now, V3ct0r said:

@Xeon, @Wrexor

Hello! Do you can write a small guide how to compile it and what functions we can use in server LUA? With examples

Isn't is with CFF Explorer?

Share this post


Link to post
Share on other sites
3 hours ago, Dimitrov said:

Isn't is with CFF Explorer?

Thats only for adding DLL to gameserver, still have to register new functions.

Share this post


Link to post
Share on other sites
On 7/8/2016 at 5:30 AM, V3ct0r said:

@Xeon, @Wrexor

Hello! Do you can write a small guide how to compile it and what functions we can use in server LUA? With examples

For documentation:

 

LUA_ARG_ERROR = 10

SQL_SUCCESS = 0
SQL_SUCCESS_WITH_INFO = 1
SQL_ERROR = -1
SQL_INVALID_HANDLE = -2
SQL_NO_DATA = 100

**************************************************************************************************
connect function

Summary:
Establishes connection to SQL Server driver.

Syntax:
LuaSql("connect", host, username, password)

Arguments:
host - SQL Server address
username - SQL Server username
password - SQL Server password

Returns:
1 on success, 0 on failure,
connection handle id on success else LUA_ARG_ERROR, SQL_ERROR, or SQL_INVALID_HANDLE.
**************************************************************************************************
query function

Summary:
Submits SQL statement

Syntax:
LuaSql("query", connHandleId, query)

Arguments:
connHandleId - Connection handle id.
query - Query string

Returns:
1 on success, 0 on failure,
statement handle id on success else LUA_ARG_ERROR, SQL_ERROR, or SQL_INVALID_HANDLE.
**************************************************************************************************
fetch function

Summary:
Fetches the next rowset of data from the result set  and returns data for all columns.

Syntax:
LuaSql("fetch", connHandleId, stmtHandleId)

Arguments:
connHandleId - Connection handle id.
stmtHandleId - Statement handle id.

Returns:
rowset on success else LUA_ARG_ERROR, SQL_NO_DATA, SQL_ERROR, or SQL_INVALID_HANDLE.
**************************************************************************************************
freehandle function

Summary:
Frees resources associated with a specific statement handle.

Syntax:
LuaSql("freehandle", connHandleId, stmtHandleId)

Arguments:
connHandleId - Connection handle id.
stmtHandleId - Statement handle id.

Returns:
LUA_ARG_ERROR, SQL_SUCCESS, SQL_ERROR, or SQL_INVALID_HANDLE.
**************************************************************************************************
close function

Summary:
Closes the connection and frees resources associated with a specific connection handle

Syntax:
LuaSql("close", handleId)

Arguments:
handleId - Connection handle id.

Returns:
LUA_ARG_ERROR, SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
**************************************************************************************************
QueryAsyn

Summary:
Executes query asynchrounously

Syntax:
QueryAsyn(host, user, pass, query)

Arguments:
host - SQL Server address
username - SQL Server username
password - SQL Server password
query - Query string
**************************************************************************************************

  • Like 1

Share this post


Link to post
Share on other sites

1) If you do not use the MSSQL 2000 when inserting the host using double backslashes.

 

Example:

local host = 'PCNAME-PC\\SQLEXPRESS'

 

2) The paths of LuaSQL and ExecuteQueryAsync functions are not sufficient to add the functions to be performed on Lua files.

PushPatch(&LuaSql, (PBYTE)0x5A5FD2);
PushPatch(&ExecuteQueryAsync, (PBYTE)0x5A5EFE);

 

Edited by GustavoHMA

Share this post


Link to post
Share on other sites
Guest
1 hour ago, Vasil said:

Love you Xeon :x:x dis is so cool .

Was made by Wrexor in case you didn't know, Xeon has only released it.

Share this post


Link to post
Share on other sites

I got this error :(

 

sshot-141.png

 

And if i add the missing file i get this

 

sshot-142.png

 

I am using Windows Server 2012 R2, do i need to install a extra files?

 

 

 

Edited by Covadola
  • Like 1

Share this post


Link to post
Share on other sites
26 minutes ago, Covadola said:

I got this error :(

 

sshot-141.png

 

And if i add the missing file i get this

 

sshot-142.png

 

I am using Windows Server 2012 R2, do i need to install a extra files?

 

 

 

Ill upload new version which will include that file later today.

  • Like 3

Share this post


Link to post
Share on other sites

In link below there is an updated version of LuaSQL which should include all required files and its also registering lua functions automatically so after you compile or just copy already compiled dll just follow attaching guide in the first post on this topic.

If you want to compile yourself be sure to select correct configuration depending on your server version, currently only 1.36 and 1.38 are supported.

If someone wants a different version send me a pm.

https://www.dropbox.com/s/3byhodvw2z26ef8/LuaSQL2.rar?dl=0

  • Like 5

Share this post


Link to post
Share on other sites
1 hour ago, Wrexor said:

In link below there is an updated version of LuaSQL which should include all required files and its also registering lua functions automatically so after you compile or just copy already compiled dll just follow attaching guide in the first post on this topic.

If you want to compile yourself be sure to select correct configuration depending on your server version, currently only 1.36 and 1.38 are supported.

If someone wants a different version send me a pm.

https://www.dropbox.com/s/3byhodvw2z26ef8/LuaSQL2.rar?dl=0

 

I think could be a nice idea release it for all existing server versions (1.x - 2.x)

Share this post


Link to post
Share on other sites
11 minutes ago, Giampy said:

 

I think could be a nice idea release it for all existing server versions (1.x - 2.x)

I would do that but problem is that every gameserver has functions at a different location and I have to manually find them.

If someone wants this to work on gameserver which currently isnt supported he can send me his gameserver.exe and I will add it.

  • Like 1

Share this post


Link to post
Share on other sites
On 10/12/2016 at 2:35 PM, Wrexor said:

I would do that but problem is that every gameserver has functions at a different location and I have to manually find them.

If someone wants this to work on gameserver which currently isnt supported he can send me his gameserver.exe and I will add it.

Can you update this to work on a v2.x GameServer? If so, I can send you my GS via PM.

Share this post


Link to post
Share on other sites
6 hours ago, Angelix said:

Can you update this to work on a v2.x GameServer? If so, I can send you my GS via PM.

I already updated it but there is a problem with v2.x gameservers. I have to edit your gameserver to load dll in a different way. Upload it so I can edit it.

Share this post


Link to post
Share on other sites
13 hours ago, Wrexor said:

Seems to work fine, thanks! :D

 

The DLL shows up when loading GS and I was able to do this:

function CheckConnection()
	if LuaSQL("connect", SQL.GameDB.Host, SQL.GameDB.User, SQL.GameDB.Password) == 1 then
		print("Connection successful!")
	else
		print("Connection failed!")
	end
end
CheckConnection()

The result is a successful connection! 

 

Now I have to learn a bit of SQL, haha.

Share this post


Link to post
Share on other sites

Quick question: How would I go on about getting an account ID based on a player's name?

 

I would have to use the "LuaSQL("fetch", connection, statement)", but how do I use that?

Share this post


Link to post
Share on other sites
11 hours ago, Angelix said:

Quick question: How would I go on about getting an account ID based on a player's name?

 

I would have to use the "LuaSQL("fetch", connection, statement)", but how do I use that?

First you have to make a connection to the SQL Server. LuaSQL will return 2 values when connecting, first value is true/false which depends if connection was successful or not, second value is handle id on successful connection or error number if it was unsuccessful.

success, handle = LuaSQL("connect", host, username, password)

If connection succeeded then you can query the SQL Server. First argument is handle returned when connecting to SQL Server, second arguments is the SQL Query which would in your case be "SELECT act_id FROM character WHERE cha_name = '" . player_name . "'". Again you get 2 returns values which are similar to previous return values, first indicates success/failure, second is handle id on success and error number on failure.

success, query = LuaSQL("query", handle, query)

If query succeded you can fetch data. First argument is connection handle id returned when connecting to SQL Server, second argument is query handle id returned when executing query. Here you only get 1 return value which is type 'table' if command succeded otherwise it's type 'number' which corresponds to error number.

data = LuaSQL("fetch", handle, query)

If everything went ok you should be able to see account ID in data variable.

After this you should also free query handle by calling LuaSQL with "freehandle" argument.

success = LuaSql("freehandle", handle, query)

You can find other information in post by @KONG here.

Share this post


Link to post
Share on other sites

@Wrexor that helped a lot! I didn't quite understand KONG's explanation and I though the functions only returned one value, that's where I was wrong since I didn't quite get the return of two values. Now with that and your help, I was able to get my account name. :P

 

function GetAccountName()
	print("--------------------------------------------------")
	local String = "SELECT act_name FROM GameDB.dbo.account WHERE act_id IN (SELECT act_id FROM GameDB.dbo.character WHERE cha_name = 'Angelix')"
	local Connect, ConnectID = LuaSQL("connect", SQL.GameDB.Host, SQL.GameDB.User, SQL.GameDB.Password)
	if Connect == SQL_SUCCESS_WITH_INFO then
		print("Successful connection.")
		local Success, Query = LuaSQL("query", ConnectID, String)
		if Success == SQL_SUCCESS_WITH_INFO then
			print("Successful query.")
			local Data = LuaSQL("fetch", ConnectID, Query)
			print("Account Name = "..Data["act_name"])
			LuaSQL("freehandle", ConnectID, Query)
			LuaSQL("close", ConnectID)
			return
		end
	end
	print("--------------------------------------------------")
end
GetAccountName()

Which for testing purposes resulted this on GS console. :P

Capture.PNG

 

Thanks a lot on both of you for the explanations and Vector since I got the query command from him on another thread since I have zero knowledge of SQL, hahah.

Edited by Angelix

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×