@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 http://lucee.org/On 7/9/2015 2:58 AM, Jean Moniatte wrote:
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
On Thu, Jul 9, 2015 at 11:22 AM, Nando Breiter <@Nando_Breiter mailto:Nando_Breiter> wrote:
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
On Thu, Jul 9, 2015 at 10:19 AM, Jean Moniatte <@Jean_Moniatte <mailto:@Jean_Moniatte>> wrote:
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
On Thu, Jul 9, 2015 at 10:01 AM, Adam Cameron <@Adam_Cameron <mailto:@Adam_Cameron>> wrote:
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
--
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
<mailto:lucee+unsubscribe@googlegroups.com>.
To post to this group, send email to
lucee@googlegroups.com <mailto:lucee@googlegroups.com>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/8c580a12-8fce-42c8-8861-7ac9da91f887%40googlegroups.com
<https://groups.google.com/d/msgid/lucee/8c580a12-8fce-42c8-8861-7ac9da91f887%40googlegroups.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
<mailto:lucee+unsubscribe@googlegroups.com>.
To post to this group, send email to lucee@googlegroups.com
<mailto:lucee@googlegroups.com>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/CAG-7QUtt87azrfaWZ0O%2BzZYrvdGa%2BDROcsg5LcK%3D_yi37CRqJg%40mail.gmail.com
<https://groups.google.com/d/msgid/lucee/CAG-7QUtt87azrfaWZ0O%2BzZYrvdGa%2BDROcsg5LcK%3D_yi37CRqJg%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
<mailto:lucee+unsubscribe@googlegroups.com>.
To post to this group, send email to lucee@googlegroups.com
<mailto:lucee@googlegroups.com>.
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
<https://groups.google.com/d/msgid/lucee/CAGHrs%3D8h906N-0qqm%2B%2BVAsG-EH9m7c_tVhshw%3DTY_HDxKqcK_A%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
mailto:lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com
mailto:lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/CAG-7QUua9tw_UOLv3TtM_LamAhkOitC0SXd6cbjvssNRZbw_Xw%40mail.gmail.com
https://groups.google.com/d/msgid/lucee/CAG-7QUua9tw_UOLv3TtM_LamAhkOitC0SXd6cbjvssNRZbw_Xw%40mail.gmail.com?utm_medium=email&utm_source=footer.
For more options, visit https://groups.google.com/d/optout.