cfquery Using cfscript

We can use cfscript to execute a query passing SQL statements to a data source. We can execute sql statement using execute method with query object. Look below for explaination and details. I hope it is helpful for you.

How to define query object in cfscript:

[code:cf]
<cfscript>
queryObj = new Query();
<!— OR —>
queryObj = createObject("component","query");
</cfscript>
[/code]

Properties :

name blockfactor cachedafter
cachedwithin dataSource dbtype
debug maxRows password
result timeout username
sql

All attributes are supported by cfquery tag can be used as attribute.

Example:

[code:cf]
<cfquery name="myList"></cfquery>

can be used as

queryObj.setName("myList");
[/code]

Methods:

addparam (Add Parameter of query).

[code:cf]queryObj.addParam(name="mediaId",value="1",cfsqltype="numeric");[/code]

execute (for executing sql statements);

[code:cf]result = queryObj.execute(sql="select ArtName from art where mediaId = :mediaId");[/code]

clearParams (To remover all set param with statements.)

[code:cf]queryObj.clearParams();[/code]

How to set properties and method with execute sql statements.

[code:cf]

<cfscript>
queryObj = new query();
queryObj.setDatasource("cfartgallery");
queryObj.setName("qListOfArts");
queryObj.addParam(name="price",value="32000",cfsqltype="NUMERIC");
queryObj.addParam(name="mediaid",value="1",cfsqltype="NUMERIC");
queryObj.addParam(name="isSold",value="0",cfsqltype="SMALLINT");
result = queryObj.execute(sql="SELECT artname,description,price FROM Art WHERE mediaId = :mediaid and isSold = :isSold and price > :price");
qListOfArts = result.getResult();
metaInfo = result.getPrefix();
queryObj.clearParams();
writeDump(qListOfArts);
writeDump(metaInfo);
</cfscript>

[/code]

Query output and Meta information.