Passing C++ variable in MySQL Statement using MySQL Driver in MySQL 8.0+

Question

I have looked through Stack Overflow's plethora of answers and questions but they are all for older versions of MySQL. I have also scoured the bowls of the internet for an answer to this and tried numerous different methods to no avail. So the question, how do I use a C++ variable in a MySQL query.

For instance:
pstmt = con->prepareStatement("SELECT balance FROM accounts WHERE id = [C++ Var]");

Where [C++ Var] would be the C++ variable. I am not sure if it is a good idea to use older methods from MySQL 5 in MySQL 8 or not. I think the most effective way would be to use the SET @var_name = data method, but I cannot find any way to implement that in C++. Currently I am using MySQL/C++ Connector 8.0 with the driver. Thank You!

nimrodm · Accepted Answer · 2019-09-14 18:46:08Z

1

I don't see why you need to use a MySQL @xxx variable here.

Using the old MySQL C++ API (which seems to be modeled after the Java JDBC API) prepareStatment call should look like:

pstmt = con->prepareStatement("SELECT balance FROM accounts WHERE id = ?");

And later on you should use something like

pstmt->setInt(1, userId); //where userId is a C++ int
ResultSet res* = pstmt->executeQuery()

Using the newer C++ API (X Dev API) the calls would look similar to the following:

Table accounts = db.getTable("accounts");
auto query = accounts.select("balance").where("id = :account_id");

RowResult res = query.bind("account_id", account_id).execute(); // where account_id is the name of an int variable.

answered Sep 14, 2019 at 18:46

nimrodm

24k7 gold badges62 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mcdz89 Over a year ago

That's a lot more info than I could find anywhere else. I will try to get that to work when I get back home tonight.

mcdz89 Over a year ago

Thank you @nimrodm for this answer it works! I wish there was more documentation for c++ or even other languages that may not have such.

nimrodm Over a year ago

Glad it helped, @mcdz89. What API did you end up using? The old JDBC or the new X-Dev?

mcdz89 Over a year ago

I used the old JDBC API as it is what I am currently working with. Thanks again!

James · Accepted Answer · 2023-04-20 16:33:46Z

These chaining examples all appear to be based on a query involving a single table. But real queries frequently involve relations between several tables. And this works just as well. Note that if there are several variables you bind them in the order they appear.

// Perform any operations on the table or query data as needed
string sql = "Select distinct foodname, price, storename from prices \
        join foods on(foods.foodkey = prices.foodkey) \
        join stores on(stores.storekey = prices.storekey) \
        where price > ? and storename like ?";
           
    //bind two parameters to the query
    SqlStatement query = session.sql(sql);
    float pval = 2.0;
    query.bind(pval);
    query.bind("V%");
    
    SqlResult res = query.execute();

And you can rebind new parameters and run the query again without it being recompiled.

Collectives™ on Stack Overflow

Passing C++ variable in MySQL Statement using MySQL Driver in MySQL 8.0+

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related