Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Connecting to Oracle via JDBC

There are a couple of ways to connect to a recent Oracle Database, going through the standard DriverManager class or the specific OracleDataSource one. Let's see both of them. And, when we are there, let's add also some consideration on connecting to MySql via DriverManager.

It's a bit of an overkill, but I have written a little abstract class that is going to be the root of a hierarchy of classes for providing access to database through JDBC:
public abstract class Connector {
    public abstract Connection getConnection() throws SQLException;
 
    public String getDatabaseVersion(Connection conn) throws SQLException {
        return conn.getMetaData().getDatabaseProductVersion();
    }
}
The point of it is that each different concrete class has its own way to get a database connection but all of them would use it in the same way. So the getConnection() method is abstract where the actual method performing JDBC operation, like getDatabaseVersion(), would act in the same way.

Using OracleDataSource

The OracleDataSourceConnector extends Connector, and its raison d'être is keeping an instance of a OracleDataSource object as its private data member. Initialized in the constructor, is used by the the getConnection() method to return a database connection.
public class OracleDataSourceConnector extends Connector {
    private OracleDataSource ods;

    public OracleDataSourceConnector(String url, String user, String password) throws SQLException {
        ods = new OracleDataSource();
        ods.setURL(url);
        ods.setUser(user);
        ods.setPassword(password);
    }

    @Override
    public Connection getConnection() throws SQLException {
        return ods.getConnection();
    }
}
The good thing about OracleDataSource is that it has its own pool of connections that is managed implicitly. On the flip side, it is not standard JDBC. That means extra work if we want to adapt our code to use a different database.

I have written a tester to check the functionality, OracleDataSourceConnectorTest. See it on GitHub for full reference. There are only a few things that I want to stress here.
public class OracleDataSourceConnectorTest {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521/orclpdb";  // 1
    private static final String USER = "hr";
    private static final String PASSWORD = "hr";

    private static OracleDataSourceConnector ods;
    private static Connection conn;

    @BeforeClass
    public static void setUp() {  // 2
        try {
            ods = new OracleDataSourceConnector(URL, USER, PASSWORD);
            conn = ods.getConnection();
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    }

    // ...
}
1. I'm using the thin Oracle JDBC driver, the other choice is the OCI one. See the Oracle documentation if you wonder which one to use. Short answer is, usually thin is the one you want to peek. My database is local, on the standard port, and the service is named orclpdb. Your setup may vary.
2. I setup the connector and a connection through this static method called only once before the tests in the class are called. The sense is that I don't want to repeat expensive operations without a reason. So, when nothing is against it, I would reuse connector and connection in more tests.

And here is a test that requires its own connector, because I want to perform a disruptive negative test:
@Test
public void testBadUser() {
 OracleDataSourceConnector connector = null;
 try {
  connector = new OracleDataSourceConnector(URL, "Unknown", PASSWORD);  // 1
 } catch (SQLException e) {
  fail(e.getMessage());
 }
 
 try {
  connector.getConnection();  // 2
  fail("No connection expected for unknown user");
 } catch (SQLException e) {
  String expectedState = "72000";
  assertEquals(expectedState, e.getSQLState());
  
  int expectedCode = 1017;
  assertEquals(expectedCode, e.getErrorCode());
 }
}
1. I pass a bas user name to the connector. No failure is expected here, since no connection is actually done.
2. I expect the failure to happen here. Oracle should react with a ORA-01017 error code, that is included in the SQL state 72000, SQL execute phase errors.

The other test ensures that I can actually get to the database:
@Test
public void testGetDatabaseNameVersion() {
 try {
  String expected = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production";
  String actual = ods.getDatabaseVersion(conn);
  assertEquals(expected, actual);
 } catch (SQLException e) {
  fail(e.getMessage());
 }
}
Using DriverManager

If I am not interested in the connection pooling service offered by OracleDataSource, and I prefer to keep as generic as I can, I could use these other solution. In the old days, it required to perform an explicit registration of the JDBC driver:
Class.forName(klass)
Where klass is the actual class name, like "oracle.jdbc.driver.OracleDriver" or "com.mysql.jdbc.Driver".

Now this is done implicitly by the DriverManager, that leads to a very slim connector:
public class PlainConnector extends Connector {
 private String url;
 private String user;
 private String password;

 public PlainConnector(String url, String user, String password) {
  this.url = url;
  this.user = user;
  this.password = password;
 }

