How to obtain async dbPoll result?

hendawh · November 5, 2023

Hi, I want to create a function to execute async SQL queries. However, I have a problem with getting the return values of dbPoll().

Code snippet for better understanding:

function db_query(...)
    if (db_settings.db_connection) then
        dbQuery(function(query_handle)
            local result, num_affected_rows, last_insert_id = dbPoll(query_handle, 0)
            return result, num_affected_rows, last_insert_id
        end, db_settings.db_connection, ...)
    end
end

For example, I want to use this function somewhere else in the script, but of course it will not return the value, because it executes faster than the values are retrieved from the database.

addCommandHandler("reg", function(player, command, ...)
    local args = {...}

    local result, num_affected_rows, last_insert_id = exports.database_connection:db_query("SELECT * FROM `players`")
    outputDebugString(tostring(num_affected_rows))
end)

I tried to use triggerEvent for this, but I don't quite understand how I'm supposed to use it later to get the return values the way I mentioned above. Any help?

IIYAMA · November 5, 2023

49 minutes ago, hendawh said:

Any help?

The following examples are part of a project I have build before.

You will have to set-up:

2 export function (one each resource)
- For sending (as you have now)
- And for receiving

You will have to generate an unique identifier for each query, so that each request can be wired back to where you want the data.

-- Resource A
Identifier = {}
function Identifier:newGenerator() return setmetatable( { index = 0 }, { __index = Identifier } ) end
function Identifier:generate()
  local index = self.index + 1
  self.index = index
  return "id:" .. getTickCount() .. "|" .. index
end

-- For new ID's
local identifierGenerator = Identifier:newGenerator()

-- function ()

	local id = identifierGenerator:generate()

-- end

Before you send a request you make sure that there is a destination:

-- Resource A
callbacks = {}
-- function ()
	local id = identifierGenerator:generate()
	
	-- Set-up the callback destination, anonymous func if you want to keep going where you left of.
	callbacks[id] = function (...)
  		iprint(...)
	end
	exports.database_connection:db_query(id, "SELECT * FROM `players`")
	

-- end

Add wiring for receiving and calling back your callback function:

-- Resource A

function dbResponse (id, ...)
	if not callbacks[id] then return end
	callbacks[id](...)
end

Keep the wiring going in the db resource, so that the ID goes ping pong:

-- Resource B
function db_query(id, ...)
	-- The source resource that made this call (important to rename the pre-defined variable, else it will be lost)
	local theSourceResource = sourceResource
  
	if (db_settings.db_connection) then
		dbQuery(function(query_handle)
			local result, num_affected_rows, last_insert_id = dbPoll(query_handle, 0)


			call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

		end, db_settings.db_connection, ...)
	end
end

kkuba · November 6, 2023

Basically u can export your database connection via global call to exported function which will return connection handle and later u use it for dbQuery and handling data from async dbPoll.

hendawh · November 7, 2023

On 05/11/2023 at 23:50, IIYAMA said:

The following examples are part of a project I have build before.

You will have to set-up:

2 export function (one each resource)
- For sending (as you have now)
- And for receiving

You will have to generate an unique identifier for each query, so that each request can be wired back to where you want the data.

-- Resource A
Identifier = {}
function Identifier:newGenerator() return setmetatable( { index = 0 }, { __index = Identifier } ) end
function Identifier:generate()
  local index = self.index + 1
  self.index = index
  return "id:" .. getTickCount() .. "|" .. index
end

-- For new ID's
local identifierGenerator = Identifier:newGenerator()

-- function ()

	local id = identifierGenerator:generate()

-- end

Before you send a request you make sure that there is a destination:

-- Resource A
callbacks = {}
-- function ()
	local id = identifierGenerator:generate()
	
	-- Set-up the callback destination, anonymous func if you want to keep going where you left of.
	callbacks[id] = function (...)
  		iprint(...)
	end
	exports.database_connection:db_query(id, "SELECT * FROM `players`")
	

-- end

Add wiring for receiving and calling back your callback function:

-- Resource A

function dbResponse (id, ...)
	if not callbacks[id] then return end
	callbacks[id](...)
end

Keep the wiring going in the db resource, so that the ID goes ping pong:

-- Resource B
function db_query(id, ...)
	-- The source resource that made this call (important to rename the pre-defined variable, else it will be lost)
	local theSourceResource = sourceResource
  
	if (db_settings.db_connection) then
		dbQuery(function(query_handle)
			local result, num_affected_rows, last_insert_id = dbPoll(query_handle, 0)


			call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

		end, db_settings.db_connection, ...)
	end
end

I saw your project, but I couldn't quite understand what was going on. Thanks a lot for explaining it.

hendawh · November 7, 2023

On 05/11/2023 at 23:50, IIYAMA said:

The following examples are part of a project I have build before.

You will have to set-up:

2 export function (one each resource)
- For sending (as you have now)
- And for receiving

