JSP - scriplet and page directive
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
Cookies for everyone
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
Session without cookies
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
Conversation on HttpSession
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
Attributes' scopes
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
The Dog ServletContextListener
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
Context init parameters
This post has been moved to Biting Code, my blog dedicated only to JVM languages.
Servlet init parameters
This post has been moved to Biting Code, a blog dedicated only to JVM languages.
Parameters for HttpServletRequest
This post has been moved to Biting Code, a blog dedicated only to JVM languages.
Request dispatching for Beer Selector
This post has been moved to Biting Code, a blog dedicated only to JVM languages.
Model for the Beer Selector
This post has been moved to Biting Code, a blog dedicated only to JVM languages.
A Beer Selector Web Application
This post has been moved to Biting Code, a blog dedicated only to JVM languages.
Head First Servlet and JSP
This post has been moved to Biting Code, a blog dedicated only to JVM languages.
Dumping a ResultSet to a output stream
We can now use the Field class, combined with the couple of functions we designed for iterating on the ResultSet to implement a global "put to" operator on the output stream for the ResultSet class that would give us the columns names and all the values for each row.
To to that we have to redesign a bit our ResultSet class.
In its private section we want to add a vector of Field:
In the public section we add a function to dump the fields name for the resultset:
And we declare the global function "put to":
We have to change the moveToThis() function, to manage the fields_ too:
Same issue for the "real" ctor (the "dummy" one does not change):
And here is the implementation for the two new functions:
To to that we have to redesign a bit our ResultSet class.
In its private section we want to add a vector of Field:
class ResultSet
{
private:
MYSQL_RES* rs_;
std::vector<Field> fields_;
// ...
In the public section we add a function to dump the fields name for the resultset:
void dumpFieldNames(std::ostream& os) const;
And we declare the global function "put to":
std::ostream& operator<<(std::ostream& out, ResultSet& rs);
We have to change the moveToThis() function, to manage the fields_ too:
void ResultSet::moveToThis(ResultSet& rhs)
{
rs_ = rhs.rs_;
fields_.assign(rhs.fields_.begin(), rhs.fields_.end());
rhs.rs_ = 0;
rhs.fields_.clear();
}
Same issue for the "real" ctor (the "dummy" one does not change):
ResultSet::ResultSet(MYSQL_RES* rs, unsigned int cols) : rs_(rs)
{
fields_.reserve(cols);
while(Field fld = mysql_fetch_field(rs_))
fields_.push_back(fld);
}
And here is the implementation for the two new functions:
void ResultSet::dumpFieldNames(std::ostream& os) const
{
std::vector<Field>::const_iterator it;
for(it = fields_.cbegin(); it != fields_.cend(); ++it)
os << *it << ' ';
os << std::endl;
}
std::ostream& operator<<(std::ostream& os, ResultSet& rs)
{
// header
rs.dumpFieldNames(os);
// all rows
rs.begin();
while(Row r = rs.getNext())
os << r;
return os;
}
Wrapping MYSQL_FIELD in a class
The MySQL Connector/C framework uses the MYSQL_FIELD (the actual name of the structure is st_mysql_field) to store the information for any column in a SQL table, such like the column name, type, and size.
Here we are about to describe a simple class, Field, that would provide an object-oriented wrapper to the raw C structure. Here we'll just provide access just for the more commonly used properties for a SQL column, it would be easy to extend the interface in the future.
Here is the Field class declaration:
1. pointer to the raw structure
2. ctor for dummy object
3. allow to use objects of this class in boolean context
4. convenience global function for putting the field name on a output stream
The getter functions are easy to be implemented:
1. for the dummy field I guess the best choice to represent its type is MYSQL_TYPE_NULL.
The "put to" global operator on the ostream is easy to implement, too:
Finally the dumpDetails() function, that is just meant to give an overview of the many internal details available on a MYSQL_FIELD object:
Here we are about to describe a simple class, Field, that would provide an object-oriented wrapper to the raw C structure. Here we'll just provide access just for the more commonly used properties for a SQL column, it would be easy to extend the interface in the future.
Here is the Field class declaration:
class Field
{
private:
MYSQL_FIELD* field_; // 1.
public:
Field() : field_(0) {} // 2.
Field(MYSQL_FIELD* field) : field_(field) {}
_OPERATOR_BOOL() const { return (field_ != 0 ? _CONVERTIBLE_TO_TRUE : 0); } // 3.
const char* getName() const;
const int getType() const;
const int getSize() const;
void dumpDetails(std::ostream& os);
};
std::ostream& operator<<(std::ostream& os, const Field& fld); // 4.
1. pointer to the raw structure
2. ctor for dummy object
3. allow to use objects of this class in boolean context
4. convenience global function for putting the field name on a output stream
The getter functions are easy to be implemented:
const char* Field::getName() const
{
return field_ ? field_->name : "Dummy";
}
const int Field::getType() const
{
return field_ ? field_->type : MYSQL_TYPE_NULL; // 1.
}
const int Field::getSize() const
{
return field_ ? field_->name_length : 0;
}
1. for the dummy field I guess the best choice to represent its type is MYSQL_TYPE_NULL.
The "put to" global operator on the ostream is easy to implement, too:
std::ostream& operator<<(std::ostream& os, const Field& fld)
{
os << fld.getName();
return os;
}
Finally the dumpDetails() function, that is just meant to give an overview of the many internal details available on a MYSQL_FIELD object:
void Field::dumpDetails(std::ostream& os)
{
if(field_ == 0)
{
os << "Dummy" << std::endl;
return;
}
os << "Database name: " << field_->db << std::endl;
os << "Table name (or alias): " << field_->table << std::endl;
os << "Table name: " << field_->org_table << std::endl;
os << "Field name (or alias): " << field_->name << std::endl;
os << "Field name (or alias) length: " << field_->name_length << std::endl;
os << "Field name: " << field_->org_name << std::endl;
os << "Field name length: " << field_->org_name_length << std::endl;
os << "Field length: " << field_->length << std::endl;
os << "Current RS field max length: " << field_->max_length << std::endl;
os << "Default field value: " << (field_->def ? field_->def : "Not set") << std::endl;
os << "Field decimals: " << field_->decimals << std::endl;
os << "Field charset: " << field_->charsetnr << std::endl;
if(field_->charsetnr == 63)
os << "Field is binary (charsetnr == 63)" << std::endl;
os << "Field type: " << field_->type << std::endl;
if(IS_NUM(field_->type))
os << "Field is numeric" << std::endl;
os << "Field is ";
switch(field_->type)
{
case MYSQL_TYPE_DECIMAL:
os << "decimal" << std::endl;
break;
case MYSQL_TYPE_TINY:
os << "tiny" << std::endl;
break;
case MYSQL_TYPE_SHORT:
os << "short";
break;
case MYSQL_TYPE_LONG:
os << "long";
break;
case MYSQL_TYPE_FLOAT:
os << "float";
break;
case MYSQL_TYPE_DOUBLE:
os << "double";
break;
case MYSQL_TYPE_NULL:
os << "null";
break;
case MYSQL_TYPE_TIMESTAMP:
os << "timestamp";
break;
case MYSQL_TYPE_LONGLONG:
os << "long long";
break;
case MYSQL_TYPE_INT24:
os << "int24";
break;
case MYSQL_TYPE_DATE:
os << "date";
break;
case MYSQL_TYPE_TIME:
os << "time";
break;
case MYSQL_TYPE_DATETIME:
os << "datetime";
break;
case MYSQL_TYPE_YEAR:
os << "year";
break;
case MYSQL_TYPE_NEWDATE:
os << "(new) date";
break;
case MYSQL_TYPE_VARCHAR:
os << "varchar";
break;
case MYSQL_TYPE_BIT:
os << "bit";
break;
case MYSQL_TYPE_NEWDECIMAL:
os << "(new) decimal";
break;
case MYSQL_TYPE_ENUM:
os << "enum";
break;
case MYSQL_TYPE_SET:
os << "set";
break;
case MYSQL_TYPE_TINY_BLOB:
os << "tiny blob";
break;
case MYSQL_TYPE_MEDIUM_BLOB:
os << "medium blob";
break;
case MYSQL_TYPE_LONG_BLOB:
os << "long blob";
break;
case MYSQL_TYPE_BLOB:
os << "blob";
break;
case MYSQL_TYPE_VAR_STRING:
os << "var string";
break;
case MYSQL_TYPE_STRING:
os << "string";
break;
case MYSQL_TYPE_GEOMETRY:
os << "geometry";
break;
default:
os << "an unexpected type";
break;
}
os << std::endl;
if(field_->flags & NOT_NULL_FLAG)
os << "Field can't be NULL" << std::endl;
if(field_->flags & PRI_KEY_FLAG)
os << "Field part of a PK" << std::endl;
if(field_->flags & UNIQUE_KEY_FLAG)
os << "Field part of a Unique Key" << std::endl;
if(field_->flags & MULTIPLE_KEY_FLAG)
os << "Field part of a Multiple Key" << std::endl;
if(field_->flags & UNSIGNED_FLAG)
os << "Field is unsigned" << std::endl;
if(field_->flags & ZEROFILL_FLAG)
os << "Field is zero-filled" << std::endl;
if(field_->flags & BINARY_FLAG)
os << "Field is binary" << std::endl;
if(field_->flags & AUTO_INCREMENT_FLAG)
os << "Field is auto-incrementing" << std::endl;
if(field_->flags & NO_DEFAULT_VALUE_FLAG)
os << "Field has no default value" << std::endl;
}
Fetching rows in a resultset
Now that we have a class Row that wraps the MySQL Connector/C MYSQL_ROW allowing also to manage a dummy row transparentely, we just have to add a couple of functions to our ResultSet class in a way that the user could happily fetch all the rows in it.
The first function, begin(), would act just like an STL begin() iterator function, leaving the class ready to fetch the first row in the resultset.
The second one, getNext(), would fetch the next row in the resultset and return it, if available.
Here is the changes in the class declaration:
And here is the functions' definition:
1. before calling the MySQL function that move the row pointer to the begin, mysql_row_seek(), we ensure that we actually have a resultset to work with, and it has a valid data section.
2. if our resultset is a dummy object, we return a dummy row.
3. low level job, mysql_fetch_row() makes available in current_row the next resultset row.
4. there is no actual row, we create a dummy one and return it.
5. create a row from the raw one, and return it.
We can now have fun writing silly test code to see how it doesn't crash our application, even if it is quite meaningless.
We call on our connection an insert statement. We know that no resultset is returned by it, but let's try to get the next row from it and dump it to cout. What we get is just a "Dummy" output on our console.
We could even try to reset the cursor on the (dummy) resultset calling begin(). No harm is expected:
A more normal usage of our classes is showed in this piece of code. We perform a SELECT, then we loop on all the resulting rows, dumping them to the output console:
Let's do now something a bit wierd, using the copy constructor we designed for the ResultSet class:
1. copying rs2 to rs2a we actually move the underlying resultset to the new object.
2. rs2 now is empty, we can't do anything sensible with it, in any case we will end up working with dummies.
3. on the other side, before using rs2a we have to reset the cursor, since it has been moved to the end by the previous piece of testing code.
4. again, here rs2a is at the end of resultset, calling getNext() will get back a dummy row.
5. here the rs2a object will be destroyed.
The first function, begin(), would act just like an STL begin() iterator function, leaving the class ready to fetch the first row in the resultset.
The second one, getNext(), would fetch the next row in the resultset and return it, if available.
Here is the changes in the class declaration:
class ResultSet
{
// ...
public:
// ...
void begin(); // ready to fetch the first row
Row getNext(); // fetch the next row, if available
};
And here is the functions' definition:
void ResultSet::begin()
{
if(rs_ && rs_->data) // 1.
mysql_row_seek(rs_, rs_->data->data);
}
Row ResultSet::getNext()
{
if(rs_ == 0)
return Row(); // 2.
mysql_fetch_row(rs_); // 3.
if(rs_->current_row == 0)
return Row(); // 4.
return Row(rs_->current_row, rs_->field_count); // 5.
}
1. before calling the MySQL function that move the row pointer to the begin, mysql_row_seek(), we ensure that we actually have a resultset to work with, and it has a valid data section.
2. if our resultset is a dummy object, we return a dummy row.
3. low level job, mysql_fetch_row() makes available in current_row the next resultset row.
4. there is no actual row, we create a dummy one and return it.
5. create a row from the raw one, and return it.
We can now have fun writing silly test code to see how it doesn't crash our application, even if it is quite meaningless.
We call on our connection an insert statement. We know that no resultset is returned by it, but let's try to get the next row from it and dump it to cout. What we get is just a "Dummy" output on our console.
We could even try to reset the cursor on the (dummy) resultset calling begin(). No harm is expected:
ResultSet rs1 = c.query("INSERT INTO drivers VALUES('Jackie Stewart', 3)");
if(!rs1)
cout << "As expected, no resultset returned" << endl;
Row silly = rs1.getNext();
silly.dump(cout);
rs1.begin();
A more normal usage of our classes is showed in this piece of code. We perform a SELECT, then we loop on all the resulting rows, dumping them to the output console:
ResultSet rs2 = c.query("SELECT name FROM drivers");
if(rs2)
{
cout << "rs returned by select:" << endl;
while(Row r = rs2.getNext())
r.dump(cout);
}
Let's do now something a bit wierd, using the copy constructor we designed for the ResultSet class:
{
ResultSet rs2a = rs2; // 1.
Row dummy = rs2.getNext(); // 2.
dummy.dump(cout);
rs2a.begin(); // 3.
while(Row r = rs2a.getNext())
r.dump(std::cout);
rs2a.getNext().dump(cout); // 4.
} // 5.
1. copying rs2 to rs2a we actually move the underlying resultset to the new object.
2. rs2 now is empty, we can't do anything sensible with it, in any case we will end up working with dummies.
3. on the other side, before using rs2a we have to reset the cursor, since it has been moved to the end by the previous piece of testing code.
4. again, here rs2a is at the end of resultset, calling getNext() will get back a dummy row.
5. here the rs2a object will be destroyed.
Wrapping up MYSQL_ROW
Our task is to improve the Connection class, to let it a way to return its current row, possibly a dummy one, to the client programmer.
Here we think about the Row class that would wrap the MYSQL_ROW type make it easier to use.
We know that a MYSQL_ROW is nothing more than an array of c-strings (or better, array of bytes, since they could contains null bytes when representing binary data), and we are developing for the C++ language, so a natural way of representing a row would be by a vector of strings.
Besides a couple of constructors, and the boolean operator to let the class user to understand if an object is a real row or a dummy one, we are about to provide just a function to dump the row on an output stream (and a global operator "put to"):
1. dummy ctor, the size of the member vector is set to zero.
Here's the functions' implementation:
1. Actually, this implementation is buggy. As you can see, the string at position i in the vector is initialized with the i c-string in the original MYSQL_ROW. This works fine for all the "normal" fields, it doesn't for the binary ones, that will be truncated at the first null byte. This is not an issue here, but we should keep in mind this limitation.
Here we think about the Row class that would wrap the MYSQL_ROW type make it easier to use.
We know that a MYSQL_ROW is nothing more than an array of c-strings (or better, array of bytes, since they could contains null bytes when representing binary data), and we are developing for the C++ language, so a natural way of representing a row would be by a vector of strings.
Besides a couple of constructors, and the boolean operator to let the class user to understand if an object is a real row or a dummy one, we are about to provide just a function to dump the row on an output stream (and a global operator "put to"):
class Row
{
private:
std::vector<std::string> row_;
public:
Row() : row_(0) {} // 1.
Row(MYSQL_ROW row, unsigned int nr);
_OPERATOR_BOOL() const { return (row_.size() > 0 ? _CONVERTIBLE_TO_TRUE : 0); }
std::ostream& dump(std::ostream& os) const;
};
std::ostream& operator<<(std::ostream& os, const Row& row);
1. dummy ctor, the size of the member vector is set to zero.
Here's the functions' implementation:
Row::Row(MYSQL_ROW row, unsigned int nr) : row_(nr)
{
for(unsigned int i = 0; i < nr; ++i)
row_[i] = row[i]; // 1.
}
std::ostream& Row::dump(std::ostream& os) const
{
if(row_.empty())
{
os << "Dummy" << std::endl;
return os;
}
std::vector<std::string>::const_iterator it;
for(it = row_.begin(); it != row_.end(); ++it)
os << *it << ' ';
os << std::endl;
return os;
}
1. Actually, this implementation is buggy. As you can see, the string at position i in the vector is initialized with the i c-string in the original MYSQL_ROW. This works fine for all the "normal" fields, it doesn't for the binary ones, that will be truncated at the first null byte. This is not an issue here, but we should keep in mind this limitation.
MYSQL_ROW in MYSQL_RES
We don't have big expectations for our toy ResultSet class. We would just like that, when we perform a SELECT on a Connection, the could go through the ResultSet we get back, printing to the output console (and maybe other output streams) any row in it.
Quite a reasonable requirement.
But before working on it we should understand better how a result set is organized in MySQL Connector/C.
Basically, we can think to MYSQL_RES as a container of rows. Well, it is much more than this, but currently we are just interested in this aspect of its personality. The rows are stored in its data section as array of MYSQL_ROW.
A MYSQL_ROW is an array of byte arrays. We could usually think to a single item in a MYSQL_ROW as a c-strings, but we should be aware that it could contain internal null-bytes, when used to represent binary data.
To get the next MYSQL_ROW in a MYSQL_RES we can use the mysql_fetch_row() function. Besides we can access directly a specific position using mysql_row_seek(). This two functions are enough for us, since we want to provide just a way to move forward in the resultset and reset it to the beginning position.
So, what we are going to do in the next couple of posts would be to design a class Row that is going to represent a single row, possibly a dummy one, of a result set; and improve our ResultSet class to give the user a way to iterate on it.
Quite a reasonable requirement.
But before working on it we should understand better how a result set is organized in MySQL Connector/C.
Basically, we can think to MYSQL_RES as a container of rows. Well, it is much more than this, but currently we are just interested in this aspect of its personality. The rows are stored in its data section as array of MYSQL_ROW.
A MYSQL_ROW is an array of byte arrays. We could usually think to a single item in a MYSQL_ROW as a c-strings, but we should be aware that it could contain internal null-bytes, when used to represent binary data.
To get the next MYSQL_ROW in a MYSQL_RES we can use the mysql_fetch_row() function. Besides we can access directly a specific position using mysql_row_seek(). This two functions are enough for us, since we want to provide just a way to move forward in the resultset and reset it to the beginning position.
So, what we are going to do in the next couple of posts would be to design a class Row that is going to represent a single row, possibly a dummy one, of a result set; and improve our ResultSet class to give the user a way to iterate on it.
Wrapping MYSQL_RES in a class
A bad thing about the Connection::query() we developed in the previous post, is that it returns a pointer to MYSQL_RES. This is bad because we are exposing the internal details of MySQL Connector/C to the client programmer; because we are relying on him to check if the pointer is not a NULL before using it; and also because, if the MYSQL_RES we are passing him is not NULL, we should rely on him for its destruction, via a call to mysql_free_result().
If he forgets to do the check against NULL, we risk our application to crash.
If he forgets to call mysql_free_result(), we'll have a memory leak.
To solve this issues we wrap the MYSQL_RES in a class, ResultSet, and we use a variation of the Null Object pattern, to let a dummy ResultSet object to be generated when actually no ResultSet is available.
A decision we have to take designing the ResultSet class is if we want to allow that an object of this class could be copied or not. A conservative suggestion would be not to allow that, the reason being that this would save us to mess around with the internal details of the MYSQL_RES object. On the other side, it could be useful to give the chance to the user to move around ResultSet object.
I have decided for an intermediate solution: a ResultSet object could be moved using the same strategy used, for instance, by std::auto_ptr. That means, after copied, the source object would be nullified.
Let's see a first implementation for the class:
1. Common functionality used by both copy ctor and assignment operator.
2. Null Object ctor.
3. "Normal" ctor.
4. operator to test if actually we have a "real" object or a null one. It is based on defines for the MSVC compiler. To do something portable I could have used the boost library (please assume here that was not possible)
And here is the implementation for the declared methods:
1. Calling mysql_free_result() with NULL is a no-op, so this piece of code works fine also for the Null Object.
So, now we can rewrite Connection::query() in this way:
If he forgets to do the check against NULL, we risk our application to crash.
If he forgets to call mysql_free_result(), we'll have a memory leak.
To solve this issues we wrap the MYSQL_RES in a class, ResultSet, and we use a variation of the Null Object pattern, to let a dummy ResultSet object to be generated when actually no ResultSet is available.
A decision we have to take designing the ResultSet class is if we want to allow that an object of this class could be copied or not. A conservative suggestion would be not to allow that, the reason being that this would save us to mess around with the internal details of the MYSQL_RES object. On the other side, it could be useful to give the chance to the user to move around ResultSet object.
I have decided for an intermediate solution: a ResultSet object could be moved using the same strategy used, for instance, by std::auto_ptr. That means, after copied, the source object would be nullified.
Let's see a first implementation for the class:
class ResultSet
{
private:
MYSQL_RES* rs_;
void moveToThis(ResultSet& rhs); // 1.
public:
ResultSet() : rs_(0) {} // 2.
ResultSet(MYSQL_RES* rs) : rs_(rs) {} // 3.
ResultSet(ResultSet& rhs);
ResultSet& operator=(ResultSet& rhs);
~ResultSet();
_OPERATOR_BOOL() const { return (rs_ != 0 ? _CONVERTIBLE_TO_TRUE : 0); } // 4.
};
1. Common functionality used by both copy ctor and assignment operator.
2. Null Object ctor.
3. "Normal" ctor.
4. operator to test if actually we have a "real" object or a null one. It is based on defines for the MSVC compiler. To do something portable I could have used the boost library (please assume here that was not possible)
And here is the implementation for the declared methods:
void ResultSet::moveToThis(ResultSet& rhs)
{
rs_ = rhs.rs_;
rhs.rs_ = 0;
}
ResultSet::ResultSet(ResultSet& rhs)
{
moveToThis(rhs);
}
ResultSet& ResultSet::operator=(ResultSet& rhs)
{
moveToThis(rhs);
return *this;
}
ResultSet::~ResultSet()
{
mysql_free_result(rs_); // 1.
}
1. Calling mysql_free_result() with NULL is a no-op, so this piece of code works fine also for the Null Object.
So, now we can rewrite Connection::query() in this way:
ResultSet Connection::query(const char* command)
{
if(mysql_query(&mysql_, command))
throw MySQLException(error());
// if no resultset is expected, return a dummy
if(mysql_.field_count == 0)
return ResultSet();
MYSQL_RES* rs = mysql_store_result(&mysql_);
return ResultSet(rs);
}
mysql_query() and mysql_store_result()
Now we want to improve the class Connection, our wrapper to the MYSQL object as available through the MyQSL Connector/C.
If we use a Connection object, as described in the previous post, we could just estabilish a connection to a MySQL server on creation and close it as it is destroyed. Adding a way to execute a SQL statement on the opened connection is a natural request.
The Connector/C function mysql_query() is meant for this task; it requires the address of the MYSQL object on which operate, and the statement itself, as a c-string. The return value is an error code, or 0 in case of no error.
That is fine for SQL statements that do not return any interesting value, like CREATE TABLE, or INSERT INTO. But this is not enough for SELECT, where we actually want to see which rows are returned.
For that task we have to call another function, mysql_store_result(), that returns a pointer to the MYSQL_RES structure containg the so called resultset generated from the previous call to mysql_query().
The flag field_count in the MYSQL object is used to signal if there is a resultset to be retrieved, as result of query execution.
So, let's write a first version of a query function for the Connection class.
We change the interface adding the declaration of the new public function:
And here is the definition:
1. in case of error, we throw an exception containing error code and error message.
2. we check the field_count to see if a resultset is expected, if not, we return NULL.
3. otherwise we return the resultset. Notice that in this case the memory associated to the MYSQL_RES pointer has to be freed by a call to mysql_free_result().
Now we can start doing stuff on the MySQL server, for instance create a database:
Create a table:
Or insert a row in a table:
If we check the value returned by all these call we'll find that always a NULL is returned.
If we use a Connection object, as described in the previous post, we could just estabilish a connection to a MySQL server on creation and close it as it is destroyed. Adding a way to execute a SQL statement on the opened connection is a natural request.
The Connector/C function mysql_query() is meant for this task; it requires the address of the MYSQL object on which operate, and the statement itself, as a c-string. The return value is an error code, or 0 in case of no error.
That is fine for SQL statements that do not return any interesting value, like CREATE TABLE, or INSERT INTO. But this is not enough for SELECT, where we actually want to see which rows are returned.
For that task we have to call another function, mysql_store_result(), that returns a pointer to the MYSQL_RES structure containg the so called resultset generated from the previous call to mysql_query().
The flag field_count in the MYSQL object is used to signal if there is a resultset to be retrieved, as result of query execution.
So, let's write a first version of a query function for the Connection class.
We change the interface adding the declaration of the new public function:
class Connection
{
// ...
public:
//...
MYSQL_RES* query(const char* command);
And here is the definition:
MYSQL_RES* Connection::query(const char* command)
{
if(mysql_query(&mysql_, command)) // 1.
throw MySQLException(error());
if(mysql_.field_count == 0) // 2.
return 0;
return mysql_store_result(&mysql_); // 3.
}
1. in case of error, we throw an exception containing error code and error message.
2. we check the field_count to see if a resultset is expected, if not, we return NULL.
3. otherwise we return the resultset. Notice that in this case the memory associated to the MYSQL_RES pointer has to be freed by a call to mysql_free_result().
Now we can start doing stuff on the MySQL server, for instance create a database:
c.query("CREATE DATABASE mytest");
Create a table:
c.query("CREATE TABLE drivers(name VARCHAR(30), champ INT)");
Or insert a row in a table:
c.query("INSERT INTO drivers VALUES('Juan Manuel Fangio', 5)");
If we check the value returned by all these call we'll find that always a NULL is returned.
Connecting to MySQL in a ++ way
As we have seen in the previous post, connecting to a MySQL server through the connector/C is easy - but just a bit verbose.
Wouldn't be nice if we could use an object-oriented structure to delegate someone else all the trivial tasks and just care about the core activity?
Well, instead of using connector/C we could use connector/C++ (natively provided by Oracle-MySQL), or the third-party wrapper to connector/C named MySQL++.
For a few good reasons, I currently cannot do this smart move now, but what I can do is providing a little object oriented wrapper of mine to the native C API. The main advantage in this approach being that I'll something more to write in this blog.
So, what I'm going to do here is writing a class, named Connection, that would make possible reduce the effort for the client programmer who just wants to connect to a MySQL server on localhost to this sleek piece of code:
First thing: instead of relying on C-style error codes and messages, we are about to use exceptions. So, we create a specific exception, derived from std::exception, that would identify errors coming from our wrapper:
1. I'm developing for VC++, so as inclusion guard I use this pragma - not a good idea, if the code should be used on different architectures. Just assume this is not the case.
That was easy.
Second step is not that simple: we are about to design a first version of our Connection class.
Currently the requirements are not very demanding: we just want to be able to specify in the constructor user name and password, and leave all the other parameters as default. But it comes naturally to provide a way to set these "other" parameters before calling the constructor. I decided to make them static member variable of the class, and provide static setters to change them according to the requirements.
Then we'll have a constructor, accepting user and password as char*, that will call the mysql_init() and mysql_real_connect(); and a destructor, for the mysql_close() call. The beauty of having the MYSQL cleanup function in the Connection destructor is that we could stop worrying about forgetting to call it. It is done automatically when our Connection object goes out of scope.
Make sense also thinking about a way of converting the errors as generated by connector/C in a way that is easier manageable by the exception constructor. As saying: starting from the current error number and error description, we want to get a string containing both. We'll do this in a short private class function.
Obviously, in the private section of the class we'll put also the MYSQL object that is going to be used by all the related functions.
By the way, we don't want to have copies of a connection object, so we disallow copy constructor and assignment operator, declaring them private (with no definition). As stated by MySQL Reference Manual: "You should not try to make a copy of a MYSQL structure. There is no guarantee that such a copy will be usable".
Third step, let's write the code. Here is the complete class declaration:
And here is the definition of the class members:
Given that, we could actually test our connection with the single-liner we have written many line above, but it is more sensible giving some feedback to the output console and catching the possible exception. So we have this code:
Wouldn't be nice if we could use an object-oriented structure to delegate someone else all the trivial tasks and just care about the core activity?
Well, instead of using connector/C we could use connector/C++ (natively provided by Oracle-MySQL), or the third-party wrapper to connector/C named MySQL++.
For a few good reasons, I currently cannot do this smart move now, but what I can do is providing a little object oriented wrapper of mine to the native C API. The main advantage in this approach being that I'll something more to write in this blog.
So, what I'm going to do here is writing a class, named Connection, that would make possible reduce the effort for the client programmer who just wants to connect to a MySQL server on localhost to this sleek piece of code:
Connection connection("root", "password");
First thing: instead of relying on C-style error codes and messages, we are about to use exceptions. So, we create a specific exception, derived from std::exception, that would identify errors coming from our wrapper:
#pragma once // 1.
#include <exception>
#include <string>
class MySQLException : public std::exception
{
public:
MySQLException(const char* what) : exception(what) {}
MySQLException(const std::string& what) : exception(what.c_str()) {}
};
1. I'm developing for VC++, so as inclusion guard I use this pragma - not a good idea, if the code should be used on different architectures. Just assume this is not the case.
That was easy.
Second step is not that simple: we are about to design a first version of our Connection class.
Currently the requirements are not very demanding: we just want to be able to specify in the constructor user name and password, and leave all the other parameters as default. But it comes naturally to provide a way to set these "other" parameters before calling the constructor. I decided to make them static member variable of the class, and provide static setters to change them according to the requirements.
Then we'll have a constructor, accepting user and password as char*, that will call the mysql_init() and mysql_real_connect(); and a destructor, for the mysql_close() call. The beauty of having the MYSQL cleanup function in the Connection destructor is that we could stop worrying about forgetting to call it. It is done automatically when our Connection object goes out of scope.
Make sense also thinking about a way of converting the errors as generated by connector/C in a way that is easier manageable by the exception constructor. As saying: starting from the current error number and error description, we want to get a string containing both. We'll do this in a short private class function.
Obviously, in the private section of the class we'll put also the MYSQL object that is going to be used by all the related functions.
By the way, we don't want to have copies of a connection object, so we disallow copy constructor and assignment operator, declaring them private (with no definition). As stated by MySQL Reference Manual: "You should not try to make a copy of a MYSQL structure. There is no guarantee that such a copy will be usable".
Third step, let's write the code. Here is the complete class declaration:
#pragma once
#include <string>
#include "my_global.h"
#include "mysql.h"
class Connection
{
private:
// default parameters
static std::string host_;
static std::string db_;
static unsigned int port_;
static std::string socket_;
static unsigned long flags_;
MYSQL mysql_;
std::string error();
Connection& operator=(const Connection&); // no assignment
Connection(const Connection&); // no copy ctor
public:
static void setHost(const std::string& host) { host_ = host; }
static void setDatabase(const std::string& db) { db_ = db; }
static void setSocket(const std::string& socket) { socket_ = socket; }
static void setPort(const unsigned int port) { port_ = port; }
static void setFlags(const unsigned long flags) { flags_ = flags; }
Connection(const char* user, const char* password);
~Connection();
};
And here is the definition of the class members:
#include <sstream>
#include "Connection.h"
#include "MySQLException.h"
// default connection values
std::string Connection::host_ = "localhost";
std::string Connection::db_;
unsigned int Connection::port_ = 3306;
std::string Connection::socket_;
unsigned long Connection::flags_;
std::string Connection::error()
{
std::ostringstream oss;
oss << mysql_errno(&mysql_) << " " << mysql_error(&mysql_);
return oss.str();
}
Connection::Connection(const char* user, const char* password)
{
if(mysql_init(&mysql_) == 0)
throw MySQLException(error());
if(mysql_real_connect(&mysql_, host_.c_str(), user, password,
db_.c_str(), port_, socket_.c_str(), flags_) == 0)
{
throw MySQLException(error());
}
}
Connection::~Connection()
{
mysql_close(&mysql_);
}
Given that, we could actually test our connection with the single-liner we have written many line above, but it is more sensible giving some feedback to the output console and catching the possible exception. So we have this code:
#include <iostream>
#include "Connection.h"
#include "MySQLException.h"
using std::cout;
using std::endl;
void ms02()
{
try
{
cout << "Opening a connection to mysql" << endl;
Connection c("root", "password");
}
catch(const MySQLException& mse)
{
cout << "Error: " << mse.what() << endl;
return;
}
cout << "Job done." << endl;
}
Checking a connection to MySQL
Let's write a minimal program that just try to connect to a MySQL server.
The assumption is that we are working in C++ for MS-Windows and we are using the MySQL connector/C as API to connecting to the database.
It is a very simple piece of code but, since we are using a C library it happens to be quite verbose and requires us to be acquainted with a type, MYSQL, and a few functions.
mysql_init()
The MYSQL type is actually a typedef for the st_mysql structure. In any case, it is a collection of data related with the database connection.
First thing we have to do is calling mysql_init(), that (allocates room and) initializes a MYSQL object in a way that we could use it to open a MySQL connection. If we pass to it a NULL pointer, it allocates a new MYSQL object before initializing it, otherwise it uses the one we provide it.
The return value is the pointer to the MYSQL object, or NULL in case of error.
mysql_options()
This function is used to change many different connection setting on the initialized MYSQL object. Currently we don't need to do anything like that, so just remember this useful piece of information for another time.
mysql_real_connect()
To connect to the database we could use the mysql_connect() function, but it is deprecated, so we just forget about it and call mysql_real_connect().
Besides an initialized MYSQL object, we need the name of the host the database sits on (NULL means "localhost"), the user name, password, database name (could be left NULL, if set, determines the default database name for the connection), port (used in case of TCP/IP connection, if we give a 0, the MySQL default 3306 will be used), unix socket number (for socket or pipe connection), and client flags.
It returns NULL in case of error.
mysql_error()
When we see a mysql_* function returns an error we can get a human readable description of it calling mysql_error() for the current MYSQL object.
mysql_close
At the end of the day, we should cleanup the connection, and this is done by mysql_close().
As we can see in the example below, it is faster to write the code:
The assumption is that we are working in C++ for MS-Windows and we are using the MySQL connector/C as API to connecting to the database.
It is a very simple piece of code but, since we are using a C library it happens to be quite verbose and requires us to be acquainted with a type, MYSQL, and a few functions.
mysql_init()
The MYSQL type is actually a typedef for the st_mysql structure. In any case, it is a collection of data related with the database connection.
First thing we have to do is calling mysql_init(), that (allocates room and) initializes a MYSQL object in a way that we could use it to open a MySQL connection. If we pass to it a NULL pointer, it allocates a new MYSQL object before initializing it, otherwise it uses the one we provide it.
The return value is the pointer to the MYSQL object, or NULL in case of error.
mysql_options()
This function is used to change many different connection setting on the initialized MYSQL object. Currently we don't need to do anything like that, so just remember this useful piece of information for another time.
mysql_real_connect()
To connect to the database we could use the mysql_connect() function, but it is deprecated, so we just forget about it and call mysql_real_connect().
Besides an initialized MYSQL object, we need the name of the host the database sits on (NULL means "localhost"), the user name, password, database name (could be left NULL, if set, determines the default database name for the connection), port (used in case of TCP/IP connection, if we give a 0, the MySQL default 3306 will be used), unix socket number (for socket or pipe connection), and client flags.
It returns NULL in case of error.
mysql_error()
When we see a mysql_* function returns an error we can get a human readable description of it calling mysql_error() for the current MYSQL object.
mysql_close
At the end of the day, we should cleanup the connection, and this is done by mysql_close().
As we can see in the example below, it is faster to write the code:
#include <iostream>
#include "my_global.h" // this is required when working for MS-Windows
#include "mysql.h"
using std::cout;
using std::endl;
void ms01()
{
cout << "Opening a connection to mysql" << endl;
MYSQL mysql; // we allocate the memory for the object, putting it on the stack
if(mysql_init(&mysql) == 0)
{
cout << "Can't initialize mysql object" << endl;
return;
}
// first 0 is for localhost, se use the default port, and specify no database
if(mysql_real_connect(&mysql, 0, "root", "password", 0, 0, 0, 0) == 0)
{
cout << "Connection failed: " << mysql_error(&mysql) << endl;
return;
}
mysql_close(&mysql);
cout << "Connection to mysql opened correctly" << endl;
}
Subscribe to:
Posts (Atom)