 @Override
 public Connection getConnection() throws SQLException {
  return DriverManager.getConnection(url, user, password);
 }
}
The constructor just store a copy of the data for connecting to the database, the getConnection() uses them going through DriverManager.

I have written another test case to see that actually the plain connector works as the OracleDataSource one. An then another one to see what I have to change to access with the same class a different database, here MySql.

This test case is accessing a MySql database that I have installed locally and on which I have added a user named "hr", to keep it close to the Oracle one. You can see how minimal are the required changes.

I had to change the URL:
private static final String URL = "jdbc:mysql://localhost:3306/hr?useSSL=false";
Notice the parameter useSLL set to false, to remove the MySql warning "Establishing SSL connection without server's identity verification is not recommended." In this test we can live without id verification.

In case of bad user, I expect now 28000 as SQL state and 1045, access denied, as error code.

And, the database version now should be something like "5.7.19-log"

Ah, right. Remember to put in your application build path the jar for oracle JDBC (currently named ojdbc8.jar) and for MySql (mysql-connector-java-5.1.43-bin.jar) and to have the databases up and running, if you want your tests to succeed.

Reference: Oracle Database JDBC Developer's Guide 12c Release 2

Full Java code on GitHub.

Go to the full post

From MySQL to HTML table

I'm using PHP to generate some HTML. Once estabilished a connection to our MySQL database, I want to perform a select on one of its table and show the result to the user.

Assuming the connection has been successfully estabilished, and that we have its resulting object in $conn, we select all the items from a table getting the resultset in the $result variable:

$query = "select * from events";
($result = mysql_query($query, $conn)) or die("Error accessing database");

The error handling is quite crude: if for any reason the select fails, we just output an error message and terminate the page generation.

Now the fun stuff: we write a function that gets in input the resultset, as returned by a successful call to mysql_query(), and output its data in an HTML table. To make it more readable we alternate yellow rows to light gray ones:

function myPrintAsTable($result)
{
print "<table><tbody>";

$rows = mysql_num_rows($result); // 1.
$cols = mysql_num_fields($result); // 2.
for($i = 0; $i < $rows; ++$i) // 3.
{
$row = mysql_fetch_row($result); // 4.
if($i%2) // 5.
print '<tr style="background-color: lightgrey;">';
else
print '<tr style="background-color: yellow;">';

for($j = 0; $j < $cols; ++$j) // 6.
print "<td>". $row[$j] ."</td>";

print "</tr>";
}
print "</table></tbody>";
}

1. mysql_num_rows() extracts from a resultset the number of rows in it.
2. mysql_num_fields() extracts from a resultset its number of fields (or columns, if we think to them in terms of table terms).
3. Loop on all the rows.
4. mysql_fetch_row() reads the next row and moves the cursor ahead, ready for the next fetch.
5. Set the background color for the current table row in alternate colors.
6. Loop on all the columns and put each field in a different table data tag.

If you have, or could have, multiline text among the data in your table, you should think about using the nl2br() function, that converts any newline in HTML <br> tags, so that you can actually display it to the user.

Go to the full post

MySQL from PHP

Connecting from PHP to MySQL is quite easy, assuming that you have all the required information.

Assuming that we want to connect to a MySQL database on the localhost by the root user that has a very unsafe password to use the test schema, we could do that by this function:

function myConnect() {
$dbHost = "localhost";
$dbUser = "root";
$dbPassword = "password";
$dbSchema = "test";

($conn = mysql_connect($dbHost, $dbUser, $dbPassword)) or die ("Can't connect");
mysql_select_db($dbSchema, $conn) or die ("Can't select database");

return $conn;
}

It's not a nice piece of code, in case of error is quite brutal, actually. But it should be clear what it does.

Go to the full post

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:

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;
}

Go to the full post

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:

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;
}

Go to the full post

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:

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.

Go to the full post

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"):

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.

Go to the full post

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.

Go to the full post

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:

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);
}

Go to the full post

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:

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.

Go to the full post

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:

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;
}

Go to the full post

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:

#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;
}

Go to the full post

Qt and MySql

I have to use Qt and MySql. This is not exactely a piece of cake, expecially the first time you do that, because there is a number of prerequisite to be filled out before actually start programming.

You should have Qt and MySql installed and working on your machine - but, well, this is quite a natural requirement.

Then you have to build the plugin for MySql. This has to be done for any database you want to use with Qt but SQLite, that is provided by default with Qt.

Building a database plugin is not difficult, but imply a different procedure for each database and platform. Luckly it is well described in the official documentation.

I found that compiling and using a database plugin is a sensitive process, probabily the fact is that we have to work with specific concepts for an operating system, a compiler, a database, all of these viewed from the point of view of the Qt framework. An inerently complex environment, I reckon. So, I was not surprised my plugin did not working at first try. The Qt framework is not very helpful in let you know which is the actual problem, and why it does not like our beautiful plugin, but after a while you should usually get the point and make it working properly.

A few suggestions if you are stuck somewhere in the process: check if the database is properly installed and the required files are available to your compiler. Check if the plugin was correctly created (in the Qt plugins/sqldrivers folder). Check if the dependecies for the generated DLL are available. For Windows-Visual C++ you could use the utility depends.exe (Dependency Walker) to find that out. If you have a problem of this kind, usually is LibMySql.ddl that is not in a PATH visible from your environment.
Another DLL that is often reported missing, IESHIMS.DLL, actually is not a mandatory one, so you don't usually have to pay attention to it.

Done that, you are ready for writing code for accessing your database from Qt.

Fist thing: you have to tell to your project you are actually using the Qt database facility. This is done in the project (.pro) file, adding the word "sql" to the QT property, that should look something like that:

QT += core gui sql

If our mysql database is not up and running, well, it is better to start it up. If you are on Windows and you want do that by shell command remember you need to have the administrator rights to do that. So open your cmd shell window as administrator before giving the "net start mysql" command.

At this point, opening a connection to the database is not anymore a big issue.

Accordingly to Jasmin and Mark, this is typically done in the application main function. I wouldn't stress much this point, and I just would say that is good enough for us to do that there.

It's only a toy connection, so we don't pay much attention to security, reusability and other issues like those, and so we just write this simple function:

bool createConnection()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName("test");
db.setUserName("root");
db.setPassword("password");
if(db.open() == false)
{
QMessageBox::critical(0, QObject::tr("Database Error"), db.lastError().databaseText());
return false;
}
return true;
}

It is worth noting that we specify the database brand calling the QSqlDatabase::addDatabase() static function, and then, after setting the usual parameters required for estabilishing a connection, we try to open it.

This function is used in this way:

#include <QtGui/QApplication>
#include <QtGui/QMessageBox>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>

#include "MainWindow.h"

namespace
{
bool createConnection()
{
// ...
}
}

int main(int argc, char *argv[])
{
QApplication app(argc, argv);

if(createConnection() == false)
return 1;

MainWindow mw;
mw.show();

return app.exec();
}

I wrote this post while reading "C++ GUI Programming with Qt 4, Second Edition" by Jasmin Blanchette and Mark Summerfield.

Go to the full post