SQL error when using parameters
-
@KroMignon , the issue I have is that originally I did exactly that, however it will not compile like that because current_date() and current_time() only have meaning when executed by the SQL server, they mean nothing in JSON and cannot be evaluated, so they become undefined. The second parameter of addBindValue defaults to QSql::In.
@SPlatten
If indeed it does not work withQSql::In | QSql::Binarythen you (seem to) have no other choices for the parameter which will work from Qt client. That would mean it "cannot be done via a bound parameter", and you would need to handle such cases via direct interpolation into the query string instead of via a bound parameter. I did a look around and was unable to find any examples/questions where a bound value from Qt is set to an expression to be evaluated server-side, so we do not know whether it is supported. Unless @KroMignon has reason to believe it should be for the proposal he offers? -
@SPlatten
If indeed it does not work withQSql::In | QSql::Binarythen you (seem to) have no other choices for the parameter which will work from Qt client. That would mean it "cannot be done via a bound parameter", and you would need to handle such cases via direct interpolation into the query string instead of via a bound parameter. I did a look around and was unable to find any examples/questions where a bound value from Qt is set to an expression to be evaluated server-side, so we do not know whether it is supported. Unless @KroMignon has reason to believe it should be for the proposal he offers? -
@JonB , Just tried using QSql::In | QSql::Binary combination as the second parameter and removed the quotes around the arguments, it still doesn't like it.
It works as in my original post I drop in the arguments inline without quotes.
@SPlatten
It won't work "without the quotes", assuming you mean something likequery.addBindValue(current_date(), QSql::In | QSql::Binary)as that won't compile. @KroMignon must intend you to try:
query.addBindValue("current_date()", QSql::In | QSql::Binary)and hope that will cause the driver to pass across plain
current_date()for evaluation at server-side. Which as I've said, unless @KroMignon knows better, I would doubt does that: more likely, it does something about passing the bytes of"current_date()"in some binary representation.It works as in my original post I drop in the arguments inline without quotes.
Indeed. That's why I said, unless you can find a way to make it work with a bound parameter, you will have to recognise a case like this (e.g. parameter is neither a quoted string nor a number) and replace the
?directly in-line instead of trying to use any placeholder parameter. -
@SPlatten
It won't work "without the quotes", assuming you mean something likequery.addBindValue(current_date(), QSql::In | QSql::Binary)as that won't compile. @KroMignon must intend you to try:
query.addBindValue("current_date()", QSql::In | QSql::Binary)and hope that will cause the driver to pass across plain
current_date()for evaluation at server-side. Which as I've said, unless @KroMignon knows better, I would doubt does that: more likely, it does something about passing the bytes of"current_date()"in some binary representation.It works as in my original post I drop in the arguments inline without quotes.
Indeed. That's why I said, unless you can find a way to make it work with a bound parameter, you will have to recognise a case like this (e.g. parameter is neither a quoted string nor a number) and replace the
?directly in-line instead of trying to use any placeholder parameter.@JonB , No, thats not what I meant. If I write this:
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());It works fine, if I try:
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);And try:
query.addBindValue("current_date()", QSql::In | QSql::Binary); query.addBindValue("current_time()", QSql::In | QSql::Binary);It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.
NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.
Kind Regards,
Sy -
@JonB , No, thats not what I meant. If I write this:
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());It works fine, if I try:
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);And try:
query.addBindValue("current_date()", QSql::In | QSql::Binary); query.addBindValue("current_time()", QSql::In | QSql::Binary);It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.
NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.
@SPlatten
You say "No, thats not what I meant.", but I do understand that is what you meant!I am suggesting: if indeed @KroMignon's
query.addBindValue("current_date()", QSql::In | QSql::Binary);does not work --- and I was questionable that it would, as I don't think that is what
QSql::Binaryis for/does, but he may know better that I --- that means there is nothing left for the variable type/QSql::ParamTypefor you to try from Qt which would work for what you need to do here.If that is true you must go for the literal, in-line-interpolated
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());in the cases where a parameter is something like neither a literal string not a number, it cannot be done via
bindValue().As I said, I have had a look around. Although it's not easy to Google for, there simply does not seem to be any question about how to bind a value which is a SQL expression instead of a literal. So I can find neither a solution nor even someone who wants to do as you want to do (although it's quite reasonable) via a bound value.
This has nothing to do with your JSON. Of course anything you put there must be legitimate JSON, there is no point in "If I put the SQL functions into the JSON without quotes then the JSON fails".
NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.
Yep, that's OK. Or, as I said, you could recognise that a parameter which is neither a quoted string nor a number (or similar) is to be interpolated directly into the query string. Though beware of injection attacks!
-
@JonB , No, thats not what I meant. If I write this:
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());It works fine, if I try:
INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);And try:
query.addBindValue("current_date()", QSql::In | QSql::Binary); query.addBindValue("current_time()", QSql::In | QSql::Binary);It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.
NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.
@SPlatten
On a related but different note, how have you decided that the user should specify a literal string parameter in your JSON file?Say the query is
SELECT * from names_table WHERE name = ?and the user wants to look for
Simon. In your JSON, do you require the user to write"args":["Simon"]or
"args":["'Simon'"]? I hope it is the latter, but do you actually require the former?
-
@SPlatten
On a related but different note, how have you decided that the user should specify a literal string parameter in your JSON file?Say the query is
SELECT * from names_table WHERE name = ?and the user wants to look for
Simon. In your JSON, do you require the user to write"args":["Simon"]or
"args":["'Simon'"]? I hope it is the latter, but do you actually require the former?
-
@JonB , I've used ? and argument binding before and it works very nicely, the single quotes are not required, passing the argument as:
"args":["Simon"]Will work just fine.
@SPlatten
Then I think you may run into problems constructing queries because you cannot distinguish, say, what is to be a string parameter from a numeric parameter. You have (presumably) made it impossible to pass an argument as a numeric, everything will go as a string. That is not what you would do in code when constructing a query which has a numeric parameter, e.g you would writequery.addBindValue(10);rather than
query.addBindValue("10");I leave you to contemplate the consequences.
Actually, this depends again on what you require from the user. Would they specify in your JSON a number for an argument via
"args":["10"]or
"args":[10]?
-
@SPlatten
Then I think you may run into problems constructing queries because you cannot distinguish, say, what is to be a string parameter from a numeric parameter. You have (presumably) made it impossible to pass an argument as a numeric, everything will go as a string. That is not what you would do in code when constructing a query which has a numeric parameter, e.g you would writequery.addBindValue(10);rather than
query.addBindValue("10");I leave you to contemplate the consequences.
Actually, this depends again on what you require from the user. Would they specify in your JSON a number for an argument via
"args":["10"]or
"args":[10]?
@JonB , actually I've played around with this in the past the routines are quite smart in the way they handle parameters, which is why its better and safer to use ? and use parameters rather than embedding the parameter values inline. The code handles the interpretation of the type and adds quotes to the query automatically, of course you won't see this but putting quotes on the data is not required.
Kind Regards,
Sy -
@JonB , actually I've played around with this in the past the routines are quite smart in the way they handle parameters, which is why its better and safer to use ? and use parameters rather than embedding the parameter values inline. The code handles the interpretation of the type and adds quotes to the query automatically, of course you won't see this but putting quotes on the data is not required.
@SPlatten
Yes, I realize that about parameters. They will respect the type of the C++ variable/JSON variant type you have read the parameters in as. That's why JSON"args":["10"]will produce a different C++/SQL type from"args":[10].Anyway, sounds like you have this under control.
