C++/SQL

[Ertzel]

Okay, so I'm working on using a SQL database for handling all of my in-game data storage. Everything works fine for saving. The problem I'm having is with loading in data.

When a user created their account, I need to go through the table and see if any username already matches the name they entered and if it does, it marks a boolean found to true. If it's not found the boolean found stays false and it lets the user make their account.

My problem is, right now if the user enters a name that's not already in my table, the whole server crashes and errors out because the SQL statement didn't find anything.

Here is the code I'm using…

```
void PlayerHandler::registerPlayer(std::string username, std::string password, int connectorNum)

{

bool found = false;

int rc;

char *error;

sqlite3 *db;

rc = sqlite3_open("rsc/Data.db", &db);

const char *sqlSelect = sqlite3_mprintf("SELECT * FROM Player WHERE userName LIKE '%s';", username.c_str());

char **results = NULL;

int rows, columns;

sqlite3_get_table(db, sqlSelect, &results, &rows, &columns, &error);

if (rc){

sqlite3_free(error);

} else {

found = true;

sf::Packet spack;

int sendPacketCode = 2;

bool sendFailure = !found;

spack << sendPacketCode << sendFailure;

ent::networkManager.sendPacket(spack, ent::connectors.at(connectorNum));

sqlite3_free_table(results);

}

sqlite3_close(db);

if(!found)

{

loadPlayer(username);

srand(time(NULL));

ent::connectors.at(connectorNum)->player = players.at(players.size() - 1);

sf::Packet spack;

int sendPacketCode = 2;

bool sendSuccess = true;

spack << sendPacketCode << sendSuccess;

ent::networkManager.sendPacket(spack, ent::connectors.at(connectorNum));

}

}
```

The stuff to do with packets all works, it's just the SQL code thats giving me problems.

[clark]

I don't know alot of things about sql , but what if you add a sql code that make this :

If player enter this name : ertzel44

then the sql search in the database to see if that name exist already

if it doesn't find it then he create , and he save somewhere (in the table)

that next time when other user want to register the sql will check both in the table and also in the script of searching if that name is taken or not?

I don't know if i replied it correctly (in english it could look a bit messed up my explanation lol )

[Ertzel]

Thats not at all what this code is suppose to do. This is only for checking if an account already exists. I don't want it to create an account if it doesn't, I already have a working code for creating accounts. Making two tables is a complete waste of time and wont solve this issue at all.

[AeroEBrown]

> Okay, so I'm working on using a SQL database for handling all of my in-game data storage. Everything works fine for saving. The problem I'm having is with loading in data.
>
> When a user created their account, I need to go through the table and see if any username already matches the name they entered and if it does, it marks a boolean found to true. If it's not found the boolean found stays false and it lets the user make their account.
>
> My problem is, right now if the user enters a name that's not already in my table, the whole server crashes and errors out because the SQL statement didn't find anything.
>
> Here is the code I'm using…
>
> ```
> void PlayerHandler::registerPlayer(std::string username, std::string password, int connectorNum)
>
> {
>
> bool found = false;
>
> int rc;
>
> char *error;
>
> sqlite3 *db;
>
> rc = sqlite3_open("rsc/Data.db", &db);
>
> const char *sqlSelect = sqlite3_mprintf("SELECT * FROM Player WHERE userName LIKE '%s';", username.c_str());
>
> char **results = NULL;
>
> int rows, columns;
>
> sqlite3_get_table(db, sqlSelect, &results, &rows, &columns, &error);
>
> if (rc){
>
> sqlite3_free(error);
>
> } else {
>
> found = true;
>
> sf::Packet spack;
>
> int sendPacketCode = 2;
>
> bool sendFailure = !found;
>
> spack << sendPacketCode << sendFailure;
>
> ent::networkManager.sendPacket(spack, ent::connectors.at(connectorNum));
>
> sqlite3_free_table(results);
>
> }
>
> sqlite3_close(db);
>
> if(!found)
>
> {
>
> loadPlayer(username);
>
> srand(time(NULL));
>
> ent::connectors.at(connectorNum)->player = players.at(players.size() - 1);
>
> sf::Packet spack;
>
> int sendPacketCode = 2;
>
> bool sendSuccess = true;
>
> spack << sendPacketCode << sendSuccess;
>
> ent::networkManager.sendPacket(spack, ent::connectors.at(connectorNum));
>
> }
>
> }
> ```
>
> The stuff to do with packets all works, it's just the SQL code thats giving me problems.

