sql error and can't show my character

kangfredy · January 5, 2013

guys i want to ask...

i have problem with my sql..i think it's possibly make my characther does'nt show..

need sugestion (please dont suggest me to re-install sql)

and i will ask.about adding database in char.

and this my adding database in char.c

|| SQL_ERROR == SqlStmt_BindColumn(stmt, 52, SQLDT_INT, &p->faction_id, 0, NULL, NULL)

but in my compile it's no problem and succesfully?why?

im using client 2012-04-10.

please move my thread >.< to 3rd party support

Brian · January 5, 2013

`faction_id`='0',

There should not be a comma after the last column to update.

If you're not sure what to edit, post the few lines before

||    SQL_ERROR == SqlStmt_BindColumn(stmt, 52, SQLDT_INT,    &p->faction_id, 0, NULL, NULL)

and I'll tell you what to change it to.

but in my compile it's no problem and succesfully?why?

because this is a runtime error in your SQL statement, not a compile error.

kangfredy · January 6, 2013

tq brian this is line before

|| SQL_ERROR == SqlStmt_BindColumn(stmt, 52, SQLDT_INT, &p->faction_id, 0, NULL, NULL)

// private declarations

#define CHAR_CONF_NAME "conf/char_athena.conf"

#define LAN_CONF_NAME "conf/subnet_athena.conf"

#define SQL_CONF_NAME "conf/inter_athena.conf"

char char_db[256] = "char";

char scdata_db[256] = "sc_data";

char cart_db[256] = "cart_inventory";

char inventory_db[256] = "inventory";

char charlog_db[256] = "charlog";

char storage_db[256] = "storage";

char interlog_db[256] = "interlog";

char reg_db[256] = "global_reg_value";

char skill_db[256] = "skill";

char memo_db[256] = "memo";

char guild_db[256] = "guild";

char guild_alliance_db[256] = "guild_alliance";

char guild_castle_db[256] = "guild_castle";

char guild_expulsion_db[256] = "guild_expulsion";

char guild_member_db[256] = "guild_member";

char guild_position_db[256] = "guild_position";

char guild_skill_db[256] = "guild_skill";

char guild_storage_db[256] = "guild_storage";

char party_db[256] = "party";

char pet_db[256] = "pet";

char mail_db[256] = "mail"; // MAIL SYSTEM

char auction_db[256] = "auction"; // Auctions System

char friend_db[256] = "friends";

char hotkey_db[256] = "hotkey";

char quest_db[256] = "quest";

char homunculus_db[256] = "homunculus";

char skill_homunculus_db[256] = "skill_homunculus";

char mercenary_db[256] = "mercenary";

char mercenary_owner_db[256] = "mercenary_owner";

char ragsrvinfo_db[256] = "ragsrvinfo";

// show loading/saving messages

int save_log = 1;

static DBMap* char_db_; // int char_id -> struct mmo_charstatus*

char db_path[1024] = "db";

int db_use_sqldbs;

struct mmo_map_server {

int fd;

uint32 ip;

uint16 port;

int users;

unsigned short map[MAX_MAP_PER_SERVER];

} server[MAX_MAP_SERVERS];

int login_fd=-1, char_fd=-1;

char userid[24];

char passwd[24];

char server_name[20];

char wisp_server_name[NAME_LENGTH] = "Server";

char login_ip_str[128];

uint32 login_ip = 0;

uint16 login_port = 6900;

char char_ip_str[128];

uint32 char_ip = 0;

char bind_ip_str[128];

uint32 bind_ip = INADDR_ANY;

uint16 char_port = 6121;

int char_maintenance = 0;

bool char_new = true;

int char_new_display = 0;

bool name_ignoring_case = false; // Allow or not identical name for characters but with a different case by [Yor]

int char_name_option = 0; // Option to know which letters/symbols are authorised in the name of a character (0: all, 1: only those in char_name_letters, 2: all EXCEPT those in char_name_letters) by [Yor]

