QueryExecute

230 views
Skip to first unread message

Michael Offner

unread,
Oct 15, 2013, 5:20:01 AM10/15/13
to ra...@googlegroups.com
Get the new QueryExecute function from ACF Splendor in Railo 4.1

<cfscript>
function QueryExecute(required string sql, any params, any datasource){
   var args={};
   if(!isNull(params))args.params=arguments.params;
   if(!isNull(datasource))args.datasource=arguments.datasource;
   query name="local.rtn" attributeCollection="#args#" {
      echo(sql);
   }
   return rtn;
}
/* Test code
dump(queryExecute(sql:"SHOW tables where Tables_in_test = ?",params:['cf_session_data'],datasource:"mysql"));

dump(queryExecute(sql:"SHOW tables where Tables_in_test = :susi",params:{susi:'cf_session_data'},datasource:"mysql"));
*/
</cfscript>

Simply copy the function to <server-context>/library/function and you have it as build in function

Have fun 
Micha

Raffaele Castagno

unread,
Oct 15, 2013, 5:40:12 AM10/15/13
to ra...@googlegroups.com
That was a quick one, man!

Raffaele

Jeroen Knoef

unread,
Oct 16, 2013, 4:29:29 AM10/16/13
to ra...@googlegroups.com
Hey, I didn't know you could pass parameters like that in cfquery! Nice..
Is this a Railo specific addition? I mean the params attribute? Couldn't find this in the docs.

Michael Offner

unread,
Oct 16, 2013, 6:10:11 AM10/16/13
to ra...@googlegroups.com
Yes, this is railo specific and new to Railo 4.1, atm this is still marked as "hidden feature", because of that the attribute is not listed in the tag reference.

Micha


2013/10/16 Jeroen Knoef <jer...@neoneo.nl>
Hey, I didn't know you could pass parameters like that in cfquery! Nice..
Is this a Railo specific addition? I mean the params attribute? Couldn't find this in the docs.

--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/railo/9e8126e7-0777-411a-bfd7-f13c696ef780%40googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.



--
/micha

Michael Offner CTO Railo Technologies GmbH

cfDEV

unread,
May 2, 2014, 2:43:24 PM5/2/14
to ra...@googlegroups.com
Hello Micha. Thanks for adding this.

Using your examples, how would I use "maxrows" or "cachedwithin" with queryExecute?

Thanks, Che

cfDEV

unread,
May 2, 2014, 3:08:47 PM5/2/14
to ra...@googlegroups.com
Hello again Micha. I tried a simple update using queryExecute an error:

<cfscript>
qu = queryExecute("
    update make
    set
    make = ?
    where makeid = ?
    "
    ,["Audi",109]
    ,{datasource="mydsn"}
);
</cfscript>

I am using Railo 4.2.0.007. It should be noted that the value was indeed updated in the database.

Thanks, Che

Message variable [____RTN] doesn't exist
Stacktrace The Error Occurred inx
/queryExecute.cfm: line 36
called from /_query.cfm: line 18
Java Stacktrace variable [____RTN] doesn't exist
  at railo.runtime.type.scope.UndefinedImpl.get(UndefinedImpl.java:208):208
  at queryexecute_cfm$cf.udfCall(/queryExecute.cfm:36):36
  at railo.runtime.type.UDFImpl.implementation(UDFImpl.java:92):92
  at railo.runtime.type.UDFImpl._call(UDFImpl.java:306):306
  at railo.runtime.type.UDFImpl.call(UDFImpl.java:207):207
  at railo.runtime.functions.system.CFFunction.call(CFFunction.java:81):81
  at _query_cfm$cf.call(/_query.cfm:18):18
  at railo.runtime.PageContextImpl.doInclude(PageContextImpl.java:912):912
  at railo.runtime.PageContextImpl.doInclude(PageContextImpl.java:864):864
  at railo.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:206):206
  at railo.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:18):18
  at railo.runtime.PageContextImpl.execute(PageContextImpl.java:2217):2217
  at railo.runtime.PageContextImpl.execute(PageContextImpl.java:2184):2184
  at railo.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:332):332
  at railo.loader.servlet.CFMLServlet.service(CFMLServlet.java:29):29
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:848):848
  at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669):669
  at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:457):457
  at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137):137
  at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:557):557
  at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231):231
  at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1075):1075
  at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:384):384
  at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193):193
  at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1009):1009
  at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135):135
  at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:224):224
  at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154):154
  at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116):116
  at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:317):317
  at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116):116
  at org.eclipse.jetty.server.Server.handle(Server.java:368):368
  at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:488):488
  at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:932):932
  at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:994):994
  at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:640):640
  at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235):235
  at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82):82
  at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628):628
  at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52):52
  at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608):608
  at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543):543
  at java.lang.Thread.run(Unknown Source):-1

Message has been deleted

Jonas Hauß

unread,
May 2, 2014, 6:07:58 PM5/2/14
to ra...@googlegroups.com
As far as I know, there is no value to return after an SQL Update. QueryExecutes only returns the query but not the resultset.

Just have a look at the BIF:

query name="local.____rtn" attributeCollection="#local.____attributeCollection#" {
    echo
(local.____sqlString);
}

return ____rtn;


cfDEV

