Pages

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.

No comments:

Post a Comment