char unknown_char_name[NAME_LENGTH] = "Unknown"; // Name to use when the requested name cannot be determined

#define TRIM_CHARS "\255\xA0\032 \x0A\x0D " //The following characters are trimmed regardless because they cause confusion and problems on the servers. [skotlex]

char char_name_letters[1024] = ""; // list of letters/symbols allowed (or not) in a character name. by [Yor]

int char_per_account = 0; //Maximum chars per account (default unlimited) [sirius]

int char_del_level = 0; //From which level u can delete character [Lupus]

int char_del_delay = 86400;

int log_char = 1; // loggin char or not [devil]

int log_inter = 1; // loggin inter or not [devil]

// Advanced subnet check [LuzZza]

struct s_subnet {

uint32 mask;

uint32 char_ip;

uint32 map_ip;

} subnet[16];

int subnet_count = 0;

struct char_session_data {

bool auth; // whether the session is authed or not

int account_id, login_id1, login_id2, sex;

int found_char[MAX_CHARS]; // ids of chars on this account

char email[40]; // e-mail (default: [email protected]) by [Yor]

time_t expiration_time; // # of seconds 1/1/1970 (timestamp): Validity limit of the account (0 = unlimited)

int group_id; // permission

uint32 version;

uint8 clienttype;

char new_name[NAME_LENGTH];

char birthdate[10+1]; // YYYY-MM-DD

};

int max_connect_user = 0;

int gm_allow_group = -1;

int autosave_interval = DEFAULT_AUTOSAVE_INTERVAL;

int start_zeny = 0;

int start_weapon = 1201;

int start_armor = 2301;

int guild_exp_rate = 100;

//Custom limits for the fame lists. [skotlex]

int fame_list_size_chemist = MAX_FAME_LIST;

int fame_list_size_smith = MAX_FAME_LIST;

int fame_list_size_taekwon = MAX_FAME_LIST;

// Char-server-side stored fame lists [DracoRPG]

struct fame_list smith_fame_list[MAX_FAME_LIST];

struct fame_list chemist_fame_list[MAX_FAME_LIST];

struct fame_list taekwon_fame_list[MAX_FAME_LIST];

// check for exit signal

// 0 is saving complete

// other is char_id

unsigned int save_flag = 0;

// Initial position (it's possible to set it in conf file)

struct point start_point = { 0, 53, 111 };

int console = 0;

//-----------------------------------------------------

// Auth database

//-----------------------------------------------------

#define AUTH_TIMEOUT 30000

struct auth_node {

int account_id;

int char_id;

uint32 login_id1;

uint32 login_id2;

uint32 ip;

int sex;

time_t expiration_time; // # of seconds 1/1/1970 (timestamp): Validity limit of the account (0 = unlimited)

int group_id;

unsigned changing_mapservers : 1;

};

static DBMap* auth_db; // int account_id -> struct auth_node*

//-----------------------------------------------------

// Online User Database

//-----------------------------------------------------

struct online_char_data {

int account_id;

int char_id;

int fd;

int waiting_disconnect;

short server; // -2: unknown server, -1: not connected, 0+: id of server

};

static DBMap* online_char_db; // int account_id -> struct online_char_data*

static int chardb_waiting_disconnect(int tid, unsigned int tick, int id, intptr_t data);

int delete_char_sql(int char_id);

/**

* @see DBCreateData

*/

static DBData create_online_char_data(DBKey key, va_list args)

{

struct online_char_data* character;

CREATE(character, struct online_char_data, 1);

character->account_id = key.i;

character->char_id = -1;

character->server = -1;

character->fd = -1;

character->waiting_disconnect = INVALID_TIMER;

return db_ptr2data(character);

}

void set_char_charselect(int account_id)

