Reading global account variables

[Bifrost]

I'm trying to implement a housing system for my server that allows me as an admin to add house owners through the NPC menu.

When the NPC successfully checks for the admin group_id, they are allowed to input an integer (account_id of a player) that is then passed on as variable to the SQL server.

T_Add:
input .@aid;
if (.@aid <= 0) goto T_Fail;
if (.@aid > 2099999) goto T_Fail;
if (.@aid < 2000000) goto T_Fail;
if (.@aid != 0){
query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','owner','1','1','"+.@aid+"')";
}

The SQL query works fine and is passed to the server without problems. However, the account which is supposed to be a houseowner now is not detected by the NPC. I check for the variable (##owner) at the beginning of the script.

if(##owner==1) goto T_Owner;

I have tried many variations of this, but it seems none of them are working. The account is always detected as non-houseowner. Could someone tell me what I'm doing wrong?

[en_dev]

Can I see the full script ?, I just wanna see where ##owner is come from or being set.

[Bifrost]

Can I see the full script ?, I just wanna see where ##owner is come from or being set.

The sql_query in my first snippet inputs the owner variable into the global_reg_value table. I'm trying to read it from there.

I got it working after trying to fix it for a few hours. I have no idea how I did it, but here is the complete script (placeholder warp locations) :

prontera,162,188,3 script Butler 862,{
if (getgroupid() == 99) goto T_GM;
set .@accid, getcharid(3);
query_sql "SELECT `account_id` FROM `global_reg_value` WHERE `str`='##owner' AND `account_id`='"+.@accid+"' AND `value`='1'", .@accid2;
if (.@accid == .@accid2){
mes "[butler]";
mes "Would you like to visit your house?";
next;
menu "Yes",T_Warp2, "Visit all houses",T_Warp, "No",quit;
next;
close;
}
mes "[butler]";
mes "I see you do not yet own a house. Are you interested in buying one?";
next;
menu "Tell me more",T_More,"No",quit, "Warp me to the houses",T_Warp;
next;
T_More:
mes "[butler]";
mes "Houses allow you to have some private space on the server with your own NPCs.";
mes "They are available to all characters on your account and you're allowed to";
mes "bring a friend to your house aswell, however, they will not be able to use";
mes "any of the offered NPC services.";
next;
mes "[butler]";
mes "Houses are largely customizable so no house will be the same.";
mes "We also offer guild houses for a larger group of players, however,";
mes "this service will cost a lot more.";
next;
mes "[butler]";
mes "If you would like to buy a house, please check the forum post by Odin.";
close;

T_Warp:
warp "himinn",48,82;
close;
T_Warp2:
warp "prt_in",95,169;
close;
T_GM:
mes "Hi.";
next;
menu "Add house owner",T_Add, "Close",quit;
next;
T_Add:
input .@aid;
if (.@aid <= 0) goto T_Fail;
if (.@aid > 2099999) goto T_Fail;
if (.@aid < 2000000) goto T_Fail;
if (.@aid != 0){
query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','##owner','1','1','"+.@aid+"')";
}
end;
close;
T_Fail:
mes "[butler]";
mes "Invalid Account ID";
close;

quit:
close;
}

Edited June 8, 2012 by Bifrost

[QQfoolsorellina]

Why not use set to specify variable value?

set ##owner,1;

Edited June 8, 2012 by QQfoolsorellina

[Bifrost]

Why not use set to specify variable value?

set ##owner,1;

Set would specify the variable for my own account, however, I need to specify the value for other accounts.

[Euphy]

I've run into a lot of problems with this, myself. The issue is that most (or all?) variables do not update when added/modified through a direct SQL query, and are only reloaded through @reloadscript or on a reboot. (And if you're ever feeling masochistic, try updating guild tables through SQL queries. They're are a nightmare. x_x)

Workaround: Attach RID to the player you need, then trigger a script using "set" from there. This will only work if the player is online, of course. I can't think of any other method.

Edit: Actually, you could keep a permanent global array of characters (names or IDs) you want to add variables to, and loop through the list OnPCLoginEvent, deleting the variable and shifting the array back if there's a match.

[Bifrost]

I've run into a lot of problems with this, myself. The issue is that most (or all?) variables do not update when added/modified through a direct SQL query, and are only reloaded through @reloadscript or on a reboot. (And if you're ever feeling masochistic, try updating guild tables through SQL queries. They're are a nightmare. x_x)

Workaround: Attach RID to the player you need, then trigger a script using "set" from there. This will only work if the player is online, of course. I can't think of any other method.

Edit: Actually, you could keep a permanent global array of characters (names or IDs) you want to add variables to, and loop through the list OnPCLoginEvent, deleting the variable and shifting the array back if there's a match.

I haven't scripted in athena for a few years - and I have never been good at it - so I have no idea what you're saying. Thanks for the input though, I appreciate it.

[Mooka]

Two points,

1. Check if the table have more than 1 result for the condition. You didn't check if value already exist to use the UPDATE command. This is necessary.

2. Maybe, the variable is being overridden when the map-server execute your "Save routine". This occours when player is online and you use a query to alter values in global_reg_value.

Try use attachrid to set variable when player is online, this should fix your problem.

Edited June 8, 2012 by Mooka

[QQfoolsorellina]

try this

T_Add:
input .@aid,2000000,2005000;
if(!query_sql("SELECT account_id FROM `login` WHERE account_id ="+.@aid,.@qaid)) goto T_Fail;
if(attachrid(.@qaid)){
set ##owner,1;
else{
query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','##owner','1','1','"+.@qaid+"')";
}
end;

Edited June 8, 2012 by QQfoolsorellina

[Bifrost]

try this

T_Add:
input .@aid,2000000,2005000;
if(!query_sql("SELECT account_id FROM `login` WHERE account_id ="+.@aid,.@qaid)) goto T_Fail;
if(attachrid(.@qaid)){
set ##owner,1;
else{
query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','##owner','1','1','"+.@qaid+"')";
}
end;

It wasn't the input part that was causing problems for me. The check of the ##owner variable didn't work properly.

I fixed the problem by now (you can see the complete scripts in my second post), but I appreciate the help, thanks.

[Mooka]

Even you already have fixed the problem, you must check if value exist in table to avoid a column clone, and also check the return of input.

Replace your T_Add label to this:

T_Add:
do
{
set @r_input, input(.@aid,2000000,2005000);
} while (@r_input != 0); // check if the input value is in the range

if(!query_sql("SELECT account_id FROM `login` WHERE account_id ="+.@aid,.@qaid)) goto T_Fail;
if(attachrid(.@qaid))
{
 set ##owner,1;
}
else
{

if(!.@qaid) //prevent two or more insertion in table
 query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','##owner','1','1','"+.@qaid+"')";
else
query_sql("UPDATE `global_reg_value` SET `value` = 1 WHERE `str`='##owner' AND `account_id`='"+.@qaid+"'");
} end;

Edited June 8, 2012 by Mooka

[QQfoolsorellina]

Even you already have fixed the problem, you must check if value exist in table to avoid a column clone, and also check the return of input.

Replace your T_Add label to this:

T_Add:
do
{
set @r_input, input(.@aid,2000000,2005000);
} while (@r_input != 0); // check if the input value is in the range

if(!query_sql("SELECT account_id FROM `login` WHERE account_id ="+.@aid,.@qaid)) goto T_Fail;
if(attachrid(.@qaid))
{
 set ##owner,1;
}
else
{

if(!.@qaid) //prevent two or more insertion in table
 query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','##owner','1','1','"+.@qaid+"')";
else
query_sql("UPDATE `global_reg_value` SET `value` = 1 WHERE `str`='##owner' AND `account_id`='"+.@qaid+"'");
} end;

it would not clone coz they was primary key set but It would show warning on map consle

and yes , you are right, we should avoid that warning

I has new one not tested , hope it work hehe

T_Add:
query_sql "SELECT account_id FROM `login` WHERE `sex`!='S' ORDER BY account_id  ASC LIMIT 1", .@minaid;
query_sql "SELECT account_id FROM `login` WHERE `sex`!='S' ORDER BY account_id  DESC LIMIT 1", .@maxid;
input .@aid;
if(.@aid >.@maxid || .@aid < .@minaid ||!query_sql("SELECT account_id FROM `login` WHERE account_id ="+.@aid,.@qaid)) goto T_Fail;
if(attachrid(.@qaid)){
	set ##owner,1;
else{
if(!query_sql("SELECT value FROM `global_reg_value` WHERE str='##owner' AND account_id = "+.@qaid,.@temp))
		query_sql "INSERT INTO `global_reg_value` (`char_id`, `str`, `value`, `type`, `account_id`) VALUES ('0','##owner','1','1','"+.@qaid+"')";
else
	query_sql("UPDATE `global_reg_value` SET value = 1 WHERE str ='##owner' AND account_id = "+.@qaid);	  
}
end;

Edited June 8, 2012 by QQfoolsorellina