What's the cleanest way to set queryOptions in a cfscript query in lucee?

[thorste...@googlemail.com]

What's the cleanest way to add queryOptions like cachedWithin to queryService in lucee?

This works, but I guess there is a nicer way.

        var queryService = new query(cachedWithin=createTimespan(0, 0, 10, 0));
        queryService.setName("qryItems");    
        queryService.setDatasource("#application.wheels.dataSourceName#");       
        queryService.addParam(name="IsTestData",value="True",cfsqltype="CF_SQL_BIT");
        queryService.setSQL("
            SELECT        bla bla
       ");

[Jonathan Brookins]

I've not used setName() but this is the way we use it:

queryService = new Query(datasource=application.wheels.dataSourceName, cachedWithin=createTimespan(0, 0, 10, 0),

  sql = "select .... ").addParam(...);

[Nando Breiter]

Short and sweet, for me it would be using queryExecute( sql, params, options )

Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland

+41 (0)91 600 9601
+41 (0)76 303 4477 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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/ca74a061-1ed5-4430-9575-96dcbfd3c49f%40googlegroups.com.

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

[Adam Cameron]

I can't vouch for Lucee, but I guess their Query.cfc works the same as ColdFusion's one. That being the case, one doesn't need to call all the individual setter methods to set properties, one can just pass them all into the constructor call.

Details here: http://blog.adamcameron.me/2014/01/using-querycfc-doesnt-have-to-be-drama.html, but the sample code is thus:

GetParks = new Query(
    datasource="cfdocexamples",
    cachedwithin=createTimeSpan(0, 6, 0, 0),
    sql    = "
        SELECT        PARKNAME, REGION, STATE
        FROM        Parks
        WHERE        STATE    = ?
        and            REGION    = ?
        ORDER BY    ParkName
    ",
    parameters    = [
        {value="MD", cfsqltype="cf_sql_varchar"},
        {value="National Capital Region", cfsqltype="cf_sql_varchar"}
    ]
).execute().getResult();

-- 

Adam

 

[Jean Moniatte]

Hello,

I am still using the cfquery syntax (no script) inside DAOs because I find it much easier to manipualte the SQL code depending on the arguments passed to the DAO. For example:

<cfquery name="getUsers" datasource="main">

    select userId, userName, userEmail

    from users

    where userStatus = "active"

    <cfif len(arguments.userName)>

        and userName = <cfqueryparam value="#arguments.userName#" cfsqltype="string">

    </cfif>

    <cfif len(arguments.userEmail)>

        and userEmail = <cfqueryparam value="#arguments.userEmail#" cfsqltype="string">

    </cfif>

</cfquery>

How would you write a similar query in cfscript?

Thanks,

Jean

 

--

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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/d8c82fb4-6626-4cd1-b87d-9c68f696dd5f%40googlegroups.com.

[Adam Cameron]

On Thursday, 9 July 2015 08:04:19 UTC+1, jmoniatte wrote:

<cfquery name="getUsers" datasource="main">

    select userId, userName, userEmail

    from users

    where userStatus = "active"

    <cfif len(arguments.userName)>

        and userName = <cfqueryparam value="#arguments.userName#" cfsqltype="string">

    </cfif>

    <cfif len(arguments.userEmail)>

        and userEmail = <cfqueryparam value="#arguments.userEmail#" cfsqltype="string">

    </cfif>

</cfquery>

How would you write a similar query in cfscript?

The chief mindset shift is the separation of "preparing your SQL statement" and "executing your SQL statement". <cfquery> let's you conflate that logic.

I don't have a CFML server running here, but - accidental syntax errors aside - this general approach would be the direct analogy of what you're doing in your <cfquery>:

sql = '

    select userId, userName, userEmail
    from users
    where userStatus = "active"

';
params = {}
if (arguments.userName.len()){
 sql &= ' and userName = :userName'
 params.userName = {value=arguments.userName, cfsqltype="string"};
}
if (arguments.userEmail.len()){
 sql &= ' and userEmail = :userEmail'
 params.userEmail = {value=arguments.userEmail, cfsqltype="string"};
}

getUsers = queryExecute(sql, params, {datasource="main"});

NB: you might just be able to pass values for the param values, instead of the structs with value/cfsqltype keys. I can't remember.

Also remember one can pass positional rather than named params with queryExecute(), if you don't need/want to label them.

TBH, I used to take this approach of having generic queries which are conditionally built, but they make me cringe a bit these days.

I'd have a more descriptive API, and have two methods: getUserByName() and getUserByEmail(), and dispense with the conditionality. The approach here is saving the developer some time when writing that particular method, but it makes all the code calling the API just that bit less "clean code".

I would have as little business logic in my DAOs... especially given this sort of approach makes testing that much more tricky.

-- 

Adam

[Jean Moniatte]

Thanks Adam for taking the time, much appreciated.

In theory I like the 2 methods option (getUserByName() and getUserByEmail()) better too, but it can quickly become many options with getUserByNameAndEmail(). Add a few more arguments (it is not rare with admin interfaces) and quickly a lot of code is duplicated.

I think that I will stick with cfquery tags, it is so simple and readable that I can live with the drawbacks (difficult to test, logic in DAOs - if we call that logic).

Thanks again Adam,

Jean

-- 

Adam

--

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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/8c580a12-8fce-42c8-8861-7ac9da91f887%40googlegroups.com.

[Nando Breiter]

Jean,

Here's working code, tested on both Lucee and ACF11, with small differences to Adam's example but essentially the same :

https://dnando.github.io/blog/2015/06/16/cfml-queries-in-script/

I find this almost as easy as using the <cfquery> tag for conditional queries, for any query in fact. 

Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland

+41 (0)91 600 9601
+41 (0)76 303 4477 cell
skype: ariamedia

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAG-7QUtt87azrfaWZ0O%2BzZYrvdGa%2BDROcsg5LcK%3D_yi37CRqJg%40mail.gmail.com.

[Jean Moniatte]

Thanks Nando for the extra example. Sticking with cfquery, the script version is too much of a hack for no true benefit in my opinion.

Jean

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAGHrs%3D8h906N-0qqm%2B%2BVAsG-EH9m7c_tVhshw%3DTY_HDxKqcK_A%40mail.gmail.com.

[Adam Cameron]

On 9 July 2015 at 10:58, Jean Moniatte <je...@ugal.com> wrote:

 the script version is too much of a hack

It's not "a hack", it's just a different implementation from that which you're comfortable with.

 the script version is too much of a hack for no true benefit in my opinion.

 

Other than not having to fall your CFCs back to needing to be in tags, which is a substantial benefit.

You like tags: fine. Don't dress your position up as anything other than that though.

-- 

Adam

[Jean Moniatte]

"Lectures" by Adam the Great. Soon on Amazon.

I like tags for queries. That's it, it is my opinion.

Jean

--
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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAOPrabWGQ56uMK7bvz8fCjn5XW8B9fCHfg%2BgunvHu%3DAjn0m-tw%40mail.gmail.com.

[Adam Cameron]

On 9 July 2015 at 11:32, Jean Moniatte <je...@ugal.com> wrote:

"Lectures" by Adam the Great. Soon on Amazon.

Oh, don't get it from Amazon. I'll send you a signed copy ;-)

 