{

struct online_char_data* character;

character = (struct online_char_data*)idb_ensure(online_char_db, account_id, create_online_char_data);

if( character->server > -1 )

if( server[character->server].users > 0 ) // Prevent this value from going negative.

server[character->server].users--;

character->char_id = -1;

character->server = -1;

if(character->waiting_disconnect != INVALID_TIMER) {

delete_timer(character->waiting_disconnect, chardb_waiting_disconnect);

character->waiting_disconnect = INVALID_TIMER;

}

if (login_fd > 0 && !session[login_fd]->flag.eof)

{

WFIFOHEAD(login_fd,6);

WFIFOW(login_fd,0) = 0x272b;

WFIFOL(login_fd,2) = account_id;

WFIFOSET(login_fd,6);

}

void set_char_online(int map_id, int char_id, int account_id)

{

struct online_char_data* character;

struct mmo_charstatus *cp;

//Update DB

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `online`='1' WHERE `char_id`='%d' LIMIT 1", char_db, char_id) )

Sql_ShowDebug(sql_handle);

//Check to see for online conflicts

character = (struct online_char_data*)idb_ensure(online_char_db, account_id, create_online_char_data);

if( character->char_id != -1 && character->server > -1 && character->server != map_id )

{

ShowNotice("set_char_online: Character %d:%d marked in map server %d, but map server %d claims to have (%d:%d) online!\n",

character->account_id, character->char_id, character->server, map_id, account_id, char_id);

mapif_disconnectplayer(server[character->server].fd, character->account_id, character->char_id, 2);

}

//Update state data

character->char_id = char_id;

character->server = map_id;

if( character->server > -1 )

server[character->server].users++;

//Get rid of disconnect timer

if(character->waiting_disconnect != INVALID_TIMER) {

delete_timer(character->waiting_disconnect, chardb_waiting_disconnect);

character->waiting_disconnect = INVALID_TIMER;

}

//Set char online in guild cache. If char is in memory, use the guild id on it, otherwise seek it.

cp = (struct mmo_charstatus*)idb_get(char_db_,char_id);

inter_guild_CharOnline(char_id, cp?cp->guild_id:-1);

//Notify login server

if (login_fd > 0 && !session[login_fd]->flag.eof)

{

WFIFOHEAD(login_fd,6);

WFIFOW(login_fd,0) = 0x272b;

WFIFOL(login_fd,2) = account_id;

WFIFOSET(login_fd,6);

}

void set_char_offline(int char_id, int account_id)

{

struct online_char_data* character;

if ( char_id == -1 )

{

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `online`='0' WHERE `account_id`='%d'", char_db, account_id) )

Sql_ShowDebug(sql_handle);

}

else

{

struct mmo_charstatus* cp = (struct mmo_charstatus*)idb_get(char_db_,char_id);

inter_guild_CharOffline(char_id, cp?cp->guild_id:-1);

if (cp)

idb_remove(char_db_,char_id);

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `online`='0' WHERE `char_id`='%d' LIMIT 1", char_db, char_id) )

Sql_ShowDebug(sql_handle);

}

if ((character = (struct online_char_data*)idb_get(online_char_db, account_id)) != NULL)

{ //We don't free yet to avoid aCalloc/aFree spamming during char change. [skotlex]

if( character->server > -1 )

if( server[character->server].users > 0 ) // Prevent this value from going negative.

server[character->server].users--;

if(character->waiting_disconnect != INVALID_TIMER){

delete_timer(character->waiting_disconnect, chardb_waiting_disconnect);

character->waiting_disconnect = INVALID_TIMER;

}

if(character->char_id == char_id)

{

character->char_id = -1;

character->server = -1;

}

//FIXME? Why Kevin free'd the online information when the char was effectively in the map-server?

}

//Remove char if 1- Set all offline, or 2- character is no longer connected to char-server.

if (login_fd > 0 && !session[login_fd]->flag.eof && (char_id == -1 || character == NULL || character->fd == -1))

{

WFIFOHEAD(login_fd,6);

WFIFOW(login_fd,0) = 0x272c;

WFIFOL(login_fd,2) = account_id;

WFIFOSET(login_fd,6);

}

/**

* @see DBApply

*/

static int char_db_setoffline(DBKey key, DBData *data, va_list ap)

{

struct online_char_data* character = (struct online_char_data*)db_data2ptr(data);

int server = va_arg(ap, int);

if (server == -1) {

character->char_id = -1;

character->server = -1;

if(character->waiting_disconnect != INVALID_TIMER){

delete_timer(character->waiting_disconnect, chardb_waiting_disconnect);

character->waiting_disconnect = INVALID_TIMER;

}

} else if (character->server == server)

character->server = -2; //In some map server that we aren't connected to.

return 0;

}

/**

* @see DBApply

*/

static int char_db_kickoffline(DBKey key, DBData *data, va_list ap)

{

struct online_char_data* character = (struct online_char_data*)db_data2ptr(data);

int server_id = va_arg(ap, int);

if (server_id > -1 && character->server != server_id)

return 0;

//Kick out any connected characters, and set them offline as appropriate.

if (character->server > -1)

mapif_disconnectplayer(server[character->server].fd, character->account_id, character->char_id, 1);

else if (character->waiting_disconnect == INVALID_TIMER)

set_char_offline(character->char_id, character->account_id);

else

return 0; // fail

return 1;

}

void set_all_offline(int id)

{

if (id < 0)

ShowNotice("Sending all users offline.\n");

else

ShowNotice("Sending users of map-server %d offline.\n",id);

online_char_db->foreach(online_char_db,char_db_kickoffline,id);

if (id >= 0 || login_fd <= 0 || session[login_fd]->flag.eof)

return;

//Tell login-server to also mark all our characters as offline.

WFIFOHEAD(login_fd,2);

WFIFOW(login_fd,0) = 0x2737;

WFIFOSET(login_fd,2);

}

void set_all_offline_sql(void)

{

//Set all players to 'OFFLINE'

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `online` = '0'", char_db) )

Sql_ShowDebug(sql_handle);

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `online` = '0'", guild_member_db) )

Sql_ShowDebug(sql_handle);

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `connect_member` = '0'", guild_db) )

Sql_ShowDebug(sql_handle);

}

/**

* @see DBCreateData

*/

static DBData create_charstatus(DBKey key, va_list args)

{

struct mmo_charstatus *cp;

cp = (struct mmo_charstatus *) aCalloc(1,sizeof(struct mmo_charstatus));

cp->char_id = key.i;

return db_ptr2data(cp);

}

int inventory_to_sql(const struct item items[], int max, int id);

int mmo_char_tosql(int char_id, struct mmo_charstatus* p)

{

int i = 0;

int count = 0;

int diff = 0;

char save_status[128]; //For displaying save information. [skotlex]

struct mmo_charstatus *cp;

int errors = 0; //If there are any errors while saving, "cp" will not be updated at the end.

StringBuf buf;

if (char_id!=p->char_id) return 0;

cp = idb_ensure(char_db_, char_id, create_charstatus);

StringBuf_Init(&buf);

memset(save_status, 0, sizeof(save_status));

//map inventory data

if( memcmp(p->inventory, cp->inventory, sizeof(p->inventory)) ) {

if (!inventory_to_sql(p->inventory, MAX_INVENTORY, p->char_id))

strcat(save_status, " inventory");

else

errors++;

}

//map cart data

if( memcmp(p->cart, cp->cart, sizeof(p->cart)) ) {

if (!memitemdata_to_sql(p->cart, MAX_CART, p->char_id, TABLE_CART))

strcat(save_status, " cart");

else

errors++;

}

//map storage data

if( memcmp(p->storage.items, cp->storage.items, sizeof(p->storage.items)) ) {

if (!memitemdata_to_sql(p->storage.items, MAX_STORAGE, p->account_id, TABLE_STORAGE))

strcat(save_status, " storage");

else

errors++;

}

if (

(p->base_exp != cp->base_exp) || (p->base_level != cp->base_level) ||

(p->job_level != cp->job_level) || (p->job_exp != cp->job_exp) ||

(p->zeny != cp->zeny) ||

(p->last_point.map != cp->last_point.map) ||

(p->last_point.x != cp->last_point.x) || (p->last_point.y != cp->last_point.y) ||

(p->max_hp != cp->max_hp) || (p->hp != cp->hp) ||

(p->max_sp != cp->max_sp) || (p->sp != cp->sp) ||

(p->status_point != cp->status_point) || (p->skill_point != cp->skill_point) ||

(p->str != cp->str) || (p->agi != cp->agi) || (p->vit != cp->vit) ||

(p->int_ != cp->int_) || (p->dex != cp->dex) || (p->luk != cp->luk) ||

(p->option != cp->option) ||

(p->party_id != cp->party_id) || (p->guild_id != cp->guild_id) ||

(p->pet_id != cp->pet_id) || (p->weapon != cp->weapon) || (p->hom_id != cp->hom_id) ||

(p->ele_id != cp->ele_id) || (p->shield != cp->shield) || (p->head_top != cp->head_top) ||

(p->head_mid != cp->head_mid) || (p->head_bottom != cp->head_bottom) || (p->delete_date != cp->delete_date) ||

(p->rename != cp->rename) || (p->robe != cp->robe) || (p->faction_id != cp->faction_id)

)

{ //Save status

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `base_level`='%d', `job_level`='%d',"

"`base_exp`='%u', `job_exp`='%u', `zeny`='%d',"

"`max_hp`='%d',`hp`='%d',`max_sp`='%d',`sp`='%d',`status_point`='%d',`skill_point`='%d',"

"`str`='%d',`agi`='%d',`vit`='%d',`int`='%d',`dex`='%d',`luk`='%d',"

"`option`='%d',`party_id`='%d',`guild_id`='%d',`pet_id`='%d',`homun_id`='%d',`elemental_id`='%d',"

"`weapon`='%d',`shield`='%d',`head_top`='%d',`head_mid`='%d',`head_bottom`='%d',"

"`last_map`='%s',`last_x`='%d',`last_y`='%d',`save_map`='%s',`save_x`='%d',`save_y`='%d', `rename`='%d',"

"`delete_date`='%lu',`robe`='%d',`faction_id`='%d',"

" WHERE `account_id`='%d' AND `char_id` = '%d'",

char_db, p->base_level, p->job_level,

p->base_exp, p->job_exp, p->zeny,

p->max_hp, p->hp, p->max_sp, p->sp, p->status_point, p->skill_point,

p->str, p->agi, p->vit, p->int_, p->dex, p->luk,

p->option, p->party_id, p->guild_id, p->pet_id, p->hom_id, p->ele_id,

p->weapon, p->shield, p->head_top, p->head_mid, p->head_bottom,

mapindex_id2name(p->last_point.map), p->last_point.x, p->last_point.y,

mapindex_id2name(p->save_point.map), p->save_point.x, p->save_point.y, p->rename,

(unsigned long)p->delete_date, // FIXME: platform-dependent size

p->robe, p->faction_id,

p->account_id, p->char_id) )

{

Sql_ShowDebug(sql_handle);

errors++;

} else

strcat(save_status, " status");

}

//Values that will seldom change (to speed up saving)

if (

(p->hair != cp->hair) || (p->hair_color != cp->hair_color) || (p->clothes_color != cp->clothes_color) ||

(p->class_ != cp->class_) ||

(p->partner_id != cp->partner_id) || (p->father != cp->father) ||

(p->mother != cp->mother) || (p->child != cp->child) ||

(p->karma != cp->karma) || (p->manner != cp->manner) ||

(p->fame != cp->fame)

)

{

if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `class`='%d',"

"`hair`='%d',`hair_color`='%d',`clothes_color`='%d',"

"`partner_id`='%d', `father`='%d', `mother`='%d', `child`='%d',"

"`karma`='%d',`manner`='%d', `fame`='%d'"

" WHERE `account_id`='%d' AND `char_id` = '%d'",

char_db, p->class_,

p->hair, p->hair_color, p->clothes_color,

p->partner_id, p->father, p->mother, p->child,

p->karma, p->manner, p->fame,

p->account_id, p->char_id) )

{

Sql_ShowDebug(sql_handle);

errors++;

} else

strcat(save_status, " status2");

}