I'm thinking that the issue is somewhere in here:

```
if (rc){

sqlite3_free(error);

}
```

You use rc like a bool, but you set it to int further up…

Also, the logic in it doesn't quite make sense to me. You don't check if the results, columns or rows fields are set to anything, which would be the logical idea in a situation such as this. (To check if results == null or whatever it becomes when there was nothing found, and if so, then it was a good name, otherwise it was a taken name.)

Thanks,

Aeroplane

[Ertzel]

I figured out the problem actually had nothing to do with the SQL part, it had to do with the loading the character in connector part, after loading data from the SQL table (It worked when loading from xml but not SQL).

I also added a check to make sure rows is > 0 after the else statement to check if it actually found data or not.

I fixed the problem kind of now but have a new random error to do with the main loading section.

```

void PlayerHandler::loadPlayers()

{

bool isNew = false;

int rc;

char *error;

sqlite3 *db;

rc = sqlite3_open("rsc/Data.db", &db);

// Create player table if it doesn't exist

const char *sqlCreateTable = "CREATE TABLE Player (userNum INTEGER PRIMARY KEY, userName VARCHAR(30), password VARCHAR(30), admin INTEGER, spriteID INTEGER, x INTEGER, y INTEGER, points INTEGER, str INTEGER, dex INTEGER, intel INTEGER, con INTEGER, hp INTEGER, UNIQUE (userName));";

rc = sqlite3_exec(db, sqlCreateTable, NULL, NULL, &error);

if (rc) {

} else {

std::cout << "Created Player Table.\n";

}

pugi::xml_document doc;

pugi::xml_parse_result result = doc.load_file(filename.c_str());

for(pugi::xml_node player = doc.first_child(); player; player = player.next_sibling())

{

std::string username = player.child("username").child_value();

const char *sqlSelect = sqlite3_mprintf("SELECT * FROM Player WHERE userName LIKE '%s';", username);

char **results = NULL;

int rows, columns;

sqlite3_get_table(db, sqlSelect, &results, &rows, &columns, &error);

if (rc){

sqlite3_free(error);

} else {

if(rows > 0 ){ // Has data

std::string username = results[14];

cout << username << endl;

std::string password = results[15];

int rank = atoi(results[16]);

int spriteID = atoi(results[17]);

int x = atoi(results[18]);

int y = atoi(results[19]);

int pnts = atoi(results[20]);

int str = atoi(results[21]);

int dex = atoi(results[22]);

int intel = atoi(results[23]);

int con = atoi(results[24]);

int hp = atoi(results[25]);

Player* nwplayer = new Player(username, password);

nwplayer->setOnline(false);

nwplayer->setPosition(x, y);

nwplayer->setPoints(pnts);

nwplayer->setStat("str", str);

nwplayer->setStat("dex", dex);

nwplayer->setStat("intel", intel);

nwplayer->setStat("con", con);

nwplayer->setHp(hp);

nwplayer->setSpriteID(spriteID);

nwplayer->setAdmin(rank);

nwplayer->setOnline(true);

nwplayer->sendAllInfo();

players.push_back(nwplayer);

}

sqlite3_free_table(results);

}

}

sqlite3_close(db);

}

```

I'm not sure where the error is, but if I delete the SQL and xml files and start fresh, I don't get an error on loading. it's when its actually getting data from the files on loading it errors out.

I save the names to the SQL file and also the xml file to tract user names, it should then loop through the XML file for each name and load the data from the SQL file matching the name it grabbed from the xml file.

