[SOLVED] Need help with adding variables to Queries

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

[SOLVED] Need help with adding variables to Queries

Post 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 )
Last edited by Slyde on Sat Jul 07, 2018 10:51 am, edited 3 times in total.
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Re: Need help with adding variables to Queries

Post 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.
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Re: Need help with adding variables to Queries

Post by Slyde »

The extra closed parenthesis at the end of the second query will be shot at sunrise!

Thanks for the help :D
Linux Mint 21.3 | wxWidgets-3.2.4
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Need help with adding variables to Queries

Post 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
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Re: Need help with adding variables to Queries

Post 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?
Linux Mint 21.3 | wxWidgets-3.2.4
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Need help with adding variables to Queries

Post 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
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Re: Need help with adding variables to Queries

Post 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.
Linux Mint 21.3 | wxWidgets-3.2.4
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Need help with adding variables to Queries

Post 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
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Re: Need help with adding variables to Queries

Post 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.
Linux Mint 21.3 | wxWidgets-3.2.4
Post Reply