You will have to generate an unique identifier for each query, so that each request can be wired back to where you want the data.

-- Resource A
Identifier = {}
function Identifier:newGenerator() return setmetatable( { index = 0 }, { __index = Identifier } ) end
function Identifier:generate()
  local index = self.index + 1
  self.index = index
  return "id:" .. getTickCount() .. "|" .. index
end

-- For new ID's
local identifierGenerator = Identifier:newGenerator()

-- function ()

	local id = identifierGenerator:generate()

-- end

Before you send a request you make sure that there is a destination:

-- Resource A
callbacks = {}
-- function ()
	local id = identifierGenerator:generate()
	
	-- Set-up the callback destination, anonymous func if you want to keep going where you left of.
	callbacks[id] = function (...)
  		iprint(...)
	end
	exports.database_connection:db_query(id, "SELECT * FROM `players`")
	

-- end

Add wiring for receiving and calling back your callback function:

-- Resource A

function dbResponse (id, ...)
	if not callbacks[id] then return end
	callbacks[id](...)
end

Keep the wiring going in the db resource, so that the ID goes ping pong:

-- Resource B
function db_query(id, ...)
	-- The source resource that made this call (important to rename the pre-defined variable, else it will be lost)
	local theSourceResource = sourceResource
  
	if (db_settings.db_connection) then
		dbQuery(function(query_handle)
			local result, num_affected_rows, last_insert_id = dbPoll(query_handle, 0)


			call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

		end, db_settings.db_connection, ...)
	end
end

You mentioned that I should prepare two functions (including one in a separate resource) and I don't understand what you meant. I want to perform a query on a resource (e.g. player login) - where should I have the function responsible for receiving query results? (spreading it into two resources: res_database, res_auth)

IIYAMA · November 7, 2023

19 hours ago, hendawh said:

You mentioned that I should prepare two functions (including one in a separate resource) and I don't understand what you meant.

A - Resource requesting data

B - Database manager

A > B

The first export function is db_query, located in resource B, which you have already created.

-- Resource A makes this call to resource B
exports.database_connection:db_query(id, "SELECT * FROM `players`")

<!-- Resource B -->
<export function="db_query" type="server" />

Spoiler

-- Resource B
function db_query(id, ...)
	-- The source resource that made this call (important to rename the pre-defined variable, else it will be lost)
	local theSourceResource = sourceResource
  
	if (db_settings.db_connection) then
		dbQuery(function(query_handle)
			local result, num_affected_rows, last_insert_id = dbPoll(query_handle, 0)


			call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

		end, db_settings.db_connection, ...)
	end
end

B > A

The second one is dbResponse, located in resource A, which is where the response is returned to, with:

-- Resource B makes this call to resource A
call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

<!-- Resource A -->
<export function="dbResponse" type="server" />

-- Resource A
function dbResponse (id, ...)
	if not callbacks[id] then return end
	callbacks[id](...)
end

Not sure if you want to keep your function naming style, else it will be db_response ofcourse.

hendawh · November 8, 2023

18 hours ago, IIYAMA said:

A - Resource requesting data

B - Database manager

A > B

The first export function is db_query, located in resource B, which you have already created.

-- Resource A makes this call to resource B
exports.database_connection:db_query(id, "SELECT * FROM `players`")

<!-- Resource B -->
<export function="db_query" type="server" />

Reveal hidden contents

-- Resource B
function db_query(id, ...)
	-- The source resource that made this call (important to rename the pre-defined variable, else it will be lost)
	local theSourceResource = sourceResource
  
	if (db_settings.db_connection) then
		dbQuery(function(query_handle)
			local result, num_affected_rows, last_insert_id = dbPoll(query_handle, 0)


			call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

		end, db_settings.db_connection, ...)
	end
end

B > A

The second one is dbResponse, located in resource A, which is where the response is returned to, with:

-- Resource B makes this call to resource A
call( theSourceResource, "dbResponse", id,  result, num_affected_rows, last_insert_id )

<!-- Resource A -->
<export function="dbResponse" type="server" />

-- Resource A
function dbResponse (id, ...)
	if not callbacks[id] then return end
	callbacks[id](...)
end

Not sure if you want to keep your function naming style, else it will be db_response ofcourse.

From what you wrote I understand that I should create and export the dbResponse() function in each resource that performs a database query at some point?

IIYAMA · November 8, 2023

51 minutes ago, hendawh said:

From what you wrote I understand that I should create and export the dbResponse() function in each resource that performs a database query at some point?

Yes, in every resource that is receiving data from the database manager, you should add an export function with the name dbResponse.
(it is important that the name 'dbResponse' is used consistent)

Sign In

How to obtain async dbPoll result?

Recommended Posts

hendawh

Link to comment

IIYAMA

Link to comment

kkuba

Link to comment

hendawh

Link to comment

hendawh

Link to comment

IIYAMA

Link to comment

hendawh

Link to comment

IIYAMA

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

Activity

MTA Network

Social Media