Using a C++ variable in MSSQL query

Question

First off, C++ is a new language to me although I have coded in a few others; so please forgive any ignorance of C++ on my part. I've worked out how to do database connections, and simple queries; however the thing that's throwing me completely is the use of variables in said queries.

I've got the following query that happily inserts data into my DB, but I can't for the life of me work out how to do the same query but inserting a variable instead of static text.

if (SQL_SUCCESS != SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"insert into testtable (name) values ('testname');", SQL_NTS)) {
    show_error(SQL_HANDLE_STMT, sqlstatementhandle);
    goto FINISHED;
}

I've done quite a lot of reading around but nobody seems to give a clear, or even the same, answer. The only constant theme is that, for obvious reasons, I should be using a prepared statement. However even this method varies wildly in application from person to person.

Using VS2015 and SQL Server 2014.

UPDATE

Thanks for all of the pointers, I've added the below code and it's now happily inserting variables!

char* newvar;
newvar = "preptest";
SQLCHAR newquery[100];
    sprintf((char*)newquery, "INSERT INTO testtable (name) VALUES ('%s');", newvar);
if (SQL_SUCCESS != SQLExecDirect(sqlstatementhandle, newquery, SQL_NTS)) {
    show_error(SQL_HANDLE_STMT, sqlstatementhandle);
}

If (lol, if; when) you see any other no-nos please feel free to point them out. A quick follow up question, if the variable needs to include an escape character, how do I go about ignoring it? Or do I just use RE to escape the escape char?

Not exactly a no-no like `goto' but a hard cast right in the middle of somewhere is ugly at best. I would at least wrap that in a minimal conversion function. Thus if you once get problems with the SQLCHAR to (whatever) char_type conversion, you can intercept there. — decltype_auto
– decltype_auto, Commented Nov 9, 2015 at 18:57

Glorfindel · Accepted Answer · 2021-07-26 05:49:04Z

1

but I can't for the life of me work out how to do the same query but inserting a variable instead of static text.

A plain std::sprintf into a char[MAX_STATEMENT_LENGTH] sql-query buffer could do the job (although that can be refined according to your needs, of course).

Btw - a goto

    goto FINISHED;

is generally a no-no in C and C++. The very few occasions where they are unavoidable, or that much more efficient than staying with block-based execution control, just confirm that rule.

Idiomatic C++ would be throw ing and handling an exception in a try/catch-block

edited Jul 26, 2021 at 5:49

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Nov 9, 2015 at 15:51

decltype_auto

1,72611 silver badges19 bronze badges

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

5 Comments

dailygrind Over a year ago

So define the query using sprintf and then execute the query later? Or am I way off?

decltype_auto Over a year ago

@dailygrind Well, 'later' may be in the next line of code thus let's better put it you do it in two steps.

dailygrind Over a year ago

Thanks for the insight, I think I'm getting closer. Should I run it with SQLExecDirect?

decltype_auto Over a year ago

if (and so it appears) you want to use SQLExecDirect instead of prepared statements then "yes". I would however dispute that SQLExecDirect is the icing on the MSSQL API cake.

dailygrind Over a year ago

Updated the question with the new code, thanks again! There is a quick follow-up though ^

Collectives™ on Stack Overflow

Using a C++ variable in MSSQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related