well, these look like separate issues.
if you write:
queryExecute( ’
insert into meditation (
title,
quote
) values (
“Here’s a title”, // 1
“Here’s a quote without quotes” // 2
)
’
);
then the single quotes on the lines marked with 1 and 2 must be escaped,
or else Lucee thinks that the SQL query ends abruptly, and the parser
doesn’t know what to do with the trailing [s a title",] and you get an
error that the closing ‘)’ which was expected here is not found.
also, I’m not sure what RDBMS you are using, but most will not accepts the
double quotes to enclose char data in the lines marked with 1 and 2.
for the first example, an easier way to do it would be:
title = “Here’s a title”;
quote = “Here’s a quote without quotes”;
queryExecute( " // changed to double quotes so we don’t need to escape
single quotes below
insert into meditation (
title,
quote
) values (
‘#title#’,
‘#quote#’
)
"
);
the second example is different. there you clearly have that first issue
described above, where you have to escape the quotes, because the quote in
your string terminates the SQL code (as above).
it will be much easier to use variables as in the example above. if you
want to inline literal strings then you will have to escape the quotes,
like so:
queryExecute( " // SQL code starts
insert into meditation (
title,
quote
) values (
‘Here is a title’,
'Here is a quote ““with quotes”” ’ // quotes are escaped so they do not
terminate the SQL code
)
" // SQL code terminates
here
);
Igal Sapir
Lucee Core Developer
Lucee.org http://lucee.org/
On 3/3/2015 11:45 AM, Nando Breiter wrote:
More clearly:
queryExecute( ’
insert into meditation (
title,
quote
) values (
“Here’s a title”,
“Here’s a quote without quotes”
)
’
);
Invalid Syntax Closing [)] for function [queryExecute] not found
Stacktrace The Error Occurred in
/Users/nando/sites/test/qEx.cfm: line 9
7:
8: ) values (
9: “Here’s a title”,
10: “Here’s a quote without quotes”
11: )
Java Stacktrace Invalid Syntax Closing [)] for function [queryExecute]
not found
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.getFunctionMember(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.startElement(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.dynamic(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.checker(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.clip(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.negatePlusMinusOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.unaryOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.expoOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.divMultiOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.modOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.plusMinusOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.concatOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.decsionOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.notOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.andOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.orOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.xorOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.eqvOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.impOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.conditionalOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.assignOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.expression(Unknown
Source):-1
at
lucee.transformer.cfml.script.CFMLScriptTransformer.expression(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.expressionStatement(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statement(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statement(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statements(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statements(Unknown
Source):-1
at
lucee.transformer.cfml.script.CFMLScriptTransformer.transform(Unknown
Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.tag(Unknown Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.body(Unknown Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.body(Unknown Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.transform(Unknown
Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.transform(Unknown
Source):-1
at lucee.runtime.compiler.CFMLCompilerImpl.compile(Unknown Source):-1
at lucee.runtime.PageSourceImpl._compile(Unknown Source):-1
at lucee.runtime.PageSourceImpl.compile(Unknown Source):-1
at lucee.runtime.PageSourceImpl.loadPhysical(Unknown Source):-1
at lucee.runtime.PageSourceImpl.loadPage(Unknown Source):-1
at lucee.runtime.PageSourceImpl.loadPage(Unknown Source):-1
at lucee.runtime.PageContextImpl.doInclude(Unknown Source):-1
at lucee.runtime.PageContextImpl.doInclude(Unknown Source):-1
at lucee.runtime.listener.ModernAppListener._onRequest(Unknown
Source):-1
at lucee.runtime.listener.MixedAppListener.onRequest(Unknown Source):-1
at lucee.runtime.PageContextImpl.execute(Unknown Source):-1
at lucee.runtime.PageContextImpl.execute(Unknown Source):-1
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(Unknown Source):-1
at lucee.loader.servlet.CFMLServlet.service(Unknown Source):-1
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728):728
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305):305
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210):210
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222):222
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123):123
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171):171
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99):99
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:931):931
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118):118
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407):407
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004):1004
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589):589
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310):310
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895):895
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918):918
at java.lang.Thread.run(Thread.java:695):695
However, if I remove the apostrophes, it works:
queryExecute( ’
insert into meditation (
title,
quote
) values (
“Here is a title”,
“Here is a quote without quotes”
)
’
);
Note that I’m using single quotes to demark the sql string.
If I do this:
queryExecute( "
insert into meditation (
title,
quote
) values (
‘Here is a title’,
'Here is a quote “with quotes” ’
)
"
);
An almost identical error message occurs, different line indicated:
Lucee 4.5.0.042 Error (template) Message Invalid Syntax Closing [)]
for function [queryExecute] not found Stacktrace The Error Occurred in
/Users/nando/sites/test/qEx.cfm: line 10
8: ) values (
9: ‘Here is a title’,
10: 'Here is a quote “with quotes” ’
11: )
12: "
With backticks like so:
queryExecute( insert into meditation ( title, quote ) values ( 'Here is a title', 'Here is a quote "with quotes" ' )
);
Message Syntax Error, Invalid Construct Stacktrace The Error Occurred
in
/Users/nando/sites/test/qEx.cfm: line 3
1:
2:
3: queryExecute( `
4: insert into meditation (
5: title,
Using backticks in the query string:
queryExecute( "
insert into meditation (
title,
quote
) values (
Here's a title,
Here's a quote "with quotes"
)
"
);
Message Invalid Syntax Closing [)] for function [queryExecute] not
found Stacktrace The Error Occurred in
/Users/nando/sites/test/qEx.cfm: line 10
8: ) values (
9: Here's a title,
10: Here's a quote "with quotes"
11: )
12: "
Java Stacktrace Invalid Syntax Closing [)] for function [queryExecute]
not found
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.getFunctionMember(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.startElement(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.dynamic(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.checker(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.clip(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.negatePlusMinusOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.unaryOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.expoOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.divMultiOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.modOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.plusMinusOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.concatOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.decsionOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.notOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.andOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.orOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.xorOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.eqvOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.impOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.conditionalOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.assignOp(Unknown
Source):-1
at
lucee.transformer.cfml.expression.AbstrCFMLExprTransformer.expression(Unknown
Source):-1
at
lucee.transformer.cfml.script.CFMLScriptTransformer.expression(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.expressionStatement(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statement(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statement(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statements(Unknown
Source):-1
at
lucee.transformer.cfml.script.AbstrCFMLScriptTransformer.statements(Unknown
Source):-1
at
lucee.transformer.cfml.script.CFMLScriptTransformer.transform(Unknown
Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.tag(Unknown Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.body(Unknown Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.body(Unknown Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.transform(Unknown
Source):-1
at lucee.transformer.cfml.tag.CFMLTransformer.transform(Unknown
Source):-1
at lucee.runtime.compiler.CFMLCompilerImpl.compile(Unknown Source):-1
at lucee.runtime.PageSourceImpl._compile(Unknown Source):-1
at lucee.runtime.PageSourceImpl.compile(Unknown Source):-1
at lucee.runtime.PageSourceImpl.loadPhysical(Unknown Source):-1
at lucee.runtime.PageSourceImpl.loadPage(Unknown Source):-1
at lucee.runtime.PageSourceImpl.loadPage(Unknown Source):-1
at lucee.runtime.PageContextImpl.doInclude(Unknown Source):-1
at lucee.runtime.PageContextImpl.doInclude(Unknown Source):-1
at lucee.runtime.listener.ModernAppListener._onRequest(Unknown
Source):-1
at lucee.runtime.listener.MixedAppListener.onRequest(Unknown Source):-1
at lucee.runtime.PageContextImpl.execute(Unknown Source):-1
at lucee.runtime.PageContextImpl.execute(Unknown Source):-1
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(Unknown Source):-1
at lucee.loader.servlet.CFMLServlet.service(Unknown Source):-1
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728):728
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305):305
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210):210
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222):222
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123):123
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171):171
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99):99
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:931):931
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118):118
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407):407
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004):1004
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589):589
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310):310
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895):895
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918):918
at java.lang.Thread.run(Thread.java:695):695
I haven’t been able to get your echo syntax to work. I’ll try again with
a more simple test and report back.
Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland
+41 (0)91 600 9601
+41 (0)76 303 4477 cell
skype: ariamedia
On Tue, Mar 3, 2015 at 8:15 PM, Igal @ Lucee.org <@Igal> wrote:
looks like you forgot to remove the trailing comma. see in red below.
On 3/3/2015 11:05 AM, Nando Breiter wrote:
Igal,
If I try to inline the string with quotes
queryExecute( "
insert into myTable (
title,
quote
) values (
:title,
’ Here is a “quote” ’
)
" ,
{
title : { value:obj.getTitle(), cfsqltype:‘cf_sql_varchar’ } ,
}
);
I get a syntax error on queryExecute(). If my test could be improved,
let me know.
Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland
+41 (0)91 600 9601 <%2B41%20%280%2991%20600%209601>
+41 (0)76 303 4477 <%2B41%20%280%2976%20303%204477> cell
skype: ariamedia
On Tue, Mar 3, 2015 at 7:28 PM, Igal @ Lucee.org <@Igal> wrote:
Nando,
this could be a bug that should be investigated. can you test it by
in-lining the string with the quotes? i.e. not using params? if that
would work then the problem lies with the param parsing.
if that part works, then please test the following cfscript syntax
(which I recommend over using queryExecute()):
query params=[title : { value:obj.getTitle(), cfsqltype:‘cf_sql_varchar’
} , quote : { value:obj.getQuote(), cfsqltype:‘cf_sql_longvarchar’ } ] {
echo("
insert into myTable (title, quote)
values ( :title, :quote );
"); }
do you get the same issue? if not then the problem is with the
queryExecute() function, which is implemented in cfscript code that ends up
calling the cfquery tag as above (so you also save a few microseconds by
calling the tag directly from your code as noted above).
please let us know your findings (and open a ticket for the issue if you
conclude that there’s a bug there so that it can be followed up on).
Igal Sapir
Lucee Core Developer
Lucee.org http://lucee.org/
On 3/3/2015 10:02 AM, Nando Breiter wrote:
I’m seeing an undesirable behavior with queryExecute(). On insert or
update, a string that contains the character " is truncated at that point.
I’m losing data. Here’s a simple example of what I’m doing:
queryExecute( ’
insert into myTable (
title,
quote
) values (
:title,
:quote
)
’ ,
{
title : { value:obj.getTitle(), cfsqltype:‘cf_sql_varchar’ } ,
quote : { value:obj.getQuote(), cfsqltype:‘cf_sql_longvarchar’ }
}
);
How do I preserve the quotation marks?
By the way, I tried preserveSingleQuotes( obj.getQuote() ), but it
doesn’t help.
Thanks!
Nando
Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland
+41 (0)91 600 9601 <%2B41%20%280%2991%20600%209601>
+41 (0)76 303 4477 <%2B41%20%280%2976%20303%204477> cell
skype: ariamedia
You received this message because you are subscribed to the Google
Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/CAGHrs%3D85hgzHv9G4kb3cZJWpZZGnMPhft5naWdxDdc%2B6NzC4yg%40mail.gmail.com
https://groups.google.com/d/msgid/lucee/CAGHrs%3D85hgzHv9G4kb3cZJWpZZGnMPhft5naWdxDdc%2B6NzC4yg%40mail.gmail.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.
–
You received this message because you are subscribed to the Google
Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/54F5FD65.6090809%40lucee.org
https://groups.google.com/d/msgid/lucee/54F5FD65.6090809%40lucee.org?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.
–
You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/CAGHrs%3D_o%2BK%3Dah74PCw%3DNPOXiVBNOmqRx1uPOXrGaUxgS4ZN_ng%40mail.gmail.com
https://groups.google.com/d/msgid/lucee/CAGHrs%3D_o%2BK%3Dah74PCw%3DNPOXiVBNOmqRx1uPOXrGaUxgS4ZN_ng%40mail.gmail.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.
–
You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/54F6085A.3080604%40lucee.org
https://groups.google.com/d/msgid/lucee/54F6085A.3080604%40lucee.org?utm_medium=email&utm_source=footer.
For more options, visit https://groups.google.com/d/optout.
–
You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/CAGHrs%3D-H%3DGYsn%2BgG5c8iJONMXDosQtFh9PabUOA2CwYxuHfkeg%40mail.gmail.com
https://groups.google.com/d/msgid/lucee/CAGHrs%3D-H%3DGYsn%2BgG5c8iJONMXDosQtFh9PabUOA2CwYxuHfkeg%40mail.gmail.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.
–
You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/54F6141C.2010109%40lucee.org
https://groups.google.com/d/msgid/lucee/54F6141C.2010109%40lucee.org?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.