Losing results when iterating over 1 to many table

Question

My dilemma is that I am losing my ResultSet from the primary table when accessing a foreign key table. I need to save the results from the first table query while I query another table.

Scenerio:
I am implementing a recipe database system. Each recipe can have 1 to many ingredients. To resolve the 1 to many relationship, I created a separate table for the ingredients.

I have a table of recipes:

  ID_Recipe: primary key, integer;   
  Recipe_Title: CHAR(128);

And a table of ingredients:

ID_Ingredient:  PRIMARY KEY, INTEGER;  
ID_Recipe:  INTEGER NOT NULL;  
Ingredient_Title: CHAR(64)  

In my program, I have a recipe object that contains a vector of ingredients:

struct Ingredient
{
  int ID;
  int recipe_ID;
  std::string title;
};

struct Recipe
{
  int ID;
  std::string title;
  std::vector<Ingredient> recipe_ingredients;
};

In order to perform a for each iteration on a recipe in the table, I must load it from the database. To complete the recipe, I have to load in all of the ingredients associated with the recipe.

The problem is that when I load in the ingredients, I lose the result set of the recipes. The MySQL Connector C++ can only handle one result set at a time. I have had no luck in copying the results either (when the table size grows, I may not want to load in the entire results).

So, how can I maintain a cursor or pointer to the recipe table while I search and load from the ingredient table?

Here is what I want to do:

1. For each recipe in the table do:
2. Read recipe result row data into Recipe variable.
3. Use recipe ID to select all ingredients where Recipe.ID_Recipe = Ingredient.ID_Recipe.
4. Load results from ingredient table into vector of Recipe variable.
5. Pass recipe object to call-back function.
6. Advance to next recipe in the table.
7. End-for

Thanks in advance for any suggestions.
(I'm using MySQL, MySQL Connector C++, Visual Studio 2008 - C++)

Answer 1

Just return one result set. Have a column (or columns) called recipe which stays the same for each ingredient. Do a join.

eg

select * from recipe left join ingr on recipe. ID_Recipe = ingr. ID_Recipe

This will give you a table you loop over... when the recipe name changes then you know you are on to a new recipe.

Answer 2

AFAICT, there's nothing in the MySQL Connector/C++ doc indicating that opening a second ResultSet invalidates the first one. Please post the code you use to process the rows. You may be doing something that closes the first ResultSet prematurely.