query in cfscript with railo 4

[Mattijs Spierings]

Hi,

In Railo 3.3 I used 2 methods of querying in my service layer (cfc):

1)
query name="r" {
   writeoutput("select * from table where id=")
   queryparam value="#value# cfsqltype="cf_sql_integer";
   writeoutput(" and sec_id=");
   queryparam value="#value2# cfsqltype="cf_sql_integer";
}

2)
var q = new Query();
q.addParam(name="cam_cus_id",value="#Arguments.cus_id#",cfsqltype="cf_sql_integer",list=false);
var sql = "select cam_id
            from dbo.Campaigns
            where cam_cus_id = :cam_cus_id";
   
//execute query and return result
 return q.execute(sql=sql).getResult();

Method 1 reminds me most of <cfquery> except for the ugle writeouput and queryparam insertions.

Is there a new method in Railo 4 that I am not aware of? Couldn't find it in release notes.

Cheers,

mattijs

[Igal]

no new method in Railo4 (is there a need?)

option 1 IS indeed a cfscript version of the cfquery tag.  in Railo most tags can be used in script by dropping the cf prefix, and adding the attributes, closing the whole thing with a semi-colon.  on tags that have a body you put the body inside { curly braces }.

did you try?: (and no, I don't mean to say Elvis here)

query name="r" {
   

   select * 

   from table 

   where id="#value#
      and sec_id="#value2#;
}

Igal

[Mattijs Spierings]

Hi Igal,

I don't know were I read I was supposed to use writeoutput within the {body} but that might not be necessary, you tell me. Interesting. Going to try it immediately.
Queryparam can be used to with in this body (like in example 1)?

Cheers,

Mattijs


Op woensdag 5 december 2012 21:24:25 UTC+1 schreef Igal het volgende:

[Igal @ getRailo.org]

to tell the truth I usually use option #2 in cfscript so I don't remember the exact syntax of option #1.

if it doesn't work as I suggested, then we should try to change Railo so that it does, because it really makes much more sense.

let us know what you find,


Igal

[Mattijs Spierings]

Hi,

Well this doesn't work:
        query name="test" {
            select * from customers
        }
        dump(test);
        abort;

but this does:
        query name="test" {
            writeoutput("select * from customers");
        }
        dump(test);
        abort;

So example 1 in first post is most complete version of this form of querying.
Changing Railo to accomodate this would be nice but I reckon you guys are already quiet busy.

If you could support something like:
        query name="test" {
            select * from customers
            where cus_id=queryparam value="#value# cfsqltype="cf_sql_integer"
        }

that of course would be awesome. But no rush...I use option 1 and 2 on different occasions for now.

Cheers,

Mat

Op vrijdag 7 december 2012 20:32:09 UTC+1 schreef Igal het volgende:

[Igal Sapir]

The main issue that I see with that suggested syntax is differentiating between literal query text and the cfqueryparam

Micha will be able to tell us if that is feasible.

Igal

--
typos, misspels, and other weird words brought to you courtesy of my mobile device.

[Peter Boughton]

The writeOutput is needed because Railo doesn't (yet?) have a full SQL parser.


Regarding this:

   query name="test" {
       select * from customers
       where cus_id=queryparam value="#value# cfsqltype="cf_sql_integer"
   }

What I would prefer (for both script and tag syntax), would be named params like this:

    <cfquery name="test">
        SELECT columns
        FROM customers
        WHERE cus_id         = :named_param
        AND   something_else = :other_param
        
        <cfqueryparam name="named_param" value=#value# cfsqltype="cf_sql_integer" />
        <cfqueryparam name="other_param" value="#other#" />
    </cfquery>

Perhaps even having the option of a single queryparam tag with all the data in:

    <cfqueryparam params=#struct# />

Well, not too fussed on that part - the main bit I'd like is being able to write the SQL in a single uninterrupted block, with param values provided separately (however that's done).

[Igal @ getRailo.org]

+1 on separating the params from the sql statement.

I'd like to also add to that the positioned-param in the form of a
question-mark so

WHERE cut_id = ?

will work as well.


Igal

--
Igal Sapir
Railo - Open Source CFML Engine
http://getrailo.org

[Jean Moniatte]

+1 from here too, it would be a very nice feature to be able to separate the params.

Thanks,

Jean

--

Jean Moniatte

UGAL

je...@ugal.com

http://www.ugal.com

[AJ Mercer]

this is something I have heard about, but not yet investigated for my self.

This is the first thing I found after a search that may give you some moe pointers on option #1

    http://stackoverflow.com/questions/9298032/sql-error-with-new-query-in-coldfusion-railo-script

