ColdFusion CFScript Query of Queries Example

This post is more for me than anything else...but I couldn't find a decent example of how to write a query of queries in ColdFusion's CFScript syntax. Below you'll see two queries; the first one is a simple query using ColdFusion's auto-generated dsn, the second one narrows the result set just a bit.

<cfscript>
    // Basic Query Syntax
    q1 = new Query();
    q1.setDatasource('cfartgallery');
    q1.setSQL('select * from artists');
    rs1 = q1.execute().getResult();
    WriteDump(var=rs1,label='RS1');
    
    // Query of Queries
    q2 = new Query();
    q2.setDBType('query');
    q2.setAttributes(rs=rs1); // needed for QoQ
    q2.addParam(name='state', value='CO', cfsqltype='cf_sql_varchar');
    q2.setSQL('SELECT * FROM rs where state = :state');
    q2.setMaxRows(2); // limit max rows, if desired
    rs2 = q2.execute().getResult();
    WriteDump(var=rs2,label='RS2');
</cfscript>

This should produce something similar to the following output:

Hope this helps...cheers!

SQL Syntax To Get Columns From Database

Have you ever wanted a simple way to get the column names of a database table, but just didn't know how to go about doing it? I've seen this question come up from time-to-time in the wild and thought I would share my simple solution using straightforward SQL syntax.

Obviously, most programming languages offer methods to obtain this information as well, however I thought this might be helpful to ColdFusion/CFML developers or any other SQL users who are looking for a quick and painless way to get what they need.

MySQL Syntax


<!--- MySQL syntax to get column names --->
<cfquery name="rsMySQLColumns" datasource="MyDSN">
    SHOW COLUMNS from MyTable
</cfquery>
<cfset MySQLColumns = ValueList(rsMySQLColumns.Field)>

MS SQL Syntax


<!--- MS SQL syntax to get column names --->
<cfquery name="rsMSSQLColumns" datasource="MyDSN">
    SELECT column_name,*
    FROM information_schema.columns
    WHERE table_name = 'MyTable'
    ORDER BY ordinal_position
</cfquery>
<cfset MSSQLColumns = ValueList(rsMSSQLColumns.column_name)>

Cheers!

Simple User-Defined Function (UDF) Get ID of New Record With ColdFusion

I've been doing more development for applications that could be used with a variety of databases. So recently I whipped up this little user-defined function (UDF) to grab the identity (ID) of a row that was inserted into a database that I thought other developers might find useful. You'll need at least ColdFusion 8 to use this little puppy.


<!---
Pass me a CFQuery result struct and I'll try my best to get the identity (ID) of a row inserted into a database.
Works with SQL Server, Oracle, Sybase, Informix, and MySQL.
Requires ColdFusion 8+

@param qResult query result structure (Required)
@return Returns any
@author Stephen Withington (steve@stephenwithington.com)
@version 0, January 7, 2010
--->

<cffunction name="getIdentity" returntype="any" access="remote" output="false">
    <cfargument name="qResult" type="struct" required="true" />
    <cfscript>
        var local = structNew();
        local.identity = "unknown";
        
        if ( structKeyExists(arguments, "qResult") ) {
        
            // SQL Server
            if ( structKeyExists(arguments.qResult, "IDENTITYCOL") ) {
                local.identity = arguments.qResult.identitycol;
            } else
            // Oracle
            if ( structKeyExists(arguments.qResult, "ROWID") ) {
                local.identity = arguments.qResult.rowid;
            } else
            // Sybase
            if ( structKeyExists(arguments.qResult, "SYB_IDENTITY") ) {
                local.identity = arguments.qResult.syb_identity;
            } else
            // Informix
            if ( structKeyExists(arguments.qResult, "SERIAL_COL") ) {
                local.identity = arguments.qResult.serial_col;
            } else
            // MySQL
            if ( structKeyExists(arguments.qResult, "GENERATED_KEY") ) {
                local.identity = arguments.qResult.generated_key;
            };

        };
        
        return local.identity;
    
</cfscript>
</cffunction>

Example Usage


<cffunction name="addName" access="public" returntype="any" output="false">
    <cfargument name="name" type="string" required="true" />
    <cfscript>
        var local = structNew();
        var rs = "";
    
</cfscript>
    <cfquery datasource="#getDatasource()#" username="#getDBUsername()#" password="#getDBPassword()#" name="rs" result="local.rsResult">
        INSERT INTO tblNames ([Name])
        VALUES (<cfqueryparam value="#arguments.name#" cfsqltype="cf_sql_varchar" maxlength="50" />)
    </cfquery>
    <cfscript>
        local.identity = getIdentity(local.rsResult);
        return local.identity;
    
</cfscript>
</cffunction>

Hope this helps someone else other than me!

More Entries

© 2024, Stephen J. Withington, Jr.  |  Hosted by Hostek.com

Creative Commons License   |   This work is licensed under a Creative Commons Attribution 3.0 Unported License.