[paid if helped]Unable to return value from database to ingame

[Calamity 0]

Hi, I'm working on something and for it to work in the manner that i require it. I need it to return a value from the database. It always returns a value of 0.

 

here's the function

function total_points(x)
local z = os.execute("mysql -u root account --execute='SELECT `points` FROM account.account WHERE id = ".. pc.get_account_id() ..";'")
return z
end

 

I've tested the query in navicat and it works fine, i can insert ints into the database but not strings??? 

 

 

i've tried using a mysql_query function but it wouldnt return any value. if someone has a working mysql_query function simlair to this, i'd appreciate if you could post it here as i could have used the wrong syntax for that.

 

any help is appreciated. 

 

I can't find any information on this so the first person to solve it gets 15 euros via Paypal, but i must be able to select any information and insert any rather than just inserting int's .

 

information - i'm on 34k

server files -  im using are macross's

[ATAG 309]

 

os.execute ([command])

This function is equivalent to the ANSI C function system. It passes command to be executed by an operating system shell. Its first result is true if the command terminated successfully, or nil otherwise. After this first result the function returns a string and a number, as follows:

    "exit": the command terminated normally; the following number is the exit status of the command.

    "signal": the command was terminated by a signal; the following number is the signal that terminated the command.

When called without a command, os.execute returns a boolean that is true if a shell is available.

You have to use a mysql_query function...

 

eg:

function mysql_query(text)
	local tmp=number(11111111,99999999)
	os.execute('mysql -u root -pPASSWORD -N -e '..string.format("%q",text)..' 2>&1 > /tmp/'..tmp)
	local res,i={},1
	local f,e=io.open("/tmp/"..tmp)
	if f then
		local line=f:read("*l")
		while line do
			res[i]={}
			string.gsub(line,"([^t]+)t*", function(s)
				table.insert(res[i],s)
			end)
			i=i+1
			line=f:read("*l")
		end
		f:close()
		os.execute("rm /tmp/"..tmp)
	end
	--[[if table.getn(res)==1 and table.getn(res[1])==1 then --only 1 variable returns
		res=res[1][1]
	end]]
	return res
end

And how-to use in quest:

--by ATAG
quest sqltest begin
	state start begin
		when 20016.chat."test list" begin
			local tbl=mysql_query("SELECT id, account_id, name from player.player LIMIT 10;")
			say("userlist: "..table.getn(tbl))
			for i,v in ipairs(tbl) do
				say(v[1].." "..v[2].." "..v[3])
			end
			say("")
		end
	end
end

[Calamity 0]

output from using the exact quest provided 

 

 

Edited August 16, 2022 by Metin2 Dev
Core X - External 2 Internal

[ATAG 309]

Some mysqlclient version requires single dash, others need double dashes in the mysql options.

[Calamity 0]

modified the post above with the output

[ATAG 309]

What is the output if you enter the command directly into console?

 

mysql -u root -p -N -e "SELECT id, account_id, name from player.player LIMIT 10;"

 

 

[Invictus 24]

ummm not sure but i have the same issue

[Calamity 0]

when i entered it directly into putty, i got the correct output as one would expect

[ATAG 309]

Try this one, maybe you get some information:

function mysql_query(text)
    local tmp=number(11111111,99999999)
    os.execute('mysql -u root -pPASSWORD -N -e '..string.format("%q",text)..' 2>&1 > /tmp/'..tmp)
    local res,i={},1
    local f,e=io.open("/tmp/"..tmp)
    if f then
        local line=f:read("*l")
        while line do
            res[i]={}
            string.gsub(line,"([^t]+)t*", function(s)
                table.insert(res[i],s)
            end)
            i=i+1
            line=f:read("*l")
        end
        f:close()
        os.execute("rm /tmp/"..tmp)
    else
        res[1] = {(e or "WTF??")}
    end
    return res
end

or remove the os.execute("rm /tmp/"..tmp) and check the random number named file in /tmp

[Calamity 0]

ok got it working, appreciated.

 

so how would i say

 

local totalX=mysql_query("SELECT points, FROM account.account WHERE id = ".. pc.get_account_id() ..";")
chat(totalX)

after i know this bit i can pay you the money, as then it'll all be solved

 

 

did test this but i cant see the syntax error :/

[Denis 1458]

ok got it working, appreciated.

 

so how would i say

 

local totalX=mysql_query("SELECT points, FROM account.account WHERE id = ".. pc.get_account_id() ..";")
chat(totalX)

after i know this bit i can pay you the money, as then it'll all be solved

 

 

did test this but i cant see the syntax error :/

 

Use this:

local totalX=mysql_query("SELECT points FROM account.account WHERE id = ".. pc.get_account_id() ..";")
chat(totalX.points[1])

 

[ATAG 309]

chat(totalX[1][1])

 

returns an indexed table. totalX[1] is the forst row, totalX[2] is 2nd, etc...

[1][1] is the first column in first roe, [1][2] is 2nd etc...