Random CFC
Thursday December 28, 2006 4:13 PM
Word Count: 2637
Today I was inspired to write a Random utility component. The first article that inspired me to write this utility was Pete Freitag's article on selecting a random row from a database table. The second was Ben Forta's article on how ColdFusion is not a DBMS. The main point was to leverage the DBMS whenever possible and let ColdFusion do the job that it needs to. With that in mind I created a Random component that allows you to retrieve a random row, retrieve random rows, and incorporated a few random utilities from cflib. After completing this I realized that putting DBMS specfic methods with utility methods was not the best design but would work for now until I get a chance to separate them. You want to see some code, ok lets take a look.
Random.cfc
<!---
License:
Copyright 2006 Dan Vega (danvega@gmail.com)
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
Copyright: Dan Vega (danvega@gmail.com)
$Id: Random.cfc
--->
<cfcomponent name="Random">
<cfset variables.dbtype = "">
<cfset variables.dsn = "">
<cffunction name="init" access="public" output="false" returntype="Random">
<cfargument name="dbtype" type="string" required="true">
<cfargument name="dsn" type="string" required="true">
<cfset var validDbTypes = "mysql,mssql,postgre,oracle">
<cfif NOT listFindNoCase(validDbTypes,arguments.dbtype,",")>
<cfthrow message="Invalid dbtype. Valid dbtypes are mysql,mssql,postgre and oracle.">
<cfelse>
<cfset variables.dbtype = arguments.dbtype>
<cfset variables.dsn = arguments.dsn>
</cfif>
<cfreturn this>
</cffunction>
<cffunction name="getRandomRow" access="public" output="false" returntype="query">
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" default="1">
<cfset var qRow = "">
<cfswitch expression="#variables.dbtype#">
<cfcase value="mysql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT *
FROM #arguments.tablename#
ORDER BY RAND()
LIMIT #arguments.topN#
</cfquery>
</cfcase>
<cfcase value="mssql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT TOP #arguments.topN# *
FROM #arguments.tablename#
ORDER BY NEWID()
</cfquery>
</cfcase>
<cfcase value="postgre">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT #arguments.topN#
FROM #arguments.tablename#
ORDER BY RANDOM()
LIMIT 1
</cfquery>
</cfcase>
<cfcase value="oracle">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT * FROM
( SELECT * FROM #arguments.tablename#
ORDER BY dbms_random.value )
WHERE rownum = #arguments.topN#
</cfquery>
</cfcase>
</cfswitch>
<cfif qRow.recordCount EQ 0>
<cfthrow message="The table [#arguments.tablename#] returned 0 records.">
</cfif>
<cfreturn qRow>
</cffunction>
<cffunction name="getRandomRows" access="public" output="false" returntype="query">
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" required="true">
<cfset var results = getRandomRow(arguments.tablename,arguments.topN)>
<cfreturn results>
</cffunction>
<cffunction name="getRandomString" access="public" output="false" returntype="string">
<cfargument name="type" type="string" required="true">
<cfargument name="length" type="numeric" required="true">
<cfset var validTypes = "alpha,alphanum,secure,numeric">
<cfset var i = 1>
<cfset var randStr = "">
<cfset var randNum = "">
<cfset var alpha = "A,B,C,D,E,F,G,H,I,J,K,L,
M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z">
<cfset var secure = "!,@,$,%,&,*,-,_,=,+,?,~">
<cfif NOT listFindNoCase(validTypes,arguments.type,",")>
<cfthrow message="Invalid type. Valid types are alpha,alphanum,secure,numeric.">
<cfelse>
<!--- RandString() http://www.cflib.org/udf.cfm?id=171&enable=1 --->
<cfscript>
/**
* Returns a random string of the specified length of either alpha, numeric or mixed-alpha-numeric characters.
* v2, support for lower case
* v3 - more streamlined code
*
* @param Type Type of random string to create. (Required)
* @param Length Length of random string to create. (Required)
* @return Returns a string.
* @author Joshua Miller (josh@joshuasmiller.com)
* @version 2, November 4, 2003
*/
for(i=1;i LTE arguments.length;i=i+1){
if(arguments.type is "alpha"){
randNum=RandRange(1,52);
useList=alpha;
}else if(arguments.type is "alphanum"){
randNum=RandRange(1,62);
useList="#alpha#,0,1,2,3,4,5,6,7,8,9";
}else if(arguments.type is "secure"){
randNum=RandRange(1,73);
useList="#alpha#,0,1,2,3,4,5,6,7,8,9,#secure#";
}else if(arguments.type is "numeric"){
randNum=RandRange(1,10);
useList="0,1,2,3,4,5,6,7,8,9";
}
randStr="#randStr##ListGetAt(useList,randNum)#";
}
return randStr;
</cfscript>
</cfif>
</cffunction>
<cffunction name="getRandomColor" access="public" output="false" returntype="string">
<cfargument name="escapePoundSign" type="boolean" default="false">
<cfset var redColor = formatBaseN(randRange(0,255),16)>
<cfset var greenColor = formatBaseN(randRange(0,255),16)>
<cfset var blueColor = formatBaseN(randRange(0,255),16)>
<cfscript>
/**
* Returns a completely random color. Beautiful, isn't it?
* Version 2 by Raymond Camden
*
* @return Returns a string.
* @author Nathan Strutz (nathans@dnsfirm.com)
* @version 2, May 13, 2003
*/
if(len(redColor) is 1) redColor = "0" & redColor;
if(len(greenColor) is 1) greenColor = "0" & greenColor;
if(len(blueColor) is 1) blueColor = "0" & blueColor;
return "##" & redColor & greenColor & blueColor;
</cfscript>
</cffunction>
<cffunction name="getRandomDirectory" access="public" output="false" returntype="string">
<cfargument name="rootDirectory" type="string" required="true">
<cfargument name="recurse" type="boolean" default="false">
<cfset var directoryName = "">
<cfset var qDirectories = "">
<cfset var listing = "">
<cfif directoryExists(arguments.rootDirectory)>
<cfdirectory action="list" directory="#arguments.rootDirectory#" name="qDirectories" recurse="#arguments.recurse#">
<cfif qDirectories.recordCount GT 0>
<cfloop query="qDirectories">
<cfif type EQ "dir">
<cfset listing = listAppend(listing,"#name#")>
</cfif>
</cfloop>
<cfelse>
<cfthrow message="The were no directories found under the root directory [#arguments.rootDirectory#].">
</cfif>
<cfelse>
<cfthrow message="The root directory passed to getRandomDirectoryName is not valid">
</cfif>
<cfreturn listGetAt(listing,randRange(1,listLen(listing)),",")>
</cffunction>
<cffunction name="getRandomImage" access="public" output="false" returntype="string">
<cfargument name="imageDirectory" type="string" required="true" hint="Absolute path to the direcotry.">
<cfset var qFiles = "">
<cfset var images = "">
<cfif directoryExists(arguments.imageDirectory)>
<cfdirectory action="list" directory="#arguments.imageDirectory#" name="qFiles">
<cfif qFiles.recordCount GT 0>
<cfloop query="qFiles">
<cfif type EQ "File" AND (findNoCase("gif",name) OR findNoCase("jpg",name) OR findNoCase("png",name)) >
<cfset images = listAppend(images,"#name#")>
</cfif>
</cfloop>
<cfelse>
<cfthrow message="There were no images found in directory [#arguments.imageDirectory#].">
</cfif>
<cfelse>
<cfthrow message="The root directory passed to getRandomDirectoryName is not valid">
</cfif>
<cfreturn listGetAt(images,randRange(1,listLen(images)),",")>
</cffunction>
</cfcomponent>
License:
Copyright 2006 Dan Vega (danvega@gmail.com)
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
Copyright: Dan Vega (danvega@gmail.com)
$Id: Random.cfc
--->
<cfcomponent name="Random">
<cfset variables.dbtype = "">
<cfset variables.dsn = "">
<cffunction name="init" access="public" output="false" returntype="Random">
<cfargument name="dbtype" type="string" required="true">
<cfargument name="dsn" type="string" required="true">
<cfset var validDbTypes = "mysql,mssql,postgre,oracle">
<cfif NOT listFindNoCase(validDbTypes,arguments.dbtype,",")>
<cfthrow message="Invalid dbtype. Valid dbtypes are mysql,mssql,postgre and oracle.">
<cfelse>
<cfset variables.dbtype = arguments.dbtype>
<cfset variables.dsn = arguments.dsn>
</cfif>
<cfreturn this>
</cffunction>
<cffunction name="getRandomRow" access="public" output="false" returntype="query">
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" default="1">
<cfset var qRow = "">
<cfswitch expression="#variables.dbtype#">
<cfcase value="mysql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT *
FROM #arguments.tablename#
ORDER BY RAND()
LIMIT #arguments.topN#
</cfquery>
</cfcase>
<cfcase value="mssql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT TOP #arguments.topN# *
FROM #arguments.tablename#
ORDER BY NEWID()
</cfquery>
</cfcase>
<cfcase value="postgre">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT #arguments.topN#
FROM #arguments.tablename#
ORDER BY RANDOM()
LIMIT 1
</cfquery>
</cfcase>
<cfcase value="oracle">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT * FROM
( SELECT * FROM #arguments.tablename#
ORDER BY dbms_random.value )
WHERE rownum = #arguments.topN#
</cfquery>
</cfcase>
</cfswitch>
<cfif qRow.recordCount EQ 0>
<cfthrow message="The table [#arguments.tablename#] returned 0 records.">
</cfif>
<cfreturn qRow>
</cffunction>
<cffunction name="getRandomRows" access="public" output="false" returntype="query">
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" required="true">
<cfset var results = getRandomRow(arguments.tablename,arguments.topN)>
<cfreturn results>
</cffunction>
<cffunction name="getRandomString" access="public" output="false" returntype="string">
<cfargument name="type" type="string" required="true">
<cfargument name="length" type="numeric" required="true">
<cfset var validTypes = "alpha,alphanum,secure,numeric">
<cfset var i = 1>
<cfset var randStr = "">
<cfset var randNum = "">
<cfset var alpha = "A,B,C,D,E,F,G,H,I,J,K,L,
M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z">
<cfset var secure = "!,@,$,%,&,*,-,_,=,+,?,~">
<cfif NOT listFindNoCase(validTypes,arguments.type,",")>
<cfthrow message="Invalid type. Valid types are alpha,alphanum,secure,numeric.">
<cfelse>
<!--- RandString() http://www.cflib.org/udf.cfm?id=171&enable=1 --->
<cfscript>
/**
* Returns a random string of the specified length of either alpha, numeric or mixed-alpha-numeric characters.
* v2, support for lower case
* v3 - more streamlined code
*
* @param Type Type of random string to create. (Required)
* @param Length Length of random string to create. (Required)
* @return Returns a string.
* @author Joshua Miller (josh@joshuasmiller.com)
* @version 2, November 4, 2003
*/
for(i=1;i LTE arguments.length;i=i+1){
if(arguments.type is "alpha"){
randNum=RandRange(1,52);
useList=alpha;
}else if(arguments.type is "alphanum"){
randNum=RandRange(1,62);
useList="#alpha#,0,1,2,3,4,5,6,7,8,9";
}else if(arguments.type is "secure"){
randNum=RandRange(1,73);
useList="#alpha#,0,1,2,3,4,5,6,7,8,9,#secure#";
}else if(arguments.type is "numeric"){
randNum=RandRange(1,10);
useList="0,1,2,3,4,5,6,7,8,9";
}
randStr="#randStr##ListGetAt(useList,randNum)#";
}
return randStr;
</cfscript>
</cfif>
</cffunction>
<cffunction name="getRandomColor" access="public" output="false" returntype="string">
<cfargument name="escapePoundSign" type="boolean" default="false">
<cfset var redColor = formatBaseN(randRange(0,255),16)>
<cfset var greenColor = formatBaseN(randRange(0,255),16)>
<cfset var blueColor = formatBaseN(randRange(0,255),16)>
<cfscript>
/**
* Returns a completely random color. Beautiful, isn't it?
* Version 2 by Raymond Camden
*
* @return Returns a string.
* @author Nathan Strutz (nathans@dnsfirm.com)
* @version 2, May 13, 2003
*/
if(len(redColor) is 1) redColor = "0" & redColor;
if(len(greenColor) is 1) greenColor = "0" & greenColor;
if(len(blueColor) is 1) blueColor = "0" & blueColor;
return "##" & redColor & greenColor & blueColor;
</cfscript>
</cffunction>
<cffunction name="getRandomDirectory" access="public" output="false" returntype="string">
<cfargument name="rootDirectory" type="string" required="true">
<cfargument name="recurse" type="boolean" default="false">
<cfset var directoryName = "">
<cfset var qDirectories = "">
<cfset var listing = "">
<cfif directoryExists(arguments.rootDirectory)>
<cfdirectory action="list" directory="#arguments.rootDirectory#" name="qDirectories" recurse="#arguments.recurse#">
<cfif qDirectories.recordCount GT 0>
<cfloop query="qDirectories">
<cfif type EQ "dir">
<cfset listing = listAppend(listing,"#name#")>
</cfif>
</cfloop>
<cfelse>
<cfthrow message="The were no directories found under the root directory [#arguments.rootDirectory#].">
</cfif>
<cfelse>
<cfthrow message="The root directory passed to getRandomDirectoryName is not valid">
</cfif>
<cfreturn listGetAt(listing,randRange(1,listLen(listing)),",")>
</cffunction>
<cffunction name="getRandomImage" access="public" output="false" returntype="string">
<cfargument name="imageDirectory" type="string" required="true" hint="Absolute path to the direcotry.">
<cfset var qFiles = "">
<cfset var images = "">
<cfif directoryExists(arguments.imageDirectory)>
<cfdirectory action="list" directory="#arguments.imageDirectory#" name="qFiles">
<cfif qFiles.recordCount GT 0>
<cfloop query="qFiles">
<cfif type EQ "File" AND (findNoCase("gif",name) OR findNoCase("jpg",name) OR findNoCase("png",name)) >
<cfset images = listAppend(images,"#name#")>
</cfif>
</cfloop>
<cfelse>
<cfthrow message="There were no images found in directory [#arguments.imageDirectory#].">
</cfif>
<cfelse>
<cfthrow message="The root directory passed to getRandomDirectoryName is not valid">
</cfif>
<cfreturn listGetAt(images,randRange(1,listLen(images)),",")>
</cffunction>
</cfcomponent>
index.cfm (used to test)
<html>
<head>
<title>Radmom CFC Testing</title>
</head>
<body>
<cfset rand = createObject("component", "org.danvega.Random")>
<cfset rand.init("mssql","test")>
<h2>Random Row Example</h2>
<cfset randrow = rand.getRandomRow("client")>
<cfdump var="#randrow#">
<br><br>
<h2>Random Row Example - Table with no rows</h2>
<cftry>
<cfset randrowNull = rand.getRandomRow("school")>
<cfdump var="#randrowNull#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
<h2>Random Rows Example - Return n random rows</h2>
<cfset randrows = rand.getRandomRow("client", 10)>
<cfdump var="#randrows#">
<br><br>
<h2>Random String Example - Return a random string (alpha,10)</h2>
<cfset randstring = rand.getRandomString("alpha", 10)>
<cfdump var="#randstring#">
<br><br>
<h2>Random String Example - Return a random string (alphanum,10)</h2>
<cfset randstring2 = rand.getRandomString("alphanum", 10)>
<cfdump var="#randstring2#">
<br><br>
<h2>Random String Example - Return a random string (secure,10)</h2>
<cfset randstring3 = rand.getRandomString("secure", 10)>
<cfdump var="#randstring3#">
<br><br>
<h2>Random String Example - Return a random string (numeric,10)</h2>
<cfset randstring4 = rand.getRandomString("numeric", 10)>
<cfdump var="#randstring4#">
<br><br>
<h2>Random Color Example - Return a random color</h2>
<cfset randcolor = rand.getRandomColor()>
<cfoutput>
<h1 style="color:#randcolor#">Random Color</h1>
</cfoutput>
<br><br>
<h2>Random Direcotry - An exception thrown with a bad directory</h2>
<cftry>
<cfset randDir = rand.getRandomDirectory("c:\danissweet")>
<cfdump var="#randDir#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
<h2>Random Direcotry - List Random Directory under parent directory thats passed.</h2>
<cftry>
<cfset randDir2 = rand.getRandomDirectory("c:\")>
<cfdump var="#randDir2#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
<!---
this 1 could take a very long time depending on disk size so by default leave commented out
<h2>Random Direcotry - List Random Directory under parent directory thats passed (same as above except this uses recursive lookup).</h2>
<cftry>
<cfset randDir3 = rand.getRandomDirectory("c:\",true)>
<cfdump var="#randDir3#">
<cfcatch type="org.danvega.random.dirNotFound">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
--->
<h2>Random Image - List A Random image under a direcotry containing images</h2>
<cfset randImage = rand.getRandomImage("C:\Documents and Settings\dvega\Desktop")>
<cfdump var="#randImage#">
<br><br>
<cfdump var="#rand#">
</body>
</html>
<head>
<title>Radmom CFC Testing</title>
</head>
<body>
<cfset rand = createObject("component", "org.danvega.Random")>
<cfset rand.init("mssql","test")>
<h2>Random Row Example</h2>
<cfset randrow = rand.getRandomRow("client")>
<cfdump var="#randrow#">
<br><br>
<h2>Random Row Example - Table with no rows</h2>
<cftry>
<cfset randrowNull = rand.getRandomRow("school")>
<cfdump var="#randrowNull#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
<h2>Random Rows Example - Return n random rows</h2>
<cfset randrows = rand.getRandomRow("client", 10)>
<cfdump var="#randrows#">
<br><br>
<h2>Random String Example - Return a random string (alpha,10)</h2>
<cfset randstring = rand.getRandomString("alpha", 10)>
<cfdump var="#randstring#">
<br><br>
<h2>Random String Example - Return a random string (alphanum,10)</h2>
<cfset randstring2 = rand.getRandomString("alphanum", 10)>
<cfdump var="#randstring2#">
<br><br>
<h2>Random String Example - Return a random string (secure,10)</h2>
<cfset randstring3 = rand.getRandomString("secure", 10)>
<cfdump var="#randstring3#">
<br><br>
<h2>Random String Example - Return a random string (numeric,10)</h2>
<cfset randstring4 = rand.getRandomString("numeric", 10)>
<cfdump var="#randstring4#">
<br><br>
<h2>Random Color Example - Return a random color</h2>
<cfset randcolor = rand.getRandomColor()>
<cfoutput>
<h1 style="color:#randcolor#">Random Color</h1>
</cfoutput>
<br><br>
<h2>Random Direcotry - An exception thrown with a bad directory</h2>
<cftry>
<cfset randDir = rand.getRandomDirectory("c:\danissweet")>
<cfdump var="#randDir#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
<h2>Random Direcotry - List Random Directory under parent directory thats passed.</h2>
<cftry>
<cfset randDir2 = rand.getRandomDirectory("c:\")>
<cfdump var="#randDir2#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
<!---
this 1 could take a very long time depending on disk size so by default leave commented out
<h2>Random Direcotry - List Random Directory under parent directory thats passed (same as above except this uses recursive lookup).</h2>
<cftry>
<cfset randDir3 = rand.getRandomDirectory("c:\",true)>
<cfdump var="#randDir3#">
<cfcatch type="org.danvega.random.dirNotFound">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<br><br>
--->
<h2>Random Image - List A Random image under a direcotry containing images</h2>
<cfset randImage = rand.getRandomImage("C:\Documents and Settings\dvega\Desktop")>
<cfdump var="#randImage#">
<br><br>
<cfdump var="#rand#">
</body>
</html>
You can download the entire zip file using the download link below. So how does it work, lets dive into the code and take a look. The init method of our component sets our dsn and our dbtype. If an incorrect dbtype is passed our component will return an error message. If it is a valid dbtype then we will initialize our component and return and instance back.
<cffunction name="init" access="public" output="false" returntype="Random">
<cfargument name="dbtype" type="string" required="true">
<cfargument name="dsn" type="string" required="true">
<cfset var validDbTypes = "mysql,mssql,postgre,oracle">
<cfif NOT listFindNoCase(validDbTypes,arguments.dbtype,",")>
<cfthrow message="Invalid dbtype. Valid dbtypes are mysql,mssql,postgre and oracle.">
<cfelse>
<cfset variables.dbtype = arguments.dbtype>
<cfset variables.dsn = arguments.dsn>
</cfif>
<cfreturn this>
</cffunction>
<cfargument name="dbtype" type="string" required="true">
<cfargument name="dsn" type="string" required="true">
<cfset var validDbTypes = "mysql,mssql,postgre,oracle">
<cfif NOT listFindNoCase(validDbTypes,arguments.dbtype,",")>
<cfthrow message="Invalid dbtype. Valid dbtypes are mysql,mssql,postgre and oracle.">
<cfelse>
<cfset variables.dbtype = arguments.dbtype>
<cfset variables.dsn = arguments.dsn>
</cfif>
<cfreturn this>
</cffunction>
Next we have our two methods that deal with our db records. If you read the link to Pete Freitag's article than our getRandomRow method will look familiar. Basically it takes the table name you want to retrieve a random row from, and limits it to 1.
<cffunction name="getRandomRow" access="public" output="false" returntype="query">
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" default="1">
<cfset var qRow = "">
<cfswitch expression="#variables.dbtype#">
<cfcase value="mysql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT *
FROM #arguments.tablename#
ORDER BY RAND()
LIMIT #arguments.topN#
</cfquery>
</cfcase>
<cfcase value="mssql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT TOP #arguments.topN# *
FROM #arguments.tablename#
ORDER BY NEWID()
</cfquery>
</cfcase>
<cfcase value="postgre">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT #arguments.topN#
FROM #arguments.tablename#
ORDER BY RANDOM()
LIMIT 1
</cfquery>
</cfcase>
<cfcase value="oracle">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT * FROM
( SELECT * FROM #arguments.tablename#
ORDER BY dbms_random.value )
WHERE rownum = #arguments.topN#
</cfquery>
</cfcase>
</cfswitch>
<cfif qRow.recordCount EQ 0>
<cfthrow message="The table [#arguments.tablename#] returned 0 records.">
</cfif>
<cfreturn qRow>
</cffunction>
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" default="1">
<cfset var qRow = "">
<cfswitch expression="#variables.dbtype#">
<cfcase value="mysql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT *
FROM #arguments.tablename#
ORDER BY RAND()
LIMIT #arguments.topN#
</cfquery>
</cfcase>
<cfcase value="mssql">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT TOP #arguments.topN# *
FROM #arguments.tablename#
ORDER BY NEWID()
</cfquery>
</cfcase>
<cfcase value="postgre">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT #arguments.topN#
FROM #arguments.tablename#
ORDER BY RANDOM()
LIMIT 1
</cfquery>
</cfcase>
<cfcase value="oracle">
<cfquery name="qRow" datasource="#variables.dsn#">
SELECT * FROM
( SELECT * FROM #arguments.tablename#
ORDER BY dbms_random.value )
WHERE rownum = #arguments.topN#
</cfquery>
</cfcase>
</cfswitch>
<cfif qRow.recordCount EQ 0>
<cfthrow message="The table [#arguments.tablename#] returned 0 records.">
</cfif>
<cfreturn qRow>
</cffunction>
Finally, the getRandomRow() method accepts a topN argument. This is so our getRandomRows can work and just use this method instead of reproducing code.
<cffunction name="getRandomRows" access="public" output="false" returntype="query">
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" required="true">
<cfset var results = getRandomRow(arguments.tablename,arguments.topN)>
<cfreturn results>
</cffunction>
<cfargument name="tablename" type="string" required="true">
<cfargument name="topN" type="numeric" required="true">
<cfset var results = getRandomRow(arguments.tablename,arguments.topN)>
<cfreturn results>
</cffunction>
There are also some utilty functions I found on cflib that I included in there because I needed them. Again you can download the link below and let me know if you have any problems. I have not had a chance to test against every DMBS and will probably split this into multiple components in the near future. I would love your feedback if you get a chance though. I am also interested in your feedback on how this could be separated. The reason you want to separate this is because the utility functions do not need to know about a dsn or dbtype. It is kind of dumb to have to initialize the component if all you need to do is getRandomString().
