Lua Lua db.query not working

[cryptomeepo]

Trying to update DB when use item but DB is not updating:

Actions:

function onUse(player, item, fromPosition, itemEx, toPosition)
db.query("UPDATE table SET account_id = '..player:getAccountId()..'")
db.query("UPDATE table SET number = '01'")
player:sendTextMessage(MESSAGE_EVENT_ADVANCE, 'You sent 01 to your account.')
player:getPosition():sendMagicEffect(CONST_ME_MAGIC_RED)
Item(item.uid):remove(1)
return true
end

I missed something?

 

[Evil Puncker]

depending on which table you are trying to update, it will only work if the character is offline

 

[cryptomeepo]

I created a new table for that. How do I know if the table is able to update when character online?

 

[Gesior.pl]

I created a new table for that. How do I know if the table is able to update when character online?

You can't update most of columns in tables that store information about player.

I don't know, if forum added some invalid characters to your query, but it looks like this:

db.query("UPDATE `table` SET account_id = '..player:getAccountId()..'")

1. You mixed ' and " in your string and it tries to set account_id to value ..player:getAccountId().. - yes, this text! It's not reading player account and pass ID (number) to query.
It should be:

db.query('UPDATE `table` SET account_id = ' .. player:getAccountId())

2. You are running UPDATE query without WHERE, which means it will update all rows in that table.

 

[cryptomeepo]

Yes you are right. I'm sorry still learning.

I changed to:

db.query('INSERT INTO table SET account_id = ' .. player:getAccountId())
db.query("INSERT INTO table SET number = '01';")

But it is filling account id in one row and number in another row (obviously). Would you help me fix that? Need both in the same row.

I don't want to use WHERE on number because the account_id won't be unique. Player can have more than one number in his account.

 

[Evil Puncker]

you need to add an if so it runs insert into if value doesn't exist, or update if it exist

 

[cryptomeepo]

Well I tried it this way. No error until I use the item:

function onUse(player, item, fromPosition, itemEx, toPosition)
db.query('INSERT INTO table (account_id, number) VALUES ('..player:getAccountId()', 01)')
player:sendTextMessage(MESSAGE_EVENT_ADVANCE, 'You sent 01 to your account.')
player:getPosition():sendMagicEffect(CONST_ME_MAGIC_RED)
Item(item.uid):remove(1)
return true
end

When use item:

Lua Script Error: [Action Interface]
data/actions/scripts/test/01.luanUse
data/actions/scripts/test/01.lua:2: attempt to call a number value
stack traceback:
[C]: at 0x7ff651213560
data/actions/scripts/test/01.lua:2: in function <data/actions/scripts/test/01.lua:1>

If I change ('..player:getAccountId()', 01)') for (01,02)') works just fine.

 

[cryptomeepo]

Tried million different ways. Everytime I use those kind of functions ..player:getAccountId() in the middle of the sentence I get this error:

attempt to call a number value
stack traceback:

 

[Nekiro]

1. Programming in Lua : 3.4 (https://www.lua.org/pil/3.4.html)
2. SQL INSERT INTO Statement (https://www.w3schools.com/sql/sql_insert.asp)
good luck

 

[cryptomeepo]

Do you see any error here?

db.query("INSERT INTO table (number, account_id) VALUES (01," ..player:getAccountId(cid)")")

If I'm asking for help is because I've already look into this tons of times and can't find.

Why we always feel like we are bothering people when asking something in this forum?

 

[cryptomeepo]

I got it.

local code = 01
function onUse(player, item, fromPosition, itemEx, toPosition)
local accId= player:getAccountId(cid)
db.query("INSERT INTO table SET time = "..getWorldTime())
db.query("UPDATE table SET number = "..code.." WHERE time=" ..getWorldTime())
db.query("UPDATE table SET account_id = "..accId.." WHERE time=" ..getWorldTime())
player:sendTextMessage(MESSAGE_EVENT_ADVANCE, 'You sent 01 to your account.')
player:getPosition():sendMagicEffect(CONST_ME_MAGIC_RED)
Item(item.uid):remove(1)
return true
end

 

[samco]

I got it.

Your code is not 100% correct. You should store the getWorldTime in a variable before using it in the querys, otherwise it could be a case where the first getWorldTime is not the same as the 2nd or 3rd.

 

[cryptomeepo]

Looks like its working thou. But thank you I'm going to look into that.

 

[pink_panther]

Firing 3 seperate queries at once, yuck.

Doing that a lot will see lag on the server as it locks and unlocks the database to do that...

You should try update your query in 1.


Also a chance getWorldTime is different in all 3 entries if you do it like this. It's quite bad. You should set getWorldTime to a variable first at the very least.

 

[samco]

Firing 3 seperate queries at once, yuck.

Doing that a lot will see lag on the server as it locks and unlocks the database to do that...

You should try update your query in 1.


Also a chance getWorldTime is different in all 3 entries if you do it like this. It's quite bad. You should set getWorldTime to a variable first at the very least.

Thats true, you should also optimize the queries to only execute one.

 

[cryptomeepo]

local code = 3
function onUse(player, item, fromPosition, itemEx, toPosition)
local accId = player:getAccountId(cid)
db.query("UPDATE table SET account_id = "..accId.." WHERE id=" ..code)
db.query("UPDATE table a INNER JOIN accounts b ON a.account_id = b.id SET a.address = b.adress WHERE a.id =" ..code)
player:sendTextMessage(MESSAGE_EVENT_ADVANCE, 'You sent '..code..' to your account.')
player:getPosition():sendMagicEffect(CONST_ME_MAGIC_RED)
Item(item.uid):remove(1)
return true
end

I changed time for the local code. Its all working just fine but I'm trying to put it in one query as you guys suggested. I'll update this post when I get it. Any ideas?
Thanks for your help.