Doing this with just the xml file works fine, it's when I grab the data from the SQL file now it's giving random decompile errors.

[Ertzel]

Now, now everything to do with loading is fine and now that I fixed loading I figured out I have a problem with saving.

It saves the initial values fine but doesn't save the new info inplace of the old info…

Here is my saving code.

```

void PlayerHandler::savePlayers()

{

int rc;

char *error;

sqlite3 *db;

rc = sqlite3_open("rsc/Data.db", &db);

std::remove(filename.c_str());

pugi::xml_document doc;

for(int i = 0; i < players.size(); i++)

{

cout << players.at(i)->getName().c_str() << " - " << players.at(i)->getAdmin() << " - " << players.at(i)->getSpriteID() << " - " << players.at(i)->getX() << " - " << players.at(i)->getY() << endl;

pugi::xml_node player = doc.append_child("player");

player.append_child("username").append_child(pugi::node_pcdata).set_value(players.at(i)->getName().c_str());

char* sqlInsert = sqlite3_mprintf("REPLACE INTO Player VALUES(NULL, '%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d) WHERE userName LIKE '%s';", players.at(i)->getName().c_str(), players.at(i)->getPassword().c_str(), players.at(i)->getAdmin(), players.at(i)->getSpriteID(), players.at(i)->getX(), players.at(i)->getY(), players.at(i)->getPoints(), players.at(i)->getStr(), players.at(i)->getDex(), players.at(i)->getIntel(), players.at(i)->getCon(), players.at(i)->getHp(), players.at(i)->getName().c_str());

rc = sqlite3_exec(db, sqlInsert, 0, 0, &error);

if (rc) {

sqlite3_free(error);

}

const char *sqlSelect = sqlite3_mprintf("SELECT * FROM Player WHERE userName LIKE '%s';", players.at(i)->getName().c_str());

char **results = NULL;

int rows, columns;

sqlite3_get_table(db, sqlSelect, &results, &rows, &columns, &error);

if (rc){

std::string username = results[14];

std::string password = results[15];

int rank = atoi(results[16]);

int spriteID = atoi(results[17]);

int x = atoi(results[18]);

int y = atoi(results[19]);

cout << username << " - " << rank << " - " << spriteID << " - " << x << " - " << y << endl;

}

sqlite3_free_table(results);

}

std::cout << "Players saved!\n";

sqlite3_close(db);

doc.save_file(filename.c_str());

}

```
(The code that selects is just for debugging to see if it saved the correct info or not)

[EDIT]

Figured out the problem. I was calling the REPLACE the same way you would call INSERT, which isn't correct. Here is the working code now

```

void PlayerHandler::savePlayers()

{

int rc;

char *error;

sqlite3 *db;

rc = sqlite3_open("rsc/Data.db", &db);

std::remove(filename.c_str());

pugi::xml_document doc;

for(int i = 0; i < players.size(); i++)

{

pugi::xml_node player = doc.append_child("player");

player.append_child("username").append_child(pugi::node_pcdata).set_value(players.at(i)->getName().c_str());

char* sqlInsert = sqlite3_mprintf("UPDATE Player SET admin=%d, spriteID=%d, x=%d, y=%d, level=%d, points=%d, str=%d, dex=%d, intel=%d, con=%d, hp=%d WHERE userName LIKE '%s';", players.at(i)->getAdmin(), players.at(i)->getSpriteID(), players.at(i)->getX(), players.at(i)->getY(), players.at(i)->getLevel(), players.at(i)->getPoints(), players.at(i)->getStr(), players.at(i)->getDex(), players.at(i)->getIntel(), players.at(i)->getCon(), players.at(i)->getHp(), players.at(i)->getName().c_str());

rc = sqlite3_exec(db, sqlInsert, 0, 0, &error);

if (rc) {

sqlite3_free(error);

}

}

std::cout << "Players saved!\n";

sqlite3_close(db);

doc.save_file(filename.c_str());

}

```