QSqlQuery: Invalid column name (with colon) under SQL Server

[Gad82]

Hi everyone,

I'm trying to execute an insert query via QSqlQuery in my SQL Server Database. The query looks like this:

INSERT INTO dbo.mytab ([col1:data1], [col2:data2], [col3:data3], [col4:data4]) VALUES(?, ?, ?, ?)

The query is correctly prepared using addBindValue function, but when it's executed I get the following error:

"INSERT INTO dbo.mytab ([col1?], [col2?], [col3?], [col4?]) VALUES(?, ?, ?, ?)"
QSqlError(8180, "QODBC3: Unable to execute statement", "[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'col1?'.

I guess this is somehow related to the role of placeholders in the query, but I cannot figure out how. Is there a way to include colon in the column names without generating this error?
Thanks in advance!

[jefftee]

I don't know SQL Server, but here goes... Is the col1:data syntax something supported by SQL Server for normal column names or is that the syntax used for named parameters?

The ":variable" syntax is typically used as a placeholder that can be replaced at run-time with QSqlQuery::bindValue, so I suspect that's where your error is coming from. Are the column names known that you're trying to insert data into or are they truly variable and you are intending to bind values for the column names?

Here's what I'd try:

INSERT INTO dbo.mytab (:col1, :col2, :col3, :col4) VALUES (:data1, :data2, :data3, :data4)

INSERT INTO dbo.mytab (:col1, :col2, :col3, :col4) VALUES (:data1, :data2, :data3, :data4)

To copy to clipboard, switch view to plain text mode 

Then prepare that statement and bind values to all of the named parameters. Never had the need to have variable column names myself, but if that's what you are trying to accomplish, might be worth a try.

Good luck.

Edit: I tried to use a named parameter for an SQLITE UPDATE statement and it doesn't work. While I don't see the restriction noted in the QSqlQuery documentation, all of the examples show named and positional parameters as values, not column names, etc.

[Gad82]

Thank you for the answer jefftee. I had a try, but unfortunately it turned out that fields name cannot be bounded.

Any other ideas?

Thanks again.

[jefftee]

You will have to resort to building your SQL statement at run time by concatenating the field names. Once built, you can still prepare the SQL statement and use named or positional values for the actual data. For example:

QString col1name = "col1";
QString col2name = "col2";
QString col1data = "foo";
QString col2data = "bar";
QString sql = "insert into foo(" + col1name + "," + col2name + ") values(:col1data,:col2data)";
QSqlQuery q(db);
q.prepare(sql);
q.bindValue(":col1data",col1data);
q.bindValue(":col2data",col2data);
q.exec();

QString col1name = "col1";
QString col2name = "col2";
QString col1data = "foo";
QString col2data = "bar";
QString sql = "insert into foo(" + col1name + "," + col2name + ") values(:col1data,:col2data)";
QSqlQuery q(db);
q.prepare(sql);
q.bindValue(":col1data",col1data);
q.bindValue(":col2data",col2data);
q.exec();

To copy to clipboard, switch view to plain text mode 

If the column names are being provided by the user, you should validate the column names using a regex or other test to ensure they aren't succeptible to SQL injections. Using bindValue for the data protects against that, but blindly accepting user input and concatenating in SQL queries is bad form, as it can and will be abused.

Hope that helps.