Sign in to follow this  
hkBattousai

[MYSQL] When should i call mysql_close() and mysql_free_result() ?

Recommended Posts

Which of the following codes is better? This one,
mysql_query(&mysql, szQuery);
MYSQL_RES * result = mysql_store_result(&mysql);
MYSQL_ROW row = mysql_fetch_row(result);
if (row[0][nUserID] == '1')
{
	mysql_free_result(result);
	mysql_close(&mysql);
	return true;
}
else
{
	mysql_free_result(result);
	mysql_close(&mysql);
	return false;
}
Or, this one? :
mysql_query(&mysql, szQuery);
MYSQL_RES * result = mysql_store_result(&mysql);
MYSQL_ROW row = mysql_fetch_row(result);

mysql_free_result(result);
mysql_close(&mysql);
if (row[0][nUserID] == '1') return true; else return false;
What I wonder is, if I free the MySQL resources, do I lose the variable "row" as well?

Share this post


Link to post
Share on other sites
Quote:
Original post by Battousai

What I wonder is, if I free the MySQL resources, do I lose the variable "row" as well?


Yes

I wrapped these statements into a RAII class. Makes for painless use. Something like:

void foo() {
sql::Query query(db, "SELECT WHATEVER");

sql::Result::iterator row = query.rows.begin();
while (row != query.rows.end()) {
//
}
}


The SQL documentation is somewhat clear on when close/free functions must be called with respect to allocation functions.

Note that mysql_close() closes the connection to database, so in your case, you'd be opening a new connection for each query, or worse, both your examples are incorrect. You call mysql_close() in response to mysql_connect(), not to close mysql_query().

Share this post


Link to post
Share on other sites
To make it clear, I'm sending the entire member function :
bool CConnection::CanPostToUser(int nTargetID)
{
MYSQL mysql;
this->Connect(&mysql);

char szQuery[128];
char szThisID[16]; itoa(this->GetUserID(), szThisID, 10);

strcpy(szQuery, "SELECT ");
strcat(szQuery, DB_FIELD_USER_CAN);
strcat(szQuery, " FROM ");
strcat(szQuery, DB_TABLE_USER);
strcat(szQuery, " WHERE ");
strcat(szQuery, DB_FIELD_USER_ID);
strcat(szQuery, "=");
strcat(szQuery, szThisID);
strcat(szQuery, ";");

mysql_query(&mysql, szQuery);
MYSQL_RES * result = mysql_store_result(&mysql);
MYSQL_ROW row = mysql_fetch_row(result);
if (row[0][nTargetID] == '1')
{
mysql_free_result(result);
mysql_close(&mysql);
return true;
}
else
{
mysql_free_result(result);
mysql_close(&mysql);
return false;
}
}


I decided to make a new connection for each function call, because it is not certain when to send a query. If it takes too much time between two queries the connection may timeout.

By the way, I see that you used C++ libraries for MySQL, I didn't know there was a C++ library. Nevertheless it is to late to modify my 900-line connection class file. I have to complete this project in a week to graduate.

Share this post


Link to post
Share on other sites
Battousai, you have a bug in your code. You used 'char[]' in C++.

std::string will save your CPU from recomputing the length of every string in every call to strcat. It will save you from the inevitable buffer overflow you'll get from using a fixed-size buffer. And if you don't think you can use it with functions that require char*, std::string.c_string() returns a const char* you can use.

Just because your query is small, doesn't save you from writing it properly now. It is neither more difficult nor more time-consuming to write it correctly the first time around. You'll probably get a better grade for it, too.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this