I like tags for queries. That's it, it is my opinion.

Kinda like:

[Nando Breiter]

Well, Adam's response was much more clear and informative than the book on React.js I've been falling asleep to every night this week, purchased on Amazon, which drones on and on repeating itself in circles endlessly. <yawn>

Components components components components components components components ... I wonder why I try and read these things. 

Adam, you should consider writing programing books. I mean that sincerely. I would greatly prefer the following:

React.js

Stop sticking your head up your ass! Use a JS framework that makes sense.

With an appropriate cartoon image on the cover to go with it. 

 

--

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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAOPrabXdPR8y%3D4BE0Yx%2Byo8HMAq0r_91nMXciifr84wubr5dJA%40mail.gmail.com.

[Igal @ Lucee.org]

@Jean -

you can use the tags in script version, so in your cfscript you will have something like the following:

query name="getUsers" params={username: arguments.userName} {
    echo("   
        select userId, userName, userEmail

            from users

            where userStatus = 'active'

    ");

    if (len(arguments.userName))
        echo("and userName = :username");
}

so it's like your cfquery tag, but the sql text must be inside echo() or writeOutput().  you can, of course, prepare your sql statement first, and then just call the query like so:

query name="getUsers" { echo(sqlStatement); }

Igal Sapir
Lucee Core Developer
Lucee.org

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAG-7QUua9tw_UOLv3TtM_LamAhkOitC0SXd6cbjvssNRZbw_Xw%40mail.gmail.com.

[Gert Franz]

For me this is the best approach:

 

getUsers = queryExecute(sql:

"SELECT   userId, userName, userEmail

 FROM         users

WHERE    userStatus = "active"

               #len(arguments.userName) ? 'AND userName = :name' : ''#

               #len(arguments.userEmail) ? 'AND userMail = :mail' : ''#

", options: {

datasource: 'main'

}, params: {

     name: {sqltype:'string', value:arguments.userName},

     email: {sqltype:'string', value:arguments.userEmail}

});

 

I actually don’t know whether the not needed params are ignored, I didn’t test it. But if they don’t then there’s a little more coding necessary. But I like this a lot!

 

Sincerely
Gert Franz

 

RASIA GmbH

Spittelgasse 7

5103 Moeriken-Wildegg

Email: ge...@rasia.ch
Skype: gert.franz

Phone Switzerland: +41 76 5680 231

--

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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/15ff5236-1346-4329-a014-97ef34c611bb%40googlegroups.com.

[Adam Cameron]

On Tuesday, 14 July 2015 13:11:55 UTC+1, Gert Franz wrote:

For me this is the best approach:

 

getUsers = queryExecute(sql:

"SELECT   userId, userName, userEmail

 FROM         users

WHERE    userStatus = "active"

               #len(arguments.userName) ? 'AND userName = :name' : ''#

               #len(arguments.userEmail) ? 'AND userMail = :mail' : ''#

", options: {

datasource: 'main'

}, params: {

     name: {sqltype:'string', value:arguments.userName},

     email: {sqltype:'string', value:arguments.userEmail}

});

 

