SQL Table support...

[Peopleperson49]

I'm trying to make a script so that on certain maps if a character is killed it will kick the character and delete it permantently. I put it as a bindcmd for ease of testing. I can't seem to get the sql for some tables to carry out this task. I am familiar with using sql and part of the functions are carried out, but deleting stuff such as char, inventory, cart inventory, and a couple other tables will not delete. I supect it is blocked in some way by the character server. I went through the entire table database to look for anything that retained specific character data to delete, but some of the tables below may be specific to my server. I don't remember which ones at the moment.

 

Peopleperson49

 

-    script    NightTerror    -1,{

OnNightTerror:
set NTCheck,1;
atcommand "@kick "+strcharinfo(0)+"";
end;

OnPCLogoutEvent:
if(playerattached()==0) { end; }
if(NTCheck!=1) { end; } else { set NTCheck,0; }
set .char_id,getcharid(0);
query_sql("DELETE FROM atcommandlog WHERE `char_id` ="+.char_id);
//query_sql("DELETE FROM BalanceRank WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM bonus_script WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM branchlog WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM buyingstore_items WHERE `buyingstore_id` = "+.char_id);
query_sql("DELETE FROM buyingstores WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM cashlog WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM cp_charprefs WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM cp_choko_rename_log WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM elemental WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM emprank WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM friends WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM guild WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM guild_member WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM homunculus WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM hotkey WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM memo WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM mercenary WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM mercenary_owner WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM mvplog WHERE `kill_char_id` ="+.char_id);
query_sql("DELETE FROM mvprank WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM npclog WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM party WHERE `leader_char` = "+.char_id);
query_sql("DELETE FROM pet WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM picklog WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM pvprank WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM quest WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM sc_data WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM skill WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM skillcooldown WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM vendings WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM zenylog WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM inventory WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM cart_inventory WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM global_reg_value WHERE `char_id` ="+.char_id);
query_sql("DELETE FROM char WHERE `char_id` ="+.char_id);
end;

OnInit:
bindatcmd("nightterror",strnpcinfo(0)+"::OnNightTerror");
end;
}

[sandbox]

Try to put them on the OnNightTerror or OnPCDieEvent label instead of OnPCLogoutEvent, you can try to put a 1 second sleep after kicking the character.

 

To make fully sure of it, you can try blocking the character just in case he logs in right after getting kicked. Then unblock him after the query_sql scripts..

 

ex.

-	script	Test	-1,{

OnPCDieEvent:
if(strcharinfo(3) == "your_map") {
		atcommand "@kick "+strcharinfo(0);
		atcommand "@block "+strcharinfo(0);
		sleep2 1000;
		query_sql("DELETE FROM char WHERE `char_id` ="+.char_id);
		atcommand "@unblock "+strcharinfo(0);
	}
end;

}

[Peopleperson49]

I had it that way origianally with the OnNightTerror using a sleep2 3000; but it still didn't happen. Basically the same reaction. Some tables deleted and others didn't.

 

Peopleperson49

[sandbox]

Hmm..

 

I think your queries should be like this

query_sql("DELETE FROM char WHERE `char_id` ='"+.char_id+"'");

[Emistry]

I think you should use .@char_id  ...

 

your .char_id will be overwrite by other character if they logout at the same time.

 

ensure your server have the privilege to delete data from your table.

[Peopleperson49]

Good catch on the .@char_id. I had it that way origonally so I'm not sure why I changed it. How do I make sure I have the proper privilege to delete information. I know it deleted some data so I should have permission. The ban works but not the unban section, so the sql might be erroring some how to stop the function. My map server shows no errors though.

 

Peopleperson49

 

-    script    Test    -1,{

OnPCDieEvent:
if(strcharinfo(3) == "aeir_beach") {
    set .@char_id,getcharid(0);
    atcommand "@kick "+strcharinfo(0);
    atcommand "@block "+strcharinfo(0);
    sleep2 3000;
    query_sql("DELETE FROM char WHERE `char_id` = '"+.@char_id+"'");
    atcommand "@unblock "+strcharinfo(0);
}
end;
}

[Peopleperson49]

So I had to go back to basics with my sql and I figured it out on my own. The only issue I get is a map server error that says, "[Error]: charif_ack_login_req failed - player not online. Any suggestions with fixing that. Otherwise it works perfectly!

 

Peopleperson49

 

Had to replace this:

query_sql("DELETE FROM char WHERE `char_id` = '"+.@char_id+"'");

With this:

query_sql("DELETE FROM `char` WHERE `char_id`='"+escape_sql(.@char_id)+"'");

Edited March 26, 2014 by Peopleperson49