MySQL error handling
#1

Just a quickie,
does anyone know a method to handle mysql errors correctly in SAMP in such a case the errors aren't logged (cluttering up the log file)?

Example,

1. A key field is set to being distinct values in MySQL.
2. Imagine we have some voting system (may contain errors),
Код:
CMD:vote(playerid, params[]) {
      new votedfor;
     if(sscanf(params, "d", votedfor)) { return; }
     if votedfor is valid...
     
     new query[200];
     mysql_format(sqlGameConnection, query, sizeof(query), "INSERT INTO votes SET pID = %d, for = %d;", PlayerInfo[playerid][pID], votedfor);
     mysql_pquery(sqlGameConnection, query, "OnVotedError", "i", playerid);
}

forward OnVotedError(playerid);
public OnVotedError(playerid) {
     if(mysql_errno() == 1062) { //duplicate record, player has already voted
            SendClientMessage(playerid, COLOR_GREY, "You have already voted");
     }
     return;
}
I'd much prefer this to having to send two queries: one SELECT & followed by an INSERT. Is there a way to avoid writing to mysql_log.txt in this situation? Going to take a quick looksy at the source.


Hmm perhaps mysql_log(none); then set it to default.
#define mysql_pquery_e(%0) mysql_log(LOG_NONE); \
mysql_pquery(%0); \
mysql_log(...); //... = default
...but this could fail seemingly queries are threaded.

I'm aware we can create sql routines which except errors but surely there's a much simpler approach in samp
Reply
#2

It's been a while but I believe you can use INSERT IGNORE INTO and then check with mysql_affected_rows which should return 0 if it was not inserted.
Reply
#3

Quote:
Originally Posted by woot
Посмотреть сообщение
It's been a while but I believe you can use INSERT IGNORE INTO and then check with mysql_affected_rows which should return 0 if it was not inserted.
Sweet thanks for that insight. I'll give this a crack in the morning.

Should be something similar to these changes...

mysql_format(sqlGameConnection, query, sizeof query, "INSERT IGNORE INTO votes SET pID = %d, ....
...

public OnVoteError(playerid) {
if(!(cache_affected_rows(sqlGameConnection)) { SendClientMessage(playerid, COLOR_GREY, "You have already voted"); }
return;
}
Reply
#4

- You should use player name instead of player id in saving data for the player in mysql because player id's keep changing after the player disconnect then reconnect, it's not stable.

- SET command is used with UPDATE not with INSERT.

- INSERT is used if there is no data for this player yet in the table.

- UPDATE is used if you want to update an existed data for a player.

Example:

INSERT INTO table_name (column1_name, column2_name ...) VALUES (data1, data2)

UPDATE table_name SET column1_name = 'something', column2_name = 'something' WHERE playername = 'iiNzTicTx'
Reply
#5

Quote:
Originally Posted by Juvanii
Посмотреть сообщение
- You should use player name instead of player id in saving data for the player in mysql because player id's keep changing after the player disconnect then reconnect, it's not stable.

- SET command is used with UPDATE not with INSERT.

- INSERT is used if there is no data for this player yet in the table.

- UPDATE is used if you want to update an existed data for a player.

Example:

INSERT INTO table_name (column1_name, column2_name ...) VALUES (data1, data2)

UPDATE table_name SET column1_name = 'something', column2_name = 'something' WHERE playername = 'iiNzTicTx'
pID := auto increment primary key representing an account ID in the MySQL database.
Using playernames will actually be bad in performance cost since you are comparing strings. Also you can use SET with INSERT. I often prefer SET for readability. Also don't forget to index playernames if you do use them. Indexing produces a 'most used' list or priority list to go through when searching records by a particular field.
As I said the question is to do with error handling. I'm aware there may be errors, after all it's simply a plan! I do appreciate answers, don't take me wrong since it develops ideas etc.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)