LuaSQL connection syntax

[kyleflow 35]

Can anyone validate if this should work if I tried to print in GS console if the connection is a success or not. Is there a more details syntax I need to include. I put this inside init.lua by trying to implement the LuaSQL.dll

SQLConnector = {};
SQLConnector.sql = { host = "KHAIRUL\TEST14", user = "sa", pass = "Y87dc#$98", db = "tradedb" };
	if SQLConnector.sql == 1 then
		print("Connection successful!")
	else
		print("Connection failed!")
	end

The current return in GS console said Connection failed!

Edited June 12, 2023 by kyleflow
result

[champ 147]

I've not used LuaSQL before but based on your code you are only creating a table that holds the necessary connection information to an SQL server. So, to put this into perspective, you have basically defined what is needed to connect to the SQL Server but not how to do it.

You have this condition:

if SQLConnector.sql == 1 then

 

It fails because you cannot compare your lua table to a number and it will always return False, which in your case equates to "print("Connection failed!")".

 

I have just done some research and found the following docs:
 

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
**************************************************************************************************

 

Based on the docs above, we can try to fix your code (might not work so please revise as needed):

local SQLConnector = {
    host = "KHAIRUL\TEST14",
    user = "sa",
    pass = "Y87dc#$98"
}
local query = "SELECT * FROM some_table"

-- connect to the database
local connHandleId = LuaSql("connect", SQLConnector.host, SQLConnector.user, SQLConnector.pass)
if connHandleId <= 0 then
    print("Failed to connect to the database.")
end

-- run a query
local stmtHandleId = LuaSql("query", connHandleId, query)
if stmtHandleId <= 0 then
    print("Failed to run the query.")
end

-- fetch data
local rowset = LuaSql("fetch", connHandleId, stmtHandleId)
if not rowset then
    print("Failed to fetch data.")
end

-- free the statement handle
local result = LuaSql("freehandle", connHandleId, stmtHandleId)
if result ~= 0 then
    print("Failed to free statement handle.")
end

-- close the connection
local result = LuaSql("close", connHandleId)
if result ~= 0 and result ~= 1 then
    print("Failed to close the connection.")
end

-- do something with rowset
print(rowset)

 

[kyleflow 35]

6 hours ago, champ said:

I've not used LuaSQL before but based on your code you are only creating a table that holds the necessary connection information to an SQL server. So, to put this into perspective, you have basically defined what is needed to connect to the SQL Server but not how to do it.

You have this condition:

if SQLConnector.sql == 1 then

 

It fails because you cannot compare your lua table to a number and it will always return False, which in your case equates to "print("Connection failed!")".

 

I have just done some research and found the following docs:
 

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
**************************************************************************************************

 

Based on the docs above, we can try to fix your code (might not work so please revise as needed):

local SQLConnector = {
    host = "KHAIRUL\TEST14",
    user = "sa",
    pass = "Y87dc#$98"
}
local query = "SELECT * FROM some_table"

-- connect to the database
local connHandleId = LuaSql("connect", SQLConnector.host, SQLConnector.user, SQLConnector.pass)
if connHandleId <= 0 then
    print("Failed to connect to the database.")
end

-- run a query
local stmtHandleId = LuaSql("query", connHandleId, query)
if stmtHandleId <= 0 then
    print("Failed to run the query.")
end

-- fetch data
local rowset = LuaSql("fetch", connHandleId, stmtHandleId)
if not rowset then
    print("Failed to fetch data.")
end

-- free the statement handle
local result = LuaSql("freehandle", connHandleId, stmtHandleId)
if result ~= 0 then
    print("Failed to free statement handle.")
end

-- close the connection
local result = LuaSql("close", connHandleId)
if result ~= 0 and result ~= 1 then
    print("Failed to close the connection.")
end

-- do something with rowset
print(rowset)

 

Noted will try this out. Thanks for the intensive details you provide. Hope I can work this out somehow. thanks a lot. Might need further explanation later.