cfquery within cfscript using cfqueryparam

[Patrick Heppler]

Maybe I'm just too stupid, but I can't get this to work.

My current query is:

query name="qSubNavi"{

echo ("SELECT alias,navititle FROM page WHERE (hidden is NULL AND tree_level=1) AND (pageID = '#arguments.currentID#' OR parent='#arguments.currentID#' OR FIND_IN_SET('#arguments.currentID#', sibling))")

};

Now I want to use cfqueryparam for arguments.currentID

Tried:

query name="qSubNavi"{

echo ("SELECT alias,navititle FROM page WHERE (hidden is NULL AND tree_level=1) AND (pageID = '' OR parent='' OR FIND_IN_SET('', sibling))");

queryparam cfsqltype="cf_sql_integer" value="#arguments.currentID#"; 

};

Also tried:

query name="qSubNavi"{

echo ("SELECT alias,navititle FROM page WHERE (hidden is NULL AND tree_level=1) AND (pageID = '' OR parent='' OR FIND_IN_SET('', sibling))")

queryparam cfsqltype="cf_sql_integer" value="#arguments.currentID#"

};

Both end up in SQL Syntax error because currentID gets just appended to the SQL

What I'm missing?

[James Kilford]

Hi Patrick, 

You need to put your queryparams inline, just like with the tag way of doing things: 

query name="getAccountIdByRegistrationGUID" {

writeOutput("select id from account where registrationGUID = ");

queryParam cfsqltype="cf_sql_varchar" value="#arguments.registrationGUID#";

writeOutput(" and isActive = 1");

}

Hope this helps.

James

--
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/5bbd3435-e69a-4b4d-9526-e34ca6d5114a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[James Kilford]

Forgot to say, alternatively you can use the new Query() syntax.  You can then do a bunch of addParam calls afterwards: 

var getAccountIdByRegistrationGUID = new Query();

getAccountIdByRegistrationGUID.setSQL("select id from account where registrationGUID = :registrationGUID and isActive = 1");

getAccountIdByRegistrationGUID.addParam(name="registrationGUID", cfsqltype="cf_sql_varchar", value="#arguments.registrationGUID#");

etc.

HTH. 

James

​

[Patrick Heppler]

Thanks! I think it's better to use the new Query() syntax, because I need the same param three times within one query.

[Adam Cameron]

On Wednesday, 16 April 2014 13:20:28 UTC+1, Patrick Heppler wrote:

Thanks! I think it's better to use the new Query() syntax, because I need the same param three times within one query.

You sure you want to use that (pretty clunky) syntax, over using queryExecute()?

eg:

result = queryExecute("

SELECT id

FROM account

WHERE registrationGUID = :registrationGUID

AND isActive = 1

", {registrationGUID=arguments.registrationGUID});

Isn't that better?

-- 

Adam

[James Kilford]

Yes, it's terrific -- no need to do the execute, get the results, etc. 

--

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/dc7a0f74-1015-477c-b73e-e1466fd40441%40googlegroups.com.

[Igal @ getRailo.org]

actually the newer new way (should work at the edge of 4.1) is my preferred one:

    params = [ {name: registrationGUID, value: arguments.registrationGUID} ];

    query name="qResult" params=params { echo("

        SELECT    id
        FROM    account
        WHERE    registrationGUID = :registrationGUID

            AND    isActive = 1;
    "); }

or with positional (unnamed) params (inlined in this example):

    query name="qResult" params=[arguments.registrationGUID] { echo("

        SELECT    id
        FROM    account
        WHERE    registrationGUID = ?
            AND    isActive = 1;
    "); }

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/CAEvdFmT4P142Novtdh5pi88kdpTmG%3De1MUTs4wO8QgLOCrf%3DXA%40mail.gmail.com.

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

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

[AJ Mercer]

Adding to Adam's queryExecute ( info I have since juts learnt from his post)

you can specify db field type as well

result = queryExecute("

SELECT id

FROM account

WHERE registrationGUID = :registrationGUID

AND isActive = 1

", {registrationGUID={value="arguments.registrationGUID, cfsqltype="CF_SQL_VARCHAR"}}

);

another post from Adam on the subject

@ Igal - I know it is just a matter of opinion, but one of the things that I loved about CFML when I first saw it was the lack of function to output dynamic strings - having echo to build an SQL string, IMHO, goes against what is so cool about CFML

--

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/dc7a0f74-1015-477c-b73e-e1466fd40441%40googlegroups.com.

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

--

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

Railo Community Manager

[Igal Sapir]

Yes, I don't like that part either...

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/CAPURtC2t5u97xemrrqsTPGp3GOwODqWPP4dHD6rVfUyhq5ppsQ%40mail.gmail.com.