/* Mercenary Owner */

if( (p->mer_id != cp->mer_id) ||

(p->arch_calls != cp->arch_calls) || (p->arch_faith != cp->arch_faith) ||

(p->spear_calls != cp->spear_calls) || (p->spear_faith != cp->spear_faith) ||

(p->sword_calls != cp->sword_calls) || (p->sword_faith != cp->sword_faith) )

{

if (mercenary_owner_tosql(char_id, p))

strcat(save_status, " mercenary");

else

errors++;

}

//memo points

if( memcmp(p->memo_point, cp->memo_point, sizeof(p->memo_point)) )

{

char esc_mapname[NAME_LENGTH*2+1];

//`memo` (`memo_id`,`char_id`,`map`,`x`,`y`)

if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `char_id`='%d'", memo_db, p->char_id) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

//insert here.

StringBuf_Clear(&buf);

StringBuf_Printf(&buf, "INSERT INTO `%s`(`char_id`,`map`,`x`,`y`) VALUES ", memo_db);

for( i = 0, count = 0; i < MAX_MEMOPOINTS; ++i )

{

if( p->memo_point.map )

{

if( count )

StringBuf_AppendStr(&buf, ",");

Sql_EscapeString(sql_handle, esc_mapname, mapindex_id2name(p->memo_point.map));

StringBuf_Printf(&buf, "('%d', '%s', '%d', '%d')", char_id, esc_mapname, p->memo_point.x, p->memo_point.y);

++count;

}

if( count )

{

if( SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

strcat(save_status, " memo");

}

//FIXME: is this neccessary? [ultramage]

for(i=0;i<MAX_SKILL;i++)

if ((p->skill.lv != 0) && (p->skill.id == 0))

p->skill.id = i; // Fix skill tree

//skills

if( memcmp(p->skill, cp->skill, sizeof(p->skill)) )

{

//`skill` (`char_id`, `id`, `lv`)

if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `char_id`='%d'", skill_db, p->char_id) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

StringBuf_Clear(&buf);

StringBuf_Printf(&buf, "INSERT INTO `%s`(`char_id`,`id`,`lv`) VALUES ", skill_db);

//insert here.

for( i = 0, count = 0; i < MAX_SKILL; ++i )

{

if( p->skill.id != 0 && p->skill.flag != SKILL_FLAG_TEMPORARY )

{

if( p->skill.flag == SKILL_FLAG_PERMANENT && p->skill.lv == 0 )

continue;

if( p->skill.flag != SKILL_FLAG_PERMANENT && (p->skill.flag - SKILL_FLAG_REPLACED_LV_0) == 0 )

continue;

if( count )

StringBuf_AppendStr(&buf, ",");

StringBuf_Printf(&buf, "('%d','%d','%d')", char_id, p->skill.id, (p->skill.flag == SKILL_FLAG_PERMANENT ? p->skill.lv : p->skill.flag - SKILL_FLAG_REPLACED_LV_0));

++count;

}

if( count )

{

if( SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

strcat(save_status, " skills");

}

diff = 0;

for(i = 0; i < MAX_FRIENDS; i++){

if(p->friends.char_id != cp->friends.char_id ||

p->friends.account_id != cp->friends.account_id){

diff = 1;

break;

}

if(diff == 1)

{ //Save friends

if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `char_id`='%d'", friend_db, char_id) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

StringBuf_Clear(&buf);

StringBuf_Printf(&buf, "INSERT INTO `%s` (`char_id`, `friend_account`, `friend_id`) VALUES ", friend_db);

for( i = 0, count = 0; i < MAX_FRIENDS; ++i )

{

if( p->friends.char_id > 0 )

{

if( count )

StringBuf_AppendStr(&buf, ",");

StringBuf_Printf(&buf, "('%d','%d','%d')", char_id, p->friends.account_id, p->friends.char_id);

count++;

}

if( count )

{

if( SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

strcat(save_status, " friends");

}

#ifdef HOTKEY_SAVING

// hotkeys

StringBuf_Clear(&buf);

StringBuf_Printf(&buf, "REPLACE INTO `%s` (`char_id`, `hotkey`, `type`, `itemskill_id`, `skill_lvl`) VALUES ", hotkey_db);

diff = 0;

for(i = 0; i < ARRAYLENGTH(p->hotkeys); i++){

if(memcmp(&p->hotkeys, &cp->hotkeys, sizeof(struct hotkey)))

{

if( diff )

StringBuf_AppendStr(&buf, ",");// not the first hotkey

StringBuf_Printf(&buf, "('%d','%u','%u','%u','%u')", char_id, (unsigned int)i, (unsigned int)p->hotkeys.type, p->hotkeys.id , (unsigned int)p->hotkeys.lv);

diff = 1;

}

if(diff) {

if( SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) )

{

Sql_ShowDebug(sql_handle);

errors++;

} else

strcat(save_status, " hotkeys");

}

#endif

StringBuf_Destroy(&buf);

if (save_status[0]!='\0' && save_log)

ShowInfo("Saved char %d - %s:%s.\n", char_id, p->name, save_status);

if (!errors)

memcpy(cp, p, sizeof(struct mmo_charstatus));

return 0;

}

/// Saves an array of 'item' entries into the specified table.

int memitemdata_to_sql(const struct item items[], int max, int id, int tableswitch)

{

StringBuf buf;

SqlStmt* stmt;

int i;

int j;

const char* tablename;

const char* selectoption;

struct item item; // temp storage variable

bool* flag; // bit array for inventory matching

bool found;

int errors = 0;

switch (tableswitch) {

case TABLE_INVENTORY: tablename = inventory_db; selectoption = "char_id"; break;

case TABLE_CART: tablename = cart_db; selectoption = "char_id"; break;

case TABLE_STORAGE: tablename = storage_db; selectoption = "account_id"; break;

case TABLE_GUILD_STORAGE: tablename = guild_storage_db; selectoption = "guild_id"; break;

default:

ShowError("Invalid table name!\n");

return 1;

}

// The following code compares inventory with current database values

// and performs modification/deletion/insertion only on relevant rows.

// This approach is more complicated than a trivial delete&insert, but

// it significantly reduces cpu load on the database server.

StringBuf_Init(&buf);

StringBuf_AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`");

for( j = 0; j < MAX_SLOTS; ++j )

StringBuf_Printf(&buf, ", `card%d`", j);

StringBuf_Printf(&buf, " FROM `%s` WHERE `%s`='%d'", tablename, selectoption, id);

stmt = SqlStmt_Malloc(sql_handle);

if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf))

|| SQL_ERROR == SqlStmt_Execute(stmt) )

{

SqlStmt_ShowDebug(stmt);

SqlStmt_Free(stmt);

StringBuf_Destroy(&buf);

return 1;

}

SqlStmt_BindColumn(stmt, 0, SQLDT_INT, &item.id, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 1, SQLDT_SHORT, &item.nameid, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 2, SQLDT_SHORT, &item.amount, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 3, SQLDT_USHORT, &item.equip, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 4, SQLDT_CHAR, &item.identify, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 5, SQLDT_CHAR, &item.refine, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 6, SQLDT_CHAR, &item.attribute, 0, NULL, NULL);

SqlStmt_BindColumn(stmt, 7, SQLDT_UINT, &item.expire_time, 0, NULL, NULL);

for( j = 0; j < MAX_SLOTS; ++j )

SqlStmt_BindColumn(stmt, 8+j, SQLDT_SHORT, &item.card[j], 0, NULL, NULL);

// bit array indicating which inventory items have already been matched

flag = (bool*) aCalloc(max, sizeof(bool));

while( SQL_SUCCESS == SqlStmt_NextRow(stmt) )

{

found = false;

// search for the presence of the item in the char's inventory

for( i = 0; i < max; ++i )

{

// skip empty and already matched entries

if( items.nameid == 0 || flag )

continue;

if( items.nameid == item.nameid

&& items.card[0] == item.card[0]

&& items.card[2] == item.card[2]

&& items.card[3] == item.card[3]

) { //They are the same item.

ARR_FIND( 0, MAX_SLOTS, j, items.card[j] != item.card[j] );

if( j == MAX_SLOTS &&

items.amount == item.amount &&

items.equip == item.equip &&

items.identify == item.identify &&

items.refine == item.refine &&

items.attribute == item.attribute &&

items.expire_time == item.expire_time )

; //Do nothing.

else

{

// update all fields.

StringBuf_Clear(&buf);

StringBuf_Printf(&buf, "UPDATE `%s` SET `amount`='%d', `equip`='%d', `identify`='%d', `refine`='%d',`attribute`='%d', `expire_time`='%u'",

tablename, items.amount, items.equip, items.identify, items.refine, items.attribute, items.expire_time);

for( j = 0; j < MAX_SLOTS; ++j )

StringBuf_Printf(&buf, ", `card%d`=%d", j, items.card[j]);

StringBuf_Printf(&buf, " WHERE `id`='%d' LIMIT 1", item.id);

if( SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

found = flag = true; //Item dealt with,

break; //skip to next item in the db.

}

if( !found )

{// Item not present in inventory, remove it.

if( SQL_ERROR == Sql_Query(sql_handle, "DELETE from `%s` where `id`='%d' LIMIT 1", tablename, item.id) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

SqlStmt_Free(stmt);

StringBuf_Clear(&buf);

StringBuf_Printf(&buf, "INSERT INTO `%s`(`%s`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`", tablename, selectoption);

for( j = 0; j < MAX_SLOTS; ++j )

StringBuf_Printf(&buf, ", `card%d`", j);

StringBuf_AppendStr(&buf, ") VALUES ");

found = false;

// insert non-matched items into the db as new items

for( i = 0; i < max; ++i )

{

// skip empty and already matched entries

if( items.nameid == 0 || flag )

continue;

if( found )

StringBuf_AppendStr(&buf, ",");

else

found = true;

StringBuf_Printf(&buf, "('%d', '%d', '%d', '%d', '%d', '%d', '%d', '%u'",

id, items.nameid, items.amount, items.equip, items.identify, items.refine, items.attribute, items.expire_time);

for( j = 0; j < MAX_SLOTS; ++j )

StringBuf_Printf(&buf, ", '%d'", items.card[j]);

StringBuf_AppendStr(&buf, ")");

}

if( found && SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) )

{

Sql_ShowDebug(sql_handle);

errors++;

}

StringBuf_Destroy(&buf);

aFree(flag);

return errors;

}

/* pretty much a copy of memitemdata_to_sql except it handles inventory_db exclusively,

* - this is required because inventory db is the only one with the 'favorite' column. */

int inventory_to_sql(const struct item items[], int max, int id) {

StringBuf buf;

SqlStmt* stmt;

int i;

int j;

struct item item; // temp storage variable

bool* flag; // bit array for inventory matching

bool found;

int errors = 0;

// The following code compares inventory with current database values

// and performs modification/deletion/insertion only on relevant rows.

// This approach is more complicated than a trivial delete&insert, but

// it significantly reduces cpu load on the database server.

StringBuf_Init(&buf);

StringBuf_AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `favorite`");

for( j = 0; j < MAX_SLOTS; ++j )

StringBuf_Printf(&buf, ", `card%d`", j);

StringBuf_Printf(&buf, " FROM `%s` WHERE `char_id`='%d'", inventory_db, id);