asked on

How do I build SQL "Insert into" basic_stringstream using variables in C++

I'm trying to build a function that will take string inputs and build an SQL statement for input into a database. Here's my existing code:

void Connector::InsertData(string _table, string _column, string _value)
{
// string I/O stream
basic_stringstream<wchar_t> strSQLStatement;
variant_t vntRecordAffected;

strSQLStatement <<L"INSERT INTO " << _table.c_str() << "(" << _column.c_str() << ") VALUES('" << _value.c_str()<< "' ) ";

cout << "strSQLStatement =" << strSQLStatement.str().c_str() <<endl;
_bstr_t strSQLstmt = strSQLStatement.str().c_str();

try
{
_connect->Execute(strSQLstmt, &vntRecordAffected, adCmdText );
}
catch( _com_error &e)
{
// get info from _com_error
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());

printf("%s", e.ErrorMessage());
cout << endl << "No Data Entered." << endl << endl;
} // end catch

cout << "Data Inserted." << endl;

} //End InsertData

Does anybody have an input?

rstaveley

Use wcout to see the wide string:

i.e.

wcout << "strSQLStatement =" << strSQLStatement.str().c_str() <<endl;

RedHedToo

ASKER

Sorry, I should've looked closer for editing purposes prior to putting it up. The problem is that the string always sends back an error. I've tried several ways to build a string to create the statement, but the only thing that works so far is creating an entire string at one time without actually using any data in variables. The line I have in there now:

strSQLStatement <<L"INSERT INTO " << _table.c_str() << "(" << _column.c_str() << ") VALUES('" << _value.c_str()<< "' ) ";

doesn't actually work properly.

Anybody have a solution?

Red.

rstaveley

If I hack your code thus, I get what looks like good SQL:
--------8<--------
#include <iostream>
#include <sstream>
using namespace std;

void /*Connector::*/InsertData(string _table, string _column, string _value)
{
// string I/O stream
basic_stringstream<wchar_t> strSQLStatement;
// variant_t vntRecordAffected;

strSQLStatement <<L"INSERT INTO " << _table.c_str() << "(" << _column.c_str() << ") VALUES('" << _value.c_str()<< "' ) ";

wcout << "strSQLStatement =" << strSQLStatement.str().c_str() <<endl;
}

int main()
{
      InsertData("mytable","mycolumn","myvalue");
}
--------8<--------

Output is:

strSQLStatement =INSERT INTO mytable(mycolumn) VALUES('myvalue' )

My SQL experience isn't strong enough to vouch whether you can get away with the lack of space between mytable and '('.

Is this not what you are getting?

Clearly this is only good for a table which has one field in it only. You have put quotes around myvalue, which means you couldn't conveniently pass a comma separated strings. You'd need to have something like

      InsertData("mytable"
            ,"my1stcolumn,my2ndcolumn"
            ,"my1stvalue','my2ndValue" /* <- Weird quotes! */
      );

RedHedToo

ASKER

I'm getting an error, actually. "IDispatch error #3092" When I googled it , the Microsoft site said I needed single quotes, which I already have. It also said it may be a problem with MDAC 2.5 (I'm running 2.7), so right now I don't know the exact problem.

Red.

rstaveley

I recommend you close this question (ask for a refund) and start a new question with an appropriate title (e.g. "MS MSL - IDispatch error #3092"). Some folks who are knowledgeable about MS SQL may not be looking at this, thinking this is an STL problem, which it isn't.

ASKER CERTIFIED SOLUTION

rstaveley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

How do I build SQL &quot;Insert into&quot; basic_stringstream using variables in C++

How do I build SQL "Insert into" basic_stringstream using variables in C++