MySQL problem with inserting quote in my QSqlQuery prepare statement

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( '":phrase"' IN BOOLEAN MODE)" );

If you used the forum's Code (</> button) wehere you are asking about quote characters we could see you are using

"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( '":phrase"' IN BOOLEAN MODE)" );

So instead of '":phrase"' have you tried ':phrase', ":phrase" or :phrase?

[swankster]

@JonB
syntax works perfectly while in workbench. as stated initial post.

the output string is also stated from executedQuery where i am receiving a '"\phrase"' instead of '"phrase"'

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

syntax works perfectly while in workbench. as stated initial post.

But from workbench you are not using bound variables, are you? Or are you?
And your first post does not use bound variables, does it?
And you still have not said or shown what error you get where in SQL code?
Trying to help, but not seeming to get anywhere.

the output string is also stated from executedQuery where i am receiving a '"\phrase"' instead of '"phrase"'

Copy and paste error messages please. The characters here are vital and I do not think as you have written. And please post inside Code tags, you are asking question about punctuation characters which we cannot even read right!

AGAINST( '":phrase"' IN BOOLEAN MODE)"

This does not look right.

the output string is also stated from executedQuery where i am receiving a '"\phrase"' instead of '"phrase"'

Already said: qDebug() or debugger may show literal characters like ' or " inside strings with backslashes.

[Ronel_qtmaster]

@swankster Why do you want to display it without appearing first? Qt has its syntax

[swankster]

@JonB
yes, i have tried ':phrase', ":phrase" and :phrase
':phrase' returns ':phrase'
:phrase returns ?
it acts as though binding does not work with AGAINST()

in workbench, correct I am not using bound value. that is what im attempting to send to MySQL. it is somewhat working in that it will match any of the words within the phrase to the field. But I need this to check for exact phrase. if it exists to prompt the user that the particular phase is already set in the DB. if it does not exist it automatically tries to add. but the field is unique so it does error when attempting to create it as new if it already exists.

The WHERE MATCH AGAINST() does works in workbench with the exact phrase.

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

it acts as though binding does not work with AGAINST()

Which is what I suggested might well be the case right from the start.......

@JonB said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

@Ronel_qtmaster
Do you have any evidence that bindValue() will be accepted by the MySQL driver for this MATCH ... AGAINST clause? Unless you have tried it you would not know (and I do not, but it would not surprise me if not). For example, LIKE and IN do not accept binds (as far as I recall).

I did not think he should suggest that to you unless he has tested it actually worked. See the earlier discussion. So unless you know the MySQL driver accepts binding for this clause do not use binding!

Binding is accepted, or not accepted, by the driver on a case by case basis. It is not just a "macro substitution".

[Ronel_qtmaster]

@swankster I would suggest you to try a simple binding syntax with Mysql and see which result you receive.So a statement different that what you want to achieve.If you have a positive result, then binding is working and your syntax is wrong.If not..

[JonB]

@Ronel_qtmaster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

and your syntax is wrong.If not..

Sorry, but this is not necessarily correct, and you are repeating (what I believe to be) the same mistake as before, in advising the OP to use binding when you have no evidence that this SQL clause, even if correct, accepts parameter binding. You seem to think that anything which is syntactically correct can have bindings put into it, and that is not the case. Please read my earlier posts.

@swankster
Good luck if you can get binding to work for your MATCH ... AGAINST, I suspect you will not. Assuming that is the case, you just need to generate the correct literal sting which you have tested in workshop into C++ code. Qt's QString::arg() is a convenient way to generate QStrings which require C++ variables' values (your phrase_var) inserted into them.

[swankster]

@JonB
binding does not work with Against. looks like I will need to do a different approach. Thanks

[JonB]

@swankster
:) Like I said, just build the (correct, including whatever quoting, and if you are OK on possible SQL injection attacks!) string and pass that without using binding, for this query.