I actually don’t know whether the not needed params are ignored,

Yeah, that's where I started, but then also wasn't too sure if it would work passing unnecessary params, so decided against suggesting it (well: testing it ;-).

On  related note... if it does ignore unnecessary params... should it?

-- 

Adam

[AJ Mercer]

The function not showing up in docs

    http://docs.lucee.org/reference/functions/queryExecute.html

Should I log this?

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/00b201d0be2e%24443cc230%24ccb64690%24%40rasia.ch.

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

--

AJ Mercer
<webonix:net strength="Industrial" /> | <webonix:org community="Open" />
http://twitter.com/webonix

[Chris Blackwell]

This is my favoured approach too, works with ormExecuteQuery as well

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/00b201d0be2e%24443cc230%24ccb64690%24%40rasia.ch.

[Chris H]

I just checked the docs as well since i haven't seen queryExecute() anywhere before.
Would be good to have it in there :D

[Jamie Jackson]

I'm looking for a concise, CF 9/Lucee 4.5-compatible cfscript syntax. I don't have CF9 handy, but I tried Adam's syntax in Lucee.

I got:

Lucee 4.5.1.022 Error (expression)
Message	Element at position [1] doesn't exist in array
Stacktrace	The Error Occurred in /var/www/vmhost/apps/MuraCMS/WEB-INF/lucee/components/org/lucee/cfml/Query.cfc: line 126  124: elseif (NextChar EQ '?') 125: { 126: result.add( positionalParams[positionalCursor] ); 127: positionalCursor++ ; 128: Pos++ ; called from /var/www/vmhost/apps/MuraCMS/WEB-INF/lucee/components/org/lucee/cfml/Query.cfc: line 50  called from /var/www/vmhost/apps/MuraCMS/test/foo.cfm: line 32
Java Stacktrace	Element at position [1] doesn't exist in array   at lucee.runtime.type.ArrayImpl.invalidPosition(ArrayImpl.java:194):194   at lucee.runtime.type.ArrayImpl.getE(ArrayImpl.java:171):171   at lucee.runtime.type.ArrayImpl.get(ArrayImpl.java:124):124   at lucee.runtime.type.util.ArraySupport.get(ArraySupport.java:314):314   at lucee.runtime.util.VariableUtilImpl.get(VariableUtilImpl.java:255):255   at lucee.runtime.PageContextImpl.get(PageContextImpl.java:1435):1435   at org.lucee.cfml.query_cfc$cf.udfCall(/var/www/vmhost/apps/MuraCMS/WEB-INF/lucee/components/org/lucee/cfml/Query.cfc:126):126   at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:111):111   at lucee.runtime.type.UDFImpl._call(UDFImpl.java:328):328   at lucee.runtime.type.UDFImpl.call(UDFImpl.java:229):229   at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:766):766   at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:742):742   at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1589):1589   at org.lucee.cfml.query_cfc$cf.udfCall(/var/www/vmhost/apps/MuraCMS/WEB-INF/lucee/components/org/lucee/cfml/Query.cfc:50):50   at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:111):111   at lucee.runtime.type.UDFImpl._call(UDFImpl.java:328):328   at lucee.runtime.type.UDFImpl.call(UDFImpl.java:229):229   at lucee.runtime.ComponentImpl._call(ComponentImpl.java:641):641   at lucee.runtime.ComponentImpl._call(ComponentImpl.java:524):524   at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1760):1760   at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:742):742   at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1589):1589   at test.foo_cfm$cf.call(/var/www/vmhost/apps/MuraCMS/test/foo.cfm:32):32   at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:950):950   at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:902):902   at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:223):223   at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:35):35   at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2257):2257   at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2224):2224   at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:456):456   at lucee.loader.servlet.CFMLServlet.service(Unknown Source):-1   at javax.servlet.http.HttpServlet.service(HttpServlet.java:727):727   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303):303   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208):208   at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52):52   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241):241   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208):208   at org.apache.catalina.filters.RemoteIpFilter.doFilter(RemoteIpFilter.java:834):834   at org.apache.catalina.filters.RemoteIpFilter.doFilter(RemoteIpFilter.java:870):870   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241):241   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208):208   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220):220   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122):122   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504):504   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170):170   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103):103   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116):116   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421):421   at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074):1074   at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611):611   at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314):314   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145):1145   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615):615   at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61):61   at java.lang.Thread.run(Thread.java:745):745
Timestamp	7/23/15 12:00:17 PM CDT

Line 32 is: ).execute().getResult();

Recommendations?

Thanks,

Jamie

 

--

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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/d8c82fb4-6626-4cd1-b87d-9c68f696dd5f%40googlegroups.com.

[Jamie Jackson]

Okay, it seems as though the "parameters = [{...},{...}]" shorthand doesn't work in Lucee, but it works if I just chain addParam()s to the query object.

That would seem to be the cross-compatible solution.

Thanks,

Jamie