Page 1 of 1
[SOLVED] Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 3:24 am
by Slyde
Hi Forum:
I'm new to C++ and use Code::Blocks and wxSQLite3. Everything is going good except that I'm having trouble using variables in a query statement. Let me show you:
Code: Select all
wxString rec_id = "";
wxString wt = "";
if (RadioButton_Grams)
wt = "0";
else
wt = "1";
wxString var1 = txtProductName->GetValue();
db->Open("zyx.db");
db->ExecuteQuery("INSERT INTO recipe_names(recipe, weight_type) VALUES(" + var1 + ", " + wt + ")");
wxSQLite3ResultSet Res = db->ExecuteQuery("SELECT recipe_id FROM recipe_names WHERE recipe = " + var1 + ")");
rec_id = Res.GetAsString(0);
It compiles fine, but wxSQLite3 crashes my program as soon as those lines are executed. Will someone show me how I'm to use variables in these queries? And, if you don't mind, I'd rather use prepared statements. I use them on websites with PHP/MariaDB. I assume it's pretty much the same here. But until I get a better understanding (and I'm a simple-minded person) of wxSQLite3, I'm kinda lost.
The database schema (I think that's what it's called) is:
Code: Select all
CREATE TABLE recipe_names( recipe_id INTEGER PRIMARY KEY AUTOINCREMENT, recipe TEXT, weight_type INTEGER )
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 4:53 am
by Slyde
I got this partially sorted out.
Code: Select all
db->Open("zyx.db");
db->ExecuteQuery("INSERT INTO recipe_names(recipe, weight_type) VALUES('" + var1 + "', '" + wt + "');");
wxSQLite3ResultSet Res2 = db->ExecuteQuery("SELECT recipe_id FROM recipe_names WHERE recipe = '" + var1 + "');");
rec_id = Res2.GetAsString(0);
db->Close();
My problem now is the INSERT query works, but the following SELECT query fails. I see nothing wrong with it, but it crashes my program.
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 6:21 am
by Slyde
The extra closed parenthesis at the end of the second query will be shot at sunrise!
Thanks for the help
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 6:52 am
by utelle
Slyde wrote:I got this partially sorted out.
Code: Select all
db->Open("zyx.db");
db->ExecuteQuery("INSERT INTO recipe_names(recipe, weight_type) VALUES('" + var1 + "', '" + wt + "');");
wxSQLite3ResultSet Res2 = db->ExecuteQuery("SELECT recipe_id FROM recipe_names WHERE recipe = '" + var1 + "');");
rec_id = Res2.GetAsString(0);
db->Close();
My problem now is the INSERT query works, but the following SELECT query fails. I see nothing wrong with it, but it crashes my program.
Obviously you found out that a string value in SQL needs to be surrounded by single quotes. However, your approach is still a bit dangerous, in case your recipe names can contain single quote characters. The much better and safer approach would be to use prepared SQL statements. The following code shows how this could look like:
Code: Select all
try
{
db->Open("zyx.db");
wxSQLite3Statement stmt1 = db->PrepareStatement("INSERT INTO recipe_names (recipe, weight_type) VALUES(?,?)");
stmt1.Bind(1, var1);
stmt1.Bind(2, wt);
stmt1.ExecuteUpdate();
wxSQLite3Statement stmt2 = db->ExecuteQuery("SELECT recipe_id FROM recipe_names WHERE recipe = ?");
stmt2.Bind(1, var1);
wxSQLite3ResultSet res2 = stmt2.ExecuteQuery();
while (res2.NextRow())
{
rec_id = res2.GetAsString(0);
}
}
catch (wxSQLite3Exception& e)
{
// Handle exceptions
}
Please note that wxSQLite3 uses exceptions to communicate problems on executing SQL statements. That is, you have to use try-catch blocks to avoid that your application crashes in case of errors.
Regards,
Ulrich
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 7:56 am
by Slyde
Thank you for the heads up. Your example code is very much appreciated. I left Qt to use wxSQLite3 with Code::Blocks. The learning curve is a little steeper this way, but I favor this to using a hybrid C++. Thanks.
I see you used ExecuteUpdate for the INSERT statement and ExecuteQuery for the SELECT statement. What's the difference? Is there one that works for all?
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 10:22 am
by utelle
Slyde wrote:Thank you for the heads up. Your example code is very much appreciated. I left Qt to use wxSQLite3 with Code::Blocks. The learning curve is a little steeper this way, but I favor this to using a hybrid C++. Thanks.
I see you used ExecuteUpdate for the INSERT statement and ExecuteQuery for the SELECT statement. What's the difference? Is there one that works for all?
You use
ExecuteUpdate for all SQL statements that do
not return a result set. You use
ExecuteQuery for SELECT statements (or other SQL statements that return a result set).
Slyde wrote:Also, did you mean:
Code: Select all
wxSQLite3Statement stmt2 = db->ExecuteQuery("SELECT recipe_id FROM recipe_names WHERE recipe = ?");
or
Code: Select all
wxSQLite3Statement stmt2 = db->PrepareStatement("SELECT recipe_id FROM recipe_names WHERE recipe = ?");
Sorry for the mix up. Of course, I meant the latter, that is, method
PrepareStatement.
Regards,
Ulrich
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 10:53 am
by Slyde
One more question, Ulrich.
In setting up the wxGrid, I want to get a count of the number of entries in a Table and then build the number of Grid rows shown accordingly. I'm used to working with PHP and MariaDB and the solution to this with wx* is elusive.
I have:
Code: Select all
wxSQLite3ResultSet res = db->ExecuteScalar("SELECT COUNT(*) FROM recipe_names");
to get a count of rows I'll need. But that's throwing an error:
Code: Select all
error: conversion from ‘int’ to non-scalar type ‘wxSQLite3ResultSet’ requested|
I thought by using ExecuteScalar that I'd "easily" get an INT return value. I'm sure I'm just using it the wrong way, but I can't find anything on how it's supposed to be used. And it'd be nice if I cld actually get it back in INT form so I cld plug it in to:
Code: Select all
Grid_View_Recipes->AppendRows( COUNT(*)_Int_Here );
I'll be out of your hair soon. I just need to know a few things. Thanks.
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 11:15 am
by utelle
Slyde wrote:In setting up the wxGrid, I want to get a count of the number of entries in a Table and then build the number of Grid rows shown accordingly. I'm used to working with PHP and MariaDB and the solution to this with wx* is elusive.
Partially, SQLite is to "blame". When issuing a SELECT query, the number of rows returned is not known in advance. That is, if you need to know the number of rows beforehand, you have to issue a "SELECT COUNT(*)" with the same selection criteria as your actual query statement.
Slyde wrote:I have:
Code: Select all
wxSQLite3ResultSet res = db->ExecuteScalar("SELECT COUNT(*) FROM recipe_names");
to get a count of rows I'll need. But that's throwing an error:
Code: Select all
error: conversion from ‘int’ to non-scalar type ‘wxSQLite3ResultSet’ requested|
This is not surprising, because method
ExecuteScalar returns an int value - which can't be converted to a result set object. Just do
Code: Select all
int numRows = db->ExecuteScalar("SELECT COUNT(*) FROM recipe_names");
to get the number of rows in table
recipe_names.
Slyde wrote:I thought by using ExecuteScalar that I'd "easily" get an INT return value.
I'm sure you will agree that is indeed easy, after you have inspected and tested the code above.
Slyde wrote:I'm sure I'm just using it the wrong way, but I can't find anything on how it's supposed to be used. And it'd be nice if I cld actually get it back in INT form so I cld plug it in to:
Code: Select all
Grid_View_Recipes->AppendRows( COUNT(*)_Int_Here );
I hope things are now a bit clearer.
The samples coming with wxSQLite3 show many of its features in action. So maybe you should spend some time on inspecting the code of the "minimal" sample. Yes, the minimal sample is NOT a GUI application, but this is just to not distract from the essentials. A slightly more elaborate GUI sample is the "treeview" sample.
Regards,
Ulrich
Re: Need help with adding variables to Queries
Posted: Fri Apr 27, 2018 11:33 am
by Slyde
Yes. You've cleared up a lot for me. And I'll indeed spend time looking at the "minimal" sample. I want to soak this in. I like it and am having fun with it. A lot of fun! Thanks again. I appreciate you sharing your knowledge with me.