it uses the

    :variable notation

--

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

Railo Community Manager

[Igal @ getRailo.org]

the :varname notation (as well as the ? param) is currently used in Railo's cfscript based queries.

we're discussing here to request a new feature so that this method will be used in <cfquery > tag as well.

[Mattijs Spierings]

So, maybe combine both as suggested above and merge the 2 approaches:

query name="r" {
   select * from table where id= :param1
   and cus_id= :param2;

   queryparam name=":param1" value="#value# cfsqltype="cf_sql_integer";
   queryparam name=":param2" value="#value2# cfsqltype="cf_sql_integer";
}
which is exactly what Peter Boughton suggested but then in CFSCRIPT notation. Would work for me

Or variation:

query name="r" {

   select * from table
   where

       id= <queryparam  value="#value# cfsqltype="cf_sql_integer"> and
       sec_id= id= <queryparam " value="#value2# cfsqltype="cf_sql_integer">
}

if this doens't give any parsing issues?

Cheers

Op zaterdag 8 december 2012 01:13:17 UTC+1 schreef Igal het volgende:

[Justin Carter]

On Saturday, December 8, 2012 9:36:51 PM UTC+11, Mattijs Spierings wrote:

So, maybe combine both as suggested above and merge the 2 approaches:

query name="r" {
   select * from table where id= :param1
   and cus_id= :param2;

   queryparam name=":param1" value="#value# cfsqltype="cf_sql_integer";
   queryparam name=":param2" value="#value2# cfsqltype="cf_sql_integer";
}
which is exactly what Peter Boughton suggested but then in CFSCRIPT notation. Would work for me

Or variation:

query name="r" {
   select * from table
   where
       id= <queryparam  value="#value# cfsqltype="cf_sql_integer"> and
       sec_id= id= <queryparam " value="#value2# cfsqltype="cf_sql_integer">
}

if this doens't give any parsing issues?

Cheers

The reason the writeOutput() is required inside Railo's query{} block is that you need a way to separate the SQL from your code, such as logic that you typically embed in dynamic queries - this is a problem for any tags that have body content.

I still think the best and most concise approach is to look at E4X-style syntax and actually use tags. It makes sense because this is the way we've always used queries, it's very readable, and it's a potential solution for other tags like cfmail and custom tags where you have a mixture of nested tags and tag body content. I wrote a blog post about this almost 2 years ago - time flies!

http://www.madfellas.com/blog/index.cfm/2011/1/26/Using-CFML-tags-in-cfscript-C4X-prototype

Compare these two syntaxes :)

// Railo 3.2 query with logic

