i am running a query and its working good in sql but when i execute with CF, i am getting an error, i tried using preservesinglequotes but it is still failing, I am on CF 2016

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select concat(''<a href=index.html?action=newreg&id='',id,''>'') as id ,`name`,a' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:389) at com.mysql.jdbc.Util.getInstance(Util.java:372) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)

It's showing you the bit of the SQL that is wrong, and... it's wrong. You have doubled-up the quotes. I presume that

concat

expression is supposed to be along the lines of

concat('<a href=index.html?action=newreg&id=',id,'>')

? You did see that, right? It's impossible to guess what yer doing wrong in yer code cos. like, you ain't shown it to us. Yer - once again - relying on us to guess.

and I mentioned in my post my dump shows nothing wrong It's when it's going on query it's making a mess

That SQL is not fine. I mean... it's obviously not fine cos the DB is saying "it's not fine", and the SQL in the error message you posted isn't fine. So I dunno what the SQL is that you're running yourself and has nothing wrong with it, but it ain't the SQL that CF is passing to the DB. There's simply no point in saying "it's fine", when it clearly isn't. And... like... you even know it's not fine because you've taken the time to post here. What does "i am running a query and its working good in sql" mean? Do you mean yer running it in MySQL Work Bench, or some other DB client? I am guessing so. Also: you still ain't shown us your code.

@Adam Cameron is correct. Without you posting your code it is difficult to see what is going wrong. Also : he's right - that your SQL is obviously faulty , too (for some yet unknown reason), because the database is throwing an error with what you're giving it. It might not make sense to you - and that's fine... but then your question should be something along the lines of; When I run the following SQL directly for mySQL it works; The following code - throws an error - which I don't understand why. With the above, example you would have given us; • the working SQL • The not working CFML And give us - the best opportunity of helping you. Anyway... I would normally compare the code, to the SQL that is actually sent to the DB logging the SQL, then copy/paste it into HeidiSQL / Workbench and tweak it there, until it's right.

var theSqlToRun = "SELECT .... ";

writeLog(type="Information", file="myFaultySQL", text="the SQL I am trying to run is : #theSqlToRun#");

myQuery = new Query(datasource="theDS", name = "myQuery", sql=theSqlToRun);
// if needed 
// myQuery.addParam(...);
qryResult = myQuery.execute().getResult();

the problem seems to be inside my code, i found the issue

(I am guilty of NOT doing this myself - no one is perfect!) You know you're not necessarily going to get an answer from a forum, instantaneously. So the extra 2-3 minutes isn;t going to "really" cost you anything in solving your issue. I have found the "trick" is to read what I have written, from the start, before sending it. Did I miss words / punctuation - all the "normal" editing stuff - sounds childish - but I STILL make these kinds of errors all the time, when I am typing what I am thinking - where my typing is slower than my brain! I also TRY - to read the post with the "The reader has no context - knows nothing about my app / my platform / my specific issue... Is there enough information in my post - to help those who I want to help me - to be able to do so? You've been here (in the slack channels etc) a while now - to know that isn't a "you" thing, either. Lots of people are told the same thing... Explain the use-case. What are you trying to achieve? Show us the "simplest" code you can that shows has the problem you're having. Let us know the things you have tried AND where they failed. (some times the answer is - "Actually the second thing you tried would have worked if you included THIS argument, or if you did "A" instead of "B". - the point being that what you tried "IS" important for us to know) Lastly - keep on asking questions - (IMHO - and what I look for in anyone I am hiring is;) - knowing that you don't know (and want to learn ) is one of the greatest assets you can have!

To add if you do solve it yourself after posting a problem share your solution. You might help someone else in the future.

Yes it certainly would be a courtesy if you reported back with what your issue/solution was.