I am using JDBC with SQLite and am running into a problem where I get the
exception:
Cannot commit transaction - SQL statements in progress
I searched the net and posted in another forum but I can't seem to find a
solution.
Once I run the method below called AddMealPlan I cannot execute anymore SQL
statements without getting the exception above.
For example if I run the other method below called GetCurrentUserMealPlans
before running AddMealPlan it works fine but if I call it after I get the
exception.
public static boolean AddMealPlan(MealPlan mealPlan) {
try {
Connection connection = MainDatabase.GetConnection();
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT MAX(ID)
FROM MealPlans");
int nextID;
if(!rs.next())
nextID = 1;
else
nextID = rs.getInt(1) + 1;
rs.close();
statement.executeUpdate("INSERT INTO MealPlans VALUES("
+ nextID + ", '" + mealPlan.getName() + "', '" + mealPlan.getDescription() +
"', '" + User.GetCurrentUser().getUsername() + "')");
System.out.println("INSERT INTO MealPlans VALUES(" +
nextID + ", '" + mealPlan.getName() + "', '" + mealPlan.getDescription() + "',
'" + User.GetCurrentUser().getUsername() + "')");
Vector<MealPlanRecipe> recipes = mealPlan.getRecipes();
for(int index = 0; index < recipes.size(); index++) {
statement.executeUpdate("INSERT INTO
MealPlanRecipes VALUES(" + nextID + ", " + recipes.get(index).getID() + ", " +
recipes.get(index).getDayOfWeek() + ", " + recipes.get(index).getAmount() +
")");
System.out.println("INSERT INTO MealPlanRecipes
VALUES(" + nextID + ", " + recipes.get(index).getID() + ", " +
recipes.get(index).getDayOfWeek() + ", " + recipes.get(index).getAmount() +
")");
}
statement.close();
connection.close();
} catch(SQLException e) {
System.err.println(e.getMessage());
return false;
}
return true;
}
public static Vector<MealPlan> GetCurrentUserMealPlans() {
Vector<MealPlan> mealPlans = new Vector<MealPlan>();
try {
Connection connection = MainDatabase.GetConnection();
Statement statement = connection.createStatement();
Statement statement1 = connection.createStatement();
Statement statement2 = connection.createStatement();
Statement statement3 = connection.createStatement();
Statement statement4 = connection.createStatement();
ResultSet rsMealPlans = statement.executeQuery("SELECT
* FROM MealPlans WHERE Owner = '" + User.GetCurrentUser().getUsername() + "'");
while(rsMealPlans.next()) {
Vector<MealPlanRecipe> recipes = new
Vector<MealPlanRecipe>();
ResultSet rsMealPlanRecipes =
statement1.executeQuery("SELECT * FROM MealPlanRecipes WHERE MealPlanID = " +
rsMealPlans.getInt(1));
while(rsMealPlanRecipes.next()) {
ResultSet rsRecipe =
statement2.executeQuery("SELECT * FROM Recipes WHERE ID = " +
rsMealPlanRecipes.getInt(2));
Vector<RecipeIngredient> ingredients =
new Vector<RecipeIngredient>();
ResultSet rsRecipeIngredients =
statement3.executeQuery("SELECT * FROM RecipeIngredients WHERE RecipeID = " +
rsRecipe.getInt(1));
while(rsRecipeIngredients.next()) {
ResultSet rsIngredient =
statement4.executeQuery("SELECT * FROM Ingredients WHERE ID = " +
rsRecipeIngredients.getInt(2));
double nutrients[] = new
double[Ingredient.NUM_NUTRIENTS];
for(int index = 0, column = 6;
index < nutrients.length ; index++, column++)
nutrients[index] =
rsIngredient.getDouble(column);
ingredients.add(new
RecipeIngredient(rsIngredient.getInt(1), rsIngredient.getString(2),
rsIngredient.getString(3), rsIngredient.getDouble(4),
rsIngredient.getString(5), nutrients, rsRecipeIngredients.getDouble(3)));
rsIngredient.close();
}
recipes.add(new
MealPlanRecipe(rsRecipe.getInt(1), rsRecipe.getString(2),
rsRecipe.getString(3), ingredients, rsMealPlanRecipes.getDouble(3),
rsMealPlanRecipes.getInt(4)));
rsRecipeIngredients.close();
rsRecipe.close();
}
mealPlans.add(new
MealPlan(rsMealPlans.getInt(1), rsMealPlans.getString(2),
rsMealPlans.getString(3), recipes));
rsMealPlanRecipes.close();
}
rsMealPlans.close();
statement.close();
statement1.close();
statement2.close();
statement3.close();
statement4.close();
connection.close();
} catch(SQLException e) {
System.err.println(e.getMessage());
return null;
}
return mealPlans;
}
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