query name="artists" datasource="cfartgallery" {

writeOutput("SELECT firstname, lastname, email, city

FROM app.artists

WHERE

city IN (?)");

queryparam cfsqltype="cf_sql_varchar" list="true" value="New York,Washington";

if (myartMember) {

writeOutput(" AND email LIKE ?");

queryparam cfsqltype="cf_sql_varchar" value="%@myart.com";

}

}

 

// E4X-style query with logic

artists2 = <cfquery datasource="cfartgallery">

SELECT firstname, lastname, email, city

FROM app.artists

WHERE

city IN (<cfqueryparam cfsqltype="cf_sql_varchar" list="true" value="New York,Washington">)

<cfif myartMember>

AND email LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%@myart.com">

</cfif>

</cfquery>;

Whether or not this is "easy" to implement in Railo's compiler is another story, I have no idea... 

cheers,

Justin

[Michael Offner]

let's look into this in detail.

Outside a cfscript everything in the code is written to the response stream.

so if you wanna do the following

----- start index.cfm ----

Hello World
----- end index.cfm ----

in cfscript, you have to do as follows:

----- start index.cfm ----

<cfscript>

   writeoutput('Hello World');

</cfscript>
----- end index.cfm ----

so to write to the response stream (or better the nearest CFMLWriter) inside cfscript, you need "writeoutput", "echo", "dump" ...

you cannot simple do the following
----- start index.cfm ----

<cfscript>

   Hello World

</cfscript>
----- end index.cfm ----

this is of course not working, because this produce a syntax error.

but this is exactly what you suggest, so to make something work, you need a new language for the tags body.

you can say the language is very simple, it only can handle literal string like your example:

query name="test" {
       select * from customers
   }

then you still have the problem to detect the bodys end ( "}" ), for example with a sql statement like

select * from customers where placeholder = '{susi}'

but even worse, what if expressions come into the game, the following is definitely not a option

query name="test" {
       select * from customers
       where cus_id=queryparam value="#value# cfsqltype="cf_sql_integer"
   }

it is not possible to write a parser that can parse this the right way,

how the parser should know that queryparam is no literal string anymore.

of course you cold do something like 

query name="test"  params="#[value]#" {
       select * from customers
       where cus_id=?
   }

but this is only a solution for cfquery, but what happens with cfmail, cfthread ...

you need the possibility to define statements and expression inside a body tag, otherwise body tags are useless.

in other words if you don't like the syntax inside the body, you need a complete new language.

Micha

2012/12/8 Igal @ getRailo.org <ig...@getrailo.org>

--

/micha

Michael Offner CTO Railo Technologies GmbH

[Justin Carter]

Micha, what about my suggestion? It doesn't need a "new language", just a different way of handling tags "within" cfscript ;)

[Michael Offner]

in your example, you are using the "tag language", what is in the end the same, a other language.

we (including Adobe) have discussed all of this in the CFML Advisory Committee and we came to the decision about the Syntax we use now. the current solution extends cfscript to support tags, but the body still follows the same rule as every other body in cfscript.

We also support "new Query(...)" in Railo, but i'm not really a Fan of it, adding build in CFCs was a failure from my perspective, BUT the syntax is definitely the best solution for queries ( from my perspective ).  

a solution could be something similar to ormExecuteQuery.

pattern:

queryExecute(string sql[,collection params[, string datasource ...]])

example usage:

qry=queryExecute(

    "SELECT firstname, lastname, email, city  FROM app.artists WHERE city in (:cities )"

    {cities:"New York,Washington"});

or

qry=queryExecute(

    "SELECT firstname, lastname, email, city  FROM app.artists WHERE city in (:cities )"

    {cities:{cfsqltype:"cf_sql_varchar", list:"true", value:"New York,Washington"}});

or
qry=queryExecute(

    "SELECT firstname, lastname, email, city  FROM app.artists WHERE city in (?)"

    ["New York,Washington"]);

or
qry=queryExecute(

    "SELECT firstname, lastname, email, city  FROM app.artists WHERE city in (?)"

    [{cfsqltype:"cf_sql_varchar", list:"true", value:"New York,Washington"}]);

Micha

p.s. Your example in the Blog for Railo's query syntax is wrong, script tags are really working the same way as regular tags, so you have to change this

1. query name="artists" datasource="cfartgallery" {  
2.     writeOutput("SELECT firstname, lastname, email, city  
3.         FROM app.artists  
4.         WHERE  
5.             city IN (?)");  
6.     queryparam cfsqltype="cf_sql_varchar" list="true" value="New York,Washington";  
7.     if (myartMember) {  
8.         writeOutput(" AND email LIKE ?");  
9.         queryparam cfsqltype="cf_sql_varchar" value="%@myart.com";  
10.     }  
11. } 

to

1. query name="artists" datasource="cfartgallery" {  
2.     writeOutput("SELECT firstname, lastname, email, city  
3.         FROM app.artists  
4.         WHERE  

1.             city IN (");  
2.     queryparam cfsqltype="cf_sql_varchar" list="true" value="New York,Washington"; writeOutput(")") 
3.     if (myartMember) {  
4.         writeOutput(" AND email LIKE ");  

1.         queryparam cfsqltype="cf_sql_varchar" value="%@myart.com";  
2.     }  
3. } 

and you can also write "echo" instead of "writeoutput" this is shorter

2012/12/10 Justin Carter <justin....@gmail.com>

[Igal @ getRailo.org]

+1 for the queryExecute() function!

the current parser for the params is written in CFML so we need to rewrite it in Java, right?


Igal

[Michael Offner]

we already have this for ormExecuteQuery.

Micha

2012/12/10 Igal @ getRailo.org <ig...@getrailo.org>

[Igal @ getRailo.org]

awesome, so I'll open a feature request in the JIRA for queryExecute().


Igal

[Mark Drew]

+1

[Michael Offner]

what we also could do is the following

<cfquery params="#[{cfsqltype:"cf_sql_varchar", list:"true", value:"New York,Washington"}]#">

   SELECT firstname, lastname, email, city  FROM app.artists WHERE city in (?)

</cfquery>

i have also checked ormExecuteQuery, the SQL is parsed by Hibernate, not Railo

Micha

2012/12/10 Mark Drew <ma...@getrailo.com>

[Mark Drew]

One thing that we should keep in mind is to reduce the number of characters rather than increase them. That is why I really don't like doing something like:

qry = <cfquery>…</cfquery>;

Seems totally counter intuitive in cfscript. 

I love the queryExecute() and very surprised it isn't in there already since in JavaScript (for example) a lot of the query functions are:

execute("SQL", ArrayofParams[]) 

so I would love the ability (if possible) to do both

queryExecute(SQL, array) 

and

queryExecute(SQL, struct)

The first (with array) for simple valiues and the second for more complex items that I don't know what order they are added to a dynamic query. 

I did a lot of this when I was working with Google Gears (the array format) 

regards

MD

[Hendrik Kramer]

+1 for both array and struct params. I would also recommend to support named parameters (:1, :2 etc.). Sometimes we need to reuse the same input parameters on different places in the sql query statement and named parameters would reduce the parameters that we need to send, e.g.

select lastname from users where ( lastname like :1 or firstname like :1 ) and region = :2

[Matt Quackenbush]

Great suggestion!

[Justin Carter]

I did an analysis of the number of characters typed, and the E4X style uses fewer characters than Railo's implementation (see the blog post). It's also much easier to read because you don't have writeOutput() calls all over the place, and editors would be able to perform better syntax highlighting on the SQL keywords because the code isn't trapped inside a string.

Those are all pretty big failings of what the CFML Advisory agreed on, which IMO is why people want a better solution :P

Just my 2 cents.

Cheers,
Justin

[Chris Blackwell]

+1 for queryExecute with array and struct params

[Justin Carter]

A queryExecute() function seems nice with trivial examples, but I get the feeling it'll be the same string concatenation hell when it comes to doing something complex... And again, you're missing formatting and syntax highlighting because all your SQL code is stuck inside a string. 

It's just such a step backwards from good old <cfquery> IMO.

cheers,

Justin

[Peter Boughton]

> One thing that we should keep in mind is to reduce
> the number of characters rather than increase them.

Absolutely! In fact, we should all write Lisp with single character variable names.

    (= q (s p))

Can't beat that for readability... right?

:P


Being able to use actual tags in script would be GREAT for readability.

As with the current implementation of tags in script, there would be no cf needed, and of course single line queries are rare, so it would be more:

    qry =
        <query>
            SELECT columns
            FROM tables
            WHERE conditions
            ORDER BY column
        </query>

Which is awesome.

Don't know how feasible it is (probably depends if there's any situations where a LT and a tag could be confused), but being able to jump out of script at will (rather than only jumping in when convenient) would result in much cleaner and nicer code.

[Alex Skinner]

I'm not excited about this the development effort VS reward seems imbalanced certainly I assume it would be to mess about with the parser

Tags = nice abstraction and not hard to abstract from your other code via functions/methods to give u script calls

Script = concatenation but if you want to use script then u thats the pain

But mixing the two seems creating a very old child which i don't think is initiative for new developers to pick up, isn't cfscript strange enough.

Is this really what the community is crying out for ? I thought cfquery and cfqueryparam where one of the nicest things of CF

my2p 

A

--
Alex Skinner
Managing Director
Pixl8 Interactive

Tel: +448452600726
Email: al...@pixl8.co.uk
Web: pixl8.co.uk

[Justin Carter]

On Tuesday, December 11, 2012 10:38:00 AM UTC+11, Alex Skinner wrote:

But mixing the two seems creating a very old child which i don't think is initiative for new developers to pick up, isn't cfscript strange enough.

Is this really what the community is crying out for ? I thought cfquery and cfqueryparam where one of the nicest things of CF

my2p 

A

Yes, cfquery and cfqueryparam are great :)

I don't think this is a case of mixing the two creating a "very odd child"... The idea behind (part of) E4X was being able to create XML literals on the fly in JavaScript / ActionScript. E.g.

personXML = <person>

  <name>Bob</name>

  <age>42</age>

</person>;

It's not counter intuitive at all, it's just like the syntax for array literals or object literals. (E4X goes a lot further than this, allowing you to traverse and manipulate the objects, but that's a bit of a tangent here).

So that was how the idea of CFML tag literals popped up... Syntactically, I think it's great because there is practically nothing new to learn.

qPerson = <cfquery>

  SELECT * from people

  WHERE name = <cfqueryparam cfsqltype="cf_sql_varchar" value="Bob">

</cfquery>;

Kinda easy, right? :P

cheers,

Justin

[Peter Boughton]

Alex wrote:
> I thought cfquery and cfqueryparam where one of the nicest things of CF

cfquery *is* one of the nicest things in CF.

cfqueryparam is a chore (I've already pointed out how named params and either child tags or an attribute would make me happy there, and to be clear I see that as an independent feature/change from where this discussion has evolved to).


The readability of cfquery (and other block-level tags) is why I still write 90%+ of my code in tags.

That doesn't change that there are places where I've got a bunch of single-line cfif and cfset statements that would be way easier to express in script syntax, but - due to the ugliness (or even impossibility, due to nesting) of mixing script with constructs like cfquery - I do it all in tags (whilst others do it all in script, making a different set of readability trade-offs).

It's basically about being able to use whatever syntax makes sense for the code, rather than being artificially limited to way X or way Y.

> don't think is initiative for new developers to pick up

Aside from disagreeing entirely over your intuitiveness claim, if you only ever design a language for new developers, all you're doing is crippling existing/advanced users.

The proposed blending of tags and script adds (at worst) a single very slight curve for newcomers, whilst providing a far more expressive syntax to experienced developers.




Gert wrote:
> Nevertheless I like the queryExecute() function as well...

Yep, having a QueryExecute(SQL,Params) function does still make sense. In particular it would be convenient for certain types of dynamic SQL generation.

[James Kilford]

Totally agree with what you say about <cfquery... but I'm writing almost entirely script now in Railo, and the absence of <cfquery doesn't trouble me.  Mostly I write a few bits of SQL in some black box somewhere and then forget about it. 

I've use the

query {

    writeOutput(

}

and the 

new Query();

methods as often as each other, and I don't mind either.  I guess I prefer the latter because of the absence of writeOutput commands, but equally I spend a lot of time head-scratching why things aren't working -- and it's usually because I forgot to do a .execute()

[Mattijs Spierings]

Concerning this example:

qPerson = <cfquery>

  SELECT * from people

  WHERE name = <cfqueryparam cfsqltype="cf_sql_varchar" value="Bob">

</cfquery>;

I know we like to keep things consisten but why cant we take out the <cfquery> but leave the <cfquerparam> in this syntax:

qPerson = "

  SELECT * from people

  WHERE name = <cfqueryparam cfsqltype="cf_sql_varchar" value="Bob">

";

Or do something like :

qPerson = "

  SELECT * from people

  WHERE name = :param cfsqltype="cf_sql_varchar" value="Bob":
Or
 WHERE name = <cfsqltype="cf_sql_varchar" value="Bob">

";

I realize you need an end charachter for the param which could be anything just so that the regex engine can catch it easily.

In general I just prefer to write clean SQL in my code with unnecessary code removed.

[Justin Carter]

I'm not sure how that could work because it would just be seen as a string.

The tags aren't just for consistency, it's necessary to have a syntax that could be parsed and not mistaken for code that has some alternative meaning (such as being interpreted just as a string). In addition, you also need to be able to use logic to build dynamic queries. But, consistency is a good reason for implementing queries in script in this way, because it's how we've always written queries in the past, and it just happens to be very readable and very concise as well :)

Cheers,
Justin

[Mattijs Spierings]

Well you can already do this:

var q= new Query();
q.addParam(name="cus_id",value="123",cfsqltype="cf_sql_integer");
q.execute(sql="
   select * from
   customers
   where cus_id=:cus_id
");

In this case :cus_id has to be parsed to, and it works like a charm. So parsing the body of query {} and finding the queryparam shouldn't be that much more difficult as long as we agree on the specific character that marks the start of the queryparam

Cheers

Mattijs

Op donderdag 13 december 2012 21:27:20 UTC+1 schreef Justin Carter het volgende:

[Justin Carter]

The difference there is that Query is an object, and you are passing strings into a function as an argument and then calling a method to execute the built up query... With your earlier examples, there isn't really a way to discern them from plain old strings... e.g.

qPerson = "SELECT * from people WHERE name = :param cfsqltype="cf_sql_varchar" value="Bob":";

s = "Select your name";

There would be no way to change a datasource, no way to do query of queries, no way to include conditional logic, etc... 

And again, putting SQL inside strings means you lose syntax highlighting of SQL keywords, which is really annoying if you are doing anything more complex than "SELECT * from Blah".

Parsing the body of a query{} block as SQL also can't work because there would be no way to do logic inside of it for handling dynamic queries.

P.S. I don't think the E4X-style syntax even needs to be "optimised" for character counts any further. It's consistent with CFML tags, it's very readable, and it's more concise that the other existing methods of doing queries in cfscript, especially with complex queries... It could only be a win/win alternative IMO :)

qPerson = <cfquery>
  SELECT * from people
  WHERE name = <cfqueryparam cfsqltype="cf_sql_varchar" value="Bob">
</cfquery>;

cheers,

Justin