I'm trying to compare all column data against a const char to see if the name in the column matches the player name that is connected to the game server. The comparison in the spaghetticode below does not work properly and results in new rows being made even if the player name already exists in the sqlite database. What is the correct way to do this comparison? Thanks in advance for your help.
Here's the snippet with the problematic section:
if(enable_sqlite_db) {
sqlite3_stmt *stmt;
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *sql;
bool name_match = false;
const char* player_database_names;
char *p_name = ci->name;
char *p_ip = ci->ip;
rc = sqlite3_open("playerinfo.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}else{
if(sql_console_msgs) fprintf(stdout, "Opened database successfully\n");
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
defformatstring(sqlstrprep)("SELECT NAME FROM PLAYERINFO");
rc = sqlite3_prepare_v2(db, sqlstrprep, -1, &stmt, NULL);
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt, SQLITE_ROW);
int columns = sqlite3_column_count(stmt);
if(sql_console_msgs) out(ECHO_CONSOLE, "-- id: %d row: %d columns: %d", id, SQLITE_ROW, columns);
player_database_names = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
}
if(player_database_names == p_name) name_match = true;
else if(player_database_names != p_name) name_match = false;
}
Here is the full code:
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
bool sql_console_msgs = true;
void QServ::savestats(clientinfo *ci)
{
if(enable_sqlite_db) {
sqlite3_stmt *stmt;
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *sql;
bool name_match = false;
const char* player_database_names;
char *p_name = ci->name;
char *p_ip = ci->ip;
rc = sqlite3_open("playerinfo.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}else{
if(sql_console_msgs) fprintf(stdout, "Opened database successfully\n");
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
defformatstring(sqlstrprep)("SELECT NAME FROM PLAYERINFO");
//defformatstring(sqlstrprep)("SELECT group_concat(NAME) FROM PLAYERINFO");
rc = sqlite3_prepare_v2(db, sqlstrprep, -1, &stmt, NULL);
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
//sometimes returns incorrectly - keeps creating undesired new rows firstguy, secondguy, firstguy
int id = sqlite3_column_int(stmt, SQLITE_ROW);
int columns = sqlite3_column_count(stmt);
if(sql_console_msgs) out(ECHO_CONSOLE, "-- id: %d row: %d columns: %d", id, SQLITE_ROW, columns);
player_database_names = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
//if (std::to_string(player_database_names).find(p_name) != std::string::npos) name_match = true;
//else name_match = false;
if(sql_console_msgs) out(ECHO_CONSOLE, "-- player db names: %s", player_database_names);
}
//if(!strcmp(player_database_names, p_name)) name_match = true;
//else if(strcmp(player_database_names, p_name)) name_match = false;
if(player_database_names == p_name) name_match = true;
else if(player_database_names != p_name) name_match = false;
}
sql = "CREATE TABLE IF NOT EXISTS PLAYERINFO(" \
"NAME TEXT NOT NULL," \
"FRAGS INT NOT NULL," \
"DEATHS INT NOT NULL," \
"FLAGS INT NOT NULL," \
"PASSES INT NOT NULL," \
"IP TEXT NOT NULL," \
"ACCURACY DECIMAL(4, 2) NOT NULL," \
"KPD DECIMAL(4, 2) NOT NULL);";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQLITE3 ERROR @ CREATE TABLE IF NOT EXISTS: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) {
if(!name_match) fprintf(stdout, "-- No previous record found under that name\n");
else fprintf(stdout, "-- Found name already, updating record instead\n");
}
}
char sqlINSERT[500];
char sqlUPDATE[1000];
int p_frags = ci->state.frags;
int p_deaths = ci->state.deaths;
int p_flags = ci->state.flags;
int p_passes = ci->state.passes;
int p_acc = (ci->state.damage*100)/max(ci->state.shotdamage, 1);
int p_kpd = (ci->state.frags)/max(ci->state.deaths, 1);
//name is different
if(!name_match) {
snprintf(sqlINSERT, 500, "INSERT INTO PLAYERINFO( NAME,FRAGS,DEATHS,FLAGS,PASSES,IP,ACCURACY,KPD ) VALUES (\"%s\", %d, %d, %d, %d, \"%s\", %d, %d)",p_name,p_frags,p_deaths,p_flags,p_passes,p_ip,p_acc,p_kpd);
//sqlEscape(sqlINSERT);
rc = sqlite3_exec(db, sqlINSERT, callback, 0, &zErrMsg);
}
//client name matches db record, update db if new info is > than db info
else if(name_match) {
snprintf(sqlUPDATE, 10000,
"UPDATE PLAYERINFO SET FRAGS = %d+(SELECT FRAGS FROM PLAYERINFO) WHERE NAME = \"%s\";" \
"UPDATE PLAYERINFO SET DEATHS = %d+(SELECT DEATHS FROM PLAYERINFO) WHERE NAME = \"%s\";" \
"UPDATE PLAYERINFO SET FLAGS = %d+(SELECT FLAGS FROM PLAYERINFO) WHERE NAME = \"%s\";" \
"UPDATE PLAYERINFO SET PASSES = %d+(SELECT PASSES FROM PLAYERINFO) WHERE NAME = \"%s\";" \
"UPDATE PLAYERINFO SET ACCURACY = %d+(SELECT ACCURACY FROM PLAYERINFO) WHERE NAME = \"%s\";" \
"UPDATE PLAYERINFO SET KPD = %d+(SELECT KPD FROM PLAYERINFO) WHERE NAME = \"%s\";",
ci->state.frags, ci->name, ci->state.deaths, ci->name, ci->state.flags, ci->name, ci->state.passes, ci->name, p_acc, ci->name, p_kpd, ci->name);
//sqlEscape(sqlUPDATE);
rc = sqlite3_exec(db, sqlUPDATE, callback, 0, &zErrMsg);
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQLITE3 ERROR @ INSERT & UPDATE: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) fprintf(stdout, "Playerinfo modified\n");
}
sqlite3_close(db);
}
}
void QServ::getstats(clientinfo *ci)
{
if(enable_sqlite_db) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
rc = sqlite3_open("playerinfo.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
sqlite3_stmt *stmt;
defformatstring(sqlstrprep)("SELECT NAME,FRAGS,ACCURACY,KPD FROM PLAYERINFO WHERE NAME == \"%s\";", ci->name);
rc = sqlite3_prepare_v2(db, sqlstrprep, -1, &stmt, NULL);
bool necho = false;
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
const char* name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
const char* allfrags = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
const char* avgacc = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
const char* avgkpd = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
if(!necho) {
if(avgacc == NULL) out(ECHO_SERV, "Name: \f0%s\f7, Total Frags: \f3%s\f7, Average KPD: \f6%s", name, allfrags, avgkpd);
else if(avgkpd == NULL) out(ECHO_SERV, "Name: \f0%s\f7, Total Frags: \f3%s\f7, Average Accuracy: \f2%s%%", name, allfrags, avgacc);
else out(ECHO_SERV, "Name: \f0%s\f7, Total Frags: \f3%s\f7, Average Accuracy: \f2%s%%\f7, Average KPD: \f6%s", name,allfrags,avgacc,avgkpd);
necho = true;
}
}
}
sqlite3_close(db);
}
}
void QServ::getnames(clientinfo *ci) {
if(enable_sqlite_db) {
sqlite3_stmt *stmt3;
sqlite3 *db;
int rc;
rc = sqlite3_open("playerinfo.db", &db);
defformatstring(sqlstrprep3)("SELECT group_concat(NAME, \", \") FROM PLAYERINFO WHERE IP == \"%s\";", ci->ip);
rc = sqlite3_prepare_v2(db, sqlstrprep3, -1, &stmt3, NULL);
while ((rc = sqlite3_step(stmt3)) == SQLITE_ROW) {
std::string names(reinterpret_cast<const char*>(sqlite3_column_text(stmt3, 0)));
defformatstring(nmsg)("Names from IP \f2%s\f7: %s", ci->ip, names.c_str());
out(ECHO_SERV, nmsg);
}
sqlite3_close(db);
}
}
void QServ::disconnectclient(clientinfo *ci)
{
if(enable_sqlite_db) savestats(ci);
}
I'm always amused by the fact that nobody wants to provide a minimal working example. In the end, I created one myself to show how to do this thing:
#include <stdlib.h>
#include <stdio.h>
#include <stdbool.h>
#include <string.h>
#include "sqlite3.h"
int main(void)
{
int rc;
sqlite3 *db;
rc = sqlite3_open("playerinfo.db", &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(EXIT_FAILURE);
}
else {
fprintf(stdout, "Opened database successfully\n");
}
char *zErrMsg = NULL;
rc = sqlite3_exec(db,
"CREATE TABLE IF NOT EXISTS PlayerInfo (id INTEGER PRIMARY KEY, name TEXT);"
"REPLACE INTO PlayerInfo(id, name) VALUES (1,'John'),(2,'Paul'),(3,'George'),(4,'Ringo'),(5,'Pete'),(6,'Stuart');",
NULL, 0, &zErrMsg);
if (rc != SQLITE_OK) {
fprintf(stderr, "SQL Database Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
exit(EXIT_FAILURE);
}
const char *search_name = "Ringo";
bool name_match = false;
sqlite3_stmt *stmt;
char *zSql = "SELECT name FROM PlayerInfo";
sqlite3_prepare_v2(db, zSql, -1, &stmt, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
const char* player_database_names = sqlite3_column_text(stmt, 0);
if (strcmp(player_database_names, search_name) == 0) {
name_match = true;
break;
}
}
sqlite3_finalize(stmt);
printf("Search name: %s - Result: %s\n", search_name, name_match ? "true" : "false");
sqlite3_close(db);
return EXIT_SUCCESS;
}
The original code had a lot of very strange things. It clearly looks as if a bunch of stuff was pasted together without reason.
I never used SQLite before, but it's really very well done. So the idea is to
sqlite3_open()) and check if successfull.sqlite3_exec() without a callback function).sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize().sqlite3_close()).As reported in the comments, this is not a good idea. If you want to see if something exists, query if it exists:
sqlite3_stmt *stmt;
char *zSql = "SELECT EXISTS(SELECT 1 FROM PlayerInfo WHERE name=?)";
sqlite3_prepare_v2(db, zSql, -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, search_name, -1, SQLITE_STATIC);
sqlite3_step(stmt);
name_match = sqlite3_column_int(stmt, 0);
sqlite3_finalize(stmt);
In this specific case, probably the parametrized query is an overkill, but who knows where that search_name comes from...
WARNING: no effort to have decent error handling.