unread,
May 2, 2014, 11:11:22 PM5/2/14
to ra...@googlegroups.com
Yes, that makes sense. Thanks.
For some reason I thought that queryExecute might finally be the answer and the best way to write any kind of query in cfscript.
BTW, can anyone chip in w/ how I would use "maxrows" or "cachedwithin"?

Che

Igal @ getRailo.org

unread,
May 3, 2014, 12:04:37 AM5/3/14
to ra...@googlegroups.com
QueryExecute() is currently implemented in cfscript.  you can see the source code for it at
https://github.com/getrailo/railo/blob/4.2/railo-java/railo-core/src/resource/library/function/queryExecute.cfm

as you can see, arguments.options is a Struct which is used as AttributeCollection, so you can pass your attributes as a struct in the last parameter, e.g.

    QueryExecute(sql, params, { maxrows: 10, cachedWithin: 1 });
--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/railo/0c499156-73db-4eb2-9094-be54673eb743%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
Igal Sapir
Railo Core Developer
http://getRailo.org/

AJ Mercer

unread,
May 3, 2014, 1:45:30 AM5/3/14
to ra...@googlegroups.com
I submitted a fix for this yesterday


--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.

For more options, visit https://groups.google.com/d/optout.

cfDEV

unread,
May 3, 2014, 8:00:05 AM5/3/14
to ra...@googlegroups.com
Thanks Igal!

cfDEV

unread,
May 5, 2014, 12:25:28 PM5/5/14
to ra...@googlegroups.com
Can anyone verify this error? Cachedwithin appears to now work (thanks!), but I cannot get maxrows to work.

<cfscript>
q = queryExecute("
    select ma.make, mo.model, mo.age
    from make ma
    left join model mo on ma.makeid = mo.makeid
    where    ma.makeid = :makeid
            and mo.model like :modelid
    "
    ,{makeid=101, modelid="C%"}
    ,{datasource="mydsn", maxrows=5}
);
</cfscript>

The error is:

Railo 4.2.0.008 Error (database)
Message    
Detail     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 'OPTION SQL_SELECT_LIMIT=5' at line 1
SQL    
select ma.make, mo.model, mo.age
from make ma
left join model mo on ma.makeid = mo.makeid
where ma.makeid = '101'
and mo.model like 'C%'
DatabaseName     MySQL
DatabaseVersion     5.6.17
DriverName     MySQL-AB JDBC Driver
DriverVersion     mysql-connector-java-5.0.8 ( Revision: ${svn.Revision} )
Datasource     ownster

Stacktrace     The Error Occurred inx
/queryExecute.cfm: line 33
called from /_query.cfm: line 71

AJ Mercer

unread,
May 5, 2014, 6:10:49 PM5/5/14
to ra...@googlegroups.com

Does that SQL run within mySQL directly?
That is, in something like mySQL workbench?

--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.

cfDEV

unread,
May 5, 2014, 6:42:22 PM5/5/14
to ra...@googlegroups.com
Yes. It is translation of an existing <cfquery> I was testing in cfscript.
All I'm trying to do is to see how easy I can write simple queries w/ queryExecute.
At first, I ran into a bug when "updating" and now I think there is a bug w/ "maxrows".
I've never submitted a bug report before, so I thought I'd double check here.

Thanks, CV

AJ Mercer

unread,
May 5, 2014, 7:05:20 PM5/5/14
to ra...@googlegroups.com
so if you take out maxrows it works fine?



For more options, visit https://groups.google.com/d/optout.

cfDEV

unread,
May 6, 2014, 7:27:29 AM5/6/14
to ra...@googlegroups.com
Yes.
I don't usually use maxrows (I use LIMIT) but I testing it appears that maxrows does not.

cfDEV

unread,
May 7, 2014, 10:47:32 AM5/7/14
to ra...@googlegroups.com
As it turns out, this may be a Railo/MySQL issue...
https://groups.google.com/forum/#!topic/railo/RUcO48KG6zU

AJ Mercer

unread,
May 7, 2014, 9:59:18 PM5/7/14
to ra...@googlegroups.com
for Igal/Railo Team, 
was the MySQL client updated for Railo 4.2?



For more options, visit https://groups.google.com/d/optout.

Igal @ getRailo.org

unread,
May 7, 2014, 11:52:24 PM5/7/14
to ra...@googlegroups.com
you mean the mysql driver?  you can see the history of the file at
https://github.com/getrailo/railo/commits/4.2/railo-java/libs/mysql-connector-java-bin.jar

For more options, visit https://groups.google.com/d/optout.

-- 
Igal Sapir
Railo Core Developer
http://getRailo.org/

AJ Mercer

unread,
May 8, 2014, 12:01:53 AM5/8/14
to ra...@googlegroups.com
so - mysql driver  5.1.20

5.1.25 has issues


from linked thread
the working version is 5.1.23





For more options, visit https://groups.google.com/d/optout.

cfDEV

unread,
May 8, 2014, 11:07:42 AM5/8/14
to ra...@googlegroups.com
FWIW, The version I downloaded is 5.1.30 (mysql-connector-java-5.1.30-bin.jar).
It now works with MySQL 5.6 on my local Windows 7 PC.

Thanks, Che
Reply all
Reply to author
